/**
* 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++));
}
}
