List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:com.report.excel.ExcelToHtmlConverter.java
License:Apache License
/** * @return maximum 1-base index of column that were rendered, zero if none *//*from www .jav a2 s .c o m*/ protected int processRow(CellRangeAddress[][] mergedRanges, HSSFRow row, Element tableRowElement) { final HSSFSheet sheet = row.getSheet(); final short maxColIx = row.getLastCellNum(); if (maxColIx <= 0) return 0; final List<Element> emptyCells = new ArrayList<Element>(maxColIx); if (isOutputRowNumbers()) { Element tableRowNumberCellElement = htmlDocumentFacade.createTableHeaderCell(); //processRowNumber( row, tableRowNumberCellElement ); emptyCells.add(tableRowNumberCellElement); } int maxRenderedColumn = 0; for (int colIx = 0; colIx < maxColIx; colIx++) { if (!isOutputHiddenColumns() && sheet.isColumnHidden(colIx)) continue; CellRangeAddress range = ExcelToHtmlUtils.getMergedRange(mergedRanges, row.getRowNum(), colIx); if (range != null && (range.getFirstColumn() != colIx || range.getFirstRow() != row.getRowNum())) continue; HSSFCell cell = row.getCell(colIx); int divWidthPx = 0; if (isUseDivsToSpan()) { divWidthPx = getColumnWidth(sheet, colIx); boolean hasBreaks = false; for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++) { if (!isOutputHiddenColumns() && sheet.isColumnHidden(nextColumnIndex)) continue; if (row.getCell(nextColumnIndex) != null && !isTextEmpty(row.getCell(nextColumnIndex))) { hasBreaks = true; break; } divWidthPx += getColumnWidth(sheet, nextColumnIndex); } if (!hasBreaks) divWidthPx = Integer.MAX_VALUE; } Element tableCellElement = htmlDocumentFacade.createTableCell(); if (range != null) { if (range.getFirstColumn() != range.getLastColumn()) tableCellElement.setAttribute("colspan", String.valueOf(range.getLastColumn() - range.getFirstColumn() + 1)); if (range.getFirstRow() != range.getLastRow()) tableCellElement.setAttribute("rowspan", String.valueOf(range.getLastRow() - range.getFirstRow() + 1)); } boolean emptyCell; if (cell != null) { emptyCell = processCell(cell, tableCellElement, getColumnWidth(sheet, colIx), divWidthPx, row.getHeight() / 20f); } else { emptyCell = true; } if (emptyCell) { emptyCells.add(tableCellElement); } else { for (Element emptyCellElement : emptyCells) { tableRowElement.appendChild(emptyCellElement); } emptyCells.clear(); tableRowElement.appendChild(tableCellElement); maxRenderedColumn = colIx; } } return maxRenderedColumn + 1; }
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; }//from w ww .java 2s . co 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/* ww w . j a v a2s .c o m*/ 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
@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. ja va2 s. c o m 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 a v a 2 s . c o m*/ 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.j a v a 2s . co m } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:com.siva.javamultithreading.ExcelUtil.java
public static void copyRow(HSSFWorkbook newWorkbook, HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); HSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); }// ww w. j a v a2 s . c om copyCell(newWorkbook, oldCell, newCell, styleMap); } } }
From source file:com.sunesoft.ancon.core.saleContract.application.UploadSaleContracts.java
/** * ?/*from w w w . j av a 2 s .com*/ * @param row ?(??) * @return Boolean */ protected Boolean checkExcelCol(HSSFRow row) { System.out.print(row.getLastCellNum()); //????false ?? if (row.getLastCellNum() != 20) return false; return true; }
From source file:com.wangzhu.poi.ExcelToHtmlConverter.java
License:Apache License
/** * @return maximum 1-base index of column that were rendered, zero if none *//*www .j a v a 2 s . c o m*/ protected int processRow(CellRangeAddress[][] mergedRanges, HSSFRow row, Element tableRowElement) { final HSSFSheet sheet = row.getSheet(); final short maxColIx = row.getLastCellNum(); if (maxColIx <= 0) { return 0; } final List emptyCells = new ArrayList(maxColIx); if (this.isOutputRowNumbers()) { Element tableRowNumberCellElement = this.htmlDocumentFacade.createTableHeaderCell(); this.processRowNumber(row, tableRowNumberCellElement); emptyCells.add(tableRowNumberCellElement); } int maxRenderedColumn = 0; for (int colIx = 0; colIx < maxColIx; colIx++) { if (!this.isOutputHiddenColumns() && sheet.isColumnHidden(colIx)) { continue; } CellRangeAddress range = AbstractExcelUtils.getMergedRange(mergedRanges, row.getRowNum(), colIx); if ((range != null) && ((range.getFirstColumn() != colIx) || (range.getFirstRow() != row.getRowNum()))) { continue; } HSSFCell cell = row.getCell(colIx); int divWidthPx = 0; if (this.isUseDivsToSpan()) { divWidthPx = AbstractExcelConverter.getColumnWidth(sheet, colIx); boolean hasBreaks = false; for (int nextColumnIndex = colIx + 1; nextColumnIndex < maxColIx; nextColumnIndex++) { if (!this.isOutputHiddenColumns() && sheet.isColumnHidden(nextColumnIndex)) { continue; } if ((row.getCell(nextColumnIndex) != null) && !this.isTextEmpty(row.getCell(nextColumnIndex))) { hasBreaks = true; break; } divWidthPx += AbstractExcelConverter.getColumnWidth(sheet, nextColumnIndex); } if (!hasBreaks) { divWidthPx = Integer.MAX_VALUE; } } Element tableCellElement = this.htmlDocumentFacade.createTableCell(); if (range != null) { if (range.getFirstColumn() != range.getLastColumn()) { tableCellElement.setAttribute("colspan", String.valueOf((range.getLastColumn() - range.getFirstColumn()) + 1)); } if (range.getFirstRow() != range.getLastRow()) { tableCellElement.setAttribute("rowspan", String.valueOf((range.getLastRow() - range.getFirstRow()) + 1)); } } boolean emptyCell; if (cell != null) { emptyCell = this.processCell(cell, tableCellElement, AbstractExcelConverter.getColumnWidth(sheet, colIx), divWidthPx, row.getHeight() / 20f); } else { emptyCell = true; } if (emptyCell) { emptyCells.add(tableCellElement); } else { for (Iterator iterator = emptyCells.iterator(); iterator.hasNext();) { Element emptyCellElement = (Element) iterator.next(); tableRowElement.appendChild(emptyCellElement); } emptyCells.clear(); tableRowElement.appendChild(tableCellElement); maxRenderedColumn = colIx; } } return maxRenderedColumn + 1; }
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());/*from www . j av 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; }