仓库源文，站点原文

layout: post title: "Workbook导出Excel文件的思路" categories: Tools tags: Workbook Excel 文件导出 IO

author: 张乘辉

• content {:toc} 一个Workbook类可以看成一个Excel文件（即工作簿）：

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

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

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

创建一个生成Workbook和响应输出流的工具类

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();
            }
        }
    }
}

在业务层封装好需要填充到Workbook的数据

@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();
                    }
                }
            }
    }
}

页面表单请求

<!--导出表单-->
    <form id="exportExcel" name="fileExport" method="POST" target="_blank">
        <div class="form-group">
            <input type="hidden" name="startDate">
            <input type="hidden" name="endDate">
            <input type="hidden" name="shopCode">
            <input type="hidden" name="tradeNo">
        </div>
    </form>


// 按钮
<button type="button" class="btn btn-info form-group" ng-click="exportBtnClick()">导出</button>

 // 导出
 $scope.exportBtnClick = function () {
     $("input[name='startDate']").val(new Date(start).getTime());
     $("input[name='endDate']").val(new Date(end).getTime());
     $("input[name='shopCode']").val($scope.searchParams.shopCode);// 从angular作用域中取值
     $("input[name='tradeNo']").val($scope.searchParams.tradeNo);
     // 表单提交
     var form = $("#exportExcel");
     form.attr("action", HostManageService._intergralHost" + "/AccountingAdjustment/export");
     form.submit();
 };

总结

导出Excel文件也就是相当于下载，就是向客户端响应字节数据，先把从数据库查询出来的数据封装到workbook对象中，再使用response.getOutputStream()向客户端响应workbook的数据，并设置相应的响应头让浏览器知道这是个下载文件的响应。