HSSFWorkbook EXCEL导出

1.导入pom依赖

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

 

2. Jakarta POI HSSF API组件

HSSF（用于操作Excel的组件）提供给用户使用的对象在rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象，样式和格式，还有辅助操作。有以下几种对象： 

常用组件：

HSSFWorkbook                      excel的文档对象

HSSFSheet                         excel的表单

HSSFRow                           excel的行

HSSFCell                          excel的格子单元

HSSFFont                          excel字体

HSSFDataFormat                    日期格式

HSSFHeader                        sheet头

HSSFFooter                        sheet尾（只有打印的时候才能看到效果）

样式：

HSSFCellStyle                       cell样式

辅助操作包括：

HSSFDateUtil                        日期

HSSFPrintSetup                      打印

HSSFErrorConstants                  错误信息表

 

3. 基本操作步骤

首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。

基本操作步骤：
 

private static final String[] excelHeader = {"产品", "资产前端标识", "客户号", "客户姓名",
            "证件号码","贷款账号","合同号", "借据号","贷款金额","贷款期限(月)","担保方式","还款方式", "放款账号","放款日期","到期日期","电话","地址","利率"};

    @Autowired
    private YkLendApplyDao ykLendApplyDao;
    
    @Autowired
    private LenderProductInfoService lenderProductInfoService;

    @Autowired
    private CustomerInfoServiceImpl customerInfoService;

public HSSFWorkbook export(String proCode, Date beginDate, Date endDate){

        //表头第一行
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet0 = wb.createSheet("report01");
        HSSFRow row0 = sheet0.createRow(0);
        HSSFCellStyle headerStyle = getCellStyle(wb, "宋体", true,14, HSSFCellStyle.ALIGN_CENTER);
        HSSFCell cell0 = row0.createCell(0);
        cell0.setCellValue("网贷发放明细报表");
        cell0.setCellStyle(headerStyle);
        sheet0.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));

        //表头第二行 机构+日期行
        //机构0-9
        HSSFRow row1 = sheet0.createRow(1);
        HSSFCell cellOrg = row1.createCell(0);
        HSSFCellStyle orgRowStyle = getCellStyle(wb, "宋体", false,9, HSSFCellStyle.ALIGN_LEFT);
        cellOrg.setCellValue("填报机构：银行总行");
        cellOrg.setCellStyle(orgRowStyle);
        sheet0.addMergedRegion(new CellRangeAddress(1, 1, 0, 8));

        //日期10-18
        HSSFCell cellDate = row1.createCell(9);
        HSSFCellStyle dateRowStyle = getCellStyle(wb, "宋体", false,9, HSSFCellStyle.ALIGN_RIGHT);
        DateUtil.formatDate(beginDate,"yyyyMMdd");
        cellDate.setCellValue("发放日期: " + DateUtil.formatDate(beginDate,"yyyyMMdd") + "至" +
                DateUtil.formatDate(endDate,"yyyyMMdd"));
        cellDate.setCellStyle(dateRowStyle);
        sheet0.addMergedRegion(new CellRangeAddress(1, 1, 9, 17));

        //表头第三行列名
        HSSFRow row2 = sheet0.createRow(2);
        HSSFCellStyle headRowStyle = getCellStyle(wb, "宋体", false, 11, HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < excelHeader.length; i++) {
            HSSFCell cell = row2.createCell(i);
            cell.setCellValue(excelHeader[i]);
            cell.setCellStyle(headRowStyle);
        }

        //表内容
        // 单元格列宽
        int[] excelHeaderWidth = { 10, 15, 13, 10, 20, 20, 25, 25, 10, 15, 10, 10,20, 10, 10, 15, 20, 10 };

        // 设置列宽度（像素）
        for (int i = 0; i < excelHeaderWidth.length; i++) {
            sheet0.setColumnWidth(i, 256 * excelHeaderWidth[i]);
        }
        
        List<YkLendApply> lendApplyList = ykLendApplyDao.getByLoanTime(beginDate, endDate);
        LenderProductInfo productInfo = lenderProductInfoService.findByCode(proCode);
        String lenderProdNo = productInfo.getLenderProdNo();    //产品编号
        String contInt = productInfo.getContInt().toString();  //利率
        String productInfoName = productInfo.getName();//产品名
        HSSFRow row3;
        if(lendApplyList != null && lendApplyList.size() > 0){
            for (int i = 0; i < lendApplyList.size(); i++) {
                row3 = sheet0.createRow(i + 3);
                YkLendApply lendApply = lendApplyList.get(i);
                row3.createCell(0).setCellValue(lenderProdNo);
                row3.createCell(1).setCellValue(productInfoName);
                row3.createCell(2).setCellValue(lendApply.getCustNo());
                row3.createCell(3).setCellValue(lendApply.getCustName());
                row3.createCell(4).setCellValue(lendApply.getIdNo());
                row3.createCell(5).setCellValue(lendApply.getLnAcNo());
                row3.createCell(6).setCellValue(lendApply.getContNo());
                row3.createCell(7).setCellValue(lendApply.getIouNo());
                row3.createCell(8).setCellValue(lendApply.getLoanAmt().toString());
                row3.createCell(9).setCellValue(lendApply.getLoanMonth());
                row3.createCell(10).setCellValue("信用");
                row3.createCell(11).setCellValue("等额本息");
                row3.createCell(12).setCellValue(lendApply.getPayeeAcNo());
                row3.createCell(13).setCellValue(DateUtil.formatDate(lendApply.getLoanTime(),"yyyyMMdd"));
                row3.createCell(14).setCellValue(lendApply.getEndLoanDate());
                row3.createCell(15).setCellValue(lendApply.getMobile());
                row3.createCell(16).setCellValue(customerInfoService.getByCustNo(
                        lendApply.getCustNo()).getAddress());
                row3.createCell(17).setCellValue(contInt);
                row3.setRowStyle(dateRowStyle);
            }
        }


        return wb;
    }

    private HSSFCellStyle getCellStyle(HSSFWorkbook wb,String FontName, boolean blod, int HeightInPoints,short alignment) {
        HSSFCellStyle style = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontName(FontName);
        font.setFontHeightInPoints((short)HeightInPoints);
        font.setBold(blod);
        style.setAlignment(alignment);
        style.setFont(font);
        return style;
    }

导出结果：

 

https://blog.csdn.net/ethan_10/article/details/80335350

http://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/HSSFWorkbook.html