List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getNumberOfSheets
@Override public int getNumberOfSheets()
From source file:com.web.mavenproject6.other.XLSParser.java
public static String parse(String name) { String result = ""; InputStream in = null;/* w ww. j a va 2 s . c om*/ HSSFWorkbook wb = null; try { in = new FileInputStream(name); wb = new HSSFWorkbook(in); } catch (IOException e) { e.printStackTrace(); } System.out.println("!!!!sheet count:" + wb.getNumberOfSheets()); for (int i = 0; i < wb.getNumberOfSheets(); i++) { System.out.println("!!!!sheet[" + i + "]:" + wb.getSheetName(i)); } for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> it = sheet.iterator(); String buf = ""; while (it.hasNext()) { Row row = it.next(); //result += row.getRowNum() + ":"; switch (row.getRowNum() + 1) { case 13: result += readCell(row); result += "<br>"; break; case 14: result += readCell(row); result += "<br>"; break; case 15: result += readCell(row); break; case 16: result += readCell(row); result += "<br>"; break; case 18: result += readCell(row); break; case 19: result += readCell(row); break; case 20: result += readCell(row); break; case 21: result += readCell(row); break; case 22: result += readCell(row); result += "<br>"; break; case 25: result += readCell(row); break; case 26: result += readCell(row); result += "<br>"; break; default: result += readCell(row); result += "<br>"; break; } // if (row.getRowNum() + 1 != 34 || row.getRowNum() + 1 != 34) { // result += readCell(row); // } else { // result += "[" + readCell(row, 0, 7) + "][" + readCell(row, 7, 10); // } } } return result; }
From source file:com.web.mavenproject6.other.XLSParser.java
public static String parseStatisticDoc(String name) { String result = ""; InputStream in = null;/*from w ww .j av a2 s .c o m*/ HSSFWorkbook wb = null; try { in = new FileInputStream(name); wb = new HSSFWorkbook(in); } catch (IOException e) { e.printStackTrace(); } System.out.println("!!!!sheet count:" + wb.getNumberOfSheets()); for (int i = 0; i < wb.getNumberOfSheets(); i++) { System.out.println("!!!!sheet[" + i + "]:" + wb.getSheetName(i)); } Sheet sheet = wb.getSheetAt(0); Iterator<Row> it = sheet.iterator(); while (it.hasNext()) { Row row = it.next(); Iterator<Cell> cells = row.iterator(); // row.getCell(0).getStringCellValue() switch (row.getRowNum()) { } result += "\n"; } return result; }
From source file:com.weibo.datasys.parser.office.extractor.ExcelParse.java
License:Open Source License
private FileData extractor(File filePath) { FileData fData = new FileData(); fData.setName(filePath.getName());// w w w. j a va 2 s.com StringBuffer sBuffer = new StringBuffer(); HSSFWorkbook workbook = null; try { workbook = new HSSFWorkbook(new FileInputStream(filePath)); for (int iSheets = 0; iSheets < workbook.getNumberOfSheets(); ++iSheets) { HSSFSheet sheet = workbook.getSheetAt(iSheets); for (int iRow = 0; iRow < sheet.getLastRowNum(); ++iRow) { HSSFRow row = sheet.getRow(iRow); for (int iCell = 0; iCell < row.getLastCellNum(); ++iCell) { HSSFCell cell = row.getCell(iCell); if (null != cell) { if (0 == cell.getCellType()) { sBuffer.append(String.valueOf(cell.getNumericCellValue())); sBuffer.append(SEGMENT_CHAR); } else if (1 == cell.getCellType()) { sBuffer.append(cell.getStringCellValue().trim()); sBuffer.append(SEGMENT_CHAR); } } } } } fData.setContent(sBuffer.toString()); } catch (Exception e) { LOG.error("", e); } return fData; }
From source file:com.xx.platform.util.tools.ms.ExcelExtractor.java
License:Apache License
public String extractText(POIFSFileSystem poifs) throws Exception { StringBuffer resultText = new StringBuffer(); HSSFWorkbook wb = new HSSFWorkbook(poifs, true); if (wb == null) { return ""; }/* w w w. j a v a 2 s .co m*/ HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } cNum = row.getLastCellNum(); for (int k = 0; k < cNum; k++) { if ((cell = row.getCell((short) k)) != null) { /*if(HSSFDateUtil.isCellDateFormatted(cell) == true) { resultText += cell.getDateCellValue().toString() + " "; } else */ if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { resultText.append(cell.getStringCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); resultText.append(d.toString()); } /* else if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){ resultText += cell.getCellFormula() + " "; } */ } } } } return resultText.toString(); }
From source file:com.xx.platform.util.tools.ms.ExcelExtrator.java
License:Apache License
public String extractText(POIFSFileSystem poifs) throws Exception { List<Map<String, String>> list = new ArrayList<Map<String, String>>(); String text = null;/*from w ww . j av a 2s . c om*/ try { HSSFWorkbook wb = new HSSFWorkbook(poifs, true); if (wb == null) { return null; } HSSFSheet sheet; HSSFRow row; HSSFCell cell; int sNum = 0; int rNum = 0; int cNum = 0; sNum = wb.getNumberOfSheets(); for (int i = 0; i < sNum; i++) { if ((sheet = wb.getSheetAt(i)) == null) { continue; } String[] key = null; //field boolean init = false; //key rNum = sheet.getLastRowNum(); for (int j = 0; j <= rNum; j++) { if ((row = sheet.getRow(j)) == null) { continue; } Map<String, String> rowdata = new HashMap<String, String>(); cNum = row.getLastCellNum(); if (!init) key = new String[cNum]; String value = ""; StringBuffer content = new StringBuffer(); for (int k = 0; k < cNum; k++) { if ((cell = row.getCell((short) k)) != null) { if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { value = cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { Double d = new Double(cell.getNumericCellValue()); value = d.toString(); } else value = ""; if (init) { content.append(value); } else { key[k] = value; } } } } } } catch (Exception e) { text = ""; } return text; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<Map<String, String>> readXLS(InputStream inputStream, Map<String, Integer> keyMaps) throws IOException { // InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(inputStream); List<Map<String, String>> list = new ArrayList(); Map<String, String> temp = null; for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { HSSFSheet sheet = wb.getSheetAt(sheetIndex); if (sheet == null) { continue; }/*from w w w . j a va 2 s . c o m*/ for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); temp = new HashMap(); if (row != null) { for (Entry<String, Integer> entry : keyMaps.entrySet()) { int index = entry.getValue(); HSSFCell cell = row.getCell(index); temp.put(entry.getKey(), getCellValue(cell)); } } list.add(temp); } } return list; }
From source file:com.zhu.action.CarAction.java
public void exportpeople(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {//from w w w . j a va2s. c om CarForm carForm = (CarForm) form; // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("?"); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? HSSFCell cell = row.createCell(0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("???"); cell.setCellStyle(style); // ? ?? OrderService orderService = new OrderService(); List<Orderinfo> list = orderService.getOrderCarDetail(carForm.getId()); System.out.println(list.size()); for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 1); Orderinfo order = (Orderinfo) list.get(i); // ? row.createCell(0).setCellValue(order.getN1()); row.createCell(1).setCellValue(order.getI1()); row = sheet.createRow(i + list.size() + 1); row.createCell(0).setCellValue(order.getN2()); row.createCell(1).setCellValue(order.getI2()); row = sheet.createRow(i + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN3()); row.createCell(1).setCellValue(order.getI3()); row = sheet.createRow(i + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN4()); row.createCell(1).setCellValue(order.getI4()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN5()); row.createCell(1).setCellValue(order.getI5()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN6()); row.createCell(1).setCellValue(order.getI6()); row = sheet.createRow( i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN7()); row.createCell(1).setCellValue(order.getI7()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN8()); row.createCell(1).setCellValue(order.getI8()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN9()); row.createCell(1).setCellValue(order.getI9()); row = sheet.createRow(i + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + list.size() + 1); row.createCell(0).setCellValue(order.getN10()); row.createCell(1).setCellValue(order.getI10()); } // ? String filePath = ""; Date dt = new Date(); DateFormat df = new SimpleDateFormat("yyyyMMddHHmmss"); String date = df.format(dt).toString(); filePath = "/Users/Nemo/Documents/carpeople" + date + ".xls"; File file = new File(filePath); try { // FileOutputStream fout = new FileOutputStream( // "/Users/Nemo/Documents/carpeople.xls"); // wb.write(fout); // fout.close(); OutputStream out = new FileOutputStream(file); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } int key = 0; int MaxRowNum = 0, MaxCellNum = 0; try { FileInputStream in = new FileInputStream(filePath); POIFSFileSystem fs = new POIFSFileSystem(in); HSSFWorkbook workbook = new HSSFWorkbook(fs); FileOutputStream out = new FileOutputStream("/Users/Nemo/Documents/carpeople" + date + ".xls"); int number = workbook.getNumberOfSheets(); for (int i = 0; i < number; i++) { // ?sheet sheet = workbook.getSheetAt(i); // 14 System.out.println("" + sheet.getSheetName() + " ? " + (sheet.getLastRowNum() + 1)); MaxRowNum = 0; for (int k = 0; k <= sheet.getLastRowNum(); k++) { HSSFRow hRow = sheet.getRow(k); // System.out.println((k + 1) + ""); if (isBlankRow(hRow)) // { int m = 0; for (m = k + 1; m <= sheet.getLastRowNum(); m++) { HSSFRow nhRow = sheet.getRow(m); if (!isBlankRow(nhRow)) { // System.out.println("?" + (m + 1)); sheet.shiftRows(m, sheet.getLastRowNum(), k - m); break; } } if (m > sheet.getLastRowNum()) break; // ? } else { // ? MaxRowNum++; if (MaxCellNum < hRow.getLastCellNum()) MaxCellNum = hRow.getLastCellNum(); } } workbook.setPrintArea(i, 0, MaxCellNum, 0, MaxRowNum); System.out .println("?? " + sheet.getSheetName() + " " + MaxRowNum); } workbook.write(out); in.close(); out.close(); } catch (IOException e) { System.out.println(key + " " + e.getMessage() + " "); e.printStackTrace(); } System.out.println("??"); }
From source file:com.zxy.commons.poi.excel.ExcelUtils.java
License:Apache License
/** * ?Excelsheet//ww w . j av a 2 s .c om * * @param inputPath ???Excel * @return Excel? * @throws IOException IOException */ public static Map<String, Table<Integer, String, String>> readAll2table(String inputPath) throws IOException { Map<String, Table<Integer, String, String>> tables = Maps.newLinkedHashMap(); FileInputStream inputStream = null; HSSFWorkbook wb = null; try { inputStream = new FileInputStream(inputPath); BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream); // HSSFWorkbook POIFSFileSystem fs = new POIFSFileSystem(bufferedInputStream); wb = new HSSFWorkbook(fs); List<String> columnNames = Lists.newLinkedList(); for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) { Table<Integer, String, String> table = TreeBasedTable.create(); HSSFSheet st = wb.getSheetAt(sheetIndex); String sheetName = st.getSheetName(); for (int rowIndex = 0; rowIndex <= st.getLastRowNum(); rowIndex++) { HSSFRow row = st.getRow(rowIndex); for (int columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) { HSSFCell cell = row.getCell(columnIndex); if (cell != null) { if (rowIndex == 0) { // columnNames.add(cell.getStringCellValue()); } else { String value = cell.getStringCellValue(); table.put(rowIndex, columnNames.get(columnIndex), value); } } } } tables.put(sheetName, table); } return tables; } finally { if (wb != null) { wb.close(); } if (inputStream != null) { inputStream.close(); } } }
From source file:data.services.FreeOptionService.java
public void updateFromXml(File fl) { try {//from ww w . jav a2s . co m FileInputStream fi = new FileInputStream(fl); int i = 1; String listName = "? ."; int s = 39191091; List<FreeOption> foForSave = new ArrayList(); List<FreeOption> foForUpd = new ArrayList(); try { HSSFWorkbook workbook = new HSSFWorkbook(fi); int sheetNumber = workbook.getNumberOfSheets(); while (i < sheetNumber) { HSSFSheet sheet = workbook.getSheetAt(i); i++; listName = sheet.getSheetName(); Iterator<Row> it = sheet.iterator(); Car car = new Car(); s = 0; while (it.hasNext()) { Row row = it.next(); s++; Cell idCell = row.getCell(0); if (idCell.getCellType() == Cell.CELL_TYPE_STRING) { String nameCell = idCell.getStringCellValue().trim(); if (nameCell.equals("CAR_ID")) { row = it.next(); s++; Cell carIdCell = row.getCell(0); String carIdstr = StringAdapter.HSSFSellValue(carIdCell); if (carIdstr.contains(".")) { int point = carIdstr.indexOf("."); carIdstr = carIdstr.substring(0, point); } Long carId = StringAdapter.toLong(carIdstr); car = carDao.find(carId); //throw new Exception(" carIdstr="+carIdstr+"; carId="+carId+"; "); } else if (nameCell.equals("OPTION_ID")) { while (it.hasNext()) { row = it.next(); s++; Cell optIdCell = row.getCell(0); if (optIdCell != null) { if (optIdCell.getCellType() == Cell.CELL_TYPE_STRING) { String optIdstr = optIdCell.getStringCellValue().trim(); if (optIdstr.equals("CAR_ID")) { /*it.remove(); break;*/ row = it.next(); s++; Cell carIdCell = row.getCell(0); String carIdstr = StringAdapter.HSSFSellValue(carIdCell); if (carIdstr.contains(".")) { int point = carIdstr.indexOf("."); carIdstr = carIdstr.substring(0, point); } Long carId = StringAdapter.toLong(carIdstr); car = carDao.find(carId); it.next(); s++; } else { String oIdstr = StringAdapter.HSSFSellValue(optIdCell); if (oIdstr.contains(".")) { int point = oIdstr.indexOf("."); oIdstr = oIdstr.substring(0, point); } Long optId = Long.valueOf(oIdstr); FreeOption fored = freeOptionDao.find(optId); if (fored != null) { FreeOption supfo = getOptFromRow(row); fored.setAudial(supfo.getAudial()); fored.setDescription(supfo.getDescription()); fored.setKinestetic(supfo.getKinestetic()); fored.setParamValue(supfo.getParamValue()); fored.setPercentValue(supfo.getPercentValue()); fored.setPrice(supfo.getPrice()); fored.setRadical(supfo.getRadical()); fored.setTitle(supfo.getTitle()); fored.setType(supfo.getType()); fored.setUid(supfo.getUid()); fored.setVisual(supfo.getVisual()); //throw new Exception("4!"); if (validate(fored)) { //freeOptionDao.update(fored); foForUpd.add(fored); } } else { FreeOption fo = getOptFromRow(row); if (car != null) { fo.setCar(car); if (validate(fo)) { foForSave.add(fo); } } //addError(": ? , ."); } } } else if (optIdCell.getCellType() == Cell.CELL_TYPE_NUMERIC) { String oIdstr = StringAdapter.HSSFSellValue(optIdCell); if (oIdstr.contains(".")) { int point = oIdstr.indexOf("."); oIdstr = oIdstr.substring(0, point); } Long optId = Long.valueOf(oIdstr); //Long optId = StringAdapter.toLong(StringAdapter.getString(optIdCell.getNumericCellValue())); FreeOption fored = freeOptionDao.find(optId); if (fored != null) { FreeOption supfo = getOptFromRow(row); fored.setAudial(supfo.getAudial()); fored.setDescription(supfo.getDescription()); fored.setKinestetic(supfo.getKinestetic()); fored.setParamValue(supfo.getParamValue()); fored.setPercentValue(supfo.getPercentValue()); fored.setPrice(supfo.getPrice()); fored.setRadical(supfo.getRadical()); fored.setTitle(supfo.getTitle()); fored.setType(supfo.getType()); fored.setUid(supfo.getUid()); fored.setVisual(supfo.getVisual()); //throw new Exception("3!"); if (validate(fored)) { //freeOptionDao.update(fored); foForUpd.add(fored); } } else { FreeOption fo = getOptFromRow(row); if (car != null) { fo.setCar(car); if (validate(fo)) { foForSave.add(fo); } } } } else if (optIdCell.getCellType() == Cell.CELL_TYPE_BLANK) { if (car != null) { FreeOption fo = getOptFromRow(row); fo.setCar(car); //throw new Exception("2!"); if (validate(fo)) { foForSave.add(fo); } } } } else { if (car != null) { FreeOption fo = getOptFromRow(row); fo.setCar(car); //throw new Exception("1! rad="+fo.getRadical()+"; a="+fo.getAudial()+"; v="+fo.getVisual()+"; perc="+fo.getPercentValue()+"; val="+fo.getParamValue()+"; price="+fo.getPrice()+"; "); if (validate(fo)) { foForSave.add(fo); } } } } } } } } workbook.close(); } catch (Exception e) { addError(": ?:" + i + ", " + listName + ", ?:" + s + ", " + StringAdapter.getStackTraceException(e)); } fi.close(); for (FreeOption fo : foForSave) { freeOptionDao.save(fo); } for (FreeOption fo : foForUpd) { freeOptionDao.update(fo); } } catch (Exception e) { addError(" xml"); addError(e.getMessage()); } }
From source file:ddf.metrics.reporting.internal.rest.MetricsEndpointTest.java
License:Open Source License
@Test public void testGetMetricsDataAsXls() throws Exception { // Create RRD file that Metrics Endpoint will detect int dateOffset = 900; // 15 minutes in seconds createRrdFile(dateOffset);//from w ww . j a v a 2s.c om UriInfo uriInfo = createUriInfo(); // Get the metrics data from the endpoint MetricsEndpoint endpoint = getEndpoint(); endpoint.setMetricsDir(TEST_DIR); Response response = endpoint.getMetricsData("uptime", "xls", null, null, Integer.toString(dateOffset), "my label", "my title", uriInfo); cleanupRrd(); InputStream xls = (InputStream) response.getEntity(); assertThat(xls, not(nullValue())); HSSFWorkbook wb = new HSSFWorkbook(xls); assertThat(wb.getNumberOfSheets(), equalTo(1)); HSSFSheet sheet = wb.getSheet("Uptime"); assertThat(sheet, not(nullValue())); // Expect 7 rows: title + blank + column headers + 2 rows of samples + blank + // totalQueryCount assertThat(sheet.getPhysicalNumberOfRows(), equalTo(7)); // first row should have title in first cell HSSFRow row = sheet.getRow(0); assertThat(row, not(nullValue())); assertThat(row.getCell(0).getStringCellValue(), startsWith("Uptime for")); // third row should have column headers in first and second cells row = sheet.getRow(2); assertThat(row.getCell(0).getStringCellValue(), equalTo("Timestamp")); assertThat(row.getCell(1).getStringCellValue(), equalTo("Value")); // should have 2 rows of samples' data row = sheet.getRow(3); assertThat(row.getCell(0).getStringCellValue(), not(nullValue())); assertThat(row.getCell(1).getNumericCellValue(), not(nullValue())); row = sheet.getRow(4); assertThat(row.getCell(0).getStringCellValue(), not(nullValue())); assertThat(row.getCell(1).getNumericCellValue(), not(nullValue())); // last row should have totalQueryCount in first cell row = sheet.getRow(sheet.getLastRowNum()); assertThat(row.getCell(0).getStringCellValue(), startsWith("Total Count:")); assertThat(row.getCell(1).getNumericCellValue(), not(nullValue())); }