List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getLastRowNum
@Override
public int getLastRowNum()
From source file:StatusUpdater.java
static void addStatusUpdate(String path, String username, String task, String comments, int optionChosen) { DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss"); Date dateobj = new Date(); FileInputStream file = null;/*w w w . j a va 2 s . co m*/ try { file = new FileInputStream(new File(path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(file); } catch (IOException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(1); int rownum = sheet.getLastRowNum(); //Blank workbook Row row = sheet.createRow(rownum + 1); Cell usernameCell = row.createCell(0); usernameCell.setCellValue(username); Cell taskCell = row.createCell(1); taskCell.setCellValue(task); Cell statusCell = row.createCell(2); switch (optionChosen) { case 1: statusCell.setCellValue("Resumed"); break; case 2: statusCell.setCellValue("Paused"); break; case 3: statusCell.setCellValue("Deferred"); break; case 4: statusCell.setCellValue("Completed"); break; } Cell timestampCell = row.createCell(3); timestampCell.setCellValue(df.format(dateobj).toString()); Cell commentsCell = row.createCell(4); commentsCell.setCellValue(comments); FileOutputStream out = null; try { out = new FileOutputStream(new File(path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } try { workbook.write(out); } catch (IOException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:ExcelConverter.java
public List<ScheduleClass> Converter() throws FileNotFoundException, IOException { ArrayList<ScheduleClass> scheduleList = new ArrayList<>(); FileInputStream fis = new FileInputStream(pathFile); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet = wb.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); CellRangeAddress add;// w w w .j ava2 s. com int colNoIdx = 0; ArrayList<String> dosen = new ArrayList<>(); ArrayList<Integer> idxDosen = new ArrayList<>(); ArrayList<Integer> colDosen = new ArrayList<>(); ArrayList<String> location = new ArrayList<>(); int idxNumber = 0; ArrayList<Integer> locationIdx = new ArrayList<>(); outerloop: for (int j = 0; j < sheet.getLastRowNum(); j++) { row = sheet.getRow(j); for (int f = 0; f < row.getLastCellNum(); f++) { Cell cell = row.getCell(j); if (cell.getStringCellValue().contains("No.")) { rowNoIdx = j; colNoIdx = cell.getColumnIndex(); break outerloop; } } } outerloop2: for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); outerloop: for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == colNoIdx && i > rowNoIdx + 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { i = sheet.getLastRowNum(); break outerloop2; } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 1)) { String delims = "[,. ]"; String[] sumary = cell.getStringCellValue().split(delims); for (int l = 0; l < sumary.length; l++) { if (sumary[l].equalsIgnoreCase("Mrt")) { sumary[l] = "3"; } } lc = LocalDate.of(Integer.parseInt(sumary[5]), Integer.parseInt(sumary[3]), Integer.parseInt(sumary[2])); } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 2)) { if (cell.getStringCellValue().equalsIgnoreCase("LIBUR")) { i = i + 1; break outerloop; } else { String delimsJam = "[-]"; String[] arrJam = cell.getStringCellValue().split(delimsJam); for (int k = 0; k < arrJam.length; k++) { arrJam[k] = arrJam[k].replace('.', ':'); } lt = LocalTime.parse(arrJam[0]); } } if (cell.getRowIndex() > rowNoIdx + 1 && cell.getColumnIndex() == (colNoIdx + 5)) { subject = cell.getStringCellValue(); } if (cell.getRowIndex() > rowNoIdx && cell.getColumnIndex() >= colNoIdx + 6 && cell.getColumnIndex() < row.getLastCellNum()) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add(splt2[l].trim()); location.add("Lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); if (!cell.getStringCellValue().isEmpty()) { dosen.add(cell.getStringCellValue().trim()); location.add(String.valueOf((int) c.getNumericCellValue()).trim()); } } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { dosen.add("".trim()); location.add(""); } } else { if (!c.getStringCellValue().isEmpty()) { dosen.add(""); location.add(""); } } } } } for (int j = 0; j < dosen.size(); j++) { scheduleList .add(new ScheduleClass(lc, lt, lt.plusHours(2), subject, dosen.get(j), location.get(j))); } dosen.clear(); location.clear(); } return Mergering(scheduleList); }
From source file:FenetrePrincipal.java
/** * This method is called from within the constructor to initialize the form. * WARNING: Do NOT modify this code. The content of this method is always * regenerated by the Form Editor./*ww w. ja v a2s.c om*/ */ @SuppressWarnings("unchecked") // <editor-fold defaultstate="collapsed" desc="Generated Code"> private void initComponents() { jScrollBar1 = new JScrollBar(); jPanelOption = new JPanel(); jPanelBanniere = new JPanel(); jLabelBanniere = new JLabel(); jPanel4 = new JPanel(); jLabel1 = new JLabel(); jPanelCentre = new JPanel(); jScrollPane1 = new JScrollPane(); jTable1 = new JTable(); jMenuBar1 = new JMenuBar(); jMenu1 = new JMenu(); jMenu2 = new JMenu(); ajouterBouton = new JButton("Ajouter Etudiant"); statBouton = new JButton(" Satistiques "); jPanelBouton1 = new JPanel(); jPanelBouton2 = new JPanel(); pdfBouton = new JButton(" Exporter Pdf "); exportBouton = new JButton("Exporter Fichier "); setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE); getContentPane().setLayout(new javax.swing.BoxLayout(getContentPane(), javax.swing.BoxLayout.LINE_AXIS)); jLabelBanniere.setText(""); javax.swing.GroupLayout jPanelBanniereLayout = new javax.swing.GroupLayout(jPanelBanniere); jPanelBanniere.setLayout(jPanelBanniereLayout); jPanelBanniereLayout.setHorizontalGroup(jPanelBanniereLayout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING).addComponent(jLabelBanniere, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)); jPanelBanniereLayout .setVerticalGroup( jPanelBanniereLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(jPanelBanniereLayout.createSequentialGroup() .addComponent(jLabelBanniere, javax.swing.GroupLayout.PREFERRED_SIZE, 50, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(0, 0, Short.MAX_VALUE))); jPanel4.setLayout(new java.awt.GridLayout(4, 1)); jLabel1.setText(""); jPanel4.add(jPanelBouton1); jPanel4.add(jPanelBouton2); jPanelBouton1.add(ajouterBouton); jPanelBouton1.add(statBouton); jPanelBouton2.add(exportBouton); jPanelBouton2.add(pdfBouton); try { File excel = new File("results.xlsx"); FileInputStream fis = new FileInputStream(excel); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheet("M1 Informatique - S8 - Pre-insc"); rowNum = ws.getLastRowNum() + 1; colNum = ws.getRow(0).getLastCellNum(); data = new String[rowNum][colNum]; for (int i = 0; i < rowNum; i++) { XSSFRow row = ws.getRow(i); for (int j = 0; j < colNum; j++) { XSSFCell cell = row.getCell(j); String value = cellToString(cell); data[i][j] = value; System.out.println("The value is : " + value); } } } catch (FileNotFoundException e) { // TODO Auto-generated catch block System.out.println("Erreur"); e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } String[] entetes = new String[colNum]; for (int i = 0; i < colNum; i++) { entetes[i] = data[0][i]; } jTable1.setModel(new javax.swing.table.DefaultTableModel(data, entetes)); jScrollPane1.setViewportView(jTable1); jTable1.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); javax.swing.GroupLayout jPanelCentreLayout = new javax.swing.GroupLayout(jPanelCentre); jPanelCentre.setLayout(jPanelCentreLayout); jPanelCentreLayout.setHorizontalGroup(jPanelCentreLayout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(jPanelCentreLayout.createSequentialGroup().addContainerGap() .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 465, Short.MAX_VALUE) .addContainerGap())); jPanelCentreLayout.setVerticalGroup(jPanelCentreLayout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(jPanelCentreLayout.createSequentialGroup().addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 481, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(0, 0, Short.MAX_VALUE))); javax.swing.GroupLayout jPanelOptionLayout = new javax.swing.GroupLayout(jPanelOption); jPanelOption.setLayout(jPanelOptionLayout); jPanelOptionLayout.setHorizontalGroup( jPanelOptionLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addComponent(jPanelBanniere, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addGroup(jPanelOptionLayout.createSequentialGroup() .addComponent(jPanel4, javax.swing.GroupLayout.PREFERRED_SIZE, 150, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jPanelCentre, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))); jPanelOptionLayout.setVerticalGroup(jPanelOptionLayout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(jPanelOptionLayout.createSequentialGroup() .addComponent(jPanelBanniere, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addGroup(jPanelOptionLayout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addComponent(jPanelCentre, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(jPanel4, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))); getContentPane().add(jPanelOption); jMenu1.setText("Fichier"); jMenuBar1.add(jMenu1); JMenuItem importer = new JMenuItem("Importer"); JMenuItem exporter = new JMenuItem("Exporter"); jMenu1.add(importer); jMenu1.add(exporter); jMenu2.setText("Edition"); jMenuBar1.add(jMenu2); setJMenuBar(jMenuBar1); importer.addActionListener(new ActionListener() { @Override public void actionPerformed(ActionEvent e) { // TODO Auto-generated method stub //FileExtensionFilterDemo f = new FileExtensionFilterDemo(); showOpenFileDialog(); } }); pack(); }
From source file:TaskAdder.java
static void addtask(String task, String comments, String username, String excel_path) { DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss"); Date dateobj = new Date(); FileInputStream file = null;//from w ww. j a va2 s.c om try { file = new FileInputStream(new File(excel_path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(file); } catch (IOException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); int rownum = sheet.getLastRowNum(); //Blank workbook Row row = sheet.createRow(rownum + 1); Cell usernameCell = row.createCell(0); usernameCell.setCellValue(username); Cell taskCell = row.createCell(1); taskCell.setCellValue(task); Cell statusCell = row.createCell(2); statusCell.setCellValue("In-Progress"); Cell timestampCell = row.createCell(3); timestampCell.setCellValue(df.format(dateobj).toString()); Cell commentsCell = row.createCell(5); commentsCell.setCellValue(comments); FileOutputStream out = null; try { out = new FileOutputStream(new File(excel_path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } try { workbook.write(out); } catch (IOException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:TaskAdder.java
static void addAllTask(String task, String comments, String username, String excel_path) { DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss"); Date dateobj = new Date(); FileInputStream file = null;/* ww w .j av a 2s . c o m*/ try { file = new FileInputStream(new File(excel_path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = null; try { workbook = new XSSFWorkbook(file); } catch (IOException ex) { Logger.getLogger(TaskFetcher.class.getName()).log(Level.SEVERE, null, ex); } //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(1); int rownum = sheet.getLastRowNum(); //Blank workbook Row row = sheet.createRow(rownum + 1); Cell usernameCell = row.createCell(0); usernameCell.setCellValue(username); Cell taskCell = row.createCell(1); taskCell.setCellValue(task); Cell statusCell = row.createCell(2); statusCell.setCellValue("Task Created"); Cell timestampCell = row.createCell(3); timestampCell.setCellValue(df.format(dateobj).toString()); Cell commentsCell = row.createCell(4); commentsCell.setCellValue(comments); FileOutputStream out = null; try { out = new FileOutputStream(new File(excel_path)); } catch (FileNotFoundException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } try { workbook.write(out); } catch (IOException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(TaskAdder.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Authenticator.java
static boolean authenticate(String username, String path) { int i = 0;// ww w .j a v a 2s . c o m try { FileInputStream f_input = new FileInputStream(new File(path)); XSSFWorkbook workbook = new XSSFWorkbook(f_input); XSSFSheet sheet = workbook.getSheetAt(0); int rows_number = sheet.getLastRowNum(); System.out.println(rows_number); XSSFRow row_user = null; XSSFCell cell_user = null; for (int iterator = 1; iterator <= rows_number; iterator++) { row_user = sheet.getRow(iterator); cell_user = row_user.getCell(0); String valid_username = cell_user.getStringCellValue(); if (username.equals(valid_username)) { System.out.println("valid user"); return true; } } return false; } catch (Exception e) { e.printStackTrace(); } return false; }
From source file:ExampleClass.java
public static void main(String[] args) throws Exception { File src = new File( "C:\\Users\\Ariq\\Documents\\NetBeansProjects\\Skripsi-Jadwal-Mengawas-Ujian\\Contoh File\\Jadwal_Pengawas_ Ujian_Pak_ Pascal.xlsx"); //File src = new File("D:\\\\Skripsi\\\\Data Baru\\\\Daftar Dosen.xlsx"); FileInputStream fis = new FileInputStream(src); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sheet1 = wb.getSheetAt(0); // Iterator< Row> rowIterator = sheet1.iterator(); int colIndex = 0; int ex = 0;//from ww w . j ava 2s . c o m int lastCol = sheet1.getLastRowNum(); int i = 0; int idx = 0; CellRangeAddress add; // while (rowIterator.hasNext()) { // row = (XSSFRow) rowIterator.next(); // Iterator< Cell> cellIterator = row.cellIterator(); // //System.out.println("i = "+i+", ex:"+ex); // // if (row.getRowNum() > 53) { // break; // } //// if(lastCol-(ex+1) == i) break; // while (cellIterator.hasNext()) { // Cell cell = cellIterator.next(); // for (int f = 0; f < sheet1.getNumMergedRegions(); f++) { // add = sheet1.getMergedRegion(f); // // int col = add.getFirstColumn(); // int rowNum = add.getFirstRow(); // if (rowNum != 0 && rowNum == cell.getRowIndex() && colIndex == cell.getColumnIndex()) { // System.out.println("col:"+col+" "+",row :"+rowNum); // String b = String.valueOf(sheet1.getRow(rowNum).getCell(col)); // System.out.println(b); // // } // // } // switch (cell.getCellType()) // { // case Cell.CELL_TYPE_FORMULA: // ex++; // switch (cell.getCachedFormulaResultType()) // { // case Cell.CELL_TYPE_NUMERIC: // i = (int)cell.getNumericCellValue(); // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // // // break; // } // break; // case Cell.CELL_TYPE_NUMERIC: // if (cell.getColumnIndex() >= 6) // { // System.out.print( // (int)cell.getNumericCellValue() + " \t\t " ); // } // break; // case Cell.CELL_TYPE_STRING: // add = sheet1.getMergedRegion(cell.getRowIndex()); // // if (cell.getStringCellValue().contentEquals("No.")) // { // colIndex = cell.getColumnIndex(); // } // if (cell.getColumnIndex() == 1) // { // System.out.print( // cell.getStringCellValue() + " \t\t " ); // } // break; // // } // } // // System.out.println(); // } for (int j = 0; j < sheet1.getLastRowNum(); j++) { row = sheet1.getRow(j); for (int k = 0; k < row.getLastCellNum(); k++) { Cell cell = row.getCell(k); // if (cell.getColumnIndex() == 1) // { // System.out.println(cell.getStringCellValue()); // } FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); if (cell.getColumnIndex() == 0 && j > 3 && evaluator.evaluate(cell).getCellType() != Cell.CELL_TYPE_NUMERIC) { System.exit(k); } if (cell.getColumnIndex() >= 6 && cell.getColumnIndex() <= 11) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.print((int) cell.getNumericCellValue() + " "); } if (cell.getCellType() == Cell.CELL_TYPE_STRING) { if (cell.getStringCellValue().contains(":")) { String[] splt = cell.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { CellReference cr = new CellReference(1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); System.out.print( cell.getStringCellValue() + " Ruang =" + (int) c.getNumericCellValue() + " "); } } if (cell.getCellType() == Cell.CELL_TYPE_BLANK && cell.getRowIndex() > 2) { CellReference cr = new CellReference(cell.getRowIndex() - 1, cell.getColumnIndex()); Row row2 = sheet1.getRow(cr.getRow()); Cell c = row2.getCell(cr.getCol()); CellReference cr2 = new CellReference(1, cell.getColumnIndex()); Row row3 = sheet1.getRow(cr2.getRow()); Cell c2 = row3.getCell(cr2.getCol()); if (c.getStringCellValue().contains(":")) { String[] splt = c.getStringCellValue().split(":"); String[] splt2 = splt[1].split(","); for (int l = 0; l < splt2.length; l++) { System.out.println(splt2[l] + "= lab"); } } else { System.out.print( c.getStringCellValue() + " Ruang = " + (int) c2.getNumericCellValue() + " "); } } } } System.out.println(""); } System.out.println(colIndex); System.out.println(idx); fis.close(); }
From source file:accounts.ExcelUtils.java
License:Apache License
public Map<String, Map<TRId, TR>> processAllSheets(String filename, Map<String, BankAccount> baMap, String accountName) throws IOException, DBException { Map<String, Map<TRId, TR>> excelTrMap = new TreeMap<>(); FileInputStream file = new FileInputStream(new File(filename)); // Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); String sheetName = workbook.getSheetName(i); if ("RentalSummary".equalsIgnoreCase(sheetName) || "CompanySummary".equalsIgnoreCase(sheetName) || "PersonalSummary".equalsIgnoreCase(sheetName)) { continue; }/*from w w w.j av a 2 s . c o m*/ if (accountName != null && !accountName.equalsIgnoreCase(sheetName)) { continue; } Map<TRId, TR> mapTr = new HashMap<>(); excelTrMap.put(sheetName, mapTr); System.out.println("Processing sheet: " + sheetName); BankAccount ba = baMap.get(sheetName); if (ba == null) { throw new IOException("Unknown bank account name in excel=" + sheetName); } for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); // Get iterator to all cells of current row TR tr = DBFactory.inst().createCorrespondingTRObj(ba); tr.setDate(row.getCell(0).getDateCellValue()); tr.setDescription(row.getCell(1).getStringCellValue()); tr.setDebit((float) row.getCell(2).getNumericCellValue()); tr.setComment(row.getCell(3).getStringCellValue()); tr.setTrType(row.getCell(4).getStringCellValue()); tr.setTaxCategory(row.getCell(5).getStringCellValue()); tr.setProperty(row.getCell(6).getStringCellValue()); tr.setOtherEntity(row.getCell(7).getStringCellValue()); String locked = row.getCell(8).getStringCellValue(); tr.setLocked("YES".equalsIgnoreCase(locked)); tr.setTrId(); mapTr.put(tr.getTrId(), tr); } } return excelTrMap; }
From source file:accounts.ExcelUtils.java
License:Apache License
public Map<String, Map<TRId, TR>> processAllSheets(String filename) throws IOException { Map<String, Map<TRId, TR>> excelTrMap = new TreeMap<>(); FileInputStream file = new FileInputStream(new File(filename)); // Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); String sheetName = workbook.getSheetName(i); Map<TRId, TR> mapTr = new HashMap<>(); excelTrMap.put(sheetName, mapTr); System.out.println("Processing sheet: " + sheetName); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); // Get iterator to all cells of current row TR tr = new TRNonDB(); tr.setDate(row.getCell(0).getDateCellValue()); tr.setDescription(row.getCell(1).getStringCellValue()); tr.setDebit((float) row.getCell(2).getNumericCellValue()); tr.setComment(row.getCell(3).getStringCellValue()); tr.setTrType(row.getCell(4).getStringCellValue()); tr.setTaxCategory(row.getCell(5).getStringCellValue()); tr.setProperty(row.getCell(6).getStringCellValue()); tr.setOtherEntity(row.getCell(7).getStringCellValue()); String lockedStr = row.getCell(7).getStringCellValue(); if ("YES".equalsIgnoreCase(lockedStr) || "TRUE".equalsIgnoreCase(lockedStr)) { tr.setLocked(true);/*w ww. j ava 2 s . c om*/ } tr.setTrId(); mapTr.put(tr.getTrId(), tr); } } return excelTrMap; }
From source file:aco.Utilities.java
License:Open Source License
static void writeExcel(int n, int m, int result) { //the file already exists; we should add a new row as the last one in the Excel file if (new File(filePath).canRead()) { //System.out.println("File already exists.."); try {//from w ww . j a va2 s . co m FileInputStream file = new FileInputStream(new File(filePath)); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook1 = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet1 = workbook1.getSheetAt(2); int countRows = sheet1.getLastRowNum() + 1; Row newRow = sheet1.createRow(countRows++); int cellnum = 0; Cell cell = newRow.createCell(cellnum++); cell.setCellValue(n); cell = newRow.createCell(cellnum++); cell.setCellValue(m); cell = newRow.createCell(cellnum++); cell.setCellValue(result); //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath)); workbook1.write(out); out.close(); //System.out.println("Written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } } else { //Blank workbook XSSFWorkbook workbook2 = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet2 = workbook2.createSheet("Results - 51 cities"); //Iterate over data and write to sheet int rownum = 0, cellnum = 0; Row row = sheet2.createRow(rownum++); Cell cell = row.createCell(cellnum++); cell.setCellValue(n); cell = row.createCell(cellnum++); cell.setCellValue(m); cell = row.createCell(cellnum++); cell.setCellValue(result); try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(new File(filePath)); workbook2.write(out); out.close(); //System.out.println("Written successfully on disk."); } catch (Exception e) { e.printStackTrace(); } } }