Java Code Examples for org.apache.poi.hssf.usermodel.HSSFSheet#getLastRowNum()

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;
}
 

@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());
    }
}
 

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;
}
 

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;
}
 

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;
}
 

/**
 * @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);
	}
}
 

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
								}
							}
						}
					}
				}
			}
		}
	}
}
 

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;
   }
 

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));
    }
}
 

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);
		}
	}
}
 

/**
 * 读取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;
}
 

@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;
   }
 

@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);
			}
		}
	}
}
 

@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);
			}
		}
	}
}
 

/**
 * @方法名 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;
	
}
 

@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;
   }
 

@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;
   }
 

@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;
   }
 

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();
}
 

@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;

}