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);elserst = 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// “壹拾”作特殊处理// “壹拾”合理// Elserst += 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("零"));// 同时为零elsestrRst += 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();}} elseoutStr = 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++) {// 序号即idWritableCellFormat 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的输出流中写excelOutputStream 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;}}

模板和效果图