Beispiel #1
0
 private XSSFSheet createTypeSheet(XSSFWorkbook workBook) {
   XSSFSheet typeSheet = workBook.createSheet(PRODUCTTYPE_SHEETNAME);
   List<ProductType> allTypes = productTypeDao.getAll();
   Collections.sort(allTypes, new LftRgtTreeNodeComparator()); // 按左值排序。
   List<AdjacencyNode<ProductType>> adjacencyNodes =
       new LftRgtTreeMenuTool<ProductType>().toAdjacencyNode(allTypes);
   int rownum = 0;
   XSSFRow typeTitleRow = typeSheet.createRow(rownum++);
   typeTitleRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue("类别名称");
   typeTitleRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("类别编号");
   typeTitleRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("父类编号");
   for (AdjacencyNode<ProductType> productType : adjacencyNodes) {
     XSSFRow row = typeSheet.createRow(rownum);
     XSSFCell typeCell = row.createCell(0, Cell.CELL_TYPE_STRING);
     typeCell.setCellValue(productType.getNode().getName());
     XSSFCell itemCell = row.createCell(1, Cell.CELL_TYPE_STRING);
     itemCell.setCellValue(productType.getNode().getItem());
     if (productType.getParent() != null) {
       XSSFCell parentItemCell = row.createCell(2, Cell.CELL_TYPE_STRING);
       parentItemCell.setCellValue(productType.getParent().getItem());
     }
     rownum++;
   }
   return typeSheet;
 }
 private void printCurso(Curso c) {
   int rowNum;
   XSSFSheet sheet =
       report.createSheet("Curso " + c.getNombre() + (c.getIsMixto() ? "(Mixto)" : "(No Mixto)"));
   rowNum = 0;
   for (Alumno a : c.getAlumnos()) {
     XSSFRow row = sheet.createRow(rowNum++);
     int cellNum = 0;
     if (rowNum == 1) {
       XSSFCell cell = row.createCell(cellNum++);
       cell.setCellValue("Id");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Apellido");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Nombre");
       cell = row.createCell(cellNum++);
       cellNum = 0;
       row = sheet.createRow(rowNum++);
     }
     XSSFCell cell = row.createCell(cellNum++);
     cell.setCellValue(a.getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(a.getApellido());
     cell = row.createCell(cellNum++);
     cell.setCellValue(a.getNombres());
     cell = row.createCell(cellNum++);
   }
 }
 private void printPreferencias() {
   int rowNum;
   XSSFSheet sheet = report.createSheet("Preferencias");
   rowNum = 0;
   for (Preferencia p : AlumnosCursosModel.getInstance().getPreferencias()) {
     XSSFRow row = sheet.createRow(rowNum++);
     int cellNum = 0;
     if (rowNum == 1) {
       XSSFCell cell = row.createCell(cellNum++);
       cell.setCellValue("Id 1");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Id 2");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Satisfecha?");
       cell = row.createCell(cellNum++);
       cellNum = 0;
       row = sheet.createRow(rowNum++);
     }
     XSSFCell cell = row.createCell(cellNum++);
     cell.setCellValue(p.getAlumno1().getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(p.getAlumno2().getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(p.getIsSatisfecha().toString());
     cell = row.createCell(cellNum++);
   }
 }
  /**
   * 创建副标题
   *
   * @param workbook
   */
  private void createSubHeads(XSSFWorkbook workbook, List subHeadList) {

    XSSFCellStyle style = null;
    int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2; // 副标题行数
    int fristCol = 0;
    int columnCount = 26;
    String title = "";
    XSSFRow row = null;
    XSSFCell cell = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    /** 副标题每行放两个,一个居左,一个居右,所以除以2,多出来的一个,另起一行, */
    for (int i = 0; i < rowCount; i++) {
      row = workbook.getSheetAt(0).createRow(i + 1); // 创建一个行对象 ,加1是因为有一行头标题
      for (int j = 0; j < 2; j++) {
        if (subHeadList.size() <= (i * 2 + j)) break;
        style = workbook.createCellStyle();
        title = subHeadList.get(i * 2 + j).toString();
        font.setFontHeightInPoints((short) 9); // 字号
        style.setFont(font);
        if (j == 0) {
          /** 因为每行两个副标题,所以第一个标题占一半 */
          fristCol = columnCount / 2;
          cell = row.createCell(j); // 创建单元格
          cell.setCellValue(title);
          style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //  水平居左
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
          cell.setCellStyle(style); //  应用样式对象
          workbook
              .getSheetAt(0)
              .addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, fristCol - 1)); // 合并单元格
        } else {
          /** 因为每行两个副标题,第一个标题占一半,第二个占剩下的所有单元格 */
          fristCol = columnCount - (columnCount / 2) - (columnCount % 2);
          cell = row.createCell(columnCount / 2); // 创建单元格
          cell.setCellValue(title);
          // 字号
          style.setFont(font);
          style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //  水平居右
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
          cell.setCellStyle(style); //  应用样式对象

          workbook
              .getSheetAt(0)
              .addMergedRegion(
                  new CellRangeAddress(i + 1, i + 1, fristCol, columnCount - 1)); // 合并单元格
        }
      }
    }
  }
 public static void addErrorDescriptionRow(int rowNum, String field, String errorDescription) {
   try {
     XSSFRow destRow = sheetWrite2.createRow(rowCount);
     XSSFCell cell = destRow.createCell(0);
     cell.setCellValue(Integer.toString(rowNum));
     cell = destRow.createCell(1);
     cell.setCellValue(field);
     cell = destRow.createCell(2);
     cell.setCellValue(errorDescription);
   } catch (Exception ex) {
     System.out.println(
         "Error in writing descriptionRow in reject excel file :: " + ex.getMessage());
     ex.printStackTrace();
   }
 }
  private void fillRowWith(XSSFRow r, String[] data) {
    XSSFCell c = null;

    for (String s : data) {
      if (c == null) c = r.createCell(0);
      else c = r.createCell(c.getColumnIndex() + 1);

      try {
        double d = Double.valueOf(s);
        c.setCellValue(d);
      } catch (NumberFormatException e) {
        c.setCellValue(s);
      }
    }
  }
 /**
  * @param srcSheet the sheet to copy.
  * @param destSheet the sheet to create.
  * @param srcRow the row to copy.
  * @param destRow the row to create.
  * @param styleMap -
  */
 public static void copyRow(
     XSSFSheet srcSheet,
     XSSFSheet destSheet,
     XSSFRow srcRow,
     XSSFRow destRow,
     Map<Integer, XSSFCellStyle> styleMap) {
   Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
   destRow.setHeight(srcRow.getHeight());
   for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
     XSSFCell oldCell = srcRow.getCell(j);
     XSSFCell newCell = destRow.getCell(j);
     if (oldCell != null) {
       if (newCell == null) {
         newCell = destRow.createCell(j);
       }
       copyCell(oldCell, newCell, styleMap);
       CellRangeAddress mergedRegion =
           getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
       if (mergedRegion != null) {
         CellRangeAddress newMergedRegion =
             new CellRangeAddress(
                 mergedRegion.getFirstRow(),
                 mergedRegion.getFirstColumn(),
                 mergedRegion.getLastRow(),
                 mergedRegion.getLastColumn());
         CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
         if (isNewMergedRegion(wrapper, mergedRegions)) {
           mergedRegions.add(wrapper);
           destSheet.addMergedRegion(wrapper.range);
         }
       }
     }
   }
 }
  public static void main(String[] args) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);

    XSSFCell cell = row.createCell(1);
    XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

    XSSFFont font1 = wb.createFont();
    font1.setBold(true);
    font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
    rt.applyFont(0, 10, font1);

    XSSFFont font2 = wb.createFont();
    font2.setItalic(true);
    font2.setUnderline(XSSFFont.U_DOUBLE);
    font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
    rt.applyFont(10, 19, font2);

    XSSFFont font3 = wb.createFont();
    font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
    rt.append(" Jumped over the lazy dog", font3);

    cell.setCellValue(rt);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
    wb.write(fileOut);
    fileOut.close();
  }
Beispiel #9
0
 /**
  * 指定した列番号の<b>XSSFCell</b>を取得する。有効範囲外のセルの場合は新規作成する。
  *
  * @param row <b>XSSFRow</b>
  * @param nColumn 取得したいセルの列番号
  * @return <b>XSSFCell</b>
  */
 public static XSSFCell getCellAnyway(XSSFRow row, int nColumn) {
   assert row != null;
   XSSFCell cell = row.getCell(nColumn);
   if (cell == null) {
     cell = row.createCell(nColumn);
   }
   return cell;
 }
Beispiel #10
0
  // returns true if data is set successfully else false
  public boolean setCellData(
      String sheetName, String colName, int rowNum, String data, String url) {
    // System.out.println("setCellData setCellData******************");
    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);

      if (rowNum <= 0) return false;

      int index = workBook.getSheetIndex(sheetName);
      int colNum = -1;
      if (index == -1) return false;

      sheet = workBook.getSheetAt(index);
      // System.out.println("A");
      row = sheet.getRow(0);
      for (int i = 0; i < row.getLastCellNum(); i++) {
        // System.out.println(row.getCell(i).getStringCellValue().trim());
        if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
      }

      if (colNum == -1) return false;
      sheet.autoSizeColumn(colNum); // ashish
      row = sheet.getRow(rowNum - 1);
      if (row == null) row = sheet.createRow(rowNum - 1);

      cell = row.getCell(colNum);
      if (cell == null) cell = row.createCell(colNum);

      cell.setCellValue(data);
      XSSFCreationHelper createHelper = workBook.getCreationHelper();

      // cell style for hyperlinks
      // by default hypelrinks are blue and underlined
      CellStyle hlink_style = workBook.createCellStyle();
      XSSFFont hlink_font = workBook.createFont();
      hlink_font.setUnderline(XSSFFont.U_SINGLE);
      hlink_font.setColor(IndexedColors.BLUE.getIndex());
      hlink_style.setFont(hlink_font);
      // hlink_style.setWrapText(true);

      XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
      link.setAddress(url);
      cell.setHyperlink(link);
      cell.setCellStyle(hlink_style);

      fos = new FileOutputStream(path);
      workBook.write(fos);

      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
Beispiel #11
0
 public void generate4Dispatches(
     List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents)
     throws IOException {
   for (int i = 0; i < sheetNames.size(); i++) {
     // Create a blank sheet
     XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i));
     // Create row object
     XSSFRow row;
     // This data needs to be written (Object[])
     Map<Integer, String[]> contentMap = new TreeMap<>();
     contentMap.put(1, headersList.get(i));
     for (int j = 0; j < contents.get(i).size(); j++) {
       contentMap.put(j + 2, contents.get(i).get(j));
     }
     // Iterate over data and write to sheet
     Set<Integer> keyid = contentMap.keySet();
     int rowid = 0;
     for (Integer key : keyid) {
       row = spreadsheet.createRow(rowid++);
       String[] objectArr = contentMap.get(key);
       int cellid = 0;
       for (String obj : objectArr) {
         if (obj.contains("\r\n")) {
           String[] strArray = obj.split("\r\n");
           for (String str : strArray) {
             Cell cell = row.createCell(cellid);
             cell.setCellValue(str);
             row = spreadsheet.createRow(rowid++);
           }
         } else {
           Cell cell = row.createCell(cellid++);
           cell.setCellValue(obj);
         }
       }
     }
     //            for (int c = 0; c < headersList.get(i).length; c++) {
     //                spreadsheet.autoSizeColumn(c);
     //            }
     // Write the workbook in file system
     FileOutputStream out = new FileOutputStream(new File(fileName));
     workbook.write(out);
     out.close();
   }
 }
  /**
   * 创建单元格
   *
   * @param row
   * @param style
   * @param colList
   * @param startCol
   * @param rowset
   */
  private void createColumn(
      XSSFWorkbook workbook,
      XSSFRow row,
      XSSFCellStyle style,
      List<String> colList,
      int startCol,
      EFRowSet rowset,
      String type) {
    XSSFCell cell = null;

    for (int i = 0; i < colList.size(); i++) {
      /** 创建单元格、设置列名称 */
      cell = row.createCell(startCol);
      cell.setCellStyle(style);
      if (type.equals("N")) {
        XSSFDataFormat df = workbook.createDataFormat();
        cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00"));
        cell.setCellValue(rowset.getNumber(colList.get(i), 0.0).doubleValue()); // 设置列名称
      } else {
        if (colList.get(i).equals("F_CRFX")) {
          if (rowset.getString(colList.get(i), "").equals("R")) {
            cell.setCellValue("入库单");
          } else if (rowset.getString(colList.get(i), "").equals("C")) {
            cell.setCellValue("出库单");
          } else if (rowset.getString(colList.get(i), "").equals("T")) {
            cell.setCellValue("退库单");
          } else if (rowset.getString(colList.get(i), "").equals("D")) {
            cell.setCellValue("调拨单");
          }
        } else if (colList.get(i).equals("F_DJLX")) {
          if (rowset.getString(colList.get(i), "").equals("R0")) {
            cell.setCellValue("采购入库");
          } else if (rowset.getString(colList.get(i), "").equals("R1")) {
            cell.setCellValue("更换入库");
          } else if (rowset.getString(colList.get(i), "").equals("T0")) {
            cell.setCellValue("退库单");
          } else if (rowset.getString(colList.get(i), "").equals("T1")) {
            cell.setCellValue("材料退货");
          } else if (rowset.getString(colList.get(i), "").equals("C0")) {
            cell.setCellValue("正常出库");
          } else if (rowset.getString(colList.get(i), "").equals("C1")) {
            cell.setCellValue("借调出库");
          } else if (rowset.getString(colList.get(i), "").equals("C2")) {
            cell.setCellValue("被借调出库");
          } else if (rowset.getString(colList.get(i), "").equals("D")) {
            cell.setCellValue("仓库调拨");
          }
        } else {
          cell.setCellValue(rowset.getString(colList.get(i), ""));
        }
      }
      startCol++;
    }
  }
Beispiel #13
0
  public <S, L> void rulewriter(Map<S, L> map) throws IOException {
    FileOutputStream fos = new FileOutputStream(file);

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet xsheet = workbook.createSheet();
    int count = 0;
    XSSFRow row = xsheet.createRow(count++);
    row.createCell(0).setCellValue("Error ID");
    row.createCell(1).setCellValue("Rule ID");

    for (Entry<S, L> entry : map.entrySet()) {
      row = xsheet.createRow(count);
      row.createCell(0).setCellValue((String) entry.getKey());
      row.createCell(1).setCellValue(entry.getValue().toString());

      count++;
    }
    workbook.write(fos);
    fos.close();
  }
Beispiel #14
0
  // returns true if column is created successfully
  public boolean addColumn(String sheetName, String colName) {
    // System.out.println("**************addColumn*********************");

    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);
      int index = workBook.getSheetIndex(sheetName);
      if (index == -1) return false;

      XSSFCellStyle style = workBook.createCellStyle();
      style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

      sheet = workBook.getSheetAt(index);

      row = sheet.getRow(0);
      if (row == null) row = sheet.createRow(0);

      // cell = row.getCell();
      // if (cell == null)
      // System.out.println(row.getLastCellNum());
      if (row.getLastCellNum() == -1) cell = row.createCell(0);
      else cell = row.createCell(row.getLastCellNum());

      cell.setCellValue(colName);
      cell.setCellStyle(style);

      fos = new FileOutputStream(path);
      workBook.write(fos);
      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }

    return true;
  }
 /** 创建一个副标题 */
 private void createSubHeadCell(
     XSSFWorkbook workbook,
     XSSFRow row,
     XSSFCellStyle style,
     String title,
     int rowIndex,
     int colIndex) {
   XSSFCell cell = row.createCell(colIndex); // 创建单元格
   cell.setCellValue(title);
   workbook
       .getSheetAt(0)
       .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 1)); // 合并单元格
   cell.setCellStyle(style);
 }
Beispiel #16
0
  public boolean setCellData(String sheetName, String colName, int rowNum, String data) {
    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);

      if (rowNum <= 0) return false;

      int index = workBook.getSheetIndex(sheetName);
      int colNum = -1;
      if (index == -1) return false;

      sheet = workBook.getSheetAt(index);

      row = sheet.getRow(0);
      for (int i = 0; i < row.getLastCellNum(); i++) {
        // System.out.println(row.getCell(i).getStringCellValue().trim());
        if (row.getCell(i).getStringCellValue().trim().equals(colName)) colNum = i;
      }
      if (colNum == -1) return false;

      sheet.autoSizeColumn(colNum);
      row = sheet.getRow(rowNum - 1);
      if (row == null) row = sheet.createRow(rowNum - 1);

      cell = row.getCell(colNum);
      if (cell == null) cell = row.createCell(colNum);

      // cell style
      // CellStyle cs = workbook.createCellStyle();
      // cs.setWrapText(true);
      // cell.setCellStyle(cs);
      cell.setCellValue(data);

      fos = new FileOutputStream(path);

      workBook.write(fos);

      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
 /**
  * 创建标题,并设置字体、字号、加粗、颜色
  *
  * @param workbook
  */
 private void setHeadCellFont(XSSFWorkbook workbook, String title) {
   XSSFCellStyle style = workbook.createCellStyle();
   XSSFRow row = (XSSFRow) workbook.getSheetAt(0).createRow(0); // 创建一个行对象
   XSSFCell cell = row.createCell(0); // 创建单元格
   XSSFFont font = workbook.createFont(); // 创建字体对象
   font.setFontName(HSSFFont.FONT_ARIAL); // 字体
   font.setFontHeightInPoints((short) 16); // 字号
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
   style.setFont(font);
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); //  水平居中
   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
   row.setHeightInPoints(23); //  设置行高23像素
   cell.setCellStyle(style); //  应用样式对象
   workbook
       .getSheetAt(0)
       .addMergedRegion(new CellRangeAddress(0, 0, 0, 16)); // 四个参数分别是:起始行,结束行,起始列,结束列
   cell.setCellValue(title); //  写入头标题
 }
 public static void addErrorRow(XSSFRow srcRow) {
   try {
     XSSFRow destRow = sheetWrite.createRow(rowCount);
     // XSSFRow destRow =null;
     for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
       XSSFCell oldCell = srcRow.getCell(j); // ancienne cell
       XSSFCell newCell = destRow.getCell(j); // new cell
       if (oldCell != null) {
         if (newCell == null) {
           newCell = destRow.createCell(j);
         }
         copyCell(oldCell, newCell);
       }
     }
   } catch (Exception ex) {
     System.out.println("Error in writing Row in reject excel file :: " + ex.getMessage());
     ex.printStackTrace();
   }
 }
Beispiel #19
0
  public static void main(String[] args) throws IOException {

    File file = new File("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
    FileInputStream fis = new FileInputStream(file);

    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = sheet.getRow(0);
    XSSFCell cell = row.getCell(0);
    System.out.println(cell);

    XSSFSheet write = workbook.createSheet();
    XSSFRow rowwrite = write.createRow(0);
    XSSFCell cell1 = rowwrite.createCell(1);
    cell1.setCellValue("Yamini");
    FileOutputStream file1 =
        new FileOutputStream("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
    workbook.write(file1);
    file1.close();
  }
  /**
   * 创建单元格
   *
   * @param row
   * @param style
   * @param colList
   * @param startCol
   * @param rowset
   */
  private void createColumn(
      XSSFWorkbook workbook,
      XSSFRow row,
      XSSFCellStyle style,
      List<String> colList,
      int startCol,
      EFRowSet rowset,
      String type) {
    XSSFCell cell = null;

    for (int i = 0; i < colList.size(); i++) {
      /** 创建单元格、设置列名称 */
      cell = row.createCell(startCol);
      cell.setCellStyle(style);
      if (type.equals("N")) {
        XSSFDataFormat df = workbook.createDataFormat();
        cell.getCellStyle().setDataFormat(df.getFormat("#,##0.00"));
        cell.setCellValue(rowset.getNumber(colList.get(i), 0.0).doubleValue()); // 设置列名称
      } else {
        cell.setCellValue(rowset.getString(colList.get(i), ""));
      }
      startCol++;
    }
  }
  @SuppressWarnings("static-access")
  public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {

    try {

      Row = ExcelWSheet.getRow(RowNum);

      Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);

      if (Cell == null) {

        Cell = Row.createCell(ColNum);

        Cell.setCellValue(Result);

      } else {

        Cell.setCellValue(Result);
      }

      // Constant variables Test Data path and Test Data file name

      FileOutputStream fileOut =
          new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);

      ExcelWBook.write(fileOut);

      fileOut.flush();

      fileOut.close();

    } catch (Exception e) {

      throw (e);
    }
  }
Beispiel #22
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;
  }
  /**
   * 创建列
   *
   * @param workbook
   */
  private void exportColumnData(XSSFWorkbook workbook) {
    XSSFCellStyle style = null;
    XSSFRow row = null;
    XSSFCell cell = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    XSSFCellStyle topStyle = setColumnTopBorder(workbook);
    XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
    row = workbook.getSheetAt(0).createRow(5); // 创建一个行对象 列标题
    style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); //  水平居中
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
    font.setFontHeightInPoints((short) 10); // 字号
    style.setFont(font); // 设置字体
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
    setColumnBorder(style, 0); // 设置列边框
    workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度

    /** 创建单元格、设置列名称、合并单元格 */
    cell = row.createCell(0);
    cell.setCellValue("材料编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 0, 0));
    cell.setCellStyle(style);

    cell = row.createCell(1);
    cell.setCellValue("材料名称");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 1, 1));
    cell.setCellStyle(style);

    cell = row.createCell(2);
    cell.setCellValue("型号规格");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 2, 2));
    cell.setCellStyle(style);

    cell = row.createCell(3);
    cell.setCellValue("单位");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 3, 3));
    cell.setCellStyle(style);

    cell = row.createCell(4);
    cell.setCellValue("申请数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 4, 4));
    cell.setCellStyle(style);

    cell = row.createCell(5);
    cell.setCellValue("入库数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 5, 5));
    cell.setCellStyle(style);

    cell = row.createCell(6);
    cell.setCellValue("出库数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 6, 8));
    cell.setCellStyle(style);

    cell = row.createCell(7);
    cell.setCellStyle(topStyle);

    cell = row.createCell(8);
    cell.setCellStyle(topStyle);

    cell = row.createCell(9);
    cell.setCellValue("退货数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 9, 10));
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellStyle(topStyle);

    cell = row.createCell(11);
    cell.setCellValue("调拨数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 11, 11));
    cell.setCellStyle(style);

    cell = row.createCell(12);
    cell.setCellValue("库存数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 12, 12));
    cell.setCellStyle(style);

    cell = row.createCell(13);
    cell.setCellValue("领用数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 13, 13));
    cell.setCellStyle(style);

    cell = row.createCell(14);
    cell.setCellValue("领用总价");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 14, 14));
    cell.setCellStyle(style);

    cell = row.createCell(15);
    cell.setCellValue("库存情况");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 15, 15));
    cell.setCellStyle(style);

    cell = row.createCell(16);
    cell.setCellValue("备注");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 16, 16));
    cell.setCellStyle(style);

    row = workbook.getSheetAt(0).createRow(6); // 创建一个行对象 列标题
    /** 创建单元格、设置列名称、合并单元格 */
    cell = row.createCell(6);
    cell.setCellValue("正常领用");
    cell.setCellStyle(style);

    cell = row.createCell(7);
    cell.setCellValue("借调");
    cell.setCellStyle(style);

    cell = row.createCell(8);
    cell.setCellValue("被借调");
    cell.setCellStyle(style);

    cell = row.createCell(9);
    cell.setCellValue("材料退货");
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellValue("厂商退货");
    cell.setCellStyle(style);

    for (int i = 0; i < 5; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(buttomStyle);
    }

    for (int i = 11; i < 16; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(buttomStyle);
    }
  }
Beispiel #24
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;
  }
Beispiel #25
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;
  }
  public XSSFWorkbook writeCTGI(
      XSSFWorkbook cTGWorkbook,
      MultipartFile fileIn,
      String classId,
      MultipartFile staplesMasterStyleGuide,
      MultipartFile attributeReport) {
    try {
      List<XSSFCell> attributeCell = writeStyle(staplesMasterStyleGuide, classId);
      List<String> attributes = getAttributes(attributeReport, classId);
      XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
      XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
      XSSFSheet worksheetOut = cTGWorkbook.getSheetAt(0);
      int i = 1;
      int j = 4;
      XSSFCell cell = null;
      for (i = 1; i < worksheetIn.getLastRowNum(); i++) {
        XSSFRow rowIn = worksheetIn.getRow(i);
        XSSFRow rowOut = worksheetOut.createRow(j);

        if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
          XSSFCell cell1 = rowOut.createCell(13);
          if (rowIn.getCell(2) != null) cell1.setCellValue(rowIn.getCell(2).getStringCellValue());
          XSSFCell cell2 = rowOut.createCell(15);
          cell2.setCellValue(classId);
          XSSFCell cell3 = rowOut.createCell(19);
          if (rowIn.getCell(3) != null) cell3.setCellValue(rowIn.getCell(3).getStringCellValue());
          XSSFCell cell4 = rowOut.createCell(20);
          if (rowIn.getCell(0) != null) cell4.setCellValue(rowIn.getCell(0).getStringCellValue());
          XSSFCell cell5 = rowOut.createCell(24);
          if (rowIn.getCell(3) != null) cell5.setCellValue(rowIn.getCell(3).getStringCellValue());
          XSSFCell cell6 = rowOut.createCell(179);
          if (rowIn.getCell(5) != null) cell6.setCellValue(rowIn.getCell(5).getNumericCellValue());

          XSSFCell cell7 = rowOut.createCell(26);
          cell7 = attributeCell.get(0);

          int z = 1;
          for (int k = 36; k < 48; k++) {
            cell = rowOut.createCell(k);
            cell.setCellValue((attributeCell.get(z)).getStringCellValue());
            z++;
          }
          int counter = 1;
          int cellCount = 63;
          for (String attribute : attributes) {

            if (counter <= 50) {
              String lable = attribute.split("###")[0];
              String value = attribute.split("###")[1];
              cell = rowOut.createCell(cellCount);
              cell.setCellValue(lable);
              cell = rowOut.createCell(cellCount + 1);
              cell.setCellValue(value);
              counter++;
              cellCount += 2;
            }
          }
        }
        j++;
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
    return cTGWorkbook;
  }
  private void generateExcelDoc(String docName) throws FileNotFoundException, IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet overall = workbook.createSheet("Overall");
    XSSFRow row = overall.createRow(0);

    XSSFCellStyle topStyle = workbook.createCellStyle();
    topStyle.setAlignment(CellStyle.ALIGN_CENTER);

    XSSFCell theme = row.createCell(0);
    theme.setCellValue("Theme");
    overall.autoSizeColumn(0);

    XSSFCell occurs = row.createCell(1);
    occurs.setCellValue("Occurrences");
    overall.autoSizeColumn(1);

    XSSFCell prev = row.createCell(2);
    prev.setCellValue("Prevalence");
    overall.autoSizeColumn(2);

    theme.setCellStyle(topStyle);
    occurs.setCellStyle(topStyle);
    prev.setCellStyle(topStyle);

    for (int i = 0; i < themes.size(); i++) {
      XSSFRow r = overall.createRow((i + 1));

      XSSFCell c = r.createCell(0);
      c.setCellValue(themes.get(i).getName());

      XSSFCell c1 = r.createCell(1);
      c1.setCellValue(themes.get(i).getTotalOccurs());

      XSSFCell c2 = r.createCell(2);
      c2.setCellValue(calculatePrevalence(themes.get(i).getTotalOccurs(), lineCount));
    }

    // This could be done in the previous loop but since we don't need
    // indices as much, we may as well use the cleaner for each loop

    for (Theme t : themes) {
      XSSFSheet themeSheet = workbook.createSheet(t.getName());
      XSSFRow row1 = themeSheet.createRow(0);

      XSSFCell keyword = row1.createCell(0);
      keyword.setCellValue("Keyword");
      keyword.setCellStyle(topStyle);

      XSSFCell occ = row1.createCell(1);
      occ.setCellValue("Occurrences");
      occ.setCellStyle(topStyle);

      XSSFCell themePrev = row1.createCell(2);
      themePrev.setCellValue("Prevalence");
      themePrev.setCellStyle(topStyle);

      for (int i = 0; i < t.getKeywords().size(); i++) {
        Keyword k = t.getKeywords().get(i);
        XSSFRow r = themeSheet.createRow((i + 1));

        XSSFCell c = r.createCell(0);
        c.setCellValue(k.getName());

        XSSFCell c1 = r.createCell(1);
        c1.setCellValue(k.getNumOccurs());

        XSSFCell c2 = r.createCell(2);
        c2.setCellValue(calculatePrevalence(k.getNumOccurs(), t.getTotalOccurs()));
      }
    }

    FileOutputStream output = new FileOutputStream(docName);
    workbook.write(output);
    output.close();
  }
  /**
   * 创建列
   *
   * @param workbook
   */
  private void exportColumnData(XSSFWorkbook workbook, List subHeadList) {
    XSSFCellStyle style = null;
    XSSFRow row = null;
    XSSFCell cell = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    XSSFCellStyle topStyle = setColumnTopBorder(workbook);
    XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
    int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2 + 1;

    row = workbook.getSheetAt(0).createRow(rowCount); // 创建一个行对象 列标题
    style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); //  水平居中
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
    font.setFontHeightInPoints((short) 10); // 字号
    style.setFont(font); // 设置字体
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
    setColumnBorder(style, 0); // 设置列边框
    workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度

    /** 创建单元格、设置列名称、合并单元格 */
    cell = row.createCell(0);
    cell.setCellValue("会计期间");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 0, 0));
    cell.setCellStyle(style);

    cell = row.createCell(1);
    cell.setCellValue("单据编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 1, 1));
    cell.setCellStyle(style);

    cell = row.createCell(2);
    cell.setCellValue("分录编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 2, 2));
    cell.setCellStyle(style);

    cell = row.createCell(3);
    cell.setCellValue("材料入库信息");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 3, 8));
    cell.setCellStyle(style);

    cell = row.createCell(4);
    cell.setCellStyle(topStyle);

    cell = row.createCell(5);
    cell.setCellStyle(topStyle);

    cell = row.createCell(6);
    cell.setCellStyle(topStyle);

    cell = row.createCell(7);
    cell.setCellStyle(topStyle);

    cell = row.createCell(8);
    cell.setCellStyle(topStyle);

    cell = row.createCell(9);
    cell.setCellValue("材料出库信息");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 9, 14));
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellStyle(topStyle);

    cell = row.createCell(11);
    cell.setCellStyle(topStyle);

    cell = row.createCell(12);
    cell.setCellStyle(topStyle);

    cell = row.createCell(13);
    cell.setCellStyle(topStyle);

    cell = row.createCell(14);
    cell.setCellStyle(topStyle);

    cell = row.createCell(15);
    cell.setCellValue("材料编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 15, 15));
    cell.setCellStyle(style);

    cell = row.createCell(16);
    cell.setCellValue("材料名称");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 16, 16));
    cell.setCellStyle(style);

    cell = row.createCell(17);
    cell.setCellValue("规格型号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 17, 17));
    cell.setCellStyle(style);

    cell = row.createCell(18);
    cell.setCellValue("计量单位");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 18, 18));
    cell.setCellStyle(style);

    cell = row.createCell(19);
    cell.setCellValue("供应商编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 19, 19));
    cell.setCellStyle(style);

    cell = row.createCell(20);
    cell.setCellValue("供应商名称");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 20, 20));
    cell.setCellStyle(style);

    cell = row.createCell(21);
    cell.setCellValue("出入方向");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 21, 21));
    cell.setCellStyle(style);

    cell = row.createCell(22);
    cell.setCellValue("单据类型");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 22, 22));
    cell.setCellStyle(style);

    cell = row.createCell(23);
    cell.setCellValue("材料单价");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 25, 25));
    cell.setCellStyle(style);

    cell = row.createCell(24);
    cell.setCellValue("材料数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 23, 23));
    cell.setCellStyle(style);

    cell = row.createCell(25);
    cell.setCellValue("材料总价");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 24, 24));
    cell.setCellStyle(style);

    row = workbook.getSheetAt(0).createRow(rowCount + 1); // 创建一个行对象 列标题
    /** 创建单元格、设置列名称、合并单元格 */

    // 材料入库信息
    cell = row.createCell(3);
    cell.setCellValue("仓库编号");
    cell.setCellStyle(style);

    cell = row.createCell(4);
    cell.setCellValue("仓库编号");
    cell.setCellStyle(style);

    cell = row.createCell(5);
    cell.setCellValue("仓库名称");
    cell.setCellStyle(style);

    cell = row.createCell(6);
    cell.setCellValue("项目编号");
    cell.setCellStyle(style);

    cell = row.createCell(7);
    cell.setCellValue("项目名称");
    cell.setCellStyle(style);

    cell = row.createCell(8);
    cell.setCellValue("产品编号");
    cell.setCellStyle(style);

    // 材料出库信息
    cell = row.createCell(9);
    cell.setCellValue("产品名称");
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellValue("仓库编号");
    cell.setCellStyle(style);

    cell = row.createCell(11);
    cell.setCellValue("仓库名称");
    cell.setCellStyle(style);

    cell = row.createCell(12);
    cell.setCellValue("项目编号");
    cell.setCellStyle(style);

    cell = row.createCell(13);
    cell.setCellValue("项目名称");
    cell.setCellStyle(style);

    cell = row.createCell(14);
    cell.setCellValue("产品编号");
    cell.setCellStyle(style);

    for (int i = 0; i < 3; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(buttomStyle);
    }

    for (int i = 15; i < 26; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(buttomStyle);
    }
  }
  @Override
  public XSSFWorkbook exportAllStatistics(
      List<StatisticData> statistics, Interview interview, ByteArrayOutputStream out)
      throws IOException {
    /*NULL is correct value*/
    try (XSSFWorkbook book = new XSSFWorkbook()) {
      XSSFSheet sheet = book.createSheet(SHEET_NAME);
      int rowNumber = 0;

      XSSFRow mainHeader = sheet.createRow(rowNumber++);
      XSSFCell cell = mainHeader.createCell(0);
      cell.setCellType(Cell.CELL_TYPE_STRING);
      cell.setCellValue(HEADER_PREFIX + interview.getName() + "\"");
      makeCellAutosizeAndBold(book, mainHeader);

      rowNumber++;

      for (StatisticData statistic : statistics) {
        /*Add question text*/
        XSSFRow row = sheet.createRow(rowNumber++);
        XSSFCell questionText = row.createCell(0);
        questionText.setCellType(Cell.CELL_TYPE_STRING);
        questionText.setCellValue(QUESTION_PREFIX + statistic.getQuestionText());

        XSSFRow tableHeader = sheet.createRow(rowNumber++);
        XSSFCell c1 = tableHeader.createCell(0);
        XSSFCell c2 = tableHeader.createCell(1);
        XSSFCell c3 = tableHeader.createCell(2);
        XSSFCell c4 = tableHeader.createCell(3);

        c1.setCellType(Cell.CELL_TYPE_STRING);
        c2.setCellType(Cell.CELL_TYPE_NUMERIC);
        c3.setCellType(Cell.CELL_TYPE_NUMERIC);
        c4.setCellType(Cell.CELL_TYPE_STRING);

        c1.setCellValue(ANSWER_CELL_HEADER);
        c2.setCellValue(PEOPLE_COUNT_HEADER);
        c3.setCellValue(PERCENT_HEADER);
        c4.setCellValue(RESPONDENTS_HEADER);

        makeCellAutosizeAndBold(book, tableHeader);

        Map<String, Object[]> answerData = statistic.getAnswerData();

        for (String key : answerData.keySet()) {
          XSSFRow answer = sheet.createRow(rowNumber++);
          XSSFCell answerText = answer.createCell(0);
          XSSFCell peopleResponded = answer.createCell(1);
          XSSFCell percentResponded = answer.createCell(2);
          XSSFCell respondents = answer.createCell(3);

          answerText.setCellType(Cell.CELL_TYPE_STRING);
          peopleResponded.setCellType(Cell.CELL_TYPE_NUMERIC);
          percentResponded.setCellType(Cell.CELL_TYPE_NUMERIC);
          respondents.setCellType(Cell.CELL_TYPE_STRING);

          answerText.setCellValue(key);
          Object[] values = answerData.get(key);

          peopleResponded.setCellValue(values[0].toString());
          percentResponded.setCellValue(values[1].toString().replace(",", "."));
          respondents.setCellValue(userAnswerService.getRespondentListHowLine(interview, key));

          makeCellsAutosize(book, answer);
        }

        rowNumber++;
      }

      book.write(out);
      return book;
    }
  }
  private void renderCell(
      BandElement bandElement,
      String bandName,
      Object value,
      int gridRow,
      int sheetRow,
      int sheetColumn,
      int rowSpan,
      int colSpan,
      boolean image) {

    if (bandElement instanceof ReportBandElement) {
      colSpan = 1;
    }
    XSSFCellStyle cellStyle =
        buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan);

    // if we have a subreport on the current grid row we have to take care of the sheetColumn
    if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
        && (gridRow == prevSubreportFirstRow)
        && (prevSubreportLastColumn != -1)) {
      sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn;
    }
    XSSFCell c = xlsRow.createCell(sheetColumn);

    if (image) {
      if ((value == null) || "".equals(value)) {
        c.setCellType(XSSFCell.CELL_TYPE_STRING);
        c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_FOUND));
      } else {
        try {
          ImageBandElement ibe = (ImageBandElement) bandElement;
          byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight());
          XSSFClientAnchor anchor =
              new XSSFClientAnchor(
                  0,
                  0,
                  0,
                  0,
                  (short) sheetColumn,
                  sheetRow,
                  (short) (sheetColumn + colSpan),
                  (sheetRow + rowSpan));
          int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

          // image is created over the cells, so if it's height is bigger we set the row height
          short height = xlsRow.getHeight();
          int realImageHeight = getRealImageSize((String) value)[1];
          if (ibe.isScaled()) {
            realImageHeight = ibe.getHeight();
          }
          short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
          boolean doResize = false;
          if (imageHeight > height) {
            xlsRow.setHeight(imageHeight);
          } else {
            doResize = true;
          }

          Picture picture = patriarch.createPicture(anchor, index);
          if (doResize) {
            picture.resize();
          }
          anchor.setAnchorType(2);
        } catch (Exception ex) {
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
        }
      }

      if (cellStyle != null) {
        c.setCellStyle(cellStyle);
      }

    } else {
      if (bandElement instanceof HyperlinkBandElement) {
        Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink();
        XSSFHyperlink link = wb.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_URL);
        link.setAddress(hyp.getUrl());
        c.setHyperlink(link);
        c.setCellValue(wb.getCreationHelper().createRichTextString(hyp.getText()));
        c.setCellType(XSSFCell.CELL_TYPE_STRING);
      } else if (bandElement instanceof ReportBandElement) {
        Report report = ((ReportBandElement) bandElement).getReport();
        ExporterBean eb = null;
        try {
          eb = getSubreportExporterBean(report, true);
          XlsxExporter subExporter = new XlsxExporter(eb, cellStyle);
          subExporter.export();
          XSSFSheet subreportSheet = subExporter.getSubreportSheet();

          if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
              && (gridRow == prevSubreportFirstRow)) {
            // other subreports on the same header line after the first
            sheetColumn = prevSubreportLastColumn;
            sheetRow -= addedPageRows;
            pageRow -= addedPageRows;
            addedPageRows = 0;
          } else {
            addedPageRows = subreportSheet.getLastRowNum();
            pageRow += addedPageRows;
            // if subreport is not on the first column we merge all cells in the columns before,
            // between the rows subreport occupies
            if (sheetColumn > 0) {
              for (int i = 0; i <= sheetColumn - 1; i++) {
                CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i);
                regions.add(new XlsxRegion(cra, null));
              }
            }
          }
          int cols = XlsxUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet);
          addRegions(xlsSheet, subExporter.getSubreportRegions(), wb);
          if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) {
            prevSubreportFirstRow = gridRow;
            prevSubreportFirstColumn = sheetColumn;
            prevSubreportLastColumn = sheetColumn + cols;
          }
        } catch (Exception e) {
          e.printStackTrace();
        } finally {
          if ((eb != null) && (eb.getResult() != null)) {
            eb.getResult().close();
          }
        }
      } else if (bandElement instanceof ImageColumnBandElement) {
        try {
          ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement;
          String v = StringUtil.getValueAsString(value, null);
          if (StringUtil.BLOB.equals(v)) {
            c.setCellType(XSSFCell.CELL_TYPE_STRING);
            c.setCellValue(wb.getCreationHelper().createRichTextString(StringUtil.BLOB));
          } else {
            byte[] imageD = StringUtil.decodeImage(v);
            byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight());
            XSSFClientAnchor anchor =
                new XSSFClientAnchor(
                    0,
                    0,
                    0,
                    0,
                    (short) sheetColumn,
                    sheetRow,
                    (short) (sheetColumn + colSpan),
                    (sheetRow + rowSpan));
            int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

            // image is created over the cells, so if it's height is bigger we set the row height
            short height = xlsRow.getHeight();
            int realImageHeight = getRealImageSize(imageBytes)[1];
            if (icbe.isScaled()) {
              realImageHeight = icbe.getHeight();
            }
            short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
            if (imageHeight > height) {
              xlsRow.setHeight(imageHeight);
            }

            Picture picture = patriarch.createPicture(anchor, index);
            picture.resize();
            anchor.setAnchorType(2);
          }
        } catch (Exception e) {
          e.printStackTrace();
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
        }

      } else {

        if (value == null) {
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(""));
        } else if (value instanceof Number) {
          c.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
          c.setCellValue(((Number) value).doubleValue());
        } else {
          String pattern = null;
          if (bandElement instanceof FieldBandElement) {
            FieldBandElement fbe = (FieldBandElement) bandElement;
            pattern = fbe.getPattern();
          }
          if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) {
            Date date;
            if (value instanceof java.sql.Date) {
              date = new Date(((java.sql.Date) value).getTime());
            } else {
              date = (java.sql.Timestamp) value;
            }
            if (cellStyle != null) {
              if (pattern == null) {
                // use default pattern if none selected
                Locale locale = Locale.getDefault();
                pattern =
                    ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale))
                        .toPattern();
              } else {
                pattern = StringUtil.getI18nString(pattern, getReportLanguage());
              }
              cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern));
            }
            c.setCellValue(date);
          } else {
            c.setCellType(XSSFCell.CELL_TYPE_STRING);
            String text = StringUtil.getValueAsString(value, pattern);
            if ((bandElement != null) && bandElement.isWrapText()) {
              // try to interpret new line characters
              // \\n is used here to be possible to add in designer grid cell with \n
              if (text.contains("\\n")
                  || text.contains("\n")
                  || text.contains("\r")
                  || text.contains("\r\n")) {
                String crLf = Character.toString((char) 13) + Character.toString((char) 10);
                int lines = countLines(text);
                if (text.contains("\r\n")) {
                  text = text.replaceAll("\r\n", crLf);
                } else {
                  text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf);
                }
                c.setCellValue(text);
                cellStyle.setWrapText(true);
                xlsRow.setHeightInPoints(lines * (cellStyle.getFont().getFontHeightInPoints() + 3));
              } else {
                c.setCellValue(wb.getCreationHelper().createRichTextString(text));
              }
            } else {
              c.setCellValue(wb.getCreationHelper().createRichTextString(text));
            }
          }
        }
      }

      if (cellStyle != null) {
        if (bandElement != null) {
          cellStyle.setRotation(bandElement.getTextRotation());
        }
        if (!(bandElement instanceof ReportBandElement)) {
          c.setCellStyle(cellStyle);
        }
      }

      if ((rowSpan > 1) || (colSpan > 1)) {
        CellRangeAddress cra =
            new CellRangeAddress(
                sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1);
        Border beBorder = bandElement.getBorder();
        if (hasRowRenderConditions(bandElement, gridRow, value)) {
          // for row render conditions we must keep the row border
          beBorder = border;
        }
        regions.add(new XlsxRegion(cra, beBorder));
      }
    }
  }