List of usage examples for org.apache.poi.ss.usermodel Workbook getNumberOfSheets
int getNumberOfSheets();
From source file:com.swordlord.gozer.components.csv.GCsvList.java
License:Open Source License
@Override public void renderToWorkbook(Workbook wb) { Sheet sheet = null;//ww w.jav a 2s.co m String caption = _obList.getCaption(); if (caption == null) { sheet = wb.createSheet(); } else { if (wb.getSheet(caption) == null) { sheet = wb.createSheet(caption); } else { sheet = wb.createSheet(MessageFormat.format("{0} {1}", caption, wb.getNumberOfSheets())); } } createTableColumn(sheet); createRows(sheet); }
From source file:com.ucuenca.dao.BaseExcelDao.java
/** * This method gets sheets of file excel * * @param workbook/*w ww . ja v a 2 s. c o m*/ * @Author pablo and adrian * @return list table */ public ArrayList<Table> getSheet(Workbook workbook) { int numberOfSheets = workbook.getNumberOfSheets(); ArrayList<Table> listTable = new ArrayList<Table>(); for (int i = 0; i < numberOfSheets; i++) { Table table = new Table_Excel(); Sheet sheet = workbook.getSheetAt(i); table.setTitle(sheet.getSheetName()); getColumn(sheet); listTable.add(table); } return listTable; }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
private static Sheet createNewSheet(Workbook workbook) { int idx = workbook.getNumberOfSheets() + 1; String sheetname = "Sheet" + idx; while (workbook.getSheet(sheetname) != null) { idx++;/*from w w w . java 2s .c o m*/ sheetname = "Sheet" + idx; } return workbook.createSheet(sheetname); }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java
License:Open Source License
/** * Returns the POI index of the first visible sheet (not hidden or very * hidden). If no sheets are visible, returns 0. This is not be possible at * least in Excel, but unfortunately POI allows it. * //ww w . j a v a 2 s . co m * @param workbook * Workbook to get the sheets from * @return Index of the first visible sheet, 0-based */ public static int getFirstVisibleSheetPOIIndex(Workbook workbook) { for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (!(workbook.isSheetHidden(i) && workbook.isSheetVeryHidden(i))) { return i; } } return 0; }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java
License:Open Source License
/** * Returns the number of visible sheets (not hidden or very hidden) in the * given Workbook.//from w w w.j ava2 s . c o m * * @param workbook * Workbook to get the sheets from * @return Number of visible sheets */ public static int getNumberOfVisibleSheets(Workbook workbook) { int result = 0; for (int i = 0; i < workbook.getNumberOfSheets(); i++) { if (!(workbook.isSheetHidden(i) || workbook.isSheetVeryHidden(i))) { result++; } } return result; }
From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java
@Override public List<String> getSheets(MultipartFile file) throws FileNotFoundException, InvalidFormatException, IOException { xlsxFile = File.createTempFile("fichier", "xslx"); file.transferTo(xlsxFile);// w w w. j a va 2 s .co m FileInputStream inp = new FileInputStream(xlsxFile); Workbook workbook = WorkbookFactory.create(inp); List<String> list = new ArrayList<>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { list.add(workbook.getSheetName(i)); } return list; }
From source file:com.wantdo.stat.excel.poi_src.formula.CheckFunctionsSupported.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length < 1) { System.err.println("Use:"); System.err.println(" CheckFunctionsSupported <filename>"); return;//from w w w . j a v a 2 s . co m } Workbook wb = WorkbookFactory.create(new File(args[0])); CheckFunctionsSupported check = new CheckFunctionsSupported(wb); // Fetch all the problems List<FormulaEvaluationProblems> problems = new ArrayList<FormulaEvaluationProblems>(); for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { problems.add(check.getEvaluationProblems(sn)); } // Produce an overall summary Set<String> unsupportedFunctions = new TreeSet<String>(); for (FormulaEvaluationProblems p : problems) { unsupportedFunctions.addAll(p.unsupportedFunctions); } if (unsupportedFunctions.isEmpty()) { System.out.println("There are no unsupported formula functions used"); } else { System.out.println("Unsupported formula functions:"); for (String function : unsupportedFunctions) { System.out.println(" " + function); } System.out.println("Total unsupported functions = " + unsupportedFunctions.size()); } // Report sheet by sheet for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) { String sheetName = wb.getSheetName(sn); FormulaEvaluationProblems probs = problems.get(sn); System.out.println(); System.out.println("Sheet = " + sheetName); if (probs.unevaluatableCells.isEmpty()) { System.out.println(" All cells evaluated without error"); } else { for (CellReference cr : probs.unevaluatableCells.keySet()) { System.out.println( " " + cr.formatAsString() + " - " + probs.unevaluatableCells.get(cr).toString()); } } } }
From source file:com.yyl.common.utils.excel.ExcelTools.java
/** * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel??? * @param inputStream ?urlurlinput?/* w w w . ja v a 2 s .c o m*/ * @param FileName ???????excel * @return Map HashMapExcelsheet?sheetkeysheet?json?value * @throws IOException */ public static Map<String, String> excel2jsonWithHeaders(InputStream inputStream, String FileName) throws IOException { System.out.println("excel2json...."); // map Map<String, String> excelMap = new LinkedHashMap<>(); // Excel??Excel CellStyle cellStyle; // ?Excel? Workbook wb; // 2007??Workbook?CellStyle if (FileName.endsWith("xlsx")) { System.out.println("2007? xlsx"); wb = new XSSFWorkbook(inputStream); XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } else { System.out.println("2007 xls"); POIFSFileSystem fs = new POIFSFileSystem(inputStream); wb = new HSSFWorkbook(fs); HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } // sheet int sheetsCounts = wb.getNumberOfSheets(); // ???sheet for (int i = 0; i < sheetsCounts; i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("" + i + "sheet:" + sheet.toString()); // sheetList List list = new LinkedList(); // jsonkey String[] cellNames; // ?key Row fisrtRow = sheet.getRow(0); // sheet if (null == fisrtRow) { continue; } // int curCellNum = fisrtRow.getLastCellNum(); System.out.println("" + curCellNum); // ??? cellNames = new String[curCellNum]; // ????JSONkey for (int m = 0; m < curCellNum; m++) { Cell cell = fisrtRow.getCell(m); // ? cell.setCellStyle(cellStyle); cell.setCellType(Cell.CELL_TYPE_STRING); // ? cellNames[m] = getCellValue(cell); } for (String s : cellNames) { System.out.print("" + i + " sheet " + s + ","); } System.out.println(); // ??? int rowNum = sheet.getLastRowNum(); System.out.println(" " + rowNum + " "); for (int j = 1; j < rowNum; j++) { // ?Map LinkedHashMap rowMap = new LinkedHashMap(); // ?? Row row = sheet.getRow(j); int cellNum = row.getLastCellNum(); // ??? for (int k = 0; k < cellNum; k++) { Cell cell = row.getCell(k); cell.setCellStyle(cellStyle); cell.setCellType(Cell.CELL_TYPE_STRING); // ??? rowMap.put(cellNames[k], getCellValue(cell)); } // ??List list.add(rowMap); } // sheet??keyListjson?Value excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list)); } System.out.println("excel2json?...."); return excelMap; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
/** * ?ApachePOIAPI??Excel???List?ListJson??LinkedExcel??? * @param inputStream ?urlurlinput?//from w w w . jav a 2 s. c o m * @param FileName ???????excel * @param headers list,String-->Arrays.asList(); * @return Map HashMapExcelsheet?sheetkeysheet?json?value * @throws IOException */ public static Map<String, String> excel2json(InputStream inputStream, String fileName, List<String> headers) throws IOException { System.out.println("excel2json...."); // map Map<String, String> excelMap = new LinkedHashMap<>(); // Excel??Excel CellStyle cellStyle; // ?Excel? Workbook wb; // 2007??Workbook?CellStyle if (fileName.endsWith("xlsx")) { System.out.println("2007? xlsx"); wb = new XSSFWorkbook(inputStream); XSSFDataFormat dataFormat = (XSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } else { System.out.println("2007 xls"); POIFSFileSystem fs = new POIFSFileSystem(inputStream); wb = new HSSFWorkbook(fs); HSSFDataFormat dataFormat = (HSSFDataFormat) wb.createDataFormat(); cellStyle = wb.createCellStyle(); // Excel? cellStyle.setDataFormat(dataFormat.getFormat("@")); } // sheet int sheetsCounts = wb.getNumberOfSheets(); // ???sheet for (int i = 0; i < sheetsCounts; i++) { Sheet sheet = wb.getSheetAt(i); System.out.println("" + i + "sheet:" + sheet.toString()); // sheetList List list = new LinkedList(); // ?key Row fisrtRow = sheet.getRow(0); // sheet if (null == fisrtRow) { continue; } // int curCellNum = fisrtRow.getLastCellNum(); System.out.println("" + curCellNum); // ??? int rowNum = sheet.getLastRowNum(); System.out.println(" " + rowNum + " "); for (int j = 1; j < rowNum; j++) { // ?Map LinkedHashMap rowMap = new LinkedHashMap(); // ?? Row row = sheet.getRow(j); int cellNum = row.getLastCellNum(); // ??? for (int k = 0; k < cellNum; k++) { Cell cell = row.getCell(k); // ??? rowMap.put(headers.get(k), getCellValue(cell)); } // ??List list.add(rowMap); } // sheet??keyListjson?Value excelMap.put(sheet.getSheetName(), JacksonUtil.bean2Json(list)); } System.out.println("excel2json?...."); return excelMap; }
From source file:controller.VisitasController.java
public void autoSizeColumns(Workbook workbook) { int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getPhysicalNumberOfRows() > 0) { Row row = sheet.getRow(8);/*from www. ja va 2s .c o m*/ Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int columnIndex = cell.getColumnIndex(); sheet.autoSizeColumn(columnIndex); } } } }