protected SCell importCell(Cell poiCell, int row, SSheet sheet) {
SCell cell = sheet.getCell(row, poiCell.getColumnIndex());
cell.setCellStyle(importCellStyle(poiCell.getCellStyle()));
switch (poiCell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
cell.setNumberValue(poiCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
RichTextString poiRichTextString = poiCell.getRichStringCellValue();
if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) {
SRichText richText = cell.setupRichTextValue();
importRichText(poiCell, poiRichTextString, richText);
} else {
cell.setStringValue(poiCell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
cell.setBooleanValue(poiCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
cell.setFormulaValue(poiCell.getCellFormula());
// ZSS-873
if (isImportCache() && !poiCell.isCalcOnLoad() && !mustCalc(cell)) {
ValueEval val = null;
switch (poiCell.getCachedFormulaResultType()) {
case Cell.CELL_TYPE_NUMERIC:
val = new NumberEval(poiCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
RichTextString poiRichTextString0 = poiCell.getRichStringCellValue();
if (poiRichTextString0 != null && poiRichTextString0.numFormattingRuns() > 0) {
SRichText richText = new RichTextImpl();
importRichText(poiCell, poiRichTextString0, richText);
val = new StringEval(richText.getText());
} else {
val = new StringEval(poiCell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
val = BoolEval.valueOf(poiCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
val = ErrorEval.valueOf(poiCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_BLANK:
default:
// do nothing
}
if (val != null) {
((AbstractCellAdv) cell).setFormulaResultValue(val);
}
}
break;
case Cell.CELL_TYPE_ERROR:
cell.setErrorValue(PoiEnumConversion.toErrorCode(poiCell.getErrorCellValue()));
break;
case Cell.CELL_TYPE_BLANK:
// do nothing because spreadsheet model auto creates blank cells
default:
// TODO log: leave an unknown cell type as a blank cell.
break;
}
Hyperlink poiHyperlink = poiCell.getHyperlink();
if (poiHyperlink != null) {
String addr = poiHyperlink.getAddress();
String label = poiHyperlink.getLabel();
SHyperlink hyperlink =
cell.setupHyperlink(
PoiEnumConversion.toHyperlinkType(poiHyperlink.getType()),
addr == null ? "" : addr,
label == null ? "" : label);
cell.setHyperlink(hyperlink);
}
Comment poiComment = poiCell.getCellComment();
if (poiComment != null) {
SComment comment = cell.setupComment();
comment.setAuthor(poiComment.getAuthor());
comment.setVisible(poiComment.isVisible());
RichTextString poiRichTextString = poiComment.getString();
if (poiRichTextString != null && poiRichTextString.numFormattingRuns() > 0) {
importRichText(poiCell, poiComment.getString(), comment.setupRichText());
} else {
comment.setText(poiComment.toString());
}
}
return cell;
}
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
File excelFile = new File("export_parts.xls");
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Parts Data");
String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
String[] columns = header.split(";");
Map<Integer, String[]> data = new HashMap<>();
String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
data.put(1, headerFormatted);
Map<Integer, String[]> commentsData = new HashMap<>();
String[] headerComments = createXLSHeaderRowComments(header, columns);
commentsData.put(1, headerComments);
List<String> selects = queryResult.getQuery().getSelects();
int i = 1;
for (QueryResultRow row : queryResult.getRows()) {
i++;
data.put(i, createXLSRow(selects, row, baseURL));
commentsData.put(i, createXLSRowComments(selects, row));
}
// Iterate over data and write to sheet
Set<Integer> keyset = data.keySet();
int rownum = 0;
for (Integer key : keyset) {
Row row = sheet.createRow(rownum++);
String[] objArr = data.get(key);
int cellnum = 0;
for (String obj : objArr) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue(obj);
}
CreationHelper factory = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
String[] commentsObjArr = commentsData.get(key);
cellnum = 0;
for (String commentsObj : commentsObjArr) {
if (commentsObj.length() > 0) {
Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);
// When the comment box is visible, have it show in a 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 1);
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(commentsObj);
comment.setString(str);
// Assign the comment to the cell
cell.setCellComment(comment);
}
cellnum++;
}
}
// Define header style
Font headerFont = workbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setFontName("Courier New");
headerFont.setItalic(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// Set header style
for (int j = 0; j < columns.length; j++) {
Cell cell = sheet.getRow(0).getCell(j);
cell.setCellStyle(headerStyle);
if (cell.getCellComment() != null) {
String comment = cell.getCellComment().getString().toString();
if (comment.equals(QueryField.CTX_PRODUCT_ID)
|| comment.equals(QueryField.CTX_SERIAL_NUMBER)
|| comment.equals(QueryField.PART_MASTER_NUMBER)) {
for (int k = 0; k < queryResult.getRows().size(); k++) {
Cell grayCell =
sheet.getRow(k + 1).getCell(j) != null
? sheet.getRow(k + 1).getCell(j)
: sheet.getRow(k + 1).createCell(j);
grayCell.setCellStyle(headerStyle);
}
}
}
}
try {
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(excelFile);
workbook.write(out);
out.close();
} catch (Exception e) {
LOGGER.log(Level.FINEST, null, e);
}
return excelFile;
}
