List of usage examples for org.apache.poi.ss.usermodel Row getPhysicalNumberOfCells
int getPhysicalNumberOfCells();
From source file:com.cn.controller.InterfaceController.java
/** * Excel?//from w w w.ja v a 2 s . co 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; }
From source file:com.cn.controller.OrderPlanController.java
/** * ?//from w w w. ja v a2s.c o m * * @param fileName * @param planType * @return -1 -- ?, 0 -- ??, -2 -- ?, -3 -- ?? */ public int importData(String fileName, int planType) { InputStream inputStream = null; int templateType;//?: 0 -- ??? | 1 -- ??? | 2 -- ?SAP? try { 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 -2; } Row headRow = sheet.getRow(0); if (headRow.getPhysicalNumberOfCells() == 38) { templateType = 0; } else if (headRow.getPhysicalNumberOfCells() == 8) { templateType = 1; } else if (headRow.getPhysicalNumberOfCells() == 34) { templateType = 2; } else { logger.info("???!"); return -3; } ArrayList<OrderPlan> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { Row row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); //logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i + ",count cell num is:" + cellNum); OrderPlan info = new OrderPlan(); // if (row.getCell(templateDataIndex[templateType][0]).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(templateDataIndex[templateType][0]).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][0]))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil .getJavaDate(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime( df.format(row.getCell(templateDataIndex[templateType][0]).getNumericCellValue())); } } // if (row.getCell(templateDataIndex[templateType][1]).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(templateDataIndex[templateType][1]).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(templateDataIndex[templateType][1]))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil .getJavaDate(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime( df.format(row.getCell(templateDataIndex[templateType][1]).getNumericCellValue())); } } //? row.getCell(templateDataIndex[templateType][2]).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(templateDataIndex[templateType][2]).getStringCellValue()); //?? row.getCell(templateDataIndex[templateType][3]).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(templateDataIndex[templateType][3]).getStringCellValue()); //??? row.getCell(templateDataIndex[templateType][4]).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(templateDataIndex[templateType][4]).getStringCellValue()); //? row.getCell(templateDataIndex[templateType][5]).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(templateDataIndex[templateType][5]).getStringCellValue()); //??? row.getCell(templateDataIndex[templateType][6]).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(templateDataIndex[templateType][6]).getStringCellValue()); //? row.getCell(templateDataIndex[templateType][7]).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(templateDataIndex[templateType][7]).getNumericCellValue()); imports.add(info); /* if (templateType == 0) { OrderPlan info = new OrderPlan(); // if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(0).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(0))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime(df.format(row.getCell(0).getNumericCellValue())); } } // if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(1).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(1))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime(df.format(row.getCell(1).getNumericCellValue())); } } //? row.getCell(13).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(13).getStringCellValue()); //?? row.getCell(14).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(14).getStringCellValue()); //??? row.getCell(16).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(16).getStringCellValue()); //? row.getCell(17).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(17).getStringCellValue()); //??? row.getCell(21).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(21).getStringCellValue()); //? row.getCell(22).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(22).getNumericCellValue()); imports.add(info); } else if (templateType == 1) { OrderPlan info = new OrderPlan(); // if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(0).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(0))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime(df.format(row.getCell(0).getNumericCellValue())); } } // if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(1).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(1))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime(df.format(row.getCell(1).getNumericCellValue())); } } //? row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(2).getStringCellValue()); //?? row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(3).getStringCellValue()); //??? row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(4).getStringCellValue()); //? row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(5).getStringCellValue()); //??? row.getCell(6).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(6).getStringCellValue()); //? row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(7).getNumericCellValue()); imports.add(info); } else if (templateType == 2) { OrderPlan info = new OrderPlan(); // if (row.getCell(0).getCellType() == Cell.CELL_TYPE_STRING) { info.setFinishTime(row.getCell(0).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(0))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(0).getNumericCellValue()); info.setFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setFinishTime(df.format(row.getCell(0).getNumericCellValue())); } } // if (row.getCell(1).getCellType() == Cell.CELL_TYPE_STRING) { info.setSendTime(row.getCell(1).getStringCellValue()); } else { if (DateUtil.isCellDateFormatted(row.getCell(1))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = DateUtil.getJavaDate(row.getCell(1).getNumericCellValue()); info.setSendTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setSendTime(df.format(row.getCell(1).getNumericCellValue())); } } //? row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierCode(row.getCell(2).getStringCellValue()); //?? row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); info.setCarrierName(row.getCell(3).getStringCellValue()); //??? row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(4).getStringCellValue()); //? row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(5).getStringCellValue()); //??? row.getCell(6).setCellType(Cell.CELL_TYPE_STRING); info.setUnit(row.getCell(6).getStringCellValue()); //? row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(7).getNumericCellValue()); imports.add(info); } */ } return batchAddData(planType, imports); } 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.servlet.InInterface.java
private ArrayList<Object> importDetailData(String detail, String beanPackage, String tableName, String fileName) throws Exception { //???/*from w w w . j a v a2 s. c o m*/ 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; 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(); 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(); //??object(, ?? ) JSONObject jSONObject = JSONObject.parseObject(detail); Iterator<String> keysIterator = jSONObject.keySet().iterator(); while (keysIterator.hasNext()) { String key = keysIterator.next(); for (Field field : fields) { // System.out.println("fieldName:" + field.getName() + ",key:" + key); if (field.getName().compareToIgnoreCase(key) == 0) { // System.out.println("fieldName:" + field.getName() + ",key:" + key + ",key value:" + jSONObject.getString(key)); field.setAccessible(true); if (!Units.strIsEmpty(jSONObject.getString(key))) { field.set(object, jSONObject.getString(key)); } } } } 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.getCellValue(cell))) { field.set(object, 0); } else { field.set(object, Integer.valueOf(Units.subZeroAndDot(Units.getCellValue(cell)))); } } } else if (field.getType() == float.class) { if (cell == null) { field.set(object, 0); } else { field.set(object, Float.valueOf(Units.getCellValue(cell))); } } else if (field.getType() == double.class) { if (cell == null) { field.set(object, false); } else { if (Units.strIsEmpty(Units.getCellValue(cell))) { field.set(object, 0); } else { field.set(object, Double.valueOf(Units.getCellValue(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.getCellValue(cell))); } } } else { if (cell == null) { field.set(object, null); } else { field.set(object, Units.getCellValue(cell)); } } } result.add(object); } return result; }
From source file:com.consensus.qa.framework.ExcelOperations.java
public void SetStatusForSimTypSheets(String sheet, FileNames fileName) throws IOException { int statusColumn; XSSFSheet workSheet = null;/*w w w . j a v a 2 s . c om*/ String filePath = FilePath(fileName); fileInput = new FileInputStream(new File(filePath)); workBook = GetWorkBook(fileName); for (int i = 0; i < workBook.getNumberOfSheets(); i++) { if (workBook.getSheetName(i).toLowerCase().contains(sheet.toLowerCase())) { workSheet = workBook.getSheetAt(i); break; } } try { for (int i = 0; i < workSheet.getPhysicalNumberOfRows(); i++) { Row statusRow = workSheet.getRow(i); for (int ct = 0; ct < statusRow.getPhysicalNumberOfCells(); ct++) { System.out.println(statusRow.getCell(ct)); } int numOfCells = statusRow.getPhysicalNumberOfCells(); if (workSheet.getRow(i).getCell(numOfCells - 1).getStringCellValue().toUpperCase() .contains(Status.STATUS.toString()) || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase() .contains(Status.UNUSED.toString()) || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase() .contains(Status.INUSE.toString()) || workSheet.getRow(0).getCell(numOfCells - 1).getStringCellValue().toUpperCase() .contains(Status.USED.toString())) { statusColumn = numOfCells - 1; } else { statusColumn = numOfCells; Cell statusCell = workSheet.getRow(i).createCell(statusColumn); statusCell.setCellValue(Status.UNUSED.toString()); continue; } System.out.println(statusRow.getCell(statusColumn)); try { if (statusRow.getCell(statusColumn) == null) { Cell cell = row.createCell(statusColumn); cell.setCellValue(Status.UNUSED.toString()); } } catch (Exception ex) { row.createCell(statusColumn).setCellValue(Status.UNUSED.toString()); } } } catch (Exception ex) { Log.error(ex.toString()); } finally { WriteAndCloseFile(filePath, fileInput, workBook); } }
From source file:com.consensus.qa.framework.ExcelOperations.java
public void SetCellValueToUsed(FileNames fileName, SheetName sheetName, String searchString) throws IOException { String filePath = FilePath(fileName); fileInput = new FileInputStream(new File(filePath)); workBook = new XSSFWorkbook(fileInput); String sheet = sheetName.toString(); int rowIndex = -1; try {/*from www.j a va 2 s .c om*/ if (sheetName.toString().contains("_")) { String[] Name = sheetName.toString().split("_"); if (Name[0].contains("IMEI")) { sheet = Name[1] + " " + Name[0]; } else sheet = Name[1]; } worksheet = workBook.getSheet(sheet); System.out.println(worksheet.getSheetName()); Iterator<Row> rowIterator = worksheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC && String .valueOf(Double.valueOf(cell.getNumericCellValue()).longValue()).equals(searchString)) { rowIndex = row.getRowNum(); break; } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().equals(searchString)) rowIndex = row.getRowNum(); break; } } } if (rowIndex != -1) { boolean flag = false; Row searchStringRow = worksheet.getRow(rowIndex); System.out.println(searchStringRow.getPhysicalNumberOfCells()); Iterator<Cell> statusChangeCell = searchStringRow.cellIterator(); while (statusChangeCell.hasNext()) { Cell statusCell = statusChangeCell.next(); if (statusCell.getStringCellValue().toLowerCase() .equals(Status.INUSE.toString().toLowerCase())) { statusCell.setCellValue(Status.USED.toString()); flag = true; } } if (flag == false) Log.error("FAILED TO FIND INUSE FIELD for " + searchString + " @FileName: " + filePath + ", Sheet: " + sheet); } } catch (Exception ex) { ex.printStackTrace(); } finally { WriteAndCloseFile(filePath, fileInput, workBook); } }
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static ArrayList<SalesForceLicenseData> readDataFromXLSXFile(String fileName) { File sfDataFile = new File(fileName); FileInputStream fileInputStream = null; ArrayList<SalesForceLicenseData> sfLicenseDataList = null; //read the file in to stream if (sfDataFile.exists()) { //System.out.println("Reading data from .xlsx file started."); logger.info("Reading data from .xlsx file : Started :: Timestamp >> " + new Date(System.currentTimeMillis())); try {// w w w . java2 s . c o m fileInputStream = new FileInputStream(sfDataFile); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); sfLicenseDataList = new ArrayList<SalesForceLicenseData>(); SalesForceLicenseData sfld = null; if (sheet != null) { int totalRows = sheet.getPhysicalNumberOfRows(); //System.out.println("Total no. of physical rows in file = "+ totalRows); logger.info("Total no. of physical rows in file = " + totalRows); Row headerRow = sheet.getRow(0); Cell headerCell; Cell dataCell; if (headerRow == null) { //System.out.println("No file header content found.") ; logger.info("No file header content found."); } else { int totalHeaderColumns = headerRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of header cells = "+ totalHeaderColumns); //logger.info("Total no. of header cells = "+ totalHeaderColumns); for (int rowCtr = 1; rowCtr < totalRows; rowCtr++) { //System.out.println("Row No. >> "+rowCtr); Row dataRow = sheet.getRow(rowCtr); if (dataRow != null) { int totalRowColumns = dataRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of current data row cells = "+ totalRowColumns); //logger.info("Total no. of current data row cells = "+ totalRowColumns); logger.info( "Row No. [" + rowCtr + "] :: Header Column Count = [" + totalHeaderColumns + "] :: Current Data Row Column Count = [" + totalRowColumns + "]"); //Discard dummy rows in spreadsheet if the count of current data row columns not equal to header columns if (totalHeaderColumns == totalRowColumns) { boolean isCustomerIdBlank = dataRow.getCell(0) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; boolean isOrgNodeIdBlank = dataRow.getCell(5) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; //System.out.println("isCustomerIdBlank >> "+isCustomerIdBlank+" :: isOrgNodeIdBlank >> "+isOrgNodeIdBlank); logger.info("Row No. [" + rowCtr + "] :: isCustomerIdBlank >> " + isCustomerIdBlank + " :: isOrgNodeIdBlank >> " + isOrgNodeIdBlank); //Condition to skip row for SF data object population if customer id or orgnode id is blank if (!isCustomerIdBlank && !isOrgNodeIdBlank) { sfld = new SalesForceLicenseData(); // For each row, loop through each column for (int colCtr = 0; colCtr < totalHeaderColumns; colCtr++) { //System.out.println("Column No. >> "+colCtr); headerCell = headerRow.getCell(colCtr); dataCell = dataRow.getCell(colCtr); if (dataCell != null) { //System.out.println("dataCell.getCellType() >> "+dataCell.getCellType()); switch (dataCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: //Do nothing System.out.println(dataCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.println(dataCell.getNumericCellValue()); populateSFDataNumericColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_STRING: //System.out.println(dataCell.getStringCellValue()); populateSFDataStrColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_BLANK: //System.out.println(" "); populateSFDataBlankColValue(sfld, dataCell, headerCell); break; default: System.out.println(dataCell); break; } } } sfLicenseDataList.add(sfld); } } } } } } } catch (FileNotFoundException e) { logger.error("FileNotFoundException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); // unexpected } catch (IOException e) { logger.error("IOException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); } finally { try { if (fileInputStream != null) fileInputStream.close(); } catch (IOException e) { logger.error("IOException : occurred while closing file input stream."); e.printStackTrace(); } } //System.out.println("Reading data from .xlsx file completed."); logger.info("Reading data from .xlsx file : Completed :: Timestamp >> " + new Date(System.currentTimeMillis())); } else { //System.out.println("File does not exists"); logger.error("File does not exists :: Filename >> [" + fileName + "]"); } return sfLicenseDataList; }
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static ArrayList<SalesForceLicenseData> readDataFromXLSFile(String fileName) { File sfDataFile = new File(fileName); FileInputStream fileInputStream = null; ArrayList<SalesForceLicenseData> sfLicenseDataList = null; SalesForceLicenseData sfld = null;//from w w w. j a v a 2s. c o m if (sfDataFile.exists()) { //System.out.println("Reading data from .xls file started."); logger.info("Reading data from .xls file : Started :: Timestamp >> " + new Date(System.currentTimeMillis())); try { //read the file in to stream fileInputStream = new FileInputStream(sfDataFile); //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); //Get first/desired sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sfLicenseDataList = new ArrayList<SalesForceLicenseData>(); if (sheet != null) { int totalRows = sheet.getPhysicalNumberOfRows(); //System.out.println("Total no. of physical rows in file = "+ totalRows); logger.info("Total no. of physical rows in file = " + totalRows); Row headerRow = sheet.getRow(0); Cell headerCell; Cell dataCell; if (headerRow == null) { //System.out.println("No file header content found.") ; logger.info("No file header content found."); } else { int totalHeaderColumns = headerRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of header cells = "+ totalHeaderColumns); logger.info("Total no. of header cells = " + totalHeaderColumns); for (int rowCtr = 1; rowCtr < totalRows; rowCtr++) { //System.out.println("Row No. >> "+rowCtr); Row dataRow = sheet.getRow(rowCtr); if (dataRow != null) { int totalRowColumns = dataRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of current data row cells = "+ totalRowColumns); //logger.info("Total no. of current data row cells = "+ totalRowColumns); logger.info( "Row No. [" + rowCtr + "] :: Header Column Count = [" + totalHeaderColumns + "] :: Current Data Row Column Count = [" + totalRowColumns + "]"); //Discard dummy rows in spreadsheet if the count of row columns not equal to header columns if (totalHeaderColumns == totalRowColumns) { boolean isCustomerIdBlank = dataRow.getCell(0) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; boolean isOrgNodeIdBlank = dataRow.getCell(5) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; //System.out.println("isCustomerIdBlank >> "+isCustomerIdBlank+" :: isOrgNodeIdBlank >> "+isOrgNodeIdBlank); logger.info("Row No. [" + rowCtr + "] :: isCustomerIdBlank >> " + isCustomerIdBlank + " :: isOrgNodeIdBlank >> " + isOrgNodeIdBlank); //Condition to skip row for SF data object population if customer id or orgnode id is blank if (!isCustomerIdBlank && !isOrgNodeIdBlank) { sfld = new SalesForceLicenseData(); // For each row, loop through each column for (int colCtr = 0; colCtr < totalHeaderColumns; colCtr++) { //System.out.println("Column No. >> "+colCtr); headerCell = headerRow.getCell(colCtr); dataCell = dataRow.getCell(colCtr); if (dataCell != null) { //System.out.println("dataCell.getCellType() >> "+dataCell.getCellType()); switch (dataCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: //Do nothing //System.out.println(dataCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.println(dataCell.getNumericCellValue()); populateSFDataNumericColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_STRING: //System.out.println(dataCell.getStringCellValue()); populateSFDataStrColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_BLANK: populateSFDataBlankColValue(sfld, dataCell, headerCell); break; default: System.out.println(dataCell); break; } } } sfLicenseDataList.add(sfld); } } } } } } } catch (FileNotFoundException e) { logger.error("FileNotFoundException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); // unexpected } catch (IOException e) { logger.error("IOException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); } finally { try { if (fileInputStream != null) fileInputStream.close(); } catch (IOException e) { logger.error("IOException : occurred while closing file input stream."); e.printStackTrace(); } } //System.out.println("Reading data from .xls file completed."); logger.info("Reading data from .xls file : Completed :: Timestamp >> " + new Date(System.currentTimeMillis())); } else { //System.out.println("File does not exists"); logger.error("File does not exists :: Filename >> [" + fileName + "]"); } return sfLicenseDataList; }
From source file:com.exilant.exility.core.XLSReader.java
License:Open Source License
/** * Purpose of this method to read rows from given Excel Sheet. * //from w ww. j a v a 2 s . co m * @param sheet * an Instance of .ss.usermodel.Sheet class from POI apache. * @return -1 if fail to read sheet else number of columns read successfully * from the sheet. * @throws ExilityException */ public int readASheet(Sheet sheet) throws ExilityException { int nonEmptyFirstRowIdx = 0; int lastRowIdx = 0; int nbrPhysicalRows = sheet.getPhysicalNumberOfRows(); String sheetName = sheet.getSheetName(); if (nbrPhysicalRows < 2) { Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS); return -1; } try { nonEmptyFirstRowIdx = sheet.getFirstRowNum(); lastRowIdx = sheet.getLastRowNum(); /* * For checking to valid header.First row must be header. */ Row headerRow = sheet.getRow(nonEmptyFirstRowIdx); int nbrCol = headerRow.getPhysicalNumberOfCells(); for (int colIdx = 0; colIdx < nbrCol; colIdx++) { Cell hCell = headerRow.getCell(colIdx); if (hCell == null || hCell.getCellType() == Cell.CELL_TYPE_BLANK) { Spit.out("Error--->Found blank column " + (colIdx + 1) + " in Sheet " + sheetName + XLSReader.INVALID_HEADER); this.columnsData.clear(); return -1; } String columnName = hCell.getStringCellValue(); this.setDataType(columnName, colIdx); } } catch (Exception e) { Spit.out(sheetName + XLSReader.INVALID_HEADER); Spit.out(e); return -1; } int nbrColumnsInARow = this.columnsData.size(); /* * Loop starts with second data row that is first row(header as column * name) excluded. */ Spit.out(sheetName + ":\n"); for (int rowIdx = (nonEmptyFirstRowIdx + 1); rowIdx <= lastRowIdx; rowIdx++) { Row row = sheet.getRow(rowIdx); if (row == null) { Spit.out(XLSReader.SKIP_BLANK_ROW + rowIdx); continue; } /** * readARow() will throws ExilityException if something goes wrong. */ this.readARow(row, nbrColumnsInARow); } return this.columnsData.size(); }
From source file:com.exilant.exility.core.XlxUtil.java
License:Open Source License
/*** * Get contents of a sheet into text rows and columns * /* w w w. j a v a2 s .c om*/ * @param sheet * @return */ private String[][] getRawData(Sheet sheet, boolean expectValueInFirstColumn) { // let us get a normalized rows/columns out of this sheet. int firstRowIdx = sheet.getFirstRowNum(); Row firstRow = sheet.getRow(firstRowIdx); int firstCellIdx = firstRow.getFirstCellNum(); int lastCellAt = firstRow.getLastCellNum(); int nbrCells = lastCellAt - firstCellIdx; int lastRow = sheet.getLastRowNum(); List<String[]> rawData = new ArrayList<String[]>(); for (int rowNbr = firstRowIdx; rowNbr <= lastRow; rowNbr++) { Row row = sheet.getRow(rowNbr); if (row == null || row.getPhysicalNumberOfCells() == 0) { Spit.out( "row at " + rowNbr + "is empty. while this is not an error, we certianly discourage this."); continue; } String[] rowData = this.getTextValues(row, firstCellIdx, nbrCells); if (rowData == null) { continue; } if (expectValueInFirstColumn) { String firstData = rowData[0]; if (firstData == null || firstData.length() == 0) { Spit.out("row at" + rowNbr + " has its first column empty, and hence the row is ignored"); continue; } } rawData.add(rowData); } if (rawData.size() > 0) { return rawData.toArray(new String[0][0]); } return null; }
From source file:com.fingence.slayer.service.impl.AssetLocalServiceImpl.java
License:Open Source License
public void loadPricingData(long userId, File excelFile, ServiceContext serviceContext, int type) { System.out.println("inside Load Pricing Data...."); if (Validator.isNull(excelFile)) return;//from w w w . java 2 s. co m InputStream is = null; try { is = new FileInputStream(excelFile); } catch (FileNotFoundException e) { //e.printStackTrace(); } if (Validator.isNull(is)) return; // Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(is); } catch (IOException e) { e.printStackTrace(); } if (Validator.isNull(workbook)) return; // Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Map<Integer, Long> columnNames = new HashMap<Integer, Long>(); int columnCount = 0; while (rowIterator.hasNext()) { Row row = rowIterator.next(); columnCount = row.getPhysicalNumberOfCells(); _log.debug("processing row ==> " + row.getRowNum()); System.out.println("processing row ==> " + row.getRowNum()); int rowNum = row.getRowNum(); if (rowNum == 0) continue; if (rowNum == 1) { for (int i = 0; i < columnCount; i++) { Cell cell = row.getCell(i); if (Validator.isNull(cell)) continue; String id_isin = CellUtil.getString(cell); Asset asset = null; try { asset = assetPersistence.fetchByIdISIN(id_isin); } catch (SystemException e) { e.printStackTrace(); } if (Validator.isNull(asset)) continue; columnNames.put(i, asset.getAssetId()); } continue; } if (rowNum > 1 && rowNum < 14) continue; System.out.println("going to process data..."); Iterator<Integer> itr = columnNames.keySet().iterator(); //for (int i=3; i < columnCount; i++){ while (itr.hasNext()) { int i = itr.next(); Date date = CellUtil.getDate(row.getCell(i)); if (Validator.isNull(date)) continue; long assetId = 0l; try { assetId = columnNames.get(i); } catch (Exception e) { _log.debug(e.getMessage() + ": There is an exception..."); continue; } double value = CellUtil.getDouble(row.getCell(++i)); History history = null; try { history = historyPersistence.fetchByAssetId_Date_Type(assetId, date, type); _log.debug("history record already present..."); } catch (SystemException e) { e.printStackTrace(); } if (Validator.isNull(history)) { long recId = 0l; try { recId = counterLocalService.increment(History.class.getName()); } catch (SystemException e) { e.printStackTrace(); } history = historyLocalService.createHistory(recId); history.setAssetId(assetId); history.setType(type); history.setValue(value); history.setLogDate(date); if (type == IConstants.HISTORY_TYPE_BOND_CASHFLOW) { double principal = CellUtil.getDouble(row.getCell(++i)); history.setPrincipal(principal); } try { history = historyLocalService.addHistory(history); } catch (SystemException e) { e.printStackTrace(); } } } } }