/**
  * 初始化函数
  *
  * @param title 表格标题,传“空值”,表示无标题
  * @param headerList 表头列表
  */
 private void initialize(String title, List<String> headerList) {
   this.wb = new SXSSFWorkbook(500);
   this.sheet = wb.createSheet("Export");
   this.styles = createStyles(wb);
   // Create title
   if (StringUtils.isNotBlank(title)) {
     Row titleRow = sheet.createRow(rownum++);
     titleRow.setHeightInPoints(30);
     Cell titleCell = titleRow.createCell(0);
     titleCell.setCellStyle(styles.get("title"));
     titleCell.setCellValue(title);
     sheet.addMergedRegion(
         new CellRangeAddress(
             titleRow.getRowNum(),
             titleRow.getRowNum(),
             titleRow.getRowNum(),
             headerList.size() - 1));
   }
   // Create header
   if (headerList == null) {
     throw new RuntimeException("headerList not null!");
   }
   Row headerRow = sheet.createRow(rownum++);
   headerRow.setHeightInPoints(16);
   for (int i = 0; i < headerList.size(); i++) {
     Cell cell = headerRow.createCell(i);
     cell.setCellStyle(styles.get("header"));
     String[] ss = StringUtils.split(headerList.get(i), "**", 2);
     if (ss.length == 2) {
       cell.setCellValue(ss[0]);
       Comment comment =
           this.sheet
               .createDrawingPatriarch()
               .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
       comment.setString(new XSSFRichTextString(ss[1]));
       cell.setCellComment(comment);
     } else {
       cell.setCellValue(headerList.get(i));
     }
     sheet.autoSizeColumn(i);
   }
   for (int i = 0; i < headerList.size(); i++) {
     int colWidth = sheet.getColumnWidth(i) * 2;
     sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
   }
   log.debug("Initialize success.");
 }
  @Override
  public void close() {
    try {
      int nbS = wb.getNumberOfSheets();
      for (int i = 0; i < nbS; i++) {
        Sheet s = wb.getSheetAt(i);
        if (s.getRow(0)
            != null) { // ceci arrive si on vide la mémoire tampon pour les grands fichiers. Dans ce
                       // cas pas de possibilité de traiter la mise en page de gros fichiers
          for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) {
            Cell c = s.getRow(0).getCell(j);
            c.getSheet().autoSizeColumn(c.getColumnIndex());
          }

          Cell firstCell = s.getRow(0).getCell(0);
          Cell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1);
          s.setAutoFilter(
              new CellRangeAddress(
                  firstCell.getRowIndex(),
                  lastCell.getRowIndex(),
                  lastCell.getRowIndex(),
                  lastCell.getColumnIndex()));
        }
      }
      wb.write(fileOut);
      fileOut.flush();
      fileOut.close();
    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Beispiel #3
0
 private SXSSFWorkbook createWorkbook() throws FileNotFoundException, IOException {
   SXSSFWorkbook wb;
   FileInputStream fis;
   File filee = POIUtil.getStream(filePath, this.titles);
   fis = new FileInputStream(filee);
   XSSFWorkbook wbb = new XSSFWorkbook(fis);
   wb =
       new SXSSFWorkbook(
           wbb,
           Integer.parseInt(
               PropertiesUtil.getPropertyValue("export.properties", Constant.PAGE_COUNT, "1000")));
   wb.setCompressTempFiles(true);
   return wb;
 }
 /**
  * 批量导出
  *
  * @param data
  * @throws FileNotFoundException
  */
 public void transform(Map<String, Object> data) throws FileNotFoundException {
   FileInputStream templateIs = new FileInputStream(template);
   try {
     XSSFWorkbook bookTmp = (XSSFWorkbook) transformer.transformXLS(templateIs, data);
     bookTmp.setSheetName(0, "第" + count + "页");
     if (count == 1) {
       book = new SXSSFWorkbook(bookTmp);
     } else {
       Sheet newSheet = book.createSheet(bookTmp.getSheetName(0));
       Sheet sheet = bookTmp.getSheetAt(0);
       Util.copySheets(newSheet, sheet);
     }
     count++;
   } catch (ParsePropertyException e) {
     e.printStackTrace();
   } catch (InvalidFormatException e) {
     e.printStackTrace();
   } finally {
     try {
       templateIs.close();
     } catch (IOException e) {
       e.printStackTrace();
     }
   }
 }
  /**
   * @see
   *     net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#afterWorking()
   */
  @Override
  protected void afterWorking() throws Exception {
    FileOutputStream out = new FileOutputStream(this.file);
    this.workbook.write(out);
    out.close();

    // dispose of temporary files backing this workbook on disk
    if (workbook instanceof SXSSFWorkbook) {
      ((SXSSFWorkbook) workbook).dispose();
    }
  }
 /**
  * 添加一个单元格
  *
  * @param row 添加的行
  * @param column 添加列号
  * @param val 添加值
  * @param align 对齐方式(1:靠左;2:居中;3:靠右)
  * @return 单元格对象
  */
 public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
   Cell cell = row.createCell(column);
   CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
   try {
     if (val == null) {
       cell.setCellValue("");
     } else if (val instanceof String) {
       cell.setCellValue((String) val);
     } else if (val instanceof Integer) {
       cell.setCellValue((Integer) val);
     } else if (val instanceof Long) {
       cell.setCellValue((Long) val);
     } else if (val instanceof Double) {
       cell.setCellValue((Double) val);
     } else if (val instanceof Float) {
       cell.setCellValue((Float) val);
     } else if (val instanceof Date) {
       DataFormat format = wb.createDataFormat();
       style.setDataFormat(format.getFormat("yyyy-MM-dd"));
       cell.setCellValue((Date) val);
     } else {
       if (fieldType != Class.class) {
         cell.setCellValue(
             (String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
       } else {
         cell.setCellValue(
             (String)
                 Class.forName(
                         this.getClass()
                             .getName()
                             .replaceAll(
                                 this.getClass().getSimpleName(),
                                 "fieldtype." + val.getClass().getSimpleName() + "Type"))
                     .getMethod("setValue", Object.class)
                     .invoke(null, val));
       }
     }
   } catch (Exception ex) {
     log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
     cell.setCellValue(val.toString());
   }
   cell.setCellStyle(style);
   return cell;
 }
  private Row createRowFrom(IRecordable o) {
    Row lastRow, r;
    String[] data;

    if (dicoLocations.containsKey(o.getClassement()))
      lastRow = dicoLocations.get(o.getClassement());
    else {
      Sheet logSheet = wb.createSheet(o.getClassement());
      lastRow = logSheet.createRow(0);
      data = o.getTitles();
      fillRowWith(lastRow, data);
      dicoLocations.put(o.getClassement(), lastRow);
    }
    r = lastRow.getSheet().createRow(lastRow.getRowNum() + 1);
    data = o.getRecords();
    fillRowWith(r, data);
    dicoLocations.replace(o.getClassement(), r);
    return r;
  }
Beispiel #8
0
 private void writeToFile(SXSSFWorkbook wb) throws FileNotFoundException, IOException {
   FileOutputStream out = new FileOutputStream(filePath);
   wb.write(out);
   wb.dispose();
   out.close();
 }
 /** 清理临时文件 */
 public ExportExcel dispose() {
   wb.dispose();
   return this;
 }
 /**
  * 输出数据流
  *
  * @param os 输出数据流
  */
 public ExportExcel write(OutputStream os) throws IOException {
   wb.write(os);
   return this;
 }