List of usage examples for org.apache.poi.ss.usermodel DataFormatter DataFormatter
public DataFormatter(Locale locale)
From source file:RefDiviedMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {// www . jav a 2 s .c o m db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefDiviedMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:RefSouceOnlyMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {//from w ww . j a va 2 s. c om db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefSouceOnlyMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:ch.swissbytes.Service.business.Spreadsheet.ToCSV.java
License:Apache License
/** * Open an Excel workbook ready for conversion. * * @param file An instance of the File class that encapsulates a handle * to a valid Excel workbook. Note that the workbook can be in * either binary (.xls) or SpreadsheetML (.xlsx) format. * @throws java.io.FileNotFoundException Thrown if the file cannot be located. * @throws java.io.IOException Thrown if a problem occurs in the file system. * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown * if invalid xml is found whilst parsing an input SpreadsheetML * file./*from ww w . ja va 2 s. c o m*/ */ private void openWorkbook(File file) throws FileNotFoundException, IOException, InvalidFormatException { FileInputStream fis = null; try { System.out.println("Opening workbook [" + file.getName() + "]"); fis = new FileInputStream(file); // Open the workbook and then create the FormulaEvaluator and // DataFormatter instances that will be needed to, respectively, // force evaluation of forumlae found in cells and create a // formatted String encapsulating the cells contents. this.workbook = WorkbookFactory.create(fis); this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(true); } finally { if (fis != null) { fis.close(); } } }
From source file:com.openitech.db.model.ExcelDataSource.java
License:Apache License
@Override public boolean loadData(boolean reload, int oldRow) { boolean result = false; if (isDataLoaded && !reload) { return false; }/*w ww . j a va 2s .c o m*/ if (sourceFile != null) { try { Workbook workBook = WorkbookFactory.create(new FileInputStream(sourceFile)); // HSSFWorkbook workBook = new HSSFWorkbook(new FileInputStream(sourceFile)); Sheet sheet = workBook.getSheetAt(0); DataFormatter dataFormatter = new DataFormatter(Locale.GERMANY); FormulaEvaluator formulaEvaluator = workBook.getCreationHelper().createFormulaEvaluator(); int lastRowNum = sheet.getLastRowNum(); boolean isFirstLineHeader = true; //count = sheet. - (isFirstLineHeader ? 1 : 0); int tempCount = 0; for (int j = 0; j <= lastRowNum; j++) { //zane se z 0 Row row = row = sheet.getRow(j); if (row == null) { continue; } // display row number in the console. System.out.println("Row No.: " + row.getRowNum()); if (isFirstLineHeader && row.getRowNum() == 0) { populateHeaders(row); continue; } tempCount++; Map<String, DataColumn> values; if (rowValues.containsKey(row.getRowNum())) { values = rowValues.get(row.getRowNum()); } else { values = new HashMap<String, DataColumn>(); rowValues.put(row.getRowNum(), values); } // once get a row its time to iterate through cells. int lastCellNum = row.getLastCellNum(); for (int i = 0; i <= lastCellNum; i++) { DataColumn dataColumn = new DataColumn(); Cell cell = row.getCell(i); if (cell == null) { continue; } System.out.println("Cell No.: " + cell.getColumnIndex()); System.out.println("Value: " + dataFormatter.formatCellValue(cell)); if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator)); } else { dataColumn = new DataColumn(dataFormatter.formatCellValue(cell)); } switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // cell type numeric. System.out.println("Numeric value: " + cell.getNumericCellValue()); dataColumn = new DataColumn(dataFormatter.formatCellValue(cell)); break; } case Cell.CELL_TYPE_STRING: // cell type string. System.out.println("String value: " + cell.getStringCellValue()); dataColumn = new DataColumn(dataFormatter.formatCellValue(cell)); break; case Cell.CELL_TYPE_BOOLEAN: // cell type string. System.out.println("String value: " + cell.getBooleanCellValue()); dataColumn.setValue(cell.getBooleanCellValue(), Boolean.class); break; case Cell.CELL_TYPE_FORMULA: // cell type string. System.out.println( "Formula value: " + dataFormatter.formatCellValue(cell, formulaEvaluator)); dataColumn = new DataColumn(dataFormatter.formatCellValue(cell, formulaEvaluator)); break; default: dataColumn.setValue(cell.getStringCellValue(), String.class); break; } values.put(getColumnName(cell.getColumnIndex()).toUpperCase(), dataColumn); } } count = tempCount; isDataLoaded = true; //se postavim na staro vrstico ali 1 if (oldRow > 0) { absolute(oldRow); } else { first(); } result = true; } catch (Exception ex) { Logger.getLogger(ExcelDataSource.class.getName()).log(Level.SEVERE, null, ex); result = false; } } return result; }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
/** * Creates a new CellValueManager and ties it to the given Spreadsheet. * * @param spreadsheet/*w w w. j a va 2 s .co m*/ * Target Spreadsheet */ public CellValueManager(Spreadsheet spreadsheet) { this.spreadsheet = spreadsheet; UI current = UI.getCurrent(); if (current != null) { formatter = new DataFormatter(current.getLocale()); } else { formatter = new DataFormatter(); } }
From source file:com.vaadin.addon.spreadsheet.CellValueManager.java
License:Open Source License
protected void updateLocale(Locale locale) { formatter = new DataFormatter(locale); localeDecimalSymbols = DecimalFormatSymbols.getInstance(locale); originalValueDecimalFormat = new DecimalFormat(EXCEL_FORMULA_BAR_DECIMAL_FORMAT, localeDecimalSymbols); cellValueFormatter.setLocaleDecimalSymbols(localeDecimalSymbols); }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java
License:Open Source License
public static Double parseNumber(Cell cell, String value, Locale locale) { if (value == null || value.trim().isEmpty()) { return null; }//from ww w .ja v a2s. co m if (cell.getCellStyle().getDataFormatString() != null) { DataFormatter df = new DataFormatter(locale); try { Method formatter = df.getClass().getDeclaredMethod("getFormat", Cell.class); formatter.setAccessible(true); Format format = (Format) formatter.invoke(df, cell); if (format != null) { ParsePosition parsePosition = new ParsePosition(0); Object parsed = format.parseObject(value, parsePosition); if (parsePosition.getIndex() == value.length()) { if (parsed instanceof Double) { return (Double) parsed; } else if (parsed instanceof Number) { return ((Number) parsed).doubleValue(); } } } } catch (NoSuchMethodException e) { } catch (InvocationTargetException e) { } catch (IllegalAccessException e) { } catch (UnsupportedOperationException e) { } } return parseNumber(value, locale); }
From source file:com.waveconn.Excel2MySQL.java
License:Apache License
void dbImport() { FileInputStream excel_file = null; try {// w ww . j a va 2 s .co m excel_file = new FileInputStream(new File(excel_file_path)); } catch (FileNotFoundException e) { System.out.println("File not found: " + excel_file_path); System.exit(-3); } try { workbook = WorkbookFactory.create(excel_file); evaluator = workbook.getCreationHelper().createFormulaEvaluator(); formatter = new DataFormatter(true); Sheet sheet = null; Row row = null; int lastRowNum = 0; System.out.println("Reading excel file content from " + excel_file_path); // Discover how many sheets there are in the workbook.... int numSheets = workbook.getNumberOfSheets(); // and then iterate through them. for (int i = 0; i < numSheets; i++) { // Get a reference to a sheet and check to see if it contains any rows. sheet = workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { // Note down the index number of the bottom-most row and // then iterate through all of the rows on the sheet starting // from the very first row - number 1 - even if it is missing. // Recover a reference to the row and then call another method // which will strip the data from the cells and build lines lastRowNum = sheet.getLastRowNum(); int start = 0; if (!is_read_first_line) start = 1; for (int j = start; j <= lastRowNum; j++) { row = sheet.getRow(j); this.rowToData(row); } } } } catch (IOException e) { e.printStackTrace(); System.out.println("IOException: " + excel_file_path); System.exit(-4); } catch (InvalidFormatException e) { e.printStackTrace(); System.out.println("Invalid Format: " + excel_file_path); System.exit(-5); } finally { if (excel_file != null) { try { excel_file.close(); } catch (IOException e) { e.printStackTrace(); System.out.println("IOException: " + excel_file_path); System.exit(-6); } } } //put valid rows into DB System.out.println("Inserting valid rows into DB table " + db_url + "/" + db_table); insertDB(); System.out.println(); //save invalid rows if any int errs = errorRows.size(); if (errs > 0) { saveError(); } else { System.out.println("There is no invalid row"); } }
From source file:edu.isi.karma.imp.excel.ToCSV.java
License:Apache License
private void openWorkbook(InputStream is) throws InvalidFormatException, IOException { this.workbook = WorkbookFactory.create(is); this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(true); }
From source file:edu.si.services.beans.excel.ExcelToCSV.java
License:Apache License
/** * Process the contents of a stream, convert the contents of the Excel * workbook into CSV format and write the result to the specified stream. * Workbooks with the .xls or * .xlsx formats are supported. This method * will ensure that the CSV file created contains the comma field separator * and that embedded characters such as the field separator, the EOL and * double quotes are escaped in accordance with Excel's convention. * * @param inStream An instance of the InputStream class that encapsulates the * Excel workbook that is to be converted. * @param separator A String that contains the value, usually one character, * that is used to separate the cells on a row. Can be null. * @param formattingConvention An int that determines if Excel style escaping * or Unix style escaping should be used. * @throws java.io.IOException Thrown if the stream handling encounters any * problems during processing./*w w w .j a va 2s . c o m*/ * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException Thrown * if the spreadsheet format cannot be processed * @throws org.apache.poi.openxml4j.exceptions Thrown if the input is not a * supported Excel format. */ public OutputStream convertExcelToCSV(InputStream inStream, String separator, String formattingConvention) throws IOException, InvalidFormatException { OutputStream outStream = new ByteArrayOutputStream(); if (separator == null) { separator = ExcelToCSV.DEFAULT_SEPARATOR; } // Ensure the value passed to the formattingConvention parameter is // within range. if (formattingConvention == null) { formattingConvention = ExcelToCSV.EXCEL_STYLE_ESCAPING; } else if (!formattingConvention.equals(ExcelToCSV.EXCEL_STYLE_ESCAPING) && !formattingConvention.equals(ExcelToCSV.UNIX_STYLE_ESCAPING)) { logger.warn("ExcelToCSV: Improper formatting convention provided"); } // Copy the seperator character and formatting convention into local // variables for use in other methods. this.separator = separator; this.formattingConvention = formattingConvention; workbook = WorkbookFactory.create(inStream); this.evaluator = this.workbook.getCreationHelper().createFormulaEvaluator(); this.formatter = new DataFormatter(true); // Convert its contents into a CSV file. this.convertToCSV(); // Return the converted CSV. return this.getCSVStream(outStream); }