POI对List数据导出Excel

------------------------------封装类---------------------------

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 导出帮助类：使用此类需要配合@ExportBean注解
 */
public class ExcelExportUtil {
    private ExcelDataStrategy excelDataStrategy;

    public ExcelExportUtil() {

    }

    public ExcelExportUtil(ExcelDataStrategy excelDataStrategy) {
        this.excelDataStrategy = excelDataStrategy;
    }


    /**
     * 导出方法
     *
     * @param response
     * @param fileName 导出的文件名
     * @param beanList 导出的数据
     * @param metaMap  元信息 clazz类对象,columnNames字段名String[],columnWidth列宽Integer[],columnComment列名String[]
     */
    public void exportBean(HttpServletResponse response, String fileName, List beanList, Map<String, Object> metaMap) throws Exception {
        try {

            Map<String, Object> objectMap = excelDataStrategy.getExcelDataMap(beanList, metaMap);

            String[] columnComments = (String[]) objectMap.get("columnComment");
            //列宽
            Integer[] excelHeaderWidth = (Integer[]) objectMap.get("columnWidth");
            //数据列
            Map<Integer, List<String>> dataMap = (Map<Integer, List<String>>) objectMap.get("data");
//            XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

            // keep 100 rows in memory, exceeding rows will be flushed to disk
            SXSSFWorkbook wb = new SXSSFWorkbook(100);
            Sheet sheet = wb.createSheet();
            sheet.setDefaultRowHeight((short) 300);

            // 设置列宽度
            if (excelHeaderWidth != null && excelHeaderWidth.length > 0) {

                for (int i = 0; i < excelHeaderWidth.length; i++) {
                    sheet.setColumnWidth(i, 32 * excelHeaderWidth[i]);
                }
            } else {//默认宽度
                for (int i = 0; i < columnComments.length; i++) {
                    sheet.setColumnWidth(i, 32 * 250);
                }
            }
            // 标题
            Cell cell;
            //列名
            String[] cells = columnComments;
            Row titleRow = sheet.createRow(0);
            for (int i = 0; i <= cells.length - 1; i++) {
                cell = titleRow.createCell((short) i);
                cell.setCellValue(cells[i]);
            }

            CellStyle cellStyle = wb.createCellStyle();
            wb.createCellStyle();
            DataFormat format = wb.createDataFormat();
            cellStyle.setDataFormat(format.getFormat("@"));
            Set<Map.Entry<Integer, List<String>>> entrySet = dataMap.entrySet();
            int size = entrySet.size();

            for (int i = 1; i <= size; i++) {
                Row dataRow = sheet.createRow(i);
                List<String> dList = dataMap.get(i);
                for (int j = 0; j < dList.size(); j++) {
                    cell = dataRow.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(dList.get(j));
                }
            }
            response.setContentType("application/vnd.ms-excel");
            String name = fileName + System.currentTimeMillis() + ".xlsx";
            String filename = "\"" + new String(name.getBytes(), "iso-8859-1") + "\"";
            response.addHeader("Content-Disposition", "attachment; filename=" + filename);
            wb.write(response.getOutputStream());
//            wb.dispose();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {

        }
    }
}

 

------------------------------调用---------------------------

 

@RequestMapping("export")
@ResponseBody
public Map<String, Object> export(HttpServletResponse response) throws Exception {
   Map<String, Object> resMap = new HashMap<String, Object>();
   String statusCode = "200", message = "导出成功";
   String[] columnNameArr = {"materialName", "materialModel","manufacturer","enterNumber","batchNumber","enterTime"};  //数据库中的字段名
   String fileName = "物料管理";
   Map<String, Object> metaMap = new HashMap<>();
   String[] columnComment = {"物料名称", "型号规格","生产厂家","进场数量","批号","进场时间"};  //Excel中的第一行
   Integer[] columnWidth = {250, 250, 250};        //列宽     
   metaMap.put("columnName", columnNameArr);
   metaMap.put("columnWidth", columnWidth);
   metaMap.put("columnComment", columnComment);
   ExcelExportUtil excelExportUtil = new ExcelExportUtil(new ExcelDataNormalStrategy());
   try {
      excelExportUtil.exportBean(response, fileName, (List) ****, metaMap);//response、filename、数组list、map
   } catch (Exception e) {
      statusCode = "300";
      message = "导出失败";
      e.printStackTrace();
   }
   resMap.put("statusCode",statusCode);
   resMap.put("message",message);
   return resMap;
}