List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter()
From source file:gov.nij.er.ui.EntityResolutionDemo.java
License:Apache License
private void loadExcelData(File file) throws Exception { LOG.debug("Loading Excel data file " + file.getAbsolutePath()); InputStream inp = new FileInputStream(file); Workbook wb = WorkbookFactory.create(inp); // note that we read all the data out of the spreadsheet first, then // update the models. this way if there is // an error, we don't wipe out what the user already has. Sheet sheet = wb.getSheetAt(0);//from w w w . j a v a 2 s . c o m Row parametersRow = sheet.getRow(0); List<String> parameterNames = new ArrayList<String>(); for (Cell cell : parametersRow) { String v = cell.getStringCellValue(); if (parameterNames.contains(v)) { error("Duplicate field: " + v); return; } parameterNames.add(v); LOG.debug("Adding parameter " + v); } int parameterCount = parameterNames.size(); LOG.debug("Excel loading read " + parameterCount + " parameters"); List<ExternallyIdentifiableRecord> records = new ArrayList<ExternallyIdentifiableRecord>(); int rowCount = sheet.getLastRowNum(); LOG.debug("Loading " + (rowCount - 1) + " rows from " + sheet.getSheetName()); int digits = (int) (Math.floor(Math.log10(rowCount)) + 1); DataFormatter dataFormatter = new DataFormatter(); for (int rowIndex = 1; rowIndex <= rowCount; rowIndex++) { List<Attribute> attributes = new ArrayList<Attribute>(parameterCount); Row row = sheet.getRow(rowIndex); for (int i = 0; i < parameterCount; i++) { Cell cell = row.getCell(i); String v = dataFormatter.formatCellValue(cell); String parameterName = parameterNames.get(attributes.size()); attributes.add(new Attribute(parameterName, v)); // LOG.debug("Adding attribute, name=" + parameterName + ", v=" // + (v==null ? "null" : "'" + v + "'")); } records.add(new ExternallyIdentifiableRecord(makeAttributes(attributes.toArray(new Attribute[] {})), String.format("%0" + digits + "d", rowIndex))); } LOG.debug("Read " + records.size() + " records from Excel"); List<RecordWrapper> recordWrappers = EntityResolutionConversionUtils.convertRecords(records); rawDataTreeModel.init(recordWrappers); parametersTableModel.clear(); parametersTableModel.addParameters(parameterNames); }
From source file:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java
License:Apache License
public ExcelRowValueConverter(FormulaEvaluator evaluator) { this.evaluator = evaluator; formatter = new DataFormatter(); }
From source file:ik1004labb5.DAOHundExcel.java
@Override public void delete(int id) { XSSFWorkbook workbook = getExcelWorkbook(); XSSFSheet worksheet = workbook.getSheetAt(0); DataFormatter df = new DataFormatter(); //Loopa igenom nollkolumnen fr att frska hitta en matchning p ID for (Row row : worksheet) { if (df.formatCellValue(row.getCell(0)).equalsIgnoreCase(Integer.toString(id))) { if (row.getRowNum() == worksheet.getLastRowNum()) { worksheet.removeRow(row); break; } else { worksheet.shiftRows(row.getRowNum() + 1, worksheet.getLastRowNum(), -1); }/*from w w w . jav a2s . c om*/ break; } } saveToExcel(workbook); }
From source file:ik1004labb5.DAOHundExcel.java
@Override public void update(DTOHund dtoHund) { XSSFWorkbook workbook = getExcelWorkbook(); XSSFSheet worksheet = workbook.getSheetAt(0); DataFormatter df = new DataFormatter(); for (Row row : worksheet) { if (df.formatCellValue(row.getCell(0)).equalsIgnoreCase(Integer.toString(dtoHund.getId()))) { row.getCell(0).setCellValue(dtoHund.getId()); row.getCell(1).setCellValue(dtoHund.getNamn()); row.getCell(2).setCellValue(dtoHund.getRas()); row.getCell(3).setCellValue(dtoHund.getBildURL()); break; }//from www. j a va 2 s . c om } //loopa, hitta matchning och skriver ver med hjlp av dtoHund.getNamn(); saveToExcel(workbook); }
From source file:ik1004labb5.DAOHundExcel.java
@Override public List<DTOHund> getHundar() { //Ls in frn inputstream, hmta workbooken, vlj rtt sheet och ls in rader List<DTOHund> hundar = new ArrayList<>(); XSSFWorkbook workbook = getExcelWorkbook(); XSSFSheet worksheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = worksheet.iterator(); DataFormatter df = new DataFormatter(); while (rowIterator.hasNext()) { Row row = rowIterator.next();//from w ww.j ava2 s . c o m //String id = row.getCell(0).getStringCellValue(); //Problem mot Excelfilen vid lgg till. Kan jag bestmma att cellen ska vara numerisk? String id = df.formatCellValue(row.getCell(0)); String namn = df.formatCellValue(row.getCell(1)); String ras = df.formatCellValue(row.getCell(2)); String bildURL = df.formatCellValue(row.getCell(3)); //String iHundgrd = df.formatCellValue(row.getCell(4)); DTOHund dtoHund = new DTOHund(Integer.parseInt(id), namn, ras, bildURL); hundar.add(dtoHund); } return hundar; }
From source file:Interface.StateBodyEmployee.StateEmployeeWorkAreaJPanel.java
public void readFromExcel() { try {//from w w w.ja v a 2 s . c om FileInputStream file = new FileInputStream(new File("sensorData.xlsx")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<org.apache.poi.ss.usermodel.Row> rowIterator = sheet.iterator(); DataFormatter df = new DataFormatter(); while (rowIterator.hasNext()) { sensorCounter++; org.apache.poi.ss.usermodel.Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); for (a = 0; a < 6; a++) { i = (a % 6); //System.out.print(row.getCell(i)+"\t"); switch (i) { case 0: location = df.formatCellValue(row.getCell(i)); // System.out.println("location= "+location); break; case 1: waterUsage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("waterUsage= "+waterUsage); break; case 2: waterStorageCapacity = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("waterStorageCapacity= "+waterStorageCapacity); break; case 3: triggerPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("triggerPercentage= "+triggerPercentage); break; case 4: criticalPercentage = Double.parseDouble(df.formatCellValue(row.getCell(i))); // System.out.println("criticalPercentage= "+criticalPercentage); break; case 5: soilType = df.formatCellValue(row.getCell(i)); // System.out.println("location= "+soilType); break; } } if (sensorCounter == 1) { s1 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 2) { s2 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 3) { s3 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 4) { s4 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } if (sensorCounter == 5) { s5 = new Sensor(location, waterUsage, waterStorageCapacity, triggerPercentage, criticalPercentage, soilType, enterprise); } } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:io.github.jonestimd.finance.file.excel.SheetParser.java
License:Open Source License
public SheetParser(Sheet sheet, int headerRow) { final DataFormatter formatter = new DataFormatter(); final Map<Integer, String> columnNames = new HashMap<>(); final int lastRow = sheet.getLastRowNum(); sheet.getRow(headerRow)//from w w w .j a v a2 s.c o m .forEach(cell -> columnNames.put(cell.getColumnIndex(), formatter.formatCellValue(cell))); for (int index = headerRow + 1; index <= lastRow; index++) { rows.add(getRow(sheet, index, columnNames, formatter)); } }
From source file:itpreneurs.itp.report.archive.CellStyleDetails.java
License:Apache License
public static void main(String[] args) throws Exception { // if(args.length == 0) { // throw new IllegalArgumentException("Filename must be given"); // }/* w w w .j a v a2 s . c o m*/ String filename = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/Workbook1.xlsx"; Workbook wb = WorkbookFactory.create(new File(filename)); DataFormatter formatter = new DataFormatter(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { Sheet sheet = wb.getSheetAt(sn); System.out.println("Sheet #" + sn + " : " + sheet.getSheetName()); for (Row row : sheet) { System.out.println(" Row " + row.getRowNum()); for (Cell cell : row) { CellReference ref = new CellReference(cell); System.out.print(" " + ref.formatAsString()); System.out.print(" (" + cell.getColumnIndex() + ") "); CellStyle style = cell.getCellStyle(); System.out.print("Format=" + style.getDataFormatString() + " "); System.out.print("FG=" + renderColor(style.getFillForegroundColorColor()) + " "); System.out.print("BG=" + renderColor(style.getFillBackgroundColorColor()) + " "); Font font = wb.getFontAt(style.getFontIndex()); System.out.print("Font=" + font.getFontName() + " "); System.out.print("FontColor="); if (font instanceof HSSFFont) { System.out.print(renderColor(((HSSFFont) font).getHSSFColor((HSSFWorkbook) wb))); } if (font instanceof XSSFFont) { System.out.print(renderColor(((XSSFFont) font).getXSSFColor())); } System.out.println(); System.out.println(" " + formatter.formatCellValue(cell)); } } System.out.println(); } }
From source file:jdbc.BS.java
String read_BS(String company, Integer rownumber, Integer data_year) { // linenumber > 5
Integer rowindex = 2;/* w w w . jav a 2s. c o m*/
Integer n = 0;
Integer y = 0;
Integer colindex = -1;//number of years in the file
Integer max_year = 2014;
String result = "N/A";
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\All\\" + company + "_BS.xlsx"));
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (rowindex > 3 && rowindex < 30) {
if (rowindex == 4) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
colindex++;
}
}
if (rowindex == rownumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
return result;
}
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
y = colindex + data_year - max_year; //get the number of cells we need to jump
if (y > 0) {
if (y == n) {//go through the cells till reach right one
return result = fmt.formatCellValue(cell);
}
n++;
}
}
}
}
rowindex++;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if ("".equals(result) || "-".equals(result) || "N/A".equals(result)) {
return "0.0";
} else {
return result;
}
}
}
From source file:jdbc.BS.java
String read_BS_Current(String company, Integer linenumber, Integer data_year) { // linenumber > 5
Integer b = 3;// w w w.ja v a 2s . com
Integer c = 0;
Integer y = 0;
Integer n = 0;
String result = null;
DataFormatter fmt = new DataFormatter();
try {
FileInputStream file_BS = new FileInputStream(
new File("C:\\Users\\tri\\Desktop\\busi\\data\\Current\\" + company + "_BS.xlsx"));
XSSFWorkbook workbook_BS = new XSSFWorkbook(file_BS);
XSSFSheet sheet_BS = workbook_BS.getSheetAt(0);
Iterator<Row> rowIterator_BS = sheet_BS.iterator();
while (rowIterator_BS.hasNext()) {
Row row = rowIterator_BS.next();
if (b > 4 && b < 30) {
if (b == 5) {
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
c = c + 1;
}
}
if (b == linenumber) {
Iterator<Cell> cellIterator = row.cellIterator();
if (data_year == 999) {
Cell cell = cellIterator.next();
result = fmt.formatCellValue(cell);
} else {
y = c + data_year - 2014;
if (y > 0) {
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (n == y) {
result = fmt.formatCellValue(cell);
}
n = n + 1;
}
} else {
result = "N/A";
}
}
}
}
b = b + 1;
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if ("".equals(result) || "-".equals(result) || "N/A".equals(result)) {
return "0.0";
} else {
return result;
}
}
}