List of usage examples for org.apache.poi.hssf.usermodel HSSFRichTextString getString
public String getString()
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * copies template cell to result row into result column. Fills this cell with data from band * * @param templateCell - template cell//from w w w. ja v a 2 s. co m * @param resultRow - result row * @param resultColumn - result column * @param band - band */ private HSSFCell copyCellFromTemplate(HSSFCell templateCell, HSSFRow resultRow, int resultColumn, BandData band) { if (templateCell == null) return null; HSSFCell resultCell = resultRow.createCell(resultColumn); HSSFCellStyle templateStyle = templateCell.getCellStyle(); HSSFCellStyle resultStyle = copyCellStyle(templateStyle); resultCell.setCellStyle(resultStyle); String templateCellValue = ""; int cellType = templateCell.getCellType(); if (cellType != HSSFCell.CELL_TYPE_FORMULA && cellType != HSSFCell.CELL_TYPE_NUMERIC) { HSSFRichTextString richStringCellValue = templateCell.getRichStringCellValue(); templateCellValue = richStringCellValue != null ? richStringCellValue.getString() : ""; templateCellValue = extractStyles(templateCell, resultCell, templateCellValue, band); } if (cellType == HSSFCell.CELL_TYPE_STRING && containsJustOneAlias(templateCellValue)) { updateValueCell(rootBand, band, templateCellValue, resultCell, drawingPatriarchsMap.get(resultCell.getSheet())); } else { String cellValue = inlineBandDataToCellString(templateCell, templateCellValue, band); setValueToCell(resultCell, cellValue, cellType); } return resultCell; }
From source file:edu.ku.brc.specify.tasks.subpane.wb.ConfigureXLS.java
License:Open Source License
@Override protected void nonInteractiveConfig() { try {/*www . ja v a2 s.co m*/ InputStream input = new FileInputStream(externalFile); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook workBook = new HSSFWorkbook(fs); HSSFSheet sheet = workBook.getSheetAt(0); // Calculate the number of rows and columns colInfo = new Vector<ImportColumnInfo>(16); Hashtable<Integer, Boolean> colTracker = new Hashtable<Integer, Boolean>(); boolean firstRow = true; int col = 0; colTracker.clear(); Vector<Integer> badHeads = new Vector<Integer>(); Vector<Integer> emptyCols = new Vector<Integer>(); checkHeadsAndCols(sheet, badHeads, emptyCols); if (firstRowHasHeaders && badHeads.size() > 0) { status = ConfigureExternalDataIFace.Status.Error; showBadHeadingsMsg(badHeads, null, getResourceString("Error")); return; } // Iterate over each row in the sheet @SuppressWarnings("unchecked") Iterator<HSSFRow> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = rows.next(); if (firstRow || numRows == 1) { // Iterate over each cell in the row and print out the cell's content int colNum = 0; int maxSize = Math.max(row.getPhysicalNumberOfCells(), row.getLastCellNum()); while (colNum < maxSize) { if (emptyCols.indexOf(new Integer(colNum)) == -1) { ImportColumnInfo.ColumnType disciplinee = ImportColumnInfo.ColumnType.Integer; String value = null; boolean skip = false; HSSFCell cell = row.getCell(colNum); if (cell == null) { //assuming numRows == 1 or not firstRowHasHeaders. //the call to checkHeadsAndCols would have already blank headers. value = ""; disciplinee = ImportColumnInfo.ColumnType.String; } else switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: double numeric = cell.getNumericCellValue(); value = Double.toString(numeric); disciplinee = ImportColumnInfo.ColumnType.Double; break; case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString richVal = cell.getRichStringCellValue(); value = richVal.getString().trim(); disciplinee = ImportColumnInfo.ColumnType.String; break; case HSSFCell.CELL_TYPE_BLANK: value = ""; disciplinee = ImportColumnInfo.ColumnType.String; break; case HSSFCell.CELL_TYPE_BOOLEAN: boolean bool = cell.getBooleanCellValue(); value = Boolean.toString(bool); disciplinee = ImportColumnInfo.ColumnType.Boolean; break; default: skip = true; break; } if (numRows == 1 && !skip) { colInfo.get(col).setData(value); col++; } else if (!skip) { if (firstRowHasHeaders) { colInfo.add(new ImportColumnInfo(colNum, disciplinee, value, value, null, null, null)); colTracker.put(col, true); } else { String colName = getResourceString("DEFAULT_COLUMN_NAME") + " " + (colNum + 1); colInfo.add(new ImportColumnInfo(colNum, disciplinee, colName, colName, null, null, null)); colTracker.put(colNum, true); } numCols++; } } colNum++; } firstRow = false; } numRows++; } Collections.sort(colInfo); readMappings(fs); status = Status.Valid; } catch (IOException ex) { edu.ku.brc.af.core.UsageTracker.incrHandledUsageCount(); edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(ConfigureXLS.class, ex); status = Status.Error; } }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadExcelJob.java
License:Open Source License
private void loadExcel(final String file) { final File fil = new File(file); if (fil.exists()) { canRead = true;/*from w w w. ja va2 s. c o m*/ if (grid != null) { try { InputStream inp = new FileInputStream(file); try { wb = new HSSFWorkbook(inp); } catch (Exception e) { MsgDialog.message("Wrong format!\nOnly Excel 97-2007 is supported!"); canRead = false; } } catch (IOException ex) { ex.printStackTrace(); } if (canRead) { for (s = 0; s < wb.getNumberOfSheets(); s++) { Display display = PlatformUI.getWorkbench().getDisplay(); display.syncExec(new Runnable() { public void run() { String name = fil.getName(); grid = new Spread().spread(SampleView.getTabFolder(), 0, 0, name); SampleView.setGrid(grid); HSSFSheet sheet = wb.getSheetAt(s); int colCount = grid.getColumnCount(); int rowCount = grid.getItemCount(); int exelRow = endOfRow(sheet); int exelColumn = endOfColumn(sheet); // System.out.println(exelRow + " " + exelColumn // + "---" + sheet.getPhysicalNumberOfRows() + // " " + // sheet.getRow(0).getPhysicalNumberOfCells()); if (colCount < exelColumn) { int diff = exelColumn - colCount; for (int i = 0; i < diff; i++) { GridColumn column = new GridColumn(grid, SWT.NONE); column.setText("C " + (i + 1 + colCount)); column.setWidth(50); } } if (rowCount < exelRow) { int diff = exelRow - rowCount; for (int i = 0; i < diff; i++) { new GridItem(grid, SWT.NONE).setHeight(16); } } // Iterate over each row in the sheet int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < exelRow; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { for (int u = 0; u < exelColumn; u++) { grid.getItem(i).setText(u, " "); } } else { for (int u = 0; u < exelColumn; u++) { HSSFCell cell = row.getCell(u); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: String val = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val); break; case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString st = cell.getRichStringCellValue(); String val2 = st.getString(); grid.getItem(i).setText(u, val2); break; case HSSFCell.CELL_TYPE_FORMULA: try { String val3 = String.valueOf(cell.getNumericCellValue()); grid.getItem(i).setText(u, val3); } catch (Exception e) { String s2 = cell.getCellFormula(); grid.getItem(i).setText(u, s2); } break; case HSSFCell.CELL_TYPE_BLANK: grid.getItem(i).setText(u, " "); break; case HSSFCell.CELL_TYPE_BOOLEAN: boolean s4 = cell.getBooleanCellValue(); if (s4) { grid.getItem(i).setText(u, "TRUE"); } else { grid.getItem(i).setText(u, "FALSE"); } break; default: break; } } else { grid.getItem(i).setText(u, " "); } } } } } }); } wb = null; } } } else { MsgDialog.message("File not found!"); } }
From source file:org.ActSrnv03.core.ResidencialTopComponent.java
public void procesaCertificaciones(List sheetData) { ////from w w w .jav a 2 s . c om // Iterates the data and print it out to the console. // int cnt = 0; SimpleDateFormat formatDateJava = new SimpleDateFormat("dd-MM-yyyy"); HSSFRichTextString richTextString; for (int i = 1; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); System.out.println(i + " -> Tenemos Exel con " + list.size() + " columnas"); if (list.size() >= 26) { for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); this.tablaCertificaciones[cnt][j] = ""; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { this.tablaCertificaciones[cnt][j] = Double.toString(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); // System.out.print(cell.getRichStringCellValue()); this.tablaCertificaciones[cnt][j] = richTextString.getString(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { this.tablaCertificaciones[cnt][j] = Boolean.toString(cell.getBooleanCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { this.tablaCertificaciones[cnt][j] = ""; } } this.tablaCertificaciones[cnt][30] = "-1"; System.out.println(cnt + " -> Cargo registro con NIF=" + this.tablaCertificaciones[cnt][11] + " Y FECHA =" + this.tablaCertificaciones[cnt][3]); cnt++; } // System.out.println("---------"); } this.nCertificaciones = cnt; System.out.println( "----------- HE CARGADO " + this.nCertificaciones + " REGISTROS DE CERTIFICACIONES ------------"); JOptionPane.showMessageDialog(null, "\nHE CARGADO:" + this.nCertificaciones + " CERTIFICACIONES", "INFORMACIN", JOptionPane.WARNING_MESSAGE); }
From source file:org.alinous.poi.PoiManager.java
License:GNU General Public License
public String getValue(int col, int row, int sheetNum) { HSSFSheet sheet = this.wb.getSheetAt(sheetNum); HSSFRow hssfRow = sheet.getRow(row); if (hssfRow == null) { hssfRow = sheet.createRow(row);/*from w w w . java 2 s .c om*/ } HSSFCell cell = hssfRow.getCell(col); if (cell == null) { cell = hssfRow.createCell(col); } HSSFRichTextString text = cell.getRichStringCellValue(); return text.getString(); }
From source file:org.amanzi.splash.importer.ExcelImporter.java
License:Open Source License
/** * Creates a child spreadsheet inside parent spreadsheet * * @param sheet sheet to import// ww w . j a v a 2 s .c om * @param sheetName name of sheet * @param monitor monitor */ @SuppressWarnings(value = { "deprecation", "unchecked" }) private void createSheet(HSSFSheet sheet, String sheetName, Transaction transaction) { spreadsheetNode = null; spreadsheetName = sheetName; createSpreadsheet(rootSpreadsheet); try { Iterator<HSSFRow> rows = sheet.rowIterator(); while (rows.hasNext()) { HSSFRow row = rows.next(); //display row number in the console. LOGGER.debug("Row No.: " + row.getRowNum()); //once get a row its time to iterate through cells. Iterator<HSSFCell> cells = row.cellIterator(); int R = row.getRowNum(); if ((R % 20) == 0) { updateTransaction(transaction); } while (cells.hasNext()) { HSSFCell cell = cells.next(); LOGGER.debug("Cell No.: " + cell.getCellNum()); int C = cell.getCellNum(); /* * Now we will get the cell type and display the values * accordingly. */ switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: { // cell type numeric. LOGGER.debug("===================================================="); LOGGER.debug("Numeric value: " + cell.getNumericCellValue()); LOGGER.debug("===================================================="); String def = Double.toString(cell.getNumericCellValue()); Cell c = new Cell(R, C, def, def, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); break; } case HSSFCell.CELL_TYPE_STRING: { // cell type string. HSSFRichTextString richTextString = cell.getRichStringCellValue(); LOGGER.debug("===================================================="); LOGGER.debug("String value: " + richTextString.getString()); LOGGER.debug("===================================================="); Cell c = new Cell(R, C, richTextString.getString(), richTextString.getString(), new CellFormat()); saveCell(c); break; } case HSSFCell.CELL_TYPE_FORMULA: // cell type string. String cellFormula = "=" + cell.getCellFormula().toLowerCase(); Cell c = new Cell(R, C, cellFormula, cellFormula, new CellFormat()); //TODO: interpet!!!!!! //Cell c = model.interpret(def, R, C); saveCell(c); LOGGER.debug("===================================================="); LOGGER.debug("Formula value: " + cellFormula); LOGGER.debug("===================================================="); break; default: { // types other than String and Numeric. LOGGER.debug("Type not supported."); break; } } } } } finally { updateTransaction(transaction); } }
From source file:org.mifos.application.importexport.xls.XlsClientsImporter.java
License:Open Source License
private String getCellStringValue(final HSSFRow row, final XlsImportConstants xlsImportConstant) { final HSSFCell cell = row.getCell(xlsImportConstant.value(), HSSFRow.RETURN_BLANK_AS_NULL); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString richText = cell.getRichStringCellValue(); return (richText == null) ? "" : richText.getString(); case HSSFCell.CELL_TYPE_NUMERIC: int intVal = (int) cell.getNumericCellValue(); return String.valueOf(intVal); default://from ww w . ja v a2 s .co m return ""; } } else { return ""; } }
From source file:org.myorg.insertar.insertarSabanaTopComponent.java
public void procesaDatosEntrada(List sheetData) { ///*from ww w.jav a 2 s . c o m*/ // Iterates the data and print it out to the console. // int cnt = 0; int fProblema = 0; int nTipos = 0; SimpleDateFormat formatDateJava = new SimpleDateFormat("dd-MM-yyyy"); HSSFRichTextString richTextString; // ........................................................................................... for (int i = 0; i < sheetData.size(); i++) { List list = (List) sheetData.get(i); System.out.println(i + " -> Tenemos Exel con " + list.size() + " columnas"); // ........................................................................................... if (cnt == 0) { // CARGAMOS LOS NOMBRES DE LOS CAMPOS for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); // ............................................................ System.out.println("Tipo Nombre =" + cell.getCellType()); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); this.nombres[j] = richTextString.getString(); System.out.println("Cargando Nombre =" + this.nombres[j]); } } this.nColumnas = list.size(); System.out.println("Numero de campos=" + this.nColumnas); } // ........................................................................................... if (cnt == 1) { // CARGAMOS LOS TIPOS DE LOS CAMPOS nTipos = list.size(); for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); this.tipos[j] = richTextString.getString(); } if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { this.tipos[j] = Double.toString(cell.getNumericCellValue()); System.out.println("Cargo this.tipos[" + j + "] = " + this.tipos[j]); } } } // ........................................................................................... if (cnt > 1) { // CARGAMOS LOS DATOS System.out.println("------------CARGAMOS LOS DATOS -----------"); if (nTipos != list.size()) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") -> HAY UN PROBLEMA :EL NMERO DE CAMPOS DEFINIDOS Y EL QUE CONTIENE LA L?NEA DE DATOS NO COINCIDE (" + nTipos + "!=" + list.size() + ") \n"; // fProblema = 2 ; } if (fProblema != 2) { for (int j = 0; j < list.size(); j++) { Cell cell = (Cell) list.get(j); this.tablaDatos[cnt - 2][j] = ""; // ............................................................ if (this.tipos[j].equals("4.0")) { // es un tipo fecha SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); try { this.tablaDatos[cnt - 2][j] = String.valueOf(sdf.format(cell.getDateCellValue())); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") -> HAY UN PROBLEMA EL TIPO FECHA EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else { // ............................................................ if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { try { this.tablaDatos[cnt - 2][j] = Double.toString(cell.getNumericCellValue()); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") -> HAY UN PROBLEMA EL TIPO DE DATO NUMERICO EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { // ............................................................ try { richTextString = (HSSFRichTextString) cell.getRichStringCellValue(); this.tablaDatos[cnt - 2][j] = richTextString.getString(); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") ->HAY UN PROBLEMA EL TIPO DE DATO TEXTO EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // ............................................................ try { this.tablaDatos[cnt - 2][j] = Boolean.toString(cell.getBooleanCellValue()); } catch (IllegalStateException e) { this.sLogTxt += "AVISO: FILA DATOS(" + (cnt - 2) + ") ->HAY UN PROBLEMA EL TIPO DE DATO BOOLEANO EN EL CAMPO: " + this.nombres[j] + "\n"; fProblema = 1; } } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // ............................................................ this.tablaDatos[cnt - 2][j] = ""; } } } // ........................................................................................... } cnt++; } else { // Nos saltamos el procesamiento de esta lnea y seguimos contando. cnt++; fProblema = 0; } } // ........................................................................................... this.nDatos = cnt; System.out.println("----------- HE CARGADO " + this.nDatos + " REGISTROS ------------"); this.sLogTxt += "----------- HE CARGADO " + this.nDatos + " REGISTROS ------------" + "\n"; logAcciones.setText(this.sLogTxt); if (fProblema == 0) { JOptionPane.showMessageDialog(null, "\nHE CARGADO:" + this.nDatos + " REGISTROS", "INFORMACIN", JOptionPane.WARNING_MESSAGE); } else { JOptionPane.showMessageDialog(null, "\nSE HAN DETECTADO PROBLEMAS, SE CANCELA LA CARGA DE ARCHIVO EXEL", "AVISO", JOptionPane.WARNING_MESSAGE); } }
From source file:org.opencrx.kernel.text.ExcelToText.java
License:BSD License
/** * This method listens for incoming records and handles them as required. * //from ww w . j a v a 2 s . co m * @param record * The record that was found while reading. */ public void processRecord(Record record) { int curentRow = 0; if (this.sstrec != null) { try { if (record instanceof CellValueRecordInterface) { if (record instanceof LabelSSTRecord) { LabelSSTRecord bof2 = (LabelSSTRecord) record; int rowNum = ((CellValueRecordInterface) record).getRow(); if (curentRow < rowNum) { this.text.append(" "); curentRow = rowNum; } try { String value = sstrec.getString(bof2.getSSTIndex()).toString(); this.text.append(value + " "); } catch (Exception e) { } } if (record instanceof NumberRecord) { NumberRecord bof2 = (NumberRecord) record; int rowNum = ((CellValueRecordInterface) record).getRow(); if (curentRow < rowNum) { this.text.append(" "); curentRow = rowNum; } String value = (new Double(bof2.getValue())).toString(); this.text.append(value + " "); } } } catch (Exception ex) { } catch (NoSuchMethodError e) { } } switch (record.getSid()) { case BoundSheetRecord.sid: BoundSheetRecord bsr = (BoundSheetRecord) record; this.text.append(bsr.getSheetname() + " "); break; case SSTRecord.sid: this.sstrec = (SSTRecord) record; break; case DSFRecord.sid: break; case TextObjectRecord.sid: TextObjectRecord textObjectRecord = (TextObjectRecord) record; HSSFRichTextString q = textObjectRecord.getStr(); String st = q.getString(); if (st != null && !"".equals(st)) { int ind = this.text.indexOf(st); if (ind == -1) { this.text.append(st + " "); } } break; case StringRecord.sid: StringRecord selectionRecord = (StringRecord) record; String str = selectionRecord.getString(); if (str != null && !"".equals(str)) { int ind = this.text.indexOf(str); if (ind == -1) { this.text.append(str + " "); } } break; } }
From source file:org.seasar.dbflute.helper.io.xls.DfTableXlsReader.java
License:Apache License
protected void setupColumns(DfDataTable table, HSSFRow nameRow, HSSFRow valueRow) { for (int i = 0;; ++i) { final HSSFCell nameCell = nameRow.getCell(i); if (nameCell == null) { break; }/*from ww w. j av a 2 s .co m*/ final HSSFRichTextString richStringCellValue = nameCell.getRichStringCellValue(); if (richStringCellValue == null) { break; } final String columnName = richStringCellValue.getString().trim(); if (columnName.length() == 0) { break; } HSSFCell valueCell = null; if (valueRow != null) { valueCell = valueRow.getCell(i); } if (valueCell != null) { table.addColumn(columnName, getColumnType(valueCell)); } else { table.addColumn(columnName); } } }