private CoverageResults() {
fileName = "Coverage-results.xls";
workbook = new HSSFWorkbook();
allElementsSheet = workbook.createSheet("All Elements");
packagesSheet = workbook.createSheet("Packages");
boundClassesSheet = workbook.createSheet("Bound Classes");
allClassesSheet = workbook.createSheet("All Classes");
boundMethodsSheet = workbook.createSheet("Bound Methods");
allMethodsSheet = workbook.createSheet("All Methods");
normalFont = workbook.createFont();
normalFont.setFontName(HSSFFont.FONT_ARIAL);
normalFont.setFontHeightInPoints((short) 10);
boldFont = workbook.createFont();
boldFont.setFontName(HSSFFont.FONT_ARIAL);
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
centerAlignmentCellStyle = workbook.createCellStyle();
centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
leftAlignmentCellStyle = workbook.createCellStyle();
leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
rightAlignmentCellStyle = workbook.createCellStyle();
rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
dataFormatCellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
}
/*
* Don't hardcode US Locale for datetimes in Excel. Experiment a bit.
*/
public static void issue26() throws InvalidFormatException, IOException {
System.out.println("Testing issue26 =================================");
Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("Sheet1");
Row row = sheet.createRow(0);
// first cell
Cell cell0 = row.createCell(0);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
cell0.setCellStyle(cellStyle);
// second cell using another format with French locale
CellStyle cs2 = wb.createCellStyle();
String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
System.out.println(
"Converted pattern in FRENCH locale is: "
+ DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));
DataFormat df = wb.createDataFormat();
cs2.setDataFormat(df.getFormat(excelFormatPattern));
Cell cell1 = row.createCell(1);
cell1.setCellValue(new Date(1385903303326L));
cell1.setCellStyle(cs2);
FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
wb.write(out);
out.close();
System.out.println("Wrote /tmp/issue26_out.xlsx");
}
@Override
public void createCellStyles(Workbook wb) {
if (wb == null) {
return;
}
CreationHelper createHelper = wb.getCreationHelper();
// Headers
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.WHITE.index);
headerStyle.setFont(font);
cellStyleMap.put(STYLE_HELP_HEADER, headerStyle);
cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle);
cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle);
cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle);
// Date and Time
CellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY"));
cellStyleMap.put(STYLE_DATE_CELL, dateStyle);
CellStyle timeStyle = wb.createCellStyle();
timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM"));
cellStyleMap.put(STYLE_TIME_CELL, timeStyle);
}
private CellStyle getDateFormatStyle(HSSFCell poiCell) {
final HSSFWorkbook poiWorkbook = poiCell.getRow().getSheet().getWorkbook();
CellStyle dateFormatStyle = poiWorkbook.createCellStyle();
CreationHelper createHelper = poiWorkbook.getCreationHelper();
dateFormatStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:MM:ss"));
dateFormatStyle.setAlignment(CellStyle.ALIGN_LEFT);
return dateFormatStyle;
}
@Override
public void setDateTimePattern(String dateTimePattern) {
this.dateTimePattern = dateTimePattern;
DateTimeConverter dtConverter = (DateTimeConverter) converters.lookup(Date.class);
dtConverter.setDateTimePattern(dateTimePattern);
dateCellStyle = workbook.createCellStyle();
CreationHelper helper = workbook.getCreationHelper();
dateCellStyle.setDataFormat(helper.createDataFormat().getFormat(dateTimePattern));
}
private void makeTemporalCell(Cell retVal, Date cellObj, String format) {
CreationHelper creationHelper = workbook.getCreationHelper();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
retVal.setCellStyle(cellStyle);
if (null != cellObj) {
Calendar calendar = Calendar.getInstance();
calendar.setTime((Date) cellObj);
retVal.setCellValue(calendar);
}
}
public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
cell.setCellValue(NumberUtils.format(fieldValue, 0));
cell.setCellStyle(cellStyle);
}
/**
* ハイパーリンクの設定
*
* @param sheet シート
* @param nRow 対象行番号
* @param nColumn 対象列番号
* @param value ハイパーリンクテキスト
* @param url ハイパーリンク先URL
*/
public static void setHyperLink(
XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
assert sheet != null;
XSSFWorkbook workbook = sheet.getWorkbook();
CreationHelper helper = workbook.getCreationHelper();
Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
hyperlink.setAddress(url);
XSSFRow row = getRowAnyway(sheet, nRow);
XSSFCell cell = getCellAnyway(row, nColumn);
cell.setCellValue(value);
cell.setHyperlink(hyperlink);
// ハイパーリンクテキストの装飾
XSSFFont font = workbook.createFont();
XSSFCellStyle style = workbook.createCellStyle();
// font.setColor(new XSSFColor(new Color(0, 0, 255)));
font.setUnderline(XSSFFont.U_SINGLE);
style.setFont(font);
cell.setCellStyle(style);
}
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);
}
}
public void outputNumericAdapter(
DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
OutputNumericConfig config = dataBean.getOutputConfig(fieldName);
Workbook workbook = cell.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
StringBuilder format = new StringBuilder("0");
for (int i = 0; i < config.floatCount(); i++) {
if (i == 0) format.append(".");
format.append("0");
}
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString()));
cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount()));
cell.setCellStyle(cellStyle);
}
@Override
public void renderCell(Element element, int rowIndex, int columnIndex) {
Cell cell = sheet.getRow(rowIndex).createCell(columnIndex);
Double numericValue;
if (isDateCell(element)) {
DateFormat df = new SimpleDateFormat(getDateCellFormat(element));
try {
cell.setCellValue(df.parse(getElementText(element)));
} catch (ParseException pex) {
System.out.println("Invalid Usage");
}
} else if ((numericValue = getNumericValue(element)) != null) {
cell.setCellValue(numericValue);
} else {
cell = sheet.getRow(rowIndex).createCell(columnIndex, Cell.CELL_TYPE_STRING);
cell.setCellValue(getElementText(element));
}
Style style = styleMapper.getStyleForElement(element);
cell.setCellStyle(styleGenerator.getStyle(cell, style));
if (isDateCell(element)) {
CreationHelper createHelper = sheet.getWorkbook().getCreationHelper();
cell.getCellStyle()
.setDataFormat(createHelper.createDataFormat().getFormat(getDateCellFormat(element)));
}
String commentText;
if ((commentText = getCellCommentText(element)) != null) {
ExcelCellCommentGenerator.addCellComment(cell, commentText, getCellCommentDimension(element));
}
if (definesFreezePane(element)) {
sheet.createFreezePane(columnIndex, rowIndex);
}
}
/**
* Creates a new instance.
*
* @param workbook target workbook
* @throws IllegalArgumentException if some parameters were {@code null}
*/
WorkbookInfo(Workbook workbook) {
if (workbook == null) {
throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
}
this.workbook = workbook;
Font font = workbook.createFont();
commonStyle = workbook.createCellStyle();
commonStyle.setFont(font);
commonStyle.setBorderTop(BorderStyle.THIN);
commonStyle.setBorderBottom(BorderStyle.THIN);
commonStyle.setBorderLeft(BorderStyle.THIN);
commonStyle.setBorderRight(BorderStyle.THIN);
titleStyle = workbook.createCellStyle();
titleStyle.cloneStyleFrom(commonStyle);
titleStyle.setLocked(true);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
titleStyle.setAlignment(HorizontalAlignment.CENTER);
CreationHelper helper = workbook.getCreationHelper();
DataFormat df = helper.createDataFormat();
dataStyle = workbook.createCellStyle();
dataStyle.cloneStyleFrom(commonStyle);
dateDataStyle = workbook.createCellStyle();
dateDataStyle.cloneStyleFrom(commonStyle);
dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$
timeDataStyle = workbook.createCellStyle();
timeDataStyle.cloneStyleFrom(commonStyle);
timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$
datetimeDataStyle = workbook.createCellStyle();
datetimeDataStyle.cloneStyleFrom(commonStyle);
datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
}
public CellStyle getNumberFormat(
int j,
Map<Integer, CellStyle> decimalFormats,
Sheet sheet,
CreationHelper createHelper,
CellType celltype) {
int mapPosition = j;
if (celltype.equals(CellType.TOTAL)) {
mapPosition = j + 90000;
} else if (celltype.equals(CellType.SUBTOTAL)) {
mapPosition = j + 80000;
} else if (celltype.equals(CellType.CF)) {
mapPosition = j + 60000;
}
if (decimalFormats.get(mapPosition) != null) return decimalFormats.get(mapPosition);
if (celltype.equals(CellType.CF)) {
j = this.getCalculatedFieldDecimals();
}
String decimals = "";
for (int i = 0; i < j; i++) {
decimals += "0";
}
CellStyle cellStyle = this.buildDataCellStyle(sheet);
DataFormat df = createHelper.createDataFormat();
String format = "#,##0";
if (decimals.length() > 0) {
format += "." + decimals;
}
cellStyle.setDataFormat(df.getFormat(format));
if (celltype.equals(CellType.TOTAL)) {
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
}
if (celltype.equals(CellType.CF)) {
cellStyle.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
}
if (celltype.equals(CellType.SUBTOTAL)) {
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}
decimalFormats.put(mapPosition, cellStyle);
return cellStyle;
}
/**
* 导出时间适配器
*
* @param fieldValue
* @param fieldName
* @return
* @throws AdapterException
*/
public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
throws AdapterException {
log.debug(
"in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}",
fieldName,
fieldValue);
Date date = null;
if (fieldValue == null) {
log.debug("fieldValue is null return");
cell.setCellValue("");
return;
} else if (fieldValue instanceof Date) {
log.debug("fieldValue instanceof Date ");
date = (Date) fieldValue;
} else if (fieldValue instanceof String) {
log.debug("fieldValue instanceof String ");
InputDateConfig config = dataBean.getInputConfig(fieldName);
try {
date = DateUtil.formatToDate((String) fieldValue, config.format());
} catch (ParseException e) {
throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
}
} else if (fieldValue instanceof Long) {
log.debug("fieldValue instanceof Long ");
date = new Date((Long) fieldValue);
} else {
throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
}
Workbook workbook = cell.getSheet().getWorkbook();
OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName);
CellStyle cellStyle = cell.getCellStyle();
if (cellStyle == null) cellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format()));
cell.setCellStyle(cellStyle);
cell.setCellValue(date);
}
/**
* 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;
}
}
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;
}
public void process2HTTP(
ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) {
try {
String strFileName = uis.getDownloadFileName();
Workbook wbCurrent = null;
if (strFileName.toLowerCase().endsWith(".xlsx")) {
wbCurrent = new XSSFWorkbook();
} else {
wbCurrent = new HSSFWorkbook();
}
HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>();
CreationHelper cr = wbCurrent.getCreationHelper();
CellStyle csDate = wbCurrent.createCellStyle();
csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern()));
CellStyle csDateTime = wbCurrent.createCellStyle();
csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern()));
CellStyle csTime = wbCurrent.createCellStyle();
csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern()));
hsCS.put("DATE", csDate);
hsCS.put("TIME", csTime);
hsCS.put("DATETIME", csDateTime);
Sheet sh = wbCurrent.createSheet("SVE Export");
int nRowCount = 0;
// BUILDING HEADER
if (uis.isIncludeHeader()) {
Row rw = sh.createRow(nRowCount);
int nCol = 0;
for (ExportColumn expColumn : expModel.getColumns()) {
rw.createCell(nCol).setCellValue(expColumn.getColumnName());
nCol++;
}
nRowCount++;
}
// Processing Values
for (ExportDataRow expRow : expModel.getRows()) {
Row rw = sh.createRow(nRowCount);
int nCol = 0;
for (ExportColumn expColumn : expModel.getColumns()) {
Cell clCurrent = rw.createCell(nCol);
setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS);
nCol++;
}
nRowCount++;
}
for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) {
sh.autoSizeColumn(nCol);
}
if (strFileName.toLowerCase().endsWith(".xlsx")) {
hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
} else if (strFileName.toLowerCase().endsWith("xls")) {
hsr.setContentType("application/vnd.ms-excel");
} else {
hsr.setContentType("application/octet-stream");
}
hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
OutputStream os = hsr.getOutputStream();
ByteArrayOutputStream bos = new ByteArrayOutputStream();
wbCurrent.write(bos);
bos.writeTo(os);
os.close();
} catch (Exception e) {
ErrorPageBuilder.getInstance()
.processError(hsr, "Error during SVE-Generation (Workbook Export)", e);
}
}
@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();
}
}
