jxl 读写Excel实例(設置cell format 格式)
来源:互联网 发布:淘宝天机充值平台 编辑:程序博客网 时间:2024/06/01 19:08
本篇文章主要讲的是jxl 操作Excel的三个简单的实例,一个是读取Excel表格数据,一个是 写一个新的Excel表格,最后是更新现有的Excel表格。在开始全面学习 jxl 之前,我们先看几个例子,让大家大概整体的了解一下jxl 是怎么操作Excel表的,以使之后更有兴趣,更好地学习jxl。
读Excel表格数据:
- import java.io.File;
- import java.io.IOException;
- import jxl.Cell;
- import jxl.Sheet;
- import jxl.Workbook;
- import jxl.read.biff.BiffException;
-
-
-
-
-
- public class TRead {
-
- public static void read (Workbook book) {
- int rows;
-
- int sheetNumber = book.getNumberOfSheets();
-
- String [] sheetNameList = book.getSheetNames();
-
- Sheet [] sheetList = book.getSheets();
-
-
- for(int i = 0;i < sheetNumber;i++) {
- System.out.println("############## " + sheetNameList[i] + " ##############");
-
- rows = sheetList[i].getRows();
- for(int j = 0;j < rows;j++) {
-
- Cell [] cellList = sheetList[i].getRow(j);
- for (Cell cell : cellList) {
- System.out.print(cell.getContents() + " ");
- }
- System.out.println();
- }
- }
- }
- public static void main(String [] args) throws IOException, BiffException {
- try {
-
- File file = new File("D://JEtest//read.xls");
-
- Workbook book = Workbook.getWorkbook(file);
- TRead.read(book);
- book.close();
- }catch (IOException | BiffException e) {
- System.out.println("Exception: " + e);
- throw e;
- }
- }
- }
读Excel表格的步奏是:工作簿--》工作表--》单元格。 当然有时候对数据经行处理要知道数据的格式,这个要在取得单元格的时候对它进行数据类型的判断,然后再转化成相应的类型,从而得到原数据类型的值。关于数据类型会在之后的文章中介绍。
生成一个新的Excel表格,并向里面写数据:
- import java.io.File;
- import java.io.IOException;
- import jxl.Workbook;
- import jxl.read.biff.BiffException;
- import jxl.write.Label;
- import jxl.write.Number;
- import jxl.write.Boolean;
- import jxl.write.DateFormat;
- import jxl.write.DateTime;
- import jxl.write.NumberFormat;
- import jxl.write.WritableCellFormat;
- import jxl.write.WritableFont;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
- import jxl.write.WriteException;
-
-
-
-
-
-
- public class TWrite {
-
- public static void write(WritableWorkbook wb) throws WriteException {
- WritableSheet wSheet = wb.createSheet("sheet1", 0);
-
-
- Label labelC = new Label(0, 0, "Label cell");
- wSheet.addCell(labelC);
-
-
- Number numberC = new Number(0, 1, 3.1415926);
- wSheet.addCell(numberC);
-
-
- Boolean booleanC = new Boolean(0, 2, true);
- wSheet.addCell(booleanC);
-
-
- DateTime dateTimeC = new DateTime(0, 3, new java.util.Date());
- wSheet.addCell(dateTimeC);
-
-
- WritableFont wFont = new WritableFont(
- WritableFont.TIMES, 18, WritableFont.BOLD, true);
- WritableCellFormat wCellFormatC = new WritableCellFormat(wFont);
- Label labelCF = new Label(1, 0, "Label Cell", wCellFormatC);
- wSheet.addCell(labelCF);
-
-
- NumberFormat nFormat = new NumberFormat("#.##");
- WritableCellFormat wCellFormatN = new WritableCellFormat(nFormat);
- Number numberNF = new Number(1, 1, 3.1415926, wCellFormatN);
- wSheet.addCell(numberNF);
-
-
- DateFormat dFormat = new DateFormat(
- "yyyy MM dd hh:mm:ss");
- WritableCellFormat wCellFormatD = new WritableCellFormat(dFormat);
- DateTime dateTimeDF = new DateTime(1, 3, new java.util.Date(), wCellFormatD);
- wSheet.addCell(dateTimeDF);
-
- }
- public static void main(String [] args) throws IOException, BiffException, WriteException {
- try {
-
-
- File file = new File("D://JEtest//write.xls");
-
- WritableWorkbook wb = Workbook.createWorkbook(file);
- TWrite.write(wb);
-
-
- wb.write();
-
- wb.close();
- }catch (IOException | WriteException e) {
- System.out.println("Exception: " + e);
- throw e;
- }
- }
- }
生成一个新的Excel表格,并向里面写入数据的顺序也是 工作簿 -- 工作表 -- 单元格。但是写入数据的顺序是反过来的,先向单元格中添加数据,然后添加到工作表上,最后写入工作簿中。这里需要注意的是,以 WritableWorkbook wb = Workbook.createWorkbook(file); 方式创建工作簿是创建了一个新的工作簿,如果file文件原来存在,那么它的数据将会失去,下一个例子将会讲解怎样更新一个Excel表格。 还有就是在工作簿关闭之前要把数据写入工作簿中,也就是 wb.write();。
更新Excel表格数据:
- import java.io.File;
- import java.io.IOException;
- import jxl.Workbook;
- import jxl.read.biff.BiffException;
- import jxl.write.Label;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
- import jxl.write.WriteException;
-
-
-
-
-
- public class TUpdate {
-
- public static void update (WritableWorkbook wb) throws WriteException {
- WritableSheet sheet = wb.createSheet("第二页", 1);
- sheet.addCell(new Label(0,0,"第二页更新数据"));
-
- }
-
- public static void main(String [] args) throws IOException, BiffException, WriteException {
- try {
-
- File file = new File("D://JEtest//update.xls");
-
- Workbook book = Workbook.getWorkbook(file);
-
- WritableWorkbook wb = Workbook.createWorkbook(file, book);
- TUpdate.update(wb);
- wb.write();
- wb.close();
- book.close();
- }catch (IOException | BiffException | WriteException e) {
- System.out.println("Exception: " + e);
- throw e;
- }
- }
- }
0 0