private Map<ColumnType, Integer> getColumnInfo(HSSFSheet sheet) { int firstRow = sheet.getFirstRowNum(); HSSFRow row = sheet.getRow(firstRow); Map<ColumnType, Integer> columnHeaders = new EnumMap<ColumnType, Integer>(ColumnType.class); for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); ++i) { HSSFCell cell = row.getCell(i); if (cell != null) { String value = cell.getStringCellValue(); if (value != null) { Integer colNum = Integer.valueOf(i); value = value.trim().toLowerCase(Locale.ENGLISH); if (value.startsWith("group")) { columnHeaders.put(ColumnType.GROUP_COLUMN, colNum); } else if (value.startsWith("artifact")) { columnHeaders.put(ColumnType.ARTIFACT_COLUMN, colNum); } else if (value.startsWith("type")) { columnHeaders.put(ColumnType.TYPE_COLUMN, colNum); } else if (value.startsWith("version")) { columnHeaders.put(ColumnType.VERSION_COLUMN, colNum); } else if (value.startsWith("classifier") || value.startsWith("alternate")) { columnHeaders.put(ColumnType.CLASSIFIER_COLUMN, colNum); } else if (value.startsWith("digest")) { columnHeaders.put(ColumnType.DIGEST_COLUMN, colNum); } if (columnHeaders.size() == 6) { return columnHeaders; } } } } if (columnHeaders.size() >= 3) { return columnHeaders; } throw new BuildException( "Input yank xls file (" + xlsFile + ") does not contains GroupId, ArtifactId, or Version columns"); }
public void batchUploadHolHoldays() throws IOException { if (file != null && file.length() > 0) { HSSFRow row; // HSSFCell cell; InputStream is = new FileInputStream(file); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); // 获得excel中的第一张表 HSSFSheet sheet = hssfWorkbook.getSheetAt(0); List<HolHoliday> saveList = new ArrayList<HolHoliday>(); String yearError = ""; String repeatError = ""; String dayError = ""; String loginNameError = ""; String deptError = ""; String operatorId = servletRequest.getParameter("userId"); for (int i = sheet.getFirstRowNum() + 1; i < sheet.getPhysicalNumberOfRows(); i++) { HolHoliday holHoliday = new HolHoliday(); row = sheet.getRow(i); String year = getCellData(row, 0); // 年度 String loginName = getCellData(row, 1); // 工号 String dayCounts = getCellData(row, 2); // 天数 String remark = getCellData(row, 3); // 备注 String reg = "[0-9]{4}"; Pattern p = Pattern.compile(reg); Matcher m = p.matcher(year); boolean yearFlag = m.matches(); if (!yearFlag) { yearError += (i + 1) + ","; continue; } List<CsUser> userList = holHolidayService.findUsersByLoginName(loginName); if (userList == null || userList.size() < 1) { loginNameError += (i + 1) + ","; continue; } CsUser csUser = userList.get(0); List<HolHoliday> list = holHolidayService.findByYearAndHolPersonId(year, csUser.getId() + ""); if (list != null && list.size() > 0) { repeatError += (i + 1) + ","; continue; } // holHoliday.setHolName(csUser.getName()); // holHoliday.setHolLoginName(csUser.getLoginName()); // holHoliday.setDeptName(csUser.getDept()); holHoliday.setHolId(csUser.getId() + ""); // holHoliday.setHolId(loginName); if (!StringUtils.isNumeric(dayCounts)) { dayCounts += (i + 1) + ","; continue; } holHoliday.setHolYear(year); holHoliday.setHolDays(Long.valueOf(dayCounts)); holHoliday.setRemark(remark); HolHoliday last = this.holHolidayService.findLastHolidaysSetByholPersonId(loginName); // 同工号,上一次的数据 if (last != null) { holHoliday.setHolDaysLeft(last.getHolDaysLeft() + holHoliday.getHolDays()); holHoliday.setHolDaysWait(last.getHolDaysWait()); } else { holHoliday.setHolDaysLeft(holHoliday.getHolDays()); holHoliday.setHolDaysWait(0l); } holHoliday.setRemoved(0l); if (StringUtils.isNotEmpty(operatorId)) { holHoliday.setOperator(Long.valueOf(operatorId)); } holHoliday.setOperateTime(sdf.format(new Date())); saveList.add(holHoliday); } servletResponse.setCharacterEncoding("utf-8"); // 务必,防止返回文件名是乱码 servletResponse.setContentType("ajax"); String msg = ""; if (StringUtils.isEmpty(yearError) && StringUtils.isEmpty(dayError) && StringUtils.isEmpty(repeatError) && StringUtils.isEmpty(loginNameError)) { try { holHolidayService.saveAll(saveList); msg = "上传成功!共有" + saveList.size() + "条数据入库!"; servletResponse.getWriter().write("{\"message\":\"success\",\"info\":\"" + msg + "\"}"); } catch (Exception e) { e.printStackTrace(); } } else { String error1 = "", error2 = "", error3 = "", error4 = ""; if (StringUtils.isNotEmpty(yearError)) { error1 = "第" + yearError.substring(0, yearError.length() - 1) + "行,年份格式错误!"; } if (StringUtils.isNotEmpty(loginNameError)) { error4 = "第" + loginNameError.substring(0, loginNameError.length() - 1) + "行,该用户不存在!"; } if (StringUtils.isNotEmpty(repeatError)) { error2 = "第" + repeatError.substring(0, repeatError.length() - 1) + "行,数据重复!该用户在该年份下已设置过公休!"; } if (StringUtils.isNotEmpty(dayError)) { error3 = "第" + dayError.substring(0, dayError.length() - 1) + "行,公休天数格式错误!"; } servletResponse .getWriter() .write( "{\"message\":\"error\",\"error1\":\"" + error1 + "\",\"error2\":\"" + error2 + "\",\"error3\":\"" + error3 + "\",\"error4\":\"" + error4 + "\"}"); } } }
/** Retrieves the text contents of the file */ public String getText() { StringBuffer text = new StringBuffer(); // We don't care about the difference between // null (missing) and blank cells _wb.setMissingCellPolicy(HSSFRow.RETURN_BLANK_AS_NULL); // Process each sheet in turn for (int i = 0; i < _wb.getNumberOfSheets(); i++) { HSSFSheet sheet = _wb.getSheetAt(i); if (sheet == null) { continue; } if (_includeSheetNames) { String name = _wb.getSheetName(i); if (name != null) { text.append(name); text.append("\n"); } } // Header text, if there is any if (_includeHeadersFooters) { text.append(_extractHeaderFooter(sheet.getHeader())); } int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); for (int j = firstRow; j <= lastRow; j++) { HSSFRow row = sheet.getRow(j); if (row == null) { continue; } // Check each cell in turn int firstCell = row.getFirstCellNum(); int lastCell = row.getLastCellNum(); if (_includeBlankCells) { firstCell = 0; } for (int k = firstCell; k < lastCell; k++) { HSSFCell cell = row.getCell(k); boolean outputContents = true; if (cell == null) { // Only output if requested outputContents = _includeBlankCells; } else { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: text.append(cell.getRichStringCellValue().getString()); break; case HSSFCell.CELL_TYPE_NUMERIC: text.append(_formatter.formatCellValue(cell)); break; case HSSFCell.CELL_TYPE_BOOLEAN: text.append(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: text.append(ErrorEval.getText(cell.getErrorCellValue())); break; case HSSFCell.CELL_TYPE_FORMULA: if (!_shouldEvaluateFormulas) { text.append(cell.getCellFormula()); } else { switch (cell.getCachedFormulaResultType()) { case HSSFCell.CELL_TYPE_STRING: HSSFRichTextString str = cell.getRichStringCellValue(); if (str != null && str.length() > 0) { text.append(str.toString()); } break; case HSSFCell.CELL_TYPE_NUMERIC: HSSFCellStyle style = cell.getCellStyle(); if (style == null) { text.append(cell.getNumericCellValue()); } else { text.append( _formatter.formatRawCellContents( cell.getNumericCellValue(), style.getDataFormat(), style.getDataFormatString())); } break; case HSSFCell.CELL_TYPE_BOOLEAN: text.append(cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: text.append(ErrorEval.getText(cell.getErrorCellValue())); break; } } break; default: throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")"); } // Output the comment, if requested and exists HSSFComment comment = cell.getCellComment(); if (_includeCellComments && comment != null) { // Replace any newlines with spaces, otherwise it // breaks the output String commentText = comment.getString().getString().replace('\n', ' '); text.append(" Comment by " + comment.getAuthor() + ": " + commentText); } } // Output a tab if we're not on the last cell if (outputContents && k < (lastCell - 1)) { text.append("\t"); } } // Finish off the row text.append("\n"); } // Finally Footer text, if there is any if (_includeHeadersFooters) { text.append(_extractHeaderFooter(sheet.getFooter())); } } return text.toString(); }
/** * Construct Statements based on given Excel File. * * @param file Given Excel File * @return the List of constructed Statements. Empty list if fail. */ public static List<Statements> newInstanceFromExcelFile(File file) { FileInputStream fileInputStream = null; final List<Statements> statementsList = new ArrayList<Statements>(); try { fileInputStream = new FileInputStream(file); final POIFSFileSystem fs = new POIFSFileSystem(fileInputStream); final HSSFWorkbook wb = new HSSFWorkbook(fs); final int numberOfSheets = wb.getNumberOfSheets(); for (int k = 0; k < numberOfSheets; k++) { final HSSFSheet sheet = wb.getSheetAt(k); final int startRow = sheet.getFirstRowNum(); final int endRow = sheet.getLastRowNum(); // If there are 3 rows, endRow will be 2. // We must have at least 2 rows. (endRow = 1) if (startRow != 0 || endRow <= startRow) { continue; } final HSSFRow row = sheet.getRow(startRow); if (row == null) { continue; } final int startCell = row.getFirstCellNum(); final int endCell = row.getLastCellNum(); // If there are 2 cols, endCell will be 2. // We must have at least 1 col. (endCell = 1) if (startCell != 0 || endCell <= startCell) { continue; } final List<String> types = new ArrayList<String>(); for (int i = startCell; i < endCell; i++) { final HSSFCell cell = row.getCell(i); if (cell == null) { continue; } // Exception may be thrown here, as cell may be numerical value. final String type = cell.getRichStringCellValue().getString(); if (type != null) { types.add(type); } } if (types.isEmpty()) { continue; } if (types.size() != (endCell - startCell)) { continue; } final Statement.What what = Statement.what(types); Statements s = new Statements(what.type, what.guiBundleWrapper); for (int i = startRow + 1; i <= endRow; i++) { final HSSFRow r = sheet.getRow(i); if (r == null) { continue; } final List<Atom> atoms = new ArrayList<Atom>(); for (int j = startCell; j < endCell; j++) { final HSSFCell cell = r.getCell(j); if (cell == null) { continue; } Object value = null; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { final HSSFRichTextString richString = cell.getRichStringCellValue(); if (richString != null) { value = richString.getString(); } else { value = ""; } } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { try { value = new Double(cell.getNumericCellValue()); } catch (NumberFormatException ex) { log.error(null, ex); value = new Double(0.0); } } else { } if (null == value) { continue; } atoms.add(new Atom(value, types.get(j - startCell))); } final Statement statement = new Statement(atoms); if (s.getType() != statement.getType()) { // Give up. s = null; break; } s.statements.add(statement); } // for (int i = startRow + 1; i <= endRow; i++) if (s != null) { statementsList.add(s); } } /* for(int k = 0; k < numberOfSheets; k++) */ } catch (Exception ex) { log.error(null, ex); } finally { org.yccheok.jstock.gui.Utils.close(fileInputStream); } return statementsList; }
/** * return the index of the first row containing data * * @return */ public int getFirstRow() { return sheet.getFirstRowNum(); }
@Override public List<Artifact> getArtifactList(Project project, File spreadsheet) throws IOException { xlsFile = spreadsheet; BufferedInputStream bis = null; HSSFWorkbook workBook = null; List<Artifact> artifacts = new ArrayList<Artifact>(); try { bis = new BufferedInputStream(new FileInputStream(xlsFile)); workBook = new HSSFWorkbook(bis); HSSFSheet sheet = workBook.getSheetAt(0); Map<ColumnType, Integer> columnHeaders = getColumnInfo(sheet); Integer typeColumn = columnHeaders.get(ColumnType.TYPE_COLUMN); Integer classifierColumn = columnHeaders.get(ColumnType.CLASSIFIER_COLUMN); Integer digestColumn = columnHeaders.get(ColumnType.DIGEST_COLUMN); String groupId = ""; String artifactId = ""; String type = JAR; String version = ""; String classifier = ""; String digest = ""; for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); ++i) { HSSFRow row = sheet.getRow(i); if (row != null) { HSSFCell cell = row.getCell(columnHeaders.get(ColumnType.GROUP_COLUMN).intValue()); if (cell != null) { String gId = cell.getStringCellValue().trim(); if (!gId.isEmpty()) { groupId = gId; } } cell = row.getCell(columnHeaders.get(ColumnType.ARTIFACT_COLUMN).intValue()); if (cell != null) { String aId = cell.getStringCellValue().trim(); if (!aId.isEmpty()) { artifactId = aId; } } cell = row.getCell(columnHeaders.get(ColumnType.VERSION_COLUMN).intValue()); if (cell != null) { String v; if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { v = String.valueOf(cell.getNumericCellValue()); } else { v = cell.getStringCellValue().trim(); } if (!v.isEmpty()) { version = v; } } cell = (typeColumn != null) ? row.getCell(typeColumn.intValue()) : null; if (cell != null) { type = cell.getStringCellValue().trim(); } cell = (classifierColumn != null) ? row.getCell(classifierColumn.intValue()) : null; if (cell != null) { classifier = cell.getStringCellValue().trim(); } cell = (digestColumn != null) ? row.getCell(digestColumn.intValue()) : null; if (cell != null) { digest = cell.getStringCellValue().trim(); } if (groupId.isEmpty() || artifactId.isEmpty() || version.isEmpty()) { if (groupId.isEmpty() || version.isEmpty()) { project.log( "Row " + row.getRowNum() + ": Invalid artifact specified: [groupId: " + groupId + ", artifactId: " + artifactId + ", classifier: " + classifier + ", version: " + version + ", digest: " + digest + "]"); } } else { artifacts.add(new Artifact(groupId, artifactId, type, classifier, version, digest)); } } artifactId = ""; classifier = ""; digest = ""; type = JAR; } project.log(sheet.getLastRowNum() + " rows read from " + xlsFile, Project.MSG_VERBOSE); } finally { if (workBook != null) { workBook.close(); } Closer.close(bis); } return artifacts; }