package com.rrz.common.utils.excel;

import java.io.IOException;

import java.io.OutputStream;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.net.URLEncoder;

import java.text.SimpleDateFormat;

import java.util.Collection;

import java.util.Date;

import java.util.Iterator;

import javax.servlet.http.HttpServletResponse;

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.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExportExcelUtils {

public static <T> HSSFWorkbook export(HttpServletResponse response, String fileName, String[] excelHeader,

Collection<T> dataList) throws NoSuchMethodException, SecurityException, IllegalAccessException,

IllegalArgumentException, InvocationTargetException, IOException {

response.setContentType("application/vnd.ms-excel;charset=UTF-8");

response.setHeader("Content-disposition",

"attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8"));

HSSFWorkbook wb = new HSSFWorkbook();

HSSFCellStyle titleStyle = titleStyle(wb);

HSSFSheet sheet = wb.createSheet(fileName);

String[] titleArray = new String[excelHeader.length];

String[] fieldArray = new String[excelHeader.length];

for (int i = 0; i < excelHeader.length; i++) {

String[] tempArray = excelHeader[i].split("#");

titleArray[i] = tempArray[0];

fieldArray[i] = tempArray[1];

}

HSSFRow row = sheet.createRow((int) 0);// 行数从0开始

HSSFCell seqCell = row.createCell(0);// cell列 从0开始 第一列添加序号

seqCell.setCellValue("序号");

seqCell.setCellStyle(titleStyle);

sheet.autoSizeColumn(0);// 自动设置宽度

for (int i = 0; i < titleArray.length; i++) {

HSSFCell titleCell = row.createCell(i + 1);

titleCell.setCellValue(titleArray[i]);

titleCell.setCellStyle(titleStyle);

sheet.autoSizeColumn(i + 1);

}

HSSFCellStyle dataStyle = dataStyle(wb);

Iterator<T> it = dataList.iterator();

int index = 0;

while (it.hasNext()) {

index++;

row = sheet.createRow(index);

HSSFCell sequenceCellValue = row.createCell(0);

sequenceCellValue.setCellValue(index);

sequenceCellValue.setCellStyle(dataStyle);

sheet.autoSizeColumn(0);

T t = (T) it.next();

for (int i = 0; i < excelHeader.length; i++) {

HSSFCell dataCell = row.createCell(i + 1);

dataCell.setCellStyle(dataStyle);

sheet.autoSizeColumn(i + 1);

String fieldName = fieldArray[i];

boolean flag = false;

if (fieldName.equals("roleFlag") || fieldName.equals("authStatus")) {

flag = true;

}

String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);// 取得对应getXxx()方法

Class<? extends Object> tCls = t.getClass();// 泛型为Object以及所有Object的子类

Method getMethod = tCls.getMethod(getMethodName, new Class[] {});// 通过方法名得到对应的方法

Object value = getMethod.invoke(t, new Object[] {});// 动态调用方,得到属性值

if (value != null) {

if (value instanceof Date) {

value = dateToStrLong((Date) value);

}

if (flag) {

if (value.toString().equals("1")) {

dataCell.setCellValue("是");

} else {

dataCell.setCellValue("否");

}

} else {

dataCell.setCellValue(value.toString());// 为当前列赋值

}

} else {

if (flag) {

dataCell.setCellValue("否");

}

}

}

}

OutputStream outputStream = response.getOutputStream();

wb.write(outputStream);

outputStream.flush();

outputStream.close();

return wb;

}

private final static HSSFCellStyle titleStyle(HSSFWorkbook wb) {

HSSFCellStyle titleStyle = wb.createCellStyle();

titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框 细边线

titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框 细边线

titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框 细边线

titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 细边线

// 设置单元格对齐方式

titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中

titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中

// 设置字体样式

HSSFFont titleFont = wb.createFont();

titleFont.setFontHeightInPoints((short) 15); // 字体高度

titleFont.setFontName("黑体"); // 字体样式

titleStyle.setFont(titleFont);

return titleStyle;

}

private final static HSSFCellStyle dataStyle(HSSFWorkbook wb) {

HSSFCellStyle dataStyle = wb.createCellStyle();

dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

// 设置居中样式

dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中

dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中

// 设置数据字体

HSSFFont dataFont = wb.createFont();

dataFont.setFontHeightInPoints((short) 12); // 字体高度

dataFont.setFontName("宋体"); // 字体

dataStyle.setFont(dataFont);

return dataStyle;

}

public static String dateToStrLong(Date dateDate) {

SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String dateString = formatter.format(dateDate);

return dateString;

}

}