package com.qxueyou.scc.base.util;
|
|
//import java.io.FileNotFoundException;
|
//import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.lang.reflect.Field;
|
import java.lang.reflect.InvocationTargetException;
|
import java.lang.reflect.Method;
|
import java.text.SimpleDateFormat;
|
//import java.util.ArrayList;
|
import java.util.Collection;
|
import java.util.Date;
|
import java.util.Iterator;
|
import java.util.List;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.logging.log4j.LogManager;
|
import org.apache.logging.log4j.Logger;
|
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 org.apache.poi.hssf.util.HSSFColor;
|
|
import com.qxueyou.scc.base.model.ExcelExportParam;
|
//import com.qxueyou.scc.user.model.UserRegistration;
|
//import com.qxueyou.scc.user.model.UserRegistrationExport;
|
|
/**
|
*
|
*
|
* @param <T>
|
* 应用泛型，代表任意一个符合javabean风格的类
|
* boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
|
*/
|
public class ExcelExportUtils<T> {
|
|
private static Logger log = LogManager.getLogger("ExcelExport");
|
|
/**
|
* 设置标题样式,字体
|
* @param objStyle
|
* @param workbook
|
*/
|
private void initTitleCellStyle(HSSFCellStyle objStyle,HSSFWorkbook workbook){
|
objStyle.setFillForegroundColor(HSSFColor.WHITE.index);
|
objStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
|
objStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
|
objStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
|
objStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
|
objStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
|
objStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
|
|
// 生成一个字体
|
HSSFFont font = workbook.createFont();
|
font.setFontHeightInPoints((short) 12);
|
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
|
// 把字体应用到标题样式
|
objStyle.setFont(font);
|
}
|
|
/**
|
* 设置导出内容样式
|
* @param objStyle
|
* @param workbook
|
*/
|
private void initContentCellStyle(HSSFCellStyle objStyle,HSSFWorkbook workbook){
|
objStyle.setFillForegroundColor(HSSFColor.WHITE.index);
|
objStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
|
objStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
|
objStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
|
objStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
|
objStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
|
objStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
|
}
|
|
/**
|
* 是否指定列宽
|
* @param sheet
|
* @param lstColumnWidth
|
*/
|
private void initColumnWidth(HSSFSheet sheet,List<Integer> lstColumnWidth){
|
if(lstColumnWidth != null){
|
for(int i=0;i<lstColumnWidth.size();i++){
|
sheet.setColumnWidth(i, lstColumnWidth.get(i));
|
}
|
}
|
}
|
|
/**
|
* 初始化导出标题
|
* @param row
|
* @param headers
|
* @param titleStyle
|
*/
|
private void initTitleRow(HSSFRow row,String[] headers,HSSFCellStyle titleStyle,List<String> lstColumn){
|
for (int i = 0; i < headers.length && (null == lstColumn || i <= lstColumn.size()); i++) {
|
HSSFCell cell = row.createCell(i);
|
cell.setCellStyle(titleStyle);
|
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
|
cell.setCellValue(text);
|
}
|
}
|
|
/**
|
* 按照T，导出所有列内容
|
* @param objExcelExportParam
|
*/
|
public void exportExcel(ExcelExportParam<T> objExcelExportParam) {
|
this.exportExcel(objExcelExportParam,false);
|
}
|
|
/**
|
* 指定列导出
|
* @param objExcelExportParam
|
*/
|
public void exportExcelByColomn(ExcelExportParam<T> objExcelExportParam) {
|
this.exportExcel(objExcelExportParam,true);
|
}
|
|
/**
|
* 导出excel实现
|
* @param objExcelExportParam
|
* @param exportByCol
|
*/
|
public void exportExcel(ExcelExportParam<T> objExcelExportParam,boolean exportByCol){
|
//获取参数：
|
String title = objExcelExportParam.getTitle();
|
Collection<T> colData = objExcelExportParam.getColData();
|
OutputStream out = objExcelExportParam.getOut();
|
String pattern = objExcelExportParam.getPattern();
|
if(null == pattern){
|
pattern = "yyyy-MM-dd";
|
}
|
boolean numberFormat = objExcelExportParam.getNumberFormat();
|
List<String> lstColumn = null;
|
if(exportByCol){
|
lstColumn = objExcelExportParam.getLstColumn();
|
}
|
|
// 声明一个工作薄
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
HSSFSheet sheet = null;
|
// 生成一个表格
|
if(StringUtils.isNotBlank(title)){
|
sheet = workbook.createSheet(title);
|
}else{
|
sheet = workbook.createSheet("Sheet1");
|
}
|
// 设置表格默认列宽度为18个字节
|
sheet.setDefaultColumnWidth(18);
|
// 设置表格默认行高度
|
sheet.setDefaultRowHeight((short)300);
|
//是否指定列宽
|
initColumnWidth(sheet,objExcelExportParam.getLstColumnWidth());
|
// 标题样式
|
HSSFCellStyle titleStyle = workbook.createCellStyle();
|
initTitleCellStyle(titleStyle,workbook);
|
// 内容样式
|
HSSFCellStyle contentStyle = workbook.createCellStyle();
|
initContentCellStyle(contentStyle,workbook);
|
// 产生表格标题行
|
HSSFRow row = sheet.createRow(0);
|
initTitleRow(row,objExcelExportParam.getHeaders(),titleStyle,lstColumn);
|
|
// 遍历集合数据，产生数据行
|
Iterator<T> it = colData.iterator();
|
int index = 0;
|
while (it.hasNext()) {
|
index++;
|
row = sheet.createRow(index);
|
T t = (T) it.next();
|
|
//默认给序号列
|
HSSFCell cell = row.createCell(0);
|
cell.setCellStyle(contentStyle);
|
HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(index));
|
HSSFFont font3 = workbook.createFont();
|
richString.applyFont(font3);
|
cell.setCellValue(richString);
|
|
//指定列导出使用
|
if(exportByCol){
|
for(int j = 0; j < lstColumn.size() ; j++ ){
|
doExportExcel(row,contentStyle,pattern,numberFormat,workbook,lstColumn.get(j),t,j+1);
|
}
|
}else{
|
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
|
Field[] fields = t.getClass().getDeclaredFields();
|
for (int i = 0; i < fields.length; i++) {
|
if(!"serialVersionUID".equals(fields[i].getName())){
|
doExportExcel(row,contentStyle,pattern,numberFormat,workbook,fields[i].getName(),t,i);
|
}
|
}
|
}
|
}
|
try {
|
workbook.write(out);
|
workbook.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally{
|
|
}
|
}
|
|
private void doExportExcel(HSSFRow row,HSSFCellStyle contentStyle,String pattern,boolean numberFormat,HSSFWorkbook workbook,String fieldName,T t,int i){
|
HSSFCell cell = row.createCell(i);
|
cell.setCellStyle(contentStyle);
|
//boolean类型默认使用get，而非is
|
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
|
try {
|
@SuppressWarnings("rawtypes")
|
Class tCls = t.getClass();
|
@SuppressWarnings("unchecked")
|
Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
|
Object value = getMethod.invoke(t, new Object[] {});
|
// 格式化内容
|
String textValue = "";
|
if (value instanceof Date) {
|
Date date = (Date) value;
|
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
|
textValue = sdf.format(date);
|
}else{
|
if(value != null){
|
textValue = String.valueOf(value);
|
}
|
}
|
//利用正则表达式判断textValue是否全部由数字组成
|
if (null != textValue) {
|
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
|
Matcher matcher = p.matcher(textValue);
|
if (matcher.matches() && numberFormat) {
|
// 是数字当作double处理
|
cell.setCellValue(Double.parseDouble(textValue));
|
} else {
|
HSSFRichTextString richString = new HSSFRichTextString(textValue);
|
HSSFFont font3 = workbook.createFont();
|
richString.applyFont(font3);
|
cell.setCellValue(richString);
|
}
|
}
|
} catch (SecurityException e) {
|
log.error(e);
|
} catch (NoSuchMethodException e) {
|
log.error(e);
|
} catch (IllegalArgumentException e) {
|
log.error(e);
|
} catch (IllegalAccessException e) {
|
log.error(e);
|
} catch (InvocationTargetException e) {
|
log.error(e);
|
} finally {
|
|
}
|
}
|
|
public static void main(String[] args) {
|
System.out.println(new Date().getTime());
|
/*// 测试学生
|
ExcelExportUtils<UserRegistrationExport> ex = new ExcelExportUtils<UserRegistrationExport>();
|
String[] headers = { "序号","班级", "姓名", "性别" ,"报名码" ,"手机号" ,"其他联系方式","状态"};
|
List<UserRegistrationExport> dataset = new ArrayList<UserRegistrationExport>();
|
UserRegistrationExport u = new UserRegistrationExport();
|
u.setClassName("高级JAVA工程师一班");
|
u.setMobilePhone("13312886688");
|
u.setSalesCode("123.34");
|
u.setSex(false);
|
u.setStatus(UserRegistration.STATUS_ACTIVE);
|
u.setUserName("刘美幸子");
|
dataset.add(u);
|
ExcelExportParam<UserRegistrationExport> obj = new ExcelExportParam<UserRegistrationExport>();
|
obj.setColData(dataset);
|
obj.setHeaders(headers);
|
|
try {
|
OutputStream out = new FileOutputStream("D://testExportExcelAll.xls");
|
obj.setOut(out);
|
ex.exportExcel(obj);
|
out.close();
|
System.out.println("excel导出成功！");
|
} catch (FileNotFoundException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
try {
|
OutputStream out = new FileOutputStream("D://testExportExcelByCol.xls");
|
List<String> lst = new ArrayList<String>();
|
lst.add("className");
|
lst.add("userName");
|
obj.setLstColumn(lst);
|
obj.setOut(out);
|
ex.exportExcelByColomn(obj);
|
out.close();
|
System.out.println("excel指定列导出成功！");
|
} catch (FileNotFoundException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}*/
|
}
|
}
|
