Java Code Examples for org.apache.poi.hssf.usermodel.HSSFSheet#getLastRowNum()
The following examples show how to use
org.apache.poi.hssf.usermodel.HSSFSheet#getLastRowNum() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: POIUtils.java From ermaster-b with Apache License 2.0 | 6 votes |
public static CellLocation findMatchCell(HSSFSheet sheet, String regexp) { for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet .getLastRowNum() + 1; rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row == null) { continue; } Integer colNum = findMatchColumn(row, regexp); if (colNum != null) { return new CellLocation(rowNum, colNum.shortValue()); } } return null; }
Example 2
Source File: SensitiveWordAdminController.java From pybbs with GNU Affero General Public License v3.0 | 6 votes |
@RequiresPermissions("sensitive_word:import") @PostMapping("import") @ResponseBody public Result _import(@RequestParam("file") MultipartFile file) { try { HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream()); HSSFSheet worksheet = workbook.getSheetAt(0); int i = 0; while (i <= worksheet.getLastRowNum()) { HSSFRow row = worksheet.getRow(i++); String word = row.getCell(0).getStringCellValue(); SensitiveWord sensitiveWord = sensitiveWordService.selectByWord(word); if (sensitiveWord == null) { sensitiveWord = new SensitiveWord(); sensitiveWord.setWord(word); sensitiveWordService.save(sensitiveWord); } } return success(); } catch (IOException e) { // e.printStackTrace(); log.error(e.getMessage()); return error(e.getMessage()); } }
Example 3
Source File: POIUtils.java From ermasterr with Apache License 2.0 | 6 votes |
public static CellLocation findCell(final HSSFSheet sheet, final String str, final int colNum) { for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) { final HSSFRow row = sheet.getRow(rowNum); if (row == null) { continue; } final HSSFCell cell = row.getCell(colNum); if (cell == null) { continue; } final HSSFRichTextString cellValue = cell.getRichStringCellValue(); if (!Check.isEmpty(cellValue.getString())) { if (cellValue.getString().equals(str)) { return new CellLocation(rowNum, (short) colNum); } } } return null; }
Example 4
Source File: POIUtils.java From ermasterr with Apache License 2.0 | 6 votes |
public static CellLocation findMatchCell(final HSSFSheet sheet, final String regexp) { for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) { final HSSFRow row = sheet.getRow(rowNum); if (row == null) { continue; } final Integer colNum = findMatchColumn(row, regexp); if (colNum != null) { return new CellLocation(rowNum, colNum.shortValue()); } } return null; }
Example 5
Source File: POIUtils.java From ermaster-b with Apache License 2.0 | 6 votes |
public static CellLocation findCell(HSSFSheet sheet, String[] strs) { for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet .getLastRowNum() + 1; rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row == null) { continue; } for (int i = 0; i < strs.length; i++) { Integer colNum = findColumn(row, strs[i]); if (colNum != null) { return new CellLocation(rowNum, colNum.shortValue()); } } } return null; }
Example 6
Source File: Util.java From Knowage-Server with GNU Affero General Public License v3.0 | 6 votes |
/** * @param destination * the sheet to create from the copy. * @param the * sheet to copy. * @param copyStyle * true copy the style. */ private static void copySheet(List<CellStyle> list, HSSFSheet source, XSSFSheet destination, boolean copyStyle) { int maxColumnNum = 0; List<CellStyle> styleMap = null; if (copyStyle) { styleMap = list; } for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) { HSSFRow srcRow = source.getRow(i); XSSFRow destRow = destination.createRow(i); if (srcRow != null) { copyRow(source, destination, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } destination.shiftRows(destination.getFirstRowNum(), destination.getLastRowNum(), 3); for (int i = 0; i <= maxColumnNum; i++) { destination.autoSizeColumn(i); } }
Example 7
Source File: WordEmbedsTest.java From sun-wordtable-read with Apache License 2.0 | 5 votes |
private static void listEmbeds2(XWPFDocument doc) throws Exception { for (final PackagePart pPart : doc.getAllEmbedds()) { final String contentType = pPart.getContentType(); System.out.println(contentType + "\n"); if (contentType.equals("application/vnd.ms-excel")) { final HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream()); for (int sheet = 0; sheet < embeddedWorkbook.getNumberOfSheets(); sheet++) { final HSSFSheet activeSheet = embeddedWorkbook.getSheetAt(sheet); if (activeSheet.getSheetName().equalsIgnoreCase("Sheet1")) { for (int rowIndex = activeSheet.getFirstRowNum(); rowIndex <= activeSheet .getLastRowNum(); rowIndex++) { final HSSFRow row = activeSheet.getRow(rowIndex); for (int cellIndex = row.getFirstCellNum(); cellIndex <= row .getLastCellNum(); cellIndex++) { final HSSFCell cell = row.getCell(cellIndex); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) System.out.println("Row:" + rowIndex + " Cell:" + cellIndex + " = " + cell.getStringCellValue()); if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { System.out.println("Row:" + rowIndex + " Cell:" + cellIndex + " = " + cell.getNumericCellValue()); cell.setCellValue(cell.getNumericCellValue() * 2); // update // the // value } } } } } } } } }
Example 8
Source File: GroupingUploadAJAXController.java From lams with GNU General Public License v2.0 | 5 votes |
public int parseGroupSpreadsheet(MultipartFile fileItem, Long groupingID, Map<String, Set<String>> groups) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream()); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); int skipped = 0; for (int i = startRow + 1; i < (endRow + 1); i++) { HSSFRow row = sheet.getRow(i); String login = parseStringCell(row.getCell(0)); if (login != null) { login = login.trim(); if (login.length() > 0) { String groupName = row.getLastCellNum() > 3 ? parseStringCell(row.getCell(3)) : null; groupName = groupName != null ? groupName.trim() : null; if (groupName == null || groupName.length() == 0) { skipped++; GroupingUploadAJAXController.log.warn("Unable to add learner " + login + " for group in related to grouping " + groupingID + " as group name is missing."); } else { Set<String> users = groups.get(groupName); if (users == null) { users = new HashSet<>(); groups.put(groupName, users); } users.add(login); } } } } return skipped; }
Example 9
Source File: ExportToExcelManager.java From ermasterr with Apache License 2.0 | 5 votes |
private void initLoopDefinitionMap(final HSSFSheet loopsSheet) { for (int i = 2; i <= loopsSheet.getLastRowNum(); i++) { final String templateSheetName = POIUtils.getCellValue(loopsSheet, i, 0); if (templateSheetName == null) { break; } final int firstLine = POIUtils.getIntCellValue(loopsSheet, i, 1); final int spaceLine = POIUtils.getIntCellValue(loopsSheet, i, 2); final String sheetName = POIUtils.getCellValue(loopsSheet, i, 3); loopDefinitionMap.put(templateSheetName, new LoopDefinition(firstLine, spaceLine, sheetName)); } }
Example 10
Source File: readExcel.java From Selenium with The Unlicense | 5 votes |
public static void readFile(String path) throws IOException { // Path to the excel file File datafile = new File(path); // A Buffered File Input Stream to read the data InputStream fis = new BufferedInputStream(new FileInputStream(datafile)); // We create a workbook which represents the excel file HSSFWorkbook book = new HSSFWorkbook(fis); // Next a sheet which represents the sheet within that excel file HSSFSheet sheet = book.getSheet("Details"); // No of rows in the sheet int rowNum = sheet.getLastRowNum() + 1; // No of columns in the sheet int colNum = sheet.getRow(0).getLastCellNum(); // A Two dimensional array of Strings which represents the data in the // sheet String[][] data = new String[rowNum][colNum]; for (int i = 0; i < rowNum; i++) { // Get the row HSSFRow row = sheet.getRow(i); for (int j = 0; j < colNum; j++) { // Get the columns or cells for the first row and keep looping // for the other rows HSSFCell cell = row.getCell(j); // Make a call to the method cellToString which actually // converts the cell contents to String String value = cellToString(cell); data[i][j] = value; // Here is where you write the logic to handle the data.I am // just printing out the contents here. System.out.println("The value is " + value); } } }
Example 11
Source File: ExcelUtil.java From phone with Apache License 2.0 | 5 votes |
/** * 读取excel到JsonArray * @param sheet * @param map 列对应的column * @param rowNumber * @param array * @return 当前的rowNumber */ private static int readSheetBody(HSSFSheet sheet, Map<Integer, String> map, int rowNumber, JSONArray array) { if (logger.isDebugEnabled()) { logger.debug("readSheetBody(HSSFSheet, Map<Integer,String>, int, JSONArray) - start"); //$NON-NLS-1$ } if(sheet!=null){ int end = sheet.getLastRowNum();//获取最后一行 for (; rowNumber<=end;rowNumber++) { HSSFRow row = sheet.getRow(rowNumber); if (row!=null) { JSONObject jsonObject = new JSONObject(); Iterator<Cell> iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); if (cell!=null) { int cellIndex = cell.getColumnIndex(); String key = map.get(cellIndex); String cellValue = getStringValue(cell); if (key!=null&&cellValue!=null&&!cellValue.equals("null")) { readSheetCell(jsonObject,key,cellValue); } } } array.add(jsonObject); } } } if (logger.isDebugEnabled()) { logger.debug("readSheetBody(HSSFSheet, Map<Integer,String>, int, JSONArray) - end"); //$NON-NLS-1$ } return rowNumber; }
Example 12
Source File: OutcomeService.java From lams with GNU General Public License v2.0 | 4 votes |
@Override @SuppressWarnings("unchecked") public int importOutcomes(MultipartFile fileItem) throws IOException { int counter = 0; POIFSFileSystem fs = new POIFSFileSystem(fileItem.getInputStream()); try (HSSFWorkbook wb = new HSSFWorkbook(fs)) { HSSFSheet sheet = wb.getSheetAt(0); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); User user = null; // make import work with files with header ("exported on") or without (pure data) HSSFRow row = sheet.getRow(startRow); HSSFCell cell = row.getCell(0); String header = cell.getStringCellValue(); startRow += "name".equalsIgnoreCase(header) ? 1 : 5; for (int i = startRow; i < (endRow + 1); i++) { row = sheet.getRow(i); cell = row.getCell(1); String code = cell.getStringCellValue(); List<Outcome> foundOutcomes = outcomeDAO.findByProperty(Outcome.class, "code", code); if (!foundOutcomes.isEmpty()) { if (log.isDebugEnabled()) { log.debug("Skipping an outcome with existing code: " + code); } continue; } cell = row.getCell(3); String scaleCode = cell.getStringCellValue(); List<OutcomeScale> foundScales = outcomeDAO.findByProperty(OutcomeScale.class, "code", scaleCode); OutcomeScale scale = foundScales.isEmpty() ? null : foundScales.get(0); if (scale == null) { if (log.isDebugEnabled()) { log.debug("Skipping an outcome with missing scale with code: " + scaleCode); } continue; } cell = row.getCell(0); String name = cell.getStringCellValue(); cell = row.getCell(2); String description = cell == null ? null : cell.getStringCellValue(); Outcome outcome = new Outcome(); outcome.setName(name); outcome.setCode(code); outcome.setDescription(description); outcome.setScale(scale); if (user == null) { UserDTO userDTO = OutcomeService.getUserDTO(); user = (User) outcomeDAO.find(User.class, userDTO.getUserID()); } outcome.setCreateBy(user); outcome.setCreateDateTime(new Date()); outcomeDAO.insert(outcome); counter++; } } return counter; }
Example 13
Source File: AllViewSheetGenerator.java From ermaster-b with Apache License 2.0 | 4 votes |
@Override public void generate(IProgressMonitor monitor, HSSFWorkbook workbook, int sheetNo, boolean useLogicalNameAsSheetName, Map<String, Integer> sheetNameMap, Map<String, ObjectModel> sheetObjectMap, ERDiagram diagram, Map<String, LoopDefinition> loopDefinitionMap) { this.clear(); LoopDefinition loopDefinition = loopDefinitionMap.get(this .getTemplateSheetName()); HSSFSheet newSheet = createNewSheet(workbook, sheetNo, loopDefinition.sheetName, sheetNameMap); sheetObjectMap.put(workbook.getSheetName(workbook .getSheetIndex(newSheet)), diagram.getDiagramContents() .getContents().getViewSet()); HSSFSheet oldSheet = workbook.getSheetAt(sheetNo); boolean first = true; for (View view : diagram.getDiagramContents().getContents() .getViewSet()) { if (first) { first = false; } else { POIUtils .copyRow(oldSheet, newSheet, loopDefinition.startLine - 1, oldSheet .getLastRowNum(), newSheet .getLastRowNum() + loopDefinition.spaceLine + 1); } this.setViewData(workbook, newSheet, view); newSheet.setRowBreak(newSheet.getLastRowNum() + loopDefinition.spaceLine); monitor.worked(1); } if (first) { for (int i = loopDefinition.startLine - 1; i <= newSheet .getLastRowNum(); i++) { HSSFRow row = newSheet.getRow(i); if (row != null) { newSheet.removeRow(row); } } } }
Example 14
Source File: AllSequencesSheetGenerator.java From ermaster-b with Apache License 2.0 | 4 votes |
@Override public void generate(IProgressMonitor monitor, HSSFWorkbook workbook, int sheetNo, boolean useLogicalNameAsSheetName, Map<String, Integer> sheetNameMap, Map<String, ObjectModel> sheetObjectMap, ERDiagram diagram, Map<String, LoopDefinition> loopDefinitionMap) { LoopDefinition loopDefinition = loopDefinitionMap.get(this .getTemplateSheetName()); HSSFSheet newSheet = createNewSheet(workbook, sheetNo, loopDefinition.sheetName, sheetNameMap); sheetObjectMap.put(workbook.getSheetName(workbook .getSheetIndex(newSheet)), diagram.getDiagramContents() .getSequenceSet()); HSSFSheet oldSheet = workbook.getSheetAt(sheetNo); boolean first = true; for (Sequence sequence : diagram.getDiagramContents().getSequenceSet()) { if (first) { first = false; } else { POIUtils .copyRow(oldSheet, newSheet, loopDefinition.startLine - 1, oldSheet .getLastRowNum(), newSheet .getLastRowNum() + loopDefinition.spaceLine + 1); } this.setSequenceData(workbook, newSheet, sequence); newSheet.setRowBreak(newSheet.getLastRowNum() + loopDefinition.spaceLine); monitor.worked(1); } if (first) { for (int i = loopDefinition.startLine - 1; i <= newSheet .getLastRowNum(); i++) { HSSFRow row = newSheet.getRow(i); if (row != null) { newSheet.removeRow(row); } } } }
Example 15
Source File: GenerateDoc.java From danyuan-application with Apache License 2.0 | 4 votes |
/** * @方法名 copySheet * @功能 复制sheet * @参数 @param sheetFrom * @参数 @param sheetTo * @参数 @return * @返回 HSSFSheet * @author Administrator * @throws */ @SuppressWarnings("deprecation") private static HSSFSheet copySheet(HSSFSheet sheetFrom, HSSFSheet sheetTo) { // 初期化 CellRangeAddress region = null; Row rowFrom = null; Row rowTo = null; Cell cellFrom = null; Cell cellTo = null; // セル結合のコピー for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) { region = sheetFrom.getMergedRegion(i); if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) { sheetTo.addMergedRegion(region); } } // セルのコピー for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) { rowFrom = sheetFrom.getRow(intRow); rowTo = sheetTo.createRow(intRow); if (null == rowFrom) { continue; } rowTo.setHeight(rowFrom.getHeight()); for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) { // セル幅のコピー sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol)); sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol)); cellFrom = rowFrom.getCell(intCol); cellTo = rowTo.createCell(intCol); if (null == cellFrom) { continue; } // セルスタイルとタイプのコピー cellTo.setCellStyle(cellFrom.getCellStyle()); cellTo.setCellType(cellFrom.getCellType()); // タイトル内容のコピー // 不同数据类型处理 int cellFromType = cellFrom.getCellType(); cellTo.setCellType(cellFromType); if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cellFrom)) { cellTo.setCellValue(cellFrom.getDateCellValue()); } else { cellTo.setCellValue(cellFrom.getNumericCellValue()); } } else if (cellFromType == HSSFCell.CELL_TYPE_STRING) { cellTo.setCellValue(cellFrom.getRichStringCellValue()); } else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) { // nothing21 } else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) { cellTo.setCellValue(cellFrom.getBooleanCellValue()); } else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) { cellTo.setCellErrorValue(cellFrom.getErrorCellValue()); } else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) { cellTo.setCellFormula(cellFrom.getCellFormula()); } else { // nothing29 } } } // 枠線の設定 sheetTo.setDisplayGridlines(false); // sheetTo.setDisplayGuts(true); // sheetTo.setDisplayRowColHeadings(true); // 剪切 // sheetTo.shiftRows(13, 15, 31, false, false, false); // Excelのズーム設定 sheetTo.setZoom(85, 100); // シートを戻る。 return sheetTo; }
Example 16
Source File: ImportService.java From lams with GNU General Public License v2.0 | 4 votes |
@Override public List parseRolesSpreadsheet(MultipartFile fileItem, String sessionId) throws IOException { results = new ArrayList<>(); HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); ImportService.log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow); setupImportStatus(sessionId, endRow - startRow); UserDTO userDTO = (UserDTO) SessionManager.getSession(sessionId).getAttribute(AttributeNames.USER); HSSFRow row; List<String> roles; int successful = 0; for (int i = startRow + 1; i < (endRow + 1); i++) { emptyRow = true; hasError = false; rowResult = new ArrayList<>(); row = sheet.getRow(i); String login = parseStringCell(row.getCell(ImportService.LOGIN)); String orgId = parseStringCell(row.getCell(ImportService.ORGANISATION)); roles = parseRolesCell(row.getCell(ImportService.ROLES)); if (emptyRow) { ImportService.log.debug("Row " + i + " is empty."); break; } if (hasError) { ImportService.log.debug("Row " + i + " has an error which has been sent to the browser."); results.add(rowResult); writeErrorsAuditLog(i + 1, rowResult, userDTO); updateImportStatus(sessionId, results.size()); continue; } else { try { saveUserRoles(isSysadmin(sessionId), login, orgId, roles, row); successful++; } catch (Exception e) { ImportService.log.error("Unable to assign roles to user: " + login, e); rowResult.add(messageService.getMessage("error.fail.add")); } if (rowResult.size() > 0) { if (ImportService.log.isDebugEnabled()) { ImportService.log.debug("Row " + i + " has " + rowResult.size() + " messages."); } writeErrorsAuditLog(i + 1, rowResult, userDTO); } results.add(rowResult); updateImportStatus(sessionId, results.size()); } } ImportService.log.debug("Found " + results.size() + " users in spreadsheet."); writeSuccessAuditLog(successful, userDTO, "audit.successful.role.import"); return results; }
Example 17
Source File: ImportService.java From lams with GNU General Public License v2.0 | 4 votes |
@Override public List parseUserSpreadsheet(MultipartFile fileItem, String sessionId) throws IOException { results = new ArrayList<>(); HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); setupImportStatus(sessionId, endRow - startRow); UserDTO userDTO = (UserDTO) SessionManager.getSession(sessionId).getAttribute(AttributeNames.USER); ImportService.log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow); HSSFRow row; User user = null; int successful = 0; for (int i = startRow + 1; i < (endRow + 1); i++) { emptyRow = true; hasError = false; rowResult = new ArrayList<>(); row = sheet.getRow(i); user = parseUser(row, i); if (emptyRow) { ImportService.log.debug("Row " + i + " is empty."); break; } if (hasError) { ImportService.log.debug("Row " + i + " has an error which has been sent to the browser."); results.add(rowResult); writeErrorsAuditLog(i + 1, rowResult, userDTO); updateImportStatus(sessionId, results.size()); continue; } else { try { service.saveUser(user); successful++; writeAuditLog(user, userDTO); ImportService.log.debug("Row " + i + " saved user: " + user.getLogin()); } catch (Exception e) { ImportService.log.debug(e); rowResult.add(messageService.getMessage("error.fail.add")); } if (rowResult.size() > 0) { if (ImportService.log.isDebugEnabled()) { ImportService.log.debug("Row " + i + " has " + rowResult.size() + " messages."); } writeErrorsAuditLog(i + 1, rowResult, userDTO); } results.add(rowResult); updateImportStatus(sessionId, results.size()); } } ImportService.log.debug("Found " + results.size() + " users in spreadsheet."); writeSuccessAuditLog(successful, userDTO, "audit.successful.user.import"); return results; }
Example 18
Source File: ImportService.java From lams with GNU General Public License v2.0 | 4 votes |
@Override public List parseGroupSpreadsheet(MultipartFile fileItem, String sessionId) throws IOException { results = new ArrayList<>(); parentOrg = service.getRootOrganisation(); HSSFSheet sheet = getSheet(fileItem); int startRow = sheet.getFirstRowNum(); int endRow = sheet.getLastRowNum(); UserDTO userDTO = (UserDTO) SessionManager.getSession(sessionId).getAttribute(AttributeNames.USER); ImportService.log.debug("Parsing spreadsheet rows " + startRow + " through " + endRow); HSSFRow row; Organisation org = null; int successful = 0; for (int i = startRow + 1; i < (endRow + 1); i++) { emptyRow = true; hasError = false; rowResult = new ArrayList<>(); row = sheet.getRow(i); if (row != null) { org = parseGroup(row, i); } // an empty row signifies a new group if (emptyRow) { ImportService.log.debug("Row " + i + " is empty."); parentOrg = service.getRootOrganisation(); continue; } if (hasError) { ImportService.log.debug("Row " + i + " has an error which has been sent to the browser."); results.add(rowResult); continue; } else { org = service.saveOrganisation(org, getCurrentUserId()); successful++; rowResult.add(org.getOrganisationId().toString()); rowResult.add(org.getName()); rowResult.add(org.getParentOrganisation().getOrganisationId().toString()); rowResult.add(org.getOrganisationType().getOrganisationTypeId().toString()); writeOrgAuditLog(org, userDTO); // if we just added a group, then the rows under it become it's subgroups if (parentOrg.getOrganisationType().getOrganisationTypeId().equals(OrganisationType.ROOT_TYPE)) { parentOrg = org; } results.add(rowResult); } } ImportService.log.debug("Found " + results.size() + " orgs in spreadsheet."); writeSuccessAuditLog(successful, userDTO, "audit.successful.organisation.import"); return results; }
Example 19
Source File: OfficeConverter.java From BBSSDK-for-Android with Apache License 2.0 | 4 votes |
private void xlsToHtml() throws Throwable { FileOutputStream output = new FileOutputStream(new File(htmlPath)); StringBuffer htmlHeaderSB = new StringBuffer(); htmlHeaderSB.append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' " + "xmlns='http://www.w3.org/TR/REC-html40'>"); htmlHeaderSB.append("<head><meta http-equiv=Content-Type content='text/html; charset=utf-8'><meta name=ProgId content=Excel.Sheet>" + "</head><body>"); output.write(htmlHeaderSB.toString().getBytes()); HSSFSheet sheet; HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(filePath)); // 获整个Excel for (int sheetIndex = 0; sheetIndex < workbook.getNumberOfSheets(); sheetIndex++) { if (workbook.getSheetAt(sheetIndex) != null) { sheet = workbook.getSheetAt(sheetIndex);// 获得不为空的这个sheet if (sheet != null) { int firstRowNum = sheet.getFirstRowNum(); // 第一行 int lastRowNum = sheet.getLastRowNum(); // 最后一行 // 构造Table output.write(("<table width=\"100%\" style=\"border:1px solid #000;border-width:1px 0 0 1px;margin:2px 0 2px 0;" + "border-collapse:collapse;\">").getBytes()); for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) { if (sheet.getRow(rowNum) != null) {// 如果行不为空, HSSFRow row = sheet.getRow(rowNum); short firstCellNum = row.getFirstCellNum(); // 该行的第一个单元格 short lastCellNum = row.getLastCellNum(); // 该行的最后一个单元格 int height = (int) (row.getHeight() / 15.625); // 行的高度 output.write(("<tr height=\"" + height + "\" style=\"border:1px solid #000;border-width:0 1px 1px 0;" + "margin:2px 0 2px 0;\">").getBytes()); for (short cellNum = firstCellNum; cellNum <= lastCellNum; cellNum++) { // 循环该行的每一个单元格 HSSFCell cell = row.getCell(cellNum); if (cell != null) { if (cell.getCellType() != HSSFCell.CELL_TYPE_BLANK) { StringBuffer tdStyle = new StringBuffer("<td style=\"border:1px solid #000; border-width:0 1px 1px 0;" + "margin:2px 0 2px 0; "); HSSFCellStyle cellStyle = cell.getCellStyle(); HSSFPalette palette = workbook.getCustomPalette(); // 类HSSFPalette用于求颜色的国际标准形式 HSSFColor hColor = palette.getColor(cellStyle.getFillForegroundColor()); HSSFColor hColor2 = palette.getColor(cellStyle.getFont(workbook).getColor()); String bgColor = convertToStardColor(hColor);// 背景颜色 short boldWeight = cellStyle.getFont(workbook).getBoldweight(); // 字体粗细 short fontHeight = (short) (cellStyle.getFont(workbook).getFontHeight() / 2); // 字体大小 String fontColor = convertToStardColor(hColor2); // 字体颜色 if (bgColor != null && !"".equals(bgColor.trim())) { tdStyle.append(" background-color:"); tdStyle.append(bgColor); tdStyle.append("; "); } if (fontColor != null && !"".equals(fontColor.trim())) { tdStyle.append(" color:"); tdStyle.append(fontColor); tdStyle.append("; "); } tdStyle.append(" font-weight:"); tdStyle.append(boldWeight); tdStyle.append("; "); tdStyle.append(" font-size: "); tdStyle.append(fontHeight); tdStyle.append("%;"); output.write((tdStyle + "\"").getBytes()); int width = (int) (sheet.getColumnWidth(cellNum) / 35.7); // int cellRegionCol = getMergerCellRegionCol(sheet, rowNum, cellNum); // 合并的列(solspan) int cellRegionRow = getMergerCellRegionRow(sheet, rowNum, cellNum);// 合并的行(rowspan) String align = convertAlignToHtml(cellStyle.getAlignment()); // String vAlign = convertVerticalAlignToHtml(cellStyle.getVerticalAlignment()); output.write((" align=\"" + align + "\" valign=\"" + vAlign + "\" width=\"" + width + "\" ").getBytes()); output.write((" colspan=\"" + cellRegionCol + "\" rowspan=\"" + cellRegionRow + "\"").getBytes()); output.write((">" + getCellValue(cell) + "</td>").getBytes()); } } } output.write("</tr>".getBytes()); } } output.write(("</table>").getBytes()); } } } output.write(("</body></html>").getBytes()); output.close(); }
Example 20
Source File: ExcelHandle.java From danyuan-application with Apache License 2.0 | 4 votes |
@SuppressWarnings({ "resource", "deprecation" }) public List<String[]> readXLS(String path, int num) throws FileNotFoundException, IOException { File file = new File(path); POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem); HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(num); int rowstart = hssfSheet.getFirstRowNum(); int rowEnd = hssfSheet.getLastRowNum(); for (int i = rowstart; i <= rowEnd; i++) { HSSFRow row = hssfSheet.getRow(i); if (null == row) { continue; } int cellStart = row.getFirstCellNum(); int cellEnd = row.getLastCellNum(); for (int k = cellStart; k <= cellEnd; k++) { HSSFCell cell = row.getCell(k); if (null == cell) { continue; } System.out.print("" + k + " "); // System.out.print("type:"+cell.getCellType()); switch (cell.getCellTypeEnum()) { case NUMERIC: // 数字 System.out.print(cell.getNumericCellValue() + " "); break; case STRING: // 字符串 System.out.print(cell.getStringCellValue() + " "); break; case BOOLEAN: // Boolean System.out.println(cell.getBooleanCellValue() + " "); break; case FORMULA: // 公式 System.out.print(cell.getCellFormula() + " "); break; case BLANK: // 空值 System.out.println(" "); break; case ERROR: // 故障 System.out.println(" "); break; default: System.out.print("未知类型 "); break; } } System.out.print("\n"); } return null; }