java 使用 Apache POI 将结果集转换为 Excel (*.xlsx) 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25570731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ResultSet to Excel (*.xlsx) Table Using Apache POI
提问by Indigo
I am trying to write ResultSet to Excel (*.xlsx) Tableusing Apache Poi.
我正在尝试使用 Apache Poi将ResultSet写入Excel (*.xlsx) 表。
Invalid Table Object Error in Office Excel
Office Excel 中的无效表对象错误
However, even though it writes the Excel file without any error, when I try to open it in Office Excel 2013, it shows an error and removes the table object to give only plain data view.
但是，即使它写入 Excel 文件没有任何错误，当我尝试在 Office Excel 2013 中打开它时，它也会显示错误并删除表对象以仅提供纯数据视图。
Here is the rough Sample Code using this example:
这是使用此示例的粗略示例代码：
public static void writeExcel(ResultSet rs, int sqliteRowCount, String dir) {
System.out.println("Writing Excel(*.xlsx) File...");
XSSFWorkbook workbook = null;
try {
if (rs != null) {
// Get ResultSet MetaData
ResultSetMetaData rsmd = rs.getMetaData();
// Number of columns
int numColumns = rsmd.getColumnCount();
// Number of rows
// + 1 for headers
int numRows = sqliteRowCount + 1;
workbook = new XSSFWorkbook();
// Create Excel Table
XSSFSheet sheet = workbook.createSheet("Text");
XSSFTable table = sheet.createTable();
table.setDisplayName("Test");
CTTable cttable;
cttable = table.getCTTable();
// Style configurations
CTTableStyleInfo style = cttable.addNewTableStyleInfo();
style.setName("TableStyleMedium16");
style.setShowColumnStripes(false);
style.setShowRowStripes(true);
// Set Table Span Area
AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1));
cttable.setRef(reference.formatAsString());
cttable.setId(1);
cttable.setName("Test");
cttable.setDisplayName("Test");
cttable.setTotalsRowCount(numRows);
cttable.setTotalsRowShown(false);
// Create Columns
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(numColumns);
// Create Column, Row, Cell Objects
CTTableColumn column;
XSSFRow row;
// Add Header and Columns
XSSFRow headerRow = sheet.createRow(0);
for (int i = 0; i < numColumns; i++) {
column = columns.addNewTableColumn();
column.setName("Column" + (i + 1));
column.setId(i + 1);
headerRow.createCell(i).setCellValue(rsmd.getColumnLabel(i + 1));
}
// Write each row from ResultSet
int rowNumber = 1;
while (rs.next()) {
row = sheet.createRow(rowNumber);
for (int y = 0; y < numColumns; y++) {
row.createCell(y).setCellValue(rs.getString(y + 1));
}
rowNumber++;
}
// Set AutoFilter
CTAutoFilter fltr = CTAutoFilter.Factory.newInstance();
fltr.setRef((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString());
cttable.setAutoFilter(fltr);
// sheet.setAutoFilter(CellRangeAddress.valueOf((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString()));
// Freeze Pan
sheet.createFreezePane(0, 1, 0, 2);
}
} catch (SQLException ex) {
System.out.println("SQL Error while writing Excel file!");
} finally {
try {
// Let's write the excel file now
if (workbook != null) {
String excelDir = dir + File.separator + "workbook.xlsx";
try (final FileOutputStream out = new FileOutputStream(excelDir)) {
workbook.write(out);
}
}
} catch (IOException ex) {
System.out.println("IO Error while writing Excel summary file!");
}
}
}
I know something is wrong with my code, but can't figure it out. Any idea, why this is happening, where would be potential mistake in my code.
我知道我的代码有问题，但无法弄清楚。任何想法，为什么会发生这种情况，我的代码中哪里有潜在的错误。
Update 1:
更新 1：
Table XML file in Excel archive if created using Apache POI
Excel 存档中的表格 XML 文件（如果使用 Apache POI 创建）
<?xml version="1.0" encoding="UTF-8"?>
<table displayName="Test" ref="A1:B881" id="1" name="Test" totalsRowCount="881" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" totalsRowShown="0"><autoFilter ref="A1:B881"/><tableColumns count="2"><tableColumn name="ID" id="1"/><tableColumn name="Name" id="2"/><tableStyleInfo name="TableStyleMedium2" showColumnStripes="true" showRowStripes="true"/></table>
Table XML file in Excel archive if table created manually
如果手动创建表格，Excel 存档中的表格 XML 文件
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<table xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" id="1" name="Table1" displayName="Table1" ref="A1:B881" totalsRowShown="0"><autoFilter ref="A1:B881"/><tableColumns count="2"><tableColumn id="1" name="ID"/><tableColumn id="2" name="Name"/></tableColumns><tableStyleInfo name="TableStyleLight9" showFirstColumn="0" showLastColumn="0" showRowStripes="1" showColumnStripes="0"/></table>
In addition, if I open the Excel archive, it does not have a theme folder in the one created by Apache POI but it is present in the one create manually in Office Excel. Strange.
此外，如果我打开 Excel 存档，它在 Apache POI 创建的文件夹中没有主题文件夹，但它存在于 Office Excel 中手动创建的文件夹中。奇怪的。
Update 2:Sample executable code (Using Netbeans):
更新 2：示例可执行代码（使用 Netbeans）：
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package apachepoi_exceltest;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo;
/**
*
*/
public class ApachePOI_ExcelTest {
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
String outputDir = "Your Local Directory Here";
// TODO code application logic here
HashMap<String, String> dataMap = new HashMap<>();
dataMap.put("ID 1", "Dummy Name 1");
dataMap.put("ID 2", "Dummy Name 2");
dataMap.put("ID 3", "Dummy Name 3");
dataMap.put("ID 4", "Dummy Name 4");
writeExcel(dataMap, outputDir);
}
private static void writeExcel(HashMap<String, String> dataMap, String outputDir) {
System.out.println("Writing Excel(*.xlsx) Summary File...");
XSSFWorkbook workbook = null;
try {
// Number of columns
int numColumns = 2; // ID and Name
// Number of rows
int numRows = dataMap.size() + 1; // +1 for header
// Create Workbook
workbook = new XSSFWorkbook();
// Create Excel Table
XSSFSheet sheet = workbook.createSheet("Summary");
XSSFTable table = sheet.createTable();
table.setDisplayName("Test");
CTTable cttable;
cttable = table.getCTTable();
// Style configurations
CTTableStyleInfo style = cttable.addNewTableStyleInfo();
style.setName("TableStyleMedium16");
style.setShowColumnStripes(false);
style.setShowRowStripes(true);
// Set Tabel Span Area
AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1));
cttable.setRef(reference.formatAsString());
cttable.setId(1);
cttable.setName("Test");
cttable.setDisplayName("Test");
cttable.setTotalsRowCount(numRows);
cttable.setTotalsRowShown(false);
// Create Columns
CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(numColumns);
// Create Column, Row, Cell Objects
CTTableColumn column;
XSSFRow row;
// Add ID Header
column = columns.addNewTableColumn();
column.setName("Column" + (1));
column.setId(1);
// Add Name Header
column = columns.addNewTableColumn();
column.setName("Column" + (1));
column.setId(1);
// Add Header Row
XSSFRow headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("ID");
headerRow.createCell(1).setCellValue("Name");
int rowNumber = 1;
for (Map.Entry<String, String> entry : dataMap.entrySet()) {
String id = entry.getKey();
String name = entry.getValue();
row = sheet.createRow(rowNumber);
row.createCell(0).setCellValue(id);
row.createCell(1).setCellValue(name);
rowNumber++;
}
// Set Filter (Below three lines code somehow not working in this example, so setting AutoFilter to WorkSheet)
// CTAutoFilter fltr = CTAutoFilter.Factory.newInstance();
// fltr.setRef((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString());
// cttable.setAutoFilter(fltr);
sheet.setAutoFilter(CellRangeAddress.valueOf((new AreaReference(new CellReference(0, 0), new CellReference(numRows - 1, numColumns - 1))).formatAsString()));
// Freeze First Row as header Row
sheet.createFreezePane(0, 1, 0, 2);
} catch (Exception ex) {
System.out.println("Error while writing Excel summary file!");
} finally {
try {
// Lets write the Excel File Now
if (workbook != null) {
String excelDir = outputDir + File.separator + "workbook.xlsx";
try (final FileOutputStream out = new FileOutputStream(excelDir)) {
workbook.write(out);
}
}
} catch (IOException ex) {
System.out.println("IO Error while writing Excel summary file!");
}
}
}
}
Libraries Used:
使用的库：
ooxml-schemas-1.1.jar
ooxml-schemas-1.1.jar
poi-3.11-beta2-20140822.jar
poi-3.11-beta2-20140822.jar
poi-ooxml-3.11-beta2-20140822.jar
poi-ooxml-3.11-beta2-20140822.jar
xmlbeans-2.6.0.jar
xmlbeans-2.6.0.jar
回答by Roman
What's wrong with your code is a presence of a single line. "cttable.setTotalsRowCount(numRows);" Remove it and everything will work. If in doubt, compare the XML definitions of some working table created manually in Excel and the definitions created with Apache POI
您的代码有什么问题是存在单行。"cttable.setTotalsRowCount(numRows);" 删除它，一切都会好起来的。如果有疑问，请比较在 Excel 中手动创建的某些工作表的 XML 定义和使用 Apache POI 创建的定义
回答by Eric S.
I had the same issue.
我遇到过同样的问题。
Digging deeply, I found that for some table XML data in the XLSX package, Excel is changing a single >to >after performing the repair. The XML from POI makes sense (use <and >to surround XML elements) so I have no idea why Microsoft chooses to break it.
深入挖掘，发现对于XLSX包中的一些表格XML数据，Excel在进行修复后，发生了单个>to的变化>。来自 POI 的 XML 是有意义的（使用<和>包围 XML 元素），所以我不知道微软为什么选择打破它。
If its the same case for you, I'd not worry too much about it.
如果你也有同样的情况，我不会太担心。
If you want to see if you have this particular difference:
如果你想看看你是否有这个特殊的区别：
- Create XLSX with POI
- Repair XLSX with Excel and save to new file
- Open both files with ZIP editor (e.g. 7Zip)
- Find xl/tables/table1.xml
- Export both XML files (POI and Excel-repaired)
- Diff the files
- 使用 POI 创建 XLSX
- 用 Excel 修复 XLSX 并保存到新文件
- 使用 ZIP 编辑器（例如 7Zip）打开两个文件
- 查找 xl/tables/table1.xml
- 导出两个 XML 文件（POI 和 Excel 修复）
- 区分文件
回答by user2334968
You have not created your table correctly. Check:
您没有正确创建您的表。查看：
- Did you create header columns in cttable?
- Did you create the same header columns through cell.setCellValue?
Remove empty first header column(POI BUG) at the end
CTTable().getTableColumns().removeTableColumn(0);
- 您是否在 cttable 中创建了标题列？
- 您是否通过 cell.setCellValue 创建了相同的标题列？
删除最后空的第一个标题列（POI BUG）
CTTable().getTableColumns().removeTableColumn(0);
Put debug into XSSFTable.class, method updateHeaders().
将调试放入 XSSFTable.class，方法 updateHeaders()。
If your table is not created properly, then
如果您的表未正确创建，则
XSSFRow row = sheet.getRow(headerRow);
will be NULL in
将在 NULL
/**
* Synchronize table headers with cell values in the parent sheet.
* Headers <em>must</em> be in sync, otherwise Excel will display a
* "Found unreadable content" message on startup.
*/
@SuppressWarnings("deprecation")
public void updateHeaders(){
XSSFSheet sheet = (XSSFSheet)getParent();
CellReference ref = getStartCellReference();
if(ref == null) return;
int headerRow = ref.getRow();
int firstHeaderColumn = ref.getCol();
XSSFRow row = sheet.getRow(headerRow);
if (row != null && row.getCTRow().validate()) {
int cellnum = firstHeaderColumn;
for (CTTableColumn col : getCTTable().getTableColumns().getTableColumnArray()) {
XSSFCell cell = row.getCell(cellnum);
if (cell != null) {
col.setName(cell.getStringCellValue());
}
cellnum++;
}
}
}
