List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
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());//www . ja v a 2 s. co m 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.xhsoft.framework.common.file.ExcelHandle.java
License:Open Source License
/** * ????.xls/*from w w w .j a v a2 s .c om*/ * @params {:,:} * @return void * @author lijiangwei * @since 2012-11-12 */ private void readXLS() { for (int sheetNum = 0; sheetNum < xls.getNumberOfSheets(); sheetNum++) { HSSFSheet xls_sheet = xls.getSheetAt(sheetNum); if (xls_sheet == null) { continue; } // ??Sheet for (int rowNum = 0; rowNum <= xls_sheet.getLastRowNum(); rowNum++) { HSSFRow xls_row = xls_sheet.getRow(rowNum); if (xls_row == null) { continue; } // ???? for (int cellNum = 0; cellNum < xls_row.getLastCellNum(); cellNum++) { HSSFCell xls_cell = xls_row.getCell(cellNum); if (xls_cell == null) { continue; } System.out.print(" " + getCellValue(xls_cell)); } System.out.println(); } } }
From source file:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java
License:Open Source License
/** * It will parse the sheet with row wise and get the text from the sheet. *//* w ww .j a v a 2s. c o m*/ private void parseSheet(HSSFSheet sheet, StringBuffer cleanBuffer) { int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int rowIdx = firstRow; rowIdx <= lastRow; rowIdx++) { HSSFRow row = sheet.getRow(rowIdx); if (row != null) { parseRow(row, cleanBuffer); } } }
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 ""; }//from w w w .j av a 2 s .com 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 . com*/ 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.yf.kp.design.siswa.FrameSiswa.java
public void fillData(File file) { try {//from ww w.j ava2s . c o m siswaService = new SiswaServiceImpl(); Siswa siswa; try (FileInputStream input = new FileInputStream(file)) { POIFSFileSystem fs = new POIFSFileSystem(input); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); Row row; for (int i = 1; i <= sheet.getLastRowNum(); i++) { System.out.println(i); row = sheet.getRow(i); siswa = new Siswa(); siswa.setNis(row.getCell(0).getStringCellValue()); siswa.setNama(row.getCell(1).getStringCellValue()); siswa.setKelas(row.getCell(2).getStringCellValue()); siswa.setJenis_kelamin(row.getCell(3).getStringCellValue()); siswa.setAgama(row.getCell(4).getStringCellValue()); siswa.setTempat_lahir(row.getCell(5).getStringCellValue()); siswa.setTgl_lahir(row.getCell(6).getDateCellValue()); siswa.setAlamat(row.getCell(7).getStringCellValue()); siswa.setNama_ortu(row.getCell(8).getStringCellValue()); siswa.setPekerjaan(row.getCell(9).getStringCellValue()); siswa.setAgama_ortu(row.getCell(10).getStringCellValue()); siswa.setTelp(row.getCell(11).getStringCellValue()); siswa.setAlamat_ortu(row.getCell(12).getStringCellValue()); siswaService.save(siswa); } } JOptionPane.showMessageDialog(this, "Data Berhasil Dimasukan"); } catch (HeadlessException | IOException | HibernateException | OfficeXmlFileException | IllegalStateException | NullPointerException e) { JOptionPane.showMessageDialog(this, "Error, Contact Your System Admin\n" + e.getMessage()); } }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLSWithHeader(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; }//w ww . ja va 2 s . co m for (int row_index = 0; row_index <= sheet.getLastRowNum(); row_index++) { HSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
From source file:com.yyl.common.utils.excel.ExcelTools.java
private static List<List<Object>> readXLS(InputStream inputStream) throws IOException { // InputStream is = new FileInputStream(file); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); List<List<Object>> o = new ArrayList(); List<Object> temp = null; if (sheet == null) { return null; }//from w ww . j a v a 2 s . c o m for (int row_index = 1; row_index <= sheet.getLastRowNum(); row_index++) { HSSFRow row = sheet.getRow(row_index); if (row == null) { continue; } temp = new ArrayList(); for (int col_index = 0; col_index <= row.getLastCellNum(); col_index++) { temp.add(getCellValue(row.getCell(col_index))); } o.add(temp); } return o; }
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 ww w . ja va 2 s. co 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 ww . j a v a 2s.c o m 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("??"); }