packagecom.test.cms.excel;importcn.hutool.core.util.ObjectUtil;importjxl.Workbook;importjxl.write.Label;importjxl.write.WritableSheet;importjxl.write.WritableWorkbook;importjavax.servlet.http.HttpServletResponse;importjava.io.OutputStream;importjava.lang.reflect.Field;importjava.text.SimpleDateFormat;import java.util.*;importjava.util.Map.Entry;public classExcelUtils {
public static voidlistToExcel(
Listlist,
LinkedHashMapfieldMap,
String sheetName,intsheetSize,
OutputStream out
)throwsException {if (list == null || list.size() == 0) {throw new Exception("資料源中沒有任何資料");
}if (sheetSize > 65535 || sheetSize < 1) {
sheetSize= 65535;
}//建立工作簿并發送到OutputStream指定的地方
WritableWorkbook wwb;try{
wwb=Workbook.createWorkbook(out);//因為2003的Excel一個工作表最多可以有65536條記錄，除去列頭剩下65535條//是以如果記錄太多，需要放到多個工作表中，其實就是個分頁的過程//1.計算一共有多少個工作表
double sheetNum = Math.ceil(list.size() / newInteger(sheetSize).doubleValue());//2.建立相應的工作表，并向其中填充資料
for (int i = 0; i < sheetNum; i++) {//如果隻有一個工作表的情況
if (1 ==sheetNum) {
WritableSheet sheet=wwb.createSheet(sheetName, i);
fillSheet(sheet, list, fieldMap,0, list.size() - 1);//有多個工作表的情況
} else{
WritableSheet sheet= wwb.createSheet(sheetName + (i + 1), i);//擷取開始索引和結束索引
int firstIndex = i *sheetSize;int lastIndex = (i + 1) * sheetSize - 1 > list.size() - 1 ? list.size() - 1 : (i + 1) * sheetSize - 1;//填充工作表
fillSheet(sheet, list, fieldMap, firstIndex, lastIndex);
}
}
wwb.write();
wwb.close();
}catch(Exception e) {
e.printStackTrace();//如果是Exception，則直接抛出
if (e instanceofException) {throw(Exception) e;//否則将其它異常包裝成Exception再抛出
} else{throw new Exception("導出Excel失敗");
}
}
}
public static voidlistToExcel(
Listlist,
LinkedHashMapfieldMap,
String sheetName,intsheetSize,
HttpServletResponse response
)throwsException {//設定預設檔案名為目前時間：年月日時分秒
String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(newDate()).toString();//設定response頭資訊
response.reset();
response.setContentType("application/vnd.ms-excel"); //改成輸出excel檔案
response.setHeader("Content-disposition", "attachment; filename=" + new String(sheetName.getBytes(), "iso-8859-1") + ".xls");//建立工作簿并發送到浏覽器
try{
OutputStream out=response.getOutputStream();
listToExcel(list, fieldMap, sheetName, sheetSize, out);
}catch(Exception e) {
e.printStackTrace();//如果是Exception，則直接抛出
if (e instanceofException) {throw(Exception) e;//否則将其它異常包裝成Exception再抛出
} else{throw new Exception("導出Excel失敗");
}
}
}
public static voidlistToExcel(
Listlist,
LinkedHashMapfieldMap,
String sheetName,
HttpServletResponse response
)throwsException {
listToExcel(list, fieldMap, sheetName,65535, response);
}
private static Object getFieldValueByName(String fieldName, Object o) throwsException {
Object value= null;
Field field=getFieldByName(fieldName, o.getClass());if (field != null) {
field.setAccessible(true);
value=field.get(o);
}else{throw new Exception(o.getClass().getSimpleName() + "類不存在字段名 " +fieldName);
}returnvalue;
}
private static Field getFieldByName(String fieldName, Class>clazz) {//拿到本類的所有字段
Field[] selfFields =clazz.getDeclaredFields();//如果本類中存在該字段，則傳回
for(Field field : selfFields) {if(field.getName().equals(fieldName)) {returnfield;
}
}//否則，檢視父類中是否存在此字段，如果有則傳回
Class> superClazz =clazz.getSuperclass();if (superClazz != null && superClazz != Object.class) {returngetFieldByName(fieldName, superClazz);
}//如果本類和父類都沒有，則傳回空
return null;
}
private static Object getFieldValueByNameSequence(String fieldNameSequence, Object o) throwsException {
Object value= null;//将fieldNameSequence進行拆分
String[] attributes = fieldNameSequence.split("\\.");if (attributes.length == 1) {
value=getFieldValueByName(fieldNameSequence, o);
}else{//根據屬性名擷取屬性對象
Object fieldObj = getFieldValueByName(attributes[0], o);
String subFieldNameSequence= fieldNameSequence.substring(fieldNameSequence.indexOf(".") + 1);
value=getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
}returnvalue;
}
private static void setColumnAutoSize(WritableSheet ws, intextraWith) {//擷取本列的最寬單元格的寬度
for (int i = 0; i < ws.getColumns(); i++) {int colWith = 0;for (int j = 0; j < ws.getRows(); j++) {
String content=ws.getCell(i, j).getContents().toString();int cellWith =content.length();if (colWith
colWith=cellWith;
}
}//設定單元格的寬度為最寬寬度+額外寬度
ws.setColumnView(i, colWith +extraWith);
}
}
private static voidfillSheet(
WritableSheet sheet,
Listlist,
LinkedHashMapfieldMap,intfirstIndex,intlastIndex
)throwsException {//定義存放英文字段名和中文字段名的數組
String[] enFields = newString[fieldMap.size()];
String[] cnFields= newString[fieldMap.size()];//填充數組
int count = 0;for (Entryentry : fieldMap.entrySet()) {
enFields[count]=entry.getKey();
cnFields[count]=entry.getValue();
count++;
}//填充表頭
for (int i = 0; i < cnFields.length; i++) {
Label label= new Label(i, 0, cnFields[i]);
sheet.addCell(label);
}//填充内容
int rowNo = 1;for (int index = firstIndex; index <= lastIndex; index++) {//擷取單個對象
T item =list.get(index);for (int i = 0; i < enFields.length; i++) {
Object objValue=getFieldValueByNameSequence(enFields[i], item);if (objValue instanceofDate) {
objValue= date2Str((Date) objValue, "yyyy-MM-dd HH:mm:ss");
}
String fieldValue= (objValue == null) ? "": objValue.toString();
Label label= newLabel(i, rowNo, fieldValue);
sheet.addCell(label);
}
rowNo++;
}//設定自動列寬
setColumnAutoSize(sheet, 5);
}public staticString date2Str(Date date, String formats) {return ObjectUtil.isEmpty(date) ? null : (newSimpleDateFormat(formats)).format(date);
}
}