List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
From source file:Utility.CSV_File_Generator.java
public static void write_single_row(XSSFSheet original_sheet, XSSFSheet resultSheet, int index) { FileOutputStream out = null;// w w w.j a v a 2 s. c o m try { out = new FileOutputStream(new File(file_details("Excel_Traffic_Rows"))); int col_index; //Create blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet intermediate = workbook.createSheet("Test Data"); //Create row object Row row; XSSFRow intermediate_row; XSSFRow actual_row = original_sheet.getRow(index); //This data needs to be written (Object[]) TreeMap<String, TreeMap<String, Cell>> row_map = new TreeMap<String, TreeMap<String, Cell>>(); if (index == 0) { TreeMap<String, Cell> cols = new TreeMap<String, Cell>(); XSSFRow temp = intermediate.createRow(index); Iterator<Cell> cellIterator = actual_row.cellIterator(); int i = 1; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cols.put(Integer.toString(i++), cell); } row_map.put("0", cols); Set<String> keyid = row_map.get("0").keySet(); int cellid = 0; for (String key : keyid) { Cell original = cols.get(key); Cell cell = temp.createCell(cellid++); switch (original.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(original.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(original.getStringCellValue()); break; } } workbook.write(out); row_map.clear(); } else { int count = 0; while (count < index) { Iterator<Row> rowIterator = resultSheet.iterator(); while (rowIterator.hasNext()) { //Reading a row from the existing result sheet TreeMap<String, Cell> data_row = new TreeMap<String, Cell>(); row = rowIterator.next(); Iterator<Cell> cell = row.cellIterator(); col_index = 0; while (cell.hasNext()) { Cell c = cell.next(); data_row.put(Integer.toString(col_index++), c); } row_map.put(Integer.toString(count), data_row); count++; } //writing the row read into the new workbook(intermediate) Set<String> keyid = row_map.keySet(); for (String key : keyid) { int column_counter = 0; intermediate_row = intermediate.createRow(Integer.parseInt(key)); TreeMap<String, Cell> map = row_map.get(key); Set<String> row_data = map.keySet(); for (String cell_data : row_data) { Cell original = map.get(cell_data); Cell new_cell = intermediate_row.createCell(column_counter++); switch (original.getCellType()) { case Cell.CELL_TYPE_NUMERIC: new_cell.setCellValue(original.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: new_cell.setCellValue(original.getStringCellValue()); break; } } } } XSSFRow temp = intermediate.createRow(index); Iterator<Cell> cellIterator = actual_row.cellIterator(); TreeMap<String, Cell> required_data = new TreeMap<String, Cell>(); int i = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); required_data.put(Integer.toString(i++), cell); } row_map.put(Integer.toString(index), required_data); required_data = row_map.get(Integer.toString(index)); Set<String> keyid = required_data.keySet(); int cellid = 0; for (String key : keyid) { Cell original = required_data.get(key); Cell cell = temp.createCell(cellid++); switch (original.getCellType()) { case Cell.CELL_TYPE_NUMERIC: cell.setCellValue(original.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: cell.setCellValue(original.getStringCellValue()); break; } } workbook.write(out); out.flush(); row_map.clear(); } out.close(); } catch (FileNotFoundException ex) { Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("Traffic Data is: " + index + " row."); }
From source file:utils.EXCELTODB.java
public static void main(String[] args) { try {/* w w w . j a v a2 s . c om*/ Class.forName("com.mysql.jdbc.Driver"); Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/lldval", "root", "passw0rd"); con.setAutoCommit(false); PreparedStatement pstm = null; FileInputStream input = new FileInputStream("./PCAT_AnalysisFile.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(input); XSSFSheet sheet = wb.getSheetAt(1); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); int id = (int) row.getCell(0).getNumericCellValue(); String name = row.getCell(1).getStringCellValue(); String address = row.getCell(2).getStringCellValue(); String sql = "INSERT INTO lldval.test VALUES('" + id + "','" + name + "','" + address + "')"; pstm = (PreparedStatement) con.prepareStatement(sql); pstm.execute(); System.out.println("Import rows " + i); } con.commit(); // pstm.close(); con.close(); input.close(); System.out.println("Success import excel to mysql table"); } catch (ClassNotFoundException e) { System.out.println(e); } catch (SQLException ex) { System.out.println(ex); } catch (IOException ioe) { System.out.println(ioe); } }
From source file:Wael.UI.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed //start if the button //aliases variables try {//from w w w.j av a 2s .c om if (jRadioButton1.isSelected()) { //Brocade Configuration variables int a; int b; int c; int d; int e; int f; File file = new File(jTextField2.getText() + "\\" + jTextField3.getText()); file.createNewFile(); FileWriter brocadefile = new FileWriter(file); FileInputStream fileinputstream = new FileInputStream(jTextField1.getText()); XSSFWorkbook workbook = new XSSFWorkbook(fileinputstream); XSSFSheet alisheet = workbook.getSheet("Aliases"); XSSFSheet zonesheet = workbook.getSheet("Zones"); XSSFSheet cfgsheet = workbook.getSheet("CFG"); b = alisheet.getPhysicalNumberOfRows(); d = zonesheet.getPhysicalNumberOfRows(); f = cfgsheet.getPhysicalNumberOfRows(); for (a = 0; a < b; a++) { XSSFRow rowa = alisheet.getRow(a); XSSFCell cellza = rowa.getCell((int) 0); XSSFCell cellzb = rowa.getCell((int) 1); if (cellza.getStringCellValue().equals("") || cellza.getStringCellValue() == null) { break; } brocadefile.write("alicreate" + " " + "\"" + cellza.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellzb.getStringCellValue().trim() + "\""); brocadefile.write("\n"); } brocadefile.write("\n"); brocadefile.write("\n"); //Start of zone creation for (c = 0; c < d; c++) { XSSFRow rowc = zonesheet.getRow(c); XSSFCell cellca = rowc.getCell((int) 0); XSSFCell cellcb = rowc.getCell((int) 1); XSSFCell cellcc = rowc.getCell((int) 2); if (cellca.getStringCellValue().equals("") || cellca.getStringCellValue() == null) { break; } //zonecreate "zonemame", "member1; member2" brocadefile.write("zonecreate" + " " + "\"" + cellca.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellcb.getStringCellValue().trim() + ";" + " " + cellcc.getStringCellValue().trim() + "\""); brocadefile.write("\n"); } brocadefile.write("\n"); brocadefile.write("\n"); XSSFRow rowc = cfgsheet.getRow(0); XSSFCell cellcfgcreateA = rowc.getCell((int) 0); XSSFCell cellcfgcreateB = rowc.getCell((int) 1); brocadefile.write("cfgcreate" + " " + "\"" + cellcfgcreateB.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellcfgcreateA.getStringCellValue().trim() + "\""); brocadefile.write("\n"); brocadefile.write("\n"); for (e = 1; e < f; e++) { XSSFRow rowe = cfgsheet.getRow(e); XSSFCell cellea = rowe.getCell((int) 0); XSSFCell celleb = rowe.getCell((int) 1); if (cellea.getStringCellValue().equals("") || cellea.getStringCellValue() == null) { break; } brocadefile.write("cfgadd" + " " + "\"" + celleb.getStringCellValue().trim() + "\"" + "," + " " + "\"" + cellea.getStringCellValue().trim() + "\""); brocadefile.write("\n"); } brocadefile.write("\n"); brocadefile.write("\n"); brocadefile.close(); } //Start of Cisco Configuration else if (jRadioButton2.isSelected()) { //Cisco Configuration variables int g; int h; int i; int j; int k; int l; File file = new File(jTextField2.getText() + "\\" + jTextField3.getText()); file.createNewFile(); FileWriter ciscofile = new FileWriter(file); FileInputStream fileinputstream = new FileInputStream(jTextField1.getText()); XSSFWorkbook ciscoworkbook = new XSSFWorkbook(fileinputstream); XSSFSheet alisheet = ciscoworkbook.getSheet("Aliases"); XSSFSheet zonesheet = ciscoworkbook.getSheet("Zones"); XSSFSheet cfgsheet = ciscoworkbook.getSheet("CFG"); h = alisheet.getPhysicalNumberOfRows(); j = zonesheet.getPhysicalNumberOfRows(); l = cfgsheet.getPhysicalNumberOfRows(); ciscofile.write("config t"); ciscofile.write("\n"); ciscofile.write("\n"); //create aliases for Cisco switch for (g = 0; g < h; g++) { XSSFRow rowg = alisheet.getRow(g); XSSFCell cellga = rowg.getCell((int) 0); XSSFCell cellgb = rowg.getCell((int) 1); XSSFCell cellgc = rowg.getCell((int) 2); //config t //fcalias name CX4240_21_SPA vsan 3 //member pwwn 50:06:01:60:46:e0:0f:ba //exit if (cellga.getStringCellValue().equals("") || cellga.getStringCellValue() == null) { break; } ciscofile.write("fcalias name" + " " + cellga.getStringCellValue().trim() + " " + cellgc.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("member pwwn" + " " + cellgb.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("exit"); ciscofile.write("\n"); ciscofile.write("\n"); } ciscofile.write("\n"); //create zones for cisco switch for (i = 0; i < j; i++) { XSSFRow rowi = zonesheet.getRow(i); XSSFCell cellia = rowi.getCell((int) 0); XSSFCell cellib = rowi.getCell((int) 1); XSSFCell cellic = rowi.getCell((int) 2); XSSFCell cellid = rowi.getCell((int) 3); //zone name U52P1_13 vsan 3 //member fcalias CX4240_21_SPA //member fcalias Unix1_52_P1 //exit if (cellia.getStringCellValue().equals("") || cellia.getStringCellValue() == null) { break; } ciscofile.write("zone name" + " " + cellia.getStringCellValue().trim() + " " + cellid.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("member fcalias" + " " + cellib.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("member fcalias" + " " + cellic.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("exit"); ciscofile.write("\n"); ciscofile.write("\n"); } ciscofile.write("\n"); ciscofile.write("\n"); //create zoneset and add it to a VSAN // XSSFRow rowmain = cfgsheet.getRow(1); XSSFCell cellcfgname = rowmain.getCell((int) 1); XSSFCell cellvsanname = rowmain.getCell((int) 2); //zoneset name main_config vsan 3 ciscofile.write("zoneset name" + " " + cellcfgname.getStringCellValue().trim() + " " + cellvsanname.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("\n"); for (k = 0; k < l; k++) { XSSFRow rowk = cfgsheet.getRow(k); XSSFCell cellka = rowk.getCell((int) 0); if (cellka.getStringCellValue().equals("") || cellka.getStringCellValue() == null) { break; } //member L51P2_14 ciscofile.write("member" + " " + cellka.getStringCellValue().trim()); ciscofile.write("\n"); } ciscofile.write("\n"); XSSFRow rowend = cfgsheet.getRow(1); XSSFCell cellendb = rowend.getCell((int) 1); XSSFCell cellendc = rowend.getCell((int) 2); //zoneset activate name main_config vsan 3 ciscofile.write("zoneset activate name" + " " + cellendb.getStringCellValue().trim() + " " + cellendc.getStringCellValue().trim()); ciscofile.write("\n"); ciscofile.write("\n"); ciscofile.close(); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:WeeklyOPD.ReadWeeklyTarget.java
public static void main(String args[]) { /******************************* TO UPDATE ON EACH RUN ***************************/ targetFile = "May 2014.xlsx"; weekNumber = 1;/*from w w w. j a v a 2 s.co m*/ femaleRowNum = 1212; childRowNum = 749; allRowNum = 7414; crNo = 187394;//crNo to begin with /*********************************************************************************/ rowCnt = 1; GenerateDailyNewOldExcelPickingRowsSequentially.mainCreateExcelAndInitialize(); try { FileInputStream targetFileIn = new FileInputStream(path + targetFile); XSSFWorkbook workbookTarget = new XSSFWorkbook(targetFileIn); XSSFSheet sheetTarget = workbookTarget.getSheetAt(0); out = new FileOutputStream(new File(path + "Week" + weekNumber + targetFile)); int startAtRow = weekNumber == 1 ? 4 : weekNumber == 2 ? 11 : weekNumber == 3 ? 18 : weekNumber == 4 ? 25 : 32; int rowsToRead = 7; System.out.println("Physical No. Of Rows: " + sheetTarget.getPhysicalNumberOfRows()); for (int rowNum = startAtRow; rowNum <= startAtRow + rowsToRead - 1 && rowNum < sheetTarget.getPhysicalNumberOfRows(); rowNum++) { System.out.println("RowNum: " + rowNum); Row row = sheetTarget.getRow(rowNum); // int date = (int) row.getCell(0).getNumericCellValue(); //// String[] splitDate = date.split("/"); // System.out.println (date); //// System.out.println (splitDate.length); // int day = Integer.parseInt(splitDate[0]); // int month = Integer.parseInt(splitDate[1]); // int year = Integer.parseInt(splitDate[2]); int medicineNew = (int) row.getCell(1).getNumericCellValue(); int surgeryNew = (int) row.getCell(4).getNumericCellValue(); int ophthalmologyNew = (int) row.getCell(7).getNumericCellValue(); int entNew = (int) row.getCell(10).getNumericCellValue(); int paediatricsNew = (int) row.getCell(13).getNumericCellValue(); int ogNew = (int) row.getCell(16).getNumericCellValue(); int orthopaedicsNew = (int) row.getCell(19).getNumericCellValue(); int dentalNew = (int) row.getCell(22).getNumericCellValue(); int casualtyNew = (int) row.getCell(25).getNumericCellValue(); int medicineOld = (int) row.getCell(2).getNumericCellValue(); int surgeryOld = (int) row.getCell(5).getNumericCellValue(); int ophthalmologyOld = (int) row.getCell(8).getNumericCellValue(); int entOld = (int) row.getCell(11).getNumericCellValue(); int paediatricsOld = (int) row.getCell(14).getNumericCellValue(); int ogOld = (int) row.getCell(17).getNumericCellValue(); int orthopaedicsOld = (int) row.getCell(20).getNumericCellValue(); int dentalOld = (int) row.getCell(23).getNumericCellValue(); System.out.println(medicineNew + "\t" + medicineOld + "\t" + surgeryOld + "\t" + surgeryNew + "\t" + surgeryOld + "\t" + surgeryNew + "\t" + ophthalmologyOld + "\t" + ophthalmologyNew + "\t" + entOld + "\t" + entNew + "\t" + paediatricsOld + "\t" + paediatricsNew + "\t" + ogOld + "\t" + ogNew + "\t" + orthopaedicsOld + "\t" + orthopaedicsNew + "\t" + dentalOld + "\t" + dentalNew + "\t" + casualtyNew); GenerateDailyNewOldExcelPickingRowsSequentially.mainGenerateExcel(medicineNew, medicineOld, surgeryNew, surgeryOld, ophthalmologyNew, ophthalmologyOld, entNew, entOld, paediatricsNew, paediatricsOld, ogNew, ogOld, orthopaedicsNew, orthopaedicsOld, dentalNew, dentalOld, casualtyNew); } workbook.write(out); GenerateDailyNewOldExcelPickingRowsSequentially.writeExcelAndcloseFiles(); System.out.println("Week " + weekNumber + " Excel generated successfully!"); System.out.println("New Row Numbers to start with:"); System.out.println("All: " + allRowNum + "\tFemale: " + femaleRowNum + "\tChild: " + childRowNum); System.out.println("New CrNo. to start with: " + crNo); } catch (Exception e) { System.err.println("Error reading target!"); e.printStackTrace(); } }