Workbook导出Excel文件的思路

Sheet sheet = Workbook.createSheet("sheetName");// 创建一个Sheet类，Sheet为工作簿中的工作表

Row row = sheet.createRow("rowName");// 创建一个Row类，Row为工作表中的某一行

Cell cell = row.createCell("cellName");// 创建一个Cell类，Cell为某一行中的单元格的值

public class ExcelUtil {

    /**
     * excel导出到输出流，谁调用谁负责关闭输出流
     *
     * @param os           输出流
     * @param excelExtName excel文件的扩展名，支持xls和xlsx，不带点号
     * @param data         Map[sheetName,rowList[cellList[cellValue]]]
     * @throws IOException
     */
    public static void writeExcel(OutputStream os, String excelExtName, Map<String, List<List<String>>> data)
            throws IOException {
        Workbook wb = null;
        try {
            if ("xls".equals(excelExtName)) {
                wb = new HSSFWorkbook();
            } else if ("xlsx".equals(excelExtName)) {
                wb = new XSSFWorkbook();
            } else {
                throw new Exception("当前文件不是excel文件");
            }

            for (String sheetName : data.keySet()) {
                Sheet sheet = wb.createSheet(sheetName);
                List<List<String>> rowList = data.get(sheetName);
                for (int i = 0; i < rowList.size(); i++) {
                    List<String> cellList = rowList.get(i);
                    Row row = sheet.createRow(i);
                    for (int j = 0; j < cellList.size(); j++) {
                        Cell cell = row.createCell(j);
                        cell.setCellValue(cellList.get(j));
                    }
                }
            }
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null) {
                wb.close();
            }
        }
    }
}

@Service
public class AccountAdjustmentService {

	@Autowired
    AccountAdjustmentMapper accountAdjustmentMapper;
    public Map<String, List<List<String>>> encapsulateAccountingAdjustment(Long startDate, Long endDate, String shopCode, String tradeNo) {

        List<AccountAdjustment> accountAdjustments = accountAdjustmentMapper.getAccountAdjustments(startDate, endDate, shopCode, tradeNo);

        Map sheetMap = new HashMap<String, List<List<String>>>();// 每个工作表对应一个键值对
        List<List<String>> rowList = new ArrayList<List<String>>();// 工作表行数据集
        List<String> cell0List = new ArrayList<String>();// 工作表第一行数据
        cell0List.add("日期");
        cell0List.add("商户识别码");
        cell0List.add("商户名称");
        cell0List.add("订单号");
        cell0List.add("调账内容");
        cell0List.add("调账结果");
        rowList.add(cell0List);

        // 填充工作表的每行数据
        for (AccountAdjustment a : accountAdjustments) {
            List<String> cellList = new ArrayList<String>();// 工作表每行的数据
            cellList.add(DateUtil.defaultFormat(a.getDate()));
            cellList.add(a.getShopCode());
            cellList.add(a.getShopName());
            cellList.add(a.getTradeNo());
            cellList.add(a.getApplyContent());
            cellList.add(a.getResultFeedbackContent());
            rowList.add(cellList);
        }

        sheetMap.put("调账单", rowList);
        return sheetMap;
    }
}

@Api(value = "调账申请与记录接口")
@RestController
@RequestMapping("/AccountingAdjustment")
public class AccountAdjustmentController {
  
	@Autowired
    private AccountAdjustmentService accountAdjustmentService;
  
    @RequestMapping(value = "/export", method = RequestMethod.POST)
    public void exportAccountingAdjustment(HttpServletResponse response, 				@RequestParam("startDate") Long startDate, @RequestParam("endDate") Long endDate, @RequestParam("shopCode") String shopCode, @RequestParam("tradeNo") String tradeNo) {

            OutputStream os = null;
            Map<String, List<List<String>>> sheetMap = accountAdjustmentService.encapsulateAccountingAdjustment(startDate, endDate, shopCode, tradeNo);

            try {
                String title = URLEncoder.encode("调账单." + CodeConstant.ms_xls, "UTF-8");
                response.setContentType("application/x-msdownload");
                response.setHeader("Content-disposition", "attachment;filename=" + title);
                response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
                response.setHeader("Pragma", "no-cache");
                response.setHeader("Expires", "0");
                response.setHeader("charset", "utf-8");
                os = response.getOutputStream();

                ExcelUtil.writeExcel(os, CodeConstant.ms_xls, sheetMap);

            } catch (IOException e) {
                if (null != os) {
                    try {
                        os.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
            }
    }
}