导出xlsx使用apache poi 3.13 on weblogic:文件格式或扩展名无效 [英] Exporting xlsx using apache poi 3.13 on weblogic : File format or extension not valid
问题描述
此前,我使用Apache POI 2.5.1使用 HSSFWorkbook
导出 .xls
文件。
将更新的Apache POI更改为3.13,我使用 SXSSFWorkbook
导出文件导出 .xlsx
文件,但导出损坏的文件。
MS Excel无法使用文件格式或扩展名无效打开文件。
<请注意,这个问题我只面对
WebLogic
服务器,它可以正常使用 JBoss
。 任何人都可以帮助我在这里做错什么?
代码:
列表< JRField> fields = ds.getFields();
SXSSFWorkbook wb = new SXSSFWorkbook();
SXSSFSheet sheet = wb.createSheet(Sheet1);
try {
CellStyle cellStyle = wb.createCellStyle();
CellStyle cellStyleColName = wb.createCellStyle();
CellStyle cellStyleTitle = wb.createCellStyle();
字体boldFont = wb.createFont();
boldFont.setFontHeightInPoints((short)16);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//身体的单元格样式
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(($#,## 0 _); [Red]($#,## 0))) ;
cellStyle.setWrapText(true);
//列名称的单元格样式
cellStyleColName.setDataFormat(HSSFDataFormat.getBuiltinFormat(($#,## 0 _); [Red]($#,## 0)) );
cellStyleColName.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyleColName.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // single line border
cellStyleColName.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //单行边框
//标题的单元格样式
cellStyleTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat(($#,## 0 _); [Red]($#,## 0)));
cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyleTitle.setFont(boldFont);
//创建标题行
行row1 = sheet.createRow((short)0);
//创建标题行
单元格cell1 = row1.createCell((short)0);
cell1.setCellValue(Demo Title);
cell1.setCellStyle(cellStyleTitle);
//标题区域
CellRangeAddress regionTitle = new CellRangeAddress((short)0,// From Row
(short)0,//从Col
)0,// To Row
(short)(this.displayCols.size() - 1)//到Col
);
sheet.addMergedRegion(regionTitle);
//列名行
int j = 0;
行row2 = sheet.createRow((short)1);
for(ReportColumn col:this.displayCols)
{
单元格cell2 = row2.createCell((short)j ++);
cell2.setCellValue(col.getDisplayName());
cell2.setCellStyle(cellStyleColName);
}
int i = 2;
while(ds.next()){
行行= sheet.createRow((short)0 + i);
int k = 0;
for(JRField field:fields){
String fieldAsString =(ds.getFieldValue(field)!= null?ds.getFieldValue(field).toString():null);
单元格= rows.createCell((short)k ++);
cell.setCellStyle(cellStyle);
cell.setCellValue(fieldAsString);
}
i ++;
if(i> RECORD_LIMIT_FROM_POI){
log.info(来自poi的行限制达到#1048576,导出的数据被截断);
break;
}
}
wb.write(os);
}
catch(异常e){
log.error(createXlsFile方法中的错误,e);
}
尝试失败
- 从
application / vnd.ms-excel
到$ $更新了响应标题中的mime类型添加xlsx = vnd.openxmlformats-officedocument.spreadsheetml.sheet
/ code>在WebLogic的自定义MIME映射文件中
这可能更多资源很重,但是你尝试过:
XSSFWorkbook wb = new SXSSFWorkbook();
XSSFSheet sheet = wb.createSheet(Sheet1);
而不是SXSSF。
这里有各种类型的一些讨论: HSSFWorkbook和XSSFWorkbook以及XSSFWorkbook和SXSSFWorkbook的优点/缺点
Earlier I was using Apache POI 2.5.1 to export .xls
file using HSSFWorkbook
.
With updated Apache POI to 3.13 I am exporting .xlsx
file using SXSSFWorkbook
but its exporting corrupted file.
MS Excel failed to open file with File format or extension not valid error.
Note that this issue I am facing only on WebLogic
server, it works fine with JBoss
.
Anybody can help what I am doing wrong here ?
Code:
List<JRField> fields = ds.getFields();
SXSSFWorkbook wb = new SXSSFWorkbook();
SXSSFSheet sheet = wb.createSheet("Sheet1");
try {
CellStyle cellStyle = wb.createCellStyle();
CellStyle cellStyleColName = wb.createCellStyle();
CellStyle cellStyleTitle = wb.createCellStyle();
Font boldFont = wb.createFont();
boldFont.setFontHeightInPoints((short)16);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Cell Style for body
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
cellStyle.setWrapText(true);
// Cell Style for Column Names
cellStyleColName.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
cellStyleColName.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyleColName.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // single line border
cellStyleColName.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // single line border
// Cell Style for Title
cellStyleTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
cellStyleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyleTitle.setFont(boldFont);
// Creating Title Row
Row row1 = sheet.createRow((short) 0);
// Creating the Title line
Cell cell1 = row1.createCell((short) 0);
cell1.setCellValue("Demo Title");
cell1.setCellStyle(cellStyleTitle);
// Title Region
CellRangeAddress regionTitle = new CellRangeAddress( (short) 0, // From Row
(short) 0, // From Col
(short) 0, // To Row
(short) (this.displayCols.size()-1) // To Col
);
sheet.addMergedRegion(regionTitle);
// Column Name Row
int j =0;
Row row2 = sheet.createRow((short) 1);
for (ReportColumn col : this.displayCols)
{
Cell cell2 = row2.createCell((short) j++);
cell2.setCellValue(col.getDisplayName());
cell2.setCellStyle(cellStyleColName);
}
int i =2;
while (ds.next()) {
Row rows = sheet.createRow((short) 0 + i);
int k = 0;
for (JRField field : fields) {
String fieldAsString = (ds.getFieldValue(field) != null ? ds.getFieldValue(field).toString():null);
Cell cell = rows.createCell((short) k++);
cell.setCellStyle(cellStyle);
cell.setCellValue(fieldAsString);
}
i++;
if (i > RECORD_LIMIT_FROM_POI){
log.info("Row limit from poi reached #1048576 and exported data is truncated.");
break;
}
}
wb.write(os);
}
catch (Exception e) {
log.error("error in createXlsFile method", e);
}
Failed attempts:
- Updated mime type in response header from
application/vnd.ms-excel
tovnd.openxmlformats-officedocument.spreadsheetml.sheet
- Added
xlsx=vnd.openxmlformats-officedocument.spreadsheetml.sheet
in custom mime mapping file for WebLogic
This may be more resource heavy, but have you tried:
XSSFWorkbook wb = new SXSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet1");
instead of SXSSF.
There is some discussion between the various types here: HSSFWorkbook vs XSSFWorkbook and the advantages/disadvantages of XSSFWorkbook and SXSSFWorkbook?
这篇关于导出xlsx使用apache poi 3.13 on weblogic:文件格式或扩展名无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!