JXL 和 POI 操作Excel 表格

今天用到　Excel 中第一次见到 POI 说实话,自己的见识还是太少了.多多学习呀.晚上回来总结了一下.下面分别是它们的写入,输出,修改.

 

 

 

package com.isw2.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

public class ExcelDemo {
	private String path = "C:/test.xls";

	public static void main(String[] args) {
		List list = new ArrayList();
		for (int i = 0; i < 10; i++) {
			int[] numArr = new int[] { 12, 45, 36, 78, 46 };
			list.add(numArr);
		}
		ExcelDemo excel = new ExcelDemo();
		excel.createExcelPOI(list);
		excel.updateExclePOI();
	}

	/**
	 * 使用 POI 更新 Excel
	 */
	public void updateExclePOI() {
		File file = new File(path);
		try {
			InputStream inPutStream = new FileInputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook(inPutStream);
			HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFRow row = sheet.getRow(7);
			row.getCell(0).setCellValue("google");
			
			inPutStream.close();
			OutputStream outPutStream = new FileOutputStream(file);
			workbook.write(outPutStream);
			outPutStream.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 使用 POI 读取 Excel
	 */
	public void readExclePOI() {
		File file = new File(path);

		try {
			InputStream inputStream = new FileInputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			HSSFSheet sheet = workbook.getSheetAt(0);
			HSSFRow row = sheet.getRow(0);
			inputStream.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 使用 POI 写入 Excel
	 * 
	 * @param list
	 */
	public void createExcelPOI(List list) {
		try {
			File file = new File(path);
			OutputStream outPutStream = new FileOutputStream(file);
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("工作表1");
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = row.createCell((short) 0);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new Date());
			// 设置时间格式
			HSSFCellStyle DateStyle = workbook.createCellStyle();
			DateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
			cell.setCellStyle(DateStyle);
			// 合并单元格
			sheet.addMergedRegion(new Region(0, (short) 0, 4, (short) 4));
			// 设置字体
			HSSFFont font = workbook.createFont();
			font.setFontName("宋体");
			// 设置斜体
			font.setItalic(true);
			// 粗体
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			HSSFCellStyle style = workbook.createCellStyle();
			style.setFont(font);
			style.setWrapText(true);
			for (int i = 0; i < list.size(); i++) {
				HSSFRow rowTemp = sheet.createRow(i + 5);
				int[] number = (int[]) list.get(i);
				for (int j = 0; j < number.length; j++) {
					HSSFCell cellTemp = rowTemp.createCell((short) j);
					cellTemp.setCellValue(number[j]);
					cellTemp.setCellStyle(style);
				}
			}
			workbook.write(outPutStream);
			outPutStream.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * JXL 更新 Excel 文件
	 */
	public void updateExcelJXL(){
		File file = new File(path);
		try {
			Workbook workbook = Workbook.getWorkbook(file);
			//这里是与写入的区别,仅此
			WritableWorkbook wwb = workbook.createWorkbook(file,workbook);
			WritableSheet sheet = wwb.getSheet(0);
			Label lable = new Label(0,7,"google");
			sheet.addCell(lable);
			wwb.write();
			wwb.close();
			workbook.close();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}

	/**
	 * JXL 读取 Excel 文件
	 */
	public void readExcelJXL() {
		File file = new File(path);
		try {
			Workbook workbook = Workbook.getWorkbook(file);
			Sheet sheet = workbook.getSheet(0);
			// 读取 时间
			DateCell dateCell = (DateCell) sheet.getCell(0, 0);
			System.out.println(dateCell.getDate());
			for (int i = 5; i < 15; i++) {
				for (int j = 0; j < 5; j++) {
					String str = sheet.getCell(j, i).getContents();
					System.out.print(str + "\t");
				}
				System.out.println();
			}
			workbook.close();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 用 JXL 写入 Excel 文件
	 * 
	 * @param list
	 */
	public void createExcelJXL(List list) {
		File file = new File(path);
		try {
			OutputStream outPutStream = new FileOutputStream(file);
			WritableWorkbook wwb = Workbook.createWorkbook(outPutStream);
			// 设置表空间
			WritableSheet ws = wwb.createSheet("工作表1", 0);
			// 合并单元格
			ws.mergeCells(0, 0, 4, 4);
			// 时间写入
			DateTime dateTime = new DateTime(0, 0, new Date());
			ws.addCell(dateTime);
			// 设置字体
			WritableFont wfc = new WritableFont(WritableFont.createFont("宋体"),
					10, WritableFont.NO_BOLD, true);
			WritableCellFormat wcfFC = new WritableCellFormat(wfc);
			// 内容自动适应
			wcfFC.setWrap(true);
			for (int i = 0; i < list.size(); i++) {
				int[] number = (int[]) list.get(i);
				for (int j = 0; j < number.length; j++) {
					Label lableTemp = new Label(j, i + 5, String
							.valueOf(number[j]), wcfFC);
					ws.addCell(lableTemp);
				}
			}
			wwb.write();
			wwb.close();
			outPutStream.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}
	}
}

只是简单的操作了些.做了些指引,以后有机会深入学习一些.

 

 这里把代码和 Jar 包放上来,和大家分享.好久没这么学习过了.久违的感觉.

 

 

参考了以下的文章:

http://www.iteye.com/topic/322837

http://sjhorse.iteye.com/blog/156905

给了很大的帮助.