动态excel组装

package com.test.controller;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author lihao
 */
@RestController
@RequestMapping("/excel")
public class ExcelController {

    @RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
    @ResponseBody
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) {
        long start = System.currentTimeMillis();
        //头指标
        List<String> topDNames = new ArrayList<>();
        Map<String, List<String>> map = new HashMap<>();
        for (int i = 1; i <= 6;i++) {
            topDNames.add("rowV" + i);
            List<String> item = new ArrayList<>();
            for (int j = 1; j <= 2; j++) {
                item.add("行" + i + "数" + j);
            }
            map.put("rowV" + i, item);
        }

        //左侧指标
        List<String> leftDNames = new ArrayList<>();
        for (int i = 1; i <= 6; i++) {
            leftDNames.add("columnV" + i);
            List<String> item = new ArrayList<>();
            for (int j = 1; j <= 3; j++) {
                item.add("列" + i + "数" + j);
            }
            map.put("columnV" + i, item);
        }


        try {
            OutputStream out = response.getOutputStream();
            response.reset();
            String fileName = "test";
            String codeFileName = java.net.URLEncoder.encode(fileName, StandardCharsets.UTF_8);
            response.setHeader("Content-Disposition", "attachment; filename=" + codeFileName + ".csv");
            response.setContentType("application/octet-stream;charset=utf-8");

            Workbook workbook = new SXSSFWorkbook(10000);
            Sheet sheet = workbook.createSheet();
            sheet.setDefaultColumnWidth(15);

            //设置上侧表头
            int rowIndex = 0;
            int columnIndex = leftDNames.size();

            List<List<String>> topKeys = new ArrayList<>();
            List<List<String>> leftKeys = new ArrayList<>();

            //计算共多少列
            int allCols = 1;
            for (String topDName : topDNames) {
                int length = map.get(topDName).size();
                allCols = allCols * length;
            }
            int tmpClos = allCols;
            for (String item : topDNames) {
                List<String> columnNames = map.get(item);
                //循环次数
                int loopCnt = allCols / tmpClos;
                tmpClos = tmpClos / columnNames.size();
                Row row = sheet.getRow(rowIndex);
                if (null == row) {
                    row = sheet.createRow(rowIndex);
                }

                List<String> keyTop = new ArrayList<>();

                for (int i = 0; i < loopCnt; i++) {
                    for (String name : columnNames) {
                        Cell cell = row.createCell(columnIndex);
                        cell.setCellValue(name);
                        for (int tmp = 0; tmp < tmpClos; tmp++) {
                            keyTop.add(name);
                        }
                        //合并单元格
                        if (tmpClos > 1) {
                            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, columnIndex, columnIndex + tmpClos - 1));
                        }
                        columnIndex = columnIndex + tmpClos;
                    }
                }
                topKeys.add(keyTop);
                columnIndex = leftDNames.size();
                rowIndex++;
            }

            //设置左侧表头

            columnIndex = 0;
            rowIndex = topDNames.size();

            //计算共多少行
            int allRows = 1;
            for (String leftDName : leftDNames) {
                int length = map.get(leftDName).size();
                allRows = allRows * length;
            }
            int tmpRows = allRows;
            for (String item : leftDNames) {
                List<String> rowNames = map.get(item);
                //循环次数
                int loopCnt = allRows / tmpRows;
                tmpRows = tmpRows / rowNames.size();
                List<String> keyleft = new ArrayList<>();
                for (int i = 0; i < loopCnt; i++) {
                    for (String name : rowNames) {
                        Row row = sheet.getRow(rowIndex);
                        if (null == row) {
                            row = sheet.createRow(rowIndex);
                        }
                        Cell cell = row.createCell(columnIndex);
                        cell.setCellValue(name);
                        for (int tmp = 0; tmp < tmpRows; tmp++) {
                            keyleft.add(name);
                        }
                        //合并单元格
                        if (tmpRows > 1) {
                            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex + tmpRows - 1, columnIndex, columnIndex));
                        }
                        rowIndex = rowIndex + tmpRows;
                    }
                }
                leftKeys.add(keyleft);
                rowIndex = topDNames.size();
                columnIndex++;
            }

            //组装数据的top keys
            List<String> topKeysStrings = new ArrayList<>();
            for (int i=0;i<topKeys.get(0).size();i++){
                StringBuffer key = new StringBuffer();
                key.append(topKeys.get(0).get(i));
                for (int j=1;j<topKeys.size();j++){
                    key.append("." + topKeys.get(j).get(i));
                }
                topKeysStrings.add(String.valueOf(key));
            }

            //组装数据的left keys
            List<String> leftKeysStrings = new ArrayList<>();
            for (int i=0;i<leftKeys.get(0).size();i++){
                StringBuffer key = new StringBuffer();
                key.append(leftKeys.get(0).get(i));
                for (int j=1;j<leftKeys.size();j++){
                    key.append("." + leftKeys.get(j).get(i));
                }
                leftKeysStrings.add(String.valueOf(key));
            }

            //数据填充
            rowIndex = topDNames.size();
            columnIndex = leftDNames.size();
            for (String left:leftKeysStrings){
                for (String top:topKeysStrings){
                    Row row = sheet.getRow(rowIndex);
                    if (null == sheet.getRow(rowIndex)) {
                        row = sheet.createRow(rowIndex);
                    }
                    Cell cell = row.createCell(columnIndex);
                    cell.setCellValue(top + "/" + left);
                    columnIndex++;
                }
                columnIndex = leftDNames.size();
                rowIndex++;
            }

            //导出
            workbook.write(out);
            out.flush();
            workbook.close();
            out.close();
            //清除临时文件
            ((SXSSFWorkbook)workbook).dispose();
            System.out.println(System.currentTimeMillis() - start);

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