List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetName
@Override public String getSheetName(int sheetIndex)
From source file:org.springframework.web.servlet.view.document.ExcelTestSuite.java
License:Apache License
public void testExcel() throws Exception { AbstractExcelView excelView = new AbstractExcelView() { protected void buildExcelDocument(Map model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.createSheet(); wb.setSheetName(0, "Test Sheet"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); }/*from w ww . jav a 2 s . c o m*/ }; excelView.render(new HashMap(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); assertEquals("Test Sheet", wb.getSheetName(0)); HSSFSheet sheet = wb.getSheet("Test Sheet"); HSSFRow row = sheet.getRow(2); HSSFCell cell = row.getCell((short) 4); assertEquals("Test Value", cell.getStringCellValue()); }
From source file:org.springframework.web.servlet.view.document.ExcelViewTests.java
License:Apache License
@Test public void testExcel() throws Exception { AbstractExcelView excelView = new AbstractExcelView() { @Override/*w w w . j a v a 2s .co m*/ protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook wb, HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFSheet sheet = wb.createSheet("Test Sheet"); // test all possible permutation of row or column not existing HSSFCell cell = getCell(sheet, 2, 4); cell.setCellValue("Test Value"); cell = getCell(sheet, 2, 3); setText(cell, "Test Value"); cell = getCell(sheet, 3, 4); setText(cell, "Test Value"); cell = getCell(sheet, 2, 4); setText(cell, "Test Value"); } }; excelView.render(new HashMap<String, Object>(), request, response); POIFSFileSystem poiFs = new POIFSFileSystem(new ByteArrayInputStream(response.getContentAsByteArray())); HSSFWorkbook wb = new HSSFWorkbook(poiFs); assertEquals("Test Sheet", wb.getSheetName(0)); HSSFSheet sheet = wb.getSheet("Test Sheet"); HSSFRow row = sheet.getRow(2); HSSFCell cell = row.getCell(4); assertEquals("Test Value", cell.getStringCellValue()); }
From source file:org.unhcr.eg.odk.utilities.xlsform.excel.ExcelFileUtility.java
protected static ArrayList<String> getListOfSheets(HSSFWorkbook workbook) { ArrayList<String> listOfSheets = new ArrayList<>(); int numberOfSheet = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheet; i++) { listOfSheets.add(workbook.getSheetName(i)); }//from w w w. ja v a 2 s .c o m return listOfSheets; }
From source file:org.wandora.application.tools.extractors.excel.ExcelExtractor.java
License:Open Source License
@Override public boolean _extractTopicsFrom(URL u, TopicMap tm) throws Exception { try {/*from ww w. jav a 2 s .co m*/ HSSFWorkbook workbook = new HSSFWorkbook(u.openStream()); ExcelExtractorUI ui = new ExcelExtractorUI(); ui.open(getSheets(workbook), getExtractors()); if (ui.wasAccepted()) { String[] extractors = ui.getExtractors(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { String sheetName = workbook.getSheetName(i); AbstractExcelExtractor extractor = getExcelExtractorFor(extractors[i]); if (extractor != null) { log("Applying '" + extractor.getName() + "' to sheet '" + sheetName + "'."); extractor.setToolLogger(getDefaultLogger()); extractor.processSheet(workbook.getSheetAt(i), tm); } else { log("Leaving sheet '" + sheetName + "' unprocessed."); } } } else { log("Extraction cancelled."); } log("Ok!"); } catch (FileNotFoundException ex) { log(ex); } catch (IOException ex) { log(ex); } catch (Exception ex) { log(ex); } setState(WAIT); return true; }
From source file:org.wandora.application.tools.extractors.excel.ExcelExtractor.java
License:Open Source License
@Override public boolean _extractTopicsFrom(File f, TopicMap tm) throws Exception { try {/*from www . j a v a 2 s . c o m*/ if (f != null) { String fn = f.getAbsolutePath(); if (fn.toLowerCase().endsWith(".xls")) { HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(f)); ExcelExtractorUI ui = new ExcelExtractorUI(); ui.open(getSheets(workbook), getExtractors()); if (ui.wasAccepted()) { String[] extractors = ui.getExtractors(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { String sheetName = workbook.getSheetName(i); AbstractExcelExtractor extractor = getExcelExtractorFor(extractors[i]); if (extractor != null) { log("Applying '" + extractor.getName() + "' to sheet '" + sheetName + "'."); extractor.setToolLogger(getDefaultLogger()); extractor.processSheet(workbook.getSheetAt(i), tm); } else { log("Leaving sheet '" + sheetName + "' unprocessed."); } } } else { log("Extraction cancelled."); } } else { XSSFWorkbook workbook = new XSSFWorkbook(f.getAbsolutePath()); ExcelExtractorUI ui = new ExcelExtractorUI(); ui.open(getSheets(workbook), getExtractors()); if (ui.wasAccepted()) { String[] extractors = ui.getExtractors(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { String sheetName = workbook.getSheetName(i); AbstractExcelExtractor extractor = getExcelExtractorFor(extractors[i]); if (extractor != null) { log("Applying '" + extractor.getName() + "' to sheet '" + sheetName + "'."); extractor.setToolLogger(getDefaultLogger()); extractor.processSheet(workbook.getSheetAt(i), tm); } else { log("Leaving sheet '" + sheetName + "' unprocessed."); } } } else { log("Extraction cancelled."); } } log("Ok!"); } } catch (FileNotFoundException ex) { log(ex); } catch (IOException ex) { log(ex); } catch (Exception ex) { log(ex); } setState(WAIT); return true; }
From source file:org.wandora.application.tools.extractors.excel.ExcelExtractor.java
License:Open Source License
public Collection getSheets(HSSFWorkbook workbook) { ArrayList<String> sheets = new ArrayList(); int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets && !forceStop(); i++) { sheets.add(workbook.getSheetName(i)); }// w w w. jav a 2 s .c o m return sheets; }
From source file:poi.hssf.usermodel.examples.HSSFReadWrite.java
License:Apache License
/** * Method main// ww w . j a va 2s . co m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (args.length == 2) { if (args[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); } } else if (args.length == 3 && args[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * Method main//from ww w . j a v a 2 s. c o m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } // wb.close(); } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); // wb.close(); } } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); // wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsFile(String fileName) { try {//from w w w. j a va2 s.com HSSFWorkbook wb = HSSFReadWrite.readxlsFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:tournoi.ExcelToBDD.java
public boolean insertArbitre(String filePath) { boolean flag = true; db = new DBConnection(); con = db.getConnection();/*from w w w . j a v a 2 s. co m*/ try { // ??excel FileInputStream fin = new FileInputStream(filePath); // HSSFWorkbook workbook = new HSSFWorkbook(fin); //Get the workbook instance for XLS file HSSFSheet sheet = workbook.getSheetAt(1);//Get first sheet from the workbook System.out.println(workbook.getSheetName(1)); HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_arbitre = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); // cell4 = row.getCell(3); // int national_point = (int) cell4.getNumericCellValue(); // cell5 = row.getCell(4); // String categorie = cell5.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO arbitres VALUES('" + id_arbitre + "','" + nom + "','" + prenom + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; }