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