Java对Excel表格的操作

import java.io.File;//引入类import java.io.IOException;import java.util.Scanner;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;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;

public class TextOperatorExcel {// 操作Excel文件的类

/** * 生成一个Excel文件 */public static void writeExcel(String fileName) {WritableWorkbook wwb = null;try {//创建一个可写入的工作薄(Workbook)对象wwb = Workbook.createWorkbook(new File(fileName));} catch (IOException e) {// 捕获流异常e.PRintStackTrace();}if (wwb != null) {// 创建一个可写入的工作表// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置WritableSheet ws = wwb.createSheet("sheet1", 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) + "列");try {ws.addCell(labelC);// 将生成的单元格添加到工作表中} catch (Exception e) {// 捕获异常e.printStackTrace();}

}}try {wwb.write();// 从内存中写入文件中wwb.close();// 从内存中写入文件中} catch (Exception e) {// 捕获异常e.printStackTrace();}}System.out.println("生成一个Excel文件："+fileName+"成功!");}/** * 将内容写入 * @param fileName * @throws Exception */public static void writeContentToExcel(String fileName) throws Exception{File tempFile=new File(fileName);WritableWorkbook workbook = Workbook.createWorkbook(tempFile);WritableSheet sheet = workbook.createSheet("TestCreateExcel", 0); //一些临时变量，用于写到excel中Label l=null;jxl.write.Number n=null;jxl.write.DateTime d=null;

//预定义的一些字体和格式，同一个Excel中最好不要有太多格式 字形 大小 加粗 倾斜 下划线 颜色WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLUE); WritableCellFormat headerFormat = new WritableCellFormat (headerFont);

WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED); WritableCellFormat titleFormat = new WritableCellFormat (titleFont);

WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat detFormat = new WritableCellFormat (detFont);

NumberFormat nf=new NumberFormat("0.00000"); //用于Number的格式WritableCellFormat priceFormat = new WritableCellFormat (detFont, nf);

DateFormat df=new DateFormat("yyyy-MM-dd");//用于日期的WritableCellFormat dateFormat = new WritableCellFormat (detFont, df);

l=new Label(0, 0, "用于测试的Excel文件", headerFormat);//创建一些单元格，再加到sheet中sheet.addCell(l);//添加标题int column=0;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, "标题 "+i, detFormat);sheet.addCell(l);d=new DateTime(column++, i+3, new java.util.Date(), dateFormat);sheet.addCell(d);l=new Label(column++, i+3, "CNY", detFormat);sheet.addCell(l);n=new jxl.write.Number(column++, i+3, 5.678, priceFormat);sheet.addCell(n);i++;column=0;l=new Label(column++, i+3, "标题 "+i, detFormat);sheet.addCell(l);d=new DateTime(column++, i+3, new java.util.Date(), dateFormat);sheet.addCell(d);l=new Label(column++, i+3, "SGD", detFormat);sheet.addCell(l);n=new jxl.write.Number(column++, i+3, 98832, 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+"成功");}

public static void readExcelInfo(String fileName) throws Exception {// 获得Excel文件多少行多少列Workbook book = Workbook.getWorkbook(new File(fileName));// 构造Workbook（工作薄）对象Sheet sheet = book.getSheet(0);// 得到第一列第一行的单元格// 获得第一个工作表对象int columnum = sheet.getColumns(); // 得到列数int rownum = sheet.getRows(); // 得到行数System.out.println(columnum);System.out.println(rownum);for (int i = 0; i < rownum; i++) // 循环进行读写{for (int j = 0; j < columnum; j++) {Cell cell1 = sheet.getCell(j, i);String result = cell1.getContents();System.out.print(result);System.out.print(" /t ");}System.out.println();}book.close();//关闭（工作薄）对象}public static void main(String[] args) {// java程序主入口处try {System.out.println("1.创建Excel文件，输入Excel文件名称（包括路径和后缀）");Scanner scan = new Scanner(System.in);final String fileName = scan.next();//获得键盘值writeExcel(fileName);//调用生成Excel方法System.out.println("2.将内容写入Excel文件");writeContentToExcel(fileName);//调用将内容写入Excel方法System.out.println("3.读取Excel文件");readExcelInfo(fileName);} catch (Exception e) {//捕获异常e.printStackTrace();}}}