package cn.knet.data.untils; import java.awt.Color;
import java.io.File;
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.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern; import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExportExcelUtil2<T> { public void exportExcel(Collection<T> dataset,String[] headers,String path,String excelName,String sheetName,String title) throws IOException
{
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet=workbook.createSheet(); // 设置表格默认列宽度为15个字节
//要是提供的sheetName就给第一个表格设置名字
if(StringUtils.isNotBlank(sheetName))
{
workbook.setSheetName(0,sheetName);
}
//设置行的默认值
int row_num=0;
if(StringUtils.isNotBlank(title)){
XSSFRow rowtitle = sheet.createRow(row_num);
rowtitle.setHeightInPoints(23);
XSSFCell cellHead = rowtitle.createCell(0);
cellHead.setCellValue(title);
//合并标题的单元格
sheet.addMergedRegion(new CellRangeAddress(row_num,row_num,0,headers.length-1));//startRow,endRow,startColumn,endColumn
//设置总标题样式==============================
//设置字体
XSSFFont font_title = workbook.createFont();
font_title.setColor(new XSSFColor(Color.black).getIndexed());
font_title.setFontHeightInPoints((short) 16);
font_title.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
//设置title样式
XSSFCellStyle style_title = workbook.createCellStyle();
style_title.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style_title.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style_title.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style_title.setBorderRight(XSSFCellStyle.BORDER_THIN);
style_title.setBorderTop(XSSFCellStyle.BORDER_THIN);
style_title.setFont(font_title);
//设置cell样式
cellHead.setCellStyle(style_title);
//解决合并单元格后加边框问题
for(int i=1;i<headers.length;i++){
cellHead = rowtitle.createCell(i);
cellHead.setCellValue("");
cellHead.setCellStyle(style_title);
} //行数加1行
row_num=row_num+1;
} //设置head样式============================================
// 生成一个字体
XSSFFont font_head = workbook.createFont();
font_head.setColor(new XSSFColor(Color.BLACK).getIndexed());
font_head.setFontHeightInPoints((short) 12);
font_head.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 生成head样式
XSSFCellStyle style_head = workbook.createCellStyle();
// 把字体应用到当前的样式
style_head.setFont(font_head);
// 设置这些样式
// style_head.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
//style_head.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style_head.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style_head.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style_head.setBorderRight(XSSFCellStyle.BORDER_THIN);
style_head.setBorderTop(XSSFCellStyle.BORDER_THIN);
style_head.setAlignment(XSSFCellStyle.ALIGN_CENTER);
//设置head样式======End==================================
//设置body样式==================================================
// 生成另一个字体
XSSFFont font_body = workbook.createFont();
font_body.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
//生成body样式
XSSFCellStyle style_body = workbook.createCellStyle();
// 把字体应用到当前的样式
style_body.setFont(font_body);
// style_body.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
//style_body.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style_body.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style_body.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style_body.setBorderRight(XSSFCellStyle.BORDER_THIN);
style_body.setBorderTop(XSSFCellStyle.BORDER_THIN);
style_body.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style_body.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 添加表格标题行
XSSFRow row_head = sheet.createRow(row_num);
row_head.setHeightInPoints(25);
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row_head.createCell(i);
cell.setCellStyle(style_head);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
//添加标题行后行号加1
row_num=row_num+1;
//添加body
// 遍历集合数据，产生数据行
//Field[] fields = types.getDeclaredFields();
Iterator<T> it = dataset.iterator();
while (it.hasNext()) {
System.err.println(row_num);
XSSFRow row_body = sheet.createRow(row_num);
row_body.setHeightInPoints(20); T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) { //设置列宽度自适应
sheet.autoSizeColumn(i,true); XSSFCell cell = row_body.createCell(i);
cell.setCellStyle(style_body);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"+ fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);
try {
Class<? extends Object> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
}
else if (value instanceof Float) {
float fValue = (Float) value;
textValue = String.valueOf(fValue);
cell.setCellValue(textValue);
}
else if (value instanceof Double) {
double dValue = (Double) value;
textValue = String.valueOf(dValue);
cell.setCellValue(textValue);
}
else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellValue(longValue);
}
else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
textValue = sdf.format(date);
}else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
} if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
XSSFRichTextString richString = new XSSFRichTextString(textValue);
XSSFFont font3 = workbook.createFont();
font3.setColor(new XSSFColor(Color.BLACK).getIndexed());
richString.applyFont(font3);
cell.setCellValue(richString);
}
} } catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
}
row_num=row_num+1;
}
OutputStream os = new FileOutputStream(new File(path+"/"+excelName+".xlsx"));
try{
workbook.write(os);
}
catch(Exception ex){
ex.printStackTrace();
}
finally{
os.flush();
os.close();
} } public void exportExcelFast(Collection<T> dataset,String[] headers,Class<T> t,String path,String excelName,String sheetName) throws IOException
{
FileOutputStream output = new FileOutputStream(new File(path+"/"+excelName+".xlsx")); //读取的文件路径
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet=workbook.createSheet(sheetName);
XSSFRow row_head = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = row_head.createCell(i);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
} Field[] fields = t.getDeclaredFields();
int row_num=1;
for (T tt : dataset) { XSSFRow row_body = sheet.createRow(row_num);
for(int i=0;i<fields.length;i++){
XSSFCell cell = row_body.createCell(i);
Object v;
try {
v = BeanUtils.getProperty(tt, fields[i].getName());
String value =v==null?"":v.toString();
cell.setCellType(XSSFCell.CELL_TYPE_STRING);//文本格式
cell.setCellValue(value);//写入内容
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//.getBeanProperty(tt, fields[i].getName()); }
row_num++;
} workbook.write(output);
output.flush();
output.close();
}
public static void main(String[] args){
/* List<Flow> list = new ArrayList<Flow>();
for(int i=0;i<50000;i++)
{
Flow f = new Flow();
f.setCurkey("枯萎奇才霏霏夺标需要奇怪棒棒酩酊大醉大模大样大模大样"+i);
f.setFlow(i);
list.add(f);
}
ExportExcelUtil2<Flow> ex = new ExportExcelUtil2<Flow>();
String[] headers= {"KEY","流水"};
try {
//ex.exportExcelFast(list, headers,Flow.class, "D:\\", "MYExcel", "MySheet");
ex.exportExcel(list, headers, "D:\\", "MYExcel", "MySheet","");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
} }