springMVC+poi导出excel

来源：互联网发布：lol全皮肤软件编辑：程序博客网时间：2024/04/24 21:52
//utilpublic class ExportUtil  {      private XSSFWorkbook wb = null;      private XSSFSheet sheet = null;      /**      * @param wb      * @param sheet      */      public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet)      {          this.wb = wb;          this.sheet = sheet;      }      /**      * 合并单元格后给合并后的单元格加边框      *       * @param region      * @param cs      */      public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs)      {          int toprowNum = region.getFirstRow();          for (int i = toprowNum; i <= region.getLastRow(); i++)          {              XSSFRow row = sheet.getRow(i);              for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++)              {                  XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,                                                  // (short) j);                  cell.setCellStyle(cs);              }          }      }      /**      * 设置表头的单元格样式      *       * @return      */      public XSSFCellStyle getHeadStyle()      {          // 创建单元格样式          XSSFCellStyle cellStyle = wb.createCellStyle();          // 设置单元格的背景颜色为淡蓝色          cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);          cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);          // 设置单元格居中对齐          cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);          // 设置单元格垂直居中对齐          cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);          // 创建单元格内容显示不下时自动换行          cellStyle.setWrapText(true);          // 设置单元格字体样式          XSSFFont font = wb.createFont();          // 设置字体加粗          font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);          font.setFontName("宋体");          font.setFontHeight((short) 200);          cellStyle.setFont(font);          // 设置单元格边框为细线条          cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);          return cellStyle;      }      /**      * 设置表体的单元格样式      *       * @return      */      public XSSFCellStyle getBodyStyle()      {          // 创建单元格样式          XSSFCellStyle cellStyle = wb.createCellStyle();          // 设置单元格居中对齐          cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);          // 设置单元格垂直居中对齐          cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);          // 创建单元格内容显示不下时自动换行          cellStyle.setWrapText(true);          // 设置单元格字体样式          XSSFFont font = wb.createFont();          // 设置字体加粗          font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);          font.setFontName("宋体");          font.setFontHeight((short) 200);          cellStyle.setFont(font);          // 设置单元格边框为细线条          cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);          cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);          return cellStyle;      }  }      //service    //导出excel    @SuppressWarnings("unused")    public void exportExcel(Long orderid , String[] titles, ServletOutputStream outputStream)      {          O2oOrder order = this.load(orderid);//根据自己的业务获得数据        List<O2oOrderDetail> list =o2oOrderDetailService.listByOrder(orderid);        // 创建一个workbook 对应一个excel应用文件          XSSFWorkbook workBook = new XSSFWorkbook();          // 在workbook中添加一个sheet,对应Excel文件中的sheet          XSSFSheet sheet = workBook.createSheet("导出excel例子");          ExportUtil exportUtil = new ExportUtil(workBook, sheet);          XSSFCellStyle headStyle = exportUtil.getHeadStyle();          XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();          // 构建表头          XSSFRow headRow = sheet.createRow(0);          XSSFCell cell = null;          for (int i = 0; i < titles.length; i++)          {              cell = headRow.createCell(i);              cell.setCellStyle(headStyle);              cell.setCellValue(titles[i]);              sheet.setColumnWidth(i,  6000);          }          // 构建表体数据          if (list != null && list.size() > 0)          {              for (int j = 0; j < 3; j++)              {                  XSSFRow bodyRow = sheet.createRow(j + 1);                //  O2oOrder goods = list.get(j);                  /*cell = bodyRow.createCell(0);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("001000000000000000000000000000001000000000001");                  cell = bodyRow.createCell(1);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("000000000000000001");                  cell = bodyRow.createCell(2);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("77001");                 cell = bodyRow.createCell(3);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("77001");                 cell = bodyRow.createCell(4);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("77001");                 cell = bodyRow.createCell(5);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("77001");                 cell = bodyRow.createCell(6);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("77001");                 cell = bodyRow.createCell(7);                  cell.setCellStyle(bodyStyle);                  cell.setCellValue("77001"); */           }          }         try          {              workBook.write(outputStream);              outputStream.flush();              outputStream.close();          }          catch (IOException e)          {              e.printStackTrace();          }          finally          {              try              {                  outputStream.close();              }              catch (IOException e)              {                  e.printStackTrace();              }          }      }     //controller      @RequestMapping("/exportExcel")      public String exportExcel(HttpServletResponse response) throws IOException      {          Long orderid =Long.parseLong("3304392850601064");         // 设置response参数，可以打开下载页面        /*response.reset();        response.setContentType("application/vnd.ms-excel;charset=utf-8");        response.setHeader("Content-Disposition", "attachment;filename=" + new String(("888" + ".xlsx").getBytes(), "utf-8"));        */        System.out.println(orderid+"这是订单号");        response.setContentType("application/binary;charset=utf-8");          try          {              ServletOutputStream outputStream = response.getOutputStream();              String fileName = new String(("导出excel例子").getBytes(), "utf-8");              response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");// 组装附件名称和格式              String[] titles = { "手机号", "收货地址", "系统订单号", "下单时间", "商品名称", "商品数量", "快递公司", "快递编号"};              o2oOrderService.exportExcel(orderid,  titles, outputStream);        }          catch (IOException e)          {              e.printStackTrace();          }          return null;      }
1 0