1.在WEB/WEB-INF建造一個excel-config檔案夾，在此檔案夾裡放入要導出資料的xls文檔。
xls文檔裡每一列的列名即是“#”+資料庫的字段名，例如：
2.建造幫助類
（1）。ChangeUtil
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.StringTokenizer;
import java.util.Vector;
public class ChangeUtil {
public static final String DATE_FORMAT_YMD = "yyyyMMdd";
public static final String DATE_FORMAT_YM = "yyyyMM";
public static final String DATE_FORMAT_Y = "yyyy";
public static final String DATE_FORMAT_YMD_HMS="yyyy-MM-dd HH:mm:ss";
private ChangeUtil() {
}
public static Vector changeStringToVector(String _str){
return changeStringToVector(_str, ",");
}
public static Vector changeStringToVector(String _str, String _token) {
if( _str== null) {
return null;
}
Vector<String> temp = new Vector<String>();
StringTokenizer st = new StringTokenizer(_str, _token);
while (st.hasMoreTokens()) {
temp.add(st.nextToken());
}
return temp;
}
public static String changeVectorToString(Vector _v) {
return changeVectorToString(_v, ",");
}
public static String changeVectorToString(Vector _v, String _token) {
if( _v == null) {
return null;
}
Enumeration enumeration = _v.elements();
String str = "";
while (enumeration.hasMoreElements()) {
str = str + (String) (enumeration.nextElement()) + _token;
}
str = str.substring(0, str.length() - 1);
return str;
}
public static String changeArrayToString(String[] _strArray) {
return changeArrayToString(_strArray, ",");
}
public static String changeArrayToString(String[] _strArray,String _token) {
if( _strArray == null) {
return null;
}
int size = _strArray.length;
if (size == 0) {
return null;
} else if (size == 1) {
return _strArray[0];
} else {
String temp = _strArray[0];
for (int i = 1; i < size; i++) {
temp = temp + _token + _strArray[i];
}
return temp;
}
}
public static String[] changeStringToArray(String _str) {
return changeStringToArray(_str, ",");
}
public static String[] changeStringToArray(String _str, String _token) {
if( _str ==null) {
return null;
}
Vector v = changeStringToVector(_str, _token);
String[] strArray = new String[v.size()];
int i = 0;
for (Enumeration em = v.elements(); em.hasMoreElements(); i++) {
strArray[i] = (String) em.nextElement();
}
return strArray;
}
public static int getAgeFromBirthday(java.util.Date _birthday,java.util.Date _fromDate) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_birthday);
int birthdayYear = calendar.get(Calendar.YEAR);
int birthdayMonth = calendar.get(Calendar.MONTH);
int birthdayDay = calendar.get(Calendar.DAY_OF_MONTH);
calendar.clear();
calendar.setTime(_fromDate);
int currentYear = calendar.get(Calendar.YEAR);
int currentMonth = calendar.get(Calendar.MONTH);
int currentDay = calendar.get(Calendar.DAY_OF_MONTH);
calendar.clear();
int age = currentYear - birthdayYear;
if (!((currentMonth >= birthdayMonth)&& (currentDay >= birthdayDay))) {
age--;
}
return age;
}
public static int getAgeFromBirthday(java.util.Date _birthday) {
return getAgeFromBirthday(_birthday,new java.util.Date(System.currentTimeMillis()));
}
public static int getAgeFromBirthday(java.sql.Timestamp _birthday) {
return getAgeFromBirthday(new java.util.Date(_birthday.getTime()),new java.util.Date(System.currentTimeMillis()));
}
public static String formatDate(java.util.Date _date) {
return formatDate(_date, DATE_FORMAT_YMD);
}
public static String formatDate(java.util.Date _date, String _pattern) {
if( _date == null) {
return null;
}
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(_pattern);
String stringDate = simpleDateFormat.format(_date);
return stringDate;
}
public static String simplefFormatChineseDate(java.util.Date _date) {
if( _date == null) {
return null;
}
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
StringBuffer sb = new StringBuffer();
sb.append(calendar.get(Calendar.YEAR))
.append("年")
.append(calendar.get(Calendar.MONTH) + 1)
.append("月")
.append(Calendar.DAY_OF_MONTH)
.append("日");
calendar.clear();
return sb.toString();
}
public static String complexFormatChineseDate(java.util.Date _date) {
if( _date == null) {
return null;
}
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
StringBuffer sb = new StringBuffer();
sb.append(calendar.get(Calendar.YEAR))
.append("年")
.append(calendar.get(Calendar.MONTH) + 1)
.append("月")
.append(Calendar.DAY_OF_MONTH)
.append("日")
.append(Calendar.HOUR_OF_DAY)
.append("時")
.append(Calendar.MINUTE)
.append("分")
.append(Calendar.SECOND)
.append("秒");
calendar.clear();
return sb.toString();
}
public static java.util.Date changeToDate(String _dateStr) throws IllegalArgumentException{
return changeToDate(_dateStr, DATE_FORMAT_YMD);
}
public static java.util.Date changeToDate(String _dateStr,String _pattern) throws IllegalArgumentException {
if (_dateStr == null || _dateStr.trim().equals("")) {
return null;
}
java.util.Date date = null;
SimpleDateFormat format = new SimpleDateFormat(_pattern);
try {
date = format.parse(_dateStr);
} catch (java.text.ParseException pe) {
throw new IllegalArgumentException("不能使用模式:[" + _pattern + "]格式化時間串:[" + _dateStr + "]");
}
return date;
}
public static java.sql.Date changeToDBDate(String _dateStr) throws IllegalArgumentException{
return changeForDBDate(changeToDate(_dateStr, DATE_FORMAT_YMD));
}
public static java.sql.Date changeToDBDate(String _dateStr,String _pattern) throws IllegalArgumentException {
return changeForDBDate(changeToDate(_dateStr, _pattern));
}
public static java.sql.Date changeForDBDate(java.util.Date _date) {
if (_date == null) {
return null;
}
return new java.sql.Date(_date.getTime());
}
public static java.util.Date changFromDBDate(java.sql.Date _date) {
return (java.util.Date) _date;
}
public static java.sql.Timestamp changeToTimestamp(java.util.Date _date) {
if (_date == null) {
return null;
}
return new java.sql.Timestamp(_date.getTime());
}
public static java.util.Date changeFromTimestamp(java.sql.Timestamp _date) {
return (java.util.Date) _date;
}
public static String changeToGB(String _str) throws Exception{
if( _str == null) {
return null;
}
String gbStr = null;
try {
gbStr = new String(_str.getBytes("ISO8859_1"), "GBK");
} catch (Exception e) {
throw e;
}
return gbStr;
}
public static String changeFromGB(String _str)throws Exception {
if( _str == null) {
return null;
}
String isoStr = null;
try {
isoStr = new String(_str.getBytes("GBK"), "ISO8859_1");
} catch (Exception e) {
throw e;
}
return isoStr;
}
public static int getYear(java.util.Date _date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
int year = calendar.get(Calendar.YEAR);
calendar.clear();
return year;
}
public static int getMonth(java.util.Date _date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
// 以0開始
int month = calendar.get(Calendar.MONTH);
calendar.clear();
return (month + 1);
}
public static int getDay(java.util.Date _date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
int day = calendar.get(Calendar.DAY_OF_MONTH);
calendar.clear();
return day;
}
public static int getHours(java.util.Date _date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
int value = calendar.get(Calendar.HOUR);
calendar.clear();
return value;
}
public static int getMinutes(java.util.Date _date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
int value = calendar.get(Calendar.MINUTE);
calendar.clear();
return value;
}
public static int getSeconds(java.util.Date _date) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_date);
int value = calendar.get(Calendar.SECOND);
calendar.clear();
return value;
}
public static int getDayCount(java.util.Date _startDate,java.util.Date _endDate) {
Calendar calendar = Calendar.getInstance();
calendar.setTime(_startDate);
int startDay = calendar.get(Calendar.DAY_OF_YEAR);
int startYear = calendar.get(Calendar.YEAR);
calendar.clear();
calendar.setTime(_endDate);
int endDay = calendar.get(Calendar.DAY_OF_YEAR);
int endYear = calendar.get(Calendar.YEAR);
calendar.clear();
return (endYear - startYear) * 365 + (endDay - startDay);
}
public static int getMonthAmount(java.sql.Date _startDate,java.sql.Date _endDate) {
int nYear = 0;
int nMonth = 0;
int nDay = 0;
int nMonthAmount = 0;
Calendar cldStart = Calendar.getInstance();
Calendar cldEnd = Calendar.getInstance();
cldStart.setTime(_startDate);
cldEnd.setTime(_endDate);
nYear = cldEnd.get(Calendar.YEAR) - cldStart.get(Calendar.YEAR);
nMonth = cldEnd.get(Calendar.MONTH) - cldStart.get(Calendar.MONTH);
nDay = cldEnd.get(Calendar.DATE) - cldStart.get(Calendar.DATE);
if (nDay > 14) {
nMonthAmount = nYear * 12 + nMonth + 1;
} else {
nMonthAmount = nYear * 12 + nMonth;
}
return nMonthAmount;
}
public static long toLong(Object _inStrObj) {
if (_inStrObj == null || _inStrObj.toString().trim().equals("")) {
return 0;
} else {
return Long.valueOf(_inStrObj.toString()).longValue();
}
}
public static int toInteger(Object _inStrObj) {
if (_inStrObj == null || _inStrObj.toString().trim().equals("")) {
return 0;
} else {
return new Integer(_inStrObj.toString()).intValue();
}
}
public static double toDouble(Object _inStrObj) {
if (_inStrObj == null || _inStrObj.toString().trim().equals("")) {
return 0;
} else {
return Double.valueOf(_inStrObj.toString()).doubleValue();
}
}
public static float toFloat(Object _inStrObj) {
if (_inStrObj == null || _inStrObj.toString().trim().equals("")) {
return 0;
} else {
return Float.valueOf(_inStrObj.toString()).floatValue();
}
}
public static String toStr(byte[] _bytes, String _encoding) throws IllegalArgumentException{
if( _bytes == null) {
return null;
}
String s = null;
try {
s = new String(_bytes, _encoding);
} catch (Exception e) {
throw new IllegalArgumentException("不支援的編碼方式:" + _encoding);
}
return s;
}
public static boolean toBoolean(Boolean _boolean) {
if (_boolean == null) {
return false;
} else {
return _boolean.booleanValue();
}
}
public static String toStr(Object _obj, String _replaceStr) {
if (_obj == null) {
return _replaceStr;
} else {
return _obj.toString();
}
}
public static String toStr(String _str, String _replaceStr) {
if (_str == null||_str.equals("null")) {
return _replaceStr;
} else {
return _str;
}
}
public static String toStr(String _str) {
return toStr(_str, "");
}
public static String toStr(Object _obj) {
if(_obj==null) {
return "";
}else{
return toStr(_obj.toString());
}
}
public static byte[] toBytes(String _str, String _encoding) throws IllegalArgumentException{
if( _str == null) {
return null;
}
byte[] b = null;
try {
b = _str.getBytes(_encoding);
} catch (Exception e) {
throw new IllegalArgumentException("不支援的編碼方式:" + _encoding);
}
return b;
}
public static String toChinese(double _dMoney) {
String[] strArr = { "零", "壹", "貳", "叁", "肆", "伍", "陸", "柒", "捌", "玖" };
String[] strArr1 = { "分", "角", "圓", "拾", "佰", "仟", "萬", "拾", "佰", "仟" };
String[] strArr2 = new String[10];
String sRtn = "";
int iTmp;
double dTmp;
try {
_dMoney += 0.001;
if ((_dMoney >= 100000000) || (_dMoney < 0.01)) {
sRtn = "";
} else {
for (int i = 0; i < 10; i++) {
dTmp = _dMoney / Math.pow(10, 7 - i);
iTmp = (new Double(dTmp)).intValue();
_dMoney -= iTmp * Math.pow(10, 7 - i);
if (iTmp != 0) {
strArr2[i] = strArr[iTmp] + strArr1[9 - i];
} else {
strArr2[i] = "";
}
}
boolean bFlag = false;
for (int i = 0; i < 10; i++) {
if (!"".equals(strArr2[i])) {
sRtn += strArr2[i];
bFlag = true;
} else {
if (i == 3) {
sRtn += "萬";
bFlag = true;
} else if (i == 7) {
sRtn += "圓";
bFlag = true;
} else if (bFlag) {
sRtn += "零";
bFlag = false;
}
}
}
if (sRtn.startsWith("萬")) {
sRtn = sRtn.substring(1, sRtn.length());
}
if (sRtn.startsWith("圓")) {
sRtn = sRtn.substring(1, sRtn.length());
}
while (sRtn.startsWith("零")) {
sRtn = sRtn.substring(1, sRtn.length());
}
if (sRtn.lastIndexOf("零") == (sRtn.length() - 1)) {
sRtn = sRtn.substring(0, sRtn.length() - 1);
}
if (sRtn.startsWith("圓")) {
sRtn = sRtn.substring(1, sRtn.length());
}
iTmp = sRtn.indexOf("圓");
if (iTmp != -1) {
if ("零".equals(sRtn.substring(iTmp - 1, iTmp))) {
sRtn =
sRtn.substring(0, iTmp - 1)
+ sRtn.substring(iTmp, sRtn.length());
}
}
iTmp = sRtn.indexOf("萬");
if (iTmp != -1) {
if ("零".equals(sRtn.substring(iTmp - 1, iTmp))) {
sRtn =
sRtn.substring(0, iTmp - 1)
+ sRtn.substring(iTmp, sRtn.length());
}
}
while (sRtn.startsWith("零")) {
sRtn = sRtn.substring(1, sRtn.length());
}
sRtn += "整";
}
} catch (Exception ex) {
}
return sRtn;
}
public static BigDecimal toBigDecimal(String _str) {
BigDecimal bd = null;
if (_str != null) {
try {
bd = new BigDecimal(_str);
} catch (Exception e) {
return null;
}
}
return bd;
}
public static Timestamp toTimestamp(String _sDate) {
Timestamp ts = null;
if (_sDate == null || "".equals(_sDate)) {
return null;
}
ts = Timestamp.valueOf(_sDate + " 00:00:00.000000000");
return ts;
}
public static String changeHtmlStr(String _rawStr) {
String str = null;
if (_rawStr != null) {
str = replaceString( " ", " ", _rawStr);
str = replaceString( "<","<", str);
str = replaceString( ">",">", str);
str = replaceString( "&","&", str);
str = replaceString( ""","\"", str);
str = replaceString( "<br>", "\r\n",str);
}
return str;
}
public static String replaceString(String _oldStr,String _newStr,String _wholeStr) {
if( _wholeStr == null){
return null;
}
if( _newStr == null) {
return _wholeStr;
}
int start=0, end=0;
StringBuffer result=new StringBuffer();
result=result.append(_wholeStr);
while ( result.indexOf(_oldStr, start)>-1) {
start=result.indexOf(_oldStr, start);
end=start+_oldStr.length();
result.replace(start,end,_newStr);
start += _newStr.length();
}
return result.toString();
}
public static String replaceFirstString(String _oldStr,String _newStr,String _wholeStr, boolean _reverse) {
if( _wholeStr == null){
return null;
}
if( _newStr == null) {
return _wholeStr;
}
StringBuffer result=new StringBuffer(_wholeStr);
int start=0, end=0;
if(!_reverse) {
if (result.indexOf(_oldStr)>-1) {
start=result.indexOf(_oldStr);
end=start+_oldStr.length();
result.replace(start,end,_newStr);
}
}else{
if (result.lastIndexOf(_oldStr)>-1) {
start=result.lastIndexOf(_oldStr);
end=start+_oldStr.length();
result.replace(start,end,_newStr);
}
}
return result.toString();
}
public static String changeToHTMLStr(String _sourceStr) {
if (_sourceStr == null) {
return null;
}
StringBuffer buff = new StringBuffer(1024);
int n = _sourceStr.length();
char c;
for (int i = 0; i < n; i++) {
c = _sourceStr.charAt(i);
if (c == '"') {
buff.append('\\');
buff.append(c);
} else if (c == '\\') {
buff.append('\\');
buff.append(c);
} else if (c == '\r') {
buff.append("\\r");
} else if (c == '\n') {
buff.append("\\n");
} else {
buff.append(c);
}
}
return buff.toString();
}
public static float roundFloat(float _value, int _len) throws IllegalArgumentException{
int iLen = _len;
checkParamPositive("_len", _len);
float d = (float) Math.pow(10, iLen);
float fValue = _value * d;
return Math.round(fValue) / d;
}
public static String formatFloat(float _value, int _len) throws IllegalArgumentException{
String fStr = String.valueOf(roundFloat(_value, _len));
StringBuffer sb = new StringBuffer(fStr);
int leftBit = fStr.length() - fStr.indexOf(".") - 1;
if (leftBit < _len) {
for (int i = 0; i < (_len - leftBit); i++) {
sb.append("0");
}
}
return sb.toString();
}
public static double roundDouble(double _value, int _len) throws IllegalArgumentException {
int iLen = _len;
checkParamPositive("_len", _len);
double d = Math.pow(10, iLen);
double dValue = _value * d;
return Math.round(dValue) / d;
}
public static String formatDouble(double _value, int _len) throws IllegalArgumentException{
String fStr = String.valueOf(roundDouble(_value, _len));
StringBuffer sb = new StringBuffer(fStr);
int leftBit = fStr.length() - fStr.indexOf(".") - 1;
if (leftBit < _len) {
for (int i = 0; i < (_len - leftBit); i++) {
sb.append("0");
}
}
return sb.toString();
}
public static String leftString(String _str, int _len) {
if (_str == null) {
return null;
}
if (_len < 0) {
return "";
}
if (_str.length() <= _len) {
return _str;
} else {
return _str.substring(0, _len);
}
}
public static String rightString(String _str, int _len) {
if (_str == null) {
return null;
}
if (_len < 0) {
return "";
}
if (_str.length() <= _len) {
return _str;
} else {
return _str.substring(_str.length() - _len);
}
}
public static String rightPad(String _str, int _size, char _padChar) {
if (_str == null) {
return null;
}
int pads = _size - _str.length();
if (pads <= 0) {
return _str; // returns original String when possible
}
return _str.concat(padding(pads, _padChar));
}
public static String leftPad(String _str, int _size, char _padChar) {
if (_str == null) {
return null;
}
int pads = _size - _str.length();
if (pads <= 0) {
return _str; // returns original String when possible
}
return padding(pads, _padChar).concat(_str);
}
private static String padding(int _repeat, char _padChar) {
String value = "";
String padStr = String.valueOf(_padChar);
if(_repeat>0) {
for(int i = 0;i<_repeat;i++) {
value = value.concat(padStr);
}
}
return value;
}
private static void checkParamPositive(String _str, int _value) throws IllegalArgumentException {
if (_value <= 0) {
throw new IllegalArgumentException("參數:" + _str + "不能小于等于0");
}
}
}
（2）。ExcelTemplate
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.eos.data.xpath.XPathLocator;
import com.eos.foundation.data.DataObjectUtil;
import com.eos.foundation.database.DatabaseUtil;
import com.primeton.data.sdo.impl.PropertyImpl;
import com.primeton.data.sdo.impl.TypeReference;
import com.primeton.data.sdo.impl.types.BooleanType;
import com.primeton.data.sdo.impl.types.DateTimeType;
import com.primeton.data.sdo.impl.types.DateType;
import com.primeton.data.sdo.impl.types.DecimalType;
import com.primeton.data.sdo.impl.types.FloatType;
import com.primeton.data.sdo.impl.types.IntType;
import com.primeton.data.sdo.impl.types.IntegerType;
import com.primeton.data.sdo.impl.types.LongType;
import commonj.sdo.DataObject;
import commonj.sdo.Type;
public class ExcelTemplate {
private String templateFile;
private String outputFile;
private String[] fieldNames;
private int startRow=-1;
private int tempStartRowNum=-1;
private int fontSize=10;
private String fontName="宋體";
private boolean titleCellBold=false;
private boolean blankCellBold=false;
private boolean autoSheet=false;
private boolean autoPagination=false;
private int maxrow=-1;
private boolean hasFormula=false;
private final String TITLE_FLAG="&";
private final String CONTENT_FLAG="#";
private final String FORMULA_FLAG="formula";
private final String UNLIMIT_FLAG="~";
private final String FIELD_AUTO_ID="_id";
private final String[] OP_FLAG=new String[]{"+","-","*","/","%",":"};
public ExcelTemplate(){
}
public ExcelTemplate(String templateFile,String outputFile){
this.templateFile=templateFile;
this.outputFile=outputFile;
}
public void setIncludeFormula(boolean hasFormula){
this.hasFormula=hasFormula;
}
public void setTitleCellBold(boolean titleCellBold){
this.titleCellBold=titleCellBold;
}
public void setBlankCellBold(boolean blankCellBold){
this.blankCellBold=blankCellBold;
}
public void setAutoSheet(boolean autoSheet){
this.autoSheet=autoSheet;
this.autoPagination=(autoSheet?false:autoPagination);
}
public void setAutoPagination(boolean autoPagination){
this.autoPagination=autoPagination;
this.autoSheet=(autoPagination?false:autoSheet);
}
public void setMaxRow(int maxrow){
this.maxrow=maxrow;
}
public void setFontSize(int size){
this.fontSize=size;
}
public void setFontName(String fontName){
this.fontName=fontName;
}
private void initialize(HSSFSheet sheet){
boolean setStart=false;
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 0; r < rows; r++){
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
for(short c = 0; c < cells; c++){
HSSFCell cell = row.getCell(c);
if(cell!=null)
{
String value=null;
if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
value=""+cell.getNumericCellValue();
}else if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
value=""+cell.getBooleanCellValue();
}else{
value=cell.getRichStringCellValue().getString();
}
if(value!=null&&!"".equals(value))
{
value=value.trim();
//内容資料
if(value.startsWith(CONTENT_FLAG)){
if(!setStart){
this.startRow=r;//設定内容填充起始行
this.fieldNames=new String[cells];
setStart=true;
}
this.fieldNames[c]=value.substring(1);//初始化内容字段
}
}
}
}
}
}
}
private void calcFormula(HSSFWorkbook wb,HSSFSheet sheet){
this.calcFormula(wb,sheet,0,sheet.getPhysicalNumberOfRows());
}
private void calcFormula(HSSFWorkbook wb,HSSFSheet sheet,int start_rang,int end_rang){
//int rows = sheet.getPhysicalNumberOfRows();
HSSFCellStyle borderStyle=this.getBorderStyle(wb);
HSSFCellStyle noneStyle=this.getNoneStyle(wb);
for (int r = start_rang; r < end_rang; r++){
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
for(short c = 0; c < cells; c++){
HSSFCell cell = row.getCell(c);
if(cell!=null){
if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
String value=cell.getRichStringCellValue().getString();
if(value!=null){
value=value.trim().toLowerCase();
if(value.startsWith(FORMULA_FLAG))
{
int index=value.indexOf("=");
String formula=value.substring(index+1);
//判斷函數是否包含以#開頭,如果是以#開頭表示必須顯示邊框，
String flag=formula.substring(0,1);
boolean showBold=false;
if(flag.equals(CONTENT_FLAG)){
formula=formula.substring(1);
showBold=true;
}
//如果包含':'符号則統計公式不包含目前行,否則會引發公式循環引用錯誤.
if(formula.indexOf(":")!=-1){
formula=formula.replaceAll(UNLIMIT_FLAG,r+"").toUpperCase();
}else{
formula=formula.replaceAll(UNLIMIT_FLAG,(r+1)+"").toUpperCase();
}
//判斷公式對應的Cell内容是否為blank,
//如果公式對應的CELL内容為空，則設定為""
int rightIndex=formula.indexOf(")");
int leftIndex=formula.indexOf("(");
String content=formula.substring(leftIndex+1,rightIndex);
int opIndex=this.getOpIndex(content);
String startPos=content.substring(0,opIndex);
String endPos=content.substring(opIndex+1);
int start_col=this.getColumnIndex(startPos.charAt(0));
int start_row=Integer.parseInt(startPos.substring(1));
int end_col=this.getColumnIndex(endPos.charAt(0));
int end_row=Integer.parseInt(endPos.substring(1));
HSSFCell startC=sheet.getRow(start_row-1).getCell((short)start_col);
HSSFCell endC=sheet.getRow(end_row-1).getCell((short)end_col);
//判斷公式開始Cell與結束cell内容是否無效
//當為均為無效的cell值，并且目前公式不包含":"，則設定公式框内容為""，
//包含":" 則設定為計算公式
if(invalidCellValue(startC)&&invalidCellValue(endC)){
if(formula.indexOf(":")==-1){
cell.setCellValue( new HSSFRichTextString(""));
}else{
cell=row.createCell((short)c);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(formula);
}
}else{
//重建Cell
cell=row.createCell((short)c);
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(formula);
}
if(showBold){
cell.setCellStyle(borderStyle);
}else{
cell.setCellStyle(noneStyle);
}
}
}
}
}
}
}
}
}
private void setFormulaBlankCell(HSSFCell cell,int startRowNum){
if (cell != null) {
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
String value = cell.getRichStringCellValue().getString();
if (value != null) {
value = value.trim().toLowerCase();
if (value.startsWith(FORMULA_FLAG)) {
int index = value.indexOf("=");
String formula = value.substring(index + 1);
String flag = formula.substring(0, 1);
if (flag.equals(CONTENT_FLAG))formula = formula.substring(1);
if (formula.indexOf(":") != -1) {
int rightIndex = formula.indexOf(")");
int leftIndex = formula.indexOf("(");
String content = formula.substring(leftIndex + 1,rightIndex).toUpperCase();
int opIndex = this.getOpIndex(content);
String startPos = content.substring(0, opIndex);
String colValue = startPos.substring(1,opIndex);
if(Integer.parseInt(colValue)-1==startRowNum)
cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
}
}
}
}
}
}
private void generateTitleDatas(DataObject exportInfo,HSSFWorkbook wb,HSSFSheet sheet)throws Exception{
int rows = sheet.getPhysicalNumberOfRows();
HSSFCellStyle borderStyle=this.getBorderStyle(wb);
HSSFCellStyle noneStyle=this.getNoneStyle(wb);
for (int r = 0; r < rows; r++){
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells =row.getPhysicalNumberOfCells();
for(short c = 0; c < cells; c++){
HSSFCell cell = row.getCell(c);
if(cell!=null){
if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
String value=cell.getRichStringCellValue().getString();
if(value!=null){
value=value.trim();
if(value.startsWith(TITLE_FLAG)){
value=value.substring(1);
//擷取對應的值，支援XPATH取值
Object obj=XPathLocator.newInstance().getValue(exportInfo, value);
String content=obj+"";
//String content=exportInfo.getString(value);
if(content==null)content="";
//重建Cell，填充标題值
cell=row.createCell((short)c);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue( new HSSFRichTextString(content));
if(!titleCellBold){
cell.setCellStyle(noneStyle);
}else{
cell.setCellStyle(borderStyle);
}
}
}
}
}
}
}
}
}
private void generateContentDatas(List<DataObject> resultset,HSSFWorkbook wb,HSSFSheet sheet){
HSSFCellStyle borderStyle=this.getBorderStyle(wb);
HSSFCellStyle noneStyle=this.getNoneStyle(wb);
//預設行号
int autoRowId=1;
for(Iterator it=resultset.iterator();it.hasNext();autoRowId++){
DataObject content=(DataObject)it.next();
HSSFRow sourceRow=sheet.getRow(startRow);
HSSFRow row=sheet.createRow(startRow++);
for(int i=0;i<sourceRow.getPhysicalNumberOfCells();i++){
//輸出自動生成的行号
if(fieldNames[i]!=null&&fieldNames[i].equals(FIELD_AUTO_ID)){
HSSFCell cell=row.createCell((short)i);
cell.setCellStyle(borderStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(autoRowId);
continue;
}
if(fieldNames[i]!=null){
HSSFCell cell=row.createCell((short)i);
cell.setCellStyle(borderStyle);
if(content!=null){
//字段名支援xpath取值
Object value=XPathLocator.newInstance().getValue(content, fieldNames[i]);
//Object value=content.get(fieldNames[i]);
if(value!=null){
if(value instanceof Double|| value instanceof BigDecimal){
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.toString()));
}else{
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(new HSSFRichTextString(value.toString()));
}
}else{
cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
}
}else{
cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
if(!blankCellBold){
cell.setCellStyle(noneStyle);
}else{
cell.setCellStyle(borderStyle);
}
}
}else{
HSSFCell sourceCell=sourceRow.getCell((short)i);
if(sourceCell!=null&&
sourceCell.getCellType()==HSSFCell.CELL_TYPE_STRING&&
sourceCell.getRichStringCellValue().getString()!=null&&
sourceCell.getRichStringCellValue().getString().toLowerCase().startsWith(FORMULA_FLAG)){
HSSFCell cell=row.createCell((short)i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(sourceCell.getRichStringCellValue());
}
}
}
if(it.hasNext()){
//向下平推一行
//sheet.shiftRows(startRow-1,sheet.getLastRowNum(),1);
shiftDown(sheet,startRow-1, sheet.getLastRowNum(), 1);
}
}
}
public void generate(List<DataObject> resultset)throws Exception{
this.generate(resultset,null);
}
public void generate(List<DataObject> resultset,DataObject exportInfo)throws Exception{
POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream(templateFile));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
initialize(sheet);
if(startRow==-1)
return ;
if(this.autoPagination){
this.generatePagination(wb,sheet,resultset,exportInfo);
}
else if(this.autoSheet){
generatePaginationSheet(wb,sheet,resultset,exportInfo);
}
else{
//先填充标題
if(exportInfo!=null)
this.generateTitleDatas(exportInfo,wb,sheet);
//生成資料内容
this.generateContentDatas(resultset,wb,sheet);
if(hasFormula){
this.calcFormula(wb,sheet);
}
}
FileOutputStream fileOut = new FileOutputStream(outputFile);
wb.write(fileOut);
fileOut.close();
}
private void generatePagination(HSSFWorkbook wb,HSSFSheet sourceSheet,List<DataObject> resultset,DataObject exportInfo)
throws Exception{
int startPosition=startRow;
tempStartRowNum=startRow;
int count=resultset.size() / maxrow;
int num=resultset.size() % maxrow;
int rows=sourceSheet.getPhysicalNumberOfRows();
System.out.println("rows="+rows);
if(num>0){
count=count+1;
num=maxrow-num;
//不足指定的maxrow，添加空行
for(int i=0;i<num;i++){
resultset.add(null);
}
}
//删除最後一行的分頁符
try{
sourceSheet.removeRowBreak(rows-1);
}catch(NullPointerException npe){
throw new Exception("指定的EXCEL模版檔案["+this.templateFile+"] 未插入分頁符");
}
//超過1頁則插入分頁符
for(int i=1;i<count;i++){
//設定分頁符
sourceSheet.setRowBreak(i*rows-1);
this.copyRows(sourceSheet,sourceSheet,0,rows,i*rows+1);
}
if(exportInfo!=null)
this.generateTitleDatas(exportInfo,wb,sourceSheet);
int current_page=0;
while(current_page<count){
List<DataObject> newList=resultset.subList(current_page*maxrow,maxrow*(current_page+1));
this.generateContentDatas(newList,wb,sourceSheet);
current_page++;
//計算下一行的資料填充起始位置
startRow=current_page*rows+maxrow+startPosition;
}
if(hasFormula)
this.calcFormula(wb,sourceSheet);
}
private void generatePaginationSheet(HSSFWorkbook wb,HSSFSheet sourceSheet,List<DataObject> resultset,DataObject exportInfo)
throws Exception{
int startPosition=startRow;
int count=resultset.size() / maxrow;
int num=resultset.size() % maxrow;
if(num>0){
count=count+1;
num=maxrow-num;
//不足指定的maxrow，添加空行
for(int i=0;i<num;i++){
resultset.add(null);
}
}
for(int i=1;i<count;i++){
HSSFSheet newsheet=wb.createSheet("Page "+i);
this.copyRows(sourceSheet,newsheet,0,sourceSheet.getLastRowNum(),0);
}
if(count>1){
for(int i=0;i<wb.getNumberOfSheets();i++){
startRow=startPosition;
List<DataObject> newList=resultset.subList(i*maxrow,maxrow*(i+1));
HSSFSheet sheet=wb.getSheetAt(i);
//先填充标題
if(exportInfo!=null)
this.generateTitleDatas(exportInfo,wb,sheet);
this.generateContentDatas(newList,wb,sheet);
if(hasFormula)
this.calcFormula(wb,sheet);
}
}else{
HSSFSheet sheet=wb.getSheetAt(0);
if(exportInfo!=null)
this.generateTitleDatas(exportInfo,wb,sheet);
this.generateContentDatas(resultset,wb,sheet);
if(hasFormula)
this.calcFormula(wb,sheet);
}
}
private HSSFCellStyle getBorderStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font=wb.createFont();
font.setFontHeightInPoints((short)fontSize);
font.setFontName(fontName);
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
return style;
}
private HSSFCellStyle getNoneStyle(HSSFWorkbook wb){
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font=wb.createFont();
font.setFontHeightInPoints((short)fontSize);
font.setFontName(fontName);
style.setFont(font);
style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
style.setBorderRight(HSSFCellStyle.BORDER_NONE);
style.setBorderTop(HSSFCellStyle.BORDER_NONE);
return style;
}
private void shiftDown(HSSFSheet sheet,int thisrow, int lastrow, int shiftcount) {
sheet.shiftRows(thisrow, lastrow, shiftcount);
for (int z = 0; z < shiftcount; z++) {
HSSFRow row = sheet.getRow(thisrow);
HSSFRow oldrow = sheet.getRow(thisrow + shiftcount);
//将各行的行高複制
oldrow.setHeight(row.getHeight());
//将各個單元格的格式複制
for (short i = 0; i <= oldrow.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = row.createCell(i);
HSSFCell oldcell = oldrow.getCell(i);
if (oldcell != null) {
switch(oldcell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(oldcell.getRichStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(oldcell.getNumericCellValue());
break;
default:
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(oldcell.getRichStringCellValue());
}
cell.setCellStyle(oldcell.getCellStyle());
}
}
//将有列跨越的複制
Vector regs = findRegion(sheet,oldrow);
if (regs.size() != 0) {
for (int i = 0; i < regs.size(); i++) {
Region reg = (Region) regs.get(i);
reg.setRowFrom(row.getRowNum());
reg.setRowTo(row.getRowNum());
sheet.addMergedRegion(reg);
}
}
thisrow++;
}
}
private Vector findRegion(HSSFSheet sheet ,HSSFRow oldrow) {
Vector<Region> regs = new Vector<Region>();
int num = sheet.getNumMergedRegions();
int curRowid = oldrow.getRowNum();
for (int i = 0; i < num; i++) {
Region reg = sheet.getMergedRegionAt(i);
if (reg.getRowFrom() == reg.getRowTo() && reg.getRowFrom() == curRowid) {
regs.add(reg);
}
}
return regs;
}
private void copyRows(HSSFSheet sourceSheet, HSSFSheet targetSheet,int pStartRow, int pEndRow, int pPosition) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
Region region = null;
int cType;
int i;
short j;
int targetRowFrom;
int targetRowTo;
if ((pStartRow == -1) || (pEndRow == -1)) {
return;
}
// 拷貝合并的單元格
for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
region = sourceSheet.getMergedRegionAt(i);
if ((region.getRowFrom() >= pStartRow)&& (region.getRowTo() <= pEndRow)) {
targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
targetRowTo = region.getRowTo() - pStartRow + pPosition;
region.setRowFrom(targetRowFrom);
region.setRowTo(targetRowTo);
targetSheet.addMergedRegion(region);
}
}
// 設定列寬
for (i = pStartRow; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow != null) {
for (j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++) {
targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
}
break;
}
}
// 拷貝行并填充資料
for (; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
for (j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++) {
sourceCell = sourceRow.getCell(j);
if (sourceCell == null) {
continue;
}
targetCell = targetRow.createCell(j);
targetCell.setCellStyle(sourceCell.getCellStyle());
cType = sourceCell.getCellType();
targetCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
break;
}
if(this.autoPagination){
this.setFormulaBlankCell(sourceCell,tempStartRowNum);
}
}
}
}
private String parseFormula(String pPOIFormula) {
final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
StringBuffer result = null;
int index;
result = new StringBuffer();
index = pPOIFormula.indexOf(cstReplaceString);
if (index >= 0) {
result.append(pPOIFormula.substring(0, index));
result.append(pPOIFormula.substring(index+ cstReplaceString.length()));
} else {
result.append(pPOIFormula);
}
return result.toString();
}
private int getColumnIndex(char c){
int i=c;
return i-65;
}
private int getOpIndex(String s){
for(int i=0;i<OP_FLAG.length;i++){
int index=s.indexOf(OP_FLAG[i]);
if(index!=-1){
return index;
}
}
return -1;
}
private boolean invalidCellValue(HSSFCell cell){
if(cell.getCellType()==HSSFCell.CELL_TYPE_BLANK){
return true;
}
else if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
if(cell.getRichStringCellValue().getString()==null||cell.getRichStringCellValue().getString().equals("")){
return true;
}
}
else if(cell.getCellType()==HSSFCell.CELL_TYPE_ERROR){
return true;
}
return false;
}
public int importData(String targetFile,String entityName,int submitCount)throws Exception{
POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream(targetFile));
HSSFWorkbook wb = new HSSFWorkbook(fs);
for(int sheetCount=0;sheetCount<wb.getNumberOfSheets();sheetCount++){
HSSFSheet sheet = wb.getSheetAt(sheetCount);
int rows = sheet.getPhysicalNumberOfRows();
initialize(sheet);
if(startRow==-1)
continue;
List<DataObject> dataObjects=new ArrayList<DataObject>();
//第一行為#字段名
//第二行為字段标題，是以内容讀取從startRow+2
for(int rowCount=startRow+2;rowCount<rows;rowCount++){
HSSFRow sourceRow=sheet.getRow(rowCount);
DataObject importEntity=DataObjectUtil.createDataObject(entityName);
//判斷某一行是否允許插入，當該行的所有列cell均為BLANK時不插入資料庫
boolean allowInsert=false;
//以下構造導入的實體對象，并根據Excel單元格的内容填充實體屬性值
for(int cellCount=0;cellCount<fieldNames.length;cellCount++){
String propertyName=fieldNames[cellCount];
HSSFCell cell=sourceRow.getCell((short)cellCount);
if(cell.getCellType()==HSSFCell.CELL_TYPE_BLANK)
continue;
allowInsert=true;
String value=null;
if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
if (HSSFDateUtil.isCellDateFormatted(cell)){
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
value= dateFormat.format((cell.getDateCellValue()));
}else{
value=String.valueOf((long) cell.getNumericCellValue());
}
}else if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
value=cell.getBooleanCellValue()+"";
}else{
value=cell.getRichStringCellValue().getString();
}
TypeReference typeReference=(TypeReference)importEntity.getType().getProperty(propertyName).getType();
Type propertyType=typeReference.getActualType();
if(propertyType instanceof IntType||propertyType instanceof IntegerType){
//防止可能出現的Excel表格讀取自動加.号
if(value.indexOf(".")!=-1)
value=value.substring(0,value.indexOf("."));
importEntity.set(fieldNames[cellCount],ChangeUtil.toInteger(value));
}
else if(propertyType instanceof BooleanType){
importEntity.set(fieldNames[cellCount],ChangeUtil.toBoolean(Boolean.valueOf(value)));
}
else if(propertyType instanceof FloatType){
importEntity.set(fieldNames[cellCount],ChangeUtil.toFloat(value));
}
else if(propertyType instanceof LongType){
if(value.indexOf(".")!=-1)
value=value.substring(0,value.indexOf("."));
importEntity.set(fieldNames[cellCount],ChangeUtil.toLong(value));
}
else if(propertyType instanceof DecimalType){
importEntity.set(fieldNames[cellCount],ChangeUtil.toBigDecimal(value));
}
else if(propertyType instanceof DateType){
importEntity.set(fieldNames[cellCount],ChangeUtil.changeToDBDate(value));
}
else if(propertyType instanceof DateTimeType){
importEntity.set(fieldNames[cellCount],ChangeUtil.toTimestamp(value));
}
else{
importEntity.set(fieldNames[cellCount], value);
}
}
if(dataObjects.size()<submitCount){
if(allowInsert)
dataObjects.add(importEntity);
}else{
if(dataObjects.size()>0){
DatabaseUtil.insertEntityBatch("default", dataObjects.toArray(new DataObject[dataObjects.size()]));
dataObjects.clear();
}
}
if(rowCount==rows-1){
if(dataObjects.size()>0)
DatabaseUtil.insertEntityBatch("default", dataObjects.toArray(new DataObject[dataObjects.size()]));
}
}
}
return 1;
}
protected boolean isExistTemplate(String templateFile)throws IOException{
File file=new File(templateFile);
return file.exists();
}
public void prepareInitializeTemplate(String templateFile,DataObject dataObject) throws Exception{
HSSFWorkbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream(templateFile);
HSSFSheet sheet = wb.createSheet("new sheet");
//設定模闆的第一行為輸出字段定義列
HSSFRow row = sheet.createRow((short)0);
Object[] properties=dataObject.getType().getDeclaredProperties().toArray();
for(int i=0;i<properties.length;i++){
PropertyImpl property=(PropertyImpl)properties[i];
HSSFCell cell = row.createCell((short)i);
HSSFRichTextString text=new HSSFRichTextString("#"+property.getName());
cell.setCellValue(text);
}
wb.write(fileOut);
fileOut.close();
}
}
（3）。ExcelUtil
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import com.eos.foundation.common.io.FileUtil;
import com.eos.foundation.eoscommon.ConfigurationUtil;
import com.eos.runtime.core.ApplicationContext;
import com.eos.system.annotation.Bizlet;
import com.eos.system.annotation.BizletParam;
import com.eos.system.annotation.ParamType;
import commonj.sdo.DataObject;
@Bizlet("Excel工具操作類")
public class ExcelUtil {
private ExcelUtil(){
//工具類不允許執行個體化
}
@Bizlet(
value="将Excel資料導入到資料庫指定的表",
params = {
@BizletParam(index = 0, paramAlias = "excelFile",type=ParamType.CONSTANT),
@BizletParam(index = 1, paramAlias = "entityFullName",type=ParamType.CONSTANT)
}
)
public static int importExcel(String excelFile,String entityFullName)throws Exception{
ExcelTemplate template=new ExcelTemplate();
return template.importData(excelFile, entityFullName, 500);
}
@Bizlet(
value="将指定的對象數組導出到指定模闆的Excel檔案",
params = {
@BizletParam(index = 0, paramAlias = "exportObjects",type=ParamType.VARIABLE),
@BizletParam(index = 1, paramAlias = "exportInfo",type=ParamType.VARIABLE),
@BizletParam(index = 2, paramAlias = "templateFilename",type=ParamType.CONSTANT)
}
)
public static String exportExcel(DataObject[] exportObjects,DataObject exportInfo,String templateFilename) throws Exception{
return exportExcel(exportObjects,exportInfo,templateFilename,false,false);
}
@Bizlet(
value="分頁将對象數組導出到指定的模闆Excel檔案",
params = {
@BizletParam(index = 0, paramAlias = "exportObjects",type=ParamType.VARIABLE),
@BizletParam(index = 1, paramAlias = "exportInfo",type=ParamType.VARIABLE),
@BizletParam(index = 2, paramAlias = "templateFilename",type=ParamType.CONSTANT)
}
)
public static String exportExcelWithPagnation(DataObject[] exportObjects,DataObject exportInfo,String templateFilename)
throws Exception{
return exportExcel(exportObjects,exportInfo,templateFilename,true,false);
}
@Bizlet(
value="分工作表将對象數組導出到指定的模闆Excel檔案",
params = {
@BizletParam(index = 0, paramAlias = "exportObjects",type=ParamType.VARIABLE),
@BizletParam(index = 1, paramAlias = "exportInfo",type=ParamType.VARIABLE),
@BizletParam(index = 2, paramAlias = "templateFilename",type=ParamType.CONSTANT)
}
)
public static String exportExcelWithSheet(DataObject[] exportObjects,DataObject exportInfo,String templateFilename)
throws Exception{
return exportExcel(exportObjects,exportInfo,templateFilename,false,true);
}
private static String exportExcel(DataObject[] exportObjects,DataObject exportInfo,String templateFilename,
boolean autoPagination,boolean autoSheet) throws Exception{
String filename=templateFilename;
if(filename.indexOf(".xls")==-1){
filename+=".xls";
}
//臨時路徑是伺服器目前war下面的excel-config目錄
String templateDir=ApplicationContext.getInstance().getWarRealPath()+ConfigurationUtil.getContributionConfig
(UtilConfiguration.CONTRIBUTION_ABFRAME_UTILS,
UtilConfiguration.MODULE_ABFRAME,
UtilConfiguration.GROUP_EXCEL,
UtilConfiguration.EXCEL_TEMPLATE_PATH);
String excelExportMaxnum=ConfigurationUtil.getContributionConfig(UtilConfiguration.CONTRIBUTION_ABFRAME_UTILS,
UtilConfiguration.MODULE_ABFRAME,
UtilConfiguration.GROUP_EXCEL,
UtilConfiguration.EXCEL_EXPORT_MAXNUM);
if(!templateDir.endsWith("/")){
templateDir+="/";
}
String tempDir=templateDir+"temp/";
File file=new File(tempDir);
if(!file.exists()){
//建立臨時目錄
FileUtil.mkDir(tempDir);
//file.createNewFile();
}
String templateFile=templateDir+filename;
String outputFile=tempDir+generateOutputExcelFile(filename);
ExcelTemplate template=new ExcelTemplate(templateFile,outputFile);
template.setAutoPagination(autoPagination);
template.setAutoSheet(autoSheet);
int excelExportMaxnumInt = 0;
try{
excelExportMaxnumInt = Integer.parseInt(excelExportMaxnum);
}catch (Exception e){
e.printStackTrace();
}
template.setMaxRow(excelExportMaxnumInt);
template.generate(Arrays.asList(exportObjects),exportInfo);
return outputFile;
}
private static String generateOutputExcelFile(String templateFilename){
String filename=templateFilename;
System.out.println("filename"+filename);
if(templateFilename.endsWith(".xls")){
filename=templateFilename.substring(0,templateFilename.length()-4);
}
SimpleDateFormat format=new SimpleDateFormat("yyyyMMddHHmmss");
String datetimeString=format.format(new Date());
filename=filename+"_"+datetimeString+".xls";
return filename;
}
}
（4）。UtilConfiguration
public interface UtilConfiguration {
public static final String CONTRIBUTION_ABFRAME_UTILS="com.primeton.example.excel";
public static final String MODULE_ABFRAME="example-config";
public static final String GROUP_EXCEL="excel-config";
public static final String EXCEL_TEMPLATE_PATH="excel_template_path";
public static final String EXCEL_EXPORT_MAXNUM="excel_export_maxnum";
}
3.建造兩個jsp頁面
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" session="false" %>
<%@include file="/common/common.jsp"%>
<%@include file="/common/skins/skin0/component.jsp"%>
<h:css href="/css/style1/style-custom.css" target="_blank" rel="external nofollow" />
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<!--
- Author(s): Administrator
- Date: 2017-09-25 11:04:01
- Description:
-->
<head>
<title>導出</title>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<script src="<%= request.getContextPath() %>/common/nui/nui.js" type="text/javascript"></script>
</head>
<body>
<h:form action = "com.primeton.example.excel.exportFood.flow" id="queryForm">
<!--頁面流action中的query-->
<input type="hidden" name="_eosFlowAction" value="query"/>
<input type="hidden" name="criteria/_entity" value="com.primeton.example.excel.sampledataset.ToFood">
<w:panel id="panel1" width="100%" title="查詢條件">
<table align="center" width="100%" class="form_table">
<tr>
<td class="form_label">
食物名稱
</td>
<td colspan="1">
<h:text property="criteria/_expr[1]/foodname"/>
<h:hidden property="criteria/_expr[1]/_op" value="like"/>
<h:hidden property="criteria/_expr[1]/_likeRule" value="all"/>
</td>
<td class="form_label">
類别
</td>
<td colspan="1">
<!--<d:select dictTypeId="gender" nullLabel="--請選擇--" property="criteria/_expr[2]/fkfoodtypeid"/>-->
<h:select property="criteria/_expr[2]/fkfoodtypeid">
<option value="">--請選擇--</option>
<option value="1">1</option>
<option value="2">2</option>
</h:select>
<h:hidden property="criteria/_expr[2]/_op" value="="/>
</td>
<td>
<a class="nui-button" οnclick="out()" iconCls="icon-edit">
導出excel
</a>
</td>
</tr>
</table>
</w:panel>
<h:hidden property="criteria/_orderby[1]/_sort" value="asc"/>
<h:hidden property="criteria/_orderby[1]/_property" value="id"/>
</h:form>
<script type="text/javascript">
nui.parse();
function out(){
document.getElementById("queryForm").submit();
}
</script>
</body>
</html>
（2）。download.jsp
<%@page pageEncoding="UTF-8"%>
<%@page import="javax.servlet.ServletOutputStream"%>
<%@page import="java.io.*"%>
<%@page import="com.eos.web.taglib.util.*" %><%
//擷取标簽中使用的國際化資源資訊
String fileNotExist=com.eos.foundation.eoscommon.ResourcesMessageUtil.getI18nResourceMessage("l_fileNotExist");
Object root= com.eos.web.taglib.util.XpathUtil.getDataContextRoot("request", pageContext);
String localFile=(String)XpathUtil.getObjectByXpath(root,"downloadFile");
System.out.println(">>>>download file is "+localFile);
byte[] buffer = new byte[512];
int size = 0;
response.reset();
response.setContentType("application/vnd.ms-excel");
//response.setHeader("Content-disposition", "attachment;filename=\""+ java.net.URLEncoder.encode(localFile,"UTF-8") + "\"");
response.setHeader("Content-disposition", "attachment;filename=\""+ java.net.URLEncoder.encode("tempExcel.xls","UTF-8") + "\"");
ServletOutputStream os = null;
FileInputStream in = null;
try {
os = response.getOutputStream();
File downloadFile=new File(localFile);
if(downloadFile!=null&&downloadFile.exists()){
in = new FileInputStream(new File(localFile));
while ((size = in.read(buffer)) != -1) {
os.write(buffer, 0, size);
}
out.clear();
out = pageContext.pushBody();
}else{
out.print(fileNotExist); //"檔案不存在！"
}
} catch(Exception e) {
e.printStackTrace();
} finally {
try {
if(in!=null)in.close();
if(os!=null)os.close();
File file=new File(localFile);
if (file!=null&&file.isFile()&&file.exists()) {
file.delete();
}
} catch (IOException e) {
e.printStackTrace();
}
}
%>
4.建造一個頁面流：如圖所示：
newFile.jsp就是建造的第一個jsp頁面。
query中聲明了輸出參數的變量criteria 類型是criteriaType
exportEmpExcel中的傳回值是downloadFile 類型是變量。
5.在配置contribution.eosinf中進行配置：
<!-- 相關配置 -->
<module name="example-config">
<!-- Excel相關配置 -->
<group name="excel-config">
<!-- EXCEL模闆路徑 -->
<configValue key="excel_template_path">/WEB-INF/excel-config/</configValue>
<!-- 導出EXCEL最大行數 -->
<configValue key="excel_export_maxnum">10000</configValue>
</group>
</module>
至此，導出excel表格就完成了，當查詢條件為空是則是導出全部資料，有條件時導出符合條件的資料。