基于poi的Excel文件导出(简单表头、固定模板)

依赖：

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.7</version>
            <scope>compile</scope>
        </dependency>             

 

配置：application-excel.yml

excel: 
  personnelRegister:
    fileName: 登记信息
    sheetName: 登记信息
    columnNames: 姓名,性别,年龄,登记日期,家庭地址,登记人
    keys: name,sex,age,date,address,createUserName

 

代码：

    @Value("${excel.personnelRegister.fileName}")
    private String personnelRegisterFileName;
    @Value("${excel.personnelRegister.sheetName}")
    private String personnelRegisterSheetName;
    @Value("${excel.personnelRegister.columnNames}")
    private String[] personnelRegisterColumnNames;
    @Value("${excel.personnelRegister.keys}")
    private String[] personnelRegisterKeys;
    @Autowired
    private XXXMapper xxxxxMapper;

    public void exportPersonnelRegister(HttpServletResponse response, String orgId, String startDate, String endDate, String keyWord) {

        List<Map<String, Object>> personnelRegisters = new ArrayList<Map<String, Object>>();

        if (StringUrils.hasText(orgId)) {

            Map<String, Object> param = new HashMap<String, Object>();
            param.put("orgId", orgId);
            param.put("startDate", startDate);
            param.put("endDate", endDate);
            param.put("keyWord", keyWord);
        
            personnelRegisters = xxxxxMapper.findExportPersonnelRegister(param);
        }

        try {
            ExcelView.buildExcelDocument(personnelRegisterFileName, personnelRegisterSheetName,
                    personnelRegisterColumnNames, personnelRegisterKeys, personnelRegisters, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    

ExcelView工具类

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelView {

    private static final int PAGE_SIZE = 20000;

    /**
     * 导出excel
     *
     * @Title: buildExcelDocument
     * @param fileName
     *            excel文件名
     * @param sheetName
     *            excel的页签名
     * @param columnNames
     *            excel列名
     * @param keys
     *            数据map的key值
     * @param data
     *            数据
     *
     */
    public static void buildExcelDocument(String fileName, String sheetName, String[] columnNames, String[] keys,
            List<Map<String, Object>> data, HttpServletResponse response) throws Exception {

        fileName = fileName+DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xls";
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建excel文档
        workbook = createWorkBook(workbook, sheetName, columnNames, keys, data);

        try {
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        }

        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);

        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=" + disposeFileName(fileName));
        ServletOutputStream out = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;

            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        } finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
        }
    }

    /**
     * 创建excel文档
     *
     * @Title: createWorkBook
     * @param workbook
     *            工作簿
     * @param sheetName
     *            excel的页签名
     * @param columnNames
     *            excel列名
     * @param keys
     *            数据map的key值
     * @param data
     *            数据集
     *
     */
    private static HSSFWorkbook createWorkBook(HSSFWorkbook workbook, String sheetName, String[] columnNames, String[] keys,
            List<Map<String, Object>> data) {
        // 创建excel工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = null;
        int sheetCount = 1;
        if (null != data && data.size() > 0) {

            sheetCount = data.size() % PAGE_SIZE == 0 ? data.size() / PAGE_SIZE : data.size() / PAGE_SIZE + 1;
        }
        for (int l = 1; l <= sheetCount; l++) {

            if (StringUtils.hasText(sheetName)) {
                sheet = wb.createSheet(sheetName + l);
            } else {
                sheet = wb.createSheet();
            }

            // 创建第一行
            Row row = sheet.createRow((short) 0);

            // 创建两种单元格格式
            CellStyle cs = wb.createCellStyle();
            CellStyle cs2 = wb.createCellStyle();
            HSSFDataFormat format = wb.createDataFormat();  
            
            // 创建两种字体
            Font f = wb.createFont();
            Font f2 = wb.createFont();

            // 创建第一种字体样式（用于列名）
            f.setFontHeightInPoints((short) 10);
            f.setColor(IndexedColors.BLACK.getIndex());
            f.setBoldweight(Font.BOLDWEIGHT_BOLD);
            // 创建第二种字体样式（用于值）
            f2.setFontHeightInPoints((short) 10);
            f2.setColor(IndexedColors.BLACK.getIndex());

            // 设置第一种单元格的样式（用于列名）
            cs.setFont(f);
            cs.setDataFormat(format.getFormat("@"));  
//            cs.setBorderLeft(CellStyle.BORDER_THIN);
//            cs.setBorderRight(CellStyle.BORDER_THIN);
//            cs.setBorderTop(CellStyle.BORDER_THIN);
//            cs.setBorderBottom(CellStyle.BORDER_THIN);
//            cs.setAlignment(CellStyle.ALIGN_CENTER);

            // 设置第二种单元格的样式（用于值）
            cs2.setFont(f2);
            cs2.setDataFormat(format.getFormat("@"));  
//            cs2.setBorderLeft(CellStyle.BORDER_THIN);
//            cs2.setBorderRight(CellStyle.BORDER_THIN);
//            cs2.setBorderTop(CellStyle.BORDER_THIN);
//            cs2.setBorderBottom(CellStyle.BORDER_THIN);
//            cs2.setAlignment(CellStyle.ALIGN_CENTER);

            // 设置列名
            for (int i = 0; i < columnNames.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(columnNames[i]);
                cell.setCellStyle(cs);
            }

            // 设置每行每列的值
            int begin = (l - 1) * PAGE_SIZE;
            int end = (begin + PAGE_SIZE) > data.size() ? data.size() : (begin + PAGE_SIZE);

            int rowCount = 1;
            for (int i = begin; i < end; i++) {
                // 创建一行
                Row row1 = sheet.createRow((short) rowCount);
                rowCount++;
                // 在row行上创建一个方格
                for (short j = 0; j < keys.length; j++) {
                    Cell cell = row1.createCell(j);
                    String s = "";
                    if (null != data.get(i).get(keys[j])) {
                        s = String.valueOf(data.get(i).get(keys[j]));
                    }
                    s = s.trim();
                    cell.setCellValue(s);
                    cell.setCellStyle(cs2);
                }
            }
        }
        return wb;
    }

    /**
     * 处理中文名称
     *
     * @Title: toUtf8String
     * @param fileName excel文件名
     * @return
     *
     */
    public static String disposeFileName(String fileName) throws Exception {
        return new String(fileName.getBytes("GB2312"), "8859_1");
    }
}

 其他工具类