/**
   * @param newSheet the sheet to create from the copy.
   * @param sheet the sheet to copy.
   * @param copyStyle true copy the style.
   */
  public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap =
        (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
      XSSFRow srcRow = sheet.getRow(i);
      XSSFRow destRow = newSheet.createRow(i);
      if (srcRow != null) {
        XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
        if (srcRow.getLastCellNum() > maxColumnNum) {
          maxColumnNum = srcRow.getLastCellNum();
        }
      }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
      newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
  }
Ejemplo n.º 2
0
 private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
   XSSFWorkbook wb = null;
   try {
     if (isXssf) {
       XSSFWorkbook w = new XSSFWorkbook();
     } else {
       HSSFWorkbook w = new HSSFWorkbook();
     }
     wb = new XSSFWorkbook();
     XSSFDataFormat format = wb.createDataFormat();
     XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
     XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
     XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
     XSSFDataFormat passwordFormat = wb.createDataFormat();
     passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
     List<ExcelHeader> headers = getHeaderList(clz);
     Collections.sort(headers);
     sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
     Row r0 = sheet.createRow(0);
     Cell cell = r0.createCell(0);
     r0.setHeightInPoints(28);
     cell.setCellValue(message + "备份记录");
     Row r = sheet.createRow(1);
     r.setHeightInPoints(25);
     cell.setCellStyle(cellStyle);
     // 输出标题
     for (int i = 0; i < headers.size(); i++) {
       Cell cell1 = r.createCell(i);
       if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
       else cell1.setCellStyle(cellStyle);
       cell1.setCellValue(headers.get(i).getTitle());
     }
     Object obj = null;
     // 输出用户资料信息
     if (message.indexOf("用户资料 ") > 0) {
       sheet.setColumnWidth(3, 32 * 150);
       sheet.setColumnWidth(4, 32 * 110);
       sheet.setColumnWidth(7, 32 * 120);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           if (getMethodName(headers.get(j)).equals("nabled"))
             cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
           else if (getMethodName(headers.get(j)).equals("password")) {
             cell2.setCellStyle(passwordCellStyle);
             cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
           } else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 输出房间使用信息数据
     else {
       sheet.setColumnWidth(0, 32 * 80);
       sheet.setColumnWidth(2, 32 * 100);
       sheet.setColumnWidth(3, 32 * 190);
       sheet.setColumnWidth(4, 32 * 190);
       sheet.setColumnWidth(5, 32 * 190);
       sheet.setColumnWidth(10, 32 * 130);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           if (j == 3 || j == 4 || j == 5) {
             XSSFCellStyle cs3 = wb.createCellStyle();
             cell2.setCellValue(new Date());
             copyDefaultCellStyle(format, cell2, cs3, 1);
           }
           if (j == 10) {
             XSSFCellStyle cs2 = wb.createCellStyle();
             copyDefaultCellStyle(format, cell2, cs2, 2);
           }
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 设置行列的默认宽度和高度
   } catch (IllegalAccessException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvocationTargetException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (NoSuchMethodException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return wb;
 }
Ejemplo n.º 3
0
 @Override
 public Workbook writeWorkbook(
     Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) {
   if (workbook == null) workbook = new XSSFWorkbook();
   int exportRecordNum = tableDataList.size(); // 导出的总记录数
   int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数
   int sheetNum =
       exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum)
           + 1; // 工作表的页数
   List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>();
   if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet";
   for (int i = 0; i < sheetNum; i++) {
     // 在Excel工作簿中建一工作表
     String sTitle = sheetTitle + (i + 1);
     XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle);
     sheet.setSelected(true); // 设置工作薄为选中
     sheet.setAutobreaks(true);
     sheet.setPrintGridlines(true);
     sheetList.add(sheet);
   }
   /** ***********************输出表头********************************* */
   if (headerNum > 0) {
     for (XSSFSheet sheet : sheetList) {
       XSSFRow headRow = sheet.createRow(0);
       headRow.setHeightInPoints(20);
       for (int i = 0; i < headerNum; i++) {
         List<String> headerRowDataList = tableDataList.get(i);
         for (int j = 0; j < headerRowDataList.size(); j++) {
           XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式
           createCell(headRow, j, headerRowDataList.get(j), cellStyle);
         }
       }
       // 固定表头
       sheet.createFreezePane(0, 1);
     }
   }
   /** *********************输出表体内容************************* */
   // 设置列样式
   XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle();
   columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
   columnStyle.setWrapText(true);
   if (tableDataList.size() > (tableStartRowNum + 1)) {
     for (int i = tableStartRowNum; i < exportRecordNum; i++) {
       List<String> rowDataList = tableDataList.get(i);
       XSSFRow row = null;
       int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数
       XSSFSheet sheet = sheetList.get(currentSheet);
       int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet;
       row = sheet.createRow(rowIndex);
       for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) {
         createCell(row, colIndex, rowDataList.get(colIndex));
       }
     }
   }
   // 调整列的宽度(取第一列为基准)
   for (XSSFSheet sheet : sheetList) {
     for (int i = 0; i < tableDataList.get(0).size(); i++) {
       sheet.autoSizeColumn((short) i);
       sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000));
     }
   }
   return workbook;
 }
Ejemplo n.º 4
0
 /**
  * Set the width (in units of 1/256th of a character width)
  *
  * <p>The maximum column width for an individual cell is 255 characters. This value represents the
  * number of characters that can be displayed in a cell that is formatted with the standard font.
  *
  * @param columnIndex - the column to set (0-based)
  * @param width - the width in units of 1/256th of a character width
  */
 public void setColumnWidth(int columnIndex, int width) {
   _sh.setColumnWidth(columnIndex, width);
 }
Ejemplo n.º 5
0
  public static boolean writeMultArray2XLSXFile(
      String fileName,
      List<List<String[]>> arrys,
      List<String> sheetLabels,
      boolean hasHead,
      int indexKey)
      throws Exception {

    if (arrys.isEmpty()) {
      System.err.println("No input data!");
      return false;
    }

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom font to the text in the comment
    XSSFFont font = wb.createFont();

    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    XSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    XSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    String lastKey = null;
    int switcher = -1;
    XSSFCell cell = null;
    String[] titleNames = null;
    int d = 0;
    for (List<String[]> arry : arrys) {
      XSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
      if (hasHead) {
        titleNames = (String[]) arry.get(0);
      }
      int columnNum = ((String[]) arry.get(0)).length;

      for (int i = 0; i < columnNum; i++) {
        sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
      }

      int rowIndex = 0;
      // create titile row
      XSSFRow row = sheet1.createRow(rowIndex);

      if (titleNames != null) {
        for (int i = 0; i < columnNum; i++) {
          cell = row.createCell(i);
          cell.setCellValue(titleNames[i]);
          cell.setCellStyle(headStyle);
        }
        rowIndex++;
      }
      int rowNum = arry.size();

      for (int i = rowIndex; i < rowNum; i++) {
        row = sheet1.createRow((i));
        String[] line = (String[]) arry.get(i);
        columnNum = line.length;
        if (indexKey >= 0) {
          if (lastKey == null && line[indexKey] != null) {
            lastKey = line[indexKey];
            switcher *= -1;
          } else if (lastKey != null && line[indexKey] == null) {
            lastKey = line[indexKey];
            switcher *= -1;
          } else if (lastKey == null && line[indexKey] == null) {
          } else {
            if (!lastKey.equals(line[indexKey])) {
              switcher *= -1;
              lastKey = line[indexKey];
            }
          }
        } else {
          switcher = 1;
        }
        for (int j = 0; j < columnNum; j++) {
          cell = row.createCell(j);
          if (switcher > 0) {
            cell.setCellStyle(bodyStyle);
          } else {
            cell.setCellStyle(markedBodyStyle);
          }

          if (line[j] != null) {
            if (Util.isNumeric(line[j])) {
              // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
              cell.setCellType(0);
              cell.setCellValue(Double.parseDouble(line[j]));
            } else {
              cell.setCellValue(line[j]);
            }
          } else {
            cell.setCellValue(".");
          }
        }
      }

      d++;
    }

    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(fileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Ejemplo n.º 6
0
  public static boolean convertTextFile2XLSXFile(
      String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception {
    BufferedReader br = LocalFileFunc.getBufferedReader(inFileName);
    String line = br.readLine();
    if (line == null) {
      return false;
    }
    String[] cells1 = Util.tokenize(line, '\t');
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet("Data");

    int columnNum = cells1.length;
    for (int i = 0; i < columnNum; i++) {
      sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
    }

    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom font to the text in the comment
    XSSFFont font = wb.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    XSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    XSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    int rowIndex = 0;
    // create titile row
    XSSFRow row = sheet1.createRow(rowIndex);

    String lastKey = null;
    int switcher = -1;
    XSSFCell cell = null;
    if (hasHead) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(cells1[i]);
        cell.setCellStyle(headStyle);
      }
    } else {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(cells1[i]);
        cell.setCellStyle(bodyStyle);
      }
    }
    rowIndex++;
    while ((line = br.readLine()) != null) {
      cells1 = Util.tokenize(line, '\t');
      row = sheet1.createRow((rowIndex));
      columnNum = cells1.length;
      if (indexKey >= 0) {
        if (lastKey == null && cells1[indexKey] != null) {
          lastKey = cells1[indexKey];
          switcher *= -1;
        } else if (lastKey != null && cells1[indexKey] == null) {
          lastKey = cells1[indexKey];
          switcher *= -1;
        } else if (lastKey == null && cells1[indexKey] == null) {
        } else {
          if (!lastKey.equals(cells1[indexKey])) {
            switcher *= -1;
            lastKey = cells1[indexKey];
          }
        }
      } else {
        switcher = 1;
      }
      // System.out.println(cells1[0]);
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (switcher > 0) {
          cell.setCellStyle(bodyStyle);
        } else {
          cell.setCellStyle(markedBodyStyle);
        }

        if (cells1[j] != null) {
          if (Util.isNumeric(cells1[j])) {
            // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(cells1[j]));
          } else {
            cell.setCellValue(cells1[j]);
          }
        } else {
          cell.setCellValue(".");
        }
      }
      rowIndex++;
    }
    br.close();
    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(outFileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Ejemplo n.º 7
0
  public static boolean writeArray2XLSXSheet(
      XSSFSheet sheet1, XSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
    int rowNum = arry.size();
    if (rowNum == 0) {
      System.err.println("No input data!");
      return false;
    }

    String[] titleNames = null;
    if (hasHead) {
      titleNames = (String[]) arry.get(0);
    }
    int columnNum = ((String[]) arry.get(0)).length;

    for (int i = 0; i < columnNum; i++) {
      sheet1.setColumnWidth((short) i, (short) ((30 * 8) / ((double) 1 / 20)));
    }

    XSSFFont font = wb.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    XSSFCellStyle headStyle = wb.createCellStyle();
    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    XSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

    XSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    markedBodyStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

    int rowIndex = 0;
    // create titile row
    XSSFRow row = sheet1.createRow(rowIndex);

    XSSFCell cell = null;
    if (titleNames != null) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell((short) i);
        cell.setCellValue(titleNames[i]);
        cell.setCellStyle(headStyle);
      }
      rowIndex++;
    }

    for (int i = rowIndex; i < rowNum; i++) {
      row = sheet1.createRow((i));
      String[] line = (String[]) arry.get(i);
      columnNum = line.length;
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (line[0] != null) {
          cell.setCellStyle(markedBodyStyle);
        } else {
          cell.setCellStyle(bodyStyle);
        }
        if (line[j] != null) {
          if (Util.isNumeric(line[j])) {
            // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(line[j]));
          } else {
            cell.setCellValue(line[j]);
          }
        } else {
          cell.setCellValue("");
        }
      }
    }
    return true;
  }
Ejemplo n.º 8
0
 @Override
 public byte[] getImportTemplate(Boolean editProductType) throws IOException {
   XSSFWorkbook workBook = new XSSFWorkbook();
   XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME);
   XSSFSheet typeSheet = createTypeSheet(workBook);
   if (!(true == editProductType)) {
     workBook.setSheetHidden(
         workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);
   }
   XSSFRow titleRow = productSheet.createRow(0);
   List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting();
   XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
   for (int i = 0; i < transSettings.size(); i++) {
     TransformSetting setting = transSettings.get(i);
     XSSFCell titleCell = null;
     titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING);
     String cellValue = setting.getDispName();
     titleCell.setCellValue(cellValue);
     productSheet.autoSizeColumn(i, true);
     int columnWidth = productSheet.getColumnWidth(i);
     int needWidth = cellValue.getBytes().length * 256;
     if (columnWidth < needWidth) {
       productSheet.setColumnWidth(i, needWidth);
     }
     if ("productTypeName".equals(setting.getPropertyName())) {
       Name type = workBook.createName();
       String name = "type";
       type.setNameName(name); // 定义名称
       type.setRefersToFormula(
           "OFFSET("
               + typeSheet.getSheetName()
               + "!$A$3,0,0,COUNTA("
               + typeSheet.getSheetName()
               + "!$A:$A)-2)");
       XSSFDataValidationConstraint constraint =
           (XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称
       CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i);
       DataValidation dataValidation = helper.createValidation(constraint, regions);
       productSheet.addValidationData(dataValidation);
     } else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得
       for (int j = 0; j < 10; j++) {
         XSSFRow row = productSheet.createRow(j + 1);
         XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING);
         int cellnum = j + 2;
         itemCell.setCellFormula(
             "IF(A"
                 + cellnum
                 + "=\"\",\"\",VLOOKUP(A"
                 + cellnum
                 + ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))");
       }
     } else if ("costMethod".equals(setting.getPropertyName())) {
       int len = CostMethod.values().length;
       String[] costNames = new String[len];
       for (int ci = 0; ci < len; ci++) {
         costNames[ci] = CostMethod.values()[ci].getMethodName();
       }
       //				XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
       XSSFDataValidationConstraint constraint =
           (XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames);
       CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i);
       XSSFDataValidation validation =
           (XSSFDataValidation) helper.createValidation(constraint, addressList);
       validation.setShowErrorBox(true);
       productSheet.addValidationData(validation);
     }
   }
   workBook.setActiveSheet(workBook.getSheetIndex(productSheet));
   ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
   workBook.write(baos);
   return baos.toByteArray();
 }