今天要用jxl.jar包实现数据excel生成,excel数据导入跟excel数据导出的功能。
不多说,上代码:O(∩_∩)O~
1.导入类:(用到的jxl包可以在这里免费下载:http://download.csdn.net/detail/wws199304/8175807)
[java]
view plain
copy
- import java.io.File;
- import java.util.Date;
- import java.util.Scanner;
- import jxl.Cell;
- import jxl.Sheet;
- import jxl.Workbook;
- import jxl.format.Colour;
- import jxl.format.UnderlineStyle;
- import jxl.write.DateFormat;
- import jxl.write.DateTime;
- import jxl.write.Label;
- import jxl.write.NumberFormat;
- import jxl.write.WritableCellFormat;
- import jxl.write.WritableFont;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
2.创建excel表
[java]
view plain
copy
- public static void writeExcel(String fileName){
- WritableWorkbook wwb = null;
- try {
- // 创建一个可写入的工作簿(WorkBook)对象,
- //这里用父类方法createWorkbook创建子类WritableWorkbook让我想起了工厂方法
- wwb = Workbook.createWorkbook(new File(fileName));
- // 创建一个可写入的工作表
- // Workbook的createSheet方法有两个参数,第一个是工作表的名称,第二个是工作表在工作簿中的位置
- WritableSheet ws = wwb.createSheet("sheetTest", 0);
- for(int i=0;i<10;i++){
- for(int j=0;j<5;j++){
- Label labelC = new Label(j,i,"第"+(i+1)+"行,第"+(j+1)+"列");
- ws.addCell(labelC);
- }
- }
- wwb.write();// 从内从中写入文件中
- wwb.close();
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- System.out.println("生成第一个Excel文件"+fileName+"成功");
- }
3.修改excel:
[java]
view plain
copy
- public static void writeExcel(String fileName){
- WritableWorkbook wwb = null;
- try {
- // 创建一个可写入的工作簿(WorkBook)对象,
- //这里用父类方法createWorkbook创建子类WritableWorkbook让我想起了工厂方法
- wwb = Workbook.createWorkbook(new File(fileName));
- // 创建一个可写入的工作表
- // Workbook的createSheet方法有两个参数,第一个是工作表的名称,第二个是工作表在工作簿中的位置
- WritableSheet ws = wwb.createSheet("sheetTest", 0);
- for(int i=0;i<10;i++){
- for(int j=0;j<5;j++){
- Label labelC = new Label(j,i,"第"+(i+1)+"行,第"+(j+1)+"列");
- ws.addCell(labelC);
- }
- }
- wwb.write();// 从内从中写入文件中
- wwb.close();
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- System.out.println("生成第一个Excel文件"+fileName+"成功");
- }
- /**
- * excel文件的修改
- * @param fileName 文件路径+文件名+文件后缀
- */
- public static void writeConentToExcel(String fileName) throws Exception{
- jxl.write.Number n = null;
- jxl.write.DateTime d = null;
- File tempFile = new File(fileName);
- WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
- WritableSheet sheet = workbook.createSheet("TestCreateExcel",0);
- // 预定义的一些字体和格式, 字形、大小、加粗、倾斜、下划线、颜色
- // 头文件
- WritableFont headerFont = new WritableFont(WritableFont.ARIAL,15,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.GREEN);
- WritableCellFormat headerFormat = new WritableCellFormat(headerFont);
- // 标题
- WritableFont titleFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
- WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
- // 内容
- WritableFont detFont = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
- WritableCellFormat detFormat = new WritableCellFormat(detFont);
- // number 格式
- NumberFormat nf=new NumberFormat("0.000");
- WritableCellFormat priceFormat = new WritableCellFormat(nf);
- // 日期
- DateFormat df = new DateFormat("yyyy-MM-dd");
- WritableCellFormat dateFormat = new WritableCellFormat(df);
- // 创建单元格
- Label l = new Label(0,0,"文件的头信息",headerFormat);
- sheet.addCell(l);
- // 添加标题
- int column=0; // 列
- //从列0开始循环,每次输出一个标题后都column++,2代表(2+1)行,titleFormat是指定格式
- l = new Label(column++,2,"姓名",titleFormat);
- sheet.addCell(l);
- l = new Label(column++,2,"日期",titleFormat);
- sheet.addCell(l);
- l = new Label(column++,2,"货币单位",titleFormat);
- sheet.addCell(l);
- l = new Label(column++,2,"薪水",titleFormat);
- sheet.addCell(l);
- // 添加内容
- int i =0; // 行
- column = 0; // 列
- l = new Label(column++,i+3,"Golden",detFormat);
- sheet.addCell(l);
- d = new DateTime(column++,i+3,new Date(),dateFormat);
- sheet.addCell(d);
- l = new Label(column++,i+3,"¥",detFormat);
- sheet.addCell(l);
- n = new jxl.write.Number(column++,i+3,12000,priceFormat);
- sheet.addCell(n);
- i++;
- column = 0; // 列
- l = new Label(column++,i+3,"路上",detFormat);
- sheet.addCell(l);
- d = new DateTime(column++,i+3,new Date(),dateFormat);
- sheet.addCell(d);
- l = new Label(column++,i+3,"¥",detFormat);
- sheet.addCell(l);
- n = new jxl.write.Number(column++,i+3,15000,priceFormat);
- sheet.addCell(n);
- i++;
- column = 0; // 列
- l = new Label(column++,i+3,"Mr.Sandman",detFormat);
- sheet.addCell(l);
- d = new DateTime(column++,i+3,new Date(),dateFormat);
- sheet.addCell(d);
- l = new Label(column++,i+3,"¥",detFormat);
- sheet.addCell(l);
- n = new jxl.write.Number(column++,i+3,13000,priceFormat);
- sheet.addCell(n);
- // 分别设置各列的宽度
- column=0;
- sheet.setColumnView(column++, 20);
- sheet.setColumnView(column++, 20);
- //货币单位列比较窄
- sheet.setColumnView(column++, 10);
- sheet.setColumnView(column++, 20);
- workbook.write();
- workbook.close();
- System.out.println("内容写入"+fileName+"成功!");
(⊙o⊙)。。。怎么是错误呀,原来是原来运行过的测试路径没有改,测试路径下的excel文件打开着。关掉重来吧!
这次正常了:
4.读取excel:
[java]
view plain
copy
- Workbook book = Workbook.getWorkbook(new File(fileName)); // 构造Workbook(工作簿)对象
- Sheet sheet = book.getSheet(0);
- // 得到第一列第一行的单元格
- int columNum = sheet.getColumns(); // 得到所有列
- int rowNum = sheet.getRows(); // 得到所有行
- System.out.println("行:"+rowNum+" 列:"+columNum);
- for(int i=0;i<rowNum;i++){// 读取行
- for(int j=0;j<columNum;j++){ //读取列
- Cell cell = sheet.getCell(j, i);
- String result = cell.getContents(); // 得到单元格的值
- System.out.print(result);
- System.out.print("\t");
- }
- System.out.println();
- }
- book.close(); // 关闭对象