/**
* generated <code><col><code> tags.
* @param sheet
* @param table container.
*/
private void generateColumns(XSSFSheet sheet, Element table) {
List<CTCols> colsList = sheet.getCTWorksheet().getColsList();
MathContext mc = new MathContext(3);
for (CTCols cols : colsList) {
long oldLevel = 1;
for (CTCol col : cols.getColArray()) {
while (true) {
if (oldLevel == col.getMin()) {
break;
}
Element column = htmlDocumentFacade.createTableColumn();
// htmlDocumentFacade.addStyleClass(column, "col", "width:2cm;");
column.setAttribute("style", "width:2cm;");
table.appendChild(column);
oldLevel++;
}
Element column = htmlDocumentFacade.createTableColumn();
String width =
new BigDecimal(sheet.getColumnWidth(Long.bitCount(oldLevel)) / 1440.0, mc).toString();
column.setAttribute("style", "width:".concat(width).concat("cm;"));
table.appendChild(column);
oldLevel++;
}
}
}
/**
* @param newSheet the sheet to create from the copy.
* @param sheet the sheet to copy.
* @param copyStyle true copy the style.
*/
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
int maxColumnNum = 0;
Map<Integer, XSSFCellStyle> styleMap =
(copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i);
if (srcRow != null) {
XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
@Test
public void autoFilter() throws Exception {
debug = false;
autoFilter = true;
InputStream inputStream = runAndRenderReport("SideBySideMultiColumns.rptdesign", "xlsx");
assertNotNull(inputStream);
try {
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
assertNotNull(workbook);
assertEquals(1, workbook.getNumberOfSheets());
assertEquals("Sheet0", workbook.getSheetAt(0).getSheetName());
XSSFSheet sheet = workbook.getSheetAt(0);
assertEquals(124, this.firstNullRow(sheet));
assertEquals(297, sheet.getRow(0).getHeightInPoints(), 1.0);
assertEquals(2048, sheet.getColumnWidth(0));
assertEquals(6196, sheet.getColumnWidth(1));
assertEquals(3749, sheet.getColumnWidth(2));
assertEquals(2396, sheet.getColumnWidth(3));
assertEquals(4516, sheet.getColumnWidth(4));
assertEquals(7072, sheet.getColumnWidth(5));
assertEquals(2048, sheet.getColumnWidth(6));
assertEquals(3509, sheet.getColumnWidth(7));
assertEquals(2048, sheet.getColumnWidth(8));
assertEquals(2314, sheet.getColumnWidth(9));
assertEquals(2338, sheet.getColumnWidth(10));
assertEquals(2048, sheet.getColumnWidth(11));
assertEquals(2048, sheet.getColumnWidth(12));
assertTrue(mergedRegion(sheet, 0, 0, 0, 5));
assertTrue(mergedRegion(sheet, 0, 7, 0, 12));
XSSFName name = workbook.getName(XSSFName.BUILTIN_FILTER_DB);
assertEquals(0, name.getSheetIndex());
assertEquals("Sheet0!$A$1:$M$2", name.getRefersToFormula());
} finally {
inputStream.close();
}
}
@Override
public Workbook writeWorkbook(
Workbook workbook, String sheetTitle, List<List<String>> tableDataList, int headerNum) {
if (workbook == null) workbook = new XSSFWorkbook();
int exportRecordNum = tableDataList.size(); // 导出的总记录数
int tableStartRowNum = headerNum > 0 ? headerNum : 0; // 表体数据开始行数
int sheetNum =
exportRecordNum / (CommonConst.EXCEL_MAX_EXPORT_NUM - headerNum)
+ 1; // 工作表的页数
List<XSSFSheet> sheetList = new ArrayList<XSSFSheet>();
if (StringUtils.isEmpty(sheetTitle)) sheetTitle = "sheet";
for (int i = 0; i < sheetNum; i++) {
// 在Excel工作簿中建一工作表
String sTitle = sheetTitle + (i + 1);
XSSFSheet sheet = (XSSFSheet) workbook.createSheet(sTitle);
sheet.setSelected(true); // 设置工作薄为选中
sheet.setAutobreaks(true);
sheet.setPrintGridlines(true);
sheetList.add(sheet);
}
/** ***********************输出表头********************************* */
if (headerNum > 0) {
for (XSSFSheet sheet : sheetList) {
XSSFRow headRow = sheet.createRow(0);
headRow.setHeightInPoints(20);
for (int i = 0; i < headerNum; i++) {
List<String> headerRowDataList = tableDataList.get(i);
for (int j = 0; j < headerRowDataList.size(); j++) {
XSSFCellStyle cellStyle = (XSSFCellStyle) createDefHeaderCellStyle(workbook); // 默认表头样式
createCell(headRow, j, headerRowDataList.get(j), cellStyle);
}
}
// 固定表头
sheet.createFreezePane(0, 1);
}
}
/** *********************输出表体内容************************* */
// 设置列样式
XSSFCellStyle columnStyle = (XSSFCellStyle) workbook.createCellStyle();
columnStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
columnStyle.setWrapText(true);
if (tableDataList.size() > (tableStartRowNum + 1)) {
for (int i = tableStartRowNum; i < exportRecordNum; i++) {
List<String> rowDataList = tableDataList.get(i);
XSSFRow row = null;
int currentSheet = i / CommonConst.EXCEL_MAX_EXPORT_NUM; // 当前工作表的页数
XSSFSheet sheet = sheetList.get(currentSheet);
int rowIndex = i - CommonConst.EXCEL_MAX_EXPORT_NUM * currentSheet;
row = sheet.createRow(rowIndex);
for (int colIndex = 0, colLength = rowDataList.size(); colIndex < colLength; colIndex++) {
createCell(row, colIndex, rowDataList.get(colIndex));
}
}
}
// 调整列的宽度(取第一列为基准)
for (XSSFSheet sheet : sheetList) {
for (int i = 0; i < tableDataList.get(0).size(); i++) {
sheet.autoSizeColumn((short) i);
sheet.setColumnWidth((short) i, (short) (sheet.getColumnWidth((short) i) + 1000));
}
}
return workbook;
}
/**
* get the width (in units of 1/256th of a character width )
*
* @param columnIndex - the column to set (0-based)
* @return width - the width in units of 1/256th of a character width
*/
public int getColumnWidth(int columnIndex) {
return _sh.getColumnWidth(columnIndex);
}
@Override
public byte[] getImportTemplate(Boolean editProductType) throws IOException {
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME);
XSSFSheet typeSheet = createTypeSheet(workBook);
if (!(true == editProductType)) {
workBook.setSheetHidden(
workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);
}
XSSFRow titleRow = productSheet.createRow(0);
List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting();
XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
for (int i = 0; i < transSettings.size(); i++) {
TransformSetting setting = transSettings.get(i);
XSSFCell titleCell = null;
titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING);
String cellValue = setting.getDispName();
titleCell.setCellValue(cellValue);
productSheet.autoSizeColumn(i, true);
int columnWidth = productSheet.getColumnWidth(i);
int needWidth = cellValue.getBytes().length * 256;
if (columnWidth < needWidth) {
productSheet.setColumnWidth(i, needWidth);
}
if ("productTypeName".equals(setting.getPropertyName())) {
Name type = workBook.createName();
String name = "type";
type.setNameName(name); // 定义名称
type.setRefersToFormula(
"OFFSET("
+ typeSheet.getSheetName()
+ "!$A$3,0,0,COUNTA("
+ typeSheet.getSheetName()
+ "!$A:$A)-2)");
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称
CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i);
DataValidation dataValidation = helper.createValidation(constraint, regions);
productSheet.addValidationData(dataValidation);
} else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得
for (int j = 0; j < 10; j++) {
XSSFRow row = productSheet.createRow(j + 1);
XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING);
int cellnum = j + 2;
itemCell.setCellFormula(
"IF(A"
+ cellnum
+ "=\"\",\"\",VLOOKUP(A"
+ cellnum
+ ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))");
}
} else if ("costMethod".equals(setting.getPropertyName())) {
int len = CostMethod.values().length;
String[] costNames = new String[len];
for (int ci = 0; ci < len; ci++) {
costNames[ci] = CostMethod.values()[ci].getMethodName();
}
// XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
XSSFDataValidationConstraint constraint =
(XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames);
CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i);
XSSFDataValidation validation =
(XSSFDataValidation) helper.createValidation(constraint, addressList);
validation.setShowErrorBox(true);
productSheet.addValidationData(validation);
}
}
workBook.setActiveSheet(workBook.getSheetIndex(productSheet));
ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
workBook.write(baos);
return baos.toByteArray();
}