Exemplo n.º 1
5
 /**
  * 初始化函数
  *
  * @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.");
 }
  private void headerProcess(Sheet tempSheet, Sheet newSheet, Date cycleFrom, Date cycleTo)
      throws Exception {
    Cell tCell = tempSheet.getRow(0).getCell(0, Row.CREATE_NULL_AS_BLANK);
    Cell nCell = newSheet.createRow(0).createCell(0, tCell.getCellType());
    nCell.setCellValue(tCell.getStringCellValue().replaceAll(PARAM_COMPANY, FWD_COMP_NAME));
    nCell.setCellStyle(tCell.getCellStyle());

    tCell = tempSheet.getRow(1).getCell(0, Row.CREATE_NULL_AS_BLANK);
    nCell = newSheet.createRow(1).createCell(0, tCell.getCellType());
    nCell.setCellValue(
        tCell
            .getStringCellValue()
            .replaceAll(PARAM_ABBR_NAME, "ADAMS-TVD")
            .replaceAll(
                PARAM_CYCLE_FROM, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleFrom))
            .replaceAll(PARAM_CYCLE_TO, DateUtil.convDateToString(DISPLAY_DATE_PATTERN, cycleTo)));
    nCell.setCellStyle(tCell.getCellStyle());

    Row tempRow = tempSheet.getRow(4);
    Row newRow = newSheet.createRow(4);

    for (int c = 0; c < tempRow.getLastCellNum(); c++) {
      tCell = tempRow.getCell(c, Row.CREATE_NULL_AS_BLANK);
      nCell = newRow.createCell(c, tCell.getCellType());
      nCell.setCellValue(tCell.getStringCellValue());
      nCell.setCellStyle(tCell.getCellStyle());
    }

    for (int i = 0; i < tempSheet.getNumMergedRegions(); i++) {
      CellRangeAddress mergedRegion = tempSheet.getMergedRegion(i);
      newSheet.addMergedRegion(mergedRegion);
    }
  }
 /**
  * Forms report in excel file. Sum weight. Sum volume etc.
  *
  * @param sheet Sheet where write the items
  * @param c Container from where take the info
  * @param lastRow last row after all items were written method uses lastRow + 2 to write report in
  *     next 2 rows after all items of the container were written
  */
 private void setReport(Container c, int lastRow) {
   String[] reportHeadings = {
     "Суммарный вес", // 6
     "Суммарный объем", // 7
     "Остаток вес", // 8
     "Остаток объем" // 9
   };
   double[] values = {
     c.getWeight(),
     c.getVolume(),
     c.getWeightLimit() - c.getWeight(),
     c.getVolumeLimit() - c.getVolume()
   };
   int dataCell = 5;
   int valuesIndex = 0; // index of the array of doubles(weigh, volume, etc)
   lastRow++;
   Row headings = outputSheet.createRow(lastRow);
   lastRow += 2;
   Row data = outputSheet.createRow(lastRow);
   for (String report : reportHeadings) {
     Cell heading = headings.createCell(dataCell);
     heading.setCellValue(report);
     Cell dataCellValue = data.createCell(dataCell);
     dataCellValue.setCellValue(values[valuesIndex]);
     dataCell++;
     valuesIndex++;
   }
 }
Exemplo n.º 4
0
  @Test
  public void overrideRowsInTemplate() throws IOException {
    XSSFWorkbook template = new XSSFWorkbook();
    template.createSheet().createRow(1);

    Workbook wb = new SXSSFWorkbook(template);
    try {
      Sheet sheet = wb.getSheetAt(0);

      try {
        sheet.createRow(1);
        fail("expected exception");
      } catch (Throwable e) {
        assertEquals(
            "Attempting to write a row[1] in the range [0,1] that is already written to disk.",
            e.getMessage());
      }
      try {
        sheet.createRow(0);
        fail("expected exception");
      } catch (Throwable e) {
        assertEquals(
            "Attempting to write a row[0] in the range [0,1] that is already written to disk.",
            e.getMessage());
      }
      sheet.createRow(2);
    } finally {
      wb.close();
      template.close();
    }
  }
Exemplo n.º 5
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());
  }
  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;
  }
Exemplo n.º 7
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";
 }
  // =================================================================================================================================
  private int exportFrontEquipListReport_header(
      List<FrontEquipListReport_subtype> subtypes, int rownum, XSSFWorkbook wb, Sheet sheet) {
    CellStyle style = getHeaderStyle(wb, (short) 11);
    CellStyle style2 = getHeaderStyle(wb, (short) 10);

    // 第一行列标题
    Row row_subtype = sheet.createRow(rownum++);
    Cell cell_subtype_0 = row_subtype.createCell(0);
    cell_subtype_0.setCellValue("");
    cell_subtype_0.setCellStyle(style);
    sheet.setColumnWidth(0, 700);
    Cell cell_subtype_1 = row_subtype.createCell(1);
    cell_subtype_1.setCellValue("");
    cell_subtype_1.setCellStyle(style);
    sheet.setColumnWidth(1, 1800);
    Cell cell_subtype_2 = row_subtype.createCell(2);
    cell_subtype_2.setCellValue("");
    cell_subtype_2.setCellStyle(style);
    sheet.setColumnWidth(2, 4800);

    // 第二行列标题
    Row row_prod = sheet.createRow(rownum++);
    Cell cell_prod_0 = row_prod.createCell(0);
    cell_prod_0.setCellValue("序号");
    cell_prod_0.setCellStyle(style2);
    Cell cell_prod_1 = row_prod.createCell(1);
    cell_prod_1.setCellValue("点位编号");
    cell_prod_1.setCellStyle(style2);
    Cell cell_prod_2 = row_prod.createCell(2);
    cell_prod_2.setCellValue("点位名称");
    cell_prod_2.setCellStyle(style2);

    int cell_num = 3;
    for (FrontEquipListReport_subtype subtype : subtypes) {
      int start_cell_num = cell_num;
      Cell cell_subtype = row_subtype.createCell(cell_num);
      cell_subtype.setCellValue(subtype.getSubtype_name());
      cell_subtype.setCellStyle(style);

      for (FrontEquipListReport_prod prod : subtype.getProds()) {
        if (start_cell_num != cell_num) {
          cell_subtype = row_subtype.createCell(cell_num);
          cell_subtype.setCellStyle(style);
        }

        Cell cell_prod = row_prod.createCell(cell_num);
        cell_prod.setCellValue(prod.getProd_name() + "(" + prod.getProd_style() + ")");
        cell_prod.setCellStyle(style2);
        cell_num++;
      }
      // 对子类型进行横向的单元格合并
      sheet.addMergedRegion(
          new CellRangeAddress(1, (short) 1, start_cell_num, (short) cell_num - 1));
    }

    return rownum;
  }
Exemplo n.º 9
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;
  }
Exemplo n.º 10
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;
  }
Exemplo n.º 11
0
  private void writeModule(TrainingModule module) {

    Row row = trainingSheet.createRow(++actualRow);
    row.createCell(COL_MODULE_NUMBER).setCellValue(module.getNumber());
    row.createCell(COL_MODULE).setCellValue(module.getName());

    // for all topics write topics
    Iterator<TrainingTopic> it = module.getTopics().iterator();
    while (it.hasNext()) {
      row = trainingSheet.createRow(++actualRow);
      writeTopic(it.next(), row);
    }
  }
Exemplo n.º 12
0
 private org.apache.poi.ss.usermodel.Row getRowForCoordinate(
     RowIndex rowIndex, SheetIndex sheetIndex) {
   Sheet sheet = workbook.getSheetAt(sheetIndex.value());
   org.apache.poi.ss.usermodel.Row row = sheet.getRow(rowIndex.value());
   if (row == null) row = sheet.createRow(rowIndex.value());
   return row;
 }
Exemplo n.º 13
0
 public static void copySheet(Sheet sheet, Sheet newSheet) {
   int maxCol = 0;
   for (int row = 0; row <= sheet.getLastRowNum(); row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = newSheet.getRow(row);
     if (newRow == null) newRow = newSheet.createRow(row);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     maxCol = (maxCol >= oldRow.getLastCellNum() - 1 ? maxCol : oldRow.getLastCellNum() - 1);
     for (int col = 0; col < oldRow.getLastCellNum(); col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col);
       if (newCell == null) newCell = newRow.createCell(col);
       copyCell(oldCell, newCell, true);
     }
   }
   for (int col = 0; col <= maxCol; col++) {
     if (sheet.getColumnWidth(col) >= 0) newSheet.setColumnWidth(col, sheet.getColumnWidth(col));
   }
   for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
     CellRangeAddress cra = sheet.getMergedRegion(i);
     newSheet.addMergedRegion(cra);
   }
 }
Exemplo n.º 14
0
 /**
  * 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());
   }
 }
Exemplo n.º 15
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");
  }
 private void setDefaults(Sheet worksheet) {
   try {
     for (Integer rowNo = 1; rowNo < 3000; rowNo++) {
       Row row = worksheet.getRow(rowNo);
       if (row == null) row = worksheet.createRow(rowNo);
       writeFormula(
           PRODUCT_COL,
           row,
           "IF(ISERROR(VLOOKUP($C"
               + (rowNo + 1)
               + ",$Q$2:$S$"
               + (savings.size() + 1)
               + ",2,FALSE)),\"\",VLOOKUP($C"
               + (rowNo + 1)
               + ",$Q$2:$S$"
               + (savings.size() + 1)
               + ",2,FALSE))");
       writeFormula(
           OPENING_BALANCE_COL,
           row,
           "IF(ISERROR(VLOOKUP($C"
               + (rowNo + 1)
               + ",$Q$2:$S$"
               + (savings.size() + 1)
               + ",3,FALSE)),\"\",VLOOKUP($C"
               + (rowNo + 1)
               + ",$Q$2:$S$"
               + (savings.size() + 1)
               + ",3,FALSE))");
     }
   } catch (Exception e) {
     logger.error(e.getMessage());
   }
 }
Exemplo n.º 17
0
 private static void createRow(
     Sheet sheet,
     Map<String, Object> rowData,
     List<String> mapping,
     List<CellStyle> styles,
     int startRowNum) {
   Row row = sheet.createRow(startRowNum);
   for (int i = 0; i < mapping.size(); i++) {
     String name = mapping.get(i);
     Object obj = rowData.get(name);
     Cell newCell = row.createCell(i);
     CellStyle style = styles.get(i);
     newCell.setCellStyle(style);
     if (obj != null) {
       if (obj instanceof Date) {
         newCell.setCellValue((Date) obj);
       } else if (obj instanceof BigDecimal) {
         double dd = ((BigDecimal) obj).doubleValue();
         newCell.setCellValue(dd);
       } else {
         newCell.setCellValue(obj.toString());
       }
     }
   }
 }
 private Result populateLoansTable(Sheet savingsTransactionSheet) {
   Result result = new Result();
   Workbook workbook = savingsTransactionSheet.getWorkbook();
   CellStyle dateCellStyle = workbook.createCellStyle();
   short df = workbook.createDataFormat().getFormat("dd/mm/yy");
   dateCellStyle.setDataFormat(df);
   int rowIndex = 1;
   Row row;
   Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
   try {
     for (CompactSavingsAccount savingsAccount : savings) {
       row = savingsTransactionSheet.createRow(rowIndex++);
       writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
       writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
       writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
       writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
       writeDate(
           LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
           row,
           savingsAccount.getTimeline().getActivatedOnDate().get(2)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(1)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(0),
           dateCellStyle);
     }
   } catch (Exception e) {
     result.addError(e.getMessage());
     logger.error(e.getMessage());
   }
   return result;
 }
Exemplo n.º 19
0
  public void insertDataToExcel(int numRow, Object[] object) {

    try {

      if (null != wb.getSheetAt(0)) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);

        if (row == null) row = aSheet.createRow((short) numRow);

        for (int i = 0; i < object.length; i++) {
          Cell csCell = row.createCell((short) i);

          CellStyle style = wb.createCellStyle();
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          style.setTopBorderColor(HSSFColor.BLACK.index);

          csCell.setCellStyle(style);

          if (object[i] != null) csCell.setCellValue(object[i].toString());
          else csCell.setCellValue("0");
        }
      }

    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
Exemplo n.º 20
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();
   }
 }
Exemplo n.º 21
0
  public void format() {

    wb = new XSSFWorkbook();

    Map styles = createStyles(wb);
    Sheet sheet = wb.createSheet("Loan Calculator");
    sheet.setPrintGridlines(false);
    sheet.setDisplayGridlines(false);
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);
    sheet.setColumnWidth(0, 768);
    sheet.setColumnWidth(1, 768);
    sheet.setColumnWidth(2, 2816);
    sheet.setColumnWidth(3, 3584);
    sheet.setColumnWidth(4, 3584);
    sheet.setColumnWidth(5, 3584);
    sheet.setColumnWidth(6, 3584);

    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(35F);
    for (int i = 1; i <= 7; i++)
      titleRow.createCell(i).setCellStyle((CellStyle) styles.get("title"));

    Cell titleCell = titleRow.getCell(2);
    titleCell.setCellValue("Simple");
  }
  public void generateReport() throws IOException {
    System.out.println("Test Report Gen");
    Sheet sheet = workbook.getSheet("sheet1");

    try {
      String strSql = "Select * from participant;";
      Sql db = new Sql();
      ResultSet rs = db.executeQuery(strSql);
      ResultSetMetaData rsMeta = rs.getMetaData();

      Row row = sheet.createRow(0);
      Cell cell;
      // get table columns which will act as title columns
      for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
        String colName = rsMeta.getColumnName(i);
        int dataType = rsMeta.getColumnType(i);
        cell = row.createCell(i - 1);
        cell.setCellValue(colName);
      }

      int i = 1;
      while (rs.next()) {
        // create row to write data to
        row = sheet.createRow(i);
        // create cells for writing data to
        for (int c = 1; c <= rsMeta.getColumnCount(); c++) {
          cell = row.createCell(c - 1);
          String colName = rsMeta.getColumnName(c);
          cell.setCellValue(rs.getString(colName));
        }

        i++;
      }

      JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated");
    } catch (SQLException ex) {
      ex.printStackTrace();
      // log Error
      FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex);
    }

    /// Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("reportTest.xls");
    workbook.write(fileOut);
    fileOut.close();
  }
Exemplo n.º 23
0
 /** @throws SQLException */
 public void fillSheet() throws SQLException {
   Row row = reportSheet.createRow(0);
   Cell cell = null;
   /** Setting head of report */
   for (int i = 0; i < reportResultSet.getMetaData().getColumnCount(); i++) {
     cell = row.createCell(i);
     cell.setCellValue(reportResultSet.getMetaData().getColumnName(i + 1));
   }
   reportResultSet.first();
   /** Filling data */
   for (int i = 1; reportResultSet.next() && i <= DEFAULT_MAX_ROW_COUNT; i++) {
     row = reportSheet.createRow(i);
     for (int j = 0; j < reportResultSet.getMetaData().getColumnCount(); j++) {
       cell = row.createCell(j);
       cell.setCellValue(reportResultSet.getString(j + 1));
     }
   }
 }
Exemplo n.º 24
0
 /**
  * Main method for writing output of calculation
  *
  * @param outputFile path to output file
  * @param finalList list with sorted containers and items
  * @param sheet sheet to create
  * @param lastRow last row for items of each container
  */
 public void writeOutput(String outputFile) {
   XSSFWorkbook outputBook = new XSSFWorkbook();
   outputSheet = outputBook.createSheet();
   outputSheet.setColumnWidth(0, 1300);
   Row headingsRow = outputSheet.createRow(0);
   setHeadings(headingsRow);
   setValues();
   writeToFile(outputFile, outputBook);
 }
  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);
    }
  }
Exemplo n.º 26
0
 protected Row getCurrentRow() {
   synchronized (syncObj) {
     Sheet s = getCurrentSheet();
     Row r = s.getRow(rowPointer);
     if (r == null) {
       r = s.createRow(rowPointer);
     }
     return r;
   }
 }
Exemplo n.º 27
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);
 }
Exemplo n.º 28
0
 /**
  * @see
  *     net.sourceforge.squirrel_sql.fw.gui.action.exportData.AbstractDataExportFileWriter#addHeaderCell(int,
  *     java.lang.String)
  */
 @Override
 protected void addHeaderCell(int colIdx, String columnName) throws Exception {
   this.withHeader = true;
   Row headerRow = sheet.getRow(0);
   if (headerRow == null) {
     headerRow = sheet.createRow(0);
   }
   Cell cell = headerRow.createCell(colIdx);
   cell.setCellValue(columnName);
 }
Exemplo n.º 29
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();
    }
  }
Exemplo n.º 30
0
  /**
   * Sheet initialization. We create as many rows as it is required to contain the crosstab.
   *
   * @param sheet The XLS sheet
   * @param json The crosstab data (it must have been enriched with the calculateDescendants method)
   * @throws JSONException
   */
  public int initSheet(Sheet sheet, CrossTab cs) throws JSONException {

    int columnsDepth = cs.getColumnsRoot().getSubTreeDepth();
    int rowsNumber = cs.getRowsRoot().getSubTreeDepth();
    // + 1 because there may be also the bottom row with the totals
    int totalRowsNumber = columnsDepth + rowsNumber + 1;
    for (int i = 0; i < totalRowsNumber + 4; i++) {
      sheet.createRow(i);
    }
    return totalRowsNumber + 4;
  }