List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getPhysicalNumberOfRows
@Override public int getPhysicalNumberOfRows()
From source file:br.com.pontocontrol.controleponto.controller.impl.ExportadorXLSController.java
@Override public boolean extrair(FolhaMensalPonto folhaMensal, String outputFileDir) { Calendar data = Calendar.getInstance(); data.set(Calendar.MONTH, folhaMensal.getMes()); data.set(Calendar.YEAR, folhaMensal.getAno()); final String nomeArquivo = format("%s-%s_%s.xls", SessaoManager.getInstance().getUsuarioAutenticado().getLogin(), formatDate("yyyy-MMMM", data.getTime()), formatDate("yyyyMMddHHmmssSSS", new Date())); final String filePath = format("%s/%s", outputFileDir, nomeArquivo); File arquivoXLS = new File(filePath); FileOutputStream fos = null;/* w w w . ja v a 2 s . c om*/ try { arquivoXLS.createNewFile(); fos = new FileOutputStream(arquivoXLS); HSSFWorkbook workbook = HSSFWorkbook.create(InternalWorkbook.createWorkbook()); final String nomePlanilha = formatDate("MMM-yy", data.getTime()); HSSFSheet planilha = workbook.createSheet(nomePlanilha); //TOTAL ROW HSSFRow totalRow = planilha.createRow(0); totalRow.createCell(CELL_INDEX.TOTAL_ROW.TOTAL_TXT).setCellValue("Total:"); totalRow.createCell(CELL_INDEX.TOTAL_ROW.TOTAL_VALUE).setCellValue(folhaMensal.calcularTotalMensal()); totalRow.createCell(CELL_INDEX.TOTAL_ROW.VAR_TXT).setCellValue("Variao:"); totalRow.createCell(CELL_INDEX.TOTAL_ROW.VAR_VALUE).setCellValue(folhaMensal.calcularVariacaoMensal()); //HEADER HSSFRow headerRow = planilha.createRow(1); headerRow.createCell(CELL_INDEX.DIA).setCellValue("Dia"); headerRow.createCell(CELL_INDEX.ENTRADA).setCellValue("Entrada"); headerRow.createCell(CELL_INDEX.ALMOCO).setCellValue("Almoo"); headerRow.createCell(CELL_INDEX.RETORNO).setCellValue("Retorno"); headerRow.createCell(CELL_INDEX.SAIDA).setCellValue("Sada"); headerRow.createCell(CELL_INDEX.TOTAL_EXP).setCellValue("Expediente"); headerRow.createCell(CELL_INDEX.VARIACAO).setCellValue("Variao"); formatHeaderRow(workbook, headerRow); Calendar cal = Calendar.getInstance(); cal.set(Calendar.YEAR, folhaMensal.getAno()); cal.set(Calendar.MONTH, folhaMensal.getMes()); for (int dia = 1; dia <= cal.getActualMaximum(Calendar.DAY_OF_MONTH); dia++) { int i = planilha.getPhysicalNumberOfRows(); HSSFRow row = planilha.createRow(i); cal.set(Calendar.DAY_OF_MONTH, dia); row.createCell(CELL_INDEX.DIA).setCellValue(formatDate("MMM dd, EEE", cal.getTime())); RegistroDiarioPonto reg = folhaMensal.getRegistros().get(dia); if (reg != null) { row.createCell(CELL_INDEX.ENTRADA) .setCellValue(reg.getEntrada() != null ? reg.getEntrada().format(TIME_FORMATTER) : ""); row.createCell(CELL_INDEX.ALMOCO) .setCellValue(reg.getAlmoco() != null ? reg.getAlmoco().format(TIME_FORMATTER) : ""); row.createCell(CELL_INDEX.RETORNO) .setCellValue(reg.getRetorno() != null ? reg.getRetorno().format(TIME_FORMATTER) : ""); row.createCell(CELL_INDEX.SAIDA) .setCellValue(reg.getSaida() != null ? reg.getSaida().format(TIME_FORMATTER) : ""); row.createCell(CELL_INDEX.TOTAL_EXP).setCellValue( reg.isRegistroDiarioCompleto() ? reg.calcularTotalExpedienteAsNumber() : 0); row.createCell(CELL_INDEX.VARIACAO) .setCellValue(reg.isRegistroDiarioCompleto() ? reg.calcularVariacaoExpediente() : 0); } else { row.createCell(CELL_INDEX.ENTRADA).setCellValue(""); row.createCell(CELL_INDEX.ALMOCO).setCellValue(""); row.createCell(CELL_INDEX.RETORNO).setCellValue(""); row.createCell(CELL_INDEX.SAIDA).setCellValue(""); row.createCell(CELL_INDEX.TOTAL_EXP).setCellValue(""); row.createCell(CELL_INDEX.VARIACAO).setCellValue(""); } if (SessaoManager.getInstance().getUsuarioAutenticado().checarSeDiaExpediente(cal)) { formatRow(workbook, row); } else { formatHeaderRow(workbook, row); } } for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { planilha.autoSizeColumn(i); } workbook.write(fos); fos.flush(); return true; } catch (IOException ex) { LOG.log(Level.SEVERE, "Erro ao criar arquivo XLS de sada", ex); return false; } finally { IOUtils.closeQuietly(fos); } }
From source file:cdc.impl.datasource.office.SheetsIterator.java
License:LGPL
public int countRecords() { int total = 0; for (int i = 0; i < sheetNames.length; i++) { HSSFSheet sheet = workbook.getSheet(sheetNames[i]); if (sheet != null) { total += sheet.getPhysicalNumberOfRows() - 1; }/*from w ww . jav a2s .co m*/ } return total; }
From source file:citibob.reports.PoiXlsWriter.java
License:Open Source License
/** Don't do fixup between xrow0 and xrow1, non-inclusive. */ public void fixupFormulas(HSSFSheet sheet, int rowIx, int n, int xrow0, int xrow1) { //System.out.println("--------- fixupFormulas(" + rowIx + "," + n + ")"); int prows = sheet.getPhysicalNumberOfRows(); int pr = 0;//from w w w .j a va 2 s .c o m for (int r = 0; pr < prows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) continue; ++pr; int pcells = row.getPhysicalNumberOfCells(); int pc = 0; for (int c = 0; pc < pcells; ++c) { HSSFCell cell = row.getCell((short) c); if (cell == null) continue; ++pc; // Fixup the formula if (cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) continue; //System.out.println("Formula cell: " + cell.getCellFormula()); //System.out.println(" ncells = " + row.getLastCellNum()); FormulaParser fp = new FormulaParser(cell.getCellFormula(), wb.getWorkbook()); fp.parse(); Ptg[] ptg = fp.getRPNPtg(); for (int i = 0; i < ptg.length; ++i) { Ptg pi = ptg[i]; // if (pi.getPtgClass() != Ptg.CLASS_REF) continue; if (pi instanceof AreaPtg) { //System.out.println("Fixing area: " + pi); AreaPtg pp = (AreaPtg) pi; if (pp.getFirstRow() >= rowIx) pp.setFirstRow((short) (pp.getFirstRow() + n)); if (pp.getLastRow() >= rowIx) { pp.setLastRow((short) (pp.getLastRow() + n)); } } else if (pi instanceof ReferencePtg) { ReferencePtg pp = (ReferencePtg) pi; if (r >= xrow0 && r < xrow1) { if (pp.getRow() <= r && pp.isRowRelative()) pp.setRow((short) (r + pp.getRow() - rowIx)); } else if (pp.getRow() >= rowIx) { pp.setRow((short) (pp.getRow() + n)); } } } // Done fixing the formula; set it back String fstr = fp.toFormulaString(wb.getWorkbook(), ptg); //System.out.println("replacing formula string (" + r + "," + c + "): " + fstr); cell.setCellFormula(fstr); } } }
From source file:citibob.reports.PoiXlsWriter.java
License:Open Source License
/** Creates a new instance of PoiTest */ public void replaceHolders(java.util.Map<String, Object> models) //throws Exception { for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); // Iterate through all rows and cols of the sheet int prows = sheet.getPhysicalNumberOfRows(); int pr = 0; for (int r = 0; pr < prows; r++) { System.out.println(r + ", " + pr + ", " + prows); HSSFRow row = sheet.getRow(r); if (row == null) continue; ++pr;//from ww w. j ava2 s.co m int pcells = row.getPhysicalNumberOfCells(); int pc = 0; for (int c = 0; pc < pcells; ++c) { HSSFCell cell = row.getCell((short) c); if (cell == null) continue; ++pc; // Look for cells like ${var} if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) continue; String value = cell.getRichStringCellValue().getString().trim(); if (!value.startsWith("${")) continue; String rsname = value.substring(2, value.length() - 1); int n = replaceOneHolder(sheet, r, c, models, rsname); if (n != NOROWCHANGE) { r += n; break; // We just deleted the whole line! } } } } }
From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java
License:Open Source License
/** * Reads the excel file row by row. Fills the excel import internal structure which is column wise.<p> * //from w w w. j a va2s . com * @param in the document input stream * * @throws IOException if something goes wring */ private void readExcelColumnContents(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); HSSFSheet sheet = excelWb.getSheetAt(0); int rowsNumber = sheet.getPhysicalNumberOfRows(); if (rowsNumber > 0) { // loop over all rows from excel // do not read first column, because here are only user raw names for (int rowCounter = 1; rowCounter < sheet.getPhysicalNumberOfRows(); rowCounter++) { HSSFRow row = sheet.getRow(rowCounter); if ((row != null)) { // get number of rows in excel if ((rowCounter) > m_rowNumber) { m_rowNumber = rowCounter; } // loop over all columns in this row for (int columnCounter = 0; columnCounter < row.getLastCellNum(); columnCounter++) { CmsExcelColumn cmsExcelCol = (CmsExcelColumn) m_colContents.get(new Integer(columnCounter)); if (cmsExcelCol != null) { // read cell HSSFCell cell = row.getCell((short) columnCounter); if (cell != null) { String text = null; try { // read cell content from excel switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: case Cell.CELL_TYPE_ERROR: // ignore all blank or error cells break; case Cell.CELL_TYPE_NUMERIC: // check for date if (DateUtil.isCellDateFormatted(cell) || DateUtil.isValidExcelDate(cell.getNumericCellValue())) { // valid date Date date = DateUtil.getJavaDate(cell.getNumericCellValue()); text = new Long(date.getTime()).toString(); } else { // no valid date text = Double.toString(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: text = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_STRING: default: text = cell.getStringCellValue(); break; } // add to column list cmsExcelCol.addNewCellValue(rowCounter, text); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } catch (Exception e) { if (LOG.isErrorEnabled()) { LOG.error(e.toString()); } } } else { // add to column list cmsExcelCol.addNewCellValue(rowCounter, ""); m_colContents.put(new Integer(columnCounter), cmsExcelCol); } } } } } } }
From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java
License:Open Source License
/** * Creates mapping between excel column names set by user and excel column names from excel internal.<p> * /*from w w w . j a va 2 s .com*/ * @param in the document input stream * * @throws IOException if something goes wring */ private void readExcelColumnMappings(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); HSSFSheet sheet = excelWb.getSheetAt(0); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { HSSFRow firstRow = sheet.getRow(0); // loop over all columns in first excel row Iterator rowIter = firstRow.cellIterator(); while (rowIter.hasNext()) { // get cell HSSFCell cell = (HSSFCell) rowIter.next(); if (cell != null) { // get user column name String userColName = cell.getStringCellValue(); // get excel column name int excelColName = cell.getCellNum(); CmsExcelColumn excelCol = new CmsExcelColumn(userColName, excelColName); m_colNames.put(userColName, new Integer(excelColName)); m_colContents.put(new Integer(excelColName), excelCol); } } } }
From source file:com.b510.excel.client.HSSFReadWrite.java
License:Apache License
/** * Method main/*from w ww . j a va 2 s. c o m*/ * * Given 1 argument takes that as the filename, inputs it and dumps the cell * values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet see * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second an * output filename (not write), attempts to fully read in the spreadsheet * and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1". * If you take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { String[] name = new String[2]; name[0] = "HSSFReadWrite.xlsx"; name[1] = "write"; if (name.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = name[0]; try { if (name.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (name.length == 2) { if (name[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(name[1]); wb.write(stream); stream.close(); } } else if (name.length == 3 && name[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to // string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(name[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.bluecubs.xinco.index.filetypes.XincoIndexMicrosoftExcel.java
License:Apache License
public String getFileContentString(File f) { int i, j, j2, k; short k2;//from ww w . ja v a 2 s. c o m HSSFWorkbook wb = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; InputStream is = null; String cell_string = ""; try { is = new FileInputStream(f); POIFSFileSystem fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); for (i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); j2 = 0; for (j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { while ((row = sheet.getRow(j2)) == null) { j2++; } j2++; k2 = 0; for (k = 0; k < row.getPhysicalNumberOfCells(); k++) { while ((cell = row.getCell(k2)) == null) { k2++; } k2++; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: cell_string = cell_string + cell.getNumericCellValue() + "\t"; break; case HSSFCell.CELL_TYPE_STRING: cell_string = cell_string + cell.getStringCellValue() + "\t"; break; default: } } cell_string = cell_string + "\n"; } cell_string = cell_string + "\n\n\n"; } is.close(); } catch (Exception fe) { cell_string = null; if (is != null) { try { is.close(); } catch (Exception ise) { } } } return cell_string; }
From source file:com.cdf.objects.ExcelData.java
public void loadData(File file) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row;//from ww w .j ava2 s . c om HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int physicalCellCount; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { physicalCellCount = sheet.getRow(i).getPhysicalNumberOfCells(); if (physicalCellCount > cols) { cols = physicalCellCount; } } } System.out.println("Row count : " + rows); System.out.println("Coloumn count: " + cols); }
From source file:com.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static DefaultTableModel splitExcelFile(File toSplit, int acceptFormula, int tableIndex, boolean convertCharsToEntites) throws Exception { //file = toSplit; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(toSplit)); HSSFWorkbook wb = new HSSFWorkbook(fs); if (tableIndex > -1) { HSSFSheet sheet = wb.getSheetAt(tableIndex - 1); boolean isBlankRow = true; int numCols = 0; int numRows = 0; ///* w w w .java2s . co m*/ //int firstRow = 0; int firstColumn = 0; int iColumn = 0; //firstRow = getFirstRow(sheet,-1); /*if(firstRow==-1) { //MessageHandler.showError("Error, Cannot Read Sheet: "+tableIndex,"Import From Excel Error"); return(null); } else {*/ HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); firstColumn = getFirstColumn(sheet); numCols = getNumberOfCells(sheet); //row = sheet.getRow(firstRow); //numRows = sheet.getLastRowNum() - firstRow; Vector rows = new Vector(); /*System.out.println(firstColumn+":"+numCols); System.out.println(sheet.getFirstRowNum()+" To "+sheet.getLastRowNum()); System.out.println(sheet.getPhysicalNumberOfRows());*/ for (int rCnt = sheet.getFirstRowNum(); rCnt < sheet.getPhysicalNumberOfRows(); ++rCnt) { //reset the blank row boolean isBlankRow = true; row = sheet.getRow(rCnt); //System.out.println(numCols + ":" + firstColumn); String[] separated = new String[numCols - firstColumn]; for (int cCnt = firstColumn; cCnt < numCols; ++cCnt) { try { HSSFCell cell = row.getCell((short) cCnt); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC://System.out.println(rCnt+":"+cCnt+ " is numeric"); double value = row.getCell((short) cCnt).getNumericCellValue(); try { //get the long value which eliminates the decimal point long iValue = (new Double(value)).longValue(); //get the double value from this long value double longValue = (new Long(iValue)).doubleValue(); //subtract the two, if answer is 0 then //value can be converted, //if not then it can't if (value - longValue == 0) { //use long value separated[cCnt - firstColumn] = String.valueOf(iValue); } //end if else { //use double value separated[cCnt - firstColumn] = String.valueOf(value); } //end else } //end try catch (NumberFormatException e) { //use double value separated[cCnt - firstColumn] = String.valueOf(value); } //end catch break; case HSSFCell.CELL_TYPE_STRING://System.out.println(rCnt+":"+cCnt+ " is string"); isBlankRow = false; separated[cCnt - firstColumn] = row.getCell((short) cCnt).getStringCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA://System.out.println(rCnt+":"+cCnt+ " is formula"); isBlankRow = false; if (acceptFormula == 0) { //prompt String[] options = { "Cell Value", "Formula Text" }; Object formulaValue = JOptionPane.showInputDialog(null, "This worksheet contains formulas\n" + "What format would you like to import " + "the formula cells by: ", "Import From Table", JOptionPane.INFORMATION_MESSAGE, null, options, options[0]); if (formulaValue.toString().equalsIgnoreCase(options[0])) { //accept values acceptFormula = 2; } //end if else { acceptFormula = 1; } //end else } //end if else if (acceptFormula == 1) { //accept formula separated[cCnt - firstColumn] = row.getCell((short) cCnt).getCellFormula(); } //end else else if (acceptFormula == 2) { //dont accept formula double doubleValue = row.getCell((short) cCnt).getNumericCellValue(); Double dValue = new Double(doubleValue); if (dValue.isNaN()) { //should have been a string separated[cCnt - firstColumn] = row.getCell((short) cCnt) .getStringCellValue(); } //end if else { try { //get the long value which eliminates the decimal point long iValue = (new Double(doubleValue)).longValue(); //get the double value from this long value double longValue = (new Long(iValue)).doubleValue(); //subtract the two, if answer is 0 then //value can be converted, //if not then it can't if (doubleValue - longValue == 0) { //use long value separated[cCnt - firstColumn] = String.valueOf(iValue); } //end if else { //use double value separated[cCnt - firstColumn] = String.valueOf(doubleValue); } //end else } //end try catch (NumberFormatException e) { //use double value separated[cCnt - firstColumn] = String.valueOf(doubleValue); } //end catch } //end else } //end else break; case HSSFCell.CELL_TYPE_ERROR://System.out.println(rCnt+":"+cCnt+ " is error"); separated[cCnt - firstColumn] = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN://System.out.println(rCnt+":"+cCnt+ " is boolean"); isBlankRow = false; boolean booleanValue = row.getCell((short) cCnt).getBooleanCellValue(); separated[cCnt - firstColumn] = String.valueOf(booleanValue); break; case HSSFCell.CELL_TYPE_BLANK://System.out.println(rCnt+":"+cCnt+ " is blank"); separated[cCnt - firstColumn] = ""; break; } } //end if cell!=null else { } //end else } catch (Exception e) { //just a blank cell separated[cCnt - firstColumn] = ""; } //end try catch } //end for cCnt if (!isBlankRow) { rows.add(separated); } //HSSFCell cell; } //end for rCnt DefaultTableModel tableModel = addRowsToTable(rows, numCols - firstColumn); /*fileName = file.getAbsolutePath(); fileName = fileName.substring(0, fileName.lastIndexOf(".")); fileName += ".xml";*/ return (tableModel); } //end else //} return (null); }