Apache poi XSSF 创建 Excel 文件 - 创建返回格式或文件扩展名无效的空文件? [英] Apache poi XSSF Creating Excel Files - Create returns empty file with format or file extension not valid?

问题描述

Controller 类的相关部分:

The relevant part of the Controller class:

@RequestMapping(value = "/DBCompare/download", method = RequestMethod.GET)
public void handleDownloadDBCompareReportGet(HttpServletResponse response, Model model){
try{
reportService.downloadResultsDto(reportDto);
}
finally{ 
}
}

本例中的 ReportService 仅从数据库中获取一些信息，并从 CreateTable 类中调用 newTableCompare().

The ReportService in this case only fetches some information from the database, and calls newTableCompare() from the CreateTable class.

下面的类应该创建一个新的 Excel 表格、工作簿等.

The following class should create a new Excel table, workbook, ect.

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
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 CreateTable {

public void newTableCompare(Class1 c1, Class1 c2) throws FileNotFoundException, 
IOException {
String tableName = "DB compare report for " + c1.getName() + "and" +c2.getName();
Workbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet) wb.createSheet();

XSSFTable table = sheet.createTable();
table.setDisplayName("Test");       
CTTable cttable = table.getCTTable();

CTTableStyleInfo style = cttable.addNewTableStyleInfo();
style.setName("TableStyle1");
style.setShowColumnStripes(true);
style.setShowRowStripes(true);

AreaReference reference = new AreaReference(new CellReference(0, 0), 
            new CellReference(2,2));
cttable.setRef(reference.formatAsString());
cttable.setId(1);
cttable.setName("Test");
cttable.setTotalsRowCount(1);

CTTableColumns columns = cttable.addNewTableColumns();
columns.setCount(3);
CTTableColumn column;
XSSFRow row;
XSSFCell cell;
for(int i=0; i<3; i++) {
column = columns.addNewTableColumn();
column.setName("Column");
column.setId(i+1);
row = sheet.createRow(i);
for(int j=0; j<3; j++) {

cell = row.createCell(j);
if(i == 0) {
cell.setCellValue("Column"+j);
} else {
cell.setCellValue("0");
}
}
}
//this correctly creates a table in workbook
FileOutputStream fos = new FileOutputStream("workbook.xlsx");
wb.write(fos);
fos.close();
wb.close();

}
}

但最终，当触发下载时，结果只是一个空文件，如果我尝试用 Excel 打开它，则会显示格式或文件扩展名无效错误.有人知道如何解决这个问题或我做错了什么吗?

But in the end, when download is triggered, the result is only an empty file and if I try to open it with Excel, format or file extension not valid error is shown. Does anybody know how to fix this or what I am doing wrong?

最后我确实发现了我做错了什么.

In the end I did find out what I have been doing wrong.

推荐答案

这里是我自己的解决方案，解决的时候忘记添加了:

Here is my own sollution, I forgot to add it when I solved the issue:

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import address.Class1;
import address.Class2;

public class CreateTable {

public void newTableCompare(Class1 class1, Class1 class2,
        ReportDcResultViewDto resultDto, OutputStream outputStream)
        throws FileNotFoundException, IOException {

    Workbook wb = new XSSFWorkbook();

    XSSFSheet sheet = (XSSFSheet) wb.createSheet();
    int rowCount = 1;
    int columnCount = 7;
    ArrayList<String> columnNames = new ArrayList<String>();

    columnNames.add("filename");
    columnNames.add("type");
    columnNames.add("created by");
    columnNames.add("modified by");
    columnNames.add("modification date");
    columnNames.add(class1.getName());
    columnNames.add(class2.getName());

    CreationHelper creationHelper = wb.getCreationHelper();
    // Create
    XSSFRow rowHeader = sheet.createRow(0);
    for (int j = 0; j < columnCount; j++) {
        // create first row
        XSSFCell cell = rowHeader.createCell(j);
        RichTextString richString = creationHelper
                .createRichTextString(columnNames.get(j));
        Font font = wb.createFont();
        font.setBold(true);
        richString.applyFont(font);

        cell.setCellValue(richString);
        CellStyle style = wb.createCellStyle();
        style.setFont(font);

        cell.setCellStyle(style);
    }

    if (CollectionUtils.isNotEmpty(resultDto
            .getClass1ExclusiveViewDtos())) {
        for (ReportDcResultViewExclusiveDto dto : resultDto
                .getClass1ExclusiveViewDtos()) {

            XSSFRow row = sheet.createRow(rowCount);
            for (int j = 0; j < columnCount; j++) {
                XSSFCell cell = row.createCell(j);

                    switch (j) {
                    case 0:
                        cell.setCellValue(dto.getFile().getFilename());
                        break;
                    case 1:
                        cell.setCellValue(dto.getFile().getType()
                                .toString());
                        break;
                    case 2:
                        cell.setCellValue(dto.getFile().getCreatedBy()
                                .getUsername());
                        break;
                    case 3:
                        cell.setCellValue(dto.getModification().getUser()
                                .getUsername());
                        break;
                    case 4:
                        cell.setCellValue(dto.getModification().getDate().toString());
                        break;
                    case 5:
                        cell.setCellValue("yes");
                        break;
                    case 6:
                        cell.setCellValue(" ");
                        break;
                    default:
                        cell.setCellValue("Missing");
                        break;
                    }
                }
                rowCount++;
            }
        }
        for (int k = 0; k < columnCount; k++) {
            sheet.autoSizeColumn(k);
    }
    System.out.println("check rowCount: current="+rowCount);
    wb.write(outputStream);
    outputStream.close();
    wb.close(); 
}
}

我希望这对某人有用.

这篇关于Apache poi XSSF 创建 Excel 文件 - 创建返回格式或文件扩展名无效的空文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！