List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:com.report.excel.ExcelToHtmlConverter.java
License:Apache License
protected void processSheet(HSSFSheet sheet) { //processSheetHeader( htmlDocumentFacade.getBody(), sheet ); sheet.setAutobreaks(true);/*from w w w . j a v a 2s. c o m*/ final int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); if (physicalNumberOfRows <= 0) return; Element table = htmlDocumentFacade.createTable(); htmlDocumentFacade.addStyleClass(table, cssClassPrefixTable, "border-collapse:collapse;border-spacing:0;"); Element tableBody = htmlDocumentFacade.createTableBody(); final CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.buildMergedRangesMap(sheet); final List<Element> emptyRowElements = new ArrayList<Element>(physicalNumberOfRows); int maxSheetColumns = 1; for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) { HSSFRow row = sheet.getRow(r); if (row == null) continue; if (!isOutputHiddenRows() && row.getZeroHeight()) continue; Element tableRowElement = htmlDocumentFacade.createTableRow(); htmlDocumentFacade.addStyleClass(tableRowElement, cssClassPrefixRow, "height:" + (row.getHeight() / 20f) + "pt;"); int maxRowColumnNumber = processRow(mergedRanges, row, tableRowElement); if (maxRowColumnNumber == 0) { emptyRowElements.add(tableRowElement); } else { if (!emptyRowElements.isEmpty()) { for (Element emptyRowElement : emptyRowElements) { tableBody.appendChild(emptyRowElement); } emptyRowElements.clear(); } tableBody.appendChild(tableRowElement); } maxSheetColumns = Math.max(maxSheetColumns, maxRowColumnNumber); } processColumnWidths(sheet, maxSheetColumns, table); if (isOutputColumnHeaders()) { //processColumnHeaders( sheet, maxSheetColumns, table ); } table.appendChild(tableBody); htmlDocumentFacade.getBody().appendChild(table); }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getMembersByExcel(InputStream is) throws Exception { // TODO Auto-generated method stub HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<Member> members = new ArrayList<Member>(); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; }// w w w . j a va2s .c o m int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); Member member = new Member(); String username = null; String mobile = null; String realName = null; for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (i == 0) { // ?? username = memberCell.toString(); } if (i == 1) { // mobile = memberCell.toString(); } if (i == 2) { // ?? realName = memberCell.toString(); } } // ? if (username != null) { member.setUsername(username); } else { String errorString = "" + rowNum + "????"; return null; } if (mobile != null) { member.setMobile(mobile); } else { String errorString = "" + rowNum + "???"; return null; } if (realName != null) { member.setRealName(realName); } else { String errorString = "" + rowNum + "????"; return null; } members.add(member); } } return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getMembers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start// w w w .j av a2s . c om for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.setIsUpdate(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } } // end?? members.add(member); } // endrow } // end return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
/** * ?week <??>/*w w w . ja v a 2 s . c om*/ * * @param in * @return * @throws Exception * @see [?#?#?] */ @Override public ExcelMessage validateCourseWeekLessions(InputStream is) throws Exception { courseWorkbook = new HSSFWorkbook(is); ExcelMessage excelMessage = new ExcelMessage(); excelMessage.setStatus(Status.success); // start for (int numSheet = 0; numSheet < courseWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = courseWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ? HSSFRow titleInfo = hssfSheet.getRow(0); ExcelMessage titleValidate = new ExcelMessage(); titleValidate.setStatus(Status.fail); titleValidate.setError("???"); if ((titleInfo.getCell(0) == null) || (!titleInfo.getCell(0).toString().equals(""))) { return titleValidate; } if ((titleInfo.getCell(1) == null) || (!titleInfo.getCell(1).toString().equals("?"))) { return titleValidate; } // start Row System.out.println(hssfSheet.getLastRowNum()); if (hssfSheet.getLastRowNum() == 0) { excelMessage.setStatus(Status.fail); excelMessage.setError("?"); is.close(); return excelMessage; } // week for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(0) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "?"); is.close(); return excelMessage; } String weekString = courseInfo.getCell(0).toString(); try { int week = Integer.parseInt(weekString); if (week == 1) { continue; } else if (week == 2) { continue; } else if (week == 3) { continue; } else if (week == 4) { continue; } else if (week == 5) { continue; } else if (week == 6) { continue; } else if (week == 7) { continue; } else { excelMessage.setStatus(Status.fail); excelMessage.setError( "" + rowNum + "?1-7"); return excelMessage; } } catch (Exception e) { logger.error("" + rowNum + "?" + e.getMessage()); excelMessage.setStatus(Status.fail); excelMessage .setError("" + rowNum + "?1-7"); is.close(); return excelMessage; } } // lessons for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(3) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "?"); is.close(); return excelMessage; } String lessonString = courseInfo.getCell(3).toString(); try { int lesson = Integer.parseInt(lessonString); if (lesson == 1) { continue; } else if (lesson == 2) { continue; } else if (lesson == 3) { continue; } else if (lesson == 4) { continue; } else if (lesson == 5) { continue; } else if (lesson == 6) { continue; } else if (lesson == 7) { continue; } else if (lesson == 8) { continue; } else { excelMessage.setStatus(Status.fail); excelMessage.setError( "" + rowNum + "?1-8"); is.close(); return excelMessage; } } catch (Exception e) { logger.error("" + rowNum + "?" + e.getMessage()); excelMessage.setStatus(Status.fail); excelMessage .setError("" + rowNum + "?1-8"); is.close(); return excelMessage; } } // start ??? for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow courseInfo = hssfSheet.getRow(rowNum); if (courseInfo == null) { continue; } if (courseInfo.getCell(2) == null) { excelMessage.setStatus(Status.fail); excelMessage.setError("" + rowNum + "???"); is.close(); return excelMessage; } } // end ??? } return excelMessage; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<Member> getTeachers(InputStream inputStream, DictSchool dictSchool, MemberType memberType, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<Member> members = new ArrayList<Member>(); // start/*from w w w.j av a 2 s. c om*/ for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow memberInfo = hssfSheet.getRow(rowNum); if (memberInfo == null) { continue; } Member member = new Member(); member.setMemberType(memberType); member.setCreateDate(new Date()); member.setModifyDate(new Date()); member.setIsEnabled(true); member.setIsLocked(false); member.setLoginFailureCount(0); Setting setting = SettingUtils.get(); member.setPassword(DigestUtils.md5Hex(setting.getInitPassword())); member.setPoint(0L); member.setRegisterIp(EduUtil.getAddr(request)); member.setSignature(""); member.setValidateCodeNumber(0); member.setDictSchool(dictSchool); // member.s(true); member.setIsAcceptLeaveInfo(true); String mobile = null; String realName = null; // start?? for (int i = 0; i < memberInfo.getLastCellNum(); i++) { HSSFCell memberCell = memberInfo.getCell(i); if (memberCell == null) { continue; } // if (i == 0) // { // // ?? // username = memberCell.toString(); // username = username.trim(); // member.setUsername(username); // } if (i == 0) { // mobile = memberCell.toString(); member.setMobile(mobile); member.setUsername(mobile); } if (i == 1) { // ?? realName = memberCell.toString(); realName = realName.trim(); member.setRealName(realName); } if (i == 2) { // ??-? String classTeacherMap = memberCell.toString(); classTeacherMap = classTeacherMap.trim(); member.setAddress(classTeacherMap); } } // end?? members.add(member); } // endrow } // end return members; }
From source file:com.sammyun.service.impl.ExcelServiceImpl.java
License:Open Source License
@Override public List<DictStudent> getDictStudents(InputStream inputStream, DictSchool dictSchool, HttpServletRequest request) throws Exception { HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream); List<DictStudent> dictStudents = new ArrayList<DictStudent>(); // start--/*from ww w . j ava2 s . com*/ for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } int infoSize = hssfSheet.getRow(0).getLastCellNum();// ? int rowSize = hssfSheet.getLastRowNum();// ??? // start Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow studentInfo = hssfSheet.getRow(rowNum); if (studentInfo == null) { continue; } DictStudent dictStudent = new DictStudent(); dictStudent.setCreateDate(new Date()); dictStudent.setModifyDate(new Date()); dictStudent.setStudentStatus(StudentStatus.active); String studentName = null; String studentNo = null; DictClass dictClass = new DictClass(); // start_?? for (int i = 0; i < studentInfo.getLastCellNum(); i++) { HSSFCell studentCell = studentInfo.getCell(i); if (studentCell == null) { continue; } if (i == 0) { // ? studentNo = studentCell.toString(); studentNo = studentNo.trim(); dictStudent.setStudentNo(studentNo); } if (i == 1) { // ?? studentName = studentCell.toString(); studentName = studentName.trim(); dictStudent.setStudentName(studentName); } if (i == 2) { // ? String className = studentCell.toString(); className = className.trim(); List<DictClass> classes = dictClassDao.getClassByName(className, dictSchool); if (classes != null) { if (classes.size() > 0) { dictClass = classes.get(0); dictStudent.setDictClass(dictClass); } } } if (i == 3) { // ? String memberString = studentCell.toString(); memberString = memberString.trim(); memberString = memberString.replaceAll("", ","); dictStudent.setStuRmark(memberString); } } // end___?? dictStudents.add(dictStudent); } } // end return dictStudents; }
From source file:com.siva.javamultithreading.ExcelUtil.java
private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) { int newRownumber = newSheet.getLastRowNum() + 1; int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow = newSheet.createRow(i + newRownumber); if (srcRow != null) { copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); }/*from w w w.ja v a 2s. c o m*/ } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:com.smanempat.controller.ControllerClassification.java
private void showXLS(JTextField txtFileDirectory, JTable tablePreview) throws FileNotFoundException, IOException { DefaultTableModel tableModel = new DefaultTableModel(); File fileName = new File(txtFileDirectory.getText()); FileInputStream inputStream = new FileInputStream(fileName); HSSFWorkbook workBook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workBook.getSheetAt(0); int rowValue = sheet.getLastRowNum() + 1; int colValue = sheet.getRow(0).getLastCellNum(); String[][] data = new String[rowValue][colValue]; String[] colName = new String[colValue]; for (int i = 0; i < rowValue; i++) { HSSFRow row = sheet.getRow(i);/*from w ww.jav a2s . co m*/ for (int j = 0; j < colValue; j++) { HSSFCell cell = row.getCell(j); int type = cell.getCellType(); Object returnCellValue = null; if (type == 0) { returnCellValue = cell.getNumericCellValue(); } else if (type == 1) { returnCellValue = cell.getStringCellValue(); } data[i][j] = returnCellValue.toString(); } } for (int i = 0; i < colValue; i++) { colName[i] = data[0][i]; } tableModel = new DefaultTableModel(data, colName); tablePreview.setModel(tableModel); tableModel.removeRow(0); }
From source file:com.softtek.mdm.web.admin.IndexController.java
private void exportExcel(String sheetName, String[] headNames, List<OrganizationModel> lists, HttpServletResponse response) {/* w w w . jav a2s . c o m*/ OutputStream out = null; try { HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = workbook.createSheet(sheetName); // // HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0); //sheet??getColumnTopStyle()/getStyle()? - ? - ? HSSFCellStyle columnTopStyle = CommUtil.getColumnTopStyle(workbook);//?? sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headNames.length - 1))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(sheetName); // int columnNum = headNames.length; HSSFRow rowRowName = sheet.createRow(2); // 2?() // sheet? for (int n = 0; n < columnNum; n++) { HSSFCell cellRowName = rowRowName.createCell(n); //? cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //?? HSSFRichTextString text = new HSSFRichTextString(headNames[n]); cellRowName.setCellValue(text); //? cellRowName.setCellStyle(columnTopStyle); //?? } //?sheet? HSSFDataFormat format = workbook.createDataFormat(); short formatDate = format.getFormat("yyyy-MM-dd hh:mm:ss"); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 3);// OrganizationModel obj = lists.get(i);//??? /*row.createCell(0).setCellValue(obj.getOrgType());*/ row.createCell(0).setCellValue(obj.getName()); row.createCell(1).setCellValue(obj.getCreateName()); row.createCell(2).setCellValue(obj.getTotalUsers() == null ? 0 : obj.getTotalUsers()); row.createCell(3).setCellValue(obj.getTotalDevices() == null ? 0 : obj.getTotalDevices()); row.createCell(4).setCellValue(obj.getLicenseCount() == null ? 0 : obj.getLicenseCount()); row.createCell(5).setCellValue(obj.getUseUsers() == null ? 0 : obj.getUseUsers()); HSSFCell cell = row.createCell(6); cell.setCellValue(obj.getCreateTime()); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(formatDate); cell.setCellStyle(cellStyle); } //?? for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { HSSFRow currentRow; //? currentRow = (sheet.getRow(rowNum) == null) ? sheet.createRow(rowNum) : sheet.getRow(rowNum); if (currentRow.getCell(colNum) != null) { HSSFCell currentCell = currentRow.getCell(colNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { int length = currentCell.getStringCellValue().getBytes().length; if (columnWidth < length) { columnWidth = length; } } } } sheet.setColumnWidth(colNum, (colNum == 0) ? (columnWidth * 256) : ((columnWidth + 10) * 256)); } if (workbook != null) { try { String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls"; String headStr = "attachment; filename=\"" + fileName + "\""; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", headStr); out = response.getOutputStream(); workbook.write(out); } catch (IOException e) { logger.error(e.getMessage()); } finally { if (out != null) { out.close(); } } } } catch (Exception e) { logger.error(e.getMessage()); } }
From source file:com.thingtrack.xbom.parser.XBomParserApplication.java
License:Apache License
private void parseXbom(InputStream file) throws FileNotFoundException, IOException, XbomParsingException { //Clear previous importations assemblingPartNodes.clear();// ww w. j a v a 2 s . c o m // Load XLS file POIFSFileSystem fs = new POIFSFileSystem(file); HSSFWorkbook workbook = new HSSFWorkbook(fs); HSSFSheet sheet = workbook.getSheet(XBOM_SHEET_CAPTION); // Parsing assembling parts for (int i = 11; i < sheet.getLastRowNum(); i++) { HSSFRow row = (HSSFRow) sheet.getRow(i); try { assemblingPartNodes.add(getAssemblingPart(row)); } //The assembling part parsing has ended catch (NoAssemblingPartException e) { break; } } if (assemblingPartNodes.size() > 2) { //Build tree relationship buildTreeRelationship(assemblingPartNodes.get(0), assemblingPartNodes.subList(1, assemblingPartNodes.size())); loadTreeTable(assemblingPartNodes.get(0)); } assemblingPartNodes.size(); }