java编程EXCEL导出,支持多sheet页导出
程序员文章站
2022-07-13 12:58:27
...
- excel导出工具类
import java.util.Collection;
/**
* Excle导出参数列表实体类
*/
public class ExportExcleDTO<T> {
*
* @param title
* 表格标题名
* @param headers
* 表格属性列名数组
* @param dataset
* 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
* @param pattern
* 如果有时间数据,设定输出格式。默认为"yyyMMdd"
* @param formatType
* 如果有Double类型数据,设定数字输出格式,默认为"General"
*/
private String title;
private String[] headers;
private Collection<T> dataSet;
private String pattern;
private String formatType;
public ExportExcleDTO() {}
public ExportExcleDTO(String title, String[] headers, Collection<T> dataSet, String pattern, String formatType) {
super();
this.title = title;
this.headers = headers;
this.dataSet = dataSet;
this.pattern = pattern;
this.formatType = formatType;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String[] getHeaders() {
return headers;
}
public void setHeaders(String[] headers) {
this.headers = headers;
}
public Collection<T> getDataSet() {
return dataSet;
}
public void setDataSet(Collection<T> dataSet) {
this.dataSet = dataSet;
}
public String getPattern() {
return pattern;
}
public void setPattern(String pattern) {
this.pattern = pattern;
}
public String getFormatType() {
return formatType;
}
public void setFormatType(String formatType) {
this.formatType = formatType;
}
}
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
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.XSSFDataFormat;
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;
import com.phfund.common.dto.ExportExcleDTO;
import com.phfund.report.util.ReportUtils;
/**
* Excle导出工具类
*/
public class ExportExcelUtils<T> {
private static Logger logger = LogManager.getLogger(ReportUtils.class);
/**
* @param exportList
* Excle导出实体类
* @param out
* 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public void exportExcel(List<ExportExcleDTO> exportList, OutputStream out) {
XSSFWorkbook workbook = null;
try {
// 声明一个工作薄
workbook = new XSSFWorkbook();
for(ExportExcleDTO<T> exportDTO :exportList){
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(exportDTO.getTitle());
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth(20);
XSSFCellStyle style2 = workbook.createCellStyle();
XSSFDataFormat df = workbook.createDataFormat();
//设置边框为细边框
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (short i = 0; i < exportDTO.getHeaders().length; i++) {
XSSFCell cell = row.createCell(i);
XSSFCellStyle style3 = workbook.createCellStyle();
style3.setBorderBottom(BorderStyle.THIN);
style3.setBorderLeft(BorderStyle.THIN);
style3.setBorderRight(BorderStyle.THIN);
style3.setBorderTop(BorderStyle.THIN);
XSSFFont font = workbook.createFont();
font.setBold(true);//字体加粗
style3.setFont(font);
cell.setCellStyle(style3);
XSSFRichTextString text = new XSSFRichTextString(exportDTO.getHeaders()[i]);
cell.setCellValue(text);
}
// 遍历集合数据,产生数据行
Iterator<T> it = exportDTO.getDataSet().iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
String typeName = t.getClass().getTypeName();
if("java.util.LinkedHashMap".equals(typeName)){
LinkedHashMap map = (LinkedHashMap) t;
int i=0;
for (Object key : map.keySet()) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Object value = map.get(key);
setValue(exportDTO.getPattern(), exportDTO.getFormatType(), style2, df, cell, value);
i++;
}
}else{
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
setValue(exportDTO.getPattern(), exportDTO.getFormatType(), style2, df, cell, value);
} catch (Exception e) {
logger.error("导出Excel异常,信息:" + e.getMessage());
}
}
}
}
}
workbook.write(out);
} catch (IOException e) {
logger.error("导出Excel异常,信息:" + e.getMessage());
}finally{
try {
workbook.close();
} catch (IOException e) {
logger.error("关闭workbook异常,信息:" + e.getMessage());
}
}
}
@SuppressWarnings("deprecation")
private void setValue(String pattern, String formatType,
XSSFCellStyle style2, XSSFDataFormat df, XSSFCell cell, Object value) {
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (Float) value;
XSSFRichTextString xfValue = new XSSFRichTextString(
String.valueOf(fValue));
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(xfValue);
} else if (value instanceof Double) {
double dValue = (Double) value;
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
//设置数字输出格式
style2.setDataFormat(df.getFormat(formatType));
cell.setCellStyle(style2);
cell.setCellValue(dValue);
} else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(longValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if(value instanceof String){
cell.setCellValue(value+"");
}else {
// 其它数据类型都当作字符串简单处理
if (value == null || "null".equals(value)) {
textValue = "";
} else {
textValue = value + "";
}
}
// 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null) {
//Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Pattern p = Pattern.compile("^[+-]?([1-9][0-9]*|0)(\\.[0-9]+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
XSSFRichTextString richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
}