List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * @param filename/* w w w. j a v a2s . c o m*/ * @param sheetNo * @param startindex * @param importDao * @return * @throws ServiceException */ public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao, HibernateTransactionManager txnManager) throws ServiceException { boolean commitedEx = false; DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("import_Tx"); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus status = txnManager.getTransaction(def); Session session = txnManager.getSessionFactory().getCurrentSession(); try { String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df_full); int maxRow = sheet.getLastRowNum(); int maxCol = 0; String tableName = importDao.getTableName(filename); int flushCounter = 0; for (int i = startindex; i <= maxRow; i++) { XSSFRow row = sheet.getRow(i); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); //Column Count } ArrayList<String> dataArray = new ArrayList<String>(); JSONObject dataObj = new JSONObject(); for (int j = 0; j < maxCol; j++) { XSSFCell cell = row.getCell(j); String val = null; if (cell == null) { dataArray.add(val); continue; } String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } dataObj.put(colHeader, val); dataArray.add(val); //Collect row data } //Insert Query if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty) importDao.dumpFileRow(tableName, dataArray.toArray()); if (flushCounter % 30 == 0) { session.flush(); session.clear(); } flushCounter++; } } try { txnManager.commit(status); } catch (Exception ex) { commitedEx = true; throw ex; } } catch (IOException ex) { throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex); } catch (Exception ex) { if (!commitedEx) { //if exception occurs during commit then dont call rollback txnManager.rollback(status); } throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex); } }
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileTransformed() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row; String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i);/* w w w . j a v a2s . co m*/ for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } XSSFWorkbook transformedWB = new XSSFWorkbook(); transformedWB.createSheet(); XSSFSheet transformedS = transformedWB.getSheetAt(0); for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { transformedS.createRow(i); } /* elkezdnk vgigmenni az alap sheeten ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre */ ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum()).setCellValue(sum); } } for (int index : matchingIndexes) { for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); row.getCell(index).setCellValue(3.14159); } /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { if(i == index){ for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { row = sheet.getRow(j); transformedS.getRow(j).createCell(transformedColumnCount).setCellValue( row.getCell(i).getRawValue() ); } transformedColumnCount++; } }*/ } int columnsInTransformed = 0; for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { String cellValue = row.getCell(j).getRawValue(); if (!cellValue.equals("3.14159")) { transformedS.getRow(i).createCell(columnsInTransformed); transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue); columnsInTransformed++; } } columnsInTransformed = 0; } row = transformedS.getRow(0); row.createCell(row.getLastCellNum()).setCellValue(atcMask); File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); transformedWB.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.mycompany.owl.fxml.FXMLFilterController.java
@FXML public void saveFileFiltered() throws FileNotFoundException, IOException { FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream); XSSFSheet sheet = workbookToModify.getSheetAt(0); XSSFRow row; String atcMask = getATCMask(); ArrayList<String> firstRowCells = new ArrayList<>(); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i);/*ww w .j ava2 s . c om*/ for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { System.out.print(row.getCell(j).getRawValue() + "\t"); if (i == 0) firstRowCells.add(row.getCell(j).getRawValue()); } System.out.println(""); } row = sheet.getRow(0); row.createCell(row.getLastCellNum()).setCellValue("ATC mask:"); row.createCell(row.getLastCellNum()).setCellValue(atcMask); ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells); System.out.println("SUMS"); for (int i = 0; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int sum = 0; for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { if (i != 0 && j != 0) { for (int index : matchingIndexes) { if (index == j) { sum += Integer.valueOf(row.getCell(j).getRawValue()); } } } } System.out.println(sum); if (i > 0) { row = sheet.getRow(i); row.createCell(row.getLastCellNum() + 1).setCellValue(sum); } } File file = fileChooser.showSaveDialog(new Stage()); if (file != null) { try { FileOutputStream fop = new FileOutputStream(file); workbookToModify.write(fop); fop.close(); } catch (Exception e) { System.out.println("Exception: " + e.getMessage()); } } }
From source file:com.yanglb.utilitys.codegen.core.reader.impl.TableReaderImpl.java
License:Apache License
/** * ?DBSheet/*from ww w . java2 s .c o m*/ * @throws CodeGenException */ @Override protected TableModel onReader(XSSFSheet sheet) throws CodeGenException { TableModel model = super.onReader(sheet, TableModel.class); // KEY String[] keys = null; List<String> listKey = new ArrayList<String>(); Map<String, String> curValue = null; // ? for (int rowNo = this.startRowNo; rowNo <= sheet.getLastRowNum(); rowNo++) { XSSFRow row = sheet.getRow(rowNo); // KEY if (rowNo == this.startRowNo) { keys = new String[row.getLastCellNum()]; } else { curValue = new HashMap<String, String>(); } // ? ?? boolean allBlank = true; // ? if (row == null) { throw new CodeGenException(String.format("error: sheet(%s), row(%d)", sheet.getSheetName(), rowNo)); } for (int colNo = this.startColNo; colNo < row.getLastCellNum(); colNo++) { XSSFCell cell = row.getCell(colNo); if (cell == null) { throw new CodeGenException(String.format("error: sheet(%s), row(%d), col(%d)", sheet.getSheetName(), rowNo, colNo)); } if (cell.getCellType() != XSSFCell.CELL_TYPE_BLANK && cell.getCellType() != XSSFCell.CELL_TYPE_ERROR) { allBlank = false; } String value = this.getCellStringValue(cell); // KEY if (rowNo == this.startRowNo) { if (StringUtility.isNullOrEmpty(value) || cell.getCellType() == XSSFCell.CELL_TYPE_BLANK || "-".equals(value)) { // / continue; } keys[colNo] = value; listKey.add(value); } else { if (colNo >= keys.length) continue; // String key = keys[colNo]; if (!StringUtility.isNullOrEmpty(key)) { curValue.put(keys[colNo], value); } } } // ? if (!allBlank && curValue != null) { model.insert(curValue); } } // model.setColumns((String[]) listKey.toArray(new String[0])); return model; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLSXWithHeader(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0);//w w w . j a v a 2 s . c o m List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; } for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLSX(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(inputStream); XSSFSheet sheet = wb.getSheetAt(0);//from w w w.j ava 2 s . com List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; } for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) { XSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { XSSFRow sourceRow = worksheet.getRow(sourceRowNum); worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false); XSSFRow newRow = worksheet.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;//from w w w. ja va 2 s. c o m continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // Set the cell data type newCell.setCellType(oldCell.getCellType()); } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } newRow.setHeight(sourceRow.getHeight()); return newRow; }
From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java
/** * Gets a XLSX file and parse it/*from w w w .ja v a2 s. com*/ * @param file The XLSX File that you want to get parsed * @return A ArrayList where the first object is a Array containing the Data and the Second the Header */ public ArrayList<Object> parseXLSXFile(File file) { int index = -1; XSSFWorkbook workbook = null; try { try { FileInputStream inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (IOException ex) { ex.printStackTrace(); } assert workbook != null; String[] strings = new String[workbook.getNumberOfSheets()]; //get all sheet names from selected workbook for (int i = 0; i < strings.length; i++) { strings[i] = workbook.getSheetName(i); } JFrame frame = new JFrame("Input Dialog"); String selectedsheet = (String) JOptionPane.showInputDialog(frame, "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null, strings, strings[0]); if (selectedsheet != null) { for (int i = 0; i < strings.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet)) index = i; } XSSFSheet sheet = workbook.getSheetAt(index); XSSFRow row = sheet.getRow(0); if (row != null) { headers = new String[row.getLastCellNum()]; for (int i = 0; i < row.getLastCellNum(); i++) { headers[i] = row.getCell(i).toString(); } } data = new String[sheet.getLastRowNum()][]; for (int j = 1; j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); int rowCount = row.getLastCellNum(); String[] dataRow = new String[rowCount]; for (int i = 0; i < rowCount; i++) { XSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK); dataRow[i] = cell.toString(); } data[j - 1] = dataRow; } } else { return null; } } catch (Exception e) { e.printStackTrace(); } ArrayList<Object> returnData = new ArrayList<>(); returnData.add(data); returnData.add(headers); return returnData; }
From source file:edu.vt.vbi.patric.common.ExcelHelper.java
License:Apache License
/** * This method automatically sets the column widths How: Measures the character length of the text in header cell of a column. Max column length * is either the title length or the title length *4 */// w w w. ja va 2 s . c o m public void setColWidths() { if (wb == null) { int margin = 4; XSSFSheet sheet = xwb.getSheetAt(0); XSSFRow row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { sheet.setColumnWidth(i, (decideXColumnWidth(sheet, i) + margin) * 256); } } else { int margin = 4; Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { sheet.setColumnWidth(i, (decideColumnWidth(sheet, i) + margin) * 256); } } }
From source file:eu.alpinweiss.filegen.command.steps.impl.ReadInputParametersStepImpl.java
License:Apache License
@Override public void execute(Model model) { String parameter = model.getParameter(INPUT_PARAMETER); model.getFieldDefinitionList().clear(); try {//from ww w.j a v a 2 s . co m FileInputStream file = new FileInputStream(new File(parameter)); try (XSSFWorkbook workbook = new XSSFWorkbook(file)) { XSSFSheet sheet = workbook.getSheetAt(0); int iterationCount = readIterationCount(sheet); String lineSeparator = readLineSeparator(sheet); String outputFileName = readOutputFileName(sheet); int sheetCount = readSheetCount(sheet); outputWriterHolder .writeValueInLine("Iterations: " + iterationCount + " lineSeparator: " + lineSeparator); List<Object[]> fields = new ArrayList<>(sheet.getLastRowNum() - 4); for (int i = 5; i <= sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); Object[] fieldDefinition = new Object[row.getLastCellNum()]; for (int y = 0; y < row.getLastCellNum(); y++) { XSSFCell cell = row.getCell(y); if (cell == null) { fieldDefinition[y] = null; break; } cell.setCellType(Cell.CELL_TYPE_STRING); fieldDefinition[y] = cell.toString(); } fields.add(fieldDefinition); } file.close(); for (Object[] field : fields) { FieldDefinition fieldDefinition = new FieldDefinition(); String name = getStringName(field[0]); if (name == null || "".equals(name)) { break; } fieldDefinition.setFieldName(name); String fieldType = (String) field[1]; fieldDefinition.setType( fieldType != null ? FieldType.valueOf(fieldType.toUpperCase()) : FieldType.STRING); String fieldNeedToGenerate = (String) field[2]; fieldDefinition.setGenerate( fieldNeedToGenerate != null ? Generate.valueOf(fieldNeedToGenerate.toUpperCase()) : Generate.N); if ((field.length > 3)) { if (field[3] != null && field[3] instanceof Number) { fieldDefinition.setPattern(field[3].toString()); } else { fieldDefinition.setPattern((String) field[3]); } } model.getFieldDefinitionList().add(fieldDefinition); } model.setRowCount(iterationCount); model.setLineSeparator(lineSeparator); model.setOutputFileName(outputFileName); model.setDataStorageCount(sheetCount); outputWriterHolder.writeValueInLine(""); workbook.close(); } } catch (FileNotFoundException e) { LOGGER.error("Can't read input parameters file", e); } catch (IOException e) { LOGGER.error("Error while reading xlsx file", e); } }