jxl处理报表

经过这两天的学习，对jxl处理报表做一下总结

1、创建excel报表

//实现对excel的操作
import java.io.File;
import java.io.IOException;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class 操作EXCEL {

	/**
	 * @param args
	 * @throws IOException 
	 * @throws WriteException 
	 * @throws RowsExceededException 
	 */
	public static void main(String[] args) throws IOException, RowsExceededException, WriteException {
		
		//1.在内存中创建EXCEL文档
		  File file = new File("d:\\联系1.xls");
		  WritableWorkbook excel = Workbook.createWorkbook(file);
		//2.在当前EXCEL文档中创建Sheet页
		  WritableSheet sheet = excel.createSheet("开发一部",0);
		//3.在当前sheet页创建一个单元格
		//sheet.mergeCells(arg0, arg1, arg2, arg3)合并单元格用法
		  Label label = new Label(0,0,"姓名");
		  Label label1= new Label(1,0,"电话");
		  Label label2 = new Label(2,0,"地址");
		  sheet.addCell(label);
		  sheet.addCell(label1);
		  sheet.addCell(label2);
		//4.在硬盘上创建EXCEL
          excel.write();
          excel.close();
	}

}

2、通过人机交互，实现数据的录入

//学生成绩录入
import java.io.File;
import java.io.IOException;
import java.util.Scanner;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class sys_in {
	public static void main(String[] args) throws IOException, RowsExceededException, WriteException {
		//1、开辟一块读取信息的内存
		Scanner sc = new Scanner(System.in);
		//2、在内存中创建一个excel
		File file = new File("d:\\成绩表.xls");
		WritableWorkbook excel = Workbook.createWorkbook(file);
		//3、创建一个sheet
		WritableSheet sheet = excel.createSheet("成绩一栏", 0);
		//4、读取信息
		System.out.println("请输入您的姓名");
		String name = sc.next();
		System.out.println("请输入您的数学成绩");
		String math = sc.next();
		System.out.println("请输入您的语文成绩");
		String chinese = sc.next();
		System.out.println("请输入您的英语成绩");
		String english = sc.next();		
		//5、生成单元格
		sheet.mergeCells(0, 0, 0, 2);//合并单元格
		Label nameLabel = new Label(0,0,name);
		
		Label mathLabel = new Label(1,0,"数学");
		Label chineseLabel = new Label(1,1,"语文");
		Label englishLabel = new Label(1,2,"英语");
		
		Label mathScore = new Label(2,0,math);
		Label chineseScore = new Label(2,1,chinese);
		Label englishScore = new Label(2,2,english);
		//添加到单元格中
		sheet.addCell(nameLabel);
		sheet.addCell(mathLabel);
		sheet.addCell(chineseLabel);
		sheet.addCell(englishLabel);
		sheet.addCell(mathScore);
		sheet.addCell(chineseScore);
		sheet.addCell(englishScore);
		//6、生成excel
		excel.write();
		excel.close();
	}
}

3、实现报表数据的读取

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {
	public static void main(String[] args) throws BiffException, IOException {
		File file = new File("d:\\dd.xls");
		//将硬盘的excel拖动到内存中
		Workbook excel= Workbook.getWorkbook(file);
		//获得当前excel第一个sheet页
		Sheet sheet = excel.getSheet(0);
		//获得总行数
		int rows = sheet.getRows();
		//获得总列数
		int columns = sheet.getColumns();
		//循环遍历获得数据
		for(int row=0;row<rows;row++){
			for(int col=0;col<columns;col++)
			{
				//得到单元格
				Cell cell = sheet.getCell(col,row);
				//从单元格中获得数据
				String content = cell.getContents();
				System.out.print(content+" ");
			}
			System.out.println();
		}
	}
}

4、实现对报表的更新

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;
import jxl.write.biff.RowsExceededException;

//对原有excel做一个副本，更改之后覆盖到原有excel中
public class UpdateExcel {
	public static void main(String[] args) throws BiffException, IOException, RowsExceededException, WriteException {
		File file = new File("d:\\dd.xls");
		Workbook wb = Workbook.getWorkbook(file);
		//创建一个副本
		WritableWorkbook copyExcel = Workbook.createWorkbook(file,wb);
		//获得一个sheet
		WritableSheet sheet = copyExcel.getSheet(0);
		//获得行数
		int rows = sheet.getRows();
		//添加一行信息
		Label name = new Label(0,rows,"小周");
		Label s1 = new Label(1,rows,"100");
		Label s2 = new Label(2,rows,"99");
		Label s3 = new Label(3,rows,"98");
		sheet.addCell(name);
		sheet.addCell(s1);
		sheet.addCell(s2);
		sheet.addCell(s3);
		
		copyExcel.write();
		copyExcel.close();
	}
}

通过上述代码，从而了解jxl的基本使用方法