网页导出excel
package site.action.ecom.backend.wechat.exportExcel;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelExportCfg {
int orderNumber() default 0; //排序的数字
String excelTitle() default ""; //Excel的标题
int columnWidth() default 3000; //列宽，默认15
String format() default "";//自定义时间格式
}
package site.action.ecom.backend.wechat.exportExcel;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
public class ExcelStyleUtil {
public static String defaultExcelName = "EXCEL导出通用标题";
public static int defaultWidth = 4000;// 默认列宽
public static String defaultAlign = "left";// 默认对齐方式
// 设置导出的EXCEL字体的样式
public static HSSFCellStyle setStyle(HSSFWorkbook workbook,
String cellType, HSSFFont font, String align, String color) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置字体
font.setFontHeightInPoints((short) 9); // 字体高度
font.setColor(HSSFFont.BOLDWEIGHT_NORMAL); // 字体颜色
font.setFontName("宋体"); // 字体
// -----------------------
/**
* 设置表格填充色
*/
HSSFPalette palette = workbook.getCustomPalette();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 宽度 (粗体)
font.setFontHeightInPoints((short) 10);// 字号8
// font.setItalic( true ); // 是否使用斜体
// font.setStrikeout(true); // 是否使用划线
// 设置单元格类型
cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index);
cellStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);
if (color.equals("LIGHTBLUE"))
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
else if (color.equals("LIGHTGREEN"))
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
else if (color.equals("GREY"))
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
else if (color.equals("ORANGE"))
cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index);
else if (color.equals("RED"))
cellStyle.setFillForegroundColor(HSSFColor.RED.index);
else
cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle.setFillBackgroundColor(HSSFColor.AQUA.index);
// cellStyle.setFillPattern(HSSFCellStyle.BIG_SPOTS);
// cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
// cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFont(font);
cellStyle.setWrapText(true);
// 设置单元格对齐方式
if ("center".equals(align))
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平局中
else if ("right".equals(align))
cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 右对齐
else
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 右对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直局中
/**
* 设置边框线
*/
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
return cellStyle;
}
}
package site.action.ecom.backend.wechat.exportExcel;
import java.io.OutputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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 site.action.ecom.backend.wechat.exportExcel.ExcelExportCfg;
import site.common.util.StringUtils;
import sun.reflect.misc.FieldUtil;
public class ExportExcel {
// 静态Map，保存类和对应的ExcelExportObj
private final static Map<Class, ExcelExportObj[]> classMap = new HashMap<Class, ExcelExportObj[]>();
public void exportExcel(List list, HttpServletResponse response)
throws NoSuchMethodException, SecurityException,
IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
Map<String, List> dataMap = new HashMap<String, List>();
dataMap.put("sheet1", list);
exportExcel(dataMap, response, null, null);
}
public void exportExcel(Map<String, List> dataMap,
HttpServletResponse response) throws NoSuchMethodException,
SecurityException, IllegalAccessException,
IllegalArgumentException, InvocationTargetException {
exportExcel(dataMap, response, null, null);
}
public void exportExcel(Map<String, List> dataMap,
HttpServletResponse response, String title)
throws NoSuchMethodException, SecurityException,
IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
exportExcel(dataMap, response, null, title);
}
public void exportExcel(Map<String, List> dataMap,
HttpServletResponse response, String[] headers)
throws NoSuchMethodException, SecurityException,
IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
exportExcel(dataMap, response, headers, null);
}
/**
* 导出数据到Excel
*
* @param dataMap
* key对应导出的内容，value对应相应的List<T>数据集合
* @param response
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
* @throws SecurityException
* @throws NoSuchMethodException
*/
@SuppressWarnings("unchecked")
public void exportExcel(Map<String, List> dataMap,
HttpServletResponse response, String[] headers, String title)
throws NoSuchMethodException, SecurityException,
IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
// 新建一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFFont font = workbook.createFont();
HSSFCellStyle cellTitleStyle = ExcelStyleUtil.setStyle(workbook,
"title", font, "center", "LIGHTGREEN");// 设置EXCEL单元格样式（标题）
HSSFCellStyle cellStyle = ExcelStyleUtil.setStyle(workbook, "cell",
font, ExcelStyleUtil.defaultAlign, "WHITE");// 设置EXCEL单元格样式
for (String key : dataMap.keySet()) {
Class c;
if (dataMap.get(key) == null || dataMap.get(key).size() == 0) {
continue;
} else {
c = dataMap.get(key).get(0).getClass();
}
if (!classMap.containsKey(c)) {
List<ExcelExportObj> excelExportList = parseExcelExportObj(c);
// 将该List按orderNum进行排序,再存入classMap
Collections.sort(excelExportList, new SortByOrderNumber());
ExcelExportObj[] excelExportArr = new ExcelExportObj[excelExportList
.size()];
excelExportList.toArray(excelExportArr);
classMap.put(c, excelExportArr);
}
ExcelExportObj[] excelExportArr = classMap.get(c);
// 生成一个表格，标题就是当前遍历的key
HSSFSheet sheet = workbook.createSheet(key);
HSSFFont font1 = workbook.createFont();
HSSFCellStyle cellTitleStyle1 = ExcelStyleUtil.setStyle(workbook,
"title", font1, "center", "LIGHTGREEN");// 设置EXCEL单元格样式（标题）
HSSFCellStyle cellStyle1 = ExcelStyleUtil.setStyle(workbook,
"cell", font1, ExcelStyleUtil.defaultAlign, "WHITE");// 设置EXCEL单元格样式
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
int colSum;
// 写标题头信息，如果headers不为空，取headers；否则遍历excelExportArr，获取excelTitle
if (headers != null && headers.length > 0) {
colSum = headers.length;
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
sheet.setColumnWidth(i, excelExportArr[i].columnWidth);
cell.setCellStyle(cellTitleStyle1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
} else { // 不为空则遍历excelExportList
colSum = excelExportArr.length;
for (int i = 0; i < excelExportArr.length; i++) {
HSSFCell cell = row.createCell(i);
sheet.setColumnWidth(i, excelExportArr[i].columnWidth);
cell.setCellStyle(cellTitleStyle1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text = new HSSFRichTextString(
excelExportArr[i].excelTitle);
cell.setCellValue(text);
}
}
// 遍历数据集合，产生数据行
Iterator it = dataMap.get(key).iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
Object t = it.next();
for (int i = 0; i < excelExportArr.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(cellStyle1);
String text = "";
if(excelExportArr[i].getDataType() == Date.class) {
Date date = (Date)BeanUtilsBean.getInstance().getPropertyUtils().getNestedProperty(t,
excelExportArr[i].fieldName);
if(!StringUtils.isEmpty(excelExportArr[i].format)){
text = new SimpleDateFormat(excelExportArr[i].format).format(date);
}
}else{
text = BeanUtils.getProperty(t,
excelExportArr[i].fieldName);
}
cell.setCellValue(text);
}
}
for (short i = 0; i < colSum; i++) {
sheet.autoSizeColumn(i); // 调整第i列宽度
}
}
try {
OutputStream out = response.getOutputStream();
response.reset();
response.setContentType("application/x-msdownload");
if(title!=null) {
title = new String(title.getBytes("utf-8"), "utf-8");
response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(title, "utf-8")+".xls");
}
else {
response.setHeader("Content-disposition", "attachment; filename=" + "book" + ".xls");
}
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private List<ExcelExportObj> parseExcelExportObj(Class objClass)
throws NoSuchMethodException, SecurityException,
IllegalAccessException, IllegalArgumentException,
InvocationTargetException {
// 利用反射，获取fields中所有的属性
Field[] fields = objClass.getDeclaredFields();
List<ExcelExportObj> excelExportList = new ArrayList<ExcelExportObj>();
for (Field field : fields) {
// 获取属性中，有注解的属性
Annotation[] annotationArr = field.getAnnotations();
for (Annotation annotation : annotationArr) {
if (annotation instanceof ExcelExportCfg) { // 判断是不是excel导出注解的属性信息
Method orderNumberMethod = annotation.annotationType()
.getMethod("orderNumber");
Method excelTitleMethod = annotation.annotationType()
.getMethod("excelTitle");
Method columnWidthMethod = annotation.annotationType()
.getMethod("columnWidth");
Method formatMethod = annotation.annotationType()
.getMethod("format");
ExcelExportObj excelExportObj = new ExcelExportObj();
excelExportObj.setOrderNumber( (Integer) orderNumberMethod.invoke(annotation)); // 排序的数字
excelExportObj.setExcelTitle((String) excelTitleMethod
.invoke(annotation)); // Excel的标题
excelExportObj.setFormat((String) formatMethod
.invoke(annotation)); // 数据格式
excelExportObj.setDataType(field.getType());//数据类型
excelExportObj.setFieldName(field.getName()); // 导出对象的属性值
excelExportObj.setColumnWidth((Integer) columnWidthMethod
.invoke(annotation));
excelExportList.add(excelExportObj);
}
}
}
return excelExportList;
}
public static class ExcelExportObj {
private int orderNumber; // 排序的数字
private String fieldName; // 导出对象的属性值
private String excelTitle; // Excel的标题
private int columnWidth;
private Class<?> dataType;
private String format;//时间格式
public Class<?> getDataType() {
return dataType;
}
public void setDataType(Class<?> dataType) {
this.dataType = dataType;
}
public String getFormat() {
return format;
}
public void setFormat(String format) {
this.format = format;
}
// private Class<T> filedType; //对象属性值的类型
public int getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(int orderNumber) {
this.orderNumber = orderNumber;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
public String getExcelTitle() {
return excelTitle;
}
public void setExcelTitle(String excelTitle) {
this.excelTitle = excelTitle;
}
public int getColumnWidth() {
return columnWidth;
}
public void setColumnWidth(int columnWidth) {
this.columnWidth = columnWidth;
}
}
class SortByOrderNumber implements Comparator {
@Override
public int compare(Object o1, Object o2) {
ExcelExportObj e1 = (ExcelExportObj) o1;
ExcelExportObj e2 = (ExcelExportObj) o2;
if (e1.getOrderNumber() > e2.getOrderNumber()) {
return 1;
} else if (e1.getOrderNumber() == e2.getOrderNumber()) {
return 0;
}
return -1;
}
}
}
/**@ExcelExportCfg(orderNumber=1, excelTitle="客户姓名")
* 导出投票信息
* @return
*/
public void exportProjectListAction(){
if(selected.length() != 0 || !selected.isEmpty()){
String[] array = selected.split(",");
List<Long> list = new ArrayList<Long>();
for(int i=0;i<array.length;i++){
list.add(Long.valueOf(array[i]));
}
String title = "投票列表信息";
Map<String, List> dataMap = new LinkedHashMap<String, List>(); // 再讲List存入一个map中
voteList=voteService.findByIdList(list);
dataMap.put("投票列表信息", voteList);
ExportExcel ex = new ExportExcel();
try {
/***** 注意 *****/
ex.exportExcel(dataMap, response, null, title); // 最后，调用这个方法，导出到excel。页面会自动下载该文件。
} catch (Exception e) {
e.printStackTrace();
}
}
@ExcelExportCfg(orderNumber=1, excelTitle="投票会员名称")
private String memberName;
/**
* 投票项目主键
*/
private Long projectId;
/**
* 投票项目名称
*/
@ExcelExportCfg(orderNumber=2, excelTitle="投票项目名称")
private String projectName;
/**
* 投票日期
*/
@ExcelExportCfg(orderNumber=3, excelTitle="投票日期", format="yyyy-MM-dd HH:mm:ss")
private Date voteDate;
/**
