说明:下面是运用java反射对POI导出Excel进行的一次封装:如果要加标题可以根据下面的导出进行改进:例如需要在总表上方加居中标题 可以对sheet.createRow(i)方法进行索引提升 原来的都加一 新的第一行合并 然后可以参考我的POI基础实践类
https://blog.csdn.net/AdolfQiu/article/details/109642597
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package cn.dreamit.p1000.module.metadata.util;
import cn.dreamit.dreamweb.util.StringUtil;
import cn.dreamit.dreamweb.web.util.ContextHolderUtils;
import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
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 java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
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.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.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
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;
import xsf.Value;
import xsf.util.DateHelper;
public class ExportExcelUtil {
public static final String EXCEl_FILE_2007 = "2007";
public static final String EXCEL_FILE_2003 = "2003";
private static final String excel2003L = ".xls";
private static final String excel2007U = ".xlsx";
public ExportExcelUtil() {
}
//导出Excel
public static <T> void exportExcel(String title, Collection<T> dataset, OutputStream out, String version) {
if (!StringUtil.isEmpty(version) && !"2003".equals(version.trim())) {
exportExcel2007(title, (String[])null, dataset, out, "yyyy-MM-dd HH:mm:ss");
} else {
exportExcel2003(title, (String[])null, dataset, out, "yyyy-MM-dd HH:mm:ss");
}
}
//根据版本号导出Excel
public static <T> void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String version) {
if (!StringUtil.isEmpty(version) && !"2003".equals(version.trim())) {
exportExcel2007(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");
} else {
exportExcel2003(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");
}
}
//导出2007版本Excel
public static <T> void exportExcel2007(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(title);
sheet.setDefaultColumnWidth(20);
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(new XSSFColor(Color.gray));
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
XSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setColor(new XSSFColor(Color.BLACK));
font.setFontHeightInPoints((short)11);
style.setFont(font);
XSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(new XSSFColor(Color.WHITE));
style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
XSSFFont font2 = workbook.createFont();
style2.setFont(font2);
XSSFRow row = sheet.createRow(0);
for(int i = 0; i < headers.length; ++i) {
XSSFCell cellHeader = row.createCell(i);
cellHeader.setCellStyle(style);
cellHeader.setCellValue(new XSSFRichTextString(headers[i]));
}
Iterator<T> it = dataset.iterator();
int index = 0;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
while(it.hasNext()) {
++index;
row = sheet.createRow(index);
T t = it.next();
Field[] fields = t.getClass().getDeclaredFields();
for(int 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);
Object value = getMethod.invoke(t);
String textValue = null;
if (value instanceof Integer) {
cell.setCellValue((double)(Integer)value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float)value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double)value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((double)(Long)value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean)value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date)value);
} else if (value != null) {
textValue = value.toString();
}
if (textValue != null) {
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
cell.setCellValue(Double.parseDouble(textValue));
} else {
XSSFRichTextString richString = new XSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException var40) {
var40.printStackTrace();
} catch (NoSuchMethodException var41) {
var41.printStackTrace();
} catch (IllegalArgumentException var42) {
var42.printStackTrace();
} catch (IllegalAccessException var43) {
var43.printStackTrace();
} catch (InvocationTargetException var44) {
var44.printStackTrace();
} finally {
;
}
}
}
try {
workbook.write(out);
} catch (IOException var39) {
var39.printStackTrace();
}
}
//导出2003版本Excel
public static <T> void exportExcel2003(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(title);
sheet.setDefaultColumnWidth(20);
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor((short)23);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setColor((short)9);
font.setFontHeightInPoints((short)11);
style.setFont(font);
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor((short)9);
style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font2 = workbook.createFont();
style2.setFont(font2);
HSSFRow row = sheet.createRow(0);
Iterator<T> it = dataset.iterator();
int index = 0;
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
while(it.hasNext()) {
T t = it.next();
Field[] fields = t.getClass().getDeclaredFields();
int i;
if (index == 0) {
for(i = 0; i < fields.length; ++i) {
Field headField = fields[i];
String headFieldName = headField.getName();
HSSFCell cellHeader = row.createCell(i);
cellHeader.setCellStyle(style);
cellHeader.setCellValue(new HSSFRichTextString(headFieldName));
}
}
++index;
row = sheet.createRow(index);
for(i = 0; i < fields.length; ++i) {
HSSFCell 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;
try {
getMethod = tCls.getMethod(getMethodName);
} catch (Exception var42) {
getMethodName = "get" + fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1);
getMethod = tCls.getMethod(getMethodName);
}
Object value = getMethod.invoke(t);
String textValue = null;
if (value instanceof Integer) {
cell.setCellValue((double)(Integer)value);
} else if (value instanceof Float) {
textValue = String.valueOf((Float)value);
cell.setCellValue(textValue);
} else if (value instanceof Double) {
textValue = String.valueOf((Double)value);
cell.setCellValue(textValue);
} else if (value instanceof Long) {
cell.setCellValue((double)(Long)value);
}
if (value instanceof Boolean) {
textValue = "是";
if (!(Boolean)value) {
textValue = "否";
}
} else if (value instanceof Date) {
textValue = sdf.format((Date)value);
} else if (value != null) {
textValue = value.toString();
}
if (textValue != null) {
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
} catch (SecurityException var43) {
var43.printStackTrace();
} catch (NoSuchMethodException var44) {
var44.printStackTrace();
} catch (IllegalArgumentException var45) {
var45.printStackTrace();
} catch (IllegalAccessException var46) {
var46.printStackTrace();
} catch (InvocationTargetException var47) {
var47.printStackTrace();
} finally {
;
}
}
}
try {
workbook.write(out);
} catch (IOException var41) {
var41.printStackTrace();
}
}
//得到某个工作簿
public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (".xls".equals(fileType)) {
wb = new HSSFWorkbook(inStr);
} else {
if (!".xlsx".equals(fileType)) {
throw new Exception("解析的文件格式有误!");
}
wb = new XSSFWorkbook(inStr);
}
return (Workbook)wb;
}
//得到某个工作薄的值
public static String getValue(Cell cell) {
if (cell == null) {
return null;
} else {
new SimpleDateFormat("yyyy-MM-dd");
String value = null;
CellType cellType = cell.getCellTypeEnum();
switch(cellType) {
case STRING:
value = cell.getStringCellValue();
break;
case BOOLEAN:
value = Value.getString(cell.getBooleanCellValue());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = DateHelper.formatDate(cell.getDateCellValue());
} else {
cell.setCellType(CellType.STRING);
value = cell.getStringCellValue();
}
case BLANK:
break;
case FORMULA:
try {
value = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException var6) {
value = String.valueOf(cell.getRichStringCellValue());
}
break;
default:
try {
value = String.valueOf(cell.getNumericCellValue());
} catch (IllegalStateException var5) {
value = String.valueOf(cell.getRichStringCellValue());
}
}
if (value != null) {
value = value.trim();
}
return value;
}
}
//下载Excel表到某个路径
public static void download(HttpServletResponse response, String fname, String fullPath) {
response.setContentType("application/x-download;charset=UTF-8");
HttpServletRequest request;
String userAgent;
try {
request = ContextHolderUtils.getRequest();
userAgent = request.getHeader("User-Agent");
if (userAgent.toLowerCase().indexOf("msie") <= 0 && userAgent.toLowerCase().indexOf("edge") <= 0) {
fname = new String(fname.getBytes(), "ISO8859-1");
} else {
fname = URLEncoder.encode(fname, "utf-8");
}
} catch (Exception var22) {
var22.printStackTrace();
}
response.addHeader("Content-Disposition", "attachment;filename=" + fname);
OutputStream out = null;
FileInputStream input = null;
try {
input = new FileInputStream(new File(fullPath));
out = response.getOutputStream();
int i = false;
byte[] b = new byte[1024];
int i;
while((i = input.read(b)) > 0) {
out.write(b, 0, i);
}
out.flush();
} catch (Exception var20) {
var20.printStackTrace();
} finally {
if (null != input) {
try {
input.close();
userAgent = null;
} catch (IOException var19) {
var19.printStackTrace();
}
}
if (null != out) {
try {
out.close();
request = null;
} catch (IOException var18) {
var18.printStackTrace();
}
}
}
}
}