List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook(InputStream s) throws IOException
From source file:HSSFReadWrite.java
License:Apache License
/** * creates an {@link HSSFWorkbook} the specified OS filename. *///from w w w .j a va2 s.c o m public static HSSFWorkbook readFile(String filename) throws IOException { return new HSSFWorkbook(new FileInputStream(filename)); }
From source file:HSSFReadWrite.java
License:Apache License
public static HSSFWorkbook readFile(File file) throws IOException { return new HSSFWorkbook(new FileInputStream(file)); }
From source file:Main3.java
/** * @param args the command line arguments *///from w ww .ja 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 {//from ww w . j a 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:adminDown.java
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*from ww w . j av a 2s. c o m*/ FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls")); HSSFWorkbook workbook = new HSSFWorkbook(file); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=CRSTrainingDetails.xls"); workbook.write(response.getOutputStream()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:NewMain.java
/** * @param args the command line arguments *//* w w w . j a va2 s . c om*/ 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 *///from w w w . ja v a 2 s .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 ww w. j av a2 s . co 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:TestUtil.java
License:BSD License
public void extractToExcel(File _xcelFile, Document doc) { try {/*from w ww . j a v a2s .c om*/ HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(_xcelFile)); NodeList cdeIds = doc.getElementsByTagName("ns1:Question"); List<Node[]> cdeNodes = new ArrayList<Node[]>(); List<Node[]> decNodes = new ArrayList<Node[]>(); List<Node[]> vdNodes = new ArrayList<Node[]>(); List<Node[]> cdNodes = new ArrayList<Node[]>(); List<Node[]> conNodes = new ArrayList<Node[]>(); for (int i = 0; i < cdeIds.getLength(); i++) { Node node = cdeIds.item(i); NodeList quesNodes = node.getChildNodes(); Node[] cdeNodeArr = null; Node[] decNodeArr = null; Node[] vdNodeArr = null; Node[] cdNodeArr = null; Node[] conNodeArr = null; for (int j = 0; j < quesNodes.getLength(); j++) { Node quesNode = quesNodes.item(j); String quesNodeName = quesNode.getNodeName(); if (quesNodeName.equalsIgnoreCase("ns1:cdeId")) { if (cdeNodeArr == null) { cdeNodeArr = new Node[4]; cdeNodes.add(cdeNodeArr); } cdeNodeArr[0] = quesNode; } else if (quesNodeName.equalsIgnoreCase("ns1:cdeLongName") && cdeNodeArr != null) { cdeNodeArr[1] = quesNode; } else if (quesNodeName.equalsIgnoreCase("ns1:decId")) { if (decNodeArr == null) { decNodeArr = new Node[2]; decNodes.add(decNodeArr); } decNodeArr[0] = quesNode; if (cdeNodeArr == null) { cdeNodeArr = new Node[4]; cdeNodes.add(cdeNodeArr); } cdeNodeArr[2] = quesNode; } else if (quesNodeName.equalsIgnoreCase("ns1:vdId")) { if (vdNodeArr == null) { vdNodeArr = new Node[4]; vdNodes.add(vdNodeArr); } vdNodeArr[0] = quesNode; if (cdeNodeArr == null) { cdeNodeArr = new Node[4]; cdeNodes.add(cdeNodeArr); } cdeNodeArr[3] = quesNode; } else if (quesNodeName.equalsIgnoreCase("ns1:dataType") && vdNodeArr != null) { vdNodeArr[1] = quesNode; } else if (quesNodeName.equalsIgnoreCase("ns1:enumerated") && vdNodeArr != null) { vdNodeArr[2] = quesNode; } else if (quesNodeName.equalsIgnoreCase("ns1:decConceptualDomainId") || quesNodeName.equalsIgnoreCase("ns1:vdConceptualDomainId")) { cdNodeArr = new Node[1]; cdNodes.add(cdNodeArr); cdNodeArr[0] = quesNode; if (quesNodeName.equalsIgnoreCase("ns1:decConceptualDomainId")) { if (decNodeArr == null) { decNodeArr = new Node[2]; decNodes.add(decNodeArr); } decNodeArr[1] = quesNode; } else if (quesNodeName.equalsIgnoreCase("ns1:vdConceptualDomainId")) { if (vdNodeArr == null) { vdNodeArr = new Node[4]; vdNodes.add(vdNodeArr); } vdNodeArr[3] = quesNode; } } else if (quesNodeName.equalsIgnoreCase("ns1:ocPrimConcepts") || quesNodeName.equalsIgnoreCase("ns1:propPrimConcepts") || quesNodeName.equalsIgnoreCase("ns1:repTermQualConcepts") || quesNodeName.equalsIgnoreCase("ns1:repTermPrimConcepts") || quesNodeName.equalsIgnoreCase("ns1:vmConcepts")) { conNodeArr = new Node[1]; conNodes.add(conNodeArr); conNodeArr[0] = quesNode; } } } writeCon(conNodes, wb); writeCD(cdNodes, wb); writeVD(vdNodes, wb); writeDEC(decNodes, wb); writeDE(cdeNodes, wb); NodeList csNodes = doc.getElementsByTagName("ns1:cs"); if (csNodes.getLength() > 0) { writeCS(csNodes.item(0), wb); } else { throw new Exception("No CS defined!!"); } NodeList csiNodes = doc.getElementsByTagName("ns1:csi"); if (csiNodes.getLength() > 0) { writeCSI(csiNodes.item(0), wb); } else { throw new Exception("No CSI defined!!"); } wb.write(new FileOutputStream(_xcelFile)); } catch (Exception e) { // TODO Auto-generated catch block 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 {//from w w w. j av a 2 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; }