List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:com.citrix.g2w.webdriver.util.ReadExcelReport.java
License:Open Source License
/** * Method to read the file./* w w w.ja v a 2s . c o m*/ * * @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; ////from w w w.ja v a2 s . co 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.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static int getFirstColumn(HSSFSheet sheet) throws Exception { int minimum = 0; boolean isFirstTime = true; for (int cnt = sheet.getFirstRowNum(); cnt < sheet.getPhysicalNumberOfRows(); ++cnt) { //get the first row HSSFRow row = sheet.getRow(cnt); boolean found = false; //now find the first column that isn't null or empty short icnt = 0; while ((icnt < row.getLastCellNum()) && (found != true)) { try { HSSFCell cell = row.getCell(icnt); //System.out.println(icnt+":"+cell.getCellType()); if (cell != null) { //System.out.println(cell.getCellType()); if (icnt < minimum) { minimum = icnt;//from w ww. ja v a2 s . co m } if (isFirstTime) { minimum = icnt; isFirstTime = false; } found = true; } } catch (NullPointerException e) { // TODO Auto-generated catch block //System.out.println(icnt+" is null"); e.printStackTrace(); } //System.out.println("minimum for row: "+cnt+ " is "+minimum); ++icnt; } } return (minimum); }
From source file:com.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static int getNumberOfCells(HSSFSheet sheet) throws Exception { int maxNumberOfCells = 0; for (int cnt = sheet.getFirstRowNum(); cnt < sheet.getPhysicalNumberOfRows(); ++cnt) { //get the first row HSSFRow row = sheet.getRow(cnt); if (row.getPhysicalNumberOfCells() > maxNumberOfCells) maxNumberOfCells = row.getPhysicalNumberOfCells(); }// w w w.jav a2 s . c o m return (maxNumberOfCells); }
From source file:com.claim.controller.ThaiMedicineController.java
public int mergeRowLimit_(HSSFSheet sheet, int curRow, int count_limit, int[] cols, int autoNum) { //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) for (int col : cols) { sheet.addMergedRegion(new CellRangeAddress(curRow - count_limit, curRow - 1, col, col)); }//from w ww.j a v a 2 s. c om //System.out.println("write Auto Run ::==" + (curRow - count_limit)); row = sheet.getRow(curRow - count_limit); row.setHeight((short) 340); cell = row.createCell(0); cell.setCellValue(autoNum); cell.setCellStyle(csNum4); return 1; }
From source file:com.claudesoft.service.CarTbDetailed.java
@Override void makeTableHead(HSSFSheet sheet) { HSSFRow row = null;/* ww w. ja v a 2s.c o m*/ HSSFCell cell = null; row = sheet.createRow(2); for (int j = 0; j < this.columnCount; j++) { // if (j == 0) { sheet.setColumnWidth(j, 10 * 256); } else { sheet.setColumnWidth(j, 15 * 256); } cell = row.createCell(j); cell.setCellStyle(makeTableHeadStyle()); } cell = sheet.getRow(2).getCell(0); cell.setCellValue(""); cell = sheet.getRow(2).getCell(1); cell.setCellValue(""); cell = sheet.getRow(2).getCell(2); cell.setCellValue(""); cell = sheet.getRow(2).getCell(3); cell.setCellValue(""); cell = sheet.getRow(2).getCell(4); cell.setCellValue(""); cell = sheet.getRow(2).getCell(5); cell.setCellValue(""); cell = sheet.getRow(2).getCell(6); cell.setCellValue(""); cell = sheet.getRow(2).getCell(7); cell.setCellValue(""); cell = sheet.getRow(2).getCell(8); cell.setCellValue(""); cell = sheet.getRow(2).getCell(9); cell.setCellValue(""); cell = sheet.getRow(2).getCell(10); cell.setCellValue(""); cell = sheet.getRow(2).getCell(11); cell.setCellValue(""); }
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;//from w ww. j a v a 2 s.c o m try { 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.cms.utils.ExcelReader.java
public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); }//w w w . j a v a 2s . c o m } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:com.cn.controller.BaseInfoController.java
/** * ?// ww w . j a v a2 s . 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.MaterialBaseInfoController.java
/** * ?//from w w w . ja v a2 s . c om * * @param fileName * @param carrierName * @return */ public int importData(String fileName, String carrierName) { InputStream inputStream = null; try { File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<MaterialBaseInfo> 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) { MaterialBaseInfo info = new MaterialBaseInfo(); 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(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); info.setProductJianHao(row.getCell(3).getStringCellValue()); row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarNum((int) row.getCell(4).getNumericCellValue()); 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 tbMaterialBaseInfo(materialBaseId, pinMing, jianHao, carModel, carNum, carrierName, productJianHao)" + "values(MATERIALBASEID.NEXTVAL, ?, ?, ?, ?, ?, ?)"); for (MaterialBaseInfo infoImport : imports) { statement.setString(1, infoImport.getPinMing()); statement.setString(2, infoImport.getJianHao()); statement.setString(3, infoImport.getCarModel()); statement.setInt(4, infoImport.getCarNum()); statement.setString(5, carrierName); statement.setString(6, infoImport.getProductJianHao()); 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; }