java - 使用 Java Apache POI 3.15 版本将 CrossTab 数据导出到 Excel 工作表的问题
问题描述
我正在从我的公司门户收集数据并尝试将其导出到 Excel 表中。我成功完成了非交叉表报告数据。但是当数据有交叉表时问题就来了。
根据我的最佳理解,我已将数据保存在 ArrayList 中。
1.
rowHeader:[Quarter, Subcategory]
2.
rowElement:[2016 Q1, 2016 Q2, 2016 Q3, 2016 Q4, Audio Equipment, Cameras, Computers, Electronics - Miscellaneous, TV's, Video Equipment]
3.
columnHeader:[Year, 2016]
4.
columnHeaders[Quarter, Subcategory, Year, 2016]
5.
metricsColumn:[Metrics, Profit, Profit Forecast, Profit Margin, Revenue, Revenue Forecast]
6.
listData:[[$9,579, $8,823, 19.42%, $49,320, $ 39,456], [$11,449, $9,619, 20.07%, $57,040, $ 53,047], [$4,901, $3,784, 20.02%, $24,480, $ 21,298], [$12,444, $9,525, 19.89%, $62,576, $ 53,815], [$8,820, $8,059, 20.19%, $43,675, $ 35,814], [$16,375, $12,986, 20.44%, $80,130, $ 70,514], [$8,526, $6,929, 15.78%, $54,020, $ 49,698], [$11,602, $9,578, 17.31%, $67,032, $ 63,680], [$4,675, $4,380, 16.83%, $27,780, $ 25,752], [$11,699, $9,421, 16.60%, $70,469, $ 54,966], [$9,386, $7,179, 17.52%, $53,563, $ 49,974], [$10,150, $9,213, 16.96%, $59,864, $ 48,490], [$8,508, $6,772, 17.16%, $49,571, $ 47,092], [$16,429, $13,529, 18.52%, $88,712, $ 83,389], [$6,009, $5,391, 18.05%, $33,295, $ 29,200], [$11,792, $9,791, 17.21%, $68,534, $ 64,285], [$9,243, $7,952, 17.15%, $53,886, $ 49,953], [$14,282, $11,679, 18.03%, $79,193, $ 74,441], [$10,999, $8,538, 15.60%, $70,511, $ 65,575], [$14,475, $11,433, 16.32%, $88,718, $ 78,515], [$5,765, $5,029, 16.77%, $34,373, $ 31,847], [$11,335, $9,567, 15.28%, $74,168, $ 62,672], [$11,990, $11,230, 16.83%, $71,255, $ 64,414], [$13,864, $11,328, 16.87%, $82,203, $ 70,695]]
到目前为止,我成功地完成了导出 Headers 部分。
我正在苦苦挣扎,现在将数据导出到 Excel 工作表中,因为我们需要为每个季度和年份进行列合并和行合并的交叉表。
如果有人可以帮助我,为它编写任何示例代码/伪代码,这对我来说将非常有帮助。从过去的 4 天开始,我一直坚持这个问题。我不知道如何做这部分,因为我从未使用过任何 exclsheet API。这里我使用的是 APACHE POI -3.15 版本。
到目前为止的代码,其中导出 Header 部分:
//Create a Row
Row headerRow = sheet.createRow(0);
//int totalcolumns = rowHeader.size() + columnHeader.size();
int mergeHeader = 0;
if(metricsColumn != null) {
mergeHeader = 1;
}
//Create Cells //[Quarter, Subcategory, Year, 2016]
int j = rowHeader.size();
int row = 0;
for (int i = 0; i < columnHeaders.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(columnHeaders.get(i));
if(j > 0) {
sheet.addMergedRegion(new CellRangeAddress(row, mergeHeader, i, i));
}
j--;
if (i == columnHeaders.size() - 1 && metricsColumn.size() > 0) {
sheet.addMergedRegion(new CellRangeAddress(0,0,i,i + metricsColumn.size() - 2));
}
cell.setCellStyle(headerCellStyle);
}
int k = 0;
int rowNum1 = 1;
int cellNum1 = 2;
Row row1 = sheet.createRow(rowNum1);
for (int i = rowHeader.size(); i < metricsColumn.size(); i++) {
while (k < metricsColumn.size()) {
Cell cell = row1.createCell(cellNum1++);
cell.setCellValue(metricsColumn.get(k));
cell.setCellStyle(headerCellStyle);
k++;
}
}
解决方案
我会让标题和数据的结构有点不同:
标题例如:
String[][] headers = new String[][] {
new String[] {"Quarter", "Subcategory", "Year", "2016", null, null, null, null},
new String[] { null, null, "Metrics", "Profit", "Profit Forecast", "Profit Margin", "Revenue", "Revenue Forecast"}
};
也许List<List<String>>
也是可能的。
行标题:
String[] quarters = new String[]{"2016 Q1", "2016 Q2", "2016 Q3", "2016 Q4"};
String[] subcategories = new String[]{"Audio Equipment", "Cameras", "Computers", "Electronics - Miscellaneous", "TV's", "Video Equipment"};
也许List<String>
也是可能的。
价值:
Double[][] values = new Double[][]{
new Double[]{9579d, 8823d, .1942, 49320d,39456d},
new Double[]{11449d, 9619d, .2007, 57040d, 53047d},
...
new Double[]{13864d, 11328d, .1687, 82203d, 70695d}
};
也许List<List<Double>>
也是可能的。
然后我还将提供结构中的基本格式说明。所以以后可以使用循环来设置它们。
例如基本表结构:
int[] columnWidths = new int[]{15*256, 15*256, 15*256, 13*256, 13*256, 13*256, 13*256, 13*256};
例如标题水平对齐:
HorizontalAlignment[][] horizontalAlignments = new HorizontalAlignment[][]{
new HorizontalAlignment[]{HorizontalAlignment.LEFT, HorizontalAlignment.CENTER, HorizontalAlignment.LEFT, HorizontalAlignment.CENTER, null, null, null, null},
new HorizontalAlignment[]{null, null, HorizontalAlignment.RIGHT, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER}
};
以及标题的合并区域:
int[][] headerMergedRegions = new int[][]{
new int[]{0, 0, 3, 7},
new int[]{0, 1, 0, 0},
new int[]{0, 1, 1, 1}
};
然后我会在默认列样式中设置常用的数字格式:
String[] columnCellStyles = new String[]{"default", "default", "default", "currency", "currency", "percent", "currency", "currency"};
...
// creating default cell style having default font
CellStyle defaultCellStyle = workbook.createCellStyle();
defaultCellStyle.setFont(defaultFont);
// we need currency style for numbers later
CellStyle currency = workbook.createCellStyle();
currency.cloneStyleFrom(defaultCellStyle);
currency.setDataFormat(format.getFormat("$#,##0"));
// we need percent style for numbers later
CellStyle percent = workbook.createCellStyle();
percent.cloneStyleFrom(defaultCellStyle);
percent.setDataFormat(format.getFormat("0.00%"));
...
// set default column styles
for (int c = 0; c < columnCellStyles.length; c++) {
String style = columnCellStyles[c];
if ("default".equals(style)) {
sheet.setDefaultColumnStyle(c, defaultCellStyle);
} else if ("currency".equals(style)) {
sheet.setDefaultColumnStyle(c, currency);
} else if ("percent".equals(style)) {
sheet.setDefaultColumnStyle(c, percent);
}
}
因此,当我们稍后设置值时,单元格样式可以来自默认列样式。为此CellStyle getPreferredCellStyle(Cell cell)
,可以使用一种方法。
对于格式化,我将依赖CellUtil的方法。因此,并非所有需要都需要CellStyles
在工作簿级别创建,但可以将单元格设置为循环。
对于单元格边框,我会使用PropertyTemplate。这使得设置边界更加容易。
完整示例:
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.PropertyTemplate;
import java.util.Map;
import java.util.HashMap;
class CreateExcelSpecial {
static CellStyle getPreferredCellStyle(Cell cell) {
// a method to get the preferred cell style for a cell
// this is either the already applied cell style
// or if that not present, then the row style (default cell style for this row)
// or if that not present, then the column style (default cell style for this column)
CellStyle cellStyle = cell.getCellStyle();
// if no explicit cell style applied then cellStyle.getIndex() is 0 for XSSF
// or 15 (0xF = the index to the default ExtendedFormatRecord (0xF)) for HSSF
if ((cell instanceof XSSFCell && cellStyle.getIndex() == 0) || (cell instanceof HSSFCell && cellStyle.getIndex() == 15)) cellStyle = cell.getRow().getRowStyle();
if (cellStyle == null) cellStyle = cell.getSheet().getColumnStyle(cell.getColumnIndex());
if (cellStyle == null) cellStyle = cell.getCellStyle();
return cellStyle;
}
public static void main(String[] args) throws Exception {
// the data
// basic table structure
int[] columnWidths = new int[]{15*256, 15*256, 15*256, 13*256, 13*256, 13*256, 13*256, 13*256};
String[] columnCellStyles = new String[]{"default", "default", "default", "currency", "currency", "percent", "currency", "currency"};
// headers content and formatting
String[][] headers = new String[][] {
new String[] {"Quarter", "Subcategory", "Year", "2016", null, null, null, null},
new String[] { null, null, "Metrics", "Profit", "Profit Forecast", "Profit Margin", "Revenue", "Revenue Forecast"}
};
HorizontalAlignment[][] horizontalAlignments = new HorizontalAlignment[][]{
new HorizontalAlignment[]{HorizontalAlignment.LEFT, HorizontalAlignment.CENTER, HorizontalAlignment.LEFT, HorizontalAlignment.CENTER, null, null, null, null},
new HorizontalAlignment[]{null, null, HorizontalAlignment.RIGHT, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER, HorizontalAlignment.CENTER}
};
VerticalAlignment[][] verticalAlignments = new VerticalAlignment[][]{
new VerticalAlignment[]{VerticalAlignment.TOP, VerticalAlignment.TOP, VerticalAlignment.CENTER, VerticalAlignment.CENTER, null, null, null, null},
new VerticalAlignment[]{null, null, VerticalAlignment.BOTTOM, VerticalAlignment.BOTTOM, VerticalAlignment.BOTTOM, VerticalAlignment.BOTTOM, VerticalAlignment.BOTTOM, VerticalAlignment.BOTTOM}
};
Boolean[][] wrapTexts = new Boolean[][]{
new Boolean[]{false, false, false, false, null, null, null, null},
new Boolean[]{null, null, false, false, true, true, false, true}
};
int[][] headerMergedRegions = new int[][]{
new int[]{0, 0, 3, 7},
new int[]{0, 1, 0, 0},
new int[]{0, 1, 1, 1}
};
// row headers
String[] quarters = new String[]{"2016 Q1", "2016 Q2", "2016 Q3", "2016 Q4"};
String[] subcategories = new String[]{"Audio Equipment", "Cameras", "Computers", "Electronics - Miscellaneous", "TV's", "Video Equipment"};
// data
Double[][] values = new Double[][]{
new Double[]{9579d, 8823d, .1942, 49320d,39456d},
new Double[]{11449d, 9619d, .2007, 57040d, 53047d},
new Double[]{4901d, 3784d, .2002, 24480d, 21298d},
new Double[]{12444d, 9525d, .1989, 62576d, 53815d},
new Double[]{8820d, 8059d, .2019, 43675d, 35814d},
new Double[]{16375d, 12986d, .2044, 80130d, 70514d},
new Double[]{8526d, 6929d, .1578, 54020d, 49698d},
new Double[]{11602d, 9578d, .1731, 67032d, 63680d},
new Double[]{4675d, 4380d, .1683, 27780d, 25752d},
new Double[]{11699d, 9421d, .1660, 70469d, 54966d},
new Double[]{9386d, 7179d, .1752, 53563d, 49974d},
new Double[]{10150d, 9213d, .1696, 59864d, 48490d},
new Double[]{8508d, 6772d, .1716, 49571d, 47092d},
new Double[]{16429d, 13529d, .1852, 88712d, 83389d},
new Double[]{6009d, 5391d, .1805, 33295d, 29200d},
new Double[]{11792d, 9791d, .1721, 68534d, 64285d},
new Double[]{9243d, 7952d, .1715, 53886d, 49953d},
new Double[]{14282d, 11679d, .1803, 79193d, 74441d},
new Double[]{10999d, 8538d, .1560, 70511d, 65575d},
new Double[]{14475d, 11433d, .1632, 88718d, 78515d},
new Double[]{5765d, 5029d, .1677, 34373d, 31847d},
new Double[]{11335d, 9567d, .1528, 74168d, 62672d},
new Double[]{11990d, 11230d, .1683, 71255d, 64414d},
new Double[]{13864d, 11328d, .1687, 82203d, 70695d}
};
try (Workbook workbook = new XSSFWorkbook();
FileOutputStream fileout = new FileOutputStream("./Excel.xlsx") ) {
// we need PropertyTemplate later
PropertyTemplate propertyTemplate = new PropertyTemplate();
// we need properties map for cell styles later
Map<String, Object> properties;
// we need DataFormat later
DataFormat format = workbook.createDataFormat();
// creating default font
Font defaultFont = workbook.createFont();
defaultFont.setFontName("Calibri");
defaultFont.setFontHeightInPoints((short)12);
// we need font in bold and white for headings
Font defaultFontWhite = workbook.createFont();
defaultFontWhite.setFontName("Calibri");
defaultFontWhite.setFontHeightInPoints((short)12);
defaultFontWhite.setBold(true);
defaultFontWhite.setColor(IndexedColors.WHITE.getIndex());
// creating default cell style having default font
CellStyle defaultCellStyle = workbook.createCellStyle();
defaultCellStyle.setFont(defaultFont);
// we need currency style for numbers later
CellStyle currency = workbook.createCellStyle();
currency.cloneStyleFrom(defaultCellStyle);
currency.setDataFormat(format.getFormat("$#,##0"));
// we need percent style for numbers later
CellStyle percent = workbook.createCellStyle();
percent.cloneStyleFrom(defaultCellStyle);
percent.setDataFormat(format.getFormat("0.00%"));
Sheet sheet = workbook.createSheet();
// set column widths
for (int c = 0; c < columnWidths.length; c++) sheet.setColumnWidth(c, columnWidths[c]);
// set default column styles
for (int c = 0; c < columnCellStyles.length; c++) {
String style = columnCellStyles[c];
if ("default".equals(style)) {
sheet.setDefaultColumnStyle(c, defaultCellStyle);
} else if ("currency".equals(style)) {
sheet.setDefaultColumnStyle(c, currency);
} else if ("percent".equals(style)) {
sheet.setDefaultColumnStyle(c, percent);
}
}
int rowIdx = 0; // looping row index
int colIdx = 0; // looping column index
// input and format headers
int i = 0;
for (String[] headerRow : headers) {
Row row = sheet.createRow(rowIdx++);
colIdx = 0;
int j = 0;
for (String header : headerRow) {
Cell cell = row.createCell(colIdx++);
cell.setCellValue(header);
properties = new HashMap<String, Object>();
properties.put(CellUtil.FILL_PATTERN, FillPatternType.SOLID_FOREGROUND);
properties.put(CellUtil.FILL_FOREGROUND_COLOR, IndexedColors.BLUE.getIndex()); //do using only IndexedColors for fills
properties.put(CellUtil.FONT, defaultFontWhite.getIndexAsInt()); // white font
if (horizontalAlignments.length == headers.length) {
HorizontalAlignment[] hAligns = horizontalAlignments[i];
if (hAligns.length == headerRow.length) {
HorizontalAlignment hAlign = hAligns[j];
properties.put(CellUtil.ALIGNMENT, hAlign); // horizontal alignment
}
}
if (verticalAlignments.length == headers.length) {
VerticalAlignment[] hAligns = verticalAlignments[i];
if (hAligns.length == headerRow.length) {
VerticalAlignment vAlign = hAligns[j];
properties.put(CellUtil.VERTICAL_ALIGNMENT, vAlign); // vertical alignment
}
}
if (wrapTexts.length == headers.length) {
Boolean[] isWrapTexts = wrapTexts[i];
if (isWrapTexts.length == headerRow.length) {
Boolean isWrapText = isWrapTexts[j];
properties.put(CellUtil.WRAP_TEXT, isWrapText); // wrap text
}
}
CellUtil.setCellStyleProperties(cell, properties); //do using CellUtil for **add** new properties to already applied cell styles
j++;
}
i++;
}
// set header merged regions
for (int[] mergedRegion : headerMergedRegions) {
if (mergedRegion.length == 4) {
sheet.addMergedRegion(new CellRangeAddress(mergedRegion[0], mergedRegion[1], mergedRegion[2], mergedRegion[3]));
}
}
// draw header borders
propertyTemplate.drawBorders(new CellRangeAddress(0, headers.length-1, 0, headers[0].length-1), BorderStyle.MEDIUM, IndexedColors.WHITE.getIndex(), BorderExtent.ALL); //since we have merged regions we can simply drawing all borders here
// input and format row headers
for (String quarter : quarters) {
Row row = sheet.createRow(rowIdx++);
Cell cell = row.createCell(0); // quarter in column 0 (A)
cell.setCellValue(quarter);
CellUtil.setVerticalAlignment(cell, VerticalAlignment.TOP); // quarter cells are top aligned
for (String subcategory : subcategories) {
cell = row.createCell(1); // subcategory in column 1 (B)
cell.setCellValue(subcategory);
sheet.addMergedRegion(new CellRangeAddress(rowIdx, rowIdx, 1, 2));
row = sheet.createRow(rowIdx++);
}
rowIdx--;
sheet.addMergedRegion(new CellRangeAddress(rowIdx-subcategories.length, rowIdx-1, 0, 0));
}
rowIdx -= quarters.length * subcategories.length; // reset the rowIdx
// input values - number formats came from default column style
for (Double[] rowValues : values) {
Row row = sheet.getRow(rowIdx++); //get the row instead of creating it
colIdx = 3; // values are from column 3 (D) on
for (Double value : rowValues) {
Cell cell = row.createCell(colIdx++);
cell.setCellValue(value);
cell.setCellStyle(getPreferredCellStyle(cell));
}
}
// draw data borders
propertyTemplate.drawBorders(new CellRangeAddress(headers.length, headers.length+values.length-1, 0, headers[0].length-1), BorderStyle.MEDIUM, IndexedColors.LIGHT_ORANGE.getIndex(), BorderExtent.ALL); //since we have merged regions we can simply drawing all borders here
//apply the PropertyTemplate borders
propertyTemplate.applyBorders(sheet);
workbook.write(fileout);
}
}
}
推荐阅读
- java - Google Photo Api Upload Error : "code": 3, "Failed: There was an error while trying to create this media item
- r - 将列表放入R中的单个数据框中
- erlang - 如何在混合项目设置中启动 Erlang shell?
- arduino - 将 Arduino Nano PWM 定时器 1 设置为 10 位和 16 kHz 的代码
- c# - 为什么我的代码说我的对象没有定义?
- vba - 如何从 OnContentChanged 事件中获取 LibreOffice calc 中的 XRange?
- c# - 我应该如何处理 LINQ 内部异常?
- c# - 如何使用 xslt 获取与特定元素值匹配的最后一条记录?
- c# - EF-Core 5:IDbContextTransaction 没有“GetDbContextTransaction()”的定义。如何正确使用不同的上下文?
- node.js - 使用异步 axios 获取服务器错误响应