List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.binlist.binlistreader.BinlistReader.java
public static void main(String[] args) { String result[] = null;//from ww w . j ava 2s. co m String folder = "/opt/"; String sourceFileName = "binlist.xls"; FileInputStream fis = null; try { fis = new FileInputStream(folder + sourceFileName); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator = null; Row row = null; Cell cell = null; int cellNo = 0; String binno = ""; String longUrl = ""; String shortUrl = ""; int rownum = 0; while (rowIterator.hasNext()) { rownum++; if (rownum == 1) continue; row = rowIterator.next(); cellIterator = row.cellIterator(); cellNo = 0; binno = ""; longUrl = ""; shortUrl = ""; cell = row.getCell(4); if (cell != null && cell.getStringCellValue() != null) { binno = cell.getStringCellValue(); } if (binno != null && binno.length() > 5) { result = postRequestV3(binno); System.out.println("rownum..:" + rownum + " binno..:" + binno + " result..:" + result.length); if (result != null) { row.getCell(5).setCellValue(checkNull(result[0], "")); row.getCell(6).setCellValue(checkNull(result[1], "")); row.getCell(7).setCellValue(checkNull(result[2], "")); } } } System.out.println("rownum..:" + rownum); fis.close(); FileOutputStream out = new FileOutputStream(folder + "newfile/" + sourceFileName); workbook.write(out); out.close(); workbook.close(); workbook = null; } catch (Exception e) { e.printStackTrace(); } finally { if (fis != null) try { fis.close(); } catch (IOException ex) { Logger.getLogger(BinlistReader.class.getName()).log(Level.SEVERE, null, ex); } fis = null; } }
From source file:com.bluecubs.xinco.index.filetypes.XincoIndexMicrosoftExcel.java
License:Apache License
public String getFileContentString(File f) { int i, j, j2, k; short k2;//from w w w . jav a 2 s.c o m HSSFWorkbook wb = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; InputStream is = null; String cell_string = ""; try { is = new FileInputStream(f); POIFSFileSystem fs = new POIFSFileSystem(is); wb = new HSSFWorkbook(fs); for (i = 0; i < wb.getNumberOfSheets(); i++) { sheet = wb.getSheetAt(i); j2 = 0; for (j = 0; j < sheet.getPhysicalNumberOfRows(); j++) { while ((row = sheet.getRow(j2)) == null) { j2++; } j2++; k2 = 0; for (k = 0; k < row.getPhysicalNumberOfCells(); k++) { while ((cell = row.getCell(k2)) == null) { k2++; } k2++; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: cell_string = cell_string + cell.getNumericCellValue() + "\t"; break; case HSSFCell.CELL_TYPE_STRING: cell_string = cell_string + cell.getStringCellValue() + "\t"; break; default: } } cell_string = cell_string + "\n"; } cell_string = cell_string + "\n\n\n"; } is.close(); } catch (Exception fe) { cell_string = null; if (is != null) { try { is.close(); } catch (Exception ise) { } } } return cell_string; }
From source file:com.cablelabs.fsm.SystemSettings.java
License:Open Source License
/** * Reads the configuration parameters from the specified worksheet in the * Xcel spreadsheet. //from w ww. j a v a2 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); HSSFRow row;//from w ww. j a v a 2s .co m 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.citrix.g2w.webdriver.util.ReadExcelReport.java
License:Open Source License
/** * Method to read the file.//from www. ja v a2 s. c om * * @param filePath * (file to read) * @return testReport */ public Map<Integer, List> readFile(final String filePath) { this.logger.log("Absolute file path:" + filePath); List<List> report = null; try { FileInputStream file = new FileInputStream(new File(filePath)); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get the worksheet count int workSheetCount = workbook.getNumberOfSheets(); for (int count = 0; count < workSheetCount; count++) { // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(count); Row rowObj = null; List rowData = new ArrayList(); report = new ArrayList<List>(); for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) { rowObj = sheet.getRow(rowNum); rowData = new ArrayList(); if (rowObj != null) { Iterator<Cell> rowCellIterator = rowObj.cellIterator(); while (rowCellIterator.hasNext()) { Cell cellObj = rowCellIterator.next(); rowData.add(this.getValue(cellObj)); } report.add(rowData); } } this.workSheetsContent.put(count, report); } } catch (Exception e) { e.printStackTrace(); String errorMessage = "Error while reading file : " + filePath; this.logger.log(errorMessage); this.logger.log(e.getMessage()); throw new RuntimeException(e.getMessage()); } return this.workSheetsContent; }
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; ////w ww .j a v a 2s . c o m //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.cms.utils.ExcelReader.java
public static List importExcel(File file, int iSheet, int iBeginRow, int iFromCol, int iToCol, int rowBack) throws FileNotFoundException { List lst = new ArrayList(); FileInputStream flieInput = new FileInputStream(file); HSSFWorkbook workbook; try {//from www . j a v a 2 s .c om workbook = new HSSFWorkbook(flieInput); HSSFSheet worksheet = workbook.getSheetAt(iSheet); int irowBack = 0; for (int i = iBeginRow; i <= worksheet.getLastRowNum(); i++) { Object[] obj = new Object[iToCol - iFromCol + 1]; Row row = worksheet.getRow(i); if (row != null) { int iCount = 0; int check = 0; for (int j = iFromCol; j <= iToCol; j++) { Cell cell = row.getCell(j); if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: obj[iCount] = cell.getStringCellValue().trim(); break; case Cell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); obj[iCount] = DateTimeUtils.convertDateToString(date, "dd/MM/yyyy"); } else { Double doubleValue = (Double) cell.getNumericCellValue(); //String.format("%.0f", doubleValue); List<String> lstValue = DataUtil.splitDot(String.valueOf(doubleValue)); if (lstValue.get(1).matches("[0]+")) { obj[iCount] = lstValue.get(0); } else { obj[iCount] = String.format("%.2f", doubleValue).trim(); } } break; case Cell.CELL_TYPE_BLANK: check++; break; } } else { obj[iCount] = null; } iCount += 1; } if (check != (iToCol - iFromCol + 1)) { lst.add(obj); } } else { irowBack += 1; } if (irowBack == rowBack) { break; } } } catch (IOException ex) { lst = null; } return lst; }
From source file:com.cn.controller.BaseInfoController.java
/** * ?//w w w. j av a 2s .c o m * * @param fileName * @return */ public int importData(String fileName) { InputStream inputStream = null; try { File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<BaseInfo> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 4) { BaseInfo info = new BaseInfo(); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(0).getStringCellValue()); row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(1).getStringCellValue()); if (Units.strIsEmpty(info.getJianHao())) continue; row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarNum((int) row.getCell(3).getNumericCellValue()); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); imports.add(info); } } DatabaseOpt opt; Connection conn = null; CallableStatement statement = null; opt = new DatabaseOpt(); try { conn = opt.getConnect(); conn.setAutoCommit(false); statement = conn.prepareCall("insert into tbBaseInfo(baseId, pinMing, jianHao, carModel, carNum)" + "values(BASEID.NEXTVAL, ?, ?, ?, ?)"); for (BaseInfo infoImport : imports) { statement.setString(1, infoImport.getPinMing()); statement.setString(2, infoImport.getJianHao()); statement.setString(3, infoImport.getCarModel()); statement.setInt(4, infoImport.getCarNum()); statement.addBatch(); } statement.executeBatch(); conn.commit(); return 0; } catch (SQLException ex) { try { if (conn != null) conn.rollback(); } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.cn.controller.CommonController.java
/** * ?/* ww w . j av a 2 s . co m*/ * * @param beanPackage * @param className * @param fileName * @return * @throws Exception */ public ArrayList<Object> importData(String beanPackage, String className, String fileName) throws Exception { //??? Class objClass = Class.forName(beanPackage + className); Field[] fields = objClass.getDeclaredFields(); ArrayList<Field> accessFields = new ArrayList<>(); ArrayList<String> fieldDes = new ArrayList<>(); for (Field field : fields) { if (field.isAnnotationPresent(FieldDescription.class)) { FieldDescription description = field.getAnnotation(FieldDescription.class); if (description.operate().compareTo("import") == 0) { fieldDes.add(description.description()); accessFields.add(field); } } } //?, ?Excel? InputStream inputStream = null; File file = new File(fileName); inputStream = new FileInputStream(file); Sheet sheet; if (fileName.endsWith(".xls")) { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else if (fileName.endsWith(".xlsx")) { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else { logger.info("?Excel!"); return null; } Row headerRow = sheet.getRow(0); //????, ??? //System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size()); if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) { //json = Units.objectToJson(-1, "???, ?, ???", null); return null; } //????? int[] templateDataIndex = new int[fieldDes.size()]; for (int i = 0; i < fieldDes.size(); i++) { Cell cell = headerRow.getCell(i); cell.setCellType(Cell.CELL_TYPE_STRING); String fieldName = cell.getStringCellValue(); if (fieldDes.indexOf(fieldName) != -1) { templateDataIndex[fieldDes.indexOf(fieldName)] = i; } else { return null; } } ArrayList<Object> result = new ArrayList<>(); //??, List for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row == null) { continue; } if (Units.isEmptyRowForExcel(row)) { continue; } Object object = objClass.newInstance(); for (int j = 0; j < accessFields.size(); j++) { Field field = accessFields.get(j); field.setAccessible(true); Cell cell = row.getCell(templateDataIndex[j]); if (field.getType() == int.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue()); } } } else if (field.getType() == float.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue()); } } } else if (field.getType() == double.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell))); } } } else if (field.getType() == boolean.class) { if (cell == null) { field.set(object, false); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, false); } else { field.set(object, Boolean.valueOf(Units.getStringCellValue(cell))); } } } else { if (cell == null) { field.set(object, ""); } else { field.set(object, Units.getStringCellValue(cell)); } } } result.add(object); } return result; }
From source file:com.cn.controller.InterfaceController.java
/** * Excel?/*from ww w . ja v a 2 s . c o m*/ * @param beanPackage * @param tableName * @param fileName * @param conn * @return * @throws Exception */ public String importData(String beanPackage, String tableName, String fileName, String conn) throws Exception { String json; CommonController commonController = new CommonController(); //??? Class objClass = Class.forName(beanPackage + tableName); Field[] fields = objClass.getDeclaredFields(); ArrayList<Field> accessFields = new ArrayList<>(); ArrayList<String> fieldDes = new ArrayList<>(); for (Field field : fields) { if (field.isAnnotationPresent(FieldDescription.class)) { FieldDescription description = field.getAnnotation(FieldDescription.class); if (description.operate().compareTo("import") == 0) { fieldDes.add(description.description()); accessFields.add(field); } } } //?, ?Excel? InputStream inputStream = null; File file = new File(fileName); inputStream = new FileInputStream(file); Sheet sheet; if (fileName.endsWith(".xls")) { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else if (fileName.endsWith(".xlsx")) { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); sheet = workbook.getSheetAt(0); } else { logger.info("?Excel!"); return null; } Row headerRow = sheet.getRow(0); //????, ??? // System.out.println("cells num:" + headerRow.getPhysicalNumberOfCells() + ",des size:" + fieldDes.size()); if (headerRow == null || headerRow.getPhysicalNumberOfCells() != fieldDes.size()) { json = Units.objectToJson(-1, "???, ?, ???", null); return json; } //????? int[] templateDataIndex = new int[fieldDes.size()]; for (int i = 0; i < fieldDes.size(); i++) { Cell cell = headerRow.getCell(i); cell.setCellType(Cell.CELL_TYPE_STRING); String fieldName = cell.getStringCellValue(); templateDataIndex[fieldDes.indexOf(fieldName)] = i; } ArrayList<Object> result = new ArrayList<>(); //??, List for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (row == null) { continue; } if (Units.isEmptyRowForExcel(row)) { continue; } Object object = objClass.newInstance(); for (int j = 0; j < accessFields.size(); j++) { Field field = accessFields.get(j); field.setAccessible(true); Cell cell = row.getCell(templateDataIndex[j]); // String fieldType = field.getGenericType().toString(); if (field.getType() == int.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell)).intValue()); } } } else if (field.getType() == float.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell)).floatValue()); } } } else if (field.getType() == double.class) { if (cell == null) { field.set(object, 0); } else { if (Units.strIsEmpty(Units.getStringCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getStringCellValue(cell))); } } } else if (field.getType() == boolean.class) { if (cell == null) { field.set(object, false); } else { if (Units.strIsEmpty(Units.getCellValue(cell))) { field.set(object, false); } else { field.set(object, Boolean.valueOf(Units.getStringCellValue(cell))); } } } else { if (cell == null) { field.set(object, null); } else { field.set(object, Units.getStringCellValue(cell)); } } } result.add(object); } ArrayList<Integer> addResult = commonController.dataBaseOperate( JSONObject.toJSONString(result, Units.features), beanPackage, tableName, "add", conn); // System.out.println("import res:" + Arrays.toString(addResult.toArray())); if (addResult.get(0) == 0) { json = Units.objectToJson(0, "?" + (addResult.size() - 1) + "??!", null); } else if (addResult.get(0) == 2) { json = Units.objectToJson(2, "?!", null); } else { int successNum = 0, failedNum = 0; String failedMsg = ":"; for (int i = 1; i < addResult.size(); i++) { int res = addResult.get(i); if (res == 1) { successNum++; } else { failedNum++; failedMsg += (i + 1) + ","; } } json = Units.objectToJson(-1, "?" + successNum + "??, " + failedNum + "??!" + failedMsg, null); } return json; }