spring读取、写入、后台接收、前后台导出excel。

引入

  1. <!-- poi office -->
  2. <dependency>
  3.     <groupId>org.apache.poi</groupId>
  4.     <artifactId>poi</artifactId>
  5.     <version>3.16</version>
  6. </dependency>
  7. <dependency>
  8.     <groupId>org.apache.poi</groupId>
  9.     <artifactId>poi-ooxml</artifactId>
  10.     <version>3.16</version>
  11. </dependency>
  12. <dependency>
  13.     <groupId>org.apache.poi</groupId>
  14.     <artifactId>poi-ooxml-schemas</artifactId>
  15.     <version>3.16</version>
  16. </dependency>

读取

  1. package excelHelper;
  3. import org.apache.poi.ss.usermodel.*;
  5. import java.io.File;
  6. import java.io.FileInputStream;
  7. import java.io.InputStream;
  9. public class ReadExcel {
  10.     public static void main(String[] args) throws Exception {
  11.         InputStream is = new FileInputStream(new File("d://测试.xls"));
  12.         //根据输入流创建Workbook对象
  13.         Workbook wb = WorkbookFactory.create(is);
  14.         //get到Sheet对象
  15.         Sheet sheet = wb.getSheetAt(0);
  16.         //这个必须用接口
  17.         for(Row row : sheet){
  18.             for(Cell cell : row){
  19.                 //cell.getCellType是获得cell里面保存的值的type
  20.                 //如Cell.CELL_TYPE_STRING
  21.                 switch(cell.getCellTypeEnum()){
  22.                     case BOOLEAN:
  23.                         //得到Boolean对象的方法
  24.                         System.out.print(cell.getBooleanCellValue()+" ");
  25.                         break;
  26.                     case NUMERIC:
  27.                         //先看是否是日期格式
  28.                         if(DateUtil.isCellDateFormatted(cell)){
  29.                             //读取日期格式
  30.                             System.out.print(cell.getDateCellValue()+" ");
  31.                         }else{
  32.                             //读取数字
  33.                             System.out.print(cell.getNumericCellValue()+" ");
  34.                         }
  35.                         break;
  36.                     case FORMULA:
  37.                         //读取公式
  38.                         System.out.print(cell.getCellFormula()+" ");
  39.                         break;
  40.                     case STRING:
  41.                         //读取String
  42.                         System.out.print(cell.getRichStringCellValue().toString()+" ");
  43.                         break;
  44.                 }
  45.             }
  46.             System.out.println("");
  47.         }
  48.     }
  49. }

写入

  1. public class SummaryHSSF {
  3.     public static void main(String[] args) throws IOException {
  4.         //创建Workbook对象（这一个对象代表着对应的一个Excel文件）
  5.         //HSSFWorkbook表示以xls为后缀名的文件
  6.         Workbook wb = new XSSFWorkbook();
  7.         //Workbook wb2 = new HSSFWorkbook();//如果是xls文件，就用这个
  8.         //获得CreationHelper对象,这个应该是一个帮助类
  9.         CreationHelper helper = wb.getCreationHelper();
  10.         //创建Sheet并给名字(表示Excel的一个Sheet)
  11.         Sheet sheet1 = wb.createSheet("HSSF_Sheet_1");
  12.         Sheet sheet2 = wb.createSheet("HSSF_Sheet_2");
  13.         //Row表示一行Cell表示一列
  14.         Row row = null;
  15.         Cell cell = null;
  16.         for(int i=0;i<60;i=i+2){
  17.             //获得这个sheet的第i行
  18.             row = sheet1.createRow(i);
  19.             //设置行长度自动
  20.             //row.setHeight((short)500);
  21.             row.setHeightInPoints(20);
  22.             //row.setZeroHeight(true);
  23.             for(int j=0;j<25;j++){
  24.                 //设置每个sheet每一行的宽度,自动,根据需求自行确定
  25.                 sheet1.autoSizeColumn(j+1, true);
  26.                 //创建一个基本的样式
  27.                 CellStyle cellStyle = SummaryHSSF.createStyleCell(wb);
  28.                 //获得这一行的每j列
  29.                 cell = row.createCell(j);
  30.                 if(j==0){
  31.                     //设置文字在单元格里面的位置
  32.                     cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
  33.                     //先创建字体样式,并把这个样式加到单元格的字体里面
  34.                     cellStyle.setFont(createFonts(wb));
  35.                     //把这个样式加到单元格里面
  36.                     cell.setCellStyle(cellStyle);
  37.                     //给单元格设值
  38.                     cell.setCellValue(true);
  39.                 }else if(j==1){
  40.                     //设置文字在单元格里面的位置
  41.                     cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
  42.                     //设置这个样式的格式(Format)
  43.                     cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "#,##0.0000");
  44.                     //先创建字体样式,并把这个样式加到单元格的字体里面
  45.                     cellStyle.setFont(createFonts(wb));
  46.                     //把这个样式加到单元格里面
  47.                     cell.setCellStyle(cellStyle);
  48.                     //给单元格设值
  49.                     cell.setCellValue(new Double(2008.2008));
  50.                 }else if(j==2){
  51.                     cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
  52.                     cellStyle.setFont(createFonts(wb));
  53.                     cell.setCellStyle(cellStyle);
  54.                     cell.setCellValue(helper.createRichTextString("RichString"+i+j));
  55.                 }else if(j==3){
  56.                     cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
  57.                     cellStyle = SummaryHSSF.setCellFormat(helper,cellStyle, "MM-yyyy-dd");
  58.                     cell.setCellStyle(cellStyle);
  59.                     cell.setCellValue(new Date());
  60.                 }else if(j==24){
  61.                     cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
  62.                     cellStyle.setFont(createFonts(wb));
  63.                     //设置公式
  64.                     cell.setCellFormula("SUM(E"+(i+1)+":X"+(i+1)+")");
  65.                 }else{
  66.                     cellStyle = SummaryHSSF.setCellStyleAlignment(cellStyle, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
  67.                     cellStyle = SummaryHSSF.setFillBackgroundColors(cellStyle,IndexedColors.ORANGE.getIndex(),IndexedColors.ORANGE.getIndex(),FillPatternType.SOLID_FOREGROUND);
  68.                     cell.setCellStyle(cellStyle);
  69.                     cell.setCellValue(1);
  70.                 }
  71.             }
  72.         }
  73.         //输出x
  74.         OutputStream os = new FileOutputStream(new File("d://测试.xlsx"));
  75.         wb.write(os);
  76.         os.close();
  77.     }
  78.     /**
  79.      * 边框
  80.      * @param wb
  81.      * @return
  82.      */
  83.     public static CellStyle createStyleCell(Workbook wb){
  84.         CellStyle cellStyle = wb.createCellStyle();
  85.         //设置一个单元格边框颜色
  86.         cellStyle.setBorderBottom(BorderStyle.THIN);
  87.         cellStyle.setBorderTop(BorderStyle.THIN);
  88.         cellStyle.setBorderLeft(BorderStyle.THIN);
  89.         cellStyle.setBorderRight(BorderStyle.THIN);
  90.         //设置一个单元格边框颜色
  91.         cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
  92.         cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
  93.         cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
  94.         cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
  95.         return cellStyle;
  96.     }
  97.     /**
  98.      * 设置文字在单元格里面的位置
  99.      * CellStyle.ALIGN_CENTER
  100.      * CellStyle.VERTICAL_CENTER
  101.      * @param cellStyle
  102.      * @param halign
  103.      * @param valign
  104.      * @return
  105.      */
  106.     public static CellStyle setCellStyleAlignment(CellStyle cellStyle,HorizontalAlignment halign,VerticalAlignment valign){
  107.         //设置上下
  108.         cellStyle.setAlignment(HorizontalAlignment.GENERAL);
  109.         //设置左右
  110.         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
  111.         return cellStyle;
  112.     }
  113.     /**
  114.      * 格式化单元格
  115.      * 如#,##0.00,m/d/yy去HSSFDataFormat或XSSFDataFormat里面找
  116.      * @param cellStyle
  117.      * @param fmt
  118.      * @return
  119.      */
  120.     public static CellStyle setCellFormat(CreationHelper helper,CellStyle cellStyle,String fmt){
  121.         //还可以用其它方法创建format
  122.         cellStyle.setDataFormat(helper.createDataFormat().getFormat(fmt));
  123.         return cellStyle;
  124.     }
  125.     /**
  126.      * 前景和背景填充的着色
  127.      * @param cellStyle
  128.      * @param bg IndexedColors.ORANGE.getIndex();
  129.      * @param fg IndexedColors.ORANGE.getIndex();
  130.      * @param fp CellStyle.SOLID_FOREGROUND
  131.      * @return
  132.      */
  133.     public static CellStyle setFillBackgroundColors(CellStyle cellStyle, short bg, short fg, FillPatternType fp){
  134.         cellStyle.setFillBackgroundColor(bg);
  135.         cellStyle.setFillForegroundColor(fg);
  136.         cellStyle.setFillPattern(fp);
  137.         return cellStyle;
  138.     }
  139.     /**
  140.      * 设置字体
  141.      * @param wb
  142.      * @return
  143.      */
  144.     public static Font createFonts(Workbook wb){
  145.         //创建Font对象
  146.         Font font = wb.createFont();
  147.         //设置字体
  148.         font.setFontName("黑体");
  149.         //着色
  150.         font.setColor(IndexedColors.BLUE.getIndex());
  151.         //斜体
  152.         font.setItalic(true);
  153.         //字体大小
  154.         font.setFontHeight((short)300);
  155.         return font;
  156.     }
  157. }

后端接收

  1. @RequestMapping(value = "/import", method = RequestMethod.POST)
  2. public String importLogisticsCcode(@RequestParam("fileName") MultipartFile file) throws Exception {
  3.     // 获取文件名
  4.     String fileName = file.getOriginalFilename();
  5.     if (StringUtils.isEmpty(fileName)){
  6.         return "文件不能为空";
  7.     }
  8.     // 获取文件后缀
  9.     String prefix=fileName.substring(fileName.lastIndexOf("."));
  10.     if (!prefix.toLowerCase().contains("xls") || !prefix.toLowerCase().contains("xlsx") ){
  11.         return "文件格式异常，请上传Excel文件格式";
  12.     }
  13.     // 防止生成的临时文件重复-建议使用UUID
  14.     final File excelFile = File.createTempFile(System.currentTimeMillis()+"", prefix);
  15.     file.transferTo(excelFile);
  17.     //由于2003和2007的版本所使用的接口不一样，所以这里统一用Workbook做兼容
  18.     boolean isExcel2003 = prefix.toLowerCase().endsWith("xls")?true:false;
  19.     Workbook workbook = null;
  20.     if(isExcel2003){
  21.         workbook = new HSSFWorkbook(new FileInputStream(excelFile));
  22.     }else{
  23.         workbook = new XSSFWorkbook(new FileInputStream(excelFile));
  24.     }
  25.     //Excel表中的内容
  26.     List<Map<String,Object>> list = new ArrayList<>();
  27.     Sheet sheet = workbook.getSheetAt(0);
  28.     //这里重1开始，跳过了标题，直接从第二行开始解析
  29.     for(int i=1; i<sheet.getLastRowNum()+1; i++) {
  30.         Row row =sheet.getRow(i);
  31.         //设置行格式和验证start 这里最好做成一个方法，免得代码多处复制
  32.         if(row.getCell(0)!=null){
  33.             row.getCell(0).setCellType(CellType.STRING);
  34.         }
  35.         if(row.getCell(1)!=null){
  36.             row.getCell(1).setCellType(CellType.STRING);
  37.         }
  38.         if(row.getCell(2)!=null){
  39.             row.getCell(2).setCellType(CellType.STRING);
  40.         }
  41.         String orderCode = row.getCell(0).getStringCellValue(); //订单编号
  42.         if (StringUtils.isEmpty(orderCode)){
  43.             return "第" + i+"行，第1列不能为空!";
  44.         }
  46.         String logisticsCompanyCode = row.getCell(1).getStringCellValue();
  47.         if (StringUtils.isEmpty(logisticsCompanyCode)){
  48.             return "第" + i+"行，第2列不能为空!";
  49.         }
  50.         String logisticsCode = row.getCell(2).getStringCellValue();
  51.         if (StringUtils.isEmpty(logisticsCode)){
  52.             return "第" + i+"行，第3列不能为空!";
  53.         }
  54.         //设置行格式和验证end
  56.         //组装列表
  57.         Map<String,Object> map = new HashMap<>();
  58.         map.put("orderCode",orderCode);
  59.         map.put("logisticsCompanyCode",logisticsCompanyCode);
  60.         map.put("logisticsCode",logisticsCode);
  61.         list.add(map);
  62.     }
  63.     //删除临时转换的文件
  64.     if (excelFile.exists()) {
  65.         excelFile.delete();
  66.     }
  67.     //list就是具体内容，剩下的就是自己处理具体业务了
  68.     System.out.println("上传的内容就是这个了：" + list);
  69. }

前后端导出

controller设置header格式

  1. @GetMapping("/export")
  2. public void export(Long[] ids,HttpServletResponse response) throws IOException {
  3.     BufferedInputStream bis = null;
  4.     BufferedOutputStream bos = null;
  5.     try {
  6.         List<Order>     order=orderService.findList(ids);
  7.         Date currentTime = new Date();
  8.         SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  9.         String dataTime=formatter.format(currentTime);
  10.         //获取收货单的字节数组
  11.         byte[] content = orderService.exportOrders(order);
  12.         InputStream is = new ByteArrayInputStream(content);
  13.         String fileName = dataTime+"发货单";
  14.         //页面弹层下载
  15.         response.reset();
  16.         response.setContentType("application/vnd.ms-excel;charset=utf-8");
  17.         response.setHeader("Content-Disposition", "attachment;filename="
  18.                 + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
  19.         ServletOutputStream out = response.getOutputStream();
  20.         //流输出
  21.         bis = new BufferedInputStream(is);
  22.         bos = new BufferedOutputStream(out);
  23.         byte[] buff = new byte[2048];
  24.         int bytesRead;
  25.         while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
  26.             out.write(buff, 0, bytesRead);
  27.         }
  28.     } catch (Exception e) {
  29.         e.printStackTrace();
  30.     }finally {
  31.         //关流
  32.         if (bis != null)
  33.             bis.close();
  34.         if (bos != null)
  35.             bos.close();
  37.     }
  38. }

service获取并设置excel的所有内容

  1. public byte[] exportOrders(List<Order> orders) {
  2.     ByteArrayOutputStream os = new ByteArrayOutputStream();
  3.     try {
  4.         Date currentTime = new Date();
  5.         SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
  6.         String dataTime=formatter.format(currentTime);
  7.         HSSFWorkbook workbook = new HSSFWorkbook();
  8.         HSSFSheet sheet = workbook.createSheet("发货单");
  9.         //设置字体
  10.         HSSFFont font = workbook.createFont();
  11.         font.setFontHeightInPoints((short)11); //字体高度
  12.         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体颜色
  13.         font.setFontName("宋体"); //字体
  14.         //生成标题样式
  15.         HSSFCellStyle style = workbook.createCellStyle();
  16.         style.setFont(font);
  17.         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  18.         style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  19.         //创建第一行
  20.         HSSFRow row = sheet.createRow(0);
  21.         //设置行高
  22.         row.setHeight((short)(30*20));
  23.         HSSFCell cell = row.createCell(0);
  24.         //设置大标题
  25.         cell.setCellValue(dataTime+"发货单");
  26.         cell.setCellStyle(style);
  27.         row = sheet.createRow( 1);
  28.         row.setHeight((short)(25*20));
  29.         //设置小标题
  30.         cell = row.createCell(0);
  31.         cell.setCellValue("序号");
  32.         cell.setCellStyle(style);
  33.         cell = row.createCell(1);
  34.         cell.setCellValue("订单编号");
  35.         cell.setCellStyle(style);
  36.         cell = row.createCell(2);
  37.         cell.setCellValue("订单创建日期");
  38.         cell.setCellStyle(style);
  39.         cell = row.createCell(3);
  40.         cell.setCellValue("会员用户名");
  41.         cell.setCellStyle(style);
  42.         cell = row.createCell(4);
  43.         cell.setCellValue("商品名称");
  44.         cell.setCellStyle(style);
  45.         cell = row.createCell(5);
  46.         cell.setCellValue("数量");
  47.         cell.setCellStyle(style);
  48.         cell = row.createCell(6);
  49.         cell.setCellValue("单位");
  50.         cell.setCellStyle(style);
  51.         cell = row.createCell(7);
  52.         cell.setCellValue("规格");
  53.         cell.setCellStyle(style);
  54.         cell = row.createCell(8);
  55.         cell.setCellValue("商品分类");
  56.         cell.setCellStyle(style);
  57.         cell = row.createCell(9);
  58.         cell.setCellValue("是否组合支付");
  59.         cell.setCellStyle(style);
  60.         cell = row.createCell(10);
  61.         cell.setCellValue("人民币");
  62.         cell.setCellStyle(style);
  63.         cell = row.createCell(11);
  64.         cell.setCellValue("积分类型");
  65.         cell.setCellStyle(style);
  66.         cell = row.createCell(12);
  67.         cell.setCellValue("积分");
  68.         cell.setCellStyle(style);
  69.         cell = row.createCell(13);
  70.         cell.setCellValue("收件人");
  71.         cell.setCellStyle(style);
  72.         cell = row.createCell(14);
  73.         cell.setCellValue("收货手机号");
  74.         cell.setCellStyle(style);
  75.         cell = row.createCell(15);
  76.         cell.setCellValue("收货人地址");
  77.         cell.setCellStyle(style);
  78.         cell = row.createCell(16);
  79.         cell.setCellValue("邮编");
  80.         cell.setCellStyle(style);
  81.         cell = row.createCell(17);
  82.         cell.setCellValue("状态");
  83.         cell.setCellStyle(style);
  84.         cell = row.createCell(18);
  85.         cell.setCellValue("快递公司");
  86.         cell.setCellStyle(style);
  87.         cell = row.createCell(19);
  88.         cell.setCellValue("快递单号");
  89.         cell.setCellStyle(style);
  90.         // 合并单元格
  91.         CellRangeAddress cra =new CellRangeAddress(0, 0, 0, 19); // 起始行, 终止行, 起始列, 终止列
  92.         //CellRangeAddress cra1 =new CellRangeAddress(1, 1, 9, 10); // 起始行, 终止行, 起始列, 终止列
  93.         sheet.addMergedRegion(cra);
  94.         //sheet.addMergedRegion(cra1);
  95.         //设置列宽
  96.         sheet.setColumnWidth(0,6*256);
  97.         sheet.setColumnWidth(1,15*256);
  98.         sheet.setColumnWidth(2,21*256);
  99.         sheet.setColumnWidth(3,14*256);
  100.         sheet.setColumnWidth(4,11*256);
  101.         sheet.setColumnWidth(5,6*256);
  102.         sheet.setColumnWidth(6,6*256);
  103.         sheet.setColumnWidth(7,13*256);
  104.         sheet.setColumnWidth(8,13*256);
  105.         sheet.setColumnWidth(9,13*256);
  106.         sheet.setColumnWidth(10,11*256);
  107.         sheet.setColumnWidth(11,11*256);
  108.         sheet.setColumnWidth(12,11*256);
  109.         sheet.setColumnWidth(13,9*256);
  110.         sheet.setColumnWidth(14,14*256);
  111.         sheet.setColumnWidth(15,59*256);
  112.         sheet.setColumnWidth(16,7*256);
  113.         sheet.setColumnWidth(17,11*256);
  114.         sheet.setColumnWidth(18,11*256);
  115.         sheet.setColumnWidth(19,11*256);
  116.         int rowNumber=0;
  117.         //向单元格里填充数据
  118.         for(int i=0;i<orders.size();i++){
  119.             List<OrderItem> orderItems=orderItemService.getMerByOrderId(orders.get(i));
  120.             for(int j=0; j<orderItems.size();j++){
  121.                 row=sheet.createRow( 2+rowNumber);
  122.                 row.setHeight((short)(20*20));
  123.                 row.createCell(0).setCellValue(1+rowNumber);
  124.                 row.createCell(1).setCellValue(orders.get(i).getSn());
  125.                 row.createCell(2).setCellValue(orders.get(i).getCreatedDate().toString());
  126.                 row.createCell(3).setCellValue(orders.get(i).getMember().getUsername());
  127.                 if(orderItems.get(j).getName().equals("茅台集团天朝上品1箱")){
  128.                     row.createCell(4).setCellValue("茅台集团天朝上品贵人酒");
  129.                     row.createCell(5).setCellValue(1*orderItems.get(j).getQuantity());
  130.                 }else if(orderItems.get(j).getName().equals("茅台集团天朝上品3箱")){
  131.                     row.createCell(4).setCellValue("茅台集团天朝上品贵人酒");
  132.                     row.createCell(5).setCellValue(3*orderItems.get(j).getQuantity());
  133.                 }else if(orderItems.get(j).getName().equals("茅台集团天朝上品5箱")){
  134.                     row.createCell(4).setCellValue("茅台集团天朝上品贵人酒");
  135.                     row.createCell(5).setCellValue(5*orderItems.get(j).getQuantity());
  136.                 }else if(orderItems.get(j).getName().equals("茅台集团天朝上品10箱")){
  137.                     row.createCell(4).setCellValue("茅台集团天朝上品贵人酒");
  138.                     row.createCell(5).setCellValue(10*orderItems.get(j).getQuantity());
  139.                 }else{
  140.                     row.createCell(4).setCellValue(orderItems.get(j).getName());
  141.                     row.createCell(5).setCellValue(orderItems.get(j).getQuantity());
  142.                 }
  143.                     row.createCell(6).setCellValue(orderItems.get(j).getSku().getProduct().getUnit());
  144.                 //规格
  145.                 List<SpecificationValue> specificationValues = orderItems.get(j).getSku().getSpecificationValues();
  146.                 if(!specificationValues.isEmpty()){
  147.                     StringBuilder sb= new StringBuilder();
  148.                     for(SpecificationValue specificationValue :specificationValues){
  149.                         sb.append(" ").append(specificationValue.getValue());
  150.                     }
  151.                     row.createCell(7).setCellValue(sb.toString());
  152.                 }else{
  153.                     row.createCell(7).setCellValue("");
  154.                 }
  155.                 //商品分类
  156.                 row.createCell(8).setCellValue(orderItems.get(j).getSku().getProduct().getProductCategory().getName());
  157.                 //组合支付
  158.                 Order.payType isExchangePoint = orderItems.get(j).getOrder().getIsExchangePoint();
  159.                 if(isExchangePoint.ordinal()==2){
  160.                     row.createCell(9).setCellValue("是");
  161.                 }else{
  162.                     row.createCell(9).setCellValue("否");
  163.                 }
  164.                 row.createCell(10).setCellValue(orders.get(i).getPrice().doubleValue());//人民币
  165.                 String currencyCode = orders.get(i).getCurrency().getCurrencyCode();
  166.                 if(!"CNY".equals(currencyCode)){
  167.                 row.createCell(11).setCellValue(currencyCode);//积分类型
  168.                 }
  169.                 row.createCell(12).setCellValue(orders.get(i).getExchangePoint().doubleValue());//积分
  170.                 row.createCell(13).setCellValue(orders.get(i).getConsignee());
  171.                 row.createCell(14).setCellValue(orders.get(i).getPhone());
  172.                 row.createCell(15).setCellValue(orders.get(i).getAreaName()+orders.get(i).getAddress());
  173.                 row.createCell(16).setCellValue(orders.get(i).getZipCode());
  174.                 row.createCell(17).setCellValue(getStart(orders.get(i).getStatus().toString()));
  175.                 rowNumber++;
  176.             }
  177.         }
  178.         workbook.write(os);
  179.     } catch (IOException e) {
  180.         e.printStackTrace();
  181.     }
  182.     byte[] content = os.toByteArray();
  183.     return content;
  184. }