List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:Main3.java
/** * @param args the command line arguments */// w w w.j a v a 2 s. c o m public static void main(String[] args) throws FileNotFoundException, IOException { HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("columA.xls")); HSSFSheet sheet = wb.getSheetAt(1); removeRow(sheet, 3); for (int i = 3; i < 4000; i++) { HSSFRow origRow = sheet.getRow(i); if (rowIsEmpty(origRow)) { System.out.println(i); //removeRow(sheet, i); } } FileOutputStream out = new FileOutputStream("okay4.xls"); wb.write(out); out.close(); }
From source file:RefDiviedMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {/*w w w. ja v a 2 s . c o m*/ db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefDiviedMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:NewMain.java
/** * @param args the command line arguments *//* w ww. ja va 2s. c o m*/ public static void main(String[] args) throws FileNotFoundException, IOException { HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("workbook.xls")); HSSFSheet sheet = wb.getSheetAt(0); int written = 2; //2997 for (int r = 2; r < 2997; r += 6) { HSSFCell cell1 = sheet.getRow(r).getCell(1); HSSFCell cell2 = sheet.getRow(r + 1).getCell(1); HSSFCell cell3 = sheet.getRow(r + 2).getCell(1); Contact c = new Contact(cell1, cell2, cell3); System.out.println(c); HSSFRow row = sheet.getRow(written); if (row == null) row = sheet.createRow(written); HSSFCell cellComp = row.createCell(4); cellComp.setCellValue(c.getCompany()); HSSFCell cellAdd = row.createCell(5); cellAdd.setCellValue(c.getAddress()); HSSFCell cellCity = row.createCell(6); cellCity.setCellValue(c.getCity()); HSSFCell cellState = row.createCell(7); cellState.setCellValue(c.getState()); HSSFCell cellZip = row.createCell(8); cellZip.setCellValue(c.getZip()); HSSFCell cellPhone = row.createCell(9); cellPhone.setCellValue(c.getPhone()); HSSFCell cellSite = row.createCell(10); cellSite.setCellValue(c.getSite()); written++; } FileOutputStream out = new FileOutputStream("okay.xls"); wb.write(out); out.close(); }
From source file:Main2.java
/** * @param args the command line arguments *//* w w w .j a va2s. c o m*/ public static void main(String[] args) throws FileNotFoundException, IOException { HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("workbook2.xls")); HSSFSheet sheet = wb.getSheetAt(1); //Advanced Imaging of Port Charlotte 2625 Tamiami Trail, Unit 1 Port Charlotte, FL 33952 941-235-4646 for (int r = 0; r < 3815; r++) { HSSFRow row = sheet.getRow(r); if (row == null) continue; HSSFCell cell = row.getCell(0); if (cell == null) continue; String parse = cell.getStringCellValue().trim(); if (parse.length() == 0) continue; //Check if the last character is a digit if (Character.isDigit(parse.charAt(parse.length() - 1)) || parse.charAt(parse.length() - 1) == ')') { int stop = parse.length() - 1; char stopChar = parse.charAt(stop); while (stopChar != ' ') { stop--; if (stop == -1) break; stopChar = parse.charAt(stop); } String number = parse.substring(stop + 1); //If it's a phone number if (number.length() > 11) { //HSSFCell cellPhone = row.createCell(8); //cellPhone.setCellValue(number); //Now search for the zip int stopZip = stop - 1; char stopCharZip = parse.charAt(stopZip); if (Character.isDigit(stopCharZip)) { //Cycle through the string backwards until you find a space while (stopCharZip != ' ') { stopZip--; if (stopZip == -1) continue; stopCharZip = parse.charAt(stopZip); } //Write down the zip in the correct spot String zipNumber = parse.substring(stopZip + 1, stop); HSSFCell cellZip = row.createCell(6); cellZip.setCellValue(zipNumber); System.out.println((cellZip == null) + " " + zipNumber); } String state = parse.substring(stopZip - 2, stopZip); //HSSFCell cellState = row.createCell(6); //cellState.setCellValue(state); } } //Find the string before the first dash int firstDash = parse.indexOf('-'); if (firstDash != -1) { String preDash = parse.substring(0, firstDash).trim(); boolean noDigits = true; for (int i = 0; i < 10; i++) { if (preDash.contains(Integer.toString(i))) { noDigits = false; } } if (noDigits && preDash.contains(" ")) { HSSFCell cellComp = row.createCell(2); //cellComp.setCellValue(preDash); } } else { int stopNum = 0; char stopCharNum = parse.charAt(stopNum); while (stopNum < parse.length() - 1 && !Character.isDigit(stopCharNum)) { stopNum++; stopCharNum = parse.charAt(stopNum); } String possTitle = parse.substring(0, stopNum); if (!possTitle.contains(",") && possTitle.length() >= 8) { HSSFCell cellComp = row.createCell(2); //cellComp.setCellValue(possTitle); } } } FileOutputStream out = new FileOutputStream("okay3.xls"); wb.write(out); out.close(); // 2997 // for (int r = 2; r < 2997 ; r += 6) // { // HSSFCell cell1 = sheet.getRow(r).getCell(1); // HSSFCell cell2 = sheet.getRow(r + 1).getCell(1); // HSSFCell cell3 = sheet.getRow(r + 2).getCell(1); // // Contact c = new Contact(cell1, cell2, cell3); // System.out.println(c); // // HSSFRow row = sheet.getRow(written); // if (row == null) // row = sheet.createRow(written); // // HSSFCell cellComp = row.createCell(4); // cellComp.setCellValue(c.getCompany()); // // HSSFCell cellAdd = row.createCell(5); // cellAdd.setCellValue(c.getAddress()); // // HSSFCell cellCity = row.createCell(6); // cellCity.setCellValue(c.getCity()); // // HSSFCell cellState = row.createCell(7); // cellState.setCellValue(c.getState()); // // HSSFCell cellZip = row.createCell(8); // cellZip.setCellValue(c.getZip()); // // HSSFCell cellPhone = row.createCell(9); // cellPhone.setCellValue(c.getPhone()); // // HSSFCell cellSite = row.createCell(10); // cellSite.setCellValue(c.getSite()); // // written++; // // } // // FileOutputStream out = new FileOutputStream("okay.xls"); // wb.write(out); // out.close(); }
From source file:ImporteerExcelsheet.java
public void importeerExcelsheet(String filePath) { try {//from w w w . j av a 2 s . c o m FileInputStream file = new FileInputStream(new File(filePath)); if (filePath.toLowerCase().indexOf(xlsx.toLowerCase()) != -1) { XSSFWorkbook wbXlsx = new XSSFWorkbook(file); sheet = wbXlsx.getSheetAt(0); Row row = sheet.getRow(3); Cell cell = row.getCell(1); try { // convert String dd-mm-yyyy naar Date yyyy-mm-dd String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet.iterator(); } else { HSSFWorkbook wbXls = new HSSFWorkbook(file); sheet1 = wbXls.getSheetAt(0); Row row = sheet1.getRow(3); Cell cell = row.getCell(1); // convert String dd-mm-yyyy naar Date yyyy-mm-dd try { String excelDatum1 = cell.getStringCellValue(); Date date = new SimpleDateFormat("dd-MM-yyyy").parse(excelDatum1); this.excelDatum = this.formatter.format(date); } catch (Exception e) { double date1 = cell.getNumericCellValue(); //convert excel double naar datum Date date2 = DateUtil.getJavaDate((double) date1); this.excelDatum = new SimpleDateFormat("yyyy-MM-dd").format(date2); } //Iterate through each rows one by one rowIterator = sheet1.iterator(); } //Iterate through each rows one by one while (rowIterator.hasNext()) { Row row = rowIterator.next(); //skip first 5 rows if (row.getRowNum() == 0 || row.getRowNum() == 1 || row.getRowNum() == 2 || row.getRowNum() == 3 || row.getRowNum() == 4) { continue; } //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //Check the cell type and format accordingly switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_BLANK: // System.out.print(cell.getStringCellValue() + "\t"); break; case Cell.CELL_TYPE_FORMULA: // System.out.print(cell.getStringCellValue() + "\t"); break; } } // Tabel client int kaartnummer = (int) row.getCell(0).getNumericCellValue(); String naam = row.getCell(1).getStringCellValue(); String naamPartner = row.getCell(2).getStringCellValue(); String telefoonnummer = row.getCell(3).getStringCellValue(); String email = row.getCell(4).getStringCellValue(); String mobiel = row.getCell(5).getStringCellValue(); int aantalPersonen = (int) row.getCell(6).getNumericCellValue(); int aantalPersonenInDeNorm = (int) row.getCell(7).getNumericCellValue(); double gebruikInMaanden = (double) row.getCell(8).getNumericCellValue(); String idSoort = row.getCell(9).getStringCellValue(); //convert excel double naar datum double datumUitgifteId1 = row.getCell(10).getNumericCellValue(); if (datumUitgifteId1 == 0.0) { this.datumUitgifteId = null; } else { Date datumUitgifteId2 = DateUtil.getJavaDate((double) datumUitgifteId1); this.datumUitgifteId = new SimpleDateFormat("yyyy-MM-dd").format(datumUitgifteId2); } //////////////////////////////////////////////////////////////////////////////////// String idNummer = row.getCell(11).getStringCellValue(); String plaatsUitgifteId = row.getCell(12).getStringCellValue(); String adres = row.getCell(13).getStringCellValue(); String postcode = row.getCell(14).getStringCellValue(); String plaats = row.getCell(15).getStringCellValue(); String status = row.getCell(16).getStringCellValue(); // Tabel intake // kaartnummer gaat hier ook in de query String intaker = row.getCell(17).getStringCellValue(); double intakeDatum1 = row.getCell(18).getNumericCellValue(); //convert excel double naar datum Date intakeDatum2 = DateUtil.getJavaDate((double) intakeDatum1); String intakeDatum = new SimpleDateFormat("yyyy-MM-dd").format(intakeDatum2); ///////////////////////////////////////////////////////////////////////// double startDatumUitgifte1 = row.getCell(19).getNumericCellValue(); //convert excel double naar datum Date startDatumUitgifte2 = DateUtil.getJavaDate((double) startDatumUitgifte1); String startDatumUitgifte = new SimpleDateFormat("yyyy-MM-dd").format(startDatumUitgifte2); ////////////////////////////////////////////////////////////////////////////////// double datumHerintake1 = row.getCell(20).getNumericCellValue(); //convert excel double naar datum Date datumHerintake2 = DateUtil.getJavaDate((double) datumHerintake1); String datumHerintake = new SimpleDateFormat("yyyy-MM-dd").format(datumHerintake2); ///////////////////////////////////////////////////////////////////////////////// // Tabel Stopt double datumStopzetting1 = row.getCell(21).getNumericCellValue(); //convert excel numbers naar datum Date datumStopzetting2 = DateUtil.getJavaDate((double) datumStopzetting1); String datumStopzetting = new SimpleDateFormat("yyyy-MM-dd").format(datumStopzetting2); ////////////////////////////////////////////////////////////////////////////////////// String redenStopzetting = row.getCell(22).getStringCellValue(); // Tabel verwijzer String verwijzerNaam = row.getCell(23).getStringCellValue(); String verwijzersDoorContactpersoon1 = row.getCell(24).getStringCellValue(); String verwijzersDoorContactpersoon = verwijzersDoorContactpersoon1.toLowerCase(); String verwijzersDoorTelefoonnummer = row.getCell(25).getStringCellValue(); String verwijzersDoorEmail = row.getCell(26).getStringCellValue(); String verwijzersNaar = row.getCell(27).getStringCellValue(); String verwijzersNaarContactpersoon = row.getCell(28).getStringCellValue(); String verwijzersNaarTelefoonnummer = row.getCell(29).getStringCellValue(); String verwijzersNaarEmail = row.getCell(30).getStringCellValue(); // Uitgiftepunt gaat in de tabel intake String uitgiftepunt = row.getCell(31).getStringCellValue(); // PakketSoort gaat in de tabel client String pakketSoort = row.getCell(32).getStringCellValue(); // Query's om de excelsheet in de database te krijgen SQLExcelSheetInsert excelSQL = new SQLExcelSheetInsert(); SQLget getSQL = new SQLget(); // Losse query uit de tabel om bepaalde gegevens te krijgen om te controleren of die al bestaat of niet int Verwijzer = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); int checkKaartnr = getSQL.getCheckKaartnummer(kaartnummer); int checkUitgiftepunt = getSQL.getUitgiftepunt(uitgiftepunt); if (Verwijzer == 0) { excelSQL.insertExcelVerwijzer(verwijzerNaam, verwijzersDoorContactpersoon, verwijzersDoorTelefoonnummer, verwijzersDoorEmail, verwijzersNaar, verwijzersNaarContactpersoon, verwijzersNaarTelefoonnummer, verwijzersNaarEmail); } if (checkUitgiftepunt == 0) { int maxVolgorde = getSQL.getCheckVolgordeLijst(); excelSQL.insertUitgiftepunt(uitgiftepunt, maxVolgorde); } int Verwijzer2 = getSQL.getVerwijzernr(verwijzerNaam, verwijzersDoorContactpersoon); /// Wanneer er geen kaartnummer is die bekend gaat die door naar insert if (checkKaartnr == 0) { // Wanneer kaartnummer alles behalve 0 is insert die if (kaartnummer > 0) { excelSQL.insertExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } else { break; } } else { excelSQL.updateExcelClient(kaartnummer, naam, naamPartner, telefoonnummer, email, mobiel, aantalPersonen, aantalPersonenInDeNorm, gebruikInMaanden, idSoort, this.datumUitgifteId, idNummer, plaatsUitgifteId, adres, postcode, plaats, status, pakketSoort, Verwijzer2); } int checkIntake = getSQL.getCheckIntake(intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer); if (checkIntake == 0) { excelSQL.insertExcelIntake(intaker, intakeDatum, startDatumUitgifte, datumHerintake, kaartnummer, uitgiftepunt); int intakeId = getSQL.getIntakeId(kaartnummer); excelSQL.insertExcelStopt(datumStopzetting, redenStopzetting, intakeId); } // Kan upgedate worden aan een knop voor een query aan status in de table voedselpakket wel of niet opgehaald String status1 = null; String checkStatus = getSQL.getStatus(kaartnummer); this.pakketAantal = getSQL.getPakketAantal(kaartnummer); int intakeId = getSQL.getIntakeId(kaartnummer); int checkPakket = getSQL.getPakket(this.excelDatum, intakeId); if (checkPakket == 0) { if (checkStatus != null) { if (checkStatus.equals("Actief")) { int intakeId2 = getSQL.getIntakeId(kaartnummer); excelSQL.insertVoedselpakket(this.excelDatum, this.pakketAantal, status1, intakeId2, uitgiftepunt); } } } } file.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:RefSouceOnlyMain.java
License:Creative Commons License
public static Element getTable(String name) { DataFormatter formatter = new DataFormatter(Locale.US); if (name == null) { DocumentBuilder db = null; try {/* ww w . j a v a2 s. c om*/ db = dbf.newDocumentBuilder(); } catch (ParserConfigurationException ex) { ta.append("\nerrors happen:\n"); ta.append(ex.getMessage() + "\n"); } doc = db.newDocument(); } if (name == null) { name = "C:\\Users\\DLiu1\\Documents\\NetBeansProjects\\Simon\\dist\\Table 1"; } String fileName = name + ".xls"; File aaa = new File(fileName); if (!aaa.exists()) { RefSouceOnlyMain .error(fileName + " doesn't exist, please copy the " + fileName + " into the same folder!"); return null; } Element tableFrame = null; try { tableFrame = doc.createElement("table"); tableFrame.setAttribute("frame", "hsides"); tableFrame.setAttribute("rules", "groups"); Element thead = doc.createElement("thead"); Element tbody = doc.createElement("tbody"); tableFrame.appendChild(thead); tableFrame.appendChild(tbody); /** Creating Input Stream**/ //InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName ); FileInputStream myInput = new FileInputStream(aaa); /** Create a POIFSFileSystem object**/ POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); /** Create a workbook using the File System**/ HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); /** Get the first sheet from workbook**/ HSSFSheet mySheet = myWorkBook.getSheetAt(0); /** We now need something to iterate through the cells.**/ Iterator rowIter = mySheet.rowIterator(); int theRow = 0; int theadRows = 1; while (rowIter.hasNext()) { theRow++; HSSFRow myRow = (HSSFRow) rowIter.next(); Iterator cellIter = myRow.cellIterator(); //Vector cellStoreVector=new Vector(); System.out.println("\nprinting " + theRow); Element tr = doc.createElement("tr"); System.out.println("\nprinting " + theRow); while (cellIter.hasNext()) { HSSFCell myCell = (HSSFCell) cellIter.next(); CellProperties cp = new CellProperties(myCell); Element td = null; int colspan = cp.getColspan(); int rowspan = cp.getRowspan(); CellReference ref = new CellReference(myCell); System.out.println( "The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(myCell)); // String myCellValue = myCell.toString(); // myCell.setCellType(Cell.CELL_TYPE_STRING); // String myCellValue = myCell.getRichStringCellValue().toString(); String myCellValue = formatter.formatCellValue(myCell); if (myCellValue != null && myCellValue.trim().endsWith(".0")) { System.out.println(myCellValue + " have 0"); myCellValue = myCellValue.replace(".0", ""); } System.out .println(myCellValue + ": colspan:" + cp.getColspan() + " rowspan:" + cp.getRowspan()); if (rowspan > 1) { if (theRow == 1) { theadRows = rowspan; } } if (theRow <= theadRows) { td = doc.createElement("th"); td.setAttribute("align", "left"); } else { td = doc.createElement("td"); td.setAttribute("align", "left"); td.setAttribute("valign", "top"); } if (colspan > 1) { td.setAttribute("colspan", colspan + ""); } if (rowspan > 1) { td.setAttribute("rowspan", rowspan + ""); } if ((colspan > 1 || rowspan > 1) && myCellValue.trim().equals("")) { continue; } Element bold = doc.createElement("bold"); tr.appendChild(td); td.appendChild(doc.createTextNode(myCellValue.trim())); // cellStoreVector.addElement(myCell); } if (theRow <= theadRows) { thead.appendChild(tr); } else { tbody.appendChild(tr); } // cellVectorHolder.addElement(cellStoreVector); } } catch (Exception e) { ta.append("\nerrors happen:\n"); ta.append(e.getMessage() + "\n"); } return tableFrame; }
From source file:AdminServlet.java
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { adminame = request.getParameter("adminname"); adpassword = request.getParameter("adminpassword"); request.setAttribute("adname", adminame); try {/*w ww .j av a2 s. co m*/ FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet worksheet = workbook.getSheetAt(0); ArrayList Rows = new ArrayList(); for (int j = worksheet.getFirstRowNum() + 1; j <= worksheet.getLastRowNum(); j++) { ArrayList row = new ArrayList(); HSSFRow row1 = worksheet.getRow(j); HSSFCell cellA1 = row1.getCell(0); String a1Val = cellA1.getStringCellValue(); row.add(a1Val); HSSFCell cellB1 = row1.getCell(1); String b1Val = cellB1.getStringCellValue(); row.add(b1Val); HSSFCell cellC1 = row1.getCell(2); String c1Val = cellC1.getStringCellValue(); row.add(c1Val); HSSFCell cellD1 = row1.getCell(3); String d1Val = cellD1.getStringCellValue(); row.add(d1Val); HSSFCell cellE1 = row1.getCell(4); String e1Val = cellE1.getStringCellValue(); row.add(e1Val); HSSFCell cellF1 = row1.getCell(5); String f1Val = cellF1.getStringCellValue(); row.add(f1Val); HSSFCell cellG1 = row1.getCell(6); String g1Val = cellG1.getStringCellValue(); row.add(g1Val); HSSFCell cellH1 = row1.getCell(7); String h1Val = cellH1.getStringCellValue(); row.add(h1Val); HSSFCell cellI1 = row1.getCell(8); String i1Val = cellI1.getStringCellValue(); row.add(i1Val); Rows.add(row); } request.setAttribute("results", Rows); if (adminame.equals("Admin") && adpassword.equals("admin")) { RequestDispatcher rd = request.getRequestDispatcher("adminHome.jsp"); rd.forward(request, response); } else { out.println("login failed"); RequestDispatcher rd = request.getRequestDispatcher("afailed.jsp"); rd.include(request, response); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:xlsxtocsv.java
static void convertToXls(File inputFile, File outputFile) { // For storing data into CSV files StringBuffer cellDData = new StringBuffer(); try {//from w ww. j av a2s .c o m FileOutputStream fos = new FileOutputStream(outputFile); // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile)); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); Cell cell; Row row; // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellDData.append(cell.getBooleanCellValue() + ","); break; case Cell.CELL_TYPE_NUMERIC: cellDData.append(cell.getNumericCellValue() + ","); break; case Cell.CELL_TYPE_STRING: cellDData.append(cell.getStringCellValue() + ","); break; case Cell.CELL_TYPE_BLANK: cellDData.append("" + ","); break; default: cellDData.append(cell + ","); } } cellDData.append("\n"); } fos.write(cellDData.toString().getBytes()); fos.close(); } catch (FileNotFoundException e) { System.err.println("Exception" + e.getMessage()); } catch (IOException e) { System.err.println("Exception" + e.getMessage()); } }
From source file:SystemPropertiesParser.java
License:Apache License
public static List<SystemProperty> parse() throws IOException { URL xls = SystemPropertiesParser.class.getResource("SystemProperties.xls"); FileInputStream fis = new FileInputStream(new File(xls.getPath())); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); int numRows = sheet.getLastRowNum(); List<SystemProperty> systemProperties = new ArrayList<>(numRows); for (int i = 1; i < numRows; i++) { HSSFRow row = sheet.getRow(i);/*from w w w .j av a 2s.c o m*/ SystemProperty.Builder builder = SystemProperty.newSystemProperty(); builder.key(row.getCell(0).getStringCellValue()); HSSFCell cell = row.getCell(1); if (cell != null) { builder.desc(cell.getStringCellValue()); } List<SystemProperty.GithubInfo> github = new ArrayList<>(); String uses = row.getCell(2).getStringCellValue(); String[] arr = uses.split(" "); for (String str : arr) { String repo = str.split("/")[0]; String path = str.substring(repo.length() + 1); SystemProperty.GithubInfo githubInfo = new SystemProperty.GithubInfo(repo, path); github.add(githubInfo); } builder.github(github); systemProperties.add(builder.build()); } fis.close(); Collections.sort(systemProperties, (o1, o2) -> o1.key.compareToIgnoreCase(o2.key)); return systemProperties; }
From source file:ambit.test.io.POItest.java
License:Open Source License
public void test1() { try {//from www. java 2 s . co m HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("data/misc/Debnath_smiles.xls")); HSSFSheet sheet = workbook.getSheetAt(0); //HSSFSheet sheet = workbook.getSheet("Sheet1"); Iterator i = sheet.rowIterator(); while (i.hasNext()) { Object o = i.next(); assertTrue(o instanceof HSSFRow); Iterator j = ((HSSFRow) o).cellIterator(); while (j.hasNext()) { Object cell = j.next(); assertTrue(cell instanceof HSSFCell); System.out.println(cell); } } //HSSFCell cell = row.getCell((short)0); } catch (IOException x) { x.printStackTrace(); fail(); } }