java excel表格导出

        在开发业务系统中，经常会有导出excel表格的需求，有的只是简单的表格，有的是包含合并单元格的比较复杂的表格，以下是我分享的excel表格导出的实现过程。

1、引入pom包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>

2、编写工具类

        大家都知道表格分为表头和内容，有的无合并单元格，有的表头有合并单元格，有的表格内容有合并单元格，下面分情况介绍。

• 简单表格合并

        即无合并单元格的表格

//简单表格，即无合并单元格的表格
public static Workbook writerOneSheet(Workbook wb, ExcelSheetContent esc) {
        List<ExcelCellContent> titleRow = esc.getTitleRow();
        List<List<ExcelCellContent>> list = esc.getList();
        String sheetName = esc.getSheetName();

        if (wb==null) wb = new XSSFWorkbook();
        Sheet sheet = (Sheet) wb.createSheet(sheetName);

        // 添加表头
        Row row = sheet.createRow(0); // 创建第二行
        for (int i = 0; i < titleRow.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titleRow.get(i).getContent());
        }

        // 循环写入行数据
        for (int i = 0; i < list.size(); i++) {
            row = (Row) sheet.createRow(i + 1);
            List<ExcelCellContent> rowList = list.get(i);
            for (int j = 0; j < rowList.size(); j++) {
                ExcelCellContent ecc = rowList.get(j);
                row.createCell(j).setCellValue(ecc.getContent());
            }
        }

        // 调整列宽
        setColumnWidthAutoSize(sheet, titleRow);
        return wb;
    }

// 设置列宽自适应
public static void setColumnWidthAutoSize(Sheet sheet, List<ExcelCellContent> titleRow) {
        for (int i = 0; i < titleRow.size(); i++) {
            int cellType = titleRow.get(i).getType();
            if (cellType == ExcelCellContent.CELL_TYPE_CN) {
                // 解决自动设置列宽中文失效的问题
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            } else {
                // 调整每一列宽度
                sheet.autoSizeColumn((short) i);
            }
        }
}

• 表格内容单元格合并

public static Workbook writerOneSheetWithMerge(Workbook wb, ExcelSheetContent esc) {
        List<ExcelCellContent> titleRow = esc.getTitleRow();
        List<List<ExcelCellContent>> list = esc.getList();
        String sheetName = esc.getSheetName();

        if (wb==null) wb = new XSSFWorkbook();
        Sheet sheet = (Sheet) wb.createSheet(sheetName);

        // 添加表头
        Row row = sheet.createRow(0); // 创建第二行
        for (int i = 0; i < titleRow.size(); i++) {
            ExcelCellContent ecc = titleRow.get(i);
            row.createCell(i).setCellValue(ecc.getContent());
        }

        // 循环写入行数据
        for (int i = 0; i < list.size(); i++) {
            row = (Row) sheet.createRow(i + 1);
            List<ExcelCellContent> rowList = list.get(i);
            for (int j = 0; j < rowList.size(); j++) {
                ExcelCellContent ecc = rowList.get(j);
                //只设置合并单元格首个单元格的内容
                if (ecc.isShow()) {
                    row.createCell(j).setCellValue(ecc.getContent());
                }
                //设置哪个范围的单元格需要合并
                if (ecc.isMerge()) {
                    CellRangeAddress region = new CellRangeAddress(ecc.firstRow, ecc.lastRow, ecc.firstCol, ecc.lastCol);
                    sheet.addMergedRegion(region);
                }
            }
        }

        // 调整列宽
        setColumnWidthAutoSize(sheet, titleRow);
        return wb;
    }

        合并单元格的关键代码为

CellRangeAddress region = new CellRangeAddress(ecc.firstRow, ecc.lastRow, ecc.firstCol, ecc.lastCol);
sheet.addMergedRegion(region);

        其中firstRow即首行下标，从0开始，lastRow即末行下标。firstCol即首列下标，从0开始，lastCol即末列下标。例如单元格B3与C3合并，firstRow=2，lastRow=2，firstCol=1,lastCol=2。

        需要注意，合并单元格时只设置首个单元格的内容即可。

• 表头单元格合并

        与内容合并同理，写法参考内容合并即可

•  导出写法 

public static void export(HttpServletResponse response, String excelName, Workbook wb) throws IOException {
        // 创建文件流
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        // 写入数据
        wb.write(os);
        // 关闭文件流
        os.close();
        byte[] writer = os.toByteArray();

        String fileName = new String((excelName + ".xlsx").getBytes("UTF-8"), "ISO-8859-1");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.setHeader("Content-Length", String.valueOf(writer.length));
        response.setContentType("application/vnd.ms-excel");
        IOUtils.write(writer, response.getOutputStream());
    }

3、业务程序调用

        以下表格中有多个sheet时的调用，单sheet去掉循环即可

public void export(HttpServletResponse response, Map<String, Map<String,String>> sheetTitleMap,Map<String, JSONArray> sheetDataMap) {
        try {
            final Workbook[] wb = {null};
            sheetTitleMap.forEach((sheet, titleMap) -> {
                // excel标题
                List<ExcelCellContent> titleRow = new ArrayList<>();
                for (String title : titleMap.keySet()) {
                    titleRow.add(new ExcelCellContent(title, titleMap.get(title), ExcelCellContent.CELL_TYPE_CN));
            }

                // excel内容
                List<List<ExcelCellContent>> rows = new ArrayList<>();
                JSONArray jsonArray = sheetDataMap.get(sheet);
                //TODO  组装成二维数组或按业务实现
                String[][] data = new String[jsonArray.size()][titleMap.size()];
                
                for (int i = 0; i < data.length; i++) {
                    List<ExcelCellContent> row = new ArrayList<>();
                    for (int j = 0; j< data[i].length; j++){
                        ExcelCellContent ecc = new ExcelCellContent(data[i][j]);
                        row.add(ecc);
                    }
                    rows.add(row);
                }

                if (wb[0] == null){
                    wb[0] = ExcelUtils.writerOneSheet(new ExcelSheetContent(sheet, titleRow, rows));
                }else {
                    wb[0] = ExcelUtils.writerOneSheet(wb[0], new ExcelSheetContent(sheet, titleRow, rows));
                }
            });
            String excelName = "导出列表";
            ExcelUtils.export(response, excelName, wb[0]);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

        实现原理是，每个sheet对应一组表头和表格内容数据，将表头数据组装成map对象，依次放入单元格中。将表格数据组装成二维数组，也依次放入单元格中。最后调用导出文件方法导出即可。

        表头和表格数据可根据实际业务需求作适当修改。

 附

        另附ExcelCellContent和ExcelSheetContent类

public class ExcelCellContent {

	String code;
	String content;// 单元格内容
	int type;// 单元格内容的类型：1英文或数字，2中文（用于解决设置自适应列宽）
	int firstRow;
	int lastRow;
	int firstCol;
	int lastCol;
	boolean show = true;
	boolean merge = false;
	List<ExcelCellContent> subCell; //合并列子集

	public static int CELL_TYPE_NUM = 1;
	public static int CELL_TYPE_CN = 2;


	public ExcelCellContent(String content) {
		this.content = content;
	}

	public ExcelCellContent(String content, int type) {
		this.content = content;
		this.type = type;
	}

	public ExcelCellContent(String code, String content, int type) {
		this.code = code;
		this.content = content;
		this.type = type;
	}
}

public class ExcelSheetContent {
	String sheetName = "sheet1";
	List<ExcelCellContent> titleRow;
	List<List<ExcelCellContent>> list;

	public ExcelSheetContent(List<ExcelCellContent> titleRow, List<List<ExcelCellContent>> list) {
		this.titleRow = titleRow;
		this.list = list;
	}

}