List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getPhysicalNumberOfCells
@Override public int getPhysicalNumberOfCells()
From source file:br.solutio.licita.controlador.ControladorPregao.java
public void editandoXlsParaExportar(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet planilha = wb.getSheetAt(0); //Move as celulas selecionadas para baixo de acordo com o valor informado planilha.shiftRows(planilha.getFirstRowNum(), planilha.getLastRowNum(), 5); HSSFRow linha0 = planilha.createRow(0); linha0.createCell(0).setCellValue("Instituio Licitadora:"); planilha.addMergedRegion(new CellRangeAddress(0, 0, 0, 1)); linha0.createCell(2)/*w ww . j a v a 2 s . c o m*/ .setCellValue(" " + getEntidade().getInstituicaoLicitadora().getPessoaJuridica().getNomeFantasia()); planilha.addMergedRegion(new CellRangeAddress(0, 0, 2, 6)); HSSFRow linha1 = planilha.createRow(1); linha1.createCell(0).setCellValue("Numero do Pregao:"); planilha.addMergedRegion(new CellRangeAddress(1, 1, 0, 1)); linha1.createCell(2).setCellValue(" " + getEntidade().getNumeroPregao()); planilha.addMergedRegion(new CellRangeAddress(1, 1, 2, 6)); HSSFRow linha2 = planilha.createRow(2); linha2.createCell(0).setCellValue("Numero do Processo:"); planilha.addMergedRegion(new CellRangeAddress(2, 2, 0, 1)); linha2.createCell(2).setCellValue(" " + getEntidade().getNumeroProcesso()); planilha.addMergedRegion(new CellRangeAddress(2, 2, 2, 6)); HSSFRow linha3 = planilha.createRow(3); linha3.createCell(0).setCellValue("Empresa Licitante:"); planilha.addMergedRegion(new CellRangeAddress(3, 3, 0, 1)); linha3.createCell(2).setCellValue("Preencha com o nome de sua Empresa"); planilha.addMergedRegion(new CellRangeAddress(3, 3, 2, 6)); HSSFRow linha4 = planilha.createRow(4); //Nova coluna para a empresas adicionarem seus valores HSSFRow linha5 = planilha.getRow(5); HSSFCell celula5 = linha5.createCell(5); celula5.setCellValue("Valor do Licitante"); //for para ajustar automaticamente o tamnho das colunas for (int i = 0; i < 6; i++) { planilha.autoSizeColumn(i); } //Cor da linha de titulos da tabela HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < linha5.getPhysicalNumberOfCells(); i++) { HSSFCell cell = linha5.getCell(i); cell.setCellStyle(cellStyle); } CellStyle unlockedCellStyle = wb.createCellStyle(); unlockedCellStyle.setLocked(false); HSSFCell celula2 = linha3.getCell(2); celula2.setCellStyle(unlockedCellStyle); }
From source file:bs.proveedores.web.informe.CuentaCorrienteProveedorBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/* w ww . j a v a 2 s . c o m*/ HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:cdc.impl.datasource.office.ExcelDataSource.java
License:LGPL
private static DataColumnDefinition[] readDataModel(String sourceName, Map params) throws IOException, RJException { BufferedInputStream is = null; try {/*from w w w . j ava 2 s . co m*/ is = new BufferedInputStream(new FileInputStream((String) params.get(PARAM_FILE))); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is)); String[] sheets; if (params.get(PARAM_SHEET) != null) { sheets = new String[] { (String) params.get(PARAM_SHEET) }; } else { sheets = new String[wb.getNumberOfSheets()]; for (int i = 0; i < sheets.length; i++) { sheets[i] = wb.getSheetName(i); } } if (sheets.length == 0) { throw new RJException("Excel file " + params.get(PARAM_FILE) + " does not provide any sheets."); } List cols = new ArrayList(); HSSFSheet sheet = wb.getSheet(sheets[0]); if (sheet == null) { //System.out.println("Thorwing: " + "Sheet " + sheets[0] + " is not provided by file " + params.get(PARAM_FILE)); throw new RJException( "Sheet '" + sheets[0] + "' is not provided by file " + params.get(PARAM_FILE)); } HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb); //first row should provide data model HSSFRow row = sheet.getRow(0); evaluator.setCurrentRow(row); for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) { HSSFCell cell = row.getCell(i); cols.add(new ExcelDataColumnDefinition(decodeValue(cell, evaluator), DataColumnDefinition.TYPE_STRING, sourceName, i)); } for (int i = 1; i < sheets.length; i++) { sheet = wb.getSheet(sheets[i]); if (sheet == null) { throw new RJException("Sheet '" + params.get(PARAM_SHEET) + "' is not provided by file " + params.get(PARAM_FILE)); } evaluator = new HSSFFormulaEvaluator(sheet, wb); //first row should provide data model row = sheet.getRow(0); evaluator.setCurrentRow(row); List localCols = new ArrayList(); for (i = 0; i < row.getPhysicalNumberOfCells(); i++) { HSSFCell cell = row.getCell(i); DataColumnDefinition col = new ExcelDataColumnDefinition(decodeValue(cell, evaluator), DataColumnDefinition.TYPE_STRING, sourceName, i); localCols.add(col); } List toRemove = new ArrayList(); for (Iterator iterator = cols.iterator(); iterator.hasNext();) { DataColumnDefinition object = (DataColumnDefinition) iterator.next(); if (!localCols.contains(object)) { toRemove.add(object); } } cols.removeAll(toRemove); } return (DataColumnDefinition[]) cols.toArray(new DataColumnDefinition[] {}); } finally { if (is != null) { is.close(); } } }
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;/*w w w .j a v a2s . 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 . jav a2s . c om*/ 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:citibob.reports.PoiXlsWriter.java
License:Open Source License
/** Only copies formatting from col0 to col1, non-inclusive. */ void copyRow(HSSFRow r0, HSSFRow r1, int col0, int col1) { // Clear r1//from w w w .ja va 2s . c om int pcells = r1.getPhysicalNumberOfCells(); int pc = 0; for (int c = 0; pc < pcells; ++c) { HSSFCell c1 = r1.getCell((short) c); if (c1 == null) continue; ++pc; r1.removeCell(c1); } // Copy over cells from r0 pcells = r0.getPhysicalNumberOfCells(); pc = 0; for (int c = 0; pc < pcells; ++c) { HSSFCell c0 = r0.getCell((short) c); if (c0 == null) continue; ++pc; HSSFCell c1 = r1.createCell((short) c); if (c >= col0 && c < col1) copyCellFormatting(c0, c1); else copyCell(c0, c1); } }
From source file:com.b510.excel.client.HSSFReadWrite.java
License:Apache License
/** * Method main/* w w w . jav a2 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.bayareasoftware.chartengine.ds.util.ExcelDump.java
License:Apache License
private static String row2string(HSSFRow r, int maxCell) { if (r == null) { return ""; }//from w w w .jav a2 s .c om StringBuilder sb = new StringBuilder(); sb.append("<tr>"); sb.append("<td>#<b>" + r.getRowNum() + "</b> phys=" + r.getPhysicalNumberOfCells() + "<br/>1st=" + r.getFirstCellNum() + " last=" + r.getLastCellNum() + "</td>"); for (short i = 0; i < maxCell; i++) { HSSFCell c = r.getCell(i); sb.append("<td>" + cell2string(c) + "</td>"); } sb.append("</tr>\n"); return sb.toString(); }
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;// w w w . ja va2 s . c om 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.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static int getNumberOfCells(HSSFSheet sheet) throws Exception { int maxNumberOfCells = 0; for (int cnt = sheet.getFirstRowNum(); cnt < sheet.getPhysicalNumberOfRows(); ++cnt) { //get the first row HSSFRow row = sheet.getRow(cnt); if (row.getPhysicalNumberOfCells() > maxNumberOfCells) maxNumberOfCells = row.getPhysicalNumberOfCells(); }// ww w. j a va 2 s . c o m return (maxNumberOfCells); }