List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:SwiftSeleniumWeb.TransactionMapping.java
License:Open Source License
public static Reporter TransactionInputData(HSSFCell controllerTestCaseID, HSSFCell controllertransactionType, String filePath) throws Exception { Reporter report = new Reporter(); HashMap<String, Object> inputHashTable = new HashMap<String, Object>(); HSSFSheet workSheet = ExcelUtility.GetSheet( Automation.configHashMap.get("TRANSACTION_INPUT_FILEPATH").toString(), "Web_Transaction_Input_Files"); int rowCount = workSheet.getLastRowNum() + 1; for (int rowIndex = 1; rowIndex < rowCount && !MainController.pauseExecution; rowIndex++) { String transactionCode = WebHelper.getCellData("TransactionCode", workSheet, rowIndex, inputHashTable); String transactionType = WebHelper.getCellData("TransactionType", workSheet, rowIndex, inputHashTable); String directoryPath = WebHelper.getCellData("DirPath", workSheet, rowIndex, inputHashTable).toString();// String inputExcel = WebHelper.getCellData("InputSheet", workSheet, rowIndex, inputHashTable).toString(); if (transactionType.toString().equalsIgnoreCase(controllertransactionType.toString())) { if (transactionCode != null && directoryPath == null && controllertransactionType.toString().equalsIgnoreCase(transactionType.toString())) { report.strInputPath = ""; report.strOperationType = ""; report.strTransactioncode = transactionCode; WebDriver.DataInput("", controllerTestCaseID.toString(), transactionType, transactionCode, ""); break; }//w w w .j av a 2s. c o m if (!transactionType.toString().startsWith("Verify")) { operationType = "Input"; } if (transactionType.toString().startsWith("Verify") && (!directoryPath.toString().isEmpty()) && (!inputExcel.toString().isEmpty())) { operationType = "InputandVerfiy"; } else if (transactionType.toString().startsWith("Verify") && (directoryPath.toString().isEmpty()) && (inputExcel.toString().isEmpty())) { operationType = "Verify"; } if (controllertransactionType.toString().equalsIgnoreCase(transactionType.toString())) { if ((directoryPath == null || inputExcel == null) && operationType != "Verify") { MainController.pauseFun("Please Enter the directory or excelsheet name"); } else { String inputFilePath = null; if (operationType != "Verify") { inputFilePath = Automation.configHashMap.get("INPUT_DATA_FILEPATH").toString() + directoryPath.toString() + "\\" + inputExcel.toString(); } System.out.println(inputFilePath); report.strInputPath = inputFilePath; report.strOperationType = operationType; report.strTransactioncode = transactionCode; WebDriver.DataInput(inputFilePath, controllerTestCaseID.toString(), transactionType, transactionCode, operationType); break; } } } else if (!transactionType.toString().equalsIgnoreCase(controllertransactionType.toString()) && rowIndex == rowCount - 1) { MainController.pauseFun( "Transaction " + MainController.controllerTransactionType.toString() + " Not Found"); ExcelUtility.writeReport(SwiftSeleniumWeb.WebDriver.report); } } return null; }
From source file:SwiftSeleniumWeb.WebHelper.java
License:Open Source License
public static void GetCellInfo(String FilePath, HSSFRow rowValues, int valuesRowIndex, int valuesRowCount) throws IOException // newly Added two Variables for Action Loop { try {/*from www. ja v a2 s.com*/ frmDate = new Date(); isDynamicNumFound = true; List<WebElement> controlList = null; String ctrlValue = null; InputStream myXls = new FileInputStream(FilePath); HSSFWorkbook workBook = new HSSFWorkbook(myXls); format = workBook.createDataFormat(); HSSFSheet sheetStructure = workBook.getSheet("Structure"); int rowCount = sheetStructure.getLastRowNum() + 1; HSSFSheet headerValues = ExcelUtility.GetSheet(FilePath, "Values"); System.out.println(Automation.dtFormat.format(frmDate)); String fromDate = Automation.dtFormat.format(frmDate); SwiftSeleniumWeb.WebDriver.report.setFromDate(fromDate); structureHeader = getValueFromHashMap(sheetStructure); columnName = null; int dynamicIndexNumber;//Added for Action Loop String imageType, indexVal, controlName, executeFlag, action, logicalName, controltype, controlID, dynamicIndex, newDynamicIndex, rowNo, colNo;//newly Added for Action Loop //Setting of default reporting values before executing a transaction SwiftSeleniumWeb.WebDriver.report.setStrMessage(""); SwiftSeleniumWeb.WebDriver.report.setStrStatus("PASS"); for (int rowIndex = 1; rowIndex < rowCount && !MainController.pauseExecution; rowIndex++) { controlName = getCellData("ControlName", sheetStructure, rowIndex, structureHeader); executeFlag = getCellData("ExecuteFlag", sheetStructure, rowIndex, structureHeader); if (executeFlag.toString().equals("Y")) { WebElement webElement = null; imageType = getCellData("ImageType", sheetStructure, rowIndex, structureHeader); action = getCellData("Action", sheetStructure, rowIndex, structureHeader); logicalName = getCellData("LogicalName", sheetStructure, rowIndex, structureHeader); controltype = getCellData("ControlType", sheetStructure, rowIndex, structureHeader); controlID = getCellData("ControlID", sheetStructure, rowIndex, structureHeader); indexVal = getCellData("Index", sheetStructure, rowIndex, structureHeader); columnName = getCellData("ColumnName", sheetStructure, rowIndex, structureHeader); rowNo = getCellData("RowNo", sheetStructure, rowIndex, structureHeader); colNo = getCellData("ColumnNo", sheetStructure, rowIndex, structureHeader); dynamicIndex = getCellData("DynamicIndex", sheetStructure, rowIndex, structureHeader); if (action.equalsIgnoreCase("LOOP")) { loopRow = rowIndex + 1; } if ((valuesRowIndex != ExcelUtility.firstRow) && (dynamicIndex.length() > 0)) //valuesRowIndex { dynamicIndexNumber = Integer .parseInt(dynamicIndex.substring(dynamicIndex.length() - 1, dynamicIndex.length())); if (ExcelUtility.dynamicNum == 0) { ExcelUtility.dynamicNum = dynamicIndexNumber + 1; isDynamicNumFound = false; } else if (ExcelUtility.dynamicNum != 0 && isDynamicNumFound) { ExcelUtility.dynamicNum = ExcelUtility.dynamicNum + 1; isDynamicNumFound = false; } newDynamicIndex = dynamicIndex.replace(String.valueOf(dynamicIndexNumber), String.valueOf(ExcelUtility.dynamicNum)); controlName = controlName.replace(dynamicIndex, newDynamicIndex); } /**Stop the execution of the current test case unexpected alert**/ control = controltype.toString(); if (isAlertPresent(control) == true) { break; } if (!action.equalsIgnoreCase("LOOP") && !action.equalsIgnoreCase("END_LOOP")) { if (valuesHeader.isEmpty() == true) { valuesHeader = getValueFromHashMap(headerValues); } Object actualValue = null; if (logicalName != null) { actualValue = valuesHeader.get(logicalName.toString()); } //headerRow.getCell(colIndex); if (actualValue == null) { System.out.println("Null"); } else { ctrlValue = getCellData(logicalName, headerValues, valuesRowIndex, valuesHeader); testcaseID = rowValues .getCell(Integer.parseInt(valuesHeader.get("TestCaseID").toString())); if (testcaseID == null) { testCase = ""; } else { testCase = testcaseID.toString(); } transactionType = rowValues .getCell(Integer.parseInt(valuesHeader.get("TransactionType").toString())); } if ((action.equals("I") && !ctrlValue.isEmpty()) || (action.equals("V") && !ctrlValue.isEmpty()) || !action.equals("I")) { if (!controltype.startsWith("Sikuli")) { if (!action.equalsIgnoreCase("LOOP") && !controltype.equalsIgnoreCase("Wait") && !action.equalsIgnoreCase("END_LOOP") && !controltype.equalsIgnoreCase("Browser") && !controltype.equalsIgnoreCase("Window") && !controltype.equalsIgnoreCase("Alert") && !controltype.equalsIgnoreCase("URL") && !controltype.equalsIgnoreCase("WaitForJS") && !controltype.contains("Robot") && !controltype.equalsIgnoreCase("Calendar") && !controltype.equalsIgnoreCase("CalendarNew") && !controltype.equalsIgnoreCase("CalendarIPF") && !controltype.equalsIgnoreCase("CalendarEBP") && (!action.equalsIgnoreCase("Read") || ((action.equalsIgnoreCase("Read") && !controlName.isEmpty()))) && !controltype.equalsIgnoreCase("JSScript") && !controltype.equalsIgnoreCase("DB") && !controlID.equalsIgnoreCase("XML") && !controltype.startsWith("Process") && !controltype.startsWith("Destroy") && !controltype.startsWith("ReadSikuli") && !controltype.equalsIgnoreCase("WebService")) { if ((indexVal.equalsIgnoreCase("") || indexVal.equalsIgnoreCase("0")) && !controlID.equalsIgnoreCase("TagValue") && !controlID.equalsIgnoreCase("TagText")) { webElement = getElementByType(controlID, controlName, control, imageType, ctrlValue); } else { controlList = getElementsByType(controlID, controlName, control, imageType, ctrlValue); if (controlList != null && controlList.size() > 1) { webElement = GetControlByIndex(indexVal, controlList, controlID, controlName, control, ctrlValue); //, ISelenium selenium) } else { break; } } } } else { sikuliScreen = new Screen(); } } /*** Perform action on the identified control ***/ doAction(imageType, controltype, controlID, controlName, ctrlValue, logicalName, action, webElement, true, sheetStructure, headerValues, rowIndex, rowCount, rowNo, colNo); } if (action == "END_LOOP" && (valuesRowCount != valuesRowIndex)) { loopRow = 1; break; } } else { System.out.println("ExecuteFlag is N"); } } //Setting of reporting values after execution in case of no exception Date toDate = new Date(); SwiftSeleniumWeb.WebDriver.report.setFromDate(Automation.dtFormat.format(frmDate)); SwiftSeleniumWeb.WebDriver.report .setStrIteration(Automation.configHashMap.get("CYCLENUMBER").toString()); SwiftSeleniumWeb.WebDriver.report.setStrTestcaseId(MainController.controllerTestCaseID.toString()); SwiftSeleniumWeb.WebDriver.report.setStrGroupName(MainController.controllerGroupName.toString()); SwiftSeleniumWeb.WebDriver.report .setStrTrasactionType(MainController.controllerTransactionType.toString()); SwiftSeleniumWeb.WebDriver.report.setStrTestDescription(MainController.testDesciption); SwiftSeleniumWeb.WebDriver.report.setToDate(Automation.dtFormat.format(toDate)); //Setting status for field verification failures if (fieldVerFailCount > 0) { SwiftSeleniumWeb.WebDriver.report.setStrMessage("Check Detailed Results"); SwiftSeleniumWeb.WebDriver.report.setStrStatus("FAIL"); } } catch (Exception e) { MainController.pauseFun(e.getMessage()); } finally { structureHeader.clear(); valuesHeader.clear(); ExcelUtility.writeReport(SwiftSeleniumWeb.WebDriver.report); fieldVerFailCount = 0; } }
From source file:test.gov.nih.nci.cacoresdk.PropertyGenerator.java
License:BSD License
@SuppressWarnings("deprecation") public void generateConfigurationFiles(String confFileName, String outputDir) throws Exception { InputStream configFile = new FileInputStream(confFileName); HSSFWorkbook wb = new HSSFWorkbook(configFile); HSSFSheet sheet = wb.getSheetAt(0); int rowNum = sheet.getLastRowNum(); HSSFRow row = sheet.getRow(0);//w w w. j a va 2s.c o m int colNum = row.getLastCellNum(); ArrayList<String> names = new ArrayList<String>(); for (int i = 0; i < colNum; i++) { HSSFCell currentCell = row.getCell(i); if (currentCell == null) { names.add(""); } else { currentCell.setCellType(currentCell.CELL_TYPE_STRING); names.add(currentCell.toString()); } } /* * First Line includes names for all different configurations (Requirement 1) * Second column is the default value (Requirement 2) * From 3 - colNum are names of different configurations. (Requirement 3) */ int currentRowNum = 1; /* * Loop through xls file to generate properties files */ while (currentRowNum <= rowNum && countRow(sheet.getRow(currentRowNum++)) == 1) { ArrayList<String> propertiesNames = new ArrayList<String>(); String filename = getFirstString(sheet.getRow(currentRowNum - 1)); int rowCount = 0; int headerRow = currentRowNum - 1; while (currentRowNum <= rowNum && countRow(sheet.getRow(currentRowNum++)) != 1) { rowCount++; } ArrayList<Properties> properties = new ArrayList<Properties>(); properties.add(new Properties()); for (int i = 1; i < colNum; i++) { Properties prop = new Properties(); properties.add(prop); } currentRowNum = headerRow + 1; for (int i = currentRowNum; i < currentRowNum + rowCount; i++) { HSSFRow currentRow = sheet.getRow(i); if (currentRow == null) continue; if (currentRow.getCell(0) == null) continue; String propertyName = currentRow.getCell(0).getStringCellValue(); propertiesNames.add(propertyName); for (int j = 1; j < colNum; j++) { Properties currentP = properties.get(j); HSSFCell currentCell = currentRow.getCell(j); if (currentCell == null) { currentP.put(propertyName, ""); } else { currentCell.setCellType(currentCell.CELL_TYPE_STRING); currentP.put(propertyName, currentCell.toString()); } } } currentRowNum = currentRowNum + rowCount; /* * Merge default properties with other properties */ for (int j = 2; j < colNum; j++) { Properties defaultP = properties.get(1); Properties currentP = properties.get(j); Set<Object> propertyNames = defaultP.keySet(); for (Object propertyKey : propertyNames) { String key = (String) propertyKey; if (currentP.getProperty(key).equals("")) currentP.setProperty(key, defaultP.getProperty(key)); } } for (int j = 1; j < colNum; j++) { File directory = new File(outputDir + "/" + names.get(j)); directory.mkdirs(); File f = new File(directory, filename); Properties currentP = properties.get(j); saveProperties(propertiesNames, currentP, f); } } }
From source file:tournoi.ExcelToBDD.java
public boolean insertJoueur(String filePath) { boolean flag = true; db = new DBConnection(); con = db.getConnection();/*from ww w . j a va 2 s .c om*/ try { // ??excel FileInputStream fin = new FileInputStream(filePath); // HSSFWorkbook workbook = new HSSFWorkbook(fin);// HSSFSheet sheet = workbook.getSheetAt(0);// HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; HSSFCell cell4 = null; HSSFCell cell5 = null; HSSFCell cell6 = null; HSSFCell cell7 = null; int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_joueur = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); cell4 = row.getCell(3); int national_point = (int) cell4.getNumericCellValue(); cell5 = row.getCell(4); String categorie = cell5.getRichStringCellValue().toString(); cell6 = row.getCell(5); String federation = cell6.getRichStringCellValue().toString(); cell7 = row.getCell(6); String ligne = cell7.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO joueurs VALUES('" + id_joueur + "','" + nom + "','" + prenom + "','" + national_point + "','" + categorie + "','" + federation + "','" + ligne + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; }
From source file:tournoi.ExcelToBDD.java
public boolean insertArbitre(String filePath) { boolean flag = true; db = new DBConnection(); con = db.getConnection();/* w w w. j a v a 2s.com*/ try { // ??excel FileInputStream fin = new FileInputStream(filePath); // HSSFWorkbook workbook = new HSSFWorkbook(fin); //Get the workbook instance for XLS file HSSFSheet sheet = workbook.getSheetAt(1);//Get first sheet from the workbook System.out.println(workbook.getSheetName(1)); HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_arbitre = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); // cell4 = row.getCell(3); // int national_point = (int) cell4.getNumericCellValue(); // cell5 = row.getCell(4); // String categorie = cell5.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO arbitres VALUES('" + id_arbitre + "','" + nom + "','" + prenom + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; }
From source file:tournoi.ExcelToBDD.java
public boolean insertTable(String filePath) { boolean flag = true; db = new DBConnection(); con = db.getConnection();//from ww w.ja v a2 s . c o m try { // ??excel FileInputStream fin = new FileInputStream(filePath); // HSSFWorkbook workbook = new HSSFWorkbook(fin); //Get the workbook instance for XLS file HSSFSheet sheet = workbook.getSheetAt(2);//Get first sheet from the workbook System.out.println(workbook.getSheetName(2)); HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; //HSSFCell cell3 = null; int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_table = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); int id_salle = (int) cell2.getNumericCellValue(); // cell3 = row.getCell(2); // String prenom = cell3.getRichStringCellValue().toString(); // cell4 = row.getCell(3); // int national_point = (int) cell4.getNumericCellValue(); // cell5 = row.getCell(4); // String categorie = cell5.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO tables VALUES('" + id_table + "','" + id_salle + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; }
From source file:tournoi.ExcelToBDD.java
public boolean insertJoueur(String filePath, String table) { boolean flag = true; db = new DBConnection(); con = db.getConnection();// www.java 2 s . c o m try { // ??excel FileInputStream fin = new FileInputStream(filePath); HSSFWorkbook workbook = new HSSFWorkbook(fin);// HSSFSheet sheet = workbook.getSheetAt(0);// HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; HSSFCell cell4 = null; HSSFCell cell5 = null; HSSFCell cell6 = null; HSSFCell cell7 = null; int totalCol = sheet.getDefaultColumnWidth();// obtenir le nbr de column for (int i = 1; i <= totalCol; i++) { } int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_joueur = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); cell4 = row.getCell(3); int national_point = (int) cell4.getNumericCellValue(); cell5 = row.getCell(4); String categorie = cell5.getRichStringCellValue().toString(); cell6 = row.getCell(5); String federation = cell6.getRichStringCellValue().toString(); cell7 = row.getCell(6); String ligne = cell7.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO joueurs VALUES('" + id_joueur + "','" + nom + "','" + prenom + "','" + national_point + "','" + categorie + "','" + federation + "','" + ligne + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; }
From source file:ubic.BAMSandAllen.NeuroNamesMappingLoader.java
License:Apache License
public static Set<NomenClatureEntry> getEntries(HSSFSheet NNsheet) { Set<NomenClatureEntry> result = new HashSet<NomenClatureEntry>(); NomenClatureEntry e;/*from w w w. j a v a2 s. c o m*/ for (int i = 1; i < NNsheet.getLastRowNum() + 1; i++) { e = new NomenClatureEntry(); e.acro = ExcelUtil.getValue(NNsheet, i, NNACROCOL); e.name = ExcelUtil.getValue(NNsheet, i, NNFULLCOL); e.NNID = ExcelUtil.getValue(NNsheet, i, NNIDCOL); e.NNName = ExcelUtil.getValue(NNsheet, i, NNDEFAULTNAME); result.add(e); } return result; }
From source file:ubic.basecode.io.excel.ExcelUtil.java
License:Apache License
/** * @param sheet/*from ww w. ja v a 2 s. c o m*/ * @param column the index of the column to get * @param header if there is a header row to be skipped * @param clean lower case * @param f * @return */ public static List<String> grabColumnValuesList(HSSFSheet sheet, int column, boolean header, boolean clean, SpreadSheetFilter f) { List<String> result = new LinkedList<String>(); int rows = sheet.getLastRowNum() + 1; for (int i = 0; i < rows; i++) { if (header && i == 0) continue; String term = ExcelUtil.getValue(sheet, i, column); if (term == null) continue; if (f.accept(sheet, i)) { term = term.trim(); if (clean) term = term.toLowerCase(); result.add(term); } } return result; }
From source file:ucar.unidata.data.DataUtil.java
License:Open Source License
/** * Convert excel to csv/*from w ww . j av a2 s . c o m*/ * * @param filename excel file * @param skipToFirstNumeric if true, skip to first numeric * @param sdf If non null then use this to format any date cells * * @return csv * * @throws Exception On badness */ public static String xlsToCsv(String filename, boolean skipToFirstNumeric, SimpleDateFormat sdf) throws Exception { StringBuffer sb = new StringBuffer(); InputStream myxls = IOUtil.getInputStream(filename, DataUtil.class); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); // first sheet boolean seenNumber = false; for (int rowIdx = sheet.getFirstRowNum(); rowIdx <= sheet.getLastRowNum(); rowIdx++) { HSSFRow row = sheet.getRow(rowIdx); if (row == null) { sb.append("\n"); continue; } boolean rowOk = true; for (short colIdx = row.getFirstCellNum(); colIdx < row.getPhysicalNumberOfCells(); colIdx++) { HSSFCell cell = row.getCell(colIdx); if (cell == null) { continue; } if (skipToFirstNumeric && !seenNumber) { if (cell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) { rowOk = false; break; } seenNumber = true; } String cellValue = null; if ((sdf != null) && (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)) { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); cellValue = sdf.format(date); } } if (cellValue == null) { cellValue = cell.toString(); } if (colIdx > 0) { sb.append(","); } sb.append(cellValue); /* if(false && comment!=null) { String author = comment.getAuthor(); String str = comment.getString().getString(); str = StringUtil.replace(str, author+":",""); str = StringUtil.replace(str, "\n",""); sb.append("("+str+")"); }*/ } if (rowOk) { sb.append("\n"); } } return sb.toString(); }