JAVA 开源项目JXL之EXCEL表格操作的实用工具

 

package com.file;

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;

//操作Excel文件的类
public class OperatorExcel {
	public static String readExcel(File file) {// 读取Excel文件的内容
		StringBuffer sb = new StringBuffer();
		Workbook wb = null;
		try {
			wb = Workbook.getWorkbook(file);// 构造Workbook（工作薄）对象
		} catch (Exception e) {// 捕获异常
			e.printStackTrace();
		}
		if (wb == null)// 判断（工作薄）对象是否为空
			return null;
		Sheet[] sheet = wb.getSheets();// 创建Sheet（工作表）对象
		if (sheet != null && sheet.length > 0) {// 工作表对象不为空
			for (int i = 0; i < sheet.length; i++) {// 对每个工作表进行循环
				int rowNum = sheet[i].getRows();// 得到当前工作表的行数
				for (int j = 0; j < rowNum; j++) {
					Cell[] cells = sheet[i].getRow(j);// 得到当前行的所有单元格
					if (cells != null && cells.length > 0) {
						for (int k = 0; k < cells.length; k++) {// 对每个单元格进行循环
							String cellValue = cells[k].getContents();// 读取当前单元格的值
							sb.append(cellValue + "\t");
						}
					}
					sb.append("\r\n");
				}
				sb.append("\r\n");
			}
		}
		wb.close();// 最后关闭资源，释放内存
		return sb.toString();
	}

	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 writeExcel(String fileName) {// 生成一个Excel文件
		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 + "成功!");
	}

	public static boolean searchKeyWord(File file, String keyWord) {// 搜索某一个文件中是否包含某个关键字
		boolean res = false;
		Workbook wb = null;
		try {
			wb = Workbook.getWorkbook(file);// 构造Workbook（工作薄）对象
		} catch (Exception e) {// 捕获异常
			return res;
		}
		if (wb == null)
			return res;
		Sheet[] sheet = wb.getSheets();// 创建Sheet（工作表）对象
		boolean breakSheet = false;
		if (sheet != null && sheet.length > 0) {
			for (int i = 0; i < sheet.length; i++) {// 对每个工作表进行循环
				if (breakSheet)
					break;
				int rowNum = sheet[i].getRows();// 得到当前工作表的行数

				boolean breakRow = false;

				for (int j = 0; j < rowNum; j++) {// 循环获得行信息
					if (breakRow)
						break;
					Cell[] cells = sheet[i].getRow(j);// 得到当前行的所有单元格
					if (cells != null && cells.length > 0) {
						boolean breakCell = false;
						for (int k = 0; k < cells.length; k++) {// 对每个单元格进行循环
							if (breakCell)
								break;
							String cellValue = cells[k].getContents();// 读取当前单元格的值
							if (cellValue == null)
								continue;
							if (cellValue.contains(keyWord)) {// 判断是否包含关键字
								res = true;
								breakCell = true;
								breakRow = true;
								breakSheet = true;
							}
						}
					}
				}
			}
		}
		wb.close();// 关闭资源，释放内存
		return res;
	}

	public static void updateExcel(String fileName) {// 修改Excel文件
		try {
			Workbook wb = Workbook.getWorkbook(new File(fileName));// 构造Workbook（工作薄）对象
			// 打开一个文件的副本，并且指定数据写回到原文件
			WritableWorkbook book = Workbook.createWorkbook(new File(fileName),
					wb);
			WritableSheet sheet = book.createSheet(" 第二页 ", 1);// 添加一个工作表
			sheet.addCell(new Label(0, 0, " 第二页的测试数据 "));// 添加一个单元格
			book.write();// 副本对象关闭
			book.close();// 工作薄对象关闭
		} catch (Exception e) {// 捕获异常
			System.out.println(e);
		}
		System.out.println("修改Excel文件" + 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文件");
			System.out.println(readExcel(new File(fileName)));// 读取Excel方法
			System.out.println("3.搜索关键字--'标题'");
			System.out.println("找到关键字?"	+ searchKeyWord(new File(fileName), "标题"));// 调用搜索关键字方法
			System.out.println("4.修改Excel文件");
			updateExcel(fileName);// 调用修改Excel文件
			System.out.println("5.读取Excel文件的行和列");
			readExcelInfo(fileName);
		} catch (Exception e) {// 捕获异常
			e.printStackTrace();
		}
	}
}