jxl操作excel读取、导入导出数据库

jxl架包轻小，应用起来简单实用，是解决操作excel问题很好的方案。下面介绍一个操作excel实用工具类以及实例。

 

 工具类:

package ash_ljv2.framework.util;

import java.io.*;
import java.util.Date;
import java.util.UUID;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.Boolean;

public class Excel{
	//文件路径
	private String path;
	private String tableName ;
	private String[] tableCols;
	
	//工作薄集合
	private Workbook workbook;
	
	public Excel(String path,String tableName,String[] tableCols) throws BiffException,IOException{
		this.tableName = tableName;
		this.tableCols = tableCols;
		this.setPath(path);	
		this.setWorkbook(Workbook.getWorkbook(new java.io.File(path)));
	}
	
	/**
	 * 获取工作薄数量
	 * @return 工作薄数量
	 */
	public int getNumberOfSheets(Workbook book){
		return book == null ? 0 :book.getNumberOfSheets();
	}
	
	/**
	 * 获取工作薄总行数
	 * @param sheet 工作薄
	 * @return 工作薄总行数
	 */
	public int getRows(Sheet sheet){
		return sheet == null ?  0 : sheet.getRows();
	}
	
	/**
	 * 获取最大列数
	 * @param sheet 工作薄
	 * @return 总行数最大列数
	 */
	public int getColumns(Sheet sheet){
		return sheet == null ?  0 : sheet.getColumns();
	}
	
	/**
	 * 获取每行单元格数组
	 * @param sheet 工作薄
	 * @param row 行数
	 * @return 每行单元格数组
	 */
	public Cell[] getRows(Sheet sheet,int row){
		return sheet == null || sheet.getRows() < row ? null : sheet.getRow(row);
	}
	
	/**
	 * 获取每行单元格数组
	 * @param sheet 工作薄
	 * @param endrow 结束行
	 * @param endCol 结束列
	 * @return 每行单元格数组
	 */
	public Cell[][] getCells(Sheet sheet,int endrow,int endcol){
		return getCells(sheet,0,endrow,0,endcol);
	}
	
	/**
	 * 获取每行单元格数组
	 * @param sheet 工作薄
	 * @param startrow 行数
	 * @param endrow 结束行
	 * @param startcol 开始列
	 * @param endCol 结束列
	 * @return 每行单元格数组
	 */
	public Cell[][] getCells(Sheet sheet,int startrow,int endrow,int startcol,int endcol)	{
		Cell[][] cellArray = new Cell[endrow-startrow][endcol-startcol];
		int maxRow = this.getRows(sheet);
		int maxCos = this.getColumns(sheet);
		for(int i = startrow ;i < endrow && i < maxRow ; i++){	
		
			for(int j = startcol ; j < endcol && j < maxCos ; j++ ){
			
				cellArray[i-startrow][j-startcol] = sheet.getCell(j, i);
			}
			
		}		
		return cellArray;
	}
	
	/**
	 * 得到行的值
	 * @param sheet
	 * @param col
	 * @param startrow
	 * @param endrow
	 * @return
	 */
	public Cell[] getColCells(Sheet sheet,int col,int startrow,int endrow){
		Cell[] cellArray = new Cell[endrow-startrow];
		int maxRow = this.getRows(sheet);
		int maxCos = this.getColumns(sheet);
		if(col <= 0 || col > maxCos || startrow > maxRow || endrow < startrow){
			return null;
		}
		if(startrow < 0){
			startrow = 0;
		}
		for(int i = startrow ;i < endrow && i < maxRow ; i++){
			cellArray[i-startrow] = sheet.getCell(col,i);
		}
		return cellArray;
	}
	
	/**
	 * 得到列的值
	 * @param sheet
	 * @param row
	 * @param startcol
	 * @param endcol
	 * @return
	 */
	public Cell[] getRowCells(Sheet sheet,int row,int startcol,int endcol){
		Cell[] cellArray = new Cell[endcol-startcol];
		int maxRow = this.getRows(sheet);
		int maxCos = this.getColumns(sheet);
		if(row <= 0 || row > maxRow || startcol > maxCos || endcol < startcol){
			return null;
		}
		if(startcol < 0){
			startcol = 0;
		}
		for(int i = startcol ;i < startcol && i < maxCos ; i++){
			cellArray[i-startcol] = sheet.getCell(i,row);
		}
		return cellArray;
	}
		
	/**
	 * 生成随机ID
	 * @return
	 */
	public static String getStrRandomId(){
		String uuid = UUID.randomUUID().toString().replace("-","");   
		return uuid; 
	}
	
	/**
	 * 组装SQL语句(扩展导入数据库额外增加字段的情况)
	 * @param sheet 工作薄
	 * @param startrow 开始行
	 * @param endrow 结束行
	 * @param startcol 开始列
	 * @param endcol 结束列
	 * @return SQL语句数组
	 */
	public Object[] constrctCellsSql(Sheet sheet,int startrow,int endrow,int startcol,int endcol,String payTime){
		Cell[][] cellArray = getCells(sheet, startrow, endrow,startcol,endcol);
		java.util.ArrayList<String> list = new java.util.ArrayList<String>();
		StringBuffer bf = new StringBuffer("INSERT INTO " + tableName+"(ID,");
		for(int i = 0 ; tableCols != null &&  i < tableCols.length ; i++){
			if(i != tableCols.length -1)
				bf.append(tableCols[i]).append(",");
			else
				bf.append(tableCols[i]).append("");
			
		}
		bf.append(",PAY_TIME) VALUES ");
		for(int i = 0;i< cellArray.length;i++){	
			//在第一列前加个随机数列
			StringBuffer sqlBuffer = new StringBuffer();	
			sqlBuffer.append(bf.toString()+"('"+getStrRandomId()+"',");	
			Cell[] cell = cellArray[i];
			if(tableCols != null && cell != null &&  tableCols.length != cell.length)
				continue;
			for(int j = 0 ; j < cell.length; j++){
				String tmp = "";
				if(cell[j] != null && cell[j].getContents() != null){
					tmp = (String)cell[j].getContents();
				}
				if(j != cell.length -1 )
					sqlBuffer.append("'").append(tmp).append("',");
				else
					sqlBuffer.append("'").append(tmp).append("'");				
			}
			//增加时间字段
			sqlBuffer.append(",").append("to_date('"+payTime+"','YYYY-MM-DD HH24:MI:SS')");
			sqlBuffer.append(")");
			list.add(sqlBuffer.toString());	
			System.out.println(sqlBuffer.toString());
		}
		System.out.println(list);
		return list.toArray();
	}
	

	/**
	 * 获取Excel文件路径
	 * @return Excel文件路径
	 */
	public String getPath(){
		return this.path;
	}
	
	/**
	 * 设置Excel文件路径
	 * @param path Excel文件路径
	 */
	public void setPath(String path){
		this.path = path;
	}
	/**
	 * 获取工作薄集合
	 */
	public Workbook getWorkbook(){
		return this.workbook;
	}
	
	/**
	 * 设置工作薄集合
	 * @param workbook 工作薄集合
	 */
	public void setWorkbook(Workbook workbook){
		this.workbook = workbook;
	}
	
	/**
	 * 
	 * @param args
	 */
	public static void main(String[] args){
		try {
			File fileWrite = new File("c:/testWrite.xls");
        			fileWrite.createNewFile();
	       		OutputStream os = new FileOutputStream(fileWrite);
	       		Excel.writeExcel(os);
       		} catch (IOException e) {
        			// TODO Auto-generated catch block
 			e.printStackTrace();
 		}
	}
}

 

读取类:

package cn.doc.service.impl;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletContext;

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

import com.opensymphony.xwork2.ActionContext;

import pojo.TblTableTemplate;
import ash_ljv2.framework.util.Excel;
import ash_ljv2.framework.util.PageBean;
import cn.doc.dao.TableTemplateDao;
import cn.doc.service.TableTemplateService;

public class TableTemplateServiceImpl implements TableTemplateService{
	private TableTemplateDao tableTemplateDao; 

	public TableTemplateDao getTableTemplateDao() {
		return tableTemplateDao;
	}

	public void setTableTemplateDao(TableTemplateDao tableTemplateDao) {
		this.tableTemplateDao = tableTemplateDao;
	}
	
	/**
	 * 读取excel
	 * @return
	 */
	public List importTableTemplate(String path){
		ArrayList list=new ArrayList();
		ServletContext request = (ServletContext) ActionContext.getContext()
		.get("com.opensymphony.xwork2.dispatcher.ServletContext");
		try {
			Excel excel = new Excel(request.getRealPath(path),null,null);
			Workbook workbook = excel.getWorkbook();
			Sheet sheet = workbook.getSheet(0);
			int a = excel.getRows(sheet);      //最大行数
			int m=excel.getColumns(sheet);  //最大列数
			Cell[][] c = excel.getCells(sheet,0,a,0,m);
			String f1 = null,f3=null;
			for(int i =0 ; i < c.length;i++){
				Cell[] obj = c[i];
				for(int j =0 ;j< obj.length; j++ ){
					 f1=obj[j].getContents().toString();
					list.add(f1);
				}
			}	
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
} 

 

导入数据库类:

Excel excel=null;
Workbook workbook=null;
Connection conn = session.connection();
ServletContext request = (ServletContext) ActionContext.getContext().get("com.opensymphony.xwork2.dispatcher.ServletContext");
try {
	excel = new Excel(request.getRealPath(path),"tbl_name",new String[]{"NAME","TYPE"}); //数组中为字段名
}catch(Exception e) {
	//e.printStackTrace();					
}
workbook = excel.getWorkbook();
Sheet sheet = workbook.getSheet(0);
Object[] obj=excel.constrctCellsSql(sheet,1,excel.getRows(sheet),0,excel.getColumns(sheet),payTime);  //payTime为工具类中额外加的字段
//这里做些业务逻辑判断......				
for(int i=0;i<obj.length;i++){
	Statement stmt;
	try {
		stmt = conn.createStatement();
		stmt.execute(obj[i].toString());
		stmt.close();
	} catch (SQLException e) {
		throw new AppException("导入的文件数据格式不正确！");
	}
}

  

 导出excel在这就不具体介绍了，无非是设置excel的属性。注意的就是数据定位到哪一行哪一列。jxl对excel的属性控制稍微弱一些，如果要格式美观等复杂的功能，建议用poi.

评论

2 楼 hufangdong 2013-03-20  

1 楼 xiao_kai 2011-12-29