Java中使用POI读取Excel模板生成Excel文件的方法

2019-01-19 03:11 阅读 392 次 评论 0 条
/** 
     * 导出 
     * @param mapping 
     * @param form 
     * @param request 
     * @param response 
     * @return 
     */  
    public ActionForward export(ActionMapping mapping, ActionForm form,  
            HttpServletRequest request, HttpServletResponse response) {  
          
        String checkBoxValueList = request.getParameter("ckv");  
        if(!CommonUtil.isNotNullorEmtry(checkBoxValueList)) {  
            this.saveMessages(request, "export.fail");  
            return mapping.findForward("export.fail");  
        }  
        List<Order> listOrder = orderService.exportOrderList(checkBoxValueList);  
          
        try {  
//          SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddhhmm");  
//          Calendar calendar = Calendar.getInstance();  
              
            ServletOutputStream os = response.getOutputStream(); //获得输出流  
            response.reset();   //清空输出流  
            String fileName = new String("订单列表".getBytes("gb2312"), "ISO8859-1") +".xls";  
            response.setHeader("Content-disposition", "attachment; filename="+ fileName); //设定输出文件头  
            response.setContentType("application/msexcel"); //定义输出类型  
  
            String filePath = request.getSession().getServletContext().getRealPath("/excel/model/order.xls");  
  
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath));    //读取excel模板  
            try {  
                HSSFSheet sheet = workbook.getSheetAt(0);   //读取第一个工作簿  
                HSSFRow row;  
                HSSFCell cell = null;  
                int rownum = 3; //添加的起始行  
                HSSFCellStyle style = this.getStyle(workbook);  
                Iterator<Order> it = listOrder.iterator();  
                Order order = null;  
                                  
                while(it.hasNext()) {  
                    order = new Order();  
                    order = it.next();  
                      
                    row = sheet.createRow(rownum);  
                      
                    myCreateCell(1, String.valueOf(rownum-2), row, cell, style);    //列1  
                    myCreateCell(2, order.getAdName(), row, cell, style);   //列2  
                    myCreateCell(3, order.getSmallAreaName(), row, cell, style);    //列1      
                                          
                    rownum++;  
                }  
                                  
                workbook.write(os);  
                os.flush();  
                os.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        return null;  
    }  
      
    private void myCreateCell(int cellnum, String value, HSSFRow row, HSSFCell cell, HSSFCellStyle style) {  
        cell = row.createCell((short) cellnum);  
        cell.setCellValue(new HSSFRichTextString(value));  
        cell.setCellStyle(style);  
    }  
      
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {  
        //设置字体;  
        HSSFFont font = workbook.createFont();  
        //设置字体大小;  
        font.setFontHeightInPoints((short) 5);  
        //设置字体名字;  
        font.setFontName("Courier New");  
        //font.setItalic(true);  
        //font.setStrikeout(true);  
        //设置样式;  
        HSSFCellStyle style = workbook.createCellStyle();  
        //设置底边框;  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        //设置底边框颜色;  
        style.setBottomBorderColor(HSSFColor.BLACK.index);  
        //设置左边框;  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        //设置左边框颜色;  
        style.setLeftBorderColor(HSSFColor.BLACK.index);  
        //设置右边框;  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        //设置右边框颜色;  
        style.setRightBorderColor(HSSFColor.BLACK.index);  
        //设置顶边框;  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        //设置顶边框颜色;  
        style.setTopBorderColor(HSSFColor.BLACK.index);  
        //在样式用应用设置的字体;  
        style.setFont(font);  
        //设置自动换行;  
        style.setWrapText(false);  
        //设置水平对齐的样式为居中对齐;  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        //设置垂直对齐的样式为居中对齐;  
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        return style;  
    }  

版权声明:本文著作权归原作者所有,欢迎分享本文,谢谢支持!
转载请注明:Java中使用POI读取Excel模板生成Excel文件的方法 | 猿笔记
分类:JAVA, 程序笔记 标签:, ,

发表评论


表情