Java操作excel!!

首先需要下载jxl.jar包

然后附上代码

 

1 package test; 2 3 import java.io.FileInputStream; 4 import java.io.FileNotFoundException; 5 import java.io.IOException; 6 import java.io.InputStream; 7 import java.util.Date; 8 9 import jxl.Cell;10 import jxl.CellType;11 import jxl.DateCell;12 import jxl.LabelCell;13 import jxl.NumberCell;14 import jxl.Sheet;15 import jxl.Workbook;16 import jxl.read.biff.BiffException;17 18 public class Test2 {19 20 /**21 * @param args22 */23 public static void main(String[] args) {24 try {25 //构建workbook对象26 jxl.Workbook rwb = null;27 //从本地文件中读取文件28 InputStream is = new FileInputStream("D://test.xls");29 //从输入流中创建workbook30 rwb = Workbook.getWorkbook(is);31 //获取第一张Sheet表32 Sheet rs = rwb.getSheet(0);33 //获取第一行，第一列的值34 Cell c00 = rs.getCell(0, 0);35 String strc00 = c00.getContents();36 //获取第一行，第二列的值37 Cell c10 = rs.getCell(1, 0);38 String strc10 = c10.getContents();39 //获取第二行，第二列的值40 Cell c11 = rs.getCell(1, 1);41 String strc11 = c11.getContents();42 System.out.println("Cell(0, 0)" + " value : " + strc00 + "; type : " + c00.getType());43 System.out.println("Cell(1, 0)" + " value : " + strc10 + "; type : " + c10.getType());44 System.out.println("Cell(1, 1)" + " value : " + strc11 + "; type : " + c11.getType());45 String strc001 = null;46 double strc101 = 0.00;47 Date strc111 = null;48 Cell c001 = rs.getCell(0, 0);49 Cell c101 = rs.getCell(1, 0);50 Cell c111 = rs.getCell(1, 1);51 if(c001.getType() == CellType.LABEL)52 {53 LabelCell labelc00 = (LabelCell)c001;54 strc001 = labelc00.getString();55 }56 if(c101.getType() == CellType.NUMBER)57 {58 NumberCell numc10 = (NumberCell)c101;59 strc101 = numc10.getValue();60 }61 if(c111.getType() == CellType.DATE)62 {63 DateCell datec11 = (DateCell)c111;64 strc111 = datec11.getDate();65 }66 System.out.println("Cell(0, 0)" + " value : " + strc001 + "; type : " + c001.getType());67 System.out.println("Cell(1, 0)" + " value : " + strc101 + "; type : " + c101.getType());68 System.out.println("Cell(1, 1)" + " value : " + strc111 + "; type : " + c111.getType());69 rwb.close();70 71 } catch (FileNotFoundException e) {72 // TODO Auto-generated catch block73 e.printStackTrace();74 } catch (BiffException e) {75 // TODO Auto-generated catch block76 e.printStackTrace();77 } catch (IndexOutOfBoundsException e) {78 // TODO Auto-generated catch block79 e.printStackTrace();80 } catch (IOException e) {81 // TODO Auto-generated catch block82 e.printStackTrace();83 }84 85 }86 87 }88

 

 

package test;import java.io.File;import java.io.IOException;import jxl.Workbook;import jxl.format.UnderlineStyle;import jxl.write.WritableFont;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;public class Test3 { /** * @param args */ public static void main(String[] args) { try { jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File( "C://test.xls")); jxl.write.WritableSheet ws = wwb.createSheet("Test Sheet 1", 0); //1.添加Label对象 jxl.write.Label labelC = new jxl.write.Label(0, 0, "This is a Label cell"); ws.addCell(labelC); //添加带有字型Formatting的对象 jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18, WritableFont.BOLD, true); jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf); jxl.write.Label labelCF = new jxl.write.Label(1, 0, "This is a Label Cell", wcfF); ws.addCell(labelCF); //添加带有字体颜色Formatting的对象 jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED); jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc); jxl.write.Label labelCFC = new jxl.write.Label(2, 0, "This is a Label Cell1W", wcfFC); ws.addCell(labelCFC); //2.添加Number对象 jxl.write.Number labelN = new jxl.write.Number(0, 1, 3.1415926); ws.addCell(labelN); //添加带有formatting的Number对象 jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##"); jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); jxl.write.Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN); ws.addCell(labelNF); //3.添加Boolean对象 jxl.write.Boolean labelB = new jxl.write.Boolean(0, 2, false); ws.addCell(labelB); //4.添加DateTime对象 jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3, new java.util.Date()); ws.addCell(labelDT); //添加带有formatting的DateFormat对象 jxl.write.DateFormat df = new jxl.write.DateFormat("dd MM yyyy hh:mm:ss"); jxl.write.WritableCellFormat wcfDF = new jxl.write.WritableCellFormat(df); jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 3, new java.util.Date(), wcfDF); ws.addCell(labelDTF); //写入Exel工作表 wwb.write(); //关闭Excel工作薄对象 wwb.close(); System.out.println("添加excel成功"); } catch (RowsExceededException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }}  

 

 

1 package test; 2 3 import java.io.FileInputStream; 4 import java.io.FileNotFoundException; 5 import java.io.IOException; 6 import java.io.InputStream; 7 8 import jxl.Cell; 9 import jxl.Sheet;10 import jxl.Workbook;11 import jxl.read.biff.BiffException;12 13 public class Test1 {14 public static void main(String[] args) {15 try {16 //构建workbook对象17 jxl.Workbook rwb = null;18 //从本地文件中读取文件19 InputStream is = new FileInputStream("D://test.xls");20 //从输入流中创建workbook21 rwb = Workbook.getWorkbook(is);22 //获取第一张Sheet表23 Sheet rs = rwb.getSheet(0);24 //获取表的总列数和总行数25 int rsColumns = rs.getColumns();26 int rsRows = rs.getRows();27 //获取指定的单元格的对象引用28 for (int i = 0; i < rsRows; i++) {29 for (int j = 0; j < rsColumns; j++) {30 Cell cell = rs.getCell(j, i);31 System.out.print(cell.getContents() + " ");32 }33 System.out.println();34 }35 rwb.close();36 37 } catch (FileNotFoundException e) {38 // TODO Auto-generated catch block39 e.printStackTrace();40 } catch (BiffException e) {41 // TODO Auto-generated catch block42 e.printStackTrace();43 } catch (IndexOutOfBoundsException e) {44 // TODO Auto-generated catch block45 e.printStackTrace();46 } catch (IOException e) {47 // TODO Auto-generated catch block48 e.printStackTrace();49 }50 51 }52 53 }54

 

 

package test;import java.io.File;import java.io.IOException;import jxl.CellType;import jxl.Workbook;import jxl.read.biff.BiffException;import jxl.write.Label;import jxl.write.WriteException;public class Test4 { /** * @param args */ public static void main(String[] args) { try { // 创建只读的Excel工作薄的对象 jxl.Workbook rw = jxl.Workbook .getWorkbook(new File("D://test.xls")); // 创建可写入的Excel工作薄对象 jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File( "C://22.xls"), rw); // 读取第一张工作表 jxl.write.WritableSheet ws = wwb.getSheet(0); // 获得第一个单元格对象 jxl.write.WritableCell wc = ws.getWritableCell(0, 0); // 判断单元格的类型, 做出相应的转化 if (wc.getType() == CellType.LABEL) { Label l = (Label) wc; l.setString("The value has been modified."); } // 写入Excel对象 wwb.write(); // 关闭可写入的Excel对象 wwb.close(); // 关闭只读的Excel对象 rw.close(); System.out.println("复制成功"); } catch (BiffException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IndexOutOfBoundsException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }}

 

就这样操作excel的基本应用就有了。非常希望和各位交流。