核心点:
Workbook wb =null;
WritableWorkbook wwb=null;
try{
wb = Workbook.getWorkbook(file);
wwb=Workbook.createWorkbook(file,wb);
/**
* Creates a writable workbook with the given filename as a copy of
* the workbook passed in. Once created, the contents of the writable
* workbook may be modified
*
* @param file the output file for the copy
* @param in the workbook to copy
* @return a writable workbook
* @exception IOException
*/
wwb=Workbook.createWorkbook(file,wb); 根据现有workbook获取一份clone,可写
本篇文章主要讲jxl 中非常重要的一个抽象类Workbook,该类是一切操作Excel表格的开始。可以通过该类得到工作簿,也可以创建一个新的可写工作簿。下面就让我们一起来看看怎样使用该类。
得到已存在Excel表格的工作簿 Workbook.getWorkbook:
- import java.io.*;
- import jxl.Workbook;
- import jxl.WorkbookSettings;
- import jxl.read.biff.BiffException;
-
- public class Tgetworkbook {
- public static void main(String args[]) {
- try {
-
- File file = new File("D://JEtest/测试.xls");
- Workbook book1 = Workbook.getWorkbook(file);
- System.out.println("sheet's number: " + book1.getNumberOfSheets());
- book1.close();
-
-
- WorkbookSettings ws = new WorkbookSettings();
- Workbook book2 = Workbook.getWorkbook(file,ws);
- System.out.println("sheet's number: " + book2.getNumberOfSheets());
- book2.close();
-
-
- InputStream is = new FileInputStream(file);
- Workbook book3 = Workbook.getWorkbook(is);
- System.out.println("sheet's number: " + book3.getNumberOfSheets());
- book3.close();
- is.close();
-
-
- InputStream is2 = new FileInputStream(file);
- Workbook book4 = Workbook.getWorkbook(is2, ws);
- System.out.println("sheet's number: " + book4.getNumberOfSheets());
- book4.close();
- is2.close();
-
-
- }catch (IOException | BiffException e) {
- System.out.println("Exception: " + e);
- }
- }
-
- }
以这种方式得到的工作簿的前题是Excel表格已存在。其中有一个WorkbookSettings 类,该类是设置工作簿相关的高级属性,包括工作簿的字符集、字符编码等属性,有一个方法
public void
setGCDisabled
(boolean disabled)
在Web相关工作时,可能回影响效率,需要把垃圾回收属性设置为禁止,true 为禁止垃圾回收。
有兴趣可以看看API 了解更多设置。
创建可写工作簿 Workbook.createWorkbook :
- import java.io.*;
- import jxl.*;
- import jxl.read.biff.BiffException;
- import jxl.write.*;
-
- public class TCreateWorkbook2 {
- public static void main(String args[]) throws IOException, WriteException, BiffException {
- try {
- File file = new File("D://JEtest/测试1.xls");
-
-
- WritableWorkbook wb1 = Workbook.createWorkbook(file);
- System.out.println("sheet numbers: " + wb1.getNumberOfSheets());
- WritableSheet wsheet1 = wb1.createSheet("sheet1", 0);
-
- System.out.println("sheet numbers: " + wb1.getNumberOfSheets());
-
- wb1.write();
- wb1.close();
-
-
-
-
-
- File file2 = new File("D://JEtest/测试2.xls");
-
- Workbook book = Workbook.getWorkbook(file2);
- WritableWorkbook wb2 = Workbook.createWorkbook(file2, book);
-
-
-
-
-
- System.out.println("sheet numbers: " + wb2.getNumberOfSheets());
-
- wb2.write();
- wb2.close();
-
-
-
-
- File file4 = new File("D://JEtest/测试4.xls");
-
- WorkbookSettings ws = new WorkbookSettings();
- WritableWorkbook wb4 = Workbook.createWorkbook(file4, ws);
- WritableSheet wsheet4 = wb4.createSheet("sheet2", 0);
- System.out.println("sheet2 numbers: " + wb4.getNumberOfSheets());
- wb4.write();
- wb4.close();
-
-
-
- WritableWorkbook wb5 = Workbook.createWorkbook(file2, book, ws);
- wb5.write();
- wb5.close();
-
- }catch (IOException | WriteException | BiffException e) {
- System.out.println("Exception: " + e);
- throw e;
- }
- }
-
- }
也可以以输出流的方式创建:
public static WritableWorkbook createWorkbook(java.io.OutputStream os)
public static WritableWorkbook createWorkbook(java.io.OutputStream os, WorkbookSettings ws)
public static WritableWorkbook createWorkbook(java.io.OutputStream os, Workbook in)
public static WritableWorkbook createWorkbook(java.io.OutputStream os, Workbook in, WorkbookSettings ws)
这里需要注意的是以 public static WritableWorkbook createWorkbook(Java.io.OutputStream os, Workbook in) 方式创建可写工作簿,在写入时一直报错,目前没有搞明白是什么原因。希望大神能留言告诉我。
Workbook其他的方法:
- import java.io.File;
- import java.io.IOException;
- import java.util.Arrays;
- import jxl.Workbook;
- import jxl.Sheet;
- import jxl.Cell;
- import jxl.Range;
- import jxl.read.biff.BiffException;
-
- public class Tworkbook {
- public static void main(String args[]) throws IOException, BiffException {
- try {
-
- File file = new File("D://JEtest/测试.xls");
- Workbook wb = Workbook.getWorkbook(file);
-
-
- System.out.println("Is the sheet protect? result: " + wb.isProtected());
-
-
-
-
-
-
- String [] rangeNameList = wb.getRangeNames();
- System.out.println("the range[]: " + Arrays.toString(rangeNameList));
-
-
-
- Range[] ranges;
- ranges = wb.findByName("name");
- System.out.println("the ranges[] length: " + ranges.length);
-
-
-
- Cell cell = wb.findCellByName("name");
- System.out.println("cell 或 range的左上单元格的内容: " + cell.getContents());
-
-
-
- Cell cellA1 = wb.getCell("第一页!A3");
- System.out.println("第一页里A1的内容: " + cellA1.getContents());
-
-
-
- int sheetnumber = wb.getNumberOfSheets();
- System.out.println("工作表个数为: " + sheetnumber);
-
-
-
- Sheet sheet0 = wb.getSheet(0);
- System.out.println("第一个表的名字是: " + sheet0.getName());
-
-
- Sheet sheet1 = wb.getSheet("第一页");
-
-
-
- Sheet [] sheetlist;
- sheetlist = wb.getSheets();
- System.out.println("工作表个数为: " + sheetlist.length);
-
-
-
- String [] sheetnamelist = wb.getSheetNames();
- System.out.println("工作表个数:" + sheetnamelist.length + " 工作表名称: " + Arrays.toString(sheetnamelist));
-
-
-
- String version = wb.getVersion();
- System.out.println(version);
-
- wb.close();
-
- }catch (IOException | BiffException | IndexOutOfBoundsException e) {
- System.out.println("Exception: " + e);
- throw e;
- }
- }
-
- }