将项目写入excel单元格 [英] Write items into excel cell

问题描述

我对 Eclipse Java 编码完全陌生.我正在尝试完成一个管理库存的项目.我遇到问题的部分是，当我尝试将项目写入 excel 单元格时，我收到错误消息，指出数组越界.

I am absolute new to the Eclipse Java coding. I am trying to finish a project for managing the inventory. The part i am having trouble with is that, when I tried to write the items into the excel cell, I got errors saying that the array is out of bounds.

PS:item 和 item.getPartname 等都定义在另一个类文件下.请帮忙.谢谢

PS: item and item.getPartname etc are all defined under another class file. Please help. thanks

            FileOutputStream os =new FileOutputStream("orderreceipt");
            //Create a new workbook for writing data
            HSSFWorkbook wb2 = new HSSFWorkbook();
            //Create a new sheet:
            HSSFSheet newsheet = wb2.createSheet("MyNewSheet");
            //Create a new row:

            for (int i=0; i<6; i++){
                HSSFRow newrow = newsheet.createRow(i);
                sentorder item = (sentorder)items.get(i);
                for (short j=0; j<5; j++){
                    HSSFCell cell = newrow.createCell(j);

                    cell.setCellValue(item.getPartname());
                    cell.setCellValue(item.getPartnumber());
                    cell.setCellValue(item.getQuantity());
                    cell.setCellValue(new Date());
                      HSSFCellStyle styleOfCell = wb2.createCellStyle();
                      styleOfCell.setDataFormat(HSSFDataFormat
                          .getBuiltinFormat("m/d/yy"));
                      styleOfCell.setFillForegroundColor(HSSFColor.AQUA.index);
                      styleOfCell.setFillPattern(HSSFCellStyle.BORDER_THIN);
                      cell.setCellStyle(styleOfCell);

                    }}
            wb2.write(os);
    }

推荐答案

我看到附上的代码有不少问题:

I can see quite a few problems with the attached code:

• 创建新 FileOutputStream 时缺少文件扩展名 - 由于您正在生成 .xls 工作簿，您可能希望将其存储在 XLS 文件中(扩展名不会自动添加)，还要确保你有一个正确的文件路径到你有写权限的目录(本地应用程序目录，在这种情况下应该没问题).

• Missing file extension when creating new FileOutputStream - since you're generating the .xls workbook, you'd probably like to store it in the XLS file (the extension is not added automatically), also just make sure you have a proper file path to the directory you have write permissions (local application dir, as in this case should be ok though).

如前所述，您要重新设置相同的单元格值 4 次

As already mentioned you are re-setting the same cell value 4 times

您多次创建相同的单元格样式(这不会在后台缓存，并且可以创建的单元格样式数量非常有限，因此如果您生成了几千行，您可能会遇到麻烦

You are creating the same cell style multiple times (this is not cached behind the scenes and there is largely limited number of cells styles which can be created so if you were generating a couple thousands of rows you might get into troubles

在编写工作簿后，您不要 flush() 和 close() 流.Streams 在 Java 中是一种需要手动关闭的宝贵资源.

You don't flush() and close() stream after writing your workbook. Streams in Java a precious resources which need to be manually closed.

如果没有堆栈跟踪，很难 100% 说出您看到的 ArrayOutOfBound 问题的来源，但是我的猜测是您正在尝试使用索引访问项目(来自项目集合)不存在，这是您从行索引而不是您拥有的项目列表驱动报告数据的结果.

Without stack trace it's difficult to say 100% where the ArrayOutOfBound issue you're seeing is coming from, however my guess would be that you're trying to access a item (from items collection) with the index that doesn't exist, which is a consequence that you're driving your report data from row indexes instead of the list of items you have.

此外，由于您对 Java 还很陌生，因此有几条准则可以让您在未来编写出更好、更不容易出错的代码:

Also, since you're quite new to Java a couple of guidelines which will allow you to produce hopefully better and less error-prone code in the future:

• 使用正确的 Java 命名约定 - 请遵循标准的 Java 命名约定 http://java.about.com/od/javasyntax/a/nameconventions.htm ，你的代码将更容易阅读和推理(尤其是当你从社区寻求帮助时) - 即 sendorder 类应该被命名为 SentOrder.

• Use proper Java naming convention - please follow standard Java naming convention http://java.about.com/od/javasyntax/a/nameconventions.htm , your code will be easier to read and reason about (especially when you're looking for help from community) - i.e. sentorder class should be named as SentOrder.

尝试将您的代码拆分成更小、更可测试的模块，即您可以有一个从主方法调用的辅助 createDataRow 方法，通常在一个内有多个内循环方法使它们非常难以测试、调试和推理.

Try to split your code into smaller, more testable modules i.e. you can have a helper createDataRow method called from your main method, in general having more than a couple of inner loops in one method makes them incredibly difficult to test, debug and reason about.

除非您确实需要生成 .xls 格式，否则请考虑使用 XSSF* 类来生成 xlsx 文档 - 它比 HSSF* 有许多改进(包括更好的 dataFormat 支持).

Unless you really need to generate .xls format, consider using XSSF* classes for generating xlsx document - it has many improvements over HSSF* (including much better dataFormat support).

考虑到这些，我重写了您的示例:

Having those in mind I've rewritten your example:

public void improved(List<SentOrder> items) throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("MyNewSheet");

    HSSFCellStyle styleOfCell = workbook.createCellStyle();
    styleOfCell.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
    styleOfCell.setFillForegroundColor(HSSFColor.AQUA.index);
    styleOfCell.setFillPattern(HSSFCellStyle.BORDER_THIN);

    int rowIndex = 0;
    for(SentOrder item : items) {
        HSSFRow row = sheet.createRow(rowIndex++);
        HSSFCell nameCell = row.createCell(0);
        nameCell.setCellValue(item.getPartName());
        HSSFCell numberCell = row.createCell(1);
        numberCell.setCellValue(item.getPartNumber());
        HSSFCell quantityCell = row.createCell(2);
        quantityCell.setCellValue(item.getQuantity());
        HSSFCell dateCell = row.createCell(3);
        dateCell.setCellValue(new Date());
        dateCell.setCellStyle(styleOfCell);
    }

    FileOutputStream os = new FileOutputStream("order_receipt.xls");
    try {            
        workbook.write(os);
    } finally {
        os.flush();
        os.close();
    }
}

这篇关于将项目写入excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！