List of usage examples for org.apache.poi.ss.usermodel Workbook close
@Override void close() throws IOException;
From source file:das.pf.io.IOExcel.java
License:Open Source License
public boolean consolidateFiles() { boolean result = false; AtomicInteger rowIndex = new AtomicInteger(3); String outputName = "DAS - " + this.out.getFileName().toString() + " consolidate.xlsx"; Workbook consolidateWb = new XSSFWorkbook(); try {/*from w w w . j a v a 2 s . co m*/ Sheet sheetConsolidate = consolidateWb.createSheet("Consolidado"); Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).sorted((p1, p2) -> { String acronym = getAcromynName(p1); String acronym2 = getAcromynName(p2); return acronym.compareToIgnoreCase(acronym2); }).forEach(p -> { try { Workbook wb = WorkbookFactory.create(p.toFile()); Sheet sheet = wb.getSheet("Procesado"); updateMessages(String.format("Copiando los datos del archivo: %s dentro del archivo: %s", p.toString(), outputName)); for (int index = 3; index < sheet.getLastRowNum(); index++) { Row row = sheet.getRow(index); Row r = sheetConsolidate.createRow(rowIndex.getAndIncrement()); row.forEach(c -> { if (c != null && c.getCellType() != Cell.CELL_TYPE_BLANK) { final Cell cell = r.createCell(c.getColumnIndex(), c.getCellType()); updateMessages( String.format("Copiando los datos de la fila: #%d", c.getRowIndex())); switch (c.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(c.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(c.getRichStringCellValue()); break; } } }); row = null; } sheet = null; wb.close(); wb = null; } catch (IOException | InvalidFormatException | EncryptedDocumentException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } }); Path path = Files.list(this.out).filter((p) -> { String name = p.toString(); return (name.endsWith(".xlsx") || name.endsWith(".xls")) && !p.getFileName().toString().equals(outputName); }).findFirst().get(); createHeadersConsolidateFile(consolidateWb, path); for (int i = 0; i < 155; i++) sheetConsolidate.autoSizeColumn(i); sheetConsolidate.setAutoFilter( CellRangeAddress.valueOf(String.format("A3:K%d", sheetConsolidate.getLastRowNum()))); try (FileOutputStream fos = new FileOutputStream(Paths.get(out.toString(), outputName).toFile())) { updateMessages(String.format("Guadando el trabajo en la ruta: '%s'", Paths.get(this.out.toString(), outputName))); consolidateWb.write(fos); result = true; } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, "Ocurrio un error al intenatr guardar el archivo consolidado", ex); } finally { consolidateWb.close(); } } catch (IOException ex) { Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex); } consolidateWb = null; return result; }
From source file:eleanalysis.SampleLibrary.java
/** * writes a report in the form of an excel spreadsheet that is exported. * Stage variable is for using a filechooser to pick where to save file. * @param myStage Stage is for saving file using FileChooser * @throws FileNotFoundException // ww w . jav a 2 s . c o m */ public void writeReport(Stage myStage) throws FileNotFoundException { FileChooser pickFile = new FileChooser(); pickFile.setInitialDirectory(new File("C:\\Users\\Yan\\Documents\\NetBeansProjects\\EleAnalysis\\")); pickFile.getExtensionFilters().addAll(new FileChooser.ExtensionFilter("XLS", "*.xls"), new FileChooser.ExtensionFilter("XLSX", "*.xlsx")); File writeFile = pickFile.showSaveDialog(myStage); FileOutputStream fileOut; fileOut = new FileOutputStream(writeFile); Workbook wb = new HSSFWorkbook(); Sheet sheet1 = wb.createSheet("WDXRF"); sheet1.setDefaultColumnWidth(15); // Create a cell space for (int i = 0; i < ElementUtils.skf.length + 5; i++) { Row tempR = sheet1.createRow(i); for (int j = 0; j <= array.size(); j++) { Cell tempC = tempR.createCell(j); } } CellStyle csCenter = wb.createCellStyle(); csCenter.setAlignment(CellStyle.ALIGN_CENTER); csCenter.setBorderTop(CellStyle.BORDER_THIN); csCenter.setBorderLeft(CellStyle.BORDER_THIN); csCenter.setBorderRight(CellStyle.BORDER_THIN); csCenter.setBorderBottom(CellStyle.BORDER_THIN); CellStyle csRight = wb.createCellStyle(); csRight.setAlignment(CellStyle.ALIGN_RIGHT); csRight.setBorderTop(CellStyle.BORDER_THIN); csRight.setBorderLeft(CellStyle.BORDER_THIN); csRight.setBorderRight(CellStyle.BORDER_THIN); csRight.setBorderBottom(CellStyle.BORDER_THIN); CellStyle csLeft = wb.createCellStyle(); csLeft.setAlignment(CellStyle.ALIGN_LEFT); csLeft.setBorderTop(CellStyle.BORDER_THIN); csLeft.setBorderLeft(CellStyle.BORDER_THIN); csLeft.setBorderRight(CellStyle.BORDER_THIN); csLeft.setBorderBottom(CellStyle.BORDER_THIN); //Top Row sheet1.getRow(0).setHeightInPoints(25); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, array.size())); sheet1.getRow(0).getCell(0).setCellValue("WDXRF Analysis"); sheet1.getRow(0).getCell(0).setCellStyle(csCenter); sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, array.size())); //Second Row sheet1.getRow(1).getCell(0).setCellValue("Conc as Wt%"); sheet1.getRow(1).getCell(0).setCellStyle(csCenter); // Third Row sheet1.getRow(2).setHeightInPoints(35); sheet1.getRow(2).getCell(0).setCellValue("Common Oxides/Oxication States"); sheet1.getRow(2).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(2).getCell(j).setCellStyle(csLeft); sheet1.getRow(2).getCell(j).setCellValue(array.get(j - 1).getName()); } //Fourth Row sheet1.getRow(3).getCell(0).setCellValue("% Detectable"); sheet1.getRow(3).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(3).getCell(j).setCellValue("0.00"); sheet1.getRow(3).getCell(j).setCellStyle(csLeft); } //Fifth Row sheet1.addMergedRegion(new CellRangeAddress(4, 4, 0, array.size())); sheet1.getRow(4).getCell(0).setCellValue("Results Normalized with Respect to Detectable Concentration"); sheet1.getRow(4).getCell(0).setCellStyle(csCenter); //Rows 6 and beyond. Prints element list and defaults values to 0 for (int i = 5; i < ElementUtils.skf.length + 5; i++) { sheet1.getRow(i).getCell(0).setCellValue(ElementUtils.skf[i - 5]); sheet1.getRow(i).getCell(0).setCellStyle(csLeft); for (int j = 1; j <= array.size(); j++) { sheet1.getRow(i).getCell(j).setCellValue("0.0"); sheet1.getRow(i).getCell(j).setCellStyle(csRight); } } // Copies values in SampleLibrary array into report for (int i = 0; i < array.size(); i++) { List<Element> eleArray = array.get(i).getArrayCopy(); for (int j = 0; j < ElementUtils.skf.length; j++) { for (int k = 0; k < eleArray.size(); k++) { if (ElementUtils.skf[j].contains(eleArray.get(k).getBaseElement())) sheet1.getRow(j + 5).getCell(i + 1).setCellValue(eleArray.get(k).getConcWeight()); } } } try { wb.write(fileOut); wb.close(); fileOut.close(); } catch (IOException ex) { Logger.getLogger(SampleLibrary.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:eu.alpinweiss.filegen.service.impl.GenerateXlsxFileServiceImpl.java
License:Apache License
public void generateExcel(String excelFilename, int rowCount, List<FieldDefinition> fieldDefinitionList, int sheetCount) { long startTime = new Date().getTime(); outputWriterHolder.writeValueInLine("Excel data generation started"); Workbook wb = new SXSSFWorkbook(); try {// www. j a v a 2s . c o m CellStyle cs = wb.createCellStyle(); cs.setFillForegroundColor(IndexedColors.LIME.getIndex()); cs.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); Font f = wb.createFont(); f.setBoldweight(Font.BOLDWEIGHT_BOLD); f.setFontHeightInPoints((short) 12); cs.setFont(f); SXSSFSheet sheet1 = (SXSSFSheet) wb.createSheet("dataSheet"); int columnCount = fieldDefinitionList.size(); Map<Integer, Input2TableInfo> input2TableInfoMap = new LinkedHashMap<>(columnCount); for (int i = 0; i < columnCount; i++) { Input2TableInfo input2TableInfo = new Input2TableInfo(); FieldDefinition fieldDefinition = fieldDefinitionList.get(i); input2TableInfo.setFieldText(fieldDefinition.getFieldName()); input2TableInfo.setFieldDefinition(fieldDefinition); input2TableInfo.initCellStyle(wb); input2TableInfo.initGenerator(); input2TableInfoMap.put(i, input2TableInfo); } if (sheetCount > 1) { CountDownLatch startSignal = new CountDownLatch(1); CountDownLatch doneSignal; doneSignal = new CountDownLatch(sheetCount); ParameterVault parameterVault = new DefaultParameterVault(0, rowCount); SheetProcessor stringProcessorSheet1 = new SheetProcessor(parameterVault, startSignal, doneSignal, cs, sheet1, columnCount, input2TableInfoMap, outputWriterHolder); new Thread(stringProcessorSheet1, "Processor-" + sheetCount).start(); for (int i = 0; i < sheetCount - 1; i++) { SXSSFSheet sheet = (SXSSFSheet) wb.createSheet("dataSheet_" + i); ParameterVault parameterVaultRest = new DefaultParameterVault(i + 1, rowCount); SheetProcessor stringProcessor = new SheetProcessor(parameterVaultRest, startSignal, doneSignal, cs, sheet, columnCount, input2TableInfoMap, outputWriterHolder); new Thread(stringProcessor, "Processor-" + i).start(); } startSignal.countDown(); doneSignal.await(); } else { ParameterVault parameterVault = new DefaultParameterVault(0, rowCount); new SheetProcessor(outputWriterHolder).generateSheetData(parameterVault, cs, sheet1, columnCount, input2TableInfoMap); } outputWriterHolder.writeValueInLine("Excel data generation finished."); long generationTime = new Date().getTime(); outputWriterHolder.writeValueInLine("Time used " + ((generationTime - startTime) / 1000) + " sec"); outputWriterHolder.writeValueInLine("Writing to file."); FileOutputStream fileOut = new FileOutputStream(excelFilename.trim()); wb.write(fileOut); fileOut.close(); long writeTime = new Date().getTime(); outputWriterHolder.writeValueInLine("Time used " + ((writeTime - generationTime) / 1000) + " sec"); outputWriterHolder.writeValueInLine("Total time used " + ((writeTime - startTime) / 1000) + " sec"); outputWriterHolder.writeValueInLine("Done"); } catch (Exception e) { LOGGER.error(e.getMessage(), e); } finally { try { wb.close(); } catch (IOException e) { LOGGER.error(e.getMessage(), e); } } }
From source file:fi.thl.pivot.export.XlsxExporter.java
private void doExport(Model model, OutputStream out) throws IOException { Map<String, ?> params = model.asMap(); Workbook wb = new XSSFWorkbook(); createExportStyles(wb);// w ww. j a va 2 s . co m Sheet sheet = wb.createSheet( WorkbookUtil.createSafeSheetName(((Label) params.get("cubeLabel")).getValue(language))); Pivot pivot = (Pivot) params.get("pivot"); int rowNumber = 0; boolean showCodes = params.containsKey("sc"); rowNumber = createColumnHeaders(pivot, sheet, showCodes); rowNumber = printData(sheet, pivot, rowNumber, showCodes); mergeRowHeaders(sheet, pivot); rowNumber = printFilters(params, sheet, rowNumber, pivot.getColumnCount() + pivot.getColumns().size()); printCopyrightNotice(sheet, rowNumber, params, pivot.getColumnCount() + pivot.getColumns().size()); printCurrentMeasureIfOnlyOneMeasureShown(params, sheet, pivot); mergeTopLeftCorner(sheet, pivot); autosizeColumns(sheet, pivot); sheet.createFreezePane(pivot.getRows().size(), pivot.getColumns().size()); wb.write(out); wb.close(); }
From source file:fr.paris.lutece.plugins.appointment.service.ClosingDayService.java
License:Open Source License
/** * Import the closing dates of a given file * //from w w w . j av a 2s .co m * @param item * the file in input * @return the list of the closing dates in the file * @throws IOException * if error during reading file */ public static List<LocalDate> getImportClosingDays(FileItem item) throws IOException { HashSet<LocalDate> listDays = new HashSet<LocalDate>(); FileInputStream fis = null; Workbook workbook = null; String strExtension = FilenameUtils.getExtension(item.getName()); if (StringUtils.equals(MARK_EXCEL_EXTENSION_XLSX, strExtension)) { try { fis = (FileInputStream) item.getInputStream(); // Using XSSF for xlsx format, for xls use HSSF workbook = new XSSFWorkbook(fis); int numberOfSheets = workbook.getNumberOfSheets(); // looping over each workbook sheet for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); Iterator<Row> rowIterator = sheet.iterator(); // iterating over each row while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); if (row.getRowNum() > 1) { Iterator<Cell> cellIterator = row.cellIterator(); // Iterating over each cell (column wise) in a // particular row. while (cellIterator.hasNext()) { Cell cell = (Cell) cellIterator.next(); // The Cell Containing String will is name. if (cell.getColumnIndex() == 3) { String strdate = StringUtils.EMPTY; if (cell.getCellType() == 0) { Instant instant = cell.getDateCellValue().toInstant(); LocalDate localDate = instant.atZone(ZoneId.systemDefault()).toLocalDate(); strdate = localDate.format(Utilities.getFormatter()); } if (StringUtils.isNotEmpty(strdate) && strdate.matches(MARK_FORMAT_DATE_REGEX)) { LocalDate date = LocalDate.parse(strdate, Utilities.getFormatter()); listDays.add(date); } } } } } } } finally { if (fis != null) { fis.close(); } if (workbook != null) { workbook.close(); } } } return new ArrayList<LocalDate>(listDays); }
From source file:github.srlee309.lessWrongBookCreator.excelReader.SummaryFileReader.java
License:Open Source License
/** * Builds list of {@link BookSummarySection}s from a given excel file * @param file to use to extract the BookSummarySections * @return bookSummarySections//from w ww. j a v a 2 s . c o m * @throws ExcelExtractionException if unable to read from the given file */ public ArrayList<BookSummarySection> getSummarySections(File file) throws ExcelExtractionException { if (file == null) { throw new ExcelExtractionException("Input file was null and cannot be read"); } else { Workbook wb = null; ArrayList<BookSummarySection> bookSummarySections = null; try { wb = WorkbookFactory.create(file); bookSummarySections = getBookSummarySections(wb); } catch (FileNotFoundException e) { logger.error("", e); throw new ExcelExtractionException(file.getName() + " not found"); } catch (IOException e) { logger.error("", e); throw new ExcelExtractionException("IOException reading from " + file.getName()); } catch (EncryptedDocumentException e) { logger.error("", e); throw new ExcelExtractionException("EncryptedDocumentException reading from " + file.getName()); } catch (InvalidFormatException e) { logger.error("", e); throw new ExcelExtractionException("InvalidFormatException reading from " + file.getName()); } finally { if (wb != null) { try { wb.close(); } catch (IOException ex) { logger.error("", ex); throw new ExcelExtractionException( "IOException with " + file.getName() + ". If the file is open, close it."); } } } return bookSummarySections; } }
From source file:hjow.hgtable.util.XLSXUtil.java
License:Apache License
/** * <p>XLSX ? ? ?? . ? ? ?? ?? , ? ? ?? ? ? ?? ?.</p> * /*from ww w .j a va 2 s .co m*/ * @param file : XLSX ? * @return ? ? */ public static List<TableSet> toTableSets(File file) { List<TableSet> tableSets = new Vector<TableSet>(); org.apache.poi.ss.usermodel.Workbook workbook = null; if (file == null) throw new NullPointerException(Manager.applyStringTable("Please select file !!")); if (!file.exists()) throw new NullPointerException(Manager.applyStringTable("File") + " " + file.getAbsolutePath() + " " + Manager.applyStringTable("is not exist")); boolean isHead = true; int rowNum = 0; int cellNum = 0; int cellCount = 0; FileInputStream fileStream = null; try { if (file.getAbsolutePath().endsWith(".xlsx") || file.getAbsolutePath().endsWith(".XLSX")) { workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(file); } else if (file.getAbsolutePath().endsWith(".xls") || file.getAbsolutePath().endsWith(".XLS")) { fileStream = new FileInputStream(file); workbook = new org.apache.poi.hssf.usermodel.HSSFWorkbook(fileStream); } org.apache.poi.ss.usermodel.FormulaEvaluator evals = workbook.getCreationHelper() .createFormulaEvaluator(); org.apache.poi.ss.usermodel.Sheet sheet = null; for (int x = 0; x < workbook.getNumberOfSheets(); x++) { TableSet newTableSet = new DefaultTableSet(); newTableSet.setColumns(new Vector<Column>()); sheet = workbook.getSheetAt(x); newTableSet.setName(sheet.getSheetName()); rowNum = 0; isHead = true; String targetData = null; for (org.apache.poi.ss.usermodel.Row row : sheet) { cellNum = 0; for (org.apache.poi.ss.usermodel.Cell cell : row) { try { if (cellNum >= cellCount) { throw new IndexOutOfBoundsException( Manager.applyStringTable("There are some cells not have their heads") + ", " + Manager.applyStringTable("Head count") + " : " + cellCount + ", " + Manager.applyStringTable("Cell Number") + " : " + cellNum); } switch (cell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: if (isHead) { newTableSet.getColumns().add(new Column( cell.getRichStringCellValue().getString(), Column.TYPE_STRING)); } else { targetData = cell.getRichStringCellValue().getString(); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_DATE)); } else { targetData = String.valueOf(cell.getDateCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } else { if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { double values = cell.getNumericCellValue(); double intPart = values - ((double) ((int) values)); if (intPart == 0.0) { targetData = String.valueOf(((int) values)); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_INTEGER); } else { targetData = String.valueOf(values); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_BOOLEAN)); } else { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: if (isHead) { newTableSet.getColumns().add(new Column( String.valueOf(cell.getStringCellValue()), Column.TYPE_NUMERIC)); } else { if (evals.evaluateFormulaCell(cell) == 0) { targetData = String.valueOf(cell.getNumericCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_NUMERIC); } else if (evals.evaluateFormulaCell(cell) == 1) { targetData = String.valueOf(cell.getStringCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_STRING); } else if (evals.evaluateFormulaCell(cell) == 4) { targetData = String.valueOf(cell.getBooleanCellValue()); newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BOOLEAN); } else { targetData = String.valueOf(cell.getCellFormula()); newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } } break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { targetData = ""; newTableSet.getColumns().get(cellNum).setType(Column.TYPE_BLANK); } break; default: if (isHead) { newTableSet.getColumns().add(new Column("", Column.TYPE_STRING)); } else { try { targetData = cell.getStringCellValue(); } catch (Exception e1) { e1.printStackTrace(); } newTableSet.getColumns().get(cellNum).setType(cell.getCellType()); } break; } if (isHead) { cellCount++; } else { while (rowNum > 0 && newTableSet.getColumns().get(cellNum).getData().size() < rowNum) { newTableSet.getColumns().get(cellNum).getData().add(""); } if (targetData != null) newTableSet.getColumns().get(cellNum).getData().add(targetData); else { newTableSet.getColumns().get(cellNum).getData().add(""); } } } catch (ArrayIndexOutOfBoundsException e1) { StringBuffer err = new StringBuffer(""); for (StackTraceElement errEl : e1.getStackTrace()) { err = err.append("\t " + errEl + "\n"); } String cellObject = null; try { cellObject = cell.getStringCellValue(); } catch (Exception e2) { } throw new ArrayIndexOutOfBoundsException( Manager.applyStringTable("Array index out of range") + " <- " + Manager.applyStringTable("Reading xlsx file") + " : " + file.getName() + ", " + sheet.getSheetName() + "\n" + Manager.applyStringTable("On") + " " + Manager.applyStringTable("Row") + " " + rowNum + ", " + Manager.applyStringTable("Cell") + " " + cellNum + ", " + Manager.applyStringTable("Value") + " : " + String.valueOf(cellObject) + "\n " + Manager.applyStringTable("<-\n") + err + "\n " + Manager.applyStringTable("Original Message") + "...\n" + e1.getMessage() + "\n" + Manager.applyStringTable("End")); } cellNum++; } isHead = false; rowNum++; } fillTableSet(newTableSet); newTableSet.removeEmptyColumn(true); tableSets.add(newTableSet); } return tableSets; } catch (Throwable e) { if (Main.MODE >= DebuggingUtil.DEBUG) e.printStackTrace(); Main.logError(e, Manager.applyStringTable("On reading xlsx") + " : " + file + "\n" + Manager.applyStringTable("At rownum") + " " + rowNum + ", " + Manager.applyStringTable("cellnum") + " " + cellNum); return null; } finally { try { workbook.close(); } catch (Throwable e) { } try { if (fileStream != null) fileStream.close(); } catch (Throwable e) { } } }
From source file:hjow.hgtable.util.XLSXUtil.java
License:Apache License
/** * <p>? ? XLSX ? ? .</p>//from w ww . j ava2 s. c om * * @param tableSet : ? ? * @param file : ? ? */ public static void save(TableSet tableSet, File file) { org.apache.poi.ss.usermodel.Workbook workbook = null; if (file == null) throw new NullPointerException(Manager.applyStringTable("Please select file !!")); FileOutputStream fileStream = null; ChainOutputStream chainStream = null; try { String targetPath = StreamUtil.getDirectoryPathOfFile(file); File dir = new File(targetPath); // Main.println(dir); if (!dir.exists()) dir.mkdir(); } catch (Throwable e) { Main.logError(e, "On mkdir on saving xlsx"); } try { fileStream = new FileOutputStream(file); chainStream = new ChainOutputStream(fileStream); StreamUtil.additionalSetting(chainStream); workbook = new org.apache.poi.xssf.streaming.SXSSFWorkbook(); // workbook = new org.apache.poi.xssf.usermodel.XSSFWorkbook(); org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet(); org.apache.poi.ss.usermodel.Row headerRow = sheet.createRow(0); for (int i = 0; i < tableSet.getColumns().size(); i++) { org.apache.poi.ss.usermodel.Cell headerCell = headerRow.createCell(i); headerCell.setCellValue(tableSet.getColumns().get(i).getName()); } for (int i = 0; i < tableSet.getRecordCount(); i++) { org.apache.poi.ss.usermodel.Row row = sheet.createRow(i + 1); for (int j = 0; j < tableSet.getColumns().size(); j++) { org.apache.poi.ss.usermodel.Cell cell = row.createCell(j); if (Column.TYPE_STRING == tableSet.getColumns().get(j).getType()) { cell.setCellValue(tableSet.getColumns().get(j).getData().get(i)); } else if (Column.TYPE_NUMERIC == tableSet.getColumns().get(j).getType()) { cell.setCellValue(Double.parseDouble(tableSet.getColumns().get(j).getData().get(i))); } else if (Column.TYPE_DATE == tableSet.getColumns().get(j).getType()) { cell.setCellValue(tableSet.getColumns().get(j).getData().get(i)); } else if (Column.TYPE_BOOLEAN == tableSet.getColumns().get(j).getType()) { cell.setCellValue(DataUtil.parseBoolean(tableSet.getColumns().get(j).getData().get(i))); } else if (Column.TYPE_BLANK == tableSet.getColumns().get(j).getType()) { cell.setCellValue(""); } else { cell.setCellValue(tableSet.getColumns().get(j).getData().get(i)); } } } workbook.write(chainStream.getOutputStream()); workbook.close(); } catch (Throwable e) { e.printStackTrace(); } finally { try { workbook.close(); } catch (Throwable e) { } try { chainStream.close(); } catch (Throwable e) { } try { fileStream.close(); } catch (Throwable e) { } } }
From source file:javaapp.CompareOpenClose.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();/*from ww w . ja v a 2 s. c o m*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.println(rpps+"|"+dval+"|"+cval); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ParseCreditorTransactionsData.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();/*from w w w. ja v a 2 s . c o m*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 32 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 32) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("") || filter.equalsIgnoreCase("-")) { System.out.print(rpps); System.out.println(filter); continue; } System.out.print(filter); System.out.println(rpps); } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }