POI通过读取Excel模板生成Excel文件

最新推荐文章于 2022-11-20 16:12:13 发布

guchenfly

最新推荐文章于 2022-11-20 16:12:13 发布

阅读量175

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/guchenfly/article/details/84117514

本文介绍了一个批量导出订单列表的功能，并通过Java实现将导出的数据转换为Excel文件的过程。包括数据筛选、读取模板Excel、创建新Excel文件、添加数据到工作表等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/**  
     * 导出  
     * @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) 10);   
        //设置字体名字;   
        font.setFontName("宋体");   
        //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;   
    }