POI数据导入Excel

在我们开发web项目的时候 避免不了的就是报表的导出，现在我就分享一下我的POI导入到Excel的具体实现操作：*声明：其中含有自己的业务逻辑，已经标注其中，自己根据需求查看。发现问题，遇到问题可以直接指出！希望在此可以帮助大家，给大家提供便利，让大家共同进步~*1.首先下载POI相关的几个jar包：(http://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包地址描述](http://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();    }   }

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