POI实现导出Excel数据工具类

  项目中很多地方需要导出excel数据，因此需要将导出excel数据封装成工具类。

实现思路:

      1、将查询结果List<T>以及T作为入参。

      2、新建一个注解，在T对象需要生成表格数据的字段加上这个注解。

供外部调用的方法:

import java.io.IOException;import java.util.List;import javax.servlet.http.HttpServletResponse;import com.coolpad.store.common.export.DataExportUtil;public class WebDataExporter {static public <T> boolean exportWebExcelWithAnnotation(HttpServletResponse resp, List<T> dataList, Class<T> clzz) throws IOException {resp.setContentType("application/vnd.ms-excel");resp.setHeader("Content-disposition", "attachment;filename=ExportData.xls");  return DataExportUtil.<T> exportExcelWithAnnotation(resp.getOutputStream(),dataList,clzz);}}

导出Excel实现方法:

import java.io.OutputStream;import java.lang.reflect.Field;import java.util.LinkedHashMap;import java.util.LinkedList;import java.util.List;import java.util.Map;import org.apache.commons.beanutils.BeanMap;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;public class DataExportUtil {static public <T> boolean exportExcelWithAnnotation(OutputStream ouputStream, List<T> dataList, Class<T> clzz) {HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("导出数据");sheet.setColumnWidth(0, 20 * 256);HSSFCellStyle style = wb.createCellStyle();// style.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居中格式HSSFFont font = wb.createFont();font.setFontHeightInPoints((short) 12);font.setColor(HSSFColor.BLACK.index);// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);font.setFontName("宋体");style.setFont(font);HSSFCellStyle styleHeader = wb.createCellStyle();styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式styleHeader.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中HSSFFont fontHeader = wb.createFont();fontHeader.setFontHeightInPoints((short) 15);fontHeader.setColor(HSSFColor.BLACK.index);fontHeader.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);fontHeader.setFontName("黑体");styleHeader.setFont(font);// 背景色HSSFColor.LIGHT_GREEN.indexstyleHeader.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);styleHeader.setFillBackgroundColor(HSSFColor.YELLOW.index);// row.setRowStyle(styleHeader);HSSFRow row = sheet.createRow((int) 0);row.setHeightInPoints(30);HSSFCell cell = row.createCell((short) 0);cell.setCellValue("序号");cell.setCellStyle(styleHeader);Map<String, String> headerMap = new LinkedHashMap<String, String>();List<String> headers = new LinkedList<String>();Field[] fields = clzz.getDeclaredFields();for (Field field : fields) {ExportHeader exHeader = field.getAnnotation(ExportHeader.class);if(null==exHeader){continue;}String header = exHeader.value();headers.add(header);headerMap.put(header, field.getName());}for (int i = 0; i < headers.size(); i++) {cell = row.createCell((short) i + 1);cell.setCellValue(headers.get(i));cell.setCellStyle(styleHeader);}for (int i = 0; i < dataList.size(); i++) {sheet.setColumnWidth(i, 20 * 256);row = sheet.createRow((int) i + 1);row.setHeightInPoints(20);BeanMap beanMap = new BeanMap(dataList.get(i));cell = row.createCell((short) 0);cell.setCellValue(i + 1);cell.setCellStyle(style);int j = 0;for (Map.Entry<String, String> kv : headerMap.entrySet()) {String header = kv.getKey();String fieldName = kv.getValue();Object val = beanMap.get(fieldName);cell = row.createCell((short) j + 1);cell.setCellValue(val!=null?val.toString():"");cell.setCellStyle(style);j++;}}try {wb.write(ouputStream);ouputStream.flush();ouputStream.close();} catch (Exception e) {e.printStackTrace();}return true;}}

注解类:

import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)public @interface ExportHeader {String value();}

查询结果数据对象:

public class User {private int id;//没有注解，不需要导出这个字段@ExportHeader("姓名")private String name;@ExportHeader("年龄")private int age;@ExportHeader("地址")private String address;public String getName() {return name;}public void setName(String name) {this.name = name;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public int getId() {return id;}public void setId(int id) {this.id = id;}}

  至此，已经完成了Excel导出工具的编写。在项目的其他地方调用WebDataExporter的exportWebExcelWithAnnotation方法即可。