在structs框架下使用JXL文件读取工具将Excel表格导入本地数据库

1.第一步使用JXLReadUtil工具类

package com.teraee.tasystem.util;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.commons.lang.StringUtils;

public class JXLReadUtil {
	private String filePath = null;
	private File file = null;
	private Workbook wk = null;
	private Map hmSheet = new HashMap();

	private JXLReadUtil(String filePath) {
		this.filePath = filePath;
		this.file = new File(filePath);
	}

	private JXLReadUtil(File file) {
		this.file = file;
	}

	public static JXLReadUtil getInstance(String filePath) {
		return new JXLReadUtil(filePath);
	}

	public static JXLReadUtil getInstance(File file) {
		return new JXLReadUtil(file);
	}

	public Workbook getWorkbook() {
		if (wk == null) {
			try {
				wk = Workbook.getWorkbook(this.file);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return wk;
	}

	/**
	 * 
	 * @param index
	 *            start with 0
	 * @return
	 */
	public Sheet getSheet(int index) {
		wk = getWorkbook();
		if (hmSheet.containsKey(index)) {
			return (Sheet) hmSheet.get(index);
		} else {
			Sheet sheet = wk.getSheet(index);
			hmSheet.put(index, sheet);
			return sheet;
		}
	}

	/**
	 * 
	 * @param sheet
	 * @param row
	 *            start with 0
	 * @return
	 */
	public String[] getContentsViaRow(Sheet sheet, int row) {
		Cell[] rowCells = sheet.getRow(row);
		int len = rowCells.length;
		String[] strCells = new String[len];
		for (int i = 0; i < len; i++) {
			strCells[i] = rowCells[i].getContents();
		}
		return strCells;
	}

	/**
	 * 
	 * @param sheet
	 * @param col
	 *            start with 0
	 * @return
	 */
	public String[] getContentsViaCol(Sheet sheet, int col) {
		Cell[] cells = sheet.getColumn(col);
		int len = cells.length;
		String[] strCols = new String[len];
		Cell c = null;
		for (int i = 0; i < len; i++) {
			c = cells[i];
			strCols[i] = c.getContents().trim();
		}
		return strCols;
	}

	public List<String[]> getFirstSheetRowsContents() {
		Sheet sheet = this.getSheet(0);
		int rows = sheet.getRows();
		List<String[]> ls = new ArrayList<String[]>();
		for (int i = 0; i < rows; i++) {
			ls.add(getContentsViaRow(sheet, i));
		}
		return ls;
	}

	public List<String[]> getFirstSheetColsContents() {
		Sheet sheet = this.getSheet(0);
		int cols = sheet.getColumns();
		List<String[]> ls = new ArrayList<String[]>();
		for (int i = 0; i < cols; i++) {
			ls.add(getContentsViaCol(sheet, i));
		}
		return ls;
	}
	
	public int getRightRows() { 
		Sheet sheet = this.getSheet(0);
		int rsCols = sheet.getColumns(); //列数 
		int rsRows = sheet.getRows(); //行数 
		int nullCellNum; 
		int afterRows = rsRows; 
		for (int i = 1; i < rsRows; i++) { //统计行中为空的单元格数 
		   nullCellNum = 0; 
		    for (int j = 0; j < rsCols; j++) { 
		        String val = sheet.getCell(j, i).getContents(); 
		        val = StringUtils.trimToEmpty(val); 
		        if (StringUtils.isBlank(val)) 
		           nullCellNum++; 
		    } 
		    if (nullCellNum >= rsCols) { //如果nullCellNum大于或等于总的列数 
		     afterRows--;          //行数减一 
		   } 
		} 
		return afterRows; 
		} 
	
	public int getRightRows(Sheet sheet) { 
		int rsCols = sheet.getColumns(); //列数 
		int rsRows = sheet.getRows(); //行数 
		int nullCellNum; 
		int afterRows = rsRows; 
		for (int i = 1; i < rsRows; i++) { //统计行中为空的单元格数 
		   nullCellNum = 0; 
		    for (int j = 0; j < rsCols; j++) { 
		        String val = sheet.getCell(j, i).getContents(); 
		        val = StringUtils.trimToEmpty(val); 
		        if (StringUtils.isBlank(val)) 
		           nullCellNum++; 
		    } 
		    if (nullCellNum >= rsCols) { //如果nullCellNum大于或等于总的列数 
		     afterRows--;          //行数减一 
		   } 
		} 
		return afterRows; 
		} 

	public static void main(String[] args) throws Exception {
		JXLReadUtil util = JXLReadUtil.getInstance("G:/test.xls");
		List<String[]> ls = util.getFirstSheetRowsContents();
		for (String[] ss : ls) {
			for (String s : ss) {
				System.out.println(s);
			}
		}

		List<String[]> lss = util.getFirstSheetColsContents();
		for (String[] ss : lss) {
			for (String s : ss) {
				System.out.println(s);
			}
		}
	}
}

2.使用maven的pom.xml配置导入jxl的jar包

<dependency>
   <groupId>net.sourceforge.jexcelapi</groupId>
   <artifactId>jxl</artifactId>
   <version>2.6.10</version>
</dependency>

3.后台Action代码

struts2框架在后台处理时要提供set和get方法用来存放前台传来的文件参数

private File file;
private String fileFileName;
private String fileFileContentType;

	public String getFileFileName() {
		return fileFileName;
	}

	public void setFileFileName(String fileFileName) {
		this.fileFileName = fileFileName;
	}

	public String getFileFileContentType() {
		return fileFileContentType;
	}

	public void setFileFileContentType(String fileFileContentType) {
		this.fileFileContentType = fileFileContentType;
	}

/**
     * 导入用户
     *@author LH
     *@data 2016年8月23日
     * @return
     */
    public String importUsers(){
    	log.loger.info("importUsers start");
    	 File uploadFile = null;
    	try {
			String serverFileName = UUID.randomUUID() + getExtention(this.getFileFileName());
			//获取导入文件路径
			uploadFile = new File(ServletActionContext.getServletContext().getRealPath("/importExcel") + "/" + serverFileName);
			log.loger.info("serverFileName "+serverFileName+" uploadFile "+uploadFile);
			//转存
			FileUtil.storeFile(this.file, uploadFile);
			//导入用户
			boolean result=this.userManager.importUsersExcel(uploadFile);
			 jsonMap = new JSONObject();
	    	if(result){
	    		jsonMap.put("result", "success");
	    	}else {
	    		jsonMap.put("result", "fail");
			}
		} catch (Exception e) {
			 jsonMap.put("result", "fail");
			 log.loger.info("import users happen exception!");
		}
    	
    	return SUCCESS;
    }

从文件中获取到后缀名

/**
     * 得到文件扩展名
     *@author LH
     *@data 2016年8月24日
     * @param fileName
     * @return
     */
    private String getExtention(String fileName)  {
        int pos = fileName.lastIndexOf(".");
        return fileName.substring(pos);
    }

4.写一个文件输入输出的工具类用于将文件转存到项目路径下

package com.teraee.tasystem.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.fileupload.DiskFileUpload;

public class FileUtil {

	private static final int BUFFER_SIZE = 16*1024 ;

	   private static Log log = Log.getLoger(new Object() {
	        public Class getClassName() {
	            return this.getClass();
	        }
	    }.getClassName());
	   
	   
	public static void storeFile(File file, String filePath) throws Exception{
		storeFile(file, new File(filePath));
	}
         
    public static void storeFile(File src, File dest) throws Exception {
    	log.loger.info("fileSrc "+src+" fileDest "+dest);
		try {
			InputStream in = null;
			OutputStream out = null;
			try {
				in = new BufferedInputStream(new FileInputStream(src), BUFFER_SIZE);
				out = new BufferedOutputStream(new FileOutputStream(dest), BUFFER_SIZE);
				byte[] buffer = new byte[BUFFER_SIZE];
				int len = -1;
				int flag=0;
				while((len = in.read(buffer)) != -1) {
					flag++;
					if(flag==1) System.out.println("in...............");
					out.write(buffer,0,len);
				}
				out.flush();
			}finally {
				if(null != in) {
					in.close();
				}
				if(null != out) {
					out.close();
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
    public static long getFileSize(File file) throws Exception{
    	long size=0;
    	if(file.exists()){
    	  FileInputStream fis=new FileInputStream(file);
    	  size=fis.available();   
    	}else{
    		//file.createNewFile();
    		System.out.println("文件不存在....");
    	}
    	
    	return size;
    }
   
}

5.接着到后台的service层将Excel文件的数据导入到数据库中

@Override
	public boolean importUsersExcel(File file) {
		JXLReadUtil jxlTool = JXLReadUtil.getInstance(file);
		Sheet sheet = jxlTool.getSheet(0);
		int rows = sheet.getRows();
		List<String[]> list = new ArrayList<String[]>();
		for (int j = 0; j < rows; j++){
			list.add(jxlTool.getContentsViaRow(sheet, j));
		}
		if (list.size() > 0){
			List<Map<String, Object>> listTwo = new ArrayList<Map<String, Object>>();
			for (int j = 2; j < list.size(); j++){
				Map<String, Object> map = new HashMap<String, Object>();
				String[] names = list.get(j);
				if(names.length==8){
					map.put("loginName", names[1].trim());
					map.put("loginPasswd", names[2].trim());
					map.put("userName", names[3].trim());
					map.put("age", names[4].trim());
					map.put("tel", names[5].trim());
					map.put("email", names[6].trim());
					map.put("role", names[7].trim());
					listTwo.add(map);
				}
			}
			log.loger.info("user data  "+listTwo);
				for (int i = 0; i < listTwo.size(); i++){
					String loginName = listTwo.get(i).get("loginName").toString();
					log.loger.info("loginName "+loginName);
					//如果登录的用户名相同则不保存
					int count= this.userDao.getUsersByloginName(loginName.toLowerCase());
					log.loger.info("count "+count);
					if(count <1){
						User user=new User();
						//导入的用户名全部变为小写
						user.setUsername(loginName.toLowerCase());
						try {
							user.setPassword(MD5Util.md5Encode(listTwo.get(i).get("loginPasswd").toString()));
						} catch (Exception e) {
							e.printStackTrace();
						}
						user.setNickname(listTwo.get(i).get("userName").toString());
						user.setAge( listTwo.get(i).get("age").toString());
						user.setPhonenumber(listTwo.get(i).get("tel").toString());
						user.setEmail(listTwo.get(i).get("email").toString());
						user.setRole(listTwo.get(i).get("role").toString());
						//设置导入的用户为本地账号
						user.setLocalstate(Constant.LOCAL_ACCOUNT_ENABLE);
						this.userDao.save(user);
						log.loger.info("save  "+user.getNickname()+" success ");
					}
				}
				return true;
			}else {
				return false;
			}
	}

最后总结：其实struct2的导入EXCEL数据其实并不难,关键之处在于怎么将一个前台传来的文件转存到本地项目路径下,再然后将存储的文件用EXCEL读取的工具类进行读取获取EXCEL里面的数据，插入到本地数据库中。