Excel导入和导出

1.导入jar

    jxl.jar

2.导入excel模板（注意，使用Workbook不可以导出xlsx格式，所以模板建议用xls。xls所有OFFICE程序都能打开，xlsx只有OFFICE2007以上的版本才能打开）

3.工具类

package com.hongwei.futures.util;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Vector;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.format.BorderLineStyle;
import jxl.write.WritableCell;
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.comet4j.event.demo.mac.Test.Person;

public class ExcelUtil {
	/**
	 * 验证输入的数据格式转换
	 * @param col
	 * @param row
	 * @param value
	 * @param wcFormat
	 * @return
	 */
	public static WritableCell cloneCellWithValue(int col, int row, Object value,WritableCellFormat wcFormat) {
		WritableCell wc = null;
		// 判断数据是否为STRING类型，是用LABLE形式插入，否则用NUMBER形式插入
		if (value == null) {
			wc = new jxl.write.Blank(col, row,wcFormat);
		} else if (value instanceof String) {
			jxl.write.Label label = new jxl.write.Label(col, row,
					value.toString(),wcFormat);
			wc = label;
		} else {
			wc = new jxl.write.Number(col, row, new Double(value.toString()).doubleValue(),wcFormat);
		}
		return wc;
	}
	
	/**
	 * 获得单元格标准格式
	 * @return
	 */
	public static WritableCellFormat getWritableCellFormatCellFormat(){
		WritableCellFormat wcf = new WritableCellFormat();     
		try {
			  // 设置居中   
			wcf.setAlignment(Alignment.CENTRE);
			wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
		} catch (WriteException e) {
			e.printStackTrace();
		}     
		return wcf;
	}

   /**
     * 导出数据为XLS格式
     * @param fileName        文件的名称，可以设为绝对路径，也可以设为相对路径
     * @param content        数据的内容
     */
    public static void exportExcel(String fileName, Vector<Person> content) {
        WritableWorkbook wwb;
        FileOutputStream fos;
        try {    
            fos = new FileOutputStream(fileName);
            wwb = Workbook.createWorkbook(fos);
            WritableSheet ws = wwb.createSheet("三国志武将列表", 10);        // 创建一个工作表

            //    设置单元格的文字格式
            WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,
                    UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
            WritableCellFormat wcf = new WritableCellFormat(wf);
            wcf.setVerticalAlignment(VerticalAlignment.CENTRE); 
            wcf.setAlignment(Alignment.CENTRE); 
            ws.setRowView(1, 500);

            //    填充数据的内容
            Person[] p = new Person[content.size()];
            for (int i = 0; i < content.size(); i++){
                p[i] = content.get(i);
                if(i == 0)
                    wcf = new WritableCellFormat();
            }

            wwb.write();
            wwb.close();

        } catch (IOException e){
        } catch (RowsExceededException e){
        } catch (WriteException e){}
    }
}

package com.hongwei.futures.util;

import java.text.DecimalFormat;
import java.text.NumberFormat;

//总体思路：
//对数字进行分级处理，级长为4
//对分级后的每级分别处理，处理后得到字符串相连
//如：123456=12|3456
//第二级：12=壹拾贰 + “万”
//第一级：3456 =叁千肆百伍拾陆 + “”
public class RMB {
	private double amount = 0.0D;
	private static final String NUM = "零壹贰叁肆伍陆柒捌玖";
	private static final String UNIT = "仟佰拾个";
	private static final String GRADEUNIT = "仟万亿兆";
	private static final String DOTUNIT = "角分厘";
	private static final int GRADE = 4;
	private static final String SIGN = "￥";
	private static final NumberFormat nf = new DecimalFormat("#0.###");

	public RMB(double amount) {
		this.amount = amount;
	}

	public String toBigAmt() {
		return toBigAmt(this.amount);
	}

	public static String toBigAmt(double amount) {
		String amt = nf.format(amount);
		Double d = new Double(amount);
		String dotPart = ""; // 取小数位
		String intPart = ""; // 取整数位
		int dotPos;
		if ((dotPos = amt.indexOf('.')) != -1) {
			intPart = amt.substring(0, dotPos);
			dotPart = amt.substring(dotPos + 1);
		} else {
			intPart = amt;
		}
		if (intPart.length() > 16)
			throw new java.lang.InternalError("The amount is too big.");
		String intBig = intToBig(intPart);
		String dotBig = dotToBig(dotPart);
		// 以下代码稍做修改，现在是完美的代码啦！
		if ((dotBig.length() == 0) && (intBig.length() != 0)) {
			return intBig + "元整";
		} else if ((dotBig.length() == 0) && (intBig.length() == 0)) {
			return intBig + "零元";
		} else if ((dotBig.length() != 0) && (intBig.length() != 0)) {
			return intBig + "元" + dotBig;
		} else {
			return dotBig;
		}
	}

	private static String dotToBig(String dotPart) {
		// 得到转换后的大写（小数部分）
		String strRet = "";
		for (int i = 0; i < dotPart.length() && i < 3; i++) {
			int num;
			if ((num = Integer.parseInt(dotPart.substring(i, i + 1))) != 0)
				strRet += NUM.substring(num, num + 1) + DOTUNIT.substring(i, i + 1);
		}
		return strRet;
	}

	private static String intToBig(String intPart) {
		// 得到转换后的大写（整数部分）
		int grade; // 级长
		String result = "";
		String strTmp = "";
		// 得到当级长
		grade = intPart.length() / GRADE;
		// 调整级次长度
		if (intPart.length() % GRADE != 0)
			grade += 1;
		// 对每级数字处理
		for (int i = grade; i >= 1; i--) {
			strTmp = getNowGradeVal(intPart, i);// 取得当前级次数字
			result += getSubUnit(strTmp);// 转换大写
			result = dropZero(result);// 除零
			// 加级次单位
			if (i > 1) // 末位不加单位
				// 单位不能相连续
				if (getSubUnit(strTmp).equals("零零零零")) {
					result += "零" + GRADEUNIT.substring(i - 1, i);
				} else {
					result += GRADEUNIT.substring(i - 1, i);
				}
		}
		return result;
	}

	private static String getNowGradeVal(String strVal, int grade) {
		// 得到当前级次的串
		String rst;
		if (strVal.length() <= grade * GRADE)
			rst = strVal.substring(0, strVal.length() - (grade - 1) * GRADE);
		else
			rst = strVal.substring(strVal.length() - grade * GRADE, strVal.length() - (grade - 1) * GRADE);
		return rst;
	}

	private static String getSubUnit(String strVal) {
		// 数值转换
		String rst = "";
		for (int i = 0; i < strVal.length(); i++) {
			String s = strVal.substring(i, i + 1);
			int num = Integer.parseInt(s);
			if (num == 0) {
				// “零”作特殊处理
				if (i != strVal.length()) // 转换后数末位不能为零
					rst += "零";
			} else {
				// If IntKey = 1 And i = 2 Then
				// “壹拾”作特殊处理
				// “壹拾”合理
				// Else
				rst += NUM.substring(num, num + 1);
				// End If
				// 追加单位
				if (i != strVal.length() - 1)// 个位不加单位
					rst += UNIT.substring(i + 4 - strVal.length(), i + 4 - strVal.length() + 1);
			}
		}
		return rst;
	}

	private static String dropZero(String strVal) {
		// 去除连继的“零”
		String strRst;
		String strBefore; // 前一位置字符
		String strNow; // 现在位置字符
		strBefore = strVal.substring(0, 1);
		strRst = strBefore;
		for (int i = 1; i < strVal.length(); i++) {
			strNow = strVal.substring(i, i + 1);
			if (strNow.equals("零") && strBefore.equals("零"))
				;// 同时为零
			else
				strRst += strNow;
			strBefore = strNow;
		}
		// 末位去零
		if (strRst.substring(strRst.length() - 1, strRst.length()).equals("零"))
			strRst = strRst.substring(0, strRst.length() - 1);
		return strRst;
	}
}

package com.hongwei.futures.util;

import java.text.DecimalFormat;
import java.util.Date;
import java.util.List;

/**
 * 
 * @author 充满智慧的威哥
 *
 */
public class StringUtil {
	
	/**
	 * 截取字符串
	 * @param s
	 * @param maxLength
	 * @return
	 */
	public static String interceptStr(String s, int maxLength) {
		if (isBlank(s)) {
			return "";
		}
		return s.length() > maxLength ? s.substring(0, maxLength - 1) + "..."  : s;
	}
	
	/**
	 * 判断字符串是否为空
	 * @param serverMoney
	 * @return
	 */
	public static boolean isBlank(String serverMoney) {
		if (serverMoney == null || serverMoney.trim().length() == 0) {
			return true;
		}
		return false;
	}
	
	/**
	 * 判断字符串是否为数字字符串
	 * @param str
	 * @return
	 */
	 public static boolean isNumeric(String str){ 
		 for (int i = str.length();--i>=0;){ 
			   if (!Character.isDigit(str.charAt(i))){
			    return false; 
			   } 
		  }
		  return true; 
    }
	 
	
	/**
	 * 首字母小写
	 * 
	 * @param s String
	 * @return String
	 */
	public static String firstCharLowerCase(String s) {
		if (s == null || "".equals(s)) {
			return ("");
		}
		return s.substring(0, 1).toLowerCase() + s.substring(1);
	}

	/**
	 * 首字母大写
	 * 
	 * @param s String
	 * @return String
	 */
	public static String firstCharUpperCase(String s) {
		if (s == null || "".equals(s)) {
			return ("");
		}
		return s.substring(0, 1).toUpperCase() + s.substring(1);
	}

	/**
	 * aBbbCcc => a_bbb_ccc
	 * 
	 * @param property
	 * @return String
	 */
	public static String getConverColName(String property) {
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < property.length(); i++) { // 遍历property如果有大写字母则将大写字母转换为_加小写
			char cur = property.charAt(i);
			if (Character.isUpperCase(cur)) {
				sb.append("_");
				sb.append(Character.toLowerCase(cur));
			} else {
				sb.append(cur);
			}
		}
		return sb.toString();
	}
	
	/**
	 * a_bbb_ccc => aBbbCcc
	 * 
	 * @param property
	 * @return String
	 */
	public static String getConverColBean(String property) {
		if (isBlank(property) || property.indexOf("_") == -1) {
			return property;
		}
		StringBuffer sb = new StringBuffer();
		boolean flag = false;
		for (int i = 0; i < property.length(); i++) { // 遍历property如果有大写字母则将大写字母转换为_加小写
			char cur = property.charAt(i);
			if ('_' == cur) {
				flag = true;
				continue;
			} else {
				sb.append(flag ? Character.toUpperCase(cur) : cur);
				flag = false;
			}
		}
		return sb.toString();
	}
	
	/**
	 * 是否有中文字符
	 * 
	 * @param s
	 * @return
	 */
	public static boolean hasCn(String s) {
		if (s == null) {
			return false;
		}
		return countCn(s) > s.length();
	}

	/**
	 * 获得字符。符合中文习惯。
	 * 
	 * @param s
	 * @param length
	 * @return
	 */
	public static String getCn(String s, int len) {
		if (s == null) {
			return s;
		}
		int sl = s.length();
		if (sl <= len) {
			return s;
		}
		// 留出一个位置用于…
		len -= 1;
		int maxCount = len * 2;
		int count = 0;
		int i = 0;
		while (count < maxCount && i < sl) {
			if (s.codePointAt(i) < 256) {
				count++;
			} else {
				count += 2;
			}
			i++;
		}
		if (count > maxCount) {
			i--;
		}
		return s.substring(0, i) + "…";
	}

	/**
	 * 计算GBK编码的字符串的字节数
	 * 
	 * @param s
	 * @return
	 */
	public static int countCn(String s) {
		if (s == null) {
			return 0;
		}
		int count = 0;
		for (int i = 0; i < s.length(); i++) {
			if (s.codePointAt(i) < 256) {
				count++;
			} else {
				count += 2;
			}
		}
		return count;
	}

	/**
	 * 文本转html
	 * 
	 * @param txt
	 * @return
	 */
	public static String txt2htm(String txt) {
		if (isBlank(txt)) {
			return txt;
		}
		StringBuilder bld = new StringBuilder();
		char c;
		for (int i = 0; i < txt.length(); i++) {
			c = txt.charAt(i);
			switch (c) {
			case '&':
				bld.append("&");
				break;
			case '<':
				bld.append("<");
				break;
			case '>':
				bld.append(">");
				break;
			case '"':
				bld.append(""");
				break;
			case ' ':
				bld.append(" ");
				break;
			case '\n':
				bld.append("<br/>");
				break;
			default:
				bld.append(c);
				break;
			}
		}
		return bld.toString();
	}

	/**
	 * html转文本
	 * 
	 * @param htm
	 * @return
	 */
	public static String htm2txt(String htm) {
		if (htm == null) {
			return htm;
		}
		htm = htm.replace("&", "&");
		htm = htm.replace("<", "<");
		htm = htm.replace(">", ">");
		htm = htm.replace(""", "\"");
		htm = htm.replace(" ", " ");
		htm = htm.replace("<br/>", "\n");
		return htm;
	}

	/**
	 * 全角-->半角
	 * 
	 * @param qjStr
	 * @return
	 */
	public String Q2B(String qjStr) {
		String outStr = "";
		String Tstr = "";
		byte[] b = null;
		for (int i = 0; i < qjStr.length(); i++) {
			try {
				Tstr = qjStr.substring(i, i + 1);
				b = Tstr.getBytes("unicode");
			} catch (java.io.UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			if (b[3] == -1) {
				b[2] = (byte) (b[2] + 32);
				b[3] = 0;
				try {
					outStr = outStr + new String(b, "unicode");
				} catch (java.io.UnsupportedEncodingException e) {
					e.printStackTrace();
				}
			} else
				outStr = outStr + Tstr;
		}
		return outStr;
	}

	public static final char[] N62_CHARS = { '0', '1', '2', '3', '4', '5', '6',
			'7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
			'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w',
			'x', 'y', 'z', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
			'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W',
			'X', 'Y', 'Z' };
	public static final char[] N36_CHARS = { '0', '1', '2', '3', '4', '5', '6',
			'7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
			'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w',
			'x', 'y', 'z' };

	private static StringBuilder longToNBuf(long l, char[] chars) {
		int upgrade = chars.length;
		StringBuilder result = new StringBuilder();
		int last;
		while (l > 0) {
			last = (int) (l % upgrade);
			result.append(chars[last]);
			l /= upgrade;
		}
		return result;
	}

	/**
	 * 长整数转换成N62
	 * 
	 * @param l
	 * @return
	 */
	public static String longToN62(long l) {
		return longToNBuf(l, N62_CHARS).reverse().toString();
	}

	public static String longToN36(long l) {
		return longToNBuf(l, N36_CHARS).reverse().toString();
	}

	/**
	 * 长整数转换成N62
	 * 
	 * @param l
	 * @param length
	 *            如N62不足length长度，则补足0。
	 * @return
	 */
	public static String longToN62(long l, int length) {
		StringBuilder sb = longToNBuf(l, N62_CHARS);
		for (int i = sb.length(); i < length; i++) {
			sb.append('0');
		}
		return sb.reverse().toString();
	}

	public static String longToN36(long l, int length) {
		StringBuilder sb = longToNBuf(l, N36_CHARS);
		for (int i = sb.length(); i < length; i++) {
			sb.append('0');
		}
		return sb.reverse().toString();
	}

	/**
	 * N62转换成整数
	 * 
	 * @param n62
	 * @return
	 */
	public static long n62ToLong(String n62) {
		return nToLong(n62, N62_CHARS);
	}

	public static long n36ToLong(String n36) {
		return nToLong(n36, N36_CHARS);
	}

	private static long nToLong(String s, char[] chars) {
		char[] nc = s.toCharArray();
		long result = 0;
		long pow = 1;
		for (int i = nc.length - 1; i >= 0; i--, pow *= chars.length) {
			int n = findNIndex(nc[i], chars);
			result += n * pow;
		}
		return result;
	}

	private static int findNIndex(char c, char[] chars) {
		for (int i = 0; i < chars.length; i++) {
			if (c == chars[i]) {
				return i;
			}
		}
		throw new RuntimeException("N62(N36)非法字符：" + c);
	}
	/**
	 * 方法描述:把数组1,2,3转化成字符串
	 * @param integerList
	 * @return
	 */
	public static String getSplitStringByInt(List<Integer> integerList){
		if(null!=integerList&&integerList.size()!=0){
			String splitString = "";
			for(int intInstance : integerList){
				splitString += intInstance+",";			
			}
			return splitString.substring(0,splitString.length()-1);
		}else{
			return null;
		}
	}/**
	 * 方法描述:把数组1,2,3转化成字符串
	 * @param integerList
	 * @return
	 */
	public static String getSplitStringByString(List<String> StringList){
		if(null!=StringList&&StringList.size()!=0){
			String splitString = "";
			for(String stringInstance : StringList){
				splitString += stringInstance+",";			
			}
			return splitString.substring(0,splitString.length()-1);
		}else{
			return null;
		}
	}
	/**
	 * 拼装('1','2','3',...) 
	 * @param ids
	 * @return
	 */
	public static String getHqlIdStr(Object[] ids){
		StringBuffer hql=new StringBuffer();
		hql.append("(");
		for(int i=0;i<ids.length-1;i++){
			hql.append("'").append(ids[i].toString()).append("'").append(",");
		}
		hql.append("'").append(ids[ids.length-1].toString()).append("'");
		hql.append(")");
		return hql.toString();
	}
	
	public static String createBlock(Long[] dirIds) {
		if (dirIds == null || dirIds.length == 0)
			return "('')";
		StringBuilder blockStr = new StringBuilder("(");
		for (int i = 0; i < dirIds.length - 1; i++) {
			blockStr.append("'").append(dirIds[i]).append( "',");
		}
		blockStr.append("'").append(dirIds[dirIds.length - 1]).append( "')");
		return blockStr.toString();
	}
	/**
	 * 判断字符串是否在规定范围内
	 * @param str
	 * @param min
	 * @param max
	 * @return
	 */
	public static Boolean checkString(String str,int min,int max){
		if(str==null||str.trim().length()<min||str.trim().length()>max)
			return false;
		return true;
	}
	
	/**
	 * 获取距离现在的时间
	 */
	public static String getMinutes(long times) {
		long time = new Date().getTime()-times;// time 单位是 毫秒
		String res = null; // 转化成天数
		
		if (time < 60 * 60 * 1000) {
			// 先判断是不是小于 60 * 60 * 1000 也就是 小于1小时，那么显示 ： **分钟前
			res = (time / 1000 / 60) + "分钟前";
		}
		else if (time >= 60 * 60 * 1000 && time < 24 * 60 * 60 * 1000) {
			// 如果大于等于1小时 小于等于一天，那么显示 ： **小时前
			res = (time / 1000 / 60 / 60) + "小时前";
		}
		else if (time >= 24 * 60 * 60 * 1000 && time < 7 * 24 * 60 * 60 * 1000 ) {
			// 如果大于等于1小时 小于等于一天，那么显示 ： **小时前
			res = (time / 1000 / 60 / 60 / 24) + "天前";
		}
		else if (time >= 7 * 24 * 60 * 60 * 1000) {
			res = "一周前";
		}
		// 如果时间不明确或者发帖不足一分钟 ，则不显示
		else {
			res = "刚刚";
		}
		return res;
	}
	
	/**
	 * 自定义格式
	 * @param pattern
	 * @param data
	 * @return
	 */
	public static String getDecimalFormat(String pattern, Object data){
		DecimalFormat df = new DecimalFormat();
		df.applyPattern(pattern);
		return  df.format(data);
	}
	
	/**
	 * 标准金额格式输出
	 * @param data
	 * @return
	 */
	public static String getDecimalFormat(Object data){
		DecimalFormat df = new DecimalFormat();
		df.applyPattern("#,###,##0.00");
		return  df.format(data);
	}
}

4.实现导入功能

/**
	 * 从Excel文件中读取数据, 将excel中的每行记录保存到hhr_stat_temp中供查看确认
	 * 
	 * @param uploadFileName
	 */
	public void uploadExcel(String uploadFileName) {
		String directory = "/uploads";
		String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
		File target = new File(targetDirectory, uploadFileName);
		try {
			FileInputStream fis = new FileInputStream(target);
			try {
				Workbook wb = Workbook.getWorkbook(fis);
				Sheet sheet = wb.getSheet(0);
				int maxBatch = hhrStatTempService.findMaxBatchNumber();
				for (int i = 1; i < sheet.getRows(); i++) {
					// 注意sheet.getCell(列,行).getContents()得到的都是String类型，记得转型
					Long userId = Long.valueOf(sheet.getCell(0, i).getContents());
					Double money = Double.valueOf(sheet.getCell(2, i).getContents());
					FuUser fuUser = fuUserService.get(userId);
					HhrStatTemp hhrStatTemp = new HhrStatTemp();
					hhrStatTemp.setFuUser(fuUser);
					hhrStatTemp.setMoney(new BigDecimal(money * 10000));
					hhrStatTemp.setCreateDate(new Date());
					hhrStatTemp.setBatchNum(maxBatch);
					hhrStatTempService.save(hhrStatTemp);
				}
				wb.close();
			} catch (BiffException e) {
				e.printStackTrace();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

5.实现导出规则的excel列表

@Action("exportExcel")
	public String exportExcel() {
		try {
			OutputStream os = this.getHttpServletResponse().getOutputStream();
			String fileName = System.currentTimeMillis() + ".xls";
			this.getHttpServletResponse().setContentType("APPLICATION/OCTET-STREAM");
			this.getHttpServletResponse().setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
			String excelPath = this.getServletContext().getRealPath("/");
			String separator = System.getProperty("file.separator");
			excelPath = excelPath + "uploads" + separator + "attach" + separator + "money_detail.xls";//所使用模板的真实路径
			InputStream is = new FileInputStream(excelPath);
			Workbook wb = Workbook.getWorkbook(is);
			WorkbookSettings settings = new WorkbookSettings();
			settings.setWriteAccess(null);
			WritableWorkbook wwb = Workbook.createWorkbook(os, wb, settings);
			WritableCell wc = null;
			WritableSheet ws = wwb.getSheet("sheet1");

			// 查询数据
			Map<String, Object> map = new HashMap<String, Object>();
			if (!StringUtil.isBlank(accountName)) {
				map.put("accountName", accountName);
			}
			if (money1 != null)
				map.put("money1", money1);
			if (money2 != null)
				map.put("money2", money2);
			if (date1 != null) {
				map.put("date1", date1);
			}
			if (date2 != null) {
				map.put("date2", date2);
			}
			List<FuMoneyDetail> detailList = fuMoneyDetailService.findFuMoneyDetailByParams(map);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

			// 第二行
			WritableCellFormat dateWcf = new WritableCellFormat();
			dateWcf.setAlignment(Alignment.CENTRE);
			dateWcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wc = ws.getWritableCell(1, 1);
			wc = ExcelUtil.cloneCellWithValue(1, 1, (date1 != null ? sdf.format(date1) : "") + " 至 " 
                        + (date2 != null ? sdf.format(date2) : ""), dateWcf);
			ws.addCell(wc);

			for (int i = 3; i < detailList.size() + 3; i++) {
				// 序号即id
				WritableCellFormat wcf = new WritableCellFormat();
				wcf.setAlignment(Alignment.CENTRE);
				wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(0, i);
				wc = ExcelUtil.cloneCellWithValue(0, i, detailList.get(i - 3).getId().toString(), wcf);
				ws.addCell(wc);

				// 用户名
				WritableCellFormat wcf2 = new WritableCellFormat();
				wcf2.setAlignment(Alignment.CENTRE);
				wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(1, i);
				wc = ExcelUtil.cloneCellWithValue(1, i, detailList.get(i - 3).getFuUser().getAccountName(), wcf2);
				ws.addCell(wc);

				// 真实姓名
				WritableCellFormat wcf3 = new WritableCellFormat();
				wcf3.setAlignment(Alignment.CENTRE);
				wcf3.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(2, i);
				wc = ExcelUtil.cloneCellWithValue(2, i, detailList.get(i - 3).getFuUser().getUserName() == null ? "" : 
                                detailList.get(i - 3).getFuUser().getUserName(), wcf3);
				ws.addCell(wc);

				// 类型
				WritableCellFormat wcf4 = new WritableCellFormat();
				wcf4.setAlignment(Alignment.CENTRE);
				wcf4.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(3, i);
				wc = ExcelUtil.cloneCellWithValue(3, i, detailList.get(i - 3).getFuDictionary().getName(), wcf3);
				ws.addCell(wc);

				// 详情
				WritableCellFormat wcf5 = new WritableCellFormat();
				wcf5.setAlignment(Alignment.CENTRE);
				wcf5.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(4, i);
				wc = ExcelUtil.cloneCellWithValue(4, i, detailList.get(i - 3).getComment(), wcf5);
				ws.addCell(wc);

				// 金额
				WritableCellFormat wcf6 = new WritableCellFormat();
				wcf6.setAlignment(Alignment.RIGHT);
				wcf6.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(5, i);
				wc = ExcelUtil.cloneCellWithValue(5, i, detailList.get(i - 3).getMoney().toString(), wcf5);
				ws.addCell(wc);

				// 可用金额
				WritableCellFormat wcf7 = new WritableCellFormat();
				wcf7.setAlignment(Alignment.RIGHT);
				wcf7.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(6, i);
				wc = ExcelUtil.cloneCellWithValue(6, i, detailList.get(i - 3).getAccountBalanceAfter().toString(), wcf5);
				ws.addCell(wc);

				// 时间
				WritableCellFormat wcf8 = new WritableCellFormat();
				wcf8.setAlignment(Alignment.CENTRE);
				wcf8.setVerticalAlignment(VerticalAlignment.CENTRE);
				wc = ws.getWritableCell(7, i);
				wc = ExcelUtil.cloneCellWithValue(7, i, sdf2.format(detailList.get(i - 3).getTime()), wcf8);
				ws.addCell(wc);
			}
			wwb.write();
			wwb.close();
			System.out.println("导出成功");
			return null;
		} catch (Exception e) {
			System.out.println("导出失败");
			e.printStackTrace();
			return null;
		}
	}

模板和效果图

6.实现导出非规则的excel表格

/**
	 * 付款确认表导出
	 * 
	 * @return
	 */
	@Action("exportExcel")
	public String exportExcel() {
		try {
			// 获取数据库信息
			FuDrawMoney draw = fuDrawMoneyService.get(id);

			// 直接往response的输出流中写excel
			OutputStream os = this.getHttpServletResponse().getOutputStream();
			// 获取文件名称
			String fileName = System.currentTimeMillis() + ".xls";
			// 下载格式设置
			this.getHttpServletResponse().setContentType("APPLICATION/OCTET-STREAM");
			this.getHttpServletResponse().setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");

			// 选择模板文件：
			String excelPath = this.getServletContext().getRealPath("/");
 			String separator = System.getProperty("file.separator");
			excelPath = excelPath + "uploads" + separator + "attach" + separator + "payMoney_sure.xls";//模板真实路径
	 		InputStream is = new FileInputStream(excelPath);
 			Workbook wb = Workbook.getWorkbook(is);
			WorkbookSettings settings = new WorkbookSettings();
			settings.setWriteAccess(null);
			// 通过模板得到一个可写的Workbook：
			WritableCell wc = null;

 			WritableWorkbook wwb = Workbook.createWorkbook(os, wb, settings);
			// 选择模板中名称为sheet1的Sheet：
 			WritableSheet ws = wwb.getSheet("sheet1");

			WritableCellFormat wcf = ExcelUtil.getWritableCellFormatCellFormat();
			WritableCellFormat noWCF = new WritableCellFormat();
 			noWCF.setBorder(Border.ALL, BorderLineStyle.NONE);
 			// 选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：

			// 第2行填表时间
			WritableCellFormat wcf2 = new WritableCellFormat();
			wcf2.setAlignment(Alignment.RIGHT);
			wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);
			wc = ws.getWritableCell(0, 1);
 			wc = ExcelUtil.cloneCellWithValue(0, 1, "填表时间：" + 
                        (draw.getCheckTime() == null ? "                      " : 
                        DateUtil.getStrFromDate(draw.getCheckTime(), "yyyy年MM月dd日")), wcf2);
			ws.addCell(wc);

			// 第56行
			WritableCellFormat wcf5 = new WritableCellFormat();
			wcf5.setBorder(Border.ALL, BorderLineStyle.THIN);
			wcf5.setAlignment(Alignment.LEFT);
			wcf5.setVerticalAlignment(VerticalAlignment.CENTRE);
			wc = ws.getWritableCell(3, 4);
			wc = ExcelUtil.cloneCellWithValue(3, 4, draw.getFuUser().getUserName(), wcf5); // 用户姓名
			ws.addCell(wc);
			wc = ws.getWritableCell(5, 4);
			wc = ExcelUtil.cloneCellWithValue(5, 4, draw.getFuUser().getPhone(), wcf5); // 用户手机号
			ws.addCell(wc);

			// 第7行
			WritableCellFormat wcf7 = new WritableCellFormat();
			wcf7.setBorder(Border.ALL, BorderLineStyle.THIN); // 加四周边框线条
			wcf7.setAlignment(Alignment.LEFT); // 水平居中
			wcf7.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中
			wc = ws.getWritableCell(1, 6);
			wc = ExcelUtil.cloneCellWithValue(1, 6, "提款后线上平台余额为：" + 
                        RMB.toBigAmt(draw.getFuUser().getAccountBalance().doubleValue()) + 
                        "（￥" + StringUtil.getDecimalFormat(draw.getFuUser().getAccountBalance()) + "）", wcf7); 
			ws.addCell(wc);

			// 第8行
			WritableCellFormat wcf8 = new WritableCellFormat();
			wcf8.setBorder(Border.ALL, BorderLineStyle.THIN); // 加四周边框线条
			wcf8.setAlignment(Alignment.CENTRE); // 水平居中
			wcf8.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中
			wc = ws.getWritableCell(2, 7);
			wc = ExcelUtil.cloneCellWithValue(2, 7, "王小明", wcf8);
			ws.addCell(wc);
			wc = ws.getWritableCell(6, 7);
			wc = ExcelUtil.cloneCellWithValue(6, 7, draw.getFuUser().getUserName(), wcf8);
			ws.addCell(wc);

			// 第9行
			WritableCellFormat wcf9 = new WritableCellFormat();
			wcf9.setBorder(Border.ALL, BorderLineStyle.THIN); // 加四周边框线条
			wcf9.setAlignment(Alignment.CENTRE); // 水平居左
			wcf9.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中
			wc = ws.getWritableCell(2, 8);
			wc = ExcelUtil.cloneCellWithValue(2, 8, "中国建设银行（北京长河湾支行）", wcf9); // 强平线
			ws.addCell(wc);
			wc = ws.getWritableCell(6, 8);
			wc = ExcelUtil.cloneCellWithValue(6, 8, draw.getFuBankCard().getBankName() + "   " + 
                        draw.getFuBankCard().getBankAddress(), wcf9); // 强平线
			ws.addCell(wc);

			// 第10行
			WritableCellFormat wcf10 = new WritableCellFormat();
			wcf10.setBorder(Border.ALL, BorderLineStyle.THIN); // 加四周边框线条
			wcf10.setAlignment(Alignment.CENTRE); // 水平居左
			wcf10.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中
			wc = ws.getWritableCell(2, 9);
			wc = ExcelUtil.cloneCellWithValue(2, 9, "6217 xxxx xxxx xxx 314", wcf10); // 强平线
			ws.addCell(wc);
			wc = ws.getWritableCell(6, 9);
			wc = ExcelUtil.cloneCellWithValue(6, 9, draw.getFuBankCard().getCardNumber(), wcf10); // 强平线
			ws.addCell(wc);

			// 第11行
			WritableCellFormat wcf11 = new WritableCellFormat();
			wcf11.setBorder(Border.ALL, BorderLineStyle.THIN); // 加四周边框线条
			wcf11.setAlignment(Alignment.LEFT); // 水平居左
			wcf11.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中
			wc = ws.getWritableCell(1, 10);
			wc = ExcelUtil.cloneCellWithValue(1, 10, "付款金额大、小写： " + 
                        RMB.toBigAmt(draw.getDrawMoney().doubleValue()) + 
                        "（￥" + StringUtil.getDecimalFormat(draw.getDrawMoney()) + "）", wcf11); // 强平线
			ws.addCell(wc);

			// 第12行
			WritableCellFormat wcf12 = new WritableCellFormat();
			wcf12.setBorder(Border.ALL, BorderLineStyle.THIN); // 加四周边框线条
			wcf12.setAlignment(Alignment.RIGHT); // 水平居左
			wcf12.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直居中
			wc = ws.getWritableCell(0, 11);
			wc = ExcelUtil.cloneCellWithValue(0, 11, "经办：" + 
                        (draw.getFuAdmin() == null ? "         " : draw.getFuAdmin().getName()) + 
                        "          核对：                            审批：                            财务：                            ", wcf12); // 强平线
			ws.addCell(wc);

			wwb.write();
			// 关闭文件
			wwb.close();
			System.out.println("导出成功");
			return null;
		} catch (Exception e) {
			System.out.println("导出失败");
			e.printStackTrace();
			return null;
		}
	}

模板和效果图