protected void writeCellValue(
Row row,
int cellIdx,
CreationHelper chelp,
CellStyle cellStyle,
Object value,
Column column) {
TypeCastGraph tcast = getCastGraph();
// String str = tcast.cast(value, String.class);
Cell cell = row.getCell(cellIdx);
if (cell == null) cell = row.createCell(cellIdx);
if (value instanceof String) {
cell.setCellValue(chelp.createRichTextString((String) value));
cell.setCellType(Cell.CELL_TYPE_STRING);
} else if (value instanceof java.util.Date) {
java.util.Date date = (java.util.Date) value;
String textDate = getDateFormat().format(date);
cell.setCellValue(chelp.createRichTextString((String) textDate));
} else if (value instanceof java.util.Calendar) {
cell.setCellValue((java.util.Calendar) value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
} else if (value instanceof Float) {
cell.setCellValue((Float) value);
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
} else {
String textValue = tcast.cast(value, String.class);
if (textValue != null) {
cell.setCellValue(chelp.createRichTextString(textValue));
cell.setCellType(Cell.CELL_TYPE_STRING);
}
}
if (cellStyle != null) {
cell.setCellStyle(cellStyle);
}
}
/**
* Add the title of the columns in the row headers
*
* @param sheet
* @param titles list of titles
* @param columnHeadersNumber number of column headers
* @param startColumn first column of the crosstab in the xls
* @param startRow first row of the crosstab in the xls
* @param createHelper
* @throws JSONException
*/
protected void buildRowHeaderTitle(
Sheet sheet,
CrossTab cs,
int columnHeadersNumber,
int startColumn,
int startRow,
CreationHelper createHelper,
Locale locale,
CellStyle cellStyle)
throws JSONException {
List<String> titles = cs.getRowHeadersTitles();
if (titles != null) {
Row row = sheet.getRow(startRow + columnHeadersNumber);
for (int i = 0; i < titles.size(); i++) {
Cell cell = row.createCell(startColumn + i);
String text = titles.get(i);
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyle);
}
if (cs.isMeasureOnRow()) {
Cell cell = row.createCell(startColumn + titles.size());
String text = "Measures";
if (locale != null) {
text =
EngineMessageBundle.getMessage("worksheet.export.crosstab.header.measures", locale);
}
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyle);
}
}
}
/**
* Builds the rows' headers recursively with this order: |-----|-----|-----| | | | 3 | | | |-----|
* | | 2 | 4 | | | |-----| | 1 | | 5 | | |-----|-----| | | | 7 | | | 6 |-----| | | | 8 |
* |-----|-----|-----| | | | 11 | | 9 | 10 |-----| | | | 12 | |-----|-----|-----|
*
* @param sheet The sheet of the XLS file
* @param siblings The siblings nodes of the headers structure
* @param rowNum The row number where the first sibling must be inserted
* @param columnNum The column number where the siblings must be inserted
* @param createHelper The file creation helper
* @throws JSONException
*/
protected void buildRowsHeaders(
Sheet sheet,
CrossTab cs,
List<Node> siblings,
int rowNum,
int columnNum,
CreationHelper createHelper,
Locale locale,
CellStyle cellStyle)
throws JSONException {
int rowsCounter = rowNum;
for (int i = 0; i < siblings.size(); i++) {
Node aNode = siblings.get(i);
List<Node> childs = aNode.getChilds();
Row row = sheet.getRow(rowsCounter);
Cell cell = row.createCell(columnNum);
String text = (String) aNode.getDescription();
if (cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
// apply the measure scale factor
text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
}
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyle);
int descendants = aNode.getLeafsNumber();
if (descendants > 1) {
sheet.addMergedRegion(
new CellRangeAddress(
rowsCounter, // first row (0-based)
rowsCounter + descendants - 1, // last row (0-based)
columnNum, // first column (0-based)
columnNum // last column (0-based)
));
}
if (childs != null && childs.size() > 0) {
buildRowsHeaders(
sheet, cs, childs, rowsCounter, columnNum + 1, createHelper, locale, cellStyle);
}
int increment = descendants > 1 ? descendants : 1;
rowsCounter = rowsCounter + increment;
}
}
// <editor-fold defaultstate="collapsed" desc="writeMetaData">
protected void writeMetaData(
Row row, int cellStart, CreationHelper createHelper, CellStyle cellStyle, MetaData meta) {
int cellIdx = cellStart;
for (Column column : meta.getColumns()) {
String colName = column.getName();
if (colName == null) continue;
Cell cell = row.getCell(cellIdx);
if (cell == null) {
cell = row.createCell(cellIdx);
}
cell.setCellValue(createHelper.createRichTextString(colName));
if (cellStyle != null) cell.setCellStyle(cellStyle);
cellIdx++;
}
}
protected int buildDataMatrix(
Sheet sheet,
CrossTab cs,
int rowOffset,
int columnOffset,
CreationHelper createHelper,
MeasureFormatter measureFormatter)
throws JSONException {
CellStyle cellStyleForNA = buildNACellStyle(sheet);
Map<Integer, CellStyle> decimalFormats = new HashMap<Integer, CellStyle>();
int endRowNum = 0;
for (int i = 0; i < cs.getDataMatrix().length; i++) {
for (int j = 0; j < cs.getDataMatrix()[0].length; j++) {
String text = (String) cs.getDataMatrix()[i][j];
int rowNum = rowOffset + i;
int columnNum = columnOffset + j;
Row row = sheet.getRow(rowNum);
if (row == null) {
row = sheet.createRow(rowNum);
}
endRowNum = rowNum;
Cell cell = row.createCell(columnNum);
try {
double value = Double.parseDouble(text);
int decimals = measureFormatter.getFormatXLS(i, j);
Double valueFormatted = measureFormatter.applyScaleFactor(value, i, j);
cell.setCellValue(valueFormatted);
cell.setCellType(this.getCellTypeNumeric());
cell.setCellStyle(
getNumberFormat(decimals, decimalFormats, sheet, createHelper, cs.getCellType(i, j)));
} catch (NumberFormatException e) {
logger.debug("Text " + text + " is not recognized as a number");
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
cell.setCellStyle(cellStyleForNA);
}
}
}
return endRowNum;
}
/**
* Builds the columns' headers recursively with this order:
* |------------------------------------------| | 1 | 9 |
* |------------------------------------------| | 2 | 5 | 10 |
* |-----------|-----------------|------------| | 3 | 4 | 6 | 7 | 8 | 11 | 12 |
* |------------------------------------------|
*
* @param sheet The sheet of the XLS file
* @param siblings The siblings nodes of the headers structure
* @param rowNum The row number where the siblings must be inserted
* @param columnNum The column number where the first sibling must be inserted
* @param createHelper The file creation helper
* @param dimensionCellStyle The cell style for cells containing dimensions (i.e. attributes'
* names)
* @param memberCellStyle The cell style for cells containing members (i.e. attributes' values)
* @throws JSONException
*/
protected void buildColumnsHeader(
Sheet sheet,
CrossTab cs,
List<Node> siblings,
int rowNum,
int columnNum,
CreationHelper createHelper,
Locale locale,
CellStyle memberCellStyle,
CellStyle dimensionCellStyle)
throws JSONException {
int columnCounter = columnNum;
for (int i = 0; i < siblings.size(); i++) {
Node aNode = (Node) siblings.get(i);
List<Node> childs = aNode.getChilds();
Row row = sheet.getRow(rowNum);
Cell cell = row.createCell(columnCounter);
String text = (String) aNode.getDescription();
if (!cs.isMeasureOnRow() && (childs == null || childs.size() <= 0)) {
// apply the measure scale factor
text = MeasureScaleFactorOption.getScaledName(text, cs.getMeasureScaleFactor(text), locale);
}
cell.setCellValue(createHelper.createRichTextString(text));
cell.setCellType(this.getCellTypeString());
int descendants = aNode.getLeafsNumber();
if (descendants > 1) {
sheet.addMergedRegion(
new CellRangeAddress(
rowNum, // first row (0-based)
rowNum, // last row (0-based)
columnCounter, // first column (0-based)
columnCounter + descendants - 1 // last column (0-based)
));
}
/*
* Now we have to set the style properly according to the nature of
* the node: if it contains the name of a dimension or a member.
* Since the structure foresees that a list of members follows a
* dimension, we calculate the position of the node with respect to
* the leaves; in case it is odd, the cell contains a dimension; in
* case it is even, the cell contains a dimension.
*/
int distanceToLeaves = aNode.getDistanceFromLeaves();
if (!cs.isMeasureOnRow()) {
distanceToLeaves--;
}
boolean isDimensionNameCell = distanceToLeaves > 0 && (distanceToLeaves % 2) == 1;
if (isDimensionNameCell) {
cell.setCellStyle(dimensionCellStyle);
} else {
cell.setCellStyle(memberCellStyle);
}
if (childs != null && childs.size() > 0) {
buildColumnsHeader(
sheet,
cs,
childs,
rowNum + 1,
columnCounter,
createHelper,
locale,
memberCellStyle,
dimensionCellStyle);
}
int increment = descendants > 1 ? descendants : 1;
columnCounter = columnCounter + increment;
}
}
@Test
public void createex() {
Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillPattern(CellStyle.NO_FILL);
Sheet sheet = wb.createSheet("数据日志");
Sheet sheet2 = wb.createSheet("second sheet");
String safeName =
WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "
Sheet sheet3 = wb.createSheet(safeName);
Header header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(
HSSFHeader.font("Stencil-Normal", "Italic")
+ HSSFHeader.fontSize((short) 16)
+ "Right w/ Stencil-Normal Italic font and size 16");
sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度
sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度
sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度
sheet.autoSizeColumn(2); // 调整第四列宽度
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short) 1);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue("ID");
cell.setCellStyle(cellStyle);
// Or do it on one line.
row.createCell(1).setCellValue("表名");
row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型"));
row.createCell(3).setCellValue("操作日期");
row.createCell(4).setCellValue("操作者");
List<Log> list = this.logList();
Row rw;
for (int i = 2; i < list.size(); i++) {
Log log = list.get(i);
rw = sheet.createRow((short) i);
rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId()));
rw.createCell(1).setCellValue(log.getDataTable());
rw.createCell(2).setCellValue(this.convertActionType(log.getActionType()));
rw.createCell(3)
.setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2));
rw.createCell(4).setCellValue(log.getAdminId());
}
String bp = this.getClass().getResource("/").toString();
try {
System.out.println(bp);
File f = new File("workbook.xls");
FileOutputStream fileOut = new FileOutputStream(f);
wb.write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
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;
}
