POI数据导入Excel

在我们开发web项目的时候 避免不了的就是报表的导出，现在我就分享一下我的POI导入到Excel的具体实现操作：

*声明：其中含有自己的业务逻辑，已经标注其中，自己根据需求查看。
发现问题，遇到问题可以直接指出！希望在此可以帮助大家，给大家提供便利，让大家共同进步~*

1.首先下载POI相关的几个jar包：
(https://img-blog.csdn.net/20160617081635153)

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

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

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

    <dependency>
       <groupId>stax</groupId>
       <artifactId>stax-api</artifactId>
       <version>1.0.1</version>
    </dependency>

    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>2.6.0</version>
    </dependency>
这里是下载后的jar包!
[jar包地址描述](https://img-blog.csdn.net/20160617191309010)


2.下面是重要的代码：


//设置POI exal文本格式
private HSSFCellStyle createCellStyle(HSSFWorkbook workbookork, short fontSize) {

    //设定样式
    HSSFCellStyle Style = workbookork.createCellStyle();

    //水平居中
    Style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    //垂直居中
    Style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    //创建字体,字体属于工作薄
    HSSFFont font = workbookork.createFont();

    //加粗字体
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    //字体大小
    font.setFontHeightInPoints(fontSize);

    //加载字体
    Style.setFont(font);

    return Style;
}

---

//导出的具体实现步骤
public void exportExcleBefore(ServletOutputStream outputStream,
    HttpServletRequest request,
    @Param("time1")String time1,
    @Param("pageSize")Integer pageSize){
try {
        //我在这里是根据时间段进行的查询 即time1,time2 
        *（自己的业务逻辑在内  参数自己看需求）*
        List<Live> list = dao.searchBefore(time1, time2, (page-1)*pageSize, pageSize);

        //创建一个工作薄
        HSSFWorkbook workbookork = new HSSFWorkbook();

        //创建合并单元格对象   起始行，结束行号，起始列号，结束列号,就是第一行的第一列，到第5列合并起来 
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0,3);

        // 自定义样式，样式属于工作部  
        HSSFCellStyle cellStyl= createCellStyle(workbookork,(short)16);
        HSSFCellStyle cellSty2= createCellStyle(workbookork,(short)13);
        // 列标题样式   创建工作表,属于工作簿的
        HSSFSheet sheet = workbookork.createSheet("宾客的入住记录");

        //加载合并单元格对象
        sheet.addMergedRegion(cellRangeAddress);

        // 创建行
        HSSFRow row1 = sheet.createRow(0);
        //创建头标题行，并且设置头标题
        HSSFCell cell1 = row1.createCell(0);

        cell1.setCellStyle(cellStyl);
        cell1.setCellValue("宾客的入住记录");

        //第二行
        HSSFRow row2 = sheet.createRow(1);

        // 创建列标题行；并且设置标题
        String title[]={"房间号","姓名","性别","证件类型","证件号","所住地址","入住天数","入住时间","终止时间"};
        for (int i = 0; i < title.length; i++) {

            HSSFCell cell2 = row2 .createCell(i);

            cell2.setCellStyle(cellSty2);

            cell2.setCellValue(title[i]); 
        }

        //单元格赋值
        if(list !=null){
            for (int j = 0; j<list.size(); j++) {
                HSSFRow row = sheet.createRow(j+2);

                HSSFCell cell11 = row.createCell(0);
                    cell11.setCellValue(list.get(j).getInfo_id());

                HSSFCell cell12 = row.createCell(1);
                cell12.setCellValue(list.get(j).getName());

                HSSFCell cell13 = row.createCell(2);
                cell13.setCellValue(list.get(j).getSex());

                HSSFCell cell14 = row.createCell(3);
                cell14.setCellValue(list.get(j).getPapers_type());

                HSSFCell cell15 = row.createCell(4);
                cell15.setCellValue(list.get(j).getPapers_no());

                HSSFCell cell16 = row.createCell(5);
                cell16.setCellValue(list.get(j).getAddress());

                HSSFCell cell17 = row.createCell(6);
                cell17.setCellValue(list.get(j).getNumber());

                //时间类型转化
                HSSFCell cell18 = row.createCell(7);
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                String format2 = format.format(list.get(j).getIn_time());
                cell18.setCellValue(format2);

                //时间类型转化
                HSSFCell cell19 = row.createCell(8);
                cell19.setCellValue(list.get(j).getOut_time().toString());
                String format3 = format.format(list.get(j).getOut_time());
                cell19.setCellValue(format3);

                }
                workbookork.write(outputStream);
                workbookork.close();

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

//最后调用这个方法
public void exportExcle(HttpServletResponse response,HttpServletRequest request,String time1,String time2,Integer num) {

    //分页用到的参数
    Integer page = request.getParameter("page") == null ? 1:Integer.valueOf(request.getParameter("page"));
    Integer pageSize= 10;

    try {
        //输出的格式是excell
        response.setContentType("application/x-execl");

        //ISO-8859-1编码转译
        response.setHeader("Content-Disposition","attachment;filename="+new String("用户列表.xls.".getBytes(),"ISO-8859-1"));
        //获取输出流
        ServletOutputStream outputStream = response.getOutputStream();

        //时间赋值为null 进行判断
        *（我自己的业务需求这是，不是POI内容）*
        if("".equals(time1)&&"".equals(time2))

                time1 = null;
                time2 = null;
        }

        //查询数据
        excel.exportExcleLive(outputStream, request, time1, time2, page, pageSize);

        if(outputStream !=null){
            outputStream.close();
        }
    } catch (Exception e) {
            e.printStackTrace();
    }   
}

版权声明：本文为博主原创文章，未经博主允许不得转载。