/** * to build the response as excel file. * * @see org.springframework.web.servlet.view.document.AbstractExcelView# * buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook, * javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse) */ @SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED) @Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook myWorkBook, HttpServletRequest arg2, HttpServletResponse arg3) throws Exception { List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions"); HSSFSheet questionsSheet = myWorkBook.createSheet(AppConstants.EXCEL_SHEET_NAME); HSSFRow headerRow = questionsSheet.createRow(0); HSSFCellStyle wrapCellStyle = myWorkBook.createCellStyle(); wrapCellStyle.setWrapText(true); wrapCellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); createNewCell(headerRow, QUESTIONID_CELLNUM, "Question ID", wrapCellStyle); createNewCell(headerRow, QUESTION_CELLNUM, "Question", wrapCellStyle); createNewCell(headerRow, ANSWER_CELLNUM, "Answer", wrapCellStyle); createNewCell( headerRow, CATEGORIES_CELLNUM, "Categories (Category seperated by comma)", wrapCellStyle); HSSFRow dataRow = null; if (!CoreUtil.isEmpty(questionDTOs)) { int i = 0; for (QuestionDTO questionDTO : questionDTOs) { dataRow = questionsSheet.createRow(i + 1); createNewCell(dataRow, QUESTIONID_CELLNUM, Long.toString(questionDTO.getId()), null); createNewCell(dataRow, QUESTION_CELLNUM, questionDTO.getQuestion(), wrapCellStyle); createNewCell(dataRow, ANSWER_CELLNUM, questionDTO.getAnswer(), wrapCellStyle); createNewCell( dataRow, CATEGORIES_CELLNUM, parseCategoryDTOs(questionDTO.getCategoryDTOs()), wrapCellStyle); // Increment row i = i + 1; } } questionsSheet.autoSizeColumn(QUESTIONID_CELLNUM); questionsSheet.autoSizeColumn(QUESTION_CELLNUM); questionsSheet.autoSizeColumn(ANSWER_CELLNUM); questionsSheet.autoSizeColumn(CATEGORIES_CELLNUM); questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle); questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle); questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle); }
private void writeHeaderRow(Entity entity, HSSFSheet sheet) { HSSFRow headerRow = sheet.createRow(0); int colnum = 0; for (Map.Entry<String, Object> component : getComponents(entity)) { String componentName = component.getKey(); headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName)); ComponentDescriptor cd = entity.descriptor().getComponent(componentName); PrimitiveType primitiveType; if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor) primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType(); else throw new UnsupportedOperationException( "Can only export simple type attributes, " + "failed to export " + entity.type() + '.' + cd.getName()); Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class); String formatString = null; if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern(); else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern(); else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern(); else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern(); else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern(); if (formatString != null) { HSSFDataFormat dataFormat = workbook.createDataFormat(); CellStyle columnStyle = workbook.createCellStyle(); columnStyle.setDataFormat(dataFormat.getFormat(formatString)); sheet.setDefaultColumnStyle(colnum, columnStyle); } colnum++; } }
private void createDetailSheet() { sheet = workbook.createSheet("Report"); for (int i = 0; i < lbls.length; i++) { sheet.setDefaultColumnStyle(i, whiteStyle); } // the first row createRow(_rowcnt++); int _idx = lbls.length - 6; setHeaderCell(_idx, null, 3); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, "Similarity Status", 4); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, null, 2); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, null, 3); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, "T13y Status", 4); // row.getCell(_idx).getCellStyle() // .setBorderRight(HSSFCellStyle.BORDER_NONE); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); setHeaderCell(++_idx, null, 2); // row.getCell(_idx).getCellStyle() // .setBorderLeft(HSSFCellStyle.BORDER_NONE); for (int i = 0; i < lbls.length - 6; i++) { setHeaderCell(i, lbls[i], 0); // row.getCell(i).getCellStyle() // .setBorderBottom(HSSFCellStyle.BORDER_NONE); } createRow(_rowcnt++); // set background color for (int i = 0; i < lbls.length - 6; i++) { setHeaderCell(i, null, 1); // row.getCell(i).getCellStyle() // .setBorderTop(HSSFCellStyle.BORDER_NONE); } for (int i = lbls.length - 6; i < lbls.length; i++) { setHeaderCell(i, lbls[i], -1); } sheet.createFreezePane(0, 2, 0, 2); for (int i = 0; i < 11; i++) _sims[i] = 0; }
@Override protected void buildExcelDocument( Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Object> errorList = (List<Object>) model.get("errorList"); List<DonVi> donViError = (List<DonVi>) errorList.get(0); List<String> statusError = (List<String>) errorList.get(1); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet("Bộ phận sử dụng bị lỗi import"); // create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Times New Roman"); font.setFontHeight((short) 260); style.setFont(font); sheet.setDefaultRowHeight((short) 400); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 12000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 6000); sheet.setColumnWidth(5, 5000); sheet.setDefaultColumnStyle(0, style); sheet.setDefaultColumnStyle(1, style); sheet.setDefaultColumnStyle(2, style); sheet.setDefaultColumnStyle(3, style); sheet.setDefaultColumnStyle(4, style); sheet.setDefaultColumnStyle(5, style); // create header row CellStyle style2 = workbook.createCellStyle(); Font font2 = workbook.createFont(); font2.setFontName("Times New Roman"); font2.setBoldweight(Font.BOLDWEIGHT_BOLD); font2.setFontHeight((short) 260); style2.setFont(font2); style2.setAlignment(CellStyle.ALIGN_CENTER); HSSFRow row2 = sheet.createRow(0); row2.createCell(0).setCellValue("Công ty điện lực thành phố Cần Thơ"); row2.getCell(0).setCellStyle(style); row2.createCell(1).setCellValue("Kho Công ty Điện Lực Cần Thơ"); row2.getCell(1).setCellStyle(style); // create header row HSSFRow header = sheet.createRow(1); response.setHeader("Content-Disposition", "inline; filename=" + "BophansudungError.xls"); header.createCell(0).setCellValue("Mã BPSD"); header.getCell(0).setCellStyle(style2); header.createCell(1).setCellValue("Tên BPSD"); header.getCell(1).setCellStyle(style2); header.createCell(2).setCellValue("Địa chỉ"); header.getCell(2).setCellStyle(style2); header.createCell(3).setCellValue("Email"); header.getCell(3).setCellStyle(style2); header.createCell(4).setCellValue("Số điện thoại"); header.getCell(4).setCellStyle(style2); header.createCell(5).setCellValue("Lỗi"); header.getCell(5).setCellStyle(style2); // create data rows int rowCount = 2; int i = 0; for (DonVi dv : donViError) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(dv.getDvMa()); aRow.createCell(1).setCellValue(dv.getDvTen()); aRow.createCell(2).setCellValue(dv.getDiaChi()); aRow.createCell(3).setCellValue(dv.getEmail()); aRow.createCell(4).setCellValue(dv.getSdt()); aRow.createCell(5).setCellValue(statusError.get(i++)); } }