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();
|
}*/
|
}
|
}
|
