Apache poi XSSF创建Excel文件-Create返回格式或文件扩展名无效的空文件

Apache poi XSSF Creating Excel Files - Create returns empty file with format or file extension not valid?

本文关键字：文件 扩展名 无效 格式 -Create XSSF poi 创建 Excel Apache 返回      更新时间：2024-05-22

控制器类的相关部分：

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

在这种情况下，ReportService只从数据库中获取一些信息，并从CreateTable类中调用newTableCompare（）。

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

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打开它，就会显示格式或文件扩展名无效的错误。有人知道如何解决这个问题吗？或者我做错了什么？

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

这是我自己的解决方案，我在解决问题时忘记添加它：

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(); 
}
}

我希望这对某人有用。

相关文章：