List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getCellFormula
public String getCellFormula()
From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java
License:Open Source License
/** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the sheet on the given sheetIndex is copied. /*from w ww . ja v a 2 s .co m*/ * @param igrid the XMA model where to copy the data * @param book the POI represntation of the data * @param sheetIndex the index of the sheet to copy * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) { GridWM grid = (GridWM) igrid; try { List errorList = new ArrayList(); grid.setSheetName(book.getSheetName(sheetIndex)); grid.colors.clear(); grid.initBuildInColors(); short ic = GridWM.HSSF_FIRST_COLOR_INDEX; HSSFPalette palette = book.getCustomPalette(); for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) { grid.colors.add(ic, new GridColor(color.getTriplet())); } grid.fonts.clear(); int numFonts = book.getNumberOfFonts(); if (numFonts > 4) { // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt() numFonts += 1; } for (short i = 0; i < numFonts; i++) { HSSFFont font = book.getFontAt(i); byte fontstyle = GridFont.FONT_NORML; if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) fontstyle |= GridFont.FONT_BOLD; if (font.getItalic()) fontstyle |= GridFont.FONT_ITALIC; grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor())); } grid.styles.clear(); for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) { HSSFCellStyle style = book.getCellStyleAt(i); grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor())); } grid.namedRanges.clear(); for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) { HSSFName name = book.getNameAt(i); String rangeName = name.getNameName(); String rangeRef = null; try { // ranges not defined but referenced by formulas have a name but no reference in HSSF rangeRef = name.getReference(); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName)) .setCode(GridWM.CODE_inconsistentRange)); } if (rangeRef != null) { try { GridRange range = grid.getJeksDelegate().toRange(rangeRef); range.setKey(rangeName); grid.namedRanges.put(rangeName, range); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef)) .setCode(GridWM.CODE_unsupportedReference)); } } } grid.rows.clear(); grid.cols.clear(); grid.cells.clear(); grid.delegate = new GridJeksDelegate(grid); HSSFSheet sheet = book.getSheetAt(sheetIndex); int firstColNum = Integer.MAX_VALUE; int lastColNum = Integer.MIN_VALUE; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row == null) continue; if (row.getFirstCellNum() >= 0) firstColNum = Math.min(firstColNum, row.getFirstCellNum()); lastColNum = Math.max(lastColNum, row.getLastCellNum()); if (lastColNum > 255) lastColNum = 255; for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell hssfcell = row.getCell(j); if (hssfcell == null) continue; GridCell gridcell = grid.getOrCreateCell(i, j); switch (hssfcell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: gridcell.setValue(hssfcell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // TODO: recherche error text byte errorCode = hssfcell.getErrorCellValue(); // gridcell.setValue(errorCode); gridcell.setValue("#ERROR"); errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord", grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode))) .setCode(GridWM.CODE_errorRecord)); break; case HSSFCell.CELL_TYPE_FORMULA: String formula = null; try { formula = hssfcell.getCellFormula(); gridcell.setFormula(formula); } catch (SysException e) { if (formula != null) gridcell.setValue("=" + formula); //set it as text without interpretation errorList.add(e); } break; case HSSFCell.CELL_TYPE_NUMERIC: if (isDateCell(book, hssfcell)) { gridcell.setValue(hssfcell.getDateCellValue()); } else { gridcell.setValue(hssfcell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: gridcell.setValue(hssfcell.getStringCellValue()); break; default: throw new SysException("unknown cell type " + hssfcell.getCellType()); } gridcell.setEditable(!hssfcell.getCellStyle().getLocked()); gridcell.setStyle(hssfcell.getCellStyle().getIndex()); } } final int scalefactor = 256 / 7; // empirically testet // int width = sheet.getDefaultColumnWidth(); // returns nonsense // width = width/scalefactor; // grid.setDefaultColumnWidth(width); for (short i = (short) firstColNum; i <= lastColNum; i++) { int width = sheet.getColumnWidth(i); width = width / scalefactor; grid.getOrCreateColumn(i).setWidth(width); } if (firstColNum == Integer.MAX_VALUE) firstColNum = 0; if (lastColNum == Integer.MIN_VALUE) lastColNum = 0; grid.setMaxRange( new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum)); grid.setVisibleRange(grid.getMaxRange()); return errorList; } finally { grid.handle(grid.new GridReloadEvent()); } }
From source file:br.eti.rogerioaguilar.minhasclasses.util.excel.leitor.LeitorExcel.java
License:Open Source License
/** * Realiza o processamento de leitura seguindo o fluxo: * //from w ww. j a v a 2 s . c om * 1 - O leitor abre a planilha <br> * 2 - o leitor comea a ler as linhas e colunas da planilha <br> * 3 - Para cada linha encontrada: <br> * 3.1 - Caso a linha esteja na lista de linhas que devem ser processadas passada ao construtor: <br> * 3.1.1 - O leitor monta uma lista de vos contendos os dados das colunas para a linha atual que <br> * estejam dentro do padro de leitura passado ao construtor <br> * 3.1.2 - O leitor chama o mtodo LinhaListener.lendoLinha passando o mapa com os dados da linha <br> * e aguarda o final do processamento. <br> * 3.1.2.1 - Caso o mtodo do listener retorne true, o leitor continua o processamento da <br> * da planilha. Caso contrrio, o processamento da planilha interrompido <br> * Se o processamento da planilha continuar (de acordo com o parmetro de retorno <br> * do listener para a linha descrito anteriormente), o leitor chama o listener para <br> * a coluna para cada coluna da linha atual. O comportamento deste listener o mesmo <br> * do listener para a linha, ou seja, se o listener retornar false o processamento da <br> * planilha interrompido. * * @throws ParseException * @throws PlanilhaNaoEncontradaException caso o ndice da planilha no seja encontrado no arquivo * @throws FileNotFoundException caso o arquivo passado como parmetro no exista * @throws ListenerException caso ocorra algum erro na chamada de algum dos listeners * @throws IOException caso ocorra algum problema de io * */ public void processarLeituraPlanilha() throws ParseException, PlanilhaNaoEncontradaException, FileNotFoundException, IOException, ListenerException { try { log.debug("Inicializando o processamento da leitura do arquivo..."); log.debug("Dados para o processamento --> " + this); POIFSFileSystem fs = null; if (this.streamArquivo != null) { fs = new POIFSFileSystem(streamArquivo); } else if (this.caminhoArquivoExcel != null) { fs = new POIFSFileSystem(new FileInputStream(this.caminhoArquivoExcel)); } else { throw new IllegalArgumentException( "No foi definido um stream para o arquivo nem um caminho para o arquivo!"); } log.debug("Processando a string de entrada --> " + this.strPadraoLeitura); Map mapaLinhasAProcessar = LeitorExcelReader .getMapaEntradas(new ByteArrayInputStream(this.strPadraoLeitura.getBytes())); log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso."); log.debug("Mapa retornado --> " + mapaLinhasAProcessar); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet planilha = wb.getSheetAt(this.indicePlanilha - 1); if (planilha == null) { log.error("Planilha no encontrada no ndice -->" + this.indicePlanilha); throw new PlanilhaNaoEncontradaException( "Planilha no encontrada no ndice -->" + this.indicePlanilha); } else { log.debug("A string de entrada --> " + this.strPadraoLeitura + " foi processada com sucesso."); boolean processarTodasAsLinhas = (mapaLinhasAProcessar.get("*") != null); boolean processarTodasAsColunas = false; boolean continuarProcessamentoLinha = true; Map propriedadesListenerLinha = new HashMap(); Map propriedadesListenerColuna = new HashMap(); List listaColunas = null; List listaVosColunas = new LinkedList(); if (processarTodasAsLinhas) { log.debug("Processando todas as linhas..."); } Iterator itLinhas = planilha.rowIterator(); while (itLinhas.hasNext() && continuarProcessamentoLinha) { HSSFRow linha = (HSSFRow) itLinhas.next(); propriedadesListenerLinha.clear(); listaVosColunas.clear(); propriedadesListenerLinha.put(LinhaListener.CHAVE_LINHA_OBJETO_ORIGINAL_POI, linha); int intLinhaAtual = linha.getRowNum() + 1; log.debug("Processando linha --> " + intLinhaAtual); if (!processarTodasAsLinhas) { listaColunas = getListaColunasLinha("" + intLinhaAtual, mapaLinhasAProcessar); } else { listaColunas = getListaColunasLinha("*", mapaLinhasAProcessar); } boolean processarLinhaAtual = processarTodasAsLinhas || (listaColunas != null); if (processarLinhaAtual) { Iterator itColunas = linha.cellIterator(); processarTodasAsColunas = (listaColunas != null) && (listaColunas.size() > 0) && ("" + listaColunas.get(0)).equals("*"); while (itColunas.hasNext()) { HSSFCell celula = (HSSFCell) itColunas.next(); int intCelulaAtual = celula.getCellNum() + 1; boolean processarColunaAtual = processarTodasAsColunas || ((listaColunas != null) && (listaColunas.size() > 0) && listaColunas.indexOf(new Long(intCelulaAtual)) != -1); LinhaColunaListenerVo linhaColunaListenerVo = new LinhaColunaListenerVo(); linhaColunaListenerVo.setLinha(intLinhaAtual); linhaColunaListenerVo.setColuna(intCelulaAtual); linhaColunaListenerVo.setCelulaAtual(celula); if (processarColunaAtual) { if (celula != null) { log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual + " deve ser processada..."); switch (celula.getCellType()) { case HSSFCell.CELL_TYPE_STRING: linhaColunaListenerVo.setValorStr(celula.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: linhaColunaListenerVo .setValorNumerico(new Double(celula.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_FORMULA: linhaColunaListenerVo.setCelulaFormula(true); linhaColunaListenerVo.setValorStrFormula(celula.getCellFormula()); break; case HSSFCell.CELL_TYPE_ERROR: linhaColunaListenerVo.setCelulaContemErro(true); linhaColunaListenerVo.setErro(new Byte(celula.getErrorCellValue())); break; case HSSFCell.CELL_TYPE_BOOLEAN: linhaColunaListenerVo .setValorBoolean(new Boolean(celula.getBooleanCellValue())); break; case HSSFCell.CELL_TYPE_BLANK: linhaColunaListenerVo.setCelulaBranca(true); linhaColunaListenerVo.setValorStr(""); break; } } else { log.warn("Clula nula!"); linhaColunaListenerVo.setCelulaNula(true); } listaVosColunas.add(linhaColunaListenerVo); } else { log.debug("Coluna --> " + intCelulaAtual + " para a linha --> " + intLinhaAtual + " no deve ser processada..."); } } if (this.linhaListener != null) { log.debug("Chamando o listener para a linha --> " + intLinhaAtual); Collections.sort(listaVosColunas, new Comparator() { public int compare(Object arg0, Object arg1) { int colunaUm = ((LinhaColunaListenerVo) arg0).getColuna(); int colunaDois = ((LinhaColunaListenerVo) arg1).getColuna(); if (colunaUm < colunaDois) { return -1; } else if (colunaUm > colunaDois) { return 1; } return 0; } }); propriedadesListenerLinha.put(LinhaListener.LISTA_VOS_LINHA, listaVosColunas); continuarProcessamentoLinha = this.linhaListener.lendoLinha(intLinhaAtual, propriedadesListenerLinha); if (!continuarProcessamentoLinha) { log.debug( "Listener retornou boolean false indicando que o processamento deve ser interrompido!"); } } else { log.debug("Listener no configurado para a linha --> " + intLinhaAtual); } if (this.colunaListener != null) { Iterator itColunasVoListener = listaVosColunas.iterator(); boolean continuarProcessamentoColunasnaLinha = true; while (itColunasVoListener.hasNext() && continuarProcessamentoColunasnaLinha) { propriedadesListenerColuna.clear(); LinhaColunaListenerVo voAtual = (LinhaColunaListenerVo) itColunasVoListener.next(); propriedadesListenerColuna.put(ColunaListener.CHAVE_VO_COLUNA, voAtual); propriedadesListenerColuna.put(ColunaListener.CHAVE_COLUNA_OBJETO_ORIGINAL_POI, voAtual.getCelulaAtual()); log.debug("Chamando o listener para a coluna --> " + voAtual.getColuna() + " na linha " + voAtual.getLinha()); continuarProcessamentoColunasnaLinha = this.colunaListener.lendoColuna( voAtual.getLinha(), voAtual.getColuna(), propriedadesListenerColuna); if (!continuarProcessamentoColunasnaLinha) { log.debug( "Listener de coluna retornou boolean false indicando que o processamento das colunas na linha " + voAtual.getLinha() + " deve ser interrompido!"); } } } else { log.debug("Listener no configurado para processamento das colunas"); } } else { log.debug("Linha --> " + intLinhaAtual + " no ser processada!"); } } } log.debug("Processamento da planilha realizado com sucesso!"); } catch (ParseException e) { e.printStackTrace(); log.error("Erro ao processar a string de entrada ", e); throw e; } catch (FileNotFoundException e) { e.printStackTrace(); log.error("Arquivo " + this.caminhoArquivoExcel + " no encontrado", e); throw e; } catch (IOException e) { e.printStackTrace(); log.error("Erro ao abrir o arquivo " + this.caminhoArquivoExcel, e); throw e; } catch (ListenerException e) { e.printStackTrace(); log.error("Erro ao processar o listener ", e); throw e; } }
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 ww w . j a v a 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
void copyCell(HSSFCell c0, HSSFCell c1) { copyCellFormatting(c0, c1);/*from w w w .ja v a 2s. c om*/ c1.setCellType(c0.getCellType()); switch (c0.getCellType()) { case HSSFCell.CELL_TYPE_STRING: c1.setCellValue(c0.getRichStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: c1.setCellValue(c0.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: c1.setCellFormula(c0.getCellFormula()); break; case HSSFCell.CELL_TYPE_BOOLEAN: c1.setCellValue(c0.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: c1.setCellErrorValue(c0.getErrorCellValue()); break; } }
From source file:com.b510.excel.client.HSSFReadWrite.java
License:Apache License
/** * Method main//from ww w .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.bayareasoftware.chartengine.ds.ExcelDataSource.java
License:Apache License
/** * work around bug with named cells/*from w ww . j av a2 s . co m*/ * */ private static void rewriteFormulas(final HSSFWorkbook workbook) { //if (true) return; // build up a cache of names // this is just an easy way of fetching the HSSFName based on the string // representation of the name final Map<String, HSSFName> nameCache = new HashMap<String, HSSFName>(workbook.getNumberOfNames()); for (int i = 0; i < workbook.getNumberOfNames(); i++) { final HSSFName name = workbook.getNameAt(i); nameCache.put(name.getNameName(), name); } // remove all the sheet names from the name references, having the sheet // names around messes up the formulas for (int i = 0; i < workbook.getNumberOfSheets(); i++) { nameCache.remove(workbook.getSheetName(i)); } //p("Names: " + nameCache.keySet()); // loop over all the cells and rewrite the formula ones for (int sheetCount = 0; sheetCount < workbook.getNumberOfSheets(); sheetCount++) { final HSSFSheet sheet = workbook.getSheetAt(sheetCount); for (final Iterator rowIterator = sheet.rowIterator(); rowIterator.hasNext();) { final HSSFRow row = (HSSFRow) rowIterator.next(); for (final Iterator cellIterator = row.cellIterator(); cellIterator.hasNext();) { final HSSFCell cell = (HSSFCell) cellIterator.next(); if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { String formula = cell.getCellFormula(); for (final String name : nameCache.keySet()) { final Pattern pattern = Pattern.compile("(\\W|^)" + name + "(\\W|$)", Pattern.CASE_INSENSITIVE); final HSSFName hssfName = nameCache.get(name); formula = pattern.matcher(formula) .replaceAll("$1" + hssfName.getReference().replace("$", "\\$") + "$2"); } //p("Resetting Cell (" + cell.toString() // + ") Formula:" + formula); cell.setCellFormula(formula); } // end if } // end for } // end for } // end for }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelDump.java
License:Apache License
private static String cell2string(HSSFCell c) { if (c == null) { return "<i>NULL CELL</i>"; }//from w w w. j a v a 2 s . co m int type = c.getCellType(); String t = null, v = null; switch (type) { case HSSFCell.CELL_TYPE_BLANK: t = "BLANK"; v = ""; break; case HSSFCell.CELL_TYPE_STRING: t = "STRING"; v = c.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (ExcelInference.isCellDateFormatted(c)) { t = "DATE"; v = c.getDateCellValue() + ""; } else { t = "NUMERIC"; v = c.getNumericCellValue() + ""; } break; case HSSFCell.CELL_TYPE_ERROR: t = "ERROR"; v = "(errByte=" + c.getErrorCellValue() + "/toString=" + c + ")"; break; case HSSFCell.CELL_TYPE_FORMULA: t = "FORMULA"; v = c.getCellFormula(); break; default: t = "(UNKNOWN TYPE: " + type + ")"; v = c.toString(); break; } short style = c.getCellStyle().getDataFormat(); return v + "<br/>(" + t + ")<br/>dataformat=0x" + Integer.toHexString(style); }
From source file:com.cablelabs.fsm.SystemSettings.java
License:Open Source License
private String getXlsCellStringValue(HSSFCell cell) { int cellType = 0; String value = ""; if (cell != null) { cellType = cell.getCellType();//from ww w. j a va 2 s. com switch (cellType) { case HSSFCell.CELL_TYPE_NUMERIC: Double doubleValue = cell.getNumericCellValue(); value = Long.toString(doubleValue.longValue()); break; case HSSFCell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case HSSFCell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_BLANK: value = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN: value = Boolean.toString(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: value = "ERROR"; break; default: } } return value; }
From source file:com.cms.utils.ExcelReader.java
public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else {/* w w w. j av a 2 s.c om*/ int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copyCell(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) { if (src == null) { dest.setCellType(HSSFCell.CELL_TYPE_BLANK); return;/* ww w.j av a 2 s . c o m*/ } if (src.getCellComment() != null) dest.setCellComment(src.getCellComment()); if (src.getCellStyle() != null) { HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb); if (nstyle == null) { nstyle = destwb.createCellStyle(); copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle()); } dest.setCellStyle(nstyle); } dest.setCellType(src.getCellType()); switch (src.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: dest.setCellValue(src.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: dest.setCellFormula(src.getCellFormula()); break; case HSSFCell.CELL_TYPE_ERROR: dest.setCellErrorValue(src.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: dest.setCellValue(src.getNumericCellValue()); break; default: dest.setCellValue(new HSSFRichTextString(src.getRichStringCellValue().getString())); break; } }