List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetName
String getSheetName(int sheet);
From source file:javaapp.SimpleExcelReaderExample.java
public static ArrayList<String> parseReport(int sno, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheetAt(sno); String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Number ---> " + sno + "---> Name : " + sname + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();//from w w w. ja va2 s. c om Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps+"|"+dval+"|"+cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:kaflib.utils.FileUtils.java
License:Open Source License
/** * Reads a spreadsheet to a set of matrices (one per worksheet). * @param file/* w ww. jav a2s . co m*/ * @return * @throws Exception */ public static Map<String, Matrix<String>> readXLSX(final File file, final boolean columnTitles) throws Exception { Map<String, Matrix<String>> matrices = new HashMap<String, Matrix<String>>(); Workbook workbook = null; workbook = new XSSFWorkbook(new FileInputStream(file)); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { String name = workbook.getSheetName(i); Sheet sheet = workbook.getSheetAt(i); if (sheet == null) { continue; } Matrix<String> matrix = new Matrix<String>(); int start = 0; if (columnTitles) { Row row = sheet.getRow(0); if (row != null) { List<String> labels = new ArrayList<String>(); for (int k = 0; k < row.getLastCellNum(); k++) { labels.add(row.getCell(k).toString()); } matrix.setColumnLabels(labels); } start = 1; } for (int j = start; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row == null) { continue; } for (int k = 0; k <= row.getLastCellNum(); k++) { Cell cell = row.getCell(k); if (cell != null) { matrix.set(j - start, k, cell.toString()); } } } matrices.put(name, matrix); } workbook.close(); return matrices; }
From source file:net.pcal.sqlsheet.XlsStatement.java
License:Apache License
private static Sheet getSheetNamed(Workbook wb, String name) throws SQLException { if (name == null) throw new IllegalArgumentException(); name = name.trim();/*w ww . ja v a 2 s.co m*/ String allSheetNames = ""; int count = wb.getNumberOfSheets(); for (int i = 0; i < count; i++) { String sheetName = wb.getSheetName(i); allSheetNames += sheetName + ","; if (sheetName == null) continue; if (sheetName.equalsIgnoreCase(name) || ("\"" + sheetName + "\"").equalsIgnoreCase(name)) { return wb.getSheetAt(i); } } String message = "No sheet named '" + name; if (count == 0) { message += " can be found. Are you sure of the Excel file path ?"; } else { if (allSheetNames.length() > 2) { allSheetNames = allSheetNames.substring(0, allSheetNames.length() - 1); } message += ". Only the following " + count + " sheets can be found (" + allSheetNames + ")"; } throw new SQLException(message); }
From source file:org.alanwilliamson.openbd.plugin.spreadsheet.functions.SpreadsheetInfo.java
License:Open Source License
public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException { cfSpreadSheetData spreadsheet = null; spreadsheet = (cfSpreadSheetData) parameters.get(0); Workbook workbook = spreadsheet.getWorkBook(); cfStructData s = new cfStructData(); /*/*from w w w . ja v a2 s . c om*/ * Sheet details */ s.setData("sheets", new cfNumberData(workbook.getNumberOfSheets())); cfArrayData sheetArr = cfArrayData.createArray(1); for (int x = 0; x < workbook.getNumberOfSheets(); x++) { String name = workbook.getSheetName(x); if (name == null) name = ""; sheetArr.addElement(new cfStringData(name)); } s.setData("sheetnames", sheetArr); /* * Workbook type */ if (workbook instanceof XSSFWorkbook) { s.setData("spreadsheettype", new cfStringData("xlsx")); } else { s.setData("spreadsheettype", new cfStringData("xls")); } /* * XSSFWorkbook */ if (workbook instanceof XSSFWorkbook) { XSSFWorkbook xSSFWorkbook = (XSSFWorkbook) workbook; CoreProperties cP = xSSFWorkbook.getProperties().getCoreProperties(); s.setData("category", new cfStringData(cP.getCategory())); s.setData("subject", new cfStringData(cP.getSubject())); s.setData("title", new cfStringData(cP.getTitle())); s.setData("revision", new cfStringData(cP.getRevision())); s.setData("author", new cfStringData(cP.getCreator())); s.setData("description", new cfStringData(cP.getDescription())); if (cP.getLastPrinted() != null) s.setData("lastprinted", new cfDateData(cP.getLastPrinted())); if (cP.getModified() != null) s.setData("lastsaved", new cfDateData(cP.getModified())); if (cP.getCreated() != null) s.setData("creationdate", new cfDateData(cP.getCreated())); } else { HSSFWorkbook hSSFWorkbook = (HSSFWorkbook) workbook; DocumentSummaryInformation dSummary = hSSFWorkbook.getDocumentSummaryInformation(); if (dSummary == null) { s.setData("category", cfStringData.EMPTY_STRING); s.setData("company", cfStringData.EMPTY_STRING); s.setData("manager", cfStringData.EMPTY_STRING); } else { s.setData("category", new cfStringData(dSummary.getCategory())); s.setData("company", new cfStringData(dSummary.getCompany())); s.setData("manager", new cfStringData(dSummary.getManager())); } SummaryInformation sInformation = hSSFWorkbook.getSummaryInformation(); if (sInformation == null) { s.setData("author", cfStringData.EMPTY_STRING); s.setData("comments", cfStringData.EMPTY_STRING); s.setData("keywords", cfStringData.EMPTY_STRING); s.setData("lastauthor", cfStringData.EMPTY_STRING); s.setData("title", cfStringData.EMPTY_STRING); s.setData("subject", cfStringData.EMPTY_STRING); } else { s.setData("author", new cfStringData(sInformation.getAuthor())); s.setData("comments", new cfStringData(sInformation.getComments())); s.setData("keywords", new cfStringData(sInformation.getKeywords())); s.setData("lastauthor", new cfStringData(sInformation.getLastAuthor())); s.setData("title", new cfStringData(sInformation.getTitle())); s.setData("subject", new cfStringData(sInformation.getSubject())); if (sInformation.getCreateDateTime() != null) s.setData("creationdate", new cfDateData(sInformation.getCreateDateTime())); if (sInformation.getLastSaveDateTime() != null) s.setData("lastsaved", new cfDateData(sInformation.getLastSaveDateTime())); if (sInformation.getLastPrinted() != null) s.setData("lastprinted", new cfDateData(sInformation.getLastPrinted())); } } return s; }
From source file:org.bbreak.excella.core.BookController.java
License:Open Source License
/** * /*from w ww . ja v a2 s. c o m*/ * * @param workbook ?? */ public BookController(Workbook workbook) { this.workbook = workbook; // ??? int numOfSheets = workbook.getNumberOfSheets(); for (int sheetCnt = 0; sheetCnt < numOfSheets; sheetCnt++) { String sheetName = workbook.getSheetName(sheetCnt); sheetNames.add(sheetName); } }
From source file:org.bbreak.excella.core.BookControllerTest.java
License:Open Source License
@Test public void testBookController() throws IOException, ParseException, ExportException { Workbook workbook = getWorkbook(); String filePath = getFilepath(); BookController controller = new BookController(filePath); workbook = controller.getBook();//from ww w .j av a 2 s.c o m // =============================================== // getSheetNames() // =============================================== List<String> sheetNames = controller.getSheetNames(); String sheetName1 = sheetNames.get(0); String sheetName2 = sheetNames.get(1); controller.setErrorHandler(new DebugErrorHandler()); // =============================================== // getErrorHandler() // =============================================== LogFactory.getLog(BookControllerTest.class).info("====ErrorHandler:" + controller.getErrorHandler()); // =============================================== // addTagParser( String sheetName, TagParser<?> parser) // =============================================== controller.addTagParser(sheetName1, new MapParser("@Map")); // =============================================== // addSheetParseListener( SheetParseListener listener) // =============================================== controller.addSheetParseListener(new TestListener()); // =============================================== // addSheetParseListener( String sheetName, SheetParseListener listener) // =============================================== controller.addSheetParseListener(sheetName2, new TestListener()); // =============================================== // addSheetExporter( String sheetName, SheetExporter exporter) // =============================================== controller.addSheetExporter(sheetName1, new org.bbreak.excella.core.exporter.sheet.ConsoleExporter()); // =============================================== // parseSheet( String sheetName) // =============================================== controller.parseSheet(workbook.getSheetName(0)); // BookData bookData = BookController.getBookData(); // Workbook newWorkbook = BookController.getBook(); controller = new BookController(workbook); // =============================================== // addTagParser( TagParser<?> parser) // =============================================== controller.addTagParser(new ListParser("@List")); // =============================================== // addSheetExporter( SheetExporter exporter) // =============================================== controller.addSheetExporter(new org.bbreak.excella.core.exporter.sheet.ConsoleExporter()); // =============================================== // addBookExporter( BookExporter exporter) // =============================================== controller.addBookExporter(new ConsoleExporter()); // =============================================== // getExporter() // =============================================== List<BookExporter> exporterList = controller.getExporter(); for (BookExporter exporter : exporterList) { LogFactory.getLog(BookControllerTest.class).info("====BookExporter:" + exporter); } // =============================================== // parseBook() // =============================================== controller.parseBook(); }
From source file:org.bbreak.excella.core.util.PoiUtil.java
License:Open Source License
/** * ????//from w w w. ja v a 2 s .co m * * @param sheet * @return ?? */ public static String getSheetName(Sheet sheet) { Workbook workbook = sheet.getWorkbook(); int sheetIndex = workbook.getSheetIndex(sheet); return workbook.getSheetName(sheetIndex); }
From source file:org.bbreak.excella.reports.exporter.ReportBookExporter.java
License:Open Source License
public void export(Workbook book, BookData bookdata) throws ExportException { // ()/*from w w w .j ava 2 s. com*/ SortedSet<Integer> deleteSheetIndexs = new TreeSet<Integer>(Collections.reverseOrder()); for (int i = 0; i < book.getNumberOfSheets(); i++) { String sheetName = book.getSheetName(i); if (sheetName.startsWith(PoiUtil.TMP_SHEET_NAME)) { deleteSheetIndexs.add(i); } } for (int index : deleteSheetIndexs) { book.removeSheetAt(index); } if (configuration != null) { // output(book, bookdata, configuration); } }
From source file:org.bbreak.excella.reports.exporter.ReportBookExporterTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.exporter.ReportBookExporter#export(org.apache.poi.ss.usermodel.Workbook, org.bbreak.excella.core.BookData)} ???? *//*from w w w . ja v a 2 s . c o m*/ @Test public void testExport() { Workbook book = getWorkbook(); ReportBookExporter exporter = new ReportBookExporter() { @Override public String getExtention() { return null; } @Override public String getFormatType() { return null; } @Override public void output(Workbook book, BookData bookdata, ConvertConfiguration configuration) throws ExportException { } }; exporter.setConfiguration(new ConvertConfiguration("")); try { exporter.export(book, null); } catch (ExportException e) { e.printStackTrace(); fail(e.toString()); } assertEquals(4, book.getNumberOfSheets()); for (int i = 0; i < book.getNumberOfSheets(); i++) { String sheetName = book.getSheetName(i); assertFalse(sheetName.startsWith(PoiUtil.TMP_SHEET_NAME)); } }
From source file:org.bbreak.excella.reports.listener.BreakAdapterTest.java
License:Open Source License
/** * {@link org.bbreak.excella.reports.listener.BreakAdapter#postParse(org.apache.poi.ss.usermodel.Sheet, org.bbreak.excella.core.SheetParser, org.bbreak.excella.core.SheetData)} ???? *///from www . jav a 2 s . c o m @Test public void testPostParse() { Workbook workbook = getWorkbook(); BreakAdapter adapter = new BreakAdapter(); SheetParser sheetParser = new SheetParser(); List<ReportsTagParser<?>> reportParsers = new ArrayList<ReportsTagParser<?>>( ReportCreateHelper.createDefaultParsers().values()); for (ReportsTagParser<?> parser : reportParsers) { sheetParser.addTagParser(parser); } Sheet sheet = workbook.getSheetAt(0); try { adapter.postParse(sheet, sheetParser, null); } catch (ParseException e) { e.printStackTrace(); fail(); } checkSheet(workbook.getSheetName(0), sheet, true); }