Java 使用 jxl 将数据添加到 Excel 文件

Adding data to an Excel file using jxl

提问by Hurdler

I'm creating an excel file using jxl. At first is should contain only some formatting and information about styles. Then, it should be updated every time someone adds new data to it.

我正在使用 jxl 创建一个 excel 文件。首先应该只包含一些关于样式的格式和信息。然后，每次有人向其中添加新数据时都应该更新它。

public class WriteExcel {

    private static WritableWorkbook workbook;
    private static WritableCellFormat timesStandard;
    private String inputFile;
    final private static int FONT_SIZE = 12;


    public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
    }

    private void prepareSheet(WritableSheet sheet) throws WriteException {
        sheet.mergeCells(0, 0, 1, 0);
        sheet.mergeCells(3, 0, 4, 0);
        sheet.mergeCells(6, 0, 7, 0);

        WritableFont times12pt = new WritableFont(WritableFont.TIMES, FONT_SIZE);
        timesStandard = new WritableCellFormat(times12pt);

        CellView cv = new CellView();
        cv.setFormat(timesStandard);
    }

    public void write() throws IOException, WriteException {
        File file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));       

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        workbook.createSheet("First", 0); 
        WritableSheet excelSheet = workbook.getSheet(0);
        prepareSheet(excelSheet);

        workbook.write();
        workbook.close();
    }

    public static void main(String[] args) throws WriteException, IOException {
        WriteExcel test = new WriteExcel();
        test.setOutputFile("c:/Users/H/Desktop/test.xls");
        test.write();
    }
}

I understand that I need another class that would let me access the file and add some data to it:

我知道我需要另一个类来让我访问文件并向其中添加一些数据：

class Modify {

    private static Logger logger = Logger.getLogger(Modify.class);
    private File inputWorkbook;
    private File outputWorkbook;

    public Modify(String input, String output) {
        inputWorkbook = new File(input);
        outputWorkbook = new File(output);
        logger.info("Input file:  " + input);
        logger.info("Output file:  " + output);
    }

    public void readWrite() throws IOException, BiffException, WriteException {
        logger.info("Reading...");
        Workbook w1 = Workbook.getWorkbook(inputWorkbook);

        logger.info("Copying...");
        WritableWorkbook w2 = Workbook.createWorkbook(outputWorkbook, w1);

        if (inputWorkbook.getName().equals("test.xls")) {
            modify(w2);
        }

        w2.write();
        w2.close();
        logger.info("Done");
    }

    private void modify(WritableWorkbook w) throws WriteException {
        logger.info("Modifying...");

        WritableSheet sheet = w.getSheet("First");

        //createContent(sheet); // contains methods responsible for adding data, for example:
        addNumber(sheet, cols, rows, 2);

    private static void addNumber(WritableSheet sheet, int column, int row, double d) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, d, timesStandard);
        sheet.addCell(number);

    }
}

But I end up with a blank Excel file with the merged cells only.

但我最终得到一个只有合并单元格的空白 Excel 文件。

How do I introduce the modifications?

我如何引入修改？

回答by FrenchFigaro

Since you're manipulating a WritableWorkbookobject which is not a member of your class for w2, shouldn't you have your modify()method not be void but instead return a WritableWorkbook?

由于您正在操作一个WritableWorkbook不是您的类成员的对象，因此您w2不应该让您的modify()方法不是 void 而是返回一个WritableWorkbook？

As I see it, a new one is instanciated when you call modify()but all changes at the end are dropped due to scope.

在我看来，当您调用时会实例化一个新的，modify()但由于范围的原因，最后的所有更改都将被删除。

In the end you'll get something like

最后你会得到类似的东西

    private WritableWorkbook modify(WritableWorkbook w) throws WriteException {
    logger.info("Modifying...");

    WritableSheet sheet = w.getSheet("First");

    //createContent(sheet); // contains methods responsible for adding data, for example:
    addNumber(sheet, cols, rows, 2);
    return sheet;
    }

quite basically. And a similar modification for addNumber seems in order too. Then the respective calls would be sheet = addNumber(sheet, cols, rows, 2);and w2 = modify(w2);

很基本。对 addNumber 的类似修改似乎也符合要求。那么相应的调用将是sheet = addNumber(sheet, cols, rows, 2);和w2 = modify(w2);