Example #1
0
  /**
   * make the excel file based on headers and contents which should have been set
   *
   * @param sheetName specified sheet name
   * @return ExcelMaker itself
   * @throws IllegalArgumentException only support String, Integer, Long, Double, Date, null as cell
   *     data.
   */
  public ExcelMaker make(String sheetName) throws IllegalArgumentException {

    // check if headers and contents set
    if (!contextReady()) {
      throw new IllegalArgumentException("invalid headers or contents");
    }

    Sheet sheet = sheetName == null ? workbook.createSheet() : workbook.createSheet(sheetName);

    // write the display headers as the 1st row in the sheet
    int rowNum = 0;
    Row headerRow = sheet.createRow(rowNum++);
    if (displayHeaders != null) {
      for (int cellNum = 0; cellNum < displayHeaders.size(); cellNum++) {
        String header = displayHeaders.get(cellNum);
        Cell cell = headerRow.createCell(cellNum);
        cell.setCellValue(header);
      }
    } else { // if display headers not set, use headers as default
      for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
        String header = headers.get(cellNum);
        Cell cell = headerRow.createCell(cellNum);
        cell.setCellValue(header);
      }
    }

    // parse each Object in content list and write as a row
    for (Object content : contents) {
      Map<String, Object> headerMap =
          objectMapper.convertValue(content, new TypeReference<HashMap<String, Object>>() {});
      Row row = sheet.createRow(rowNum++);
      // only find properties that declared by headers
      for (int cellNum = 0; cellNum < headers.size(); cellNum++) {
        String header = headers.get(cellNum);
        Cell cell = row.createCell(cellNum);
        Object obj = headerMap.get(header);
        if (obj == null) {
          obj = "";
        }
        if (obj instanceof String) {
          cell.setCellValue((String) obj);
        } else if (obj instanceof Integer) {
          cell.setCellValue((Integer) obj);
        } else if (obj instanceof Long) {
          cell.setCellValue((Long) obj);
        } else if (obj instanceof Double) {
          cell.setCellValue((Double) obj);
        } else if (obj instanceof Date) {
          cell.setCellValue((Date) obj);
        } else {
          throw new IllegalArgumentException("unsupported cell type");
        }
      }
    }
    return this;
  }
 private void write() {
   sheet = workbook.createSheet("Inventory");
   int col = header(1, true);
   header(col, false);
   data(1, filterByInputType(true));
   data(col, filterByInputType(false));
 }
Example #3
0
  public void make(File source, DataSet header, DataSet master) throws Exception {
    this.header = header;
    this.master = master;

    if (header == null || master == null) throw new Exception("Dataset is empty");

    long t = System.currentTimeMillis();

    InputStream inp = new FileInputStream(source);
    Workbook oldBook = WorkbookFactory.create(inp);
    Sheet oldSheet = oldBook.getSheetAt(0);

    Workbook newBook = new HSSFWorkbook();
    Sheet newSheet = newBook.createSheet(oldSheet.getSheetName());

    init(newBook);
    process(oldSheet, newSheet);

    File target = File.createTempFile("libra", ".xls");
    target.deleteOnExit();
    FileOutputStream fileOut = new FileOutputStream(target);

    newBook.write(fileOut);
    fileOut.close();
    oldBook.close();
    inp.close();

    Desktop.getDesktop().open(target);

    System.out.println(System.currentTimeMillis() - t);
  }
  private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("sheet1");
    CellStyle headerStyle = getHeaderStyle(wb);
    CellStyle firstCellStyle = getFirsetCellStyle(wb);
    CellStyle commonCellStyle = getCommonCellStyle(wb);
    CellStyle amtCellStyle = getAmtCellStyle(wb);

    for (int i = 0; i < LENGTH_9; i++) {
      sheet1.setColumnWidth(i, STR_15 * STR_256);
    }

    // 表头
    Row row = sheet1.createRow(0);
    row.setHeightInPoints(STR_20);

    Cell cell = headInfo(headerStyle, row);

    if (detailList.size() == 0) {
      row = sheet1.createRow(1);
      cell = row.createCell(0);
      cell.setCellValue(NO_RECORD);
    } else {
      fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
    }
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    wb.write(outputStream);
    InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
    outputStream.close();
    return inputStream;
  }
Example #5
0
 public void writeData(Data data, boolean onlyNumbers) {
   try {
     File file = new File(path);
     if (!file.exists()) {
       file.createNewFile();
     }
     FileOutputStream fOutputStream = new FileOutputStream(file);
     org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
     org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
     @SuppressWarnings("rawtypes")
     ArrayList<ArrayList> dataList = data.getData();
     for (int i = 0; i < dataList.size(); i++) {
       Row row = sheet.createRow(i);
       for (int j = 0; j < dataList.get(i).size(); j++) {
         row.createCell(j);
         if (onlyNumbers) {
           row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
         } else {
           row.getCell(j).setCellValue((String) dataList.get(i).get(j));
         }
       }
     }
     workbook.write(fOutputStream);
     workbook.close();
     fOutputStream.close();
     workbook = null;
     file = null;
     sheet = null;
   } catch (EncryptedDocumentException | IOException e) {
     e.printStackTrace();
   }
 }
Example #6
0
  /*
   * Don't hardcode US Locale for datetimes in Excel.  Experiment a bit.
   */
  public static void issue26() throws InvalidFormatException, IOException {

    System.out.println("Testing issue26 =================================");
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);

    // first cell
    Cell cell0 = row.createCell(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
    cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
    cell0.setCellStyle(cellStyle);

    // second cell using another format with French locale
    CellStyle cs2 = wb.createCellStyle();
    String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
    System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
    String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
    System.out.println(
        "Converted pattern in FRENCH locale is: "
            + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));

    DataFormat df = wb.createDataFormat();
    cs2.setDataFormat(df.getFormat(excelFormatPattern));
    Cell cell1 = row.createCell(1);
    cell1.setCellValue(new Date(1385903303326L));
    cell1.setCellStyle(cs2);

    FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
    wb.write(out);
    out.close();
    System.out.println("Wrote /tmp/issue26_out.xlsx");
  }
 /**
  * write excel sheet, specified value to specified cell.
  *
  * @param sheetName excel sheet name
  * @param row row index which to be changed
  * @param col column index which to be changed
  * @param value value to be put into cell
  * @throws RuntimeException
  */
 public void setExcelValue(String sheetName, int row, int col, String value) {
   Workbook workBook = null;
   try {
     if (new File(fileName).exists()) {
       workBook = getWorkBook(new FileInputStream(fileName), false);
     } else {
       workBook = getWorkBook(null, false);
     }
     xlSheet = workBook.getSheet(sheetName);
     if (xlSheet == null) {
       xlSheet = workBook.createSheet(sheetName);
     }
     xlRow = xlSheet.getRow(row - 1);
     if (xlRow == null) {
       xlRow = xlSheet.createRow((short) row - 1);
     }
     xlCell = xlRow.getCell(col - 1);
     if (xlCell == null) {
       xlCell = xlRow.createCell(col - 1);
     }
     xlCell.setCellType(1); // set cell type as string
     xlCell.setCellValue(value);
     FileOutputStream fileOut = new FileOutputStream(fileName);
     workBook.write(fileOut);
     fileOut.flush();
     fileOut.close();
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("set excel value failed:" + e.getMessage());
   }
 }
Example #8
0
  @RequestMapping(value = "/excel", method = RequestMethod.GET)
  public void excel(HttpServletResponse response) throws Exception {
    Workbook wb = new HSSFWorkbook();
    //        CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");
    org.apache.poi.ss.usermodel.Row row = sheet.createRow((short) 0);
    row = sheet.createRow((short) 1);
    row.createCell(0).setCellValue("Mã");
    row.createCell(1).setCellValue("Tên");
    row.createCell(2).setCellValue("Mã Path");
    row.createCell(3).setCellValue("Tên path");
    row.createCell(4).setCellValue("Cấp danh mục");

    int i = 1;
    List<Category> listAll = categoryService.listAll();
    for (Category print : listAll) {
      if (print.getName().length() > 19) {
        i++;
        row = sheet.createRow((short) i);
        row.createCell(0).setCellValue(String.valueOf(print.getId()));
        row.createCell(1).setCellValue(String.valueOf(print.getName()));
        row.createCell(2).setCellValue(String.valueOf(print.getPath()));
        String path = "";
        for (String categoryId : print.getPath()) {
          Category get = categoryService.get(categoryId);
          path += get.getName() + " >>";
        }
        row.createCell(3).setCellValue(String.valueOf(path));
        row.createCell(4).setCellValue(String.valueOf(print.getLevel()));
      }
    }
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=Danh-sach-danh-muc.xls");
    wb.write(response.getOutputStream());
  }
Example #9
0
 // ����Excel
 public String ExcelExport() throws Exception {
   HttpServletRequest request = ServletActionContext.getRequest();
   String ids = request.getParameter("ids");
   List<Dise> list = new ArrayList<Dise>();
   String[] array = ids.split(",");
   int[] id = new int[array.length];
   for (int i = 0; i < id.length; i++) {
     Dise dise = DiseService.findById(Integer.valueOf(array[i]));
     list.add(dise);
   }
   Workbook workbook = new HSSFWorkbook();
   Sheet sheet = workbook.createSheet("ѧ����Ϣ");
   Row row = sheet.createRow(0);
   row.createCell(0).setCellValue("ѧ��");
   row.createCell(1).setCellValue("����");
   row.createCell(2).setCellValue("����");
   row.createCell(3).setCellValue("�Ա�");
   row.createCell(4).setCellValue("��ַ");
   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
   for (int i = 1; i <= list.size(); i++) {
     Dise stu = list.get(i - 1);
     row = sheet.createRow(i);
     row.createCell(0).setCellValue(stu.getIds());
     row.createCell(1).setCellValue(stu.getName());
     row.createCell(2).setCellValue(stu.getA());
     row.createCell(3).setCellValue(stu.getB());
     row.createCell(4).setCellValue(stu.getC());
   }
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   workbook.write(baos);
   excelFile = new ByteArrayInputStream(baos.toByteArray());
   baos.close();
   return "excel";
 }
Example #10
0
 @Override
 public ExcelSheetWriter createWritable(
     String entityName,
     Iterable<AttributeMetaData> attributes,
     AttributeWriteMode attributeWriteMode) {
   Sheet poiSheet = workbook.createSheet(entityName);
   return new ExcelSheetWriter(poiSheet, attributes, attributeWriteMode, cellProcessors);
 }
Example #11
0
 protected Sheet getCurrentSheet() {
   synchronized (syncObj) {
     if (sheet == null) {
       Workbook wb = getCurrentWorkbook();
       sheet = wb.createSheet();
     }
     return sheet;
   }
 }
Example #12
0
  public static File writerFile(String[] title, List<String[]> content, String filePath)
      throws IOException {
    checkDir(filePath);

    File f = new File(filePath);

    if (!f.exists()) {
      f.createNewFile();
    }
    FileOutputStream out = new FileOutputStream(f);

    wb = new SXSSFWorkbook();
    setStyle(wb);
    Sheet sheet = wb.createSheet("sheet1");

    Row titleRow = sheet.createRow(0);

    titleRow.setHeightInPoints(20);

    int tCount = title.length;
    for (int i = 0; i < tCount; i++) {
      Cell cell = titleRow.createCell(i);
      cell.setCellStyle(titleStyle);
      cell.setCellType(Cell.CELL_TYPE_STRING);
      cell.setCellValue(title[i]);

      sheet.setColumnWidth(i, 5000);
    }
    int rnum = 1;
    for (String[] c : content) {
      Row r = sheet.createRow(rnum);
      for (int i = 0; i < c.length; i++) {
        Cell cell = r.createCell(i);
        cell.setCellStyle(contentStyle);
        cell.setCellType(Cell.CELL_TYPE_STRING);
        sheet.setColumnWidth(i, 5000);

        String v = c[i];
        if (v == null) {
          v = "";
        }

        cell.setCellValue(v);
      }

      rnum++;
    }

    wb.write(out);
    out.flush();
    wb.close();
    out.close();

    return f;
  }
  private void writeFullDataToSpreadsheet(
      OutputStream fos, Map<String, List<Map<String, Object>>> data) throws IOException {
    LOG.debug("writeFullDataToSpreadsheet started" + data);

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    // Sheet sheet = workbook.getSheetAt(0);

    // LOG.debug("workbook.getActiveSheetIndex()" + workbook.getActiveSheetIndex());
    // sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());
    int rowNum = sheet.getLastRowNum();
    LOG.debug("sheet.getLastRowNum()" + sheet.getLastRowNum());

    for (String methodName : data.keySet()) {

      boolean isHeaderWritten = false;
      Map<String, Integer> parameterIndexMap = new LinkedHashMap<String, Integer>();
      for (Map<String, Object> methodData : data.get(methodName)) {
        // rowNum increment by one to proceed with next record of the method.
        LOG.debug("methodData.keySet().size" + methodData.keySet().size());
        LOG.debug("methodData" + methodData);

        if (!isHeaderWritten) {
          int columnIndex = 0;
          // Write the method name and parameter names in header.
          writeDataToCell(sheet, rowNum, columnIndex++, methodName);
          for (String parameterName : methodData.keySet()) {
            writeDataToCell(sheet, rowNum, columnIndex, parameterName);
            // capturing column index so that corresponding values will be placed at same column
            parameterIndexMap.put(parameterName, columnIndex);
            columnIndex++;
          }
          // incrementing row after writing header
          rowNum++;
          isHeaderWritten = true;
        }

        // Write the actual result and test status values.
        if (isHeaderWritten) {

          int columnIndex = 0;
          // we need to put empty cell in first column as per easytest xls structure.
          writeDataToCell(sheet, rowNum, columnIndex++, null);
          for (String parameter : methodData.keySet()) {
            writeDataToCell(
                sheet, rowNum, parameterIndexMap.get(parameter), methodData.get(parameter));
          }
          rowNum++;
        }
      }
    }
    // Write the output to a file
    workbook.write(fos);
    LOG.debug("writeFullDataToSpreadsheet finished");
  }
 /**
  * @see
  *     net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#beforeWorking()
  */
 @Override
 protected void beforeWorking(File file) throws IOException {
   if (_exportFormat == ExportFormat.EXPORT_FORMAT_XLS) {
     this.workbook = new HSSFWorkbook(); // See https://gist.github.com/madan712/3912272
   } else {
     this.workbook =
         new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
   }
   this.file = file;
   this.sheet = workbook.createSheet("Squirrel SQL Export");
 }
Example #15
0
 /** @throws Exception */
 public void makeXlsxFile() throws Exception {
   String fileName = "test.xlsx";
   FileOutputStream fileOut = new FileOutputStream(fileName);
   Workbook wb = new XSSFWorkbook();
   Sheet sheet = wb.createSheet("test"); // シート作成
   Row row = sheet.createRow((short) 0);
   Cell cell = row.createCell(0);
   cell.setCellValue("日本語は通る?");
   wb.write(fileOut);
   fileOut.close();
 }
 @Override
 public Result populate(Workbook workbook) {
   Sheet savingsTransactionSheet = workbook.createSheet("SavingsTransaction");
   setLayout(savingsTransactionSheet);
   Result result = officeSheetPopulator.populate(workbook);
   if (result.isSuccess()) result = clientSheetPopulator.populate(workbook);
   if (result.isSuccess()) result = extrasSheetPopulator.populate(workbook);
   if (result.isSuccess()) result = populateLoansTable(savingsTransactionSheet);
   if (result.isSuccess()) result = setRules(savingsTransactionSheet);
   setDefaults(savingsTransactionSheet);
   return result;
 }
Example #17
0
  /**
   * put dataList to excel sheets.
   *
   * @param sheetName excel sheet name.
   * @param dataList data list to be parsed and put into excel sheets.
   * @param rowNum row count of the sheet to be modified.
   * @param ignoreRows rows to skip when put value.
   * @param ignoreColumns columns to skip when put value.
   * @throws RuntimeException
   * @throws IllegalArgumentException
   */
  public void putListToExcelWithFullIgnore(
      String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) {
    if (dataList.size() % rowNum != 0) {
      LOG.error("dataList has wrong element count for excel!");
      throw new IllegalArgumentException("dataList has wrong element count for excel!");
    }

    String value = null;
    int index = 0;
    final int colCount = dataList.size() / rowNum;
    Workbook workBook = null;

    try {
      if (new File(fileName).exists()) {
        workBook = getWorkBook(new FileInputStream(fileName), false);
      } else {
        workBook = getWorkBook(null, false);
      }
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        xlSheet = workBook.createSheet(sheetName);
      }

      for (int j = ignoreRows; j < ignoreRows + rowNum; j++) {
        xlRow = xlSheet.getRow(j);
        if (xlRow == null) {
          xlRow = xlSheet.createRow(j);
        }
        for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) {
          value = dataList.get(index);
          xlCell = xlRow.getCell(i);
          if (xlCell == null) {
            xlCell = xlRow.createCell(i);
          }

          xlCell.setCellType(1);
          if (value != null) {
            xlCell.setCellValue(value);
          }
          index++;
        }
      }

      FileOutputStream fileOut = new FileOutputStream(fileName);
      workBook.write(fileOut);
      fileOut.flush();
      fileOut.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("set excel value failed:" + e.getMessage());
    }
  }
Example #18
0
  public void testWriteAndRead() throws ParseException {

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();

    Row row = sheet.createRow(2);

    Cell cell = row.createCell(3);

    CellHelper test = new CellHelper();

    // Numeric

    test.setCellValue(cell, 42.2);

    Double numericResult = test.getCellValue(cell, Double.class);

    assertEquals(42.2, numericResult);

    // Date

    test.setCellValue(cell, DateHelper.parseDate("2013-08-30"));

    Date dateResult = test.getCellValue(cell, Date.class);

    assertEquals(DateHelper.parseDate("2013-08-30"), dateResult);

    // Boolean

    test.setCellValue(cell, true);

    Boolean booleanResult = test.getCellValue(cell, Boolean.class);

    assertEquals(true, (boolean) booleanResult);

    // Text

    test.setCellValue(cell, "apple");

    String stringResult = test.getCellValue(cell, String.class);

    assertEquals("apple", stringResult);

    // Null

    test.setCellValue(cell, null);

    Object nullResult = test.getCellValue(cell, Object.class);

    assertEquals(null, nullResult);
  }
  /**
   * Creates a new excel-file and creates the title colons. Be careful: if file already exists it
   * will be overridden!!
   *
   * @param filename
   */
  public void newTrainingFile(String filename, String language) throws IOException {

    // create new file
    Workbook wb = new HSSFWorkbook();

    ls = LanguageSupportFactory.getLanguageSupport("en");

    // create info and training sheet
    infoSheet = wb.createSheet(LanguageSupportFactory.getInfoSheetName());
    trainingSheet = wb.createSheet(ls.NameTrainingSheet());

    // add version info
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = infoSheet.createRow(0);
    // Create a cell and put a value in it.
    row.createCell(0).setCellValue("Version:");
    row.createCell(1).setCellValue(this.VERSION);

    row = infoSheet.createRow(1);
    row.createCell(0).setCellValue("Language:");
    row.createCell(1).setCellValue(this.LANGUAGE);

    // add training titles
    row = trainingSheet.createRow(0);
    row.createCell(COL_MODULE_NUMBER).setCellValue(ls.ModuleNumberTitle());
    row.createCell(COL_MODULE).setCellValue(ls.ModuleTitle());
    row.createCell(COL_TOPIC).setCellValue(ls.TopicTitle());
    row.createCell(COL_ACTION).setCellValue(ls.ActionTitle());
    row.createCell(COL_DURATION_ACTION).setCellValue(ls.DurationActionTitle());
    row.createCell(COL_SELECTED).setCellValue(ls.SelectedTitle());
    row.createCell(COL_DURATION_TOPIC).setCellValue(ls.DurationTopicTitle());
    row.createCell(COL_DURATION_MOUDLE).setCellValue(ls.DurationModuleTitle());
    row.createCell(COL_TARGET_GROUPS).setCellValue(ls.TargetGroupsTitle());
    row.createCell(COL_DURATION_TRAINING).setCellValue(ls.DurationTrainingTitle());

    save(filename, wb);
  }
  public static void main(String[] args) throws Exception {
    // The path to the documents directory.
    String dataDir = Utils.getDataDir(ApacheHideUnHideCells.class);

    InputStream inStream = new FileInputStream(dataDir + "workbook.xls");
    Workbook workbook = WorkbookFactory.create(inStream);
    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(0);
    row.setZeroHeight(true);

    FileOutputStream fileOut = new FileOutputStream(dataDir + "ApacheHideUnhideCells.xls");
    workbook.write(fileOut);
    fileOut.close();

    System.out.println("Process Completed.");
  }
Example #21
0
 /**
  * @param wb
  * @param reviewerSheetName
  */
 public static void create(Workbook wb, String sheetName) {
   int sheetNum = wb.getSheetIndex(sheetName);
   if (sheetNum >= 0) {
     wb.removeSheetAt(sheetNum);
   }
   Sheet sheet = wb.createSheet(sheetName);
   CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
   CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
   Row row = sheet.createRow(0);
   for (int i = 0; i < MultiDocumentSpreadsheet.MAX_DOCUMENTS; i++) {
     sheet.setColumnWidth(i, COL_WIDTH * 256);
     sheet.setDefaultColumnStyle(i, defaultStyle);
     Cell cell = row.createCell(i);
     cell.setCellStyle(headerStyle);
   }
 }
 public static void create(Workbook wb, String sheetName) {
   int sheetNum = wb.getSheetIndex(sheetName);
   if (sheetNum >= 0) {
     wb.removeSheetAt(sheetNum);
   }
   Sheet sheet = wb.createSheet(sheetName);
   CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
   CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
   Row row = sheet.createRow(0);
   for (int i = 0; i < HEADER_TITLES.length; i++) {
     sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
     sheet.setDefaultColumnStyle(i, defaultStyle);
     Cell cell = row.createCell(i);
     cell.setCellStyle(headerStyle);
     cell.setCellValue(HEADER_TITLES[i]);
   }
 }
Example #23
0
 public boolean CreateExcel(String path) {
   try {
     wb = new HSSFWorkbook(); // 创建新的Excel工作簿
     sheet = wb.createSheet("民政婚姻登记");
     Row row = sheet.createRow(3);
     Cell cell = row.createCell(0);
     cell.setCellType(Cell.CELL_TYPE_STRING);
     cell.setCellValue(1);
     // OutputStreamReader  in = new OutputStreamReader(new FileOutputStream(path),"UTF-8");
     fileOut = new FileOutputStream(path);
     wb.write(fileOut);
     fileOut.close();
   } catch (IOException e) {
     System.out.println("CreateExcel() ERRO\n");
     return false;
   }
   return true;
 }
  public void printStatSpreadSheed() {
    try {
      // create workbook
      workbook = new XSSFWorkbook();
      FileOutputStream fileOut =
          new FileOutputStream(new File(saveDirectory, outputName + ".xlsx"));

      // create a worksheet
      Sheet sheet0 = workbook.createSheet("Contamination stats");
      sheet0.createFreezePane(0, 1, 0, 1);

      // create row, one for each item plus header
      // Row[] rows = new Row[]
    } catch (Exception e) {
      System.err.println("\nProblem printing the spreadsheet!");
      e.printStackTrace();
      System.exit(1);
    }
  }
Example #25
0
 public void appendToFile(Map<String, Double> content, String name) {
   Sheet sheet = null;
   sheet = workbook.createSheet();
   Row head = sheet.createRow(0);
   head.createCell(0).setCellValue(name);
   head.createCell(1).setCellValue("Сумма");
   int index = 1;
   double sum = 0;
   for (Map.Entry<String, Double> entry : content.entrySet()) {
     Row row = sheet.createRow(index);
     row.createCell(0).setCellValue(entry.getKey());
     row.createCell(1).setCellValue(entry.getValue());
     sum += entry.getValue();
     index++;
   }
   Row sumRow = sheet.createRow(index);
   sumRow.createCell(0).setCellValue("Итого");
   sumRow.createCell(1).setCellValue(sum);
 }
  public void addSheet(
      String sheetName, List<Object[]> bodyData, int[] entryTypes, String[] header) {
    Sheet addedSheet = workbook.createSheet(sheetName);

    createHeaderRow(addedSheet.createRow(0), header);

    // goes through each entry in the list creating the rows
    for (int index = 0; index < bodyData.size(); index++) {
      createBodyRow(
          addedSheet.createRow(index + 1),
          bodyData.get(index),
          entryTypes); // the 1 accounts for the header
    }

    // it now auto-sizes the columns
    for (int column = 0; column <= header.length; column++) {
      addedSheet.autoSizeColumn(column);
    }
  }
  public static void prepareSurvey() {
    Map<Integer, SentimentSentence> orderToSentence =
        importOrderToSentence(
            PreprocessingForDocumentVector.PYTHON_WORKSPACE + "order-to-sentence.txt");

    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("survey");
    int count = 0;

    while (count <= orderToSentence.size()) {
      Row row = sheet.createRow(count);

      Cell cellReviewId = row.createCell(0);
      Cell cellSentenceId = row.createCell(1);
      Cell cellSentence = row.createCell(2);
      Cell cellRating = row.createCell(3);

      if (count == 0) {
        cellReviewId.setCellValue("review id");
        cellSentenceId.setCellValue("sentence id");
        cellSentence.setCellValue("sentence");
        cellRating.setCellValue("Rating (1, 2, 3 or 4 stars)");
      } else {
        SentimentSentence sentence = orderToSentence.get(count - 1);
        cellReviewId.setCellValue(sentence.getReviewId());
        cellSentenceId.setCellValue(sentence.getId());
        cellSentence.setCellValue(sentence.getSentence());
      }

      ++count;
    }

    Path outputPath =
        Paths.get(PreprocessingForDocumentVector.PYTHON_WORKSPACE + "sentiment_survey.xlsx");
    try {

      Files.deleteIfExists(outputPath);
      wb.write(Files.newOutputStream(outputPath, StandardOpenOption.CREATE));
      wb.close();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
Example #28
0
  @Test
  public void overrideFlushedRows() throws IOException {
    Workbook wb = new SXSSFWorkbook(3);
    try {
      Sheet sheet = wb.createSheet();

      sheet.createRow(1);
      sheet.createRow(2);
      sheet.createRow(3);
      sheet.createRow(4);

      thrown.expect(Throwable.class);
      thrown.expectMessage(
          "Attempting to write a row[1] in the range [0,1] that is already written to disk.");
      sheet.createRow(1);
    } finally {
      wb.close();
    }
  }
  public ArquivoCliente() {

    count = 1;
    if (!new File("RepositorioCliente.xls").exists()) {
      // cria planilha
      wb = new HSSFWorkbook();
      // planilha
      sheetExcel = wb.createSheet();
      Row row = sheetExcel.createRow(0);

      String array[] = {
        "Nome",
        " CPF",
        " Email ",
        " RG ",
        " Data de nascimento ",
        " Endereco ",
        "CNH",
        "Placa do carro"
      };
      for (int i = 0; i < array.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(array[i]);
      }

      try {
        this.fos = new FileOutputStream("RepositorioCliente.xls");
        wb.write(fos);
        fos.close();

      } catch (Exception e) {
        e.printStackTrace();
      }
    } else {
      try {
        this.fis = new FileInputStream("RepositorioCliente.xls");
        wb = new HSSFWorkbook(fis);
        fis.close();
      } catch (IOException e) {
      }
    }
  }
  private Workbook createExcel(List<ConfirmationRecord> dataList) {
    InputStream templateExcelConf = null;
    Workbook wb = null;
    int contentRow = 5;
    try {
      Date currentCycle = null;
      templateExcelConf =
          Thread.currentThread()
              .getContextClassLoader()
              .getResourceAsStream(TEMPLATE_EXCEL_CONF_REPORT_PATH);
      wb = WorkbookFactory.create(templateExcelConf);

      Sheet tempSheet = wb.getSheetAt(0);
      Sheet newSheet = null;

      //			Excel Processing below here
      for (ConfirmationRecord conf : dataList) {
        if (maxCycleFrom == null || maxCycleFrom.compareTo(conf.getCycleFrom()) < 0)
          maxCycleFrom = conf.getCycleFrom();
        if (maxCycleTo == null || maxCycleTo.compareTo(conf.getCycleTo()) < 0)
          maxCycleTo = conf.getCycleTo();

        if (currentCycle == null || currentCycle.compareTo(conf.getCycleFrom()) != 0) {
          currentCycle = conf.getCycleFrom();
          newSheet = wb.createSheet(getSheetName(conf.getCycleFrom(), conf.getCycleTo()));
          headerProcess(tempSheet, newSheet, conf.getCycleFrom(), conf.getCycleTo());
          contentRow = 5;
        }

        contentProcess(tempSheet, newSheet, contentRow, conf);
        contentRow++;
      }
      return wb;
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }