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