List of usage examples for org.apache.poi.ss.usermodel Cell getNumericCellValue
double getNumericCellValue();
From source file:br.com.bb.intranet.supermt.pf.desembolso.service.ImportaDados.java
/** * * @param planilha objeto do tipo Planilha que contm todos os dados * necessrios para facilitar a operao na planilha *//*from w w w . j a va2 s.c o m*/ public void processaPlanilha(Planilha planilha) throws NegocioException { List<Desembolso> dadosParaSalvar = new ArrayList(); int linha = 0; Double d; try { //cria um workbook = planilha toda com todas as abas XSSFWorkbook workbook = new XSSFWorkbook(planilha.getFile()); //recuperamos apenas a aba mandada XSSFSheet sheet = workbook.getSheetAt(planilha.getNumeroDaPlanilha() - 1); //retorna todas as linhas da planilha selecioada Iterator<Row> rowIterator = sheet.iterator(); //varre todas as linhas da planilha selecionada while (rowIterator.hasNext() && linha < planilha.getUltimaLinha()) { linha++; //recebe cada linha da planilha Row row = rowIterator.next(); //andar as linhas que sero ignoradas no incio if (row.getRowNum() < planilha.getUltimaLinha()) { continue; } //pegamos todas as celulas desta linha Iterator<Cell> cellIterator = row.iterator(); //responsavel por definir qual coluna esta sendo trabalhada no intante int coluna = 1; Cell cell; Desembolso desembolso = new Desembolso(); //varremos todas as celulas da linha atual while (cellIterator.hasNext()) { //criamos uma celula cell = cellIterator.next(); //TODO O CDIGO DE PERSISTENCIA AQUI!! switch (coluna) { case ColunasDesembolso.GRUPO: desembolso.setGrupo(cell.getStringCellValue()); break; case ColunasDesembolso.PREFIXO_SUPER_INTENDENCIA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setPrefixoSuperintendencia(cell.getStringCellValue()); break; case ColunasDesembolso.NOME_SUPER_INTENDENCIA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setNomeSuperintendencia(cell.getStringCellValue()); break; case ColunasDesembolso.PREFIXO_REGIONAL: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setPrefixoRegional(cell.getStringCellValue()); break; case ColunasDesembolso.NOME_AGENCIA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setNomeAgencia(cell.getStringCellValue()); break; case ColunasDesembolso.ORCAMENTO_PROPOSTO_ACUMULADO: desembolso .setOrcamentoPropostoAcumulado(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_ATUAL: desembolso.setRealizadoAtual(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_UM: desembolso.setPercentualAtingimentoUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_D_MENOS_UM: desembolso.setRealizadoDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.NECESSIDADE_DIA_MENOS_UM: cell.setCellType(Cell.CELL_TYPE_NUMERIC); desembolso.setNecessidadeDiaDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.META_CONTATOS_ACUMULADA: cell.setCellType(Cell.CELL_TYPE_NUMERIC); desembolso.setMetaContatosAcumulada(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_CONTATOS_MES: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setRealizadoContatosMes(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setPercentualAtingimentoContatos( this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.CONTATOS_D_MENOS_UM: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setContatosDmenosUm(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.CONTATOS_D_MENOS_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setContatosDmenosDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PREFIXO_REPETE: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setPrefixoRepete(cell.getStringCellValue()); break; case ColunasDesembolso.AGENCIA_REPETE: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setAgenciaRepete(cell.getStringCellValue()); break; case ColunasDesembolso.CODIDGO_CARTEIRA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setCodigoCarteira(cell.getStringCellValue()); break; case ColunasDesembolso.CARTEIRA: cell.setCellType(Cell.CELL_TYPE_STRING); desembolso.setCarteira(cell.getStringCellValue()); break; case ColunasDesembolso.ORCAMENTO_PROPORCIONAL_ACUMULADO_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setOrcamentoProporcionalAcumuladoDois( this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_ATUAL_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setRealizadoAtualDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso .setPercentualAgintimentoDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.META_CONTATOS_ACUMULADA_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso .setMetaContatosAcumuladaDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.REALIZADO_CONTATOS_MES_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setRealizadoContatosMesDois(this.doubleToBigDecimal(cell.getNumericCellValue())); break; case ColunasDesembolso.PERCENTUAL_ATINGIMENTO_CONTATOS_DOIS: cell.setCellType(cell.CELL_TYPE_NUMERIC); desembolso.setPercentualAtingimentoContatosDois( this.doubleToBigDecimal(cell.getNumericCellValue())); break; } System.out.println("valor = " + cell.toString()); coluna++; } dadosParaSalvar.add(desembolso); } this.salvar(dadosParaSalvar); } catch (FileNotFoundException ex) { throw new NegocioException("Arquivo com Erro Tente novamente!!"); } catch (IOException ex) { throw new NegocioException("Arquivo com Erro Tente novamente!!"); } }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
private String cellToString(Cell cell) { String result = null;/*from ww w.j av a 2 s. co m*/ switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: result = cell.getStringCellValue(); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { result = cell.getDateCellValue().toString(); } else { int i = (int) cell.getNumericCellValue(); double d = cell.getNumericCellValue(); if (i == d) { result = String.valueOf(i); } else { result = String.valueOf(d); } } break; case Cell.CELL_TYPE_BOOLEAN: result = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: result = cell.getCellFormula(); break; default: result = null; } return result; }
From source file:br.com.gartech.nfse.integrador.view.ImportarView.java
private Object getCellValue(Cell cell) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: return cell.getStringCellValue(); case Cell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue(); case Cell.CELL_TYPE_NUMERIC: return cell.getNumericCellValue(); }//from w ww .jav a 2 s . c o m return null; }
From source file:br.com.tecsinapse.dataio.importer.ImporterUtils.java
License:LGPL
public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell, boolean expectedDate) { final CellValue cellValue = safeEvaluteFormula(evaluator, cell); if (cellValue == null) { return ""; }//from w ww . ja va 2 s. c o m switch (cellValue.getCellType()) { case BOOLEAN: return cellValue.getBooleanValue(); case NUMERIC: if (DateUtil.isCellDateFormatted(cell) || (expectedDate && DateUtil.isValidExcelDate(cellValue.getNumberValue()))) { return cell.getDateCellValue(); } BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION, BigDecimal.ROUND_HALF_UP); return bd.stripTrailingZeros(); case STRING: return cellValue.getStringValue(); case ERROR: return "ERRO"; default: return ""; } }
From source file:br.com.tecsinapse.exporter.importer.ImporterUtils.java
License:LGPL
public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell) { final CellValue cellValue = evaluator.evaluate(cell); if (cellValue == null) { return ""; }//from w w w. j a v a 2 s .c o m switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: return Boolean.valueOf(cellValue.getBooleanValue()); case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); return date; } BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION, BigDecimal.ROUND_HALF_UP); return bd.stripTrailingZeros(); case Cell.CELL_TYPE_STRING: return cellValue.getStringValue(); case Cell.CELL_TYPE_ERROR: return "ERRO"; default: return ""; } }
From source file:br.com.tiagods.model.Arquivo.java
public String tratarTipo(Cell celula) { //metodo usado para tratar as celulas switch (celula.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(celula)) return sdf.format(celula.getDateCellValue());//campo do tipo data formatando no ato else {/*from w w w. j av a 2s .com*/ return String.valueOf((long) celula.getNumericCellValue());//campo do tipo numerico, convertendo double para long } case Cell.CELL_TYPE_STRING: return String.valueOf(celula.getStringCellValue());//conteudo do tipo texto case Cell.CELL_TYPE_BOOLEAN: return "";//conteudo do tipo booleano case Cell.CELL_TYPE_BLANK: return "";//em branco default: return ""; } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
private Object getCellValue(Cell cell) { Object o = ""; try {//from w w w . jav a 2 s . c o m switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: o = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: o = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: o = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BLANK: o = ""; break; } } catch (Exception e) { e.printStackTrace(); System.err.println(e.getMessage()); } return o; }
From source file:br.unesp.rc.desafio.utils.Spreadsheet.java
public static ArrayList<String> ReadXlsSpreadsheet(File spreadsheet) { /*/*www . jav a2 s .c o m*/ Constructing File */ ArrayList values = new ArrayList<String>(); FileInputStream inputStr = null; try { inputStr = new FileInputStream(spreadsheet); } catch (FileNotFoundException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Workbook currentSpreadsheetFile = null; try { HSSFRow row; currentSpreadsheetFile = new HSSFWorkbook(inputStr); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } Sheet sheet = currentSpreadsheetFile.getSheetAt(0); Iterator<Row> rowItr = sheet.rowIterator(); while (rowItr.hasNext()) { row = (HSSFRow) rowItr.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); String cellValue = ""; switch (cell.getCellTypeEnum()) { default: // cellValue = cell.getCellFormula(); cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); break; case NUMERIC: cellValue = Double.toString(cell.getNumericCellValue()); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); case BLANK: break; case STRING: break; } if (!cell.getStringCellValue().isEmpty()) { values.add(cell.getStringCellValue()); values.add(","); // System.out.println("HOLD IT"); } else { values.add("0"); values.add(","); // System.out.println("OBJECTION!!"); } //System.out.print(cell.getStringCellValue() + " \t\t " ); } //System.out.println(); values.add(";"); } try { inputStr.close(); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } //System.out.println(values.get(0)); return values; }
From source file:browsermator.com.MyTable.java
MyTable(String csvFile) {
DataFile = csvFile;// w w w. jav a 2s. c om
DataTable = new JTable();
myEntries = new ArrayList<>();
File filecheck = new File(csvFile);
if (filecheck.isAbsolute()) {
String[] left_right_side_of_dot = csvFile.split("\\.");
String file_extension = left_right_side_of_dot[left_right_side_of_dot.length - 1];
switch (file_extension) {
case "xls":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception e) {
System.out.println("Error occurred while reading XLS file: " + e.toString());
}
break;
case "xlsx":
try {
FileInputStream file = new FileInputStream(new File(DataFile));
XSSFWorkbook workbook = new XSSFWorkbook(file);
//Get first sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
int number_of_cells = 0;
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int number_of_thesecells = row.getPhysicalNumberOfCells();
if (number_of_thesecells > number_of_cells) {
number_of_cells = number_of_thesecells;
}
}
Iterator<Row> rowIterator2 = sheet.iterator();
while (rowIterator2.hasNext()) {
Row row = rowIterator2.next();
String[] myRow = new String[number_of_cells];
Iterator<Cell> cellIterator = row.cellIterator();
int cell_index = 0;
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
Boolean boolvalue = cell.getBooleanCellValue();
String cellvalue = "false";
if (boolvalue) {
cellvalue = "true";
} else
myRow[cell_index] = cellvalue;
break;
case Cell.CELL_TYPE_NUMERIC:
myRow[cell_index] = Double.toString(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
myRow[cell_index] = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_BLANK:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_ERROR:
myRow[cell_index] = "";
break;
case Cell.CELL_TYPE_FORMULA:
myRow[cell_index] = cell.getCellFormula();
break;
}
cell_index++;
}
if (cell_index != number_of_cells) {
for (int x = cell_index; x < number_of_cells; x++)
myRow[cell_index] = "";
}
myEntries.add(myRow);
}
file.close();
}
catch (Exception ex) {
System.out.print("Exception during XLSX import: " + ex.toString());
}
break;
case "csv":
try {
CSVFileReader = new CSVReader(new FileReader(DataFile), ',', '"', '\0');
myEntries = CSVFileReader.readAll();
} catch (Exception e) {
}
}
columnnames = (String[]) myEntries.get(0);
DefaultTableModel tableModel = new DefaultTableModel(columnnames, myEntries.size() - 1);
rowcount = tableModel.getRowCount();
this.number_of_records = rowcount;
for (int x = 0; x < rowcount + 1; x++) {
int columnnumber = 0;
if (x > 0) {
for (String thiscellvalue : (String[]) myEntries.get(x)) {
tableModel.setValueAt(thiscellvalue, x - 1, columnnumber);
columnnumber++;
}
}
}
DataTable = new JTable(tableModel);
int number_of_rows = DataTable.getRowCount();
if (number_of_rows < 20) {
DataTable.setPreferredScrollableViewportSize(
new Dimension(1200, number_of_rows * DataTable.getRowHeight()));
}
} else {
columnnames[0] = "Stored URL List:" + csvFile;
DefaultTableModel tableModel = new DefaultTableModel(columnnames, 0);
DataTable = new JTable(tableModel);
DataTable.getColumnModel().getColumn(0).setPreferredWidth(200);
DataTable.setPreferredScrollableViewportSize(new Dimension(20, 0));
}
}
From source file:businessCharts.readExcell.java
public TreeMap<Double, String> getcontentList(String colName, String colName2) throws Exception { TreeMap<Double, String> treemap = new TreeMap<Double, String>(); initExcel("excel.xlsx"); XSSFSheet spreadsheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = spreadsheet.iterator(); XSSFRow row = (XSSFRow) rowIterator.next(); int setColno = getColNo(colName, row); int setColno2 = getColNo(colName2, row); while (rowIterator.hasNext()) { row = (XSSFRow) rowIterator.next(); Cell cell = row.getCell(setColno); Cell cell2 = row.getCell(setColno2); try {//from w w w .j av a 2s . c om treemap.put(cell.getNumericCellValue(), new SimpleDateFormat("yyyy-MM-dd").format(cell2.getDateCellValue())); } catch (Exception ex) { treemap.put(cell.getNumericCellValue(), "nodate"); } } return treemap; }