导入导出Excel工具，支持XML和properties配置（一）

需要jxl.jar,dom4j.jar

 JXLUtil.java

 

package ssh.util;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
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;

/**
 * 导入,导出excel
 * @author gary
 * @version 1.0
 *
 */
public class JXLUtil {
	
	private JXLReader reader;
	
	public JXLUtil(){
		reader = new JXLReader();
	}
	
	
	/**
	 * 返回目标数据库列名key
	 * @param target
	 * @return
	 */
	public String getTargetKey(String target){
		return reader.getTargetValue(target, "key");
	}
	
	/**
	 * 返回目标标题
	 * @param target
	 * @return
	 */
	public String getTargetTitle(String target){
		return reader.getTargetValue(target, "title");
	}
	
	/**
	 * 返回目标文件名
	 * @param target
	 * @return
	 */
	public String getTargetFileName(String target){
		return reader.getTargetValue(target, "fileName");
	}
	
	/**
	 * 导出EXCEL
	 * 
	 * jxl.xml配置:
	 * <jxl>
	 * 		<目标>
	 * 			<title>标题</title>
	 * 			<key>数据库中的列名</key>
	 * 			<fileName>文件名</fileName>
	 * 		</目标>
	 * </jxl>
	 * example:
	 * <jxl>
	 * 		<user>
	 * 			<title>ID,用户名,登陆密码,真实名字</title>
	 * 			<key>userId,userName,password,realName</key>
	 * 			<fileName>userInfo</fileName>
	 *		</user>
	 * </jxl>
	 * 
	 * jxl.properties配置:
	 * 标题: jxl.目标.title
	 * 数据库表中的列名: jxl.目标.key
	 * 文件名: jxl.目标.fileName
	 * example:
	 * jxl.user.title=ID,\u7528\u6237\u540D,\u767B\u9646\u5BC6\u7801,\u771F\u5B9E\u540D\u5B57
	 * jxl.user.key=userId,userName,password,realName
	 * jxl.user.fileName=userInfo
	 * 
	 * @param list
	 * @param target
	 * @param response
	 */
	public void exportExcel(List<Map<String,?>> list, String target, HttpServletResponse response){
		
		try{
			//设置table列名
			String excelTitle = getTargetTitle(target);
			String[] excelTitleArray = excelTitle.split(",");
			//文件名
			String fileName = getTargetFileName(target) + "_" + new SimpleDateFormat("yyyy-MM-dd").format(new Date());
			//取得key
			String excelKey = getTargetKey(target);
			String[] excelKeyArray = excelKey.split(",");
			// 取得输出流 
			OutputStream os = response.getOutputStream();
			// 清空输出流
			response.reset();   
			// 设定输出文件头,不用GBK会乱码
				response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("GBK"),"ISO-8859-1") + ".xls");
			// 定义输出类型 
			response.setContentType("application/msexcel;charset=utf-8");
			// 建立excel文件 
			WritableWorkbook wwb;
			wwb = Workbook.createWorkbook(os);
			// 创建一个工作表
			WritableSheet ws = wwb.createSheet("Sheet1", 10);   
			// 设置单元格的文字格式
			//WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
			//        UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
			WritableFont wf = new WritableFont(WritableFont.ARIAL,11,WritableFont.BOLD,false,
	        UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
		    WritableCellFormat wcf = new WritableCellFormat(wf);
		    // 对齐方式
		    wcf.setAlignment(Alignment.CENTRE);
		    wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
		    // 边框
		    wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
		    // 背景色
		    wcf.setBackground(Colour.GRAY_25);
		    ws.setRowView(0, 500);
		    
		    //设置列头名
		    for (int j=0;j<excelKeyArray.length;j++) {
		    	ws.addCell(new Label(j, 0, excelTitleArray[j], wcf));
		    }
		    //设置内容
		    wcf = new WritableCellFormat();
		    for (int i = 0; i < list.size(); i++) {
		    	Map<String, ?> mapList = (Map<String, ?>)list.get(i);
		    	for (int j=0;j<excelKeyArray.length;j++) {
		    		ws.addCell(new Label(j, i+1, (mapList.get(excelKeyArray[j])+"").toString(), wcf));
		    	}
		    }
		    wwb.write();
		    wwb.close();
		} catch (IOException e){
        } catch (RowsExceededException e){
        } catch (WriteException e){
        }

	}
	
	/**
	 * 从excel文件读取数据,返回list
	 * @param file
	 * @param target
	 * @return
	 */
	public List<Map<String, ?>> getImportData(File file, String target){
		
		List<Map<String, ?>> data = new ArrayList<Map<String, ?>>();
		try {
			Workbook book = Workbook.getWorkbook(file);
	
			// 获得第一个sheet,默认有三个
			Sheet sheet = book.getSheet(0);
			// 一共有多少行多少列数据
			int rows = sheet.getRows();
			int columns = sheet.getColumns();
	
			boolean hasText = false;
			for (int i = 0; i < rows; i++) {
				// 过滤掉没有文本内容的行
				for (int j = 0; j < columns; j++)
					if (sheet.getCell(j, i).getContents() != "") {
						hasText = true;
						break;
					}
				if (hasText) {
					Map<String, String> temp = new HashMap<String, String>();
					
					//取得key
					String excelKey = getTargetKey(target);
					String[] excelKeyArray = excelKey.split(",");
					
					for(int k = 0 ; k < excelKeyArray.length ; k++){
						temp.put(excelKeyArray[k], sheet.getCell(k, i).getContents());
					}
//					temp.put(Integer.valueOf(sheet.getCell(0, i).getContents()));
					data.add(temp);
				}
			}
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return data;
	}

	
	//附:jdbc批量导入例子
	
//	//添加用户信息
//	private static final String SQL_ADD_USER = 
//		"insert into users(userId,userName,password,realName) values (?,?,?,?)";
//	
//	/**
//	 * 从Excel文件导入到数据库user表
//	 */
//    public int[] importUser(final File file) throws Exception {
//		
//        BatchPreparedStatementSetter pss = new BatchPreparedStatementSetter() {
//            
//            public void setValues(PreparedStatement ps, int i) throws SQLException {
//                Map row = (Map)(jxlUtil.getImportData(file, "user").get(i));
//
//				//取得key
//				String excelKey =jxlUtil.getTargetKey("user");
//				String[] excelKeyArray = excelKey.split(",");
//				
//				for(int k = 0 ; k < excelKeyArray.length ; k++){
//					ps.setString(k+1, (String)row.get(excelKeyArray[k]));
//				}
//    			
//            }
//            
//            public int getBatchSize() {
//                return jxlUtil.getImportData(file, "user").size();
//            }
//        };
//        return jt.batchUpdate(SQL_ADD_USER, pss);
//    }
}