xlsutil.java

使用WEBWORK,SPRING,HIBERNATE编写的简单的添加

//Copyright: Copyright (c) 2006 by 徐建协
//
package com.common.util;

import java.io.*;
import java.util.*;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/*******************************************************************************
 * <p>
 * File Name: XlsUtil.java
 * </p>
 * <p>
 * Title: excel的操作的公用方法
 * </p>
 * <p>
 * Description: 从excel文件流获取单元格数据
 * </p>
 * <p>
 * Company:
 * </p>
 * 
 * @author 徐建协
 * @version 1.0 2006/07/06
 ******************************************************************************/
public class XlsUtil {

	/**
	 * 从EXCEL文件中获取数据
	 * 
	 * @param path
	 *            String 文件的路径
	 * @return 二维数组
	 */
	public static String[][] GetArrays(String path) {
		try {
			InputStream is = new FileInputStream(path);
			Workbook wb = Workbook.getWorkbook(is);// 从输入流创建Workbook
			Sheet rs = wb.getSheet(0);// 获取第一张Sheet表
			String[][] tmp = new String[rs.getRows()][rs.getColumns()];
			for (int i = 0; i < rs.getRows(); i++) {
				for (int j = 0; j < rs.getColumns(); j++) {
					tmp[i][j] = rs.getCell(j, i).getContents().trim();
				}
			}
			rs = null;
			wb.close();
			is.close();
			wb = null;
			is = null;
			return tmp;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 从EXCEL文件中获取数据
	 * 
	 * @param file
	 *            File 文件
	 * @return 二维数组
	 */
	public static String[][] GetArrays(File file) {
		try {
			InputStream is = new FileInputStream(file);
			Workbook wb = Workbook.getWorkbook(is);// 从输入流创建Workbook
			Sheet rs = wb.getSheet(0);// 获取第一张Sheet表
			String[][] tmp = new String[rs.getRows()][rs.getColumns()];
			for (int i = 0; i < rs.getRows(); i++) {
				for (int j = 0; j < rs.getColumns(); j++) {
					tmp[i][j] = rs.getCell(j, i).getContents().trim();
				}
			}
			rs = null;
			wb.close();
			is.close();
			wb = null;
			is = null;
			return tmp;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 从EXCEL文件中获取数据
	 * 
	 * @param is
	 *            InputStream 输入流
	 * @return 二维数组
	 */
	public static String[][] GetArrays(InputStream is) {
		try {
			Workbook wb = Workbook.getWorkbook(is);// 从输入流创建Workbook
			Sheet rs = wb.getSheet(0);// 获取第一张Sheet表
			String[][] tmp = new String[rs.getRows()][rs.getColumns()];
			for (int i = 0; i < rs.getRows(); i++) {
				for (int j = 0; j < rs.getColumns(); j++) {
					tmp[i][j] = rs.getCell(j, i).getContents().trim();
				}
			}
			rs = null;
			wb.close();
			is.close();
			wb = null;
			is = null;
			return tmp;
		} catch (Exception e) {
			System.out.println(e.toString());
			return null;
		}
	}

	/***************************************************************************
	 * 从EXCEL字节数组中获取数据
	 * 
	 * @param data
	 *            byte[]
	 * @return String[][]
	 */
	public static String[][] GetArrays(byte[] data) {
		try {
			InputStream is = new ByteArrayInputStream(data);
			Workbook wb = Workbook.getWorkbook(is);// 从输入流创建Workbook
			Sheet rs = wb.getSheet(0);// 获取第一张Sheet表
			String[][] tmp = new String[rs.getRows()][rs.getColumns()];
			for (int i = 0; i < rs.getRows(); i++) {
				for (int j = 0; j < rs.getColumns(); j++) {
					tmp[i][j] = rs.getCell(j, i).getContents().trim();
				}
			}
			rs = null;
			wb.close();
			is.close();
			data = null;
			wb = null;
			is = null;
			return tmp;
		} catch (Exception e) {
			System.out.println(e.toString());
			return null;
		}
	}

	/**
	 * 往输出流中写入取数据
	 * 
	 * @param os
	 *            OutputStream 输入流
	 * @param xt
	 *            XlsTable
	 * @return 二维数组
	 */
	public static void WriteXls(OutputStream os, XlsTable xt) {
		try {
			WritableWorkbook wwb = Workbook.createWorkbook(os);
			WritableSheet ws = wwb.createSheet("表一", 0);// 创建第一个工作表
			// 创建标题字体及颜色
			WritableFont wfTitle = new WritableFont(WritableFont.ARIAL,
					WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE, Colour.RED);
			WritableCellFormat wcfTitle = new WritableCellFormat(wfTitle);
			// 创建数据框的字体及颜色
			WritableFont wfData = new WritableFont(WritableFont.ARIAL,
					WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD,
					false, UnderlineStyle.NO_UNDERLINE, Colour.GREEN);
			WritableCellFormat wcfData = new WritableCellFormat(wfData);

			// 添加标题数据
			for (int i = 0; i < xt.titleList.size(); i++) {
				Label lable = new Label(i, 0, (String) xt.titleList.get(i),
						wcfTitle);
				ws.addCell(lable);
			}
			// 添加表数据
			for (int i = 0; i < xt.conentList.size(); i++) {
				String[] rowData = (String[]) xt.conentList.get(i);
				for (int j = 0; j < rowData.length; j++) {
					Label lable = new Label(j, i + 1, rowData[j], wcfData);
					ws.addCell(lable);
				}
			}
			wwb.write();
			wwb.close();
			ws = null;
			wwb = null;

		} catch (Exception e) {
			System.out.println(e.toString());
		}
	}

	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		String[][] data = XlsUtil.GetArrays("11.xls");
		for (int i = 0; i < data.length; i++) {
			System.out.print("第" + (i + 1) + "行 ");
			for (int j = 0; j < data[0].length; j++) {
				System.out.print(data[i][j] + ",");
			}
			System.out.println("");
		}

	}

}