poi导出excel

使用poi将数据导出到excel表

1. 引人依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
 </dependency> 
           

2.创建一个工作簿，设置表格样式

HSSFWorkbook workbook = new HSSFWorkbook();

  //居中
  HSSFCellStyle style = workbook.createCellStyle();
  HSSFFont font3 = workbook.createFont();
  font3.setFontHeightInPoints((short) 12);//字体大小
  style.setFont(font3);
  style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
  style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

  //设置边框
  style .setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 
  style .setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
  style .setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
  style .setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
  
  //合并单元格
  CellRangeAddress region22 = new CellRangeAddress(4, 4, 1, 2);
  sheet.addMergedRegion(region22);
  // 使用RegionUtil类为合并后的单元格添加边框
  RegionUtil.setBorderBottom(1, cellRangeAddress, hssfSheet, hssfWorkbook); // 下边框
  RegionUtil.setBorderLeft(1, cellRangeAddress, hssfSheet, hssfWorkbook); // 左边框
  RegionUtil.setBorderRight(1, cellRangeAddress, hssfSheet, hssfWorkbook); // 有边框
  RegionUtil.setBorderTop(1, cellRangeAddress, hssfSheet, hssfWorkbook); // 上边框
  

  //如果是数字的话需要设置数字格式
  HSSFDataFormat format = workbook.createDataFormat();
  stylePoint.setDataFormat(format.getFormat("0.00")); //  两位小数
           

3.生成一个表格，设置单元格宽度，高度

//生成一个表格，设置表格名称为"sheet1"
  HSSFSheet sheet = workbook.createSheet("sheet1");

  //设置表格列宽度为10个字节
  sheet.setDefaultColumnWidth(18);//设置默认高度
  sheet.setColumnWidth(0, 10 * 256);//设置第一行宽度
           

4.创建表头，文本内容

//创建表头
        HSSFRow headRow = sheet.createRow(7);
        headRow.setHeight((short)450);//设置行高
        //设置表头信息
        HSSFCell headCell0 = headRow.createCell(0);
        HSSFCell headCell1 = headRow.createCell(1);
        HSSFCell headCell2 = headRow.createCell(2);
        HSSFCell headCell3 = headRow.createCell(3);
        HSSFCell headCell4 = headRow.createCell(4);
        HSSFCell headCell5 = headRow.createCell(5);
        HSSFCell headCell6 = headRow.createCell(6);

        headCell0.setCellValue("序号");
        headCell1.setCellValue("名称");
        headCell2.setCellValue("规格");
        headCell3.setCellValue("品牌");
        headCell4.setCellValue("单价（元）");
        headCell5.setCellValue("数量（盒）");
        headCell6.setCellValue("金额（元）");

        headCell0.setCellStyle(style);
        headCell1.setCellStyle(style);
        headCell2.setCellStyle(style);
        headCell3.setCellStyle(style);
        headCell4.setCellStyle(style);
        headCell5.setCellStyle(style);
        headCell6.setCellStyle(style);

 // 遍历上面数据库查到的数据
        //序号
        int x = 1;
        for (StockRequisitionGoodsVO pm : stockRequisitionGoodsVOList) {
            //填充数据
            HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            dataRow.setHeight((short)450);//目的是想把行高设置成25px

            //看你实体类在进行填充
            HSSFCell dataCell0 = dataRow.createCell(0);
            HSSFCell dataCell1 = dataRow.createCell(1);
            HSSFCell dataCell2 = dataRow.createCell(2);
            HSSFCell dataCell3 = dataRow.createCell(3);
            HSSFCell dataCell4 = dataRow.createCell(4);
            HSSFCell dataCell5 = dataRow.createCell(5);
            HSSFCell dataCell6 = dataRow.createCell(6);

            //序号
            dataCell0.setCellValue(x);
            dataCell1.setCellValue("刀片");
            dataCell2.setCellValue(pm.getGoodsModel());
            dataCell3.setCellValue(pm.getBrand());
            dataCell4.setCellValue(pm.getPrice());
            dataCell5.setCellValue(pm.getTotalNum());
            dataCell6.setCellValue(pm.getGoodsAmount().doubleValue());
            x++;

            dataCell0.setCellStyle(style);
            dataCell1.setCellStyle(style);
            dataCell2.setCellStyle(style);
            dataCell3.setCellStyle(style);
            dataCell4.setCellStyle(stylePoint);
            dataCell5.setCellStyle(style);
            dataCell6.setCellStyle(stylePoint);
        }
           

5.导出

//准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //这后面可以设置导出Excel的名称，此例中名为student.xls
        String fileName = "对账单-" + billPO.getFactoryPO().getFactoryName() + ".xls";
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

        //刷新缓冲
        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载
        workbook.write(response.getOutputStream());