List of usage examples for org.apache.poi.ss.usermodel Cell getCellFormula
String getCellFormula();
SUM(C4:E4) From source file:apm.common.utils.excel.ImportExcel.java
License:Open Source License
/** * ??/* ww w . jav a 2 s . c o m*/ * @param row ? * @param column ??? * @return ? */ public Object getCellValue(Row row, int column) { Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { val = cell.getNumericCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { val = cell.getStringCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { val = cell.getCellFormula(); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { val = cell.getErrorCellValue(); } } } catch (Exception e) { return val; } return val; }
From source file:bad.robot.excel.row.CopyRow.java
License:Apache License
private static void setCellDataValue(Cell oldCell, Cell newCell) { switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break;//from w w w . j ava 2s.com case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } }
From source file:br.com.gartech.nfse.integrador.util.ExcelHelper.java
private String cellToString(Cell cell) { String result = null;/*w w w .j av a 2 s. c o 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.unesp.rc.desafio.utils.Spreadsheet.java
public static ArrayList<String> ReadXlsxSpreadsheet(File spreadsheet) { /*/*from w w w . j ava 2 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 { XSSFRow row1; currentSpreadsheetFile = new XSSFWorkbook(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()) { row1 = (XSSFRow) rowItr.next(); Iterator<Cell> cellIterator = row1.cellIterator(); while (cellIterator.hasNext()) { String cellValue = ""; Cell cell = cellIterator.next(); switch (cell.getCellTypeEnum()) { default: cellValue = cell.getCellFormula(); cell.setCellType(CellType.STRING); cell.setCellValue(cellValue); break; case BLANK: break; case STRING: break; } values.add(cell.getStringCellValue()); System.out.print(cell.getStringCellValue() + " \t\t "); } System.out.println(); } try { inputStr.close(); } catch (IOException ex) { Logger.getLogger(Spreadsheet.class.getName()).log(Level.SEVERE, null, ex); } return values; }
From source file:browsermator.com.MyTable.java
MyTable(String csvFile) {
DataFile = csvFile;/*from w w w . java 2 s . 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:cn.afterturn.easypoi.util.PoiSheetUtil.java
License:Apache License
private static void cloneCell(Cell cNew, Cell cOld) { cNew.setCellComment(cOld.getCellComment()); cNew.setCellStyle(cOld.getCellStyle()); switch (cNew.getCellType()) { case BOOLEAN: { cNew.setCellValue(cOld.getBooleanCellValue()); break;// www . j a v a 2s. co m } case NUMERIC: { cNew.setCellValue(cOld.getNumericCellValue()); break; } case STRING: { cNew.setCellValue(cOld.getStringCellValue()); break; } case ERROR: { cNew.setCellValue(cOld.getErrorCellValue()); break; } case FORMULA: { cNew.setCellFormula(cOld.getCellFormula()); break; } default: cNew.setCellValue(cOld.getStringCellValue()); } }
From source file:cn.bzvs.excel.imports.ExcelImportServer.java
License:Apache License
/** * ?key,?????/*from w ww.j a v a 2 s . c om*/ * * @param cell * @return */ private String getKeyValue(Cell cell) { Object obj = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: obj = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: obj = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: obj = cell.getNumericCellValue(); break; case Cell.CELL_TYPE_FORMULA: obj = cell.getCellFormula(); break; default: cell.setCellType(Cell.CELL_TYPE_STRING); obj = cell.getStringCellValue(); } return obj == null ? null : obj.toString().trim(); }
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * @param workbook //from w ww . ja v a 2 s.c om * @param fieldNames ?? * @param sheetName ??? * @return */ private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) { String[] strKey = fieldNames.split(","); List<Map<String, String>> listMap = new ArrayList<>(); int i = 1; try { Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } while (true) { Row row = sheet.getRow(i); if (row == null) { break; } Map<String, String> map = new HashMap<String, String>(); map.put("rowid", String.valueOf(row.getRowNum())); for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) { Cell cell; cell = row.getCell(keyIndex); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // ?cell?Date if (DateUtil.isCellDateFormatted(cell)) { // Date?CellDate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } // else { // ??Cell Integer num = new Integer((int) cell.getNumericCellValue()); cellValue = String.valueOf(num); } break; } case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: cellValue = " "; } } map.put(strKey[keyIndex], cellValue); } listMap.add(map); i++; } } catch (Exception e) { logger.debug("?" + i + "??"); throw new RuntimeException(e); } return listMap; }
From source file:co.foldingmap.data.ExcelDataConnector.java
License:Open Source License
/** * Returns a cell value as a DataCell object. * //from w w w . jav a 2 s . c o m * @param cell * @return */ public DataCell getCellText(Cell cell) { DataCell cellText; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: cellText = new DataCell(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellText = new DataCell(cell.getDateCellValue().toString()); } else { cellText = new DataCell(Double.toString(cell.getNumericCellValue())); } break; case Cell.CELL_TYPE_BOOLEAN: cellText = new DataCell(Boolean.toString(cell.getBooleanCellValue())); break; case Cell.CELL_TYPE_FORMULA: cellText = new DataCell(cell.getCellFormula()); break; default: cellText = new DataCell(""); } return cellText; }
From source file:com.accenture.control.ExtraiPlanilha.java
public static void gravaCTPlanilha(List<Plano> plano, String panilha, int linhaCelula) throws FileNotFoundException, IOException, InvalidFormatException, SQLException, ClassNotFoundException {//from w w w. j a v a 2 s.c o m ManipulaDadosSQLite banco = new ManipulaDadosSQLite(); //recebe a planilha e atribui a variavel arquivo FileInputStream arquivo = new FileInputStream(new File(panilha)); System.out.println(panilha); //instacia um workbook passando arquivo como paramentro XSSFWorkbook workbook = new XSSFWorkbook(arquivo); XSSFSheet sheetPlano = workbook.getSheetAt(1); String cadeia = "B", segmento = "C", produto = "D", funcionalidade = "E", cenarioItegrado = "F", sistemaMaster = "G", sistemasEnvolvidos = "H", fornecedor = "I", tpRequisito = "J", requisito = "K", cenarioTeste = "L", casoTeste = "M", descricao = "N", nomeStep = "P", descricaoStep = "Q", resultadoEsperado = "R", cenarioAuto = "U", type = "V", trg = "W", subject = "X", criacao = "Y"; CellReference cellReference = new CellReference("B8"); // Row row = sheetPlano.getRow(cellReference.getRow()); // Cell cell = row.getCell(cellReference.getCol()); int tamanhoLista = plano.size(); int numeroCelula = 8; int linha = linhaCelula; int celula = 12; Row row = sheetPlano.getRow(linha); Cell cell;//= row.getCell(celula); Cell celCadeia = row.getCell(1); Cell celSegmento = row.getCell(2); Cell celProduto = row.getCell(3); Cell celFuncionalidade = row.getCell(4); Cell celCenarioIntegracao = row.getCell(5); Cell celSistemaMaster = row.getCell(6); Cell celSistemaEnvolvidos = row.getCell(7); Cell celFornecedor = row.getCell(8); Cell celTpRequisito = row.getCell(9); Cell celRequisito = row.getCell(10); Cell celCenario = row.getCell(11); Cell celCasoTeste = row.getCell(12); Cell celDescricao = row.getCell(13); Cell celQtdSistemas = row.getCell(19); Cell celCenarioAuto = row.getCell(21); Cell celType = row.getCell(22); Cell celTrg = row.getCell(23); Cell celSubject = row.getCell(24); Cell celCriacao = row.getCell(25); Cell celStep = row.getCell(16); Step steps = new Step(); for (int i = 0; i < tamanhoLista; i++) { row = sheetPlano.getRow(linha); celCadeia = row.getCell(1); celSegmento = row.getCell(2); celProduto = row.getCell(3); celFuncionalidade = row.getCell(4); celCenarioIntegracao = row.getCell(5); celSistemaMaster = row.getCell(6); celSistemaEnvolvidos = row.getCell(7); celFornecedor = row.getCell(8); celTpRequisito = row.getCell(9); celRequisito = row.getCell(10); celCenario = row.getCell(11); celCasoTeste = row.getCell(12); celDescricao = row.getCell(13); Cell celComplexidade = row.getCell(15); celQtdSistemas = row.getCell(19); Cell celQtdStep = row.getCell(20); celCenarioAuto = row.getCell(21); celType = row.getCell(22); celTrg = row.getCell(23); celSubject = row.getCell(24); celCriacao = row.getCell(25); celCadeia.setCellValue(plano.get(i).getCadeia()); celSegmento.setCellValue(plano.get(i).getSegmento()); celProduto.setCellValue(plano.get(i).getProduto()); celFuncionalidade.setCellValue(plano.get(i).getFuncionalidade()); celCenarioIntegracao.setCellValue(plano.get(i).getCenarioIntegrado()); celSistemaMaster.setCellValue(plano.get(i).getSistemaMaster()); celSistemaEnvolvidos.setCellValue(plano.get(i).getSistemasEnvolvidos()); celFornecedor.setCellValue(plano.get(i).getFornecedor()); celTpRequisito.setCellValue(plano.get(i).getTpRequisito()); celRequisito.setCellValue(plano.get(i).getRequisito()); celCenario.setCellValue(plano.get(i).getCenarioTeste()); celCasoTeste.setCellValue(plano.get(i).getCasoTeste()); celDescricao.setCellValue(plano.get(i).getDescCasoTeste()); String formulaComplexibilidade = celComplexidade.getCellFormula(); celComplexidade.setCellFormula(formulaComplexibilidade); celQtdSistemas.setCellValue(plano.get(i).getQtdSistemas()); String formulaQtdStep = celQtdStep.getCellFormula(); celQtdStep.setCellFormula(formulaQtdStep); celCenarioAuto.setCellValue(plano.get(i).getCenarioAutomatizavel()); celType.setCellValue(plano.get(i).getType()); celTrg.setCellValue(plano.get(i).getTrg()); celSubject.setCellValue(plano.get(i).getSubject()); celCriacao.setCellValue(plano.get(i).getCriacaoAlteracao()); celStep = row.getCell(16); row = sheetPlano.getRow(linha); // celCasoTeste = row.getCell(12); int linhaStep = linha; List<Plano> listPlanos = banco.selectPlanoPorId(plano.get(i)); List<Step> listStep = banco.getStepPorPlano(plano.get(i)); int linhaLimpeza = linha; //limpa as clulas de step for (int cont = 0; cont <= 24; cont++) { Cell celNomeStep = row.getCell(16); Cell celDescStep = row.getCell(17); Cell celResultadoStep = row.getCell(18); String valor = null; celNomeStep.setCellValue(valor); celDescStep.setCellValue(valor); celResultadoStep.setCellValue(valor); linhaLimpeza = linhaLimpeza + 1; row = sheetPlano.getRow(linhaLimpeza); } //fim row = sheetPlano.getRow(linha); int tamanho = listStep.size(); int idTemp = 0; for (int cont = 0; cont < tamanho; cont++) { // if(idTemp != listPlanos.get(cont).getStep().getId()){ // model.addRow(new String[]{String.valueOf(listPlanos.get(cont).getStep().getNomeStep()), listPlanos.get(cont).getStep().getDescStep(), // listPlanos.get(cont).getStep().getResultadoStep(), String.valueOf(listPlanos.get(cont).getStep().getId())}); // } Cell celNomeStep = row.getCell(16); Cell celDescStep = row.getCell(17); Cell celResultadoStep = row.getCell(18); celNomeStep.setCellValue(listStep.get(cont).getNomeStep()); celDescStep.setCellValue(listStep.get(cont).getDescStep()); celResultadoStep.setCellValue(listStep.get(cont).getResultadoStep()); //caso o ct seja alterao pinta os steps de amarelo - inicio if (plano.get(i).getCriacaoAlteracao().equals("Alterao")) { Color color = new XSSFColor(java.awt.Color.yellow); // XSSFCellStyle style = workbook.createCellStyle(); // style.setBorderTop((short) 6); // double lines border // style.setBorderBottom((short) 1); // single line border // style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); // // celNomeStep.setCellStyle(style); } //fim linhaStep = linhaStep + 1; row = sheetPlano.getRow(linhaStep); celStep = row.getCell(16); idTemp = listPlanos.get(cont).getId(); } linha = linha + 25; row = sheetPlano.getRow(linha); cell = celCadeia; } FileOutputStream fileOut = new FileOutputStream(new File(panilha)); workbook.write(fileOut); fileOut.close(); }