@Override
public void writeRow(List<?> row) {
Row record = sheet.createRow(sheet.getLastRowNum() + 1);
for (int i = 0; i < row.size(); i++) {
Cell cell = record.createCell(i);
Object value = row.get(i);
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(converters.convert(value, Double.class));
} else if (value instanceof Date || value instanceof DateTime || value instanceof Calendar) {
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellStyle(dateCellStyle);
cell.setCellValue(converters.convert(value, Date.class));
} else if (value instanceof Boolean) {
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
cell.setCellValue((Boolean) value);
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(converters.convert(value, String.class));
}
}
}
@Override
public void writeHeader() {
if (header != null) {
Row record = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
Cell cell = record.createCell(i);
cell.setCellValue(header.get(i));
}
}
}
@Override
public List<String> getAnchors() {
List<String> anchors = new ArrayList<>();
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
anchors.add(workbook.getSheetName(i));
}
return anchors;
}
private List<ExcelHeader> getHeaderList(Class clz) {
List<ExcelHeader> headers = new ArrayList<ExcelHeader>();
// 获取全部get/is方法
Method[] ms = clz.getDeclaredMethods();
for (Method m : ms) {
String mn = m.getName();
if (mn.startsWith("get") || mn.startsWith("is")) {
if (m.isAnnotationPresent(ExcelResources.class)) {
ExcelResources er = m.getAnnotation(ExcelResources.class);
headers.add(new ExcelHeader(er.title(), er.order(), mn));
}
}
}
return headers;
}
@Override
public void writeItem(Map<String, ?> item) {
List<Object> row = new ArrayList<>();
for (String key : getHeader()) {
row.add(item.get(key));
}
if (this.type != null) {
for (int i = 0; i < row.size(); i++) {
Class clazz = (i < this.type.size() ? this.type.get(i) : null);
if (clazz != null) row.set(i, converters.convert(row.get(i), clazz));
}
}
writeRow(row);
}
public List<Object> readExcel(Workbook wb, Class clz, int readLine, int tailLine) {
Sheet sheet = wb.getSheetAt(0); // 取第一张表
List<Object> objs = null;
try {
Row row = sheet.getRow(readLine); // 开始行，主题栏
objs = new ArrayList<Object>();
Map<Integer, String> maps = getHeaderMap(row, clz); // 设定对应的字段顺序与方法名
if (maps == null || maps.size() <= 0)
throw new RuntimeException("要读取的Excel的格式不正确，检查是否设定了合适的行"); // 与order顺序不符
for (int i = readLine + 1; i <= sheet.getLastRowNum() - tailLine; i++) { // 取数据
row = sheet.getRow(i);
Object obj = clz.newInstance(); // 调用无参结构
for (Cell c : row) {
int ci = c.getColumnIndex();
String mn = maps.get(ci).substring(3); // 消除get
mn = mn.substring(0, 1).toLowerCase() + mn.substring(1);
Map<String, Object> params = new HashMap<String, Object>();
if (!"enterDate".equals(mn)) c.setCellType(Cell.CELL_TYPE_STRING); // 设置单元格格式
else c.setCellType(Cell.CELL_TYPE_NUMERIC);
if (this.getCellValue(c).trim().equals("是")) {
BeanUtils.copyProperty(obj, mn, 1);
} else if (this.getCellValue(c).trim().equals("否")) {
BeanUtils.copyProperty(obj, mn, 0);
} else BeanUtils.copyProperty(obj, mn, this.getCellValue(c));
}
objs.add(obj);
}
} catch (InstantiationException e) {
e.printStackTrace();
logger.error(e);
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NumberFormatException e) {
e.printStackTrace();
logger.error(e);
}
return objs;
}
private Collection<Object[]> loadFromSpreadsheet(final InputStream excelFile) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook(excelFile);
data = new ArrayList<Object[]>();
Sheet sheet = workbook.getSheetAt(0);
int numberOfColumns = countNonEmptyColumns(sheet);
List<Object[]> rows = new ArrayList<Object[]>();
List<Object> rowData = new ArrayList<Object>();
for (Row row : sheet) {
if (isEmpty(row)) {
break;
} else {
rowData.clear();
for (int column = 0; column < numberOfColumns; column++) {
Cell cell = row.getCell(column);
rowData.add(objectFrom(workbook, cell));
}
rows.add(rowData.toArray());
}
}
return rows;
}
public void readProcessSpreadSheet() {
processSpreadSheet = System.getProperty("processSpreadSheet");
System.out.println("processSpreadSheet: " + processSpreadSheet);
if (processCodesList.size() == 0) {
if (processSpreadSheet != null && !processSpreadSheet.equals("")) {
Workbook wb1 = null;
try {
wb1 = new XSSFWorkbook(processSpreadSheet);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Sheet sheet = wb1.getSheetAt(0);
Row row;
Cell cell;
int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();
int cols = 0; // No of columns
int tmp = 0;
// This trick ensures that we get the data properly even if it
// doesn't start from first few rows
for (int i = 0; i < 10 || i < rows; i++) {
row = sheet.getRow(i);
if (row != null) {
tmp = sheet.getRow(i).getPhysicalNumberOfCells();
// out.println("tmp value"+tmp);
if (tmp > cols) {
cols = tmp;
}
}
}
ProcessDefinition tempProcessCode;
for (int r1 = 0; r1 < rows; r1++) {
tempProcessCode = new ProcessDefinition();
row = sheet.getRow(r1);
if (row != null) {
if (row.getCell(0) != null) {
for (int counter = 0; counter < cols; counter++) {
cell = row.getCell((short) counter);
// cell = row.getCell(1);
if (counter == 0) {
if (cell != null) {
tempProcessCode.setProcessName(cell.getStringCellValue());
} else {
tempProcessCode.setProcessName("");
}
} else if (counter == 1) {
if (cell != null) {
tempProcessCode.setProcessCode(cell.getStringCellValue());
} else {
tempProcessCode.setProcessCode("");
}
} else if (counter == 2) {
if (cell != null) {
tempProcessCode.setIaeaCode(cell.getStringCellValue());
} else {
tempProcessCode.setIaeaCode("");
}
} else if (counter == 3) {
if (cell != null) {
tempProcessCode.setProcessDescription(cell.getStringCellValue());
// System.out.println(tempProcessCode.getProcessDescription());
} else {
tempProcessCode.setProcessDescription("");
// System.out.println("Process Description EMpty");
}
}
}
}
} else {
rows++;
}
processCodesList.add(tempProcessCode);
}
}
}
System.out.println(processCodesList.size());
}
private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
XSSFWorkbook wb = null;
try {
if (isXssf) {
XSSFWorkbook w = new XSSFWorkbook();
} else {
HSSFWorkbook w = new HSSFWorkbook();
}
wb = new XSSFWorkbook();
XSSFDataFormat format = wb.createDataFormat();
XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
XSSFDataFormat passwordFormat = wb.createDataFormat();
passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
List<ExcelHeader> headers = getHeaderList(clz);
Collections.sort(headers);
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
Row r0 = sheet.createRow(0);
Cell cell = r0.createCell(0);
r0.setHeightInPoints(28);
cell.setCellValue(message + "备份记录");
Row r = sheet.createRow(1);
r.setHeightInPoints(25);
cell.setCellStyle(cellStyle);
// 输出标题
for (int i = 0; i < headers.size(); i++) {
Cell cell1 = r.createCell(i);
if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
else cell1.setCellStyle(cellStyle);
cell1.setCellValue(headers.get(i).getTitle());
}
Object obj = null;
// 输出用户资料信息
if (message.indexOf("用户资料 ") > 0) {
sheet.setColumnWidth(3, 32 * 150);
sheet.setColumnWidth(4, 32 * 110);
sheet.setColumnWidth(7, 32 * 120);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
copyDefaultCellStyle(null, cell2, cellStyle, 0);
if (getMethodName(headers.get(j)).equals("nabled"))
cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
else if (getMethodName(headers.get(j)).equals("password")) {
cell2.setCellStyle(passwordCellStyle);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
} else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 输出房间使用信息数据
else {
sheet.setColumnWidth(0, 32 * 80);
sheet.setColumnWidth(2, 32 * 100);
sheet.setColumnWidth(3, 32 * 190);
sheet.setColumnWidth(4, 32 * 190);
sheet.setColumnWidth(5, 32 * 190);
sheet.setColumnWidth(10, 32 * 130);
for (int i = 0; i < objs.size(); i++) {
r = sheet.createRow(i + 2);
obj = objs.get(i);
for (int j = 0; j < headers.size(); j++) {
Cell cell2 = r.createCell(j);
if (j == 3 || j == 4 || j == 5) {
XSSFCellStyle cs3 = wb.createCellStyle();
cell2.setCellValue(new Date());
copyDefaultCellStyle(format, cell2, cs3, 1);
}
if (j == 10) {
XSSFCellStyle cs2 = wb.createCellStyle();
copyDefaultCellStyle(format, cell2, cs2, 2);
}
copyDefaultCellStyle(null, cell2, cellStyle, 0);
cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
}
}
}
// 设置行列的默认宽度和高度
} catch (IllegalAccessException e) {
e.printStackTrace();
logger.error(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
logger.error(e);
} catch (NoSuchMethodException e) {
e.printStackTrace();
logger.error(e);
}
return wb;
}
private String[] createXLSRowComments(List<String> selects, QueryResultRow row) {
List<String> commentsData = new ArrayList<>();
PartRevision part = row.getPartRevision();
PartIteration lastIteration = part.getLastIteration();
for (String select : selects) {
if (select.equals(QueryField.CTX_SERIAL_NUMBER)) {
String path = row.getPath();
if (path != null && !path.isEmpty()) {
commentsData.add(path);
}
} else if (select.startsWith(QueryField.PART_REVISION_ATTRIBUTES_PREFIX)) {
String attributeSelectType =
select
.substring(0, select.indexOf("."))
.substring(QueryField.PART_REVISION_ATTRIBUTES_PREFIX.length());
String attributeSelectName = select.substring(select.indexOf(".") + 1);
StringBuilder commentsSbattr = new StringBuilder();
if (lastIteration != null) {
List<InstanceAttribute> attributes = lastIteration.getInstanceAttributes();
if (attributes != null) {
for (InstanceAttribute attribute : attributes) {
InstanceAttributeDescriptor attributeDescriptor =
new InstanceAttributeDescriptor(attribute);
if (attributeDescriptor.getName().equals(attributeSelectName)
&& attributeDescriptor.getStringType().equals(attributeSelectType)) {
commentsSbattr.append(attribute.getId() + "|");
}
}
}
}
String commentsContent = commentsSbattr.toString().trim();
if (commentsContent.length() > 0) {
commentsContent = commentsContent.substring(0, commentsContent.lastIndexOf("|"));
}
commentsData.add(commentsContent);
} else if (select.startsWith(QueryField.PATH_DATA_ATTRIBUTES_PREFIX)) {
String attributeSelectType =
select
.substring(0, select.indexOf("."))
.substring(QueryField.PATH_DATA_ATTRIBUTES_PREFIX.length());
String attributeSelectName = select.substring(select.indexOf(".") + 1);
PathDataIteration pdi = row.getPathDataIteration();
StringBuilder commentsSbpattr = new StringBuilder();
if (pdi != null) {
List<InstanceAttribute> attributes = pdi.getInstanceAttributes();
if (attributes != null) {
for (InstanceAttribute attribute : attributes) {
InstanceAttributeDescriptor attributeDescriptor =
new InstanceAttributeDescriptor(attribute);
if (attributeDescriptor.getName().equals(attributeSelectName)
&& attributeDescriptor.getStringType().equals(attributeSelectType)) {
commentsSbpattr.append(attribute.getId() + "|");
}
}
}
}
String commentsContent = commentsSbpattr.toString().trim();
if (commentsContent.length() > 0) {
commentsContent = commentsContent.substring(0, commentsContent.lastIndexOf("|"));
}
commentsData.add(commentsContent);
} else {
commentsData.add("");
}
}
String commentsRowData = StringUtils.join(commentsData, ";");
return commentsRowData.split(";");
}
private String[] createXLSRow(List<String> selects, QueryResultRow row, String baseURL) {
List<String> data = new ArrayList<>();
PartRevision part = row.getPartRevision();
PartIteration lastCheckedInIteration = part.getLastCheckedInIteration();
PartIteration lastIteration = part.getLastIteration();
QueryContext context = row.getContext();
for (String select : selects) {
switch (select) {
case QueryField.CTX_PRODUCT_ID:
String productId = context != null ? context.getConfigurationItemId() : "";
data.add(productId);
break;
case QueryField.CTX_SERIAL_NUMBER:
String serialNumber = context != null ? context.getSerialNumber() : "";
data.add(serialNumber != null ? serialNumber : "");
break;
case QueryField.PART_MASTER_NUMBER:
data.add(part.getPartNumber());
break;
case QueryField.PART_MASTER_NAME:
String sName = part.getPartName();
data.add(sName != null ? sName : "");
break;
case QueryField.PART_MASTER_TYPE:
String sType = part.getType();
data.add(sType != null ? sType : "");
break;
case QueryField.PART_REVISION_MODIFICATION_DATE:
data.add(
(lastIteration != null && lastIteration.getModificationDate() != null)
? simpleDateFormat.format(lastIteration.getModificationDate())
: "");
break;
case QueryField.PART_REVISION_CREATION_DATE:
data.add(
(part.getCreationDate() != null)
? simpleDateFormat.format(part.getCreationDate())
: "");
break;
case QueryField.PART_REVISION_CHECKOUT_DATE:
data.add(
(part.getCheckOutDate() != null)
? simpleDateFormat.format(part.getCheckOutDate())
: "");
break;
case QueryField.PART_REVISION_CHECKIN_DATE:
data.add(
(lastCheckedInIteration != null && lastCheckedInIteration.getCheckInDate() != null)
? simpleDateFormat.format(lastCheckedInIteration.getCheckInDate())
: "");
break;
case QueryField.PART_REVISION_VERSION:
data.add(part.getVersion() != null ? part.getVersion() : "");
break;
case QueryField.PART_REVISION_LIFECYCLE_STATE:
data.add(part.getLifeCycleState() != null ? part.getLifeCycleState() : "");
break;
case QueryField.PART_REVISION_STATUS:
data.add(part.getStatus().toString());
break;
case QueryField.AUTHOR_LOGIN:
User user = part.getAuthor();
data.add(user.getLogin());
break;
case QueryField.AUTHOR_NAME:
User userAuthor = part.getAuthor();
data.add(userAuthor.getName());
break;
case QueryField.CTX_DEPTH:
data.add(row.getDepth() + "");
break;
case QueryField.CTX_AMOUNT:
data.add(row.getAmount() + "");
break;
case QueryField.PART_ITERATION_LINKED_DOCUMENTS:
StringBuilder sb = new StringBuilder();
if (lastCheckedInIteration != null) {
Set<DocumentLink> linkedDocuments = lastCheckedInIteration.getLinkedDocuments();
for (DocumentLink documentLink : linkedDocuments) {
DocumentRevision targetDocument = documentLink.getTargetDocument();
sb.append(
baseURL
+ "/documents/"
+ targetDocument.getWorkspaceId()
+ "/"
+ targetDocument.getId()
+ "/"
+ targetDocument.getVersion()
+ " ");
}
}
data.add(sb.toString());
break;
case QueryField.CTX_P2P_SOURCE:
Map<String, List<PartLinkList>> sources = row.getSources();
String sourcePartLinksAsString = Tools.getPartLinksAsExcelString(sources);
data.add(sourcePartLinksAsString);
break;
case QueryField.CTX_P2P_TARGET:
Map<String, List<PartLinkList>> targets = row.getTargets();
String targetPartLinksAsString = Tools.getPartLinksAsExcelString(targets);
data.add(targetPartLinksAsString);
break;
default:
if (select.startsWith(QueryField.PART_REVISION_ATTRIBUTES_PREFIX)) {
String attributeSelectType =
select
.substring(0, select.indexOf("."))
.substring(QueryField.PART_REVISION_ATTRIBUTES_PREFIX.length());
String attributeSelectName = select.substring(select.indexOf(".") + 1);
String attributeValue = "";
StringBuilder sbattr = new StringBuilder();
if (lastIteration != null) {
List<InstanceAttribute> attributes = lastIteration.getInstanceAttributes();
if (attributes != null) {
for (InstanceAttribute attribute : attributes) {
InstanceAttributeDescriptor attributeDescriptor =
new InstanceAttributeDescriptor(attribute);
if (attributeDescriptor.getName().equals(attributeSelectName)
&& attributeDescriptor.getStringType().equals(attributeSelectType)) {
attributeValue = attribute.getValue() + "";
if (attributeDescriptor.getType() == InstanceAttributeDescriptor.Type.DATE) {
attributeValue =
attribute.getValue() != null
? attributeDateFormat.format(attribute.getValue())
: "";
} else if (attribute instanceof InstanceListOfValuesAttribute) {
attributeValue =
((InstanceListOfValuesAttribute) attribute).getSelectedName();
}
sbattr.append(attributeValue + "|");
}
}
}
}
String content = sbattr.toString().trim();
if (content.length() > 0) {
content = content.substring(0, content.lastIndexOf("|"));
}
data.add(content);
}
if (select.startsWith(QueryField.PATH_DATA_ATTRIBUTES_PREFIX)) {
String attributeSelectType =
select
.substring(0, select.indexOf("."))
.substring(QueryField.PATH_DATA_ATTRIBUTES_PREFIX.length());
String attributeSelectName = select.substring(select.indexOf(".") + 1);
String attributeValue = "";
PathDataIteration pdi = row.getPathDataIteration();
StringBuilder sbpdattr = new StringBuilder();
if (pdi != null) {
List<InstanceAttribute> attributes = pdi.getInstanceAttributes();
if (attributes != null) {
for (InstanceAttribute attribute : attributes) {
InstanceAttributeDescriptor attributeDescriptor =
new InstanceAttributeDescriptor(attribute);
if (attributeDescriptor.getName().equals(attributeSelectName)
&& attributeDescriptor.getStringType().equals(attributeSelectType)) {
attributeValue = attribute.getValue() + "";
if (attributeDescriptor.getType() == InstanceAttributeDescriptor.Type.DATE) {
attributeValue =
attribute.getValue() != null
? attributeDateFormat.format(attribute.getValue())
: "";
} else if (attribute instanceof InstanceListOfValuesAttribute) {
attributeValue =
((InstanceListOfValuesAttribute) attribute).getSelectedName();
}
sbpdattr.append(attributeValue + "|");
}
}
}
}
String content = sbpdattr.toString().trim();
if (content.length() > 0) {
content = content.substring(0, content.lastIndexOf("|"));
}
data.add(content);
}
}
}
String rowData = StringUtils.join(data, ";");
return rowData.split(";");
}