private void ReadDataToArray(Workbook dataWb) { Connection con = DbConnection.getConnection(); String DataSheetInsertQuery = "insert into DataSheet_tbl values(?,?,?);"; for (int i = 1; i < dataWb.getSheet("Data").getPhysicalNumberOfRows(); i++) { if (!dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue().equals(null)) { try { preparedStatement = con.prepareStatement(DataSheetInsertQuery); preparedStatement.setString( 1, String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(1).getNumericCellValue())); preparedStatement.setDate( 2, new Date( (dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue()).getTime())); preparedStatement.setString( 3, String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(2).getNumericCellValue())); preparedStatement.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } DbConnection.closePreparedStatement(preparedStatement); DbConnection.closeConnection(con); System.out.println("All the data in the sheet is analysed and updated to memory"); }
/** * @param data * @param fileType */ private void buildWorkBook(byte[] data, FileType fileType) { ByteArrayInputStream bs = new ByteArrayInputStream(data); try { if (fileType == FileType.XLSX) workbook = new XSSFWorkbook(bs); else if (fileType == FileType.XLS) { workbook = new HSSFWorkbook(bs); } sheetEvent = workbook.getSheet("Event Details"); sheetParticipants = workbook.getSheet("Participants Details"); } catch (IOException e) { sLogger.error("Issues with files", e); } }
public static void main(String args[]) { try { String fileName = "C:/Users/NITHA/Desktop/Task.xlsx"; // Create the input stream from the xlsx/xls file FileInputStream fis = new FileInputStream(fileName); // Create Workbook instance for xlsx/xls file input stream Workbook workbook = null; if (fileName.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fileName.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(fis); } // Get the nth sheet from the workbook Sheet sheet = workbook.getSheet(HEADER_VALUES); retrieveSheetInformation(sheet); // close file input stream fis.close(); } catch (IOException e) { e.printStackTrace(); } }
/** * get the specified excel sheet and put its value string to list. * * @param sheetName excel sheet name * @param ignoreRows first several rows not to read. * @param ignoreCols first several cols not to read. * @param readRows specified row count to read. * @param readColumns specified column count to read. * @throws RuntimeException */ public List<String> excelToList( String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) { FileInputStream fso = null; List<String> paraList = new ArrayList<String>(); try { fso = new FileInputStream(fileName); Workbook workBook = getWorkBook(fso, true); xlSheet = workBook.getSheet(sheetName); if (xlSheet == null) { LOG.error("sheet [" + sheetName + "] does not exist!"); throw new RuntimeException("sheet [" + sheetName + "] does not exist!"); } readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows; for (int i = ignoreRows; i < ignoreRows + readRows; i++) { xlRow = xlSheet.getRow(i); readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns; if (xlRow != null) { for (int j = ignoreCols; j < ignoreCols + readColumns; j++) { xlCell = xlRow.getCell(j); if (xlCell == null) { paraList.add(""); } else { paraList.add(xlCell.toString()); } } } } fso.close(); } catch (Exception e) { LOG.error(e); throw new RuntimeException("read excel failed:" + e.getMessage()); } return paraList; }
public void loadBeachLocations() { try { FileInputStream file = new FileInputStream(new File("FISHERFOLK TEAM DATA SUMMARY.xls")); workbook = new HSSFWorkbook(file); Sheet sheet = workbook.getSheet("BEACH SUMMARY"); Iterator<Row> rowIterator = sheet.iterator(); rowIterator.next(); // Skip the header row. while (rowIterator.hasNext()) { Row row = rowIterator.next(); String code = row.getCell(1).getStringCellValue().trim(); System.out.println(code); if (!code.isEmpty()) { String beachname = row.getCell(1).getStringCellValue().trim(); String county = row.getCell(2).getStringCellValue().trim(); String description = row.getCell(1).getStringCellValue().trim() + "Beach"; System.out.println(beachname + ":" + county + ":" + description); Beach beach = new Beach(beachname, description, county); beach.saveBeach(); } } JOptionPane.showMessageDialog(null, "Done Adding Beaches Locations...."); } catch (IOException ex) { ex.printStackTrace(); Logger.getLogger(Reporting.class.getName()).log(Level.SEVERE, null, ex); } }
public void parse() throws FileNotFoundException, IOException, InvalidFormatException { InputStream inp; inp = new FileInputStream(fileName); Workbook wb = WorkbookFactory.create(inp); Row row; Sheet sheet = wb.getSheet(sheetName); int startingRow = 0; boolean breakNow = false; for (int i = startingRow; i <= sheet.getLastRowNum(); i++) { if (breakNow) { break; } row = sheet.getRow(i); if (row == null) { break; } for (int j = 0; j < row.getLastCellNum(); j++) { if (row.getCell(j).getStringCellValue().isEmpty() || row.getCell(j).getStringCellValue() == null) { breakNow = true; break; } // category.setCategoryName(row.getCell(j).getStringCellValue()); } } inp.close(); }
private Transaction parseAsTransaction(Row row) { String savingsAccountIdCheck = readAsInt(SAVINGS_ACCOUNT_NO_COL, row); if (!savingsAccountIdCheck.equals("")) savingsAccountId = savingsAccountIdCheck; String transactionType = readAsString(TRANSACTION_TYPE_COL, row); String amount = readAsDouble(AMOUNT_COL, row).toString(); String transactionDate = readAsDate(TRANSACTION_DATE_COL, row); String paymentType = readAsString(PAYMENT_TYPE_COL, row); String paymentTypeId = getIdByName(workbook.getSheet("Extras"), paymentType).toString(); String accountNumber = readAsLong(ACCOUNT_NO_COL, row); String checkNumber = readAsLong(CHECK_NO_COL, row); String routingCode = readAsLong(ROUTING_CODE_COL, row); String receiptNumber = readAsLong(RECEIPT_NO_COL, row); String bankNumber = readAsLong(BANK_NO_COL, row); return new Transaction( amount, transactionDate, paymentTypeId, accountNumber, checkNumber, routingCode, receiptNumber, bankNumber, Integer.parseInt(savingsAccountId), transactionType, row.getRowNum()); }
private void initActiveSheet() { if (dataFile == null) return; if (sheetIndex > -1) { dataSheet = dataFile.getSheetAt(sheetIndex); if (dataSheet == null) { throw new IndexOutOfBoundsException( "Sheet with index " + sheetIndex + " does not exist in file: " + inputFile.getFullPath()); } } else if (sheetName != null) { dataSheet = dataFile.getSheet(sheetName); if (dataSheet == null) { throw new IllegalArgumentException( "Sheet with name " + sheetName + " does not exist in file: " + inputFile.getFullPath()); } } else { int index = dataFile.getActiveSheetIndex(); dataSheet = dataFile.getSheetAt(index); } headerColumns = null; int numMergedRegions = dataSheet.getNumMergedRegions(); mergedRegions = new ArrayList<>(numMergedRegions); for (int i = 0; i < numMergedRegions; i++) { mergedRegions.add(dataSheet.getMergedRegion(i)); } }
/** * write excel sheet, specified value to specified cell. * * @param sheetName excel sheet name * @param row row index which to be changed * @param col column index which to be changed * @param value value to be put into cell * @throws RuntimeException */ public void setExcelValue(String sheetName, int row, int col, String value) { Workbook workBook = null; try { if (new File(fileName).exists()) { workBook = getWorkBook(new FileInputStream(fileName), false); } else { workBook = getWorkBook(null, false); } xlSheet = workBook.getSheet(sheetName); if (xlSheet == null) { xlSheet = workBook.createSheet(sheetName); } xlRow = xlSheet.getRow(row - 1); if (xlRow == null) { xlRow = xlSheet.createRow((short) row - 1); } xlCell = xlRow.getCell(col - 1); if (xlCell == null) { xlCell = xlRow.createCell(col - 1); } xlCell.setCellType(1); // set cell type as string xlCell.setCellValue(value); FileOutputStream fileOut = new FileOutputStream(fileName); workBook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception e) { LOG.error(e); throw new RuntimeException("set excel value failed:" + e.getMessage()); } }
private void generateEnforcementsRegion(Workbook wb, StringBuilder sb) { // Get the Enforcements Sheet Sheet sheet = wb.getSheet("Enforcements"); Iterator<Row> rowIt = sheet.rowIterator(); // Ignore the Header row rowIt.next(); while (rowIt.hasNext()) { Row row = rowIt.next(); Cell cellId = row.getCell(0); if (cellId != null) { int id = (int) cellId.getNumericCellValue(); Cell cellName = row.getCell(1); String name = cellName.getStringCellValue().replaceAll("\"", "'"); Cell cellDescription = row.getCell(2); String description = cellDescription.getStringCellValue().replaceAll("\"", "'"); Cell cellType = row.getCell(3); String type = cellType.getStringCellValue(); sb.append("Enforcement En" + id + " {"); sb.append("\n"); sb.append("\tName \"" + name + "\""); sb.append("\n"); sb.append("\tDescription \"" + description + "\""); sb.append("\n"); sb.append("\tType " + type); sb.append("\n}"); sb.append("\n\n"); } else break; } }
public void rowCount(String sheetName) throws EncryptedDocumentException, InvalidFormatException, IOException { FileInputStream fis = new FileInputStream(filepath); Workbook wb = WorkbookFactory.create(fis); Sheet sh = wb.getSheet(sheetName); int rowcont = sh.getLastRowNum() + 1; System.out.println(rowcont); }
private void generateServicesRegion(Workbook wb, StringBuilder sb) { // Get the Services Sheet Sheet sheet = wb.getSheet("Services"); Iterator<Row> rowIt = sheet.rowIterator(); // Ignore the Header row rowIt.next(); while (rowIt.hasNext()) { Row row = rowIt.next(); Cell cellId = row.getCell(0); if (cellId != null) { int id = (int) cellId.getNumericCellValue(); Cell cellName = row.getCell(1); String name = cellName.getStringCellValue().replaceAll("\"", "'"); Cell cellDescription = row.getCell(2); String description = cellDescription.getStringCellValue().replaceAll("\"", "'"); Cell cellPrivateData = row.getCell(3); Cell cellPartOf = row.getCell(4); sb.append("Service S" + id + " {"); sb.append("\n"); sb.append("\tName \"" + name + "\""); sb.append("\n"); sb.append("\tDescription \"" + description + "\""); sb.append("\n"); if (cellPrivateData.getCellType() == Cell.CELL_TYPE_NUMERIC) { int privateData = (int) cellPrivateData.getNumericCellValue(); sb.append("\tRefersTo PrivateData PD" + privateData); sb.append("\n"); } else if (cellPrivateData.getCellType() == Cell.CELL_TYPE_STRING) { String privateData = cellPrivateData.getStringCellValue(); if (privateData.equals("All")) { sb.append("\tRefersTo PrivateData All"); } else { sb.append("\tRefersTo PrivateData "); for (String s : privateData.split(", ")) { sb.append("PD" + s + ","); } // Delete last ',' sb.deleteCharAt(sb.length() - 1); } sb.append("\n"); } if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) { int partOf = (int) cellPartOf.getNumericCellValue(); sb.append("\tService_Part S" + partOf); sb.append("\n"); } sb.append("}"); sb.append("\n\n"); } else break; } }
public static Sheet readExcelFile(String filePath, String sheetName) throws Exception { File f = new File(filePath); FileInputStream in = new FileInputStream(f); Workbook wrkBook = (Workbook) WorkbookFactory.create(in); Sheet merchantsheet = wrkBook.getSheet(sheetName); return merchantsheet; }
private void generateRecipientsRegion(Workbook wb, StringBuilder sb) { // Get the Recipients Sheet Sheet sheet = wb.getSheet("Recipients"); Iterator<Row> rowIt = sheet.rowIterator(); // Ignore the Header row rowIt.next(); while (rowIt.hasNext()) { Row row = rowIt.next(); Cell cellId = row.getCell(0); if (cellId != null) { if (cellId.getCellType() == Cell.CELL_TYPE_NUMERIC) { int id = (int) cellId.getNumericCellValue(); Cell cellDescription = row.getCell(1); String description = cellDescription.getStringCellValue().replaceAll("\"", "'"); Cell cellScope = row.getCell(2); String scope = cellScope.getStringCellValue(); if (scope.contains("/")) { scope = "Internal/External"; } else { scope = scope.substring(0, 1).toUpperCase() + scope.substring(1); } Cell cellType = row.getCell(3); String type = cellType.getStringCellValue(); if (type.contains("/")) { type = "Individual/Organization"; } else { type = type.substring(0, 1).toUpperCase() + type.substring(1); } Cell cellPartOf = row.getCell(4); sb.append("Recipient R" + id + " {"); sb.append("\n"); sb.append("\tName \"" + description + "\""); sb.append("\n"); sb.append("\tDescription \"" + description + "\""); sb.append("\n"); if (cellPartOf.getCellType() == Cell.CELL_TYPE_NUMERIC) { int partOf = (int) cellPartOf.getNumericCellValue(); sb.append("\tRecipient_Part R" + partOf); sb.append("\n"); } sb.append("\tScope " + scope); sb.append("\n"); sb.append("\tType " + type); sb.append("\n}"); sb.append("\n\n"); } } else break; } }
public String getTestData(String sheetName, int rowNum, int colNum) throws EncryptedDocumentException, InvalidFormatException, IOException { FileInputStream fis = new FileInputStream(filepath); Workbook wb = WorkbookFactory.create(fis); Sheet sh = wb.getSheet(sheetName); Row row = sh.getRow(rowNum); String data = row.getCell(colNum).getStringCellValue(); return data; }
private void openWorkbook(Workbook workbook) { ObservableList<String> sheets = new ObservableListWrapper<>(new ArrayList<>()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { worksheetSelector.getItems().add(workbook.getSheetName(i)); } sheetReader = (String sheetName) -> readSheet(workbook.getSheet(sheetName)); Platform.runLater(() -> worksheetSelector.getSelectionModel().selectFirst()); }
public Integer getColumnCount() throws FileNotFoundException, IOException, InvalidFormatException { InputStream inp; inp = new FileInputStream(fileName); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheet(sheetName); Row row = sheet.getRow(0); int numColumns = row.getLastCellNum(); inp.close(); return numColumns; }
/** * Opens a excel sheet * * @param fileName name of the file where you want data * @param sheetName name of the sheet in the excel file */ public ParsingExcel(String fileName, String sheetName) { System.out.println(fileName); try { File file = new File(fileName); System.out.println(file.getAbsolutePath()); if (fileName.indexOf("xlsx") < 0) { // for .xls format wb = new HSSFWorkbook(new FileInputStream(file)); ws = wb.getSheet(sheetName); } else { // for .xlsx format wb = new XSSFWorkbook(fileName); ws = (XSSFSheet) wb.getSheet(sheetName); } } catch (IOException io) { throw new Error( "Invalid file '" + fileName + "' or incorrect sheet '" + sheetName + "', enter a valid one"); } }
/** * put dataList to excel sheets. * * @param sheetName excel sheet name. * @param dataList data list to be parsed and put into excel sheets. * @param rowNum row count of the sheet to be modified. * @param ignoreRows rows to skip when put value. * @param ignoreColumns columns to skip when put value. * @throws RuntimeException * @throws IllegalArgumentException */ public void putListToExcelWithFullIgnore( String sheetName, List<String> dataList, int rowNum, int ignoreRows, int ignoreColumns) { if (dataList.size() % rowNum != 0) { LOG.error("dataList has wrong element count for excel!"); throw new IllegalArgumentException("dataList has wrong element count for excel!"); } String value = null; int index = 0; final int colCount = dataList.size() / rowNum; Workbook workBook = null; try { if (new File(fileName).exists()) { workBook = getWorkBook(new FileInputStream(fileName), false); } else { workBook = getWorkBook(null, false); } xlSheet = workBook.getSheet(sheetName); if (xlSheet == null) { xlSheet = workBook.createSheet(sheetName); } for (int j = ignoreRows; j < ignoreRows + rowNum; j++) { xlRow = xlSheet.getRow(j); if (xlRow == null) { xlRow = xlSheet.createRow(j); } for (int i = ignoreColumns; i < ignoreColumns + colCount; i++) { value = dataList.get(index); xlCell = xlRow.getCell(i); if (xlCell == null) { xlCell = xlRow.createCell(i); } xlCell.setCellType(1); if (value != null) { xlCell.setCellValue(value); } index++; } } FileOutputStream fileOut = new FileOutputStream(fileName); workBook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception e) { LOG.error(e); throw new RuntimeException("set excel value failed:" + e.getMessage()); } }
/** * read excel Xls and add the result into arraylist. * * @param sheetName excel sheet name * @throws RuntimeException */ public List<Map<String, String>> excelToList(String sheetName) { Row firstxlRow = null; FileInputStream fso = null; List<Map<String, String>> paraList = new ArrayList<Map<String, String>>(); try { fso = new FileInputStream(fileName); Workbook workBook = getWorkBook(fso, true); xlSheet = workBook.getSheet(sheetName); if (xlSheet == null) { LOG.error("sheet [" + sheetName + "] does not exist!"); return null; } firstxlRow = xlSheet.getRow(xlSheet.getFirstRowNum()); int firstCell = firstxlRow.getFirstCellNum(); int lastCell = firstxlRow.getLastCellNum(); List<String> keyList = new ArrayList<String>(); for (int cNum = firstCell; cNum < lastCell; cNum++) { if (firstxlRow.getCell(cNum).toString() == null) { break; } keyList.add(firstxlRow.getCell(cNum).toString()); } for (int i = xlSheet.getFirstRowNum() + 1; i < xlSheet.getPhysicalNumberOfRows(); i++) { xlRow = xlSheet.getRow(i); List<String> valueList = new ArrayList<String>(); if (xlRow == null) { break; } for (int j = firstCell; j < lastCell; j++) { xlCell = xlRow.getCell(j); if (xlCell == null) { valueList.add(null); continue; } else { valueList.add(xlCell.toString()); } } paraList.add(creatMap(keyList, valueList)); } fso.close(); } catch (Exception e) { LOG.error(e); throw new RuntimeException("read excel failed:" + e.getMessage()); } return paraList; }
public String setTestData(String sheetName, int rowNum, int colNum, String data) throws EncryptedDocumentException, InvalidFormatException, IOException { FileInputStream fis = new FileInputStream(filepath); Workbook wb = WorkbookFactory.create(fis); Sheet sh = wb.getSheet(sheetName); Row row = sh.getRow(rowNum); Cell cel = row.createCell(colNum); cel.setCellType(Cell.CELL_TYPE_STRING); cel.setCellValue(data); FileOutputStream fos = new FileOutputStream(filepath); cel.setCellValue(data); wb.write(fos); wb.close(); return data; }
@Test public void test() throws Throwable { Workbook wb = WorkbookFactory.create(new File(Resources.getResource("excel/employees.xlsx").getFile())); Sheet sheet = wb.getSheet("employees"); Iterator<Row> itr = sheet.iterator(); itr.next(); List<Employee> employees = new LinkedList<>(); while (itr.hasNext()) { employees.add(populate(itr.next())); } IOUtils.closeQuietly(wb); Assert.assertEquals(2, employees.size()); TestUtils.print(employees); }
public int getRowCount(String sheetName) { int retVal = 0; try { FileInputStream fis = new FileInputStream(path1); Workbook wb = WorkbookFactory.create(fis); Sheet s = wb.getSheet(sheetName); retVal = s.getLastRowNum(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return retVal; }
@Override public Result parse() { Result result = new Result(); Sheet savingsTransactionSheet = workbook.getSheet("RecurringDepositTransaction"); Integer noOfEntries = getNumberOfRows(savingsTransactionSheet, AMOUNT_COL); for (int rowIndex = 1; rowIndex < noOfEntries; rowIndex++) { Row row; try { row = savingsTransactionSheet.getRow(rowIndex); if (isNotImported(row, STATUS_COL)) savingsTransactions.add(parseAsTransaction(row)); } catch (Exception e) { logger.error("row = " + rowIndex, e); result.addError("Row = " + rowIndex + " , " + e.getMessage()); } } return result; }
@Override public void run(TaskMonitor tm) throws Exception { tm.setTitle("Loading network from table"); tm.setProgress(0.0); tm.setStatusMessage("Loading network..."); Workbook workbook = null; // Load Spreadsheet data for preview. if (fileType != null && (fileType.equalsIgnoreCase(SupportedFileType.EXCEL.getExtension()) || fileType.equalsIgnoreCase(SupportedFileType.OOXML.getExtension())) && workbook == null) { try { workbook = WorkbookFactory.create(is); } catch (InvalidFormatException e) { throw new IllegalArgumentException( "Could not read Excel file. Maybe the file is broken?", e); } finally { if (is != null) is.close(); } } try { if (this.fileType.equalsIgnoreCase(SupportedFileType.EXCEL.getExtension()) || this.fileType.equalsIgnoreCase(SupportedFileType.OOXML.getExtension())) { String networkName = ntmp.getName(); if (networkName == null) networkName = workbook.getSheetName(0); final Sheet sheet = workbook.getSheet(networkName); reader = new ExcelNetworkSheetReader( networkName, sheet, ntmp, nMap, rootNetwork, serviceRegistrar); } else { reader = new NetworkTableReader(inputName, is, ntmp, nMap, rootNetwork, serviceRegistrar); } } catch (Exception ioe) { tm.showMessage(TaskMonitor.Level.ERROR, "Unable to read table: " + ioe.getMessage()); return; } loadNetwork(tm); tm.setProgress(1.0); }
public void generateReport() throws IOException { System.out.println("Test Report Gen"); Sheet sheet = workbook.getSheet("sheet1"); try { String strSql = "Select * from participant;"; Sql db = new Sql(); ResultSet rs = db.executeQuery(strSql); ResultSetMetaData rsMeta = rs.getMetaData(); Row row = sheet.createRow(0); Cell cell; // get table columns which will act as title columns for (int i = 1; i <= rsMeta.getColumnCount(); i++) { String colName = rsMeta.getColumnName(i); int dataType = rsMeta.getColumnType(i); cell = row.createCell(i - 1); cell.setCellValue(colName); } int i = 1; while (rs.next()) { // create row to write data to row = sheet.createRow(i); // create cells for writing data to for (int c = 1; c <= rsMeta.getColumnCount(); c++) { cell = row.createCell(c - 1); String colName = rsMeta.getColumnName(c); cell.setCellValue(rs.getString(colName)); } i++; } JOptionPane.showMessageDialog(null, "Excel Report of All Participants Generated"); } catch (SQLException ex) { ex.printStackTrace(); // log Error FacesFingerPrintProject.logger.log(Level.SEVERE, "ERROR", ex); } /// Write the output to a file FileOutputStream fileOut = new FileOutputStream("reportTest.xls"); workbook.write(fileOut); fileOut.close(); }
public String getExcelData(String sheetName, int rowNum, int colNum) { String retVal = null; try { FileInputStream fis = new FileInputStream(path1); Workbook wb = WorkbookFactory.create(fis); Sheet s = wb.getSheet(sheetName); Row r = s.getRow(rowNum); Cell c = r.getCell(colNum); retVal = c.getStringCellValue(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return retVal; }
private void generatePrivateDataRegion(Workbook wb, StringBuilder sb) { // Get the Private Data Sheet Sheet sheet = wb.getSheet("PrivateData"); Iterator<Row> rowIt = sheet.rowIterator(); // Ignore the Header row rowIt.next(); while (rowIt.hasNext()) { Row row = rowIt.next(); Cell cellId = row.getCell(0); if (cellId != null) { int id = (int) cellId.getNumericCellValue(); Cell cellType = row.getCell(1); String type = cellType.getStringCellValue().replaceAll(" ", ""); Cell cellDescription = row.getCell(2); String description = cellDescription.getStringCellValue().replaceAll("\"", "'"); Cell cellAttributes = row.getCell(3); String attributes = cellAttributes.getStringCellValue(); sb.append("PrivateData PD" + id + " {"); sb.append("\n"); sb.append("\tName \"PD" + id + "\""); sb.append("\n"); sb.append("\tDescription \"" + description + "\""); sb.append("\n"); sb.append("\tType " + type); sb.append("\n"); for (String a : attributes.split(",\n")) { a = a.substring(0, 1).toUpperCase() + a.substring(1); sb.append("\tAttribute \"" + a + "\" {"); sb.append("\n"); sb.append("\t\tDescription \"" + a + "\""); sb.append("\n\t}"); sb.append("\n"); } // Delete last '\n' // sb.deleteCharAt(sb.length() - 2); sb.deleteCharAt(sb.length() - 1); sb.append("\n}"); sb.append("\n\n"); } else break; } }
public void writeToExcel(String sheetName, int rowNum, int colNum, String desc) { try { FileInputStream fis = new FileInputStream(path1); Workbook wb = WorkbookFactory.create(fis); Sheet s = wb.getSheet(sheetName); Row r = s.getRow(rowNum); Cell c = r.createCell(colNum); c.setCellValue(desc); FileOutputStream fos = new FileOutputStream(path1); wb.write(fos); fos.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (InvalidFormatException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
public String[] getHeaders() throws FileNotFoundException, IOException, InvalidFormatException { InputStream inp; inp = new FileInputStream(fileName); Workbook wb = WorkbookFactory.create(inp); List<String> listHeaders = new ArrayList(); Row row; Sheet sheet = wb.getSheet(sheetName); row = sheet.getRow(0); for (int j = 0; j < row.getLastCellNum(); j++) { if (row.getCell(j).getStringCellValue().isEmpty() || row.getCell(j).getStringCellValue() == null) { break; } listHeaders.add(row.getCell(j).getStringCellValue()); } inp.close(); return listHeaders.toArray(new String[listHeaders.size()]); }