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;
}
}
模板和效果图