Java 使用 jxl 修改现有的 excel

Modifying existing excel using jxl

提问by TestUser

I m not able to edit the existing excel sheet using jxl. It always creates a new one. Can anyone please help me out with it. Please give a small sample code.

我无法使用 jxl 编辑现有的 Excel 工作表。它总是创造一个新的。任何人都可以帮我解决这个问题。请给出一个小的示例代码。

采纳答案by lalli

jxl is designed for increased read efficiency (since this is the primary use of the API). In order to improve performance, data which relates to output information (eg. all the formatting information such as fonts) is not interpreted when the spreadsheet is read, since this is superfluous when interrogating the raw data values.

jxl 旨在提高读取效率（因为这是 API 的主要用途）。为了提高性能，在读取电子表格时不解释与输出信息相关的数据（例如所有格式信息，如字体），因为在询问原始数据值时这是多余的。

However, if we need to modify this spreadsheet a handle to the various write interfaces is needed, which can be obtained using the copy method.

但是，如果我们需要修改此电子表格，则需要各种写入接口的句柄，可以使用复制方法获得。

Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));
WritableWorkbook copy = Workbook.createWorkbook(new File("temp.xls"), workbook);

This copies the information that has already been read in as well as performing the additional processing to interpret the fields that are necessary to for writing spreadsheets. The disadvantage of this read-optimized strategy is that we have two spreadsheets held in memory rather than just one, thus doubling the memory requirements.

这会复制已读入的信息，并执行附加处理以解释编写电子表格所需的字段。这种读取优化策略的缺点是我们在内存中保存了两个电子表格，而不是一个，因此内存需求加倍。

But after this, you can do whatever you want. Like:

但在此之后，您可以为所欲为。喜欢：

WritableSheet sheet2 = copy.getSheet(1); 
WritableCell cell = sheet2.getWritableCell(1, 2); 

if (cell.getType() == CellType.LABEL) 
{ 
  Label l = (Label) cell; 
  l.setString("modified cell"); 
}
copy.write(); 
copy.close();
workbook.close();

Note: this is directly taken from Andy Khan's tutorial page.

注意：这是直接取自Andy Khan 的教程页面。

回答by sboda

I know that this is quite an old question, but if anyone will encounter the same problem, then to preserve the correct formatting (font type, colouring, etc. ) you should save the cell format before casting it to Label, and then force the cell to the previous formatting. Code:

我知道这是一个很老的问题，但是如果有人会遇到同样的问题，那么为了保留正确的格式（字体类型、颜色等），您应该在将其转换为 Label 之前保存单元格格式，然后强制单元格到以前的格式。代码：

CellFormat cfm = cell.getCellFormat();
Label l = (Label) cell; 
l.setString("modified cell");
cell.setCellFormat(cfm);

回答by David Ophiuchus

//there is god example of it, you can copy in ur project and check it out, to 
//understand how  it works

Workbook wk = Workbook.getWorkbook(new File("ex.xls"));
// 
WritableWorkbook wkr = Workbook.createWorkbook(new File("modifed.xls"), wk);
/* second line makes copy of wk excel file object /creates a readable spreadsheet.
both are now similar and i can Modify exiting wkr spreadsheets */



 //next 2 line retrieve sheet number 0  and cell (1,1)


 WritableSheet getsht = wkr.getSheet(0);
 WritableCell getcl = getsht.getWritableCell(1, 1);


 //making own font 

WritableFont ft = new WritableFont(WritableFont.ARIAL, 20 , WritableFont.BOLD, true , UnderlineStyle.SINGLE); 
//making Format, which uses font
WritableCellFormat   form   =   new WritableCellFormat( ft);

Number nb = ( Number ) getcl ;          
nb.setCellFormat( form );    


wkr.write();
wkr.close();

回答by Newsonic

I personally use this code to append the xls file and create one if it doesn't exist.
Using jxl 2.6:

我个人使用此代码附加 xls 文件并在它不存在时创建一个。
使用 jxl 2.6：

    public class Excel {

        private String fileName = "excel_file.xls";
        private String sheetName = "sheet1";
        private WritableWorkbook writableWorkbook;
        private int rowCount;
        private Workbook wb;

// assigns checks if file exists or not, both cases we assign it to a WritableWorkbook // object so that we can write to it.
        private void assignWorkBook() throws IOException, BiffException {
    //        File f = new File(System.getProperty("user.dir") +"\"+fileName);
            File inp = new File(fileName);
            try{
                wb = Workbook.getWorkbook(inp);
                writableWorkbook = Workbook.createWorkbook(inp, wb);
            } catch (FileNotFoundException e){
                writableWorkbook = Workbook.createWorkbook(inp); //Create a new one
            }
        }

        public int getRowCount() {
            return rowCount;
        }

// this function writes a vector to an excel file, checks if there is already a sheet 
// with that name or not, and uses it. then we have to close the Workbook object before 
// we could write to the file, and then we save the file.
// That is, the file is always saved after writing to it.

        public void writeRow(Vector<String> playerVector) throws WriteException, IOException, BiffException {
            assignWorkBook();
            WritableSheet excelSheet;
            if(writableWorkbook.getNumberOfSheets() == 0) {
                excelSheet = writableWorkbook.createSheet(sheetName, 0);
            }
            else {
                excelSheet = writableWorkbook.getSheet(sheetName);
            }
            rowCount = excelSheet.getRows();
            int colCount = 0;
            for(String playerStat:playerVector) {
                Label label = new Label(colCount++, rowCount, playerStat);
                excelSheet.addCell(label);
            }
            if(wb != null) {
                wb.close();
            }
            writableWorkbook.write();
            writableWorkbook.close(); //everytime save it.
        }
    }