List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem
public POIFSFileSystem(InputStream stream) throws IOException
From source file:com.cablelabs.fsm.SystemSettings.java
License:Open Source License
/** * Reads the configuration parameters from the specified worksheet in the * Xcel spreadsheet. /*w w w .j a v a 2 s.co m*/ * * @param arglist - the arguments of the spreadsheet to read, such as file * name, sheet, and columns to read. * @param forRegistrar - a flag stating whether to use the hashKey as the key * in the properties table or the value of the IP property * @param forRemoval - whether this is being added or removed from the * properties table. * * @return - returns the Network Element Label this properties were saved under or * null. */ @SuppressWarnings("deprecation") private Hashtable<String, Properties> readConfigParameters(String[] arglist) { POIFSFileSystem fs = null; HSSFWorkbook wb = null; int xlsSheetIndex = 0; ArrayList<Object> hashKeyList = new ArrayList<Object>(); String hashKey = null; String propertyKey = null; String propertyValue = null; Hashtable<String, Properties> table = null; // verify execution syntax - make sure proper number of parameters are passed in if (arglist.length != 5) { logger.trace(PC2LogCategory.Settings, subCat, "Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n"); //System.out.print("Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n"); return table; } String xlsFileName = arglist[0]; String xlsSheetName = arglist[1]; String xlsHashKeyColumn = arglist[2]; String xlsPropertyKeyColumn = arglist[3]; String xlsPropertyValueColumn = arglist[4]; logger.trace(PC2LogCategory.Settings, subCat, "Excel File Name is: " + xlsFileName + "\nExcel Sheet Name is: " + xlsSheetName + "\nExcel Key Column is: " + xlsHashKeyColumn + "\nExcel Field Name Column is: " + xlsPropertyKeyColumn + "\nExcel Value Column is: " + xlsPropertyValueColumn); // use POI to read the excel file try { fs = new POIFSFileSystem(new FileInputStream(xlsFileName)); logger.trace(PC2LogCategory.Settings, subCat, "FS= " + fs); } catch (IOException exception) { logger.trace(PC2LogCategory.Settings, subCat, "Failed to read the file named " + xlsFileName); } ; // read the workbook try { wb = new HSSFWorkbook(fs); } catch (IOException exception) { logger.trace(PC2LogCategory.Settings, subCat, "Failed to create a workbook"); } ; try { xlsSheetIndex = wb.getSheetIndex(xlsSheetName); HSSFSheet sheet = wb.getSheetAt(xlsSheetIndex); HSSFRow row = null; HSSFCell cell = null; boolean formatKnown = false; table = new Hashtable<String, Properties>(); int initialRow = 0; if (sheet.getRow(0) == null) //|| sheet.getRow(0).getCell(Short.decode("0")) != null) initialRow = 1; int rows = sheet.getLastRowNum(); for (int r = initialRow; r <= rows; r++) { row = sheet.getRow(r); if (row != null) { if (xlsHashKeyColumn != null) { cell = row.getCell(Short.decode(xlsHashKeyColumn)); // Support the original format of the settings files where the network element is in column 1 instead of zero if (!formatKnown && cell == null && xlsHashKeyColumn.equals("0")) { xlsHashKeyColumn = "1"; xlsPropertyKeyColumn = "3"; xlsPropertyValueColumn = "4"; cell = row.getCell(Short.decode(xlsHashKeyColumn)); } formatKnown = true; } else if (!formatKnown) { hashKey = SettingConstants.DUT; cell = row.getCell(Short.decode("0")); // Support the original format of the settings files where the network element is in column 1 instead of zero cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); propertyKey = getXlsCellStringValue(cell); if (cell == null || propertyKey.startsWith("Step 1")) { xlsPropertyKeyColumn = "3"; xlsPropertyValueColumn = "4"; } cell = null; formatKnown = true; } if (cell != null) { hashKey = getXlsCellStringValue(cell); } if ((hashKey != null) && (hashKey != "")) { if (!hashKeyList.contains(hashKey)) { hashKeyList.add(hashKey); table.put(hashKey, new Properties()); } } Properties p = null; if (hashKey != null) p = table.get(hashKey); if (p != null) { cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); propertyKey = getXlsCellStringValue(cell); cell = row.getCell(Short.decode(xlsPropertyValueColumn)); propertyValue = getXlsCellStringValue(cell); if (propertyKey.equals("MAC Address")) { p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue)); } // PCPCSII-125 // Create a colon verions of each MAC Address in the configuration files if (propertyKey.contains("MAC Address")) { String value = createMACAddressWithColons(propertyValue); p.setProperty(propertyKey + " Colon", value); } // Before putting the key/value pair into the property class, // (except for the LinkLocalAddress property), // see if it is an IP property and if so determine if the // value is an IPv6 address using the compressed form. if (propertyKey.contains(SettingConstants.IP) && propertyValue.contains(SettingConstants.IP_COMP_FORM)) { try { propertyValue = Conversion.ipv6LongForm(propertyValue); } catch (IllegalArgumentException iae) { logger.error(PC2LogCategory.Settings, subCat, hashKey + "- Error processing key=" + propertyKey + " value=" + propertyValue + ". Detected an invalid IPv6 address."); } } if (propertyKey != null && propertyValue != null && propertyKey != "" && propertyValue != "") p.setProperty(propertyKey, propertyValue); cell = null; logger.trace(PC2LogCategory.Settings, subCat, hashKey + "- Adding key=" + propertyKey + " value=" + propertyValue); } } } for (int q = 0; q < hashKeyList.size(); q++) { String currentHashKey = hashKeyList.get(q).toString(); Properties p = table.get(currentHashKey); addDeviceType(currentHashKey, p); // key = currentHashKey; if (currentHashKey.equals("DUT")) { String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE); if (deviceType != null) { table.put(deviceType + "0", p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + deviceType + "0 to system settings."); } } else if (currentHashKey.startsWith("UE")) { String sim = p.getProperty(SettingConstants.SIMULATED); if (sim != null && (sim.equalsIgnoreCase("false") || sim.equalsIgnoreCase("no") || sim.equalsIgnoreCase("disable"))) { String pui = p.getProperty(SettingConstants.PUI); String pui2 = p.getProperty(SettingConstants.PUI2); if (pui != null) { table.put(pui, p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + pui + " to system settings."); } if (pui2 != null && !pui2.equals(pui)) { table.put(pui2, p); logger.debug(PC2LogCategory.PCSim2, subCat, "Adding label=" + pui2 + " to system settings."); } } } } // table = new Hashtable<String, Properties>(); // // for (int q = 0; q < hashKeyList.size(); q++) { // // String currentHashKey = hashKeyList.get(q).toString(); // logger.trace(PC2LogCategory.Settings, subCat, // "****** OK hashKey(q) = " + hashKeyList.get(q) + " ******"); // logger.trace(PC2LogCategory.Settings, subCat, // "Loop " + q); // Properties p = new Properties(); // for (int r = 0; r <= rows; r++) { // row = sheet.getRow(r); // if (row != null) { // if (xlsHashKeyColumn != null) { // cell = row.getCell(Short.decode(xlsHashKeyColumn)); // } // if (cell != null) { // hashKey = getXlsCellStringValue(cell); // } // else { // hashKey = SettingConstants.DUT; // } // if (hashKey == hashKeyList.get(q)) { // cell = row.getCell(Short.decode(xlsPropertyKeyColumn)); // propertyKey = getXlsCellStringValue(cell); // cell = row.getCell(Short.decode(xlsPropertyValueColumn)); // propertyValue = getXlsCellStringValue(cell); // // if (propertyKey.equals("MAC Address")) { // p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue)); // } // // PCPCSII-125 // // Create a colon verions of each MAC Address in the configuration files // if (propertyKey.contains("MAC Address")) { // String value = createMACAddressWithColons(propertyValue); // p.setProperty(propertyKey + " Colon", value); // } // // // Before putting the key/value pair into the property class, // // (except for the LinkLocalAddress property), // // see if it is an IP property and if so determine if the // // value is an IPv6 address using the compressed form. // if (propertyKey.contains(SettingConstants.IP) && // propertyValue.contains(SettingConstants.IP_COMP_FORM)) { // try { // propertyValue = Conversion.ipv6LongForm(propertyValue); // } // catch (IllegalArgumentException iae) { // logger.error(PC2LogCategory.Settings, subCat, // currentHashKey + "- Error processing key=" + propertyKey + " value=" + propertyValue // + ". Detected an invalid IPv6 address."); // } // // } // // p.setProperty(propertyKey, propertyValue); // logger.trace(PC2LogCategory.Settings, subCat, // currentHashKey + "- Adding key=" + propertyKey + " value=" + propertyValue); // } // // } // } // // if (!(currentHashKey.equals("Network Element Label") || // currentHashKey.equals("Label"))){ // addDeviceType(currentHashKey, p); // table.put(currentHashKey, p); // // key = currentHashKey; // if (currentHashKey.equals("DUT")) { // String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE); // if (deviceType != null) { // table.put(deviceType+"0", p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + deviceType + "0 to system settings."); // // } // } // else if (currentHashKey.startsWith("UE")) { // String sim = p.getProperty(SettingConstants.SIMULATED); // if (sim != null && // (sim.equalsIgnoreCase("false") || // sim.equalsIgnoreCase("no")|| // sim.equalsIgnoreCase("disable"))) { // String pui = p.getProperty(SettingConstants.PUI); // String pui2 = p.getProperty(SettingConstants.PUI2); // if (pui != null) { // table.put(pui, p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + pui + " to system settings."); // // } // if (pui2 != null && !pui2.equals(pui)) { // table.put(pui2, p); // logger.debug(PC2LogCategory.PCSim2, subCat, // "Adding label=" + pui2 + " to system settings."); // } // } // } // } // // } } catch (Exception e) { logger.error(PC2LogCategory.Settings, subCat, "Check xls workbook name, sheet name, and column parameters."); e.printStackTrace(); } return table; }
From source file:com.cdf.objects.ExcelData.java
public void loadData(File file) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0);// ww w .j av a 2 s. co m HSSFRow row; HSSFCell cell; int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int physicalCellCount; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { physicalCellCount = sheet.getRow(i).getPhysicalNumberOfCells(); if (physicalCellCount > cols) { cols = physicalCellCount; } } } System.out.println("Row count : " + rows); System.out.println("Coloumn count: " + cols); }
From source file:com.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static DefaultTableModel splitExcelFile(File toSplit, int acceptFormula, int tableIndex, boolean convertCharsToEntites) throws Exception { //file = toSplit; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(toSplit)); HSSFWorkbook wb = new HSSFWorkbook(fs); if (tableIndex > -1) { HSSFSheet sheet = wb.getSheetAt(tableIndex - 1); boolean isBlankRow = true; int numCols = 0; int numRows = 0; ////from w w w . j a v a2 s . c om //int firstRow = 0; int firstColumn = 0; int iColumn = 0; //firstRow = getFirstRow(sheet,-1); /*if(firstRow==-1) { //MessageHandler.showError("Error, Cannot Read Sheet: "+tableIndex,"Import From Excel Error"); return(null); } else {*/ HSSFRow row = sheet.getRow(sheet.getFirstRowNum()); firstColumn = getFirstColumn(sheet); numCols = getNumberOfCells(sheet); //row = sheet.getRow(firstRow); //numRows = sheet.getLastRowNum() - firstRow; Vector rows = new Vector(); /*System.out.println(firstColumn+":"+numCols); System.out.println(sheet.getFirstRowNum()+" To "+sheet.getLastRowNum()); System.out.println(sheet.getPhysicalNumberOfRows());*/ for (int rCnt = sheet.getFirstRowNum(); rCnt < sheet.getPhysicalNumberOfRows(); ++rCnt) { //reset the blank row boolean isBlankRow = true; row = sheet.getRow(rCnt); //System.out.println(numCols + ":" + firstColumn); String[] separated = new String[numCols - firstColumn]; for (int cCnt = firstColumn; cCnt < numCols; ++cCnt) { try { HSSFCell cell = row.getCell((short) cCnt); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC://System.out.println(rCnt+":"+cCnt+ " is numeric"); double value = row.getCell((short) cCnt).getNumericCellValue(); try { //get the long value which eliminates the decimal point long iValue = (new Double(value)).longValue(); //get the double value from this long value double longValue = (new Long(iValue)).doubleValue(); //subtract the two, if answer is 0 then //value can be converted, //if not then it can't if (value - longValue == 0) { //use long value separated[cCnt - firstColumn] = String.valueOf(iValue); } //end if else { //use double value separated[cCnt - firstColumn] = String.valueOf(value); } //end else } //end try catch (NumberFormatException e) { //use double value separated[cCnt - firstColumn] = String.valueOf(value); } //end catch break; case HSSFCell.CELL_TYPE_STRING://System.out.println(rCnt+":"+cCnt+ " is string"); isBlankRow = false; separated[cCnt - firstColumn] = row.getCell((short) cCnt).getStringCellValue(); break; case HSSFCell.CELL_TYPE_FORMULA://System.out.println(rCnt+":"+cCnt+ " is formula"); isBlankRow = false; if (acceptFormula == 0) { //prompt String[] options = { "Cell Value", "Formula Text" }; Object formulaValue = JOptionPane.showInputDialog(null, "This worksheet contains formulas\n" + "What format would you like to import " + "the formula cells by: ", "Import From Table", JOptionPane.INFORMATION_MESSAGE, null, options, options[0]); if (formulaValue.toString().equalsIgnoreCase(options[0])) { //accept values acceptFormula = 2; } //end if else { acceptFormula = 1; } //end else } //end if else if (acceptFormula == 1) { //accept formula separated[cCnt - firstColumn] = row.getCell((short) cCnt).getCellFormula(); } //end else else if (acceptFormula == 2) { //dont accept formula double doubleValue = row.getCell((short) cCnt).getNumericCellValue(); Double dValue = new Double(doubleValue); if (dValue.isNaN()) { //should have been a string separated[cCnt - firstColumn] = row.getCell((short) cCnt) .getStringCellValue(); } //end if else { try { //get the long value which eliminates the decimal point long iValue = (new Double(doubleValue)).longValue(); //get the double value from this long value double longValue = (new Long(iValue)).doubleValue(); //subtract the two, if answer is 0 then //value can be converted, //if not then it can't if (doubleValue - longValue == 0) { //use long value separated[cCnt - firstColumn] = String.valueOf(iValue); } //end if else { //use double value separated[cCnt - firstColumn] = String.valueOf(doubleValue); } //end else } //end try catch (NumberFormatException e) { //use double value separated[cCnt - firstColumn] = String.valueOf(doubleValue); } //end catch } //end else } //end else break; case HSSFCell.CELL_TYPE_ERROR://System.out.println(rCnt+":"+cCnt+ " is error"); separated[cCnt - firstColumn] = ""; break; case HSSFCell.CELL_TYPE_BOOLEAN://System.out.println(rCnt+":"+cCnt+ " is boolean"); isBlankRow = false; boolean booleanValue = row.getCell((short) cCnt).getBooleanCellValue(); separated[cCnt - firstColumn] = String.valueOf(booleanValue); break; case HSSFCell.CELL_TYPE_BLANK://System.out.println(rCnt+":"+cCnt+ " is blank"); separated[cCnt - firstColumn] = ""; break; } } //end if cell!=null else { } //end else } catch (Exception e) { //just a blank cell separated[cCnt - firstColumn] = ""; } //end try catch } //end for cCnt if (!isBlankRow) { rows.add(separated); } //HSSFCell cell; } //end for rCnt DefaultTableModel tableModel = addRowsToTable(rows, numCols - firstColumn); /*fileName = file.getAbsolutePath(); fileName = fileName.substring(0, fileName.lastIndexOf(".")); fileName += ".xml";*/ return (tableModel); } //end else //} return (null); }
From source file:com.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static String[] getWorkSheets(File toSplit) throws FileNotFoundException, IOException, Exception { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(toSplit)); HSSFWorkbook wb = new HSSFWorkbook(fs); String[] workSheets = new String[wb.getNumberOfSheets() + 1]; //this.tablesCombo.addItem("- Please Select -"); workSheets[0] = "- Please Select -"; for (int cnt = 0; cnt < wb.getNumberOfSheets(); ++cnt) { workSheets[cnt + 1] = wb.getSheetName(cnt); }/* w w w . j ava 2s . c o m*/ return (workSheets); }
From source file:com.codecrate.shard.transfer.excel.ExcelObjectImporter.java
License:Apache License
public Collection importObjects(File file, ProgressMonitor progress) { Collection results = new ArrayList(); try {/* www .j a v a2 s.c om*/ POIFSFileSystem poifs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook workbook = new HSSFWorkbook(poifs); HSSFSheet sheet = workbook.getSheetAt(0); int firstRow = sheet.getFirstRowNum() + 1; int lastRow = sheet.getLastRowNum(); progress.startTask("Import objects from " + file.getName(), lastRow - firstRow); for (int currentRow = firstRow; currentRow <= lastRow; currentRow++) { HSSFRow row = sheet.getRow(currentRow); try { Object result = rowHandler.handleRow(row); results.add(result); } catch (Exception e) { LOG.error("Error importing row: " + currentRow, e); } progress.completeUnitOfWork(); } } catch (Exception e) { LOG.error("Error importing file: " + file, e); } progress.finish(); return results; }
From source file:com.concursive.connect.web.modules.plans.utils.AssignmentExcelImporter.java
License:Open Source License
/** * Description of the Method/*from www. java2 s.c om*/ * * @param requirement Description of the Parameter * @param db Description of the Parameter * @param buffer Description of the Parameter * @return Description of the Return Value * @throws java.sql.SQLException Description of the Exception */ public static boolean parse(byte[] buffer, Requirement requirement, Connection db) throws SQLException { if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> parseExcel"); } try { db.setAutoCommit(false); // stream the Excel Spreadsheet from the uploaded byte array POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(buffer)); HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); // get the first sheet HSSFSheet sheet = hssfworkbook.getSheetAt(0); // define objects for housing spreadsheet data HSSFRow currentRow = sheet.getRow(0); // parse each row, create and insert into a new requirement with a tree int rows = sheet.getPhysicalNumberOfRows(); if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> Number of rows: " + rows); } // Columns int columnHeader = -1; int columnMax = -1; boolean columnItemComplete = false; short itemColumn = -1; short priorityColumn = -1; short assignedToColumn = -1; short effortColumn = -1; short startColumn = -1; short endColumn = -1; // parse for (int r = 0; r < rows; r++) { currentRow = sheet.getRow(r); if (currentRow != null) { // Search for header if (columnHeader == -1) { int cells = currentRow.getPhysicalNumberOfCells(); for (short c = 0; c < cells; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null) { if ("Item".equals(getValue(cell))) { columnHeader = r; itemColumn = c; columnMax = c; } else if (itemColumn > -1 && !columnItemComplete && c > itemColumn) { if ("".equals(getValue(cell))) { columnMax = c; } else if (!"".equals(getValue(cell))) { columnItemComplete = true; } } if ("Priority".equals(getValue(cell))) { columnHeader = r; priorityColumn = c; } else if ("Assigned To".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Lead".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Effort".equals(getValue(cell))) { columnHeader = r; effortColumn = c; } else if ("Start".equals(getValue(cell))) { columnHeader = r; startColumn = c; } else if ("End".equals(getValue(cell))) { columnHeader = r; endColumn = c; } } } } // Process each column if (columnHeader > -1 && r > columnHeader) { boolean gotOne = false; Assignment assignment = new Assignment(); assignment.setProjectId(requirement.getProjectId()); assignment.setRequirementId(requirement.getId()); // Activities and folders if (itemColumn > -1) { // Get the first indent level that has data for (short c = itemColumn; c <= columnMax; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null && !"".equals(getValue(cell))) { assignment.setRole(getValue(cell)); assignment.setIndent(c); gotOne = true; break; } } } if (gotOne) { // Priority if (priorityColumn > -1) { HSSFCell cell = currentRow.getCell(priorityColumn); if (cell != null) { assignment.setPriorityId(getValue(cell)); } } // Effort if (effortColumn > -1) { HSSFCell cell = currentRow.getCell(effortColumn); if (cell != null) { assignment.setEstimatedLoe(getValue(cell)); if (assignment.getEstimatedLoeTypeId() == -1) { assignment.setEstimatedLoeTypeId(2); } } } // Assigned To if (assignedToColumn > -1) { HSSFCell cell = currentRow.getCell(assignedToColumn); if (cell != null) { assignment.addUsers(getValue(cell)); } } // Start Date if (startColumn > -1) { HSSFCell cell = currentRow.getCell(startColumn); if (cell != null) { assignment.setEstStartDate(getDateValue(cell)); } } // Due Date if (endColumn > -1) { HSSFCell cell = currentRow.getCell(endColumn); if (cell != null) { assignment.setDueDate(getDateValue(cell)); } } assignment.setEnteredBy(requirement.getEnteredBy()); assignment.setModifiedBy(requirement.getModifiedBy()); assignment.setStatusId(1); // Make sure a valid priority is set if (assignment.getPriorityId() < 1 || assignment.getPriorityId() > 3) { assignment.setPriorityId(2); } // Make sure user is on team, before adding, else unset the field if (!assignment.hasValidTeam(db)) { assignment.getAssignedUserList().clear(); } // Insert the assignment assignment.insert(db); if (System.getProperty("DEBUG") != null) { System.out.println( "AssignmentExcelImporter-> Assignment Inserted: " + assignment.getId()); } } } } } db.commit(); } catch (Exception e) { db.rollback(); e.printStackTrace(System.out); return false; } finally { db.setAutoCommit(true); } return true; }
From source file:com.daphne.es.showcase.excel.service.ExcelDataService.java
License:Apache License
/** * excel 2003 biff?//from w ww .ja v a2 s. c o m * xml? ?SAX * @param user * @param is */ @Async public void importExcel2003(final User user, final InputStream is) { ExcelDataService proxy = ((ExcelDataService) AopContext.currentProxy()); BufferedInputStream bis = null; InputStream dis = null; try { long beginTime = System.currentTimeMillis(); List<ExcelData> dataList = Lists.newArrayList(); //? bis = new BufferedInputStream(is); // org.apache.poi.poifs.filesystem.Filesystem POIFSFileSystem poifs = new POIFSFileSystem(bis); // ? Workbook(excel )? dis = poifs.createDocumentInputStream("Workbook"); // HSSFRequest HSSFRequest req = new HSSFRequest(); // ? req.addListenerForAllRecords(new Excel2003ImportListener(proxy, dataList, batchSize)); // HSSFEventFactory factory = new HSSFEventFactory(); // ??? factory.processEvents(req, dis); //??batchSize? if (dataList.size() > 0) { proxy.doBatchSave(dataList); } long endTime = System.currentTimeMillis(); Map<String, Object> context = Maps.newHashMap(); context.put("seconds", (endTime - beginTime) / 1000); notificationApi.notify(user.getId(), "excelImportSuccess", context); } catch (Exception e) { log.error("excel import error", e); Map<String, Object> context = Maps.newHashMap(); context.put("error", e.getMessage()); notificationApi.notify(user.getId(), "excelImportError", context); } finally { // ? IOUtils.closeQuietly(bis); // ? IOUtils.closeQuietly(dis); } }
From source file:com.diversityarrays.kdxplore.importdata.bms.BmsExcelImportHelper.java
License:Open Source License
public BmsExcelImportHelper(File excelFile) throws IOException { this.excelFile = excelFile; if (!excelFile.getName().toLowerCase().endsWith(".xls")) { //$NON-NLS-1$ throw new IllegalArgumentException("Only .xls files supported"); }//from w ww . j a va2 s .c o m POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile)); HSSFWorkbook workbook = null; workbook = new HSSFWorkbook(fs); try { List<String> missing = new ArrayList<>(); description = workbook.getSheet(SHEET_NAME_DESCRIPTION); if (null == description) { missing.add(SHEET_NAME_DESCRIPTION); } observation = workbook.getSheet(SHEET_NAME_OBSERVATION); if (null == observation) { missing.add(SHEET_NAME_OBSERVATION); } if (!missing.isEmpty()) { throw new IOException(StringUtil.join("Missing required worksheet(s): ", ",", missing)); } } finally { if (workbook != null) { try { workbook.close(); } catch (IOException ignore) { } } } }
From source file:com.docdoku.server.esindexer.ESTools.java
License:Open Source License
private static String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException { StringBuilder sb = new StringBuilder(); try (InputStream excelStream = new BufferedInputStream(inputStream)) { if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files POIFSFileSystem excelFS = new POIFSFileSystem(excelStream); ExcelExtractor excelExtractor = new ExcelExtractor(excelFS); sb.append(excelExtractor.getText()); } else { // New format XSSFWorkbook workBook = new XSSFWorkbook(excelStream); int numberOfSheets = workBook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { XSSFSheet sheet = workBook.getSheetAt(0); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { XSSFRow row = (XSSFRow) rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); sb.append(cell.toString()); sb.append(" "); }//from w w w . j av a 2 s .c o m sb.append("\n"); } sb.append("\n"); } } } return sb.toString(); }
From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java
License:Apache License
/** * Reads the decision table out of an Excel spreadsheet and generates the * approriate XML. //ww w .ja va 2 s. co m * @param file * @param sb * @return true if at least one decision table was found in this file * @throws Exception */ public boolean convertDecisionTable(StringBuffer data, File file, XMLPrinter out, int depth) throws Exception { if (!(file.getName().endsWith(".xls"))) return false; InputStream input = new FileInputStream(file.getAbsolutePath()); POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); boolean tablefound = false; CountsAreDirty = false; for (int i = 0; i < wb.getNumberOfSheets(); i++) { tablefound |= convertOneSheet(data, file.getName(), wb.getSheetAt(i), out, depth); } if (CountsAreDirty == true) { System.out.println( "Line Numbers on Contexts, Initial Actions, Conditions, and/or Actions are incorrect.\r\n" + "A Corrected version has been written to the decision table directory"); OutputStream output = new FileOutputStream(file.getAbsolutePath() + ".fixedCounts"); wb.write(output); } else { (new File(file.getAbsolutePath() + ".fixedCounts")).delete(); } return tablefound; }