前几天用layui框架做列表导出Excel的功能,但是发现复杂的表头无法使用自带插件导出,然后在搞了下面的代码,可以支持复杂的表头导出,直接作为工具类用就好了
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.security.SecureRandom;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import com.vingsoft.iframework.utils.Decompression;
public abstract class ExportExcelPro {
/**
*
* @description 导出 Excel表格 工具方法,A:支持 多行列标题 B:支持列标题中的合并单元格 C:支持数据项为Map类型
* @author taorui
* @date 2016-3-4 上午11:32:19
* @param sheetTitle 工作表名称
* @param entityList 数据集合<java.util.List<Class<?>>> 或者 <java.util.List<Map<String,Object>>>
* @param attrname 属性列表组成的字符串,每个属性之间用中文逗号分隔
* @param titleArr 列标题名【英文逗号分隔的字符串】的【数组】 如果列占用1行1列,则直接使用列名, 否则 使用 : 列名|占用行数|占用列数
* 例如:
* String title1 = "AB|2|1,CD,其它,FF"; // 第一行标题
* String title2 = "年龄|1|2,学校";// 第二行标题
* new String[]{title1,title2} 传入方法
* @param response <javax.servlet.http.HttpServletResponse>
* @param clazz List数据项【bean的实体类型】,如果不使用实体类 而是使用 Map<String,Object> 则传入null即可
* 建议使用 HashMap,方便且效率高,减少反射
* @throws Exception
* @return void
*/
@SuppressWarnings({ "unchecked", "rawtypes" })
public static void exportExcel(String sheetTitle, List entityList,String attrname, String[] titleArr,
HttpServletResponse response,Class clazz) throws Exception {
CellStyle dataStyle = null;
SimpleDateFormat myFormatter = new SimpleDateFormat("yyyyMMddHHmmss");
Workbook wb = new HSSFWorkbook();
Sheet s = wb.createSheet(sheetTitle);
List<String> usedList = new ArrayList<String>();
// 标题字体加粗
Font boldFont = wb.createFont();
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFont(boldFont);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headerStyle.setBorderTop(CellStyle.BORDER_THIN);
headerStyle.setBorderRight(CellStyle.BORDER_THIN);
headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
dataStyle = wb.createCellStyle();
dataStyle.setBorderTop(CellStyle.BORDER_THIN);
dataStyle.setBorderRight(CellStyle.BORDER_THIN);
dataStyle.setBorderBottom(CellStyle.BORDER_THIN);
dataStyle.setBorderLeft(CellStyle.BORDER_THIN);
// 写入标题
Row r = null;
String[] attrnames = attrname.split(",");
int rowNum = 0;
for(int t=0;t<titleArr.length;t++){// 遍历每行 标题
String[] titles = titleArr[t].split(",");
r = s.getRow(rowNum);
if(r==null){
r = s.createRow(rowNum);
}
int colNum = 0;
for (int i = 0; i < titles.length; i++) {
String[] title = titles[i].split("\\|");
// 首先判断本单元格是否已经使用
while(usedList.contains(rowNum+","+colNum)){
colNum++;// 被使用就到下一个单元格
}
if(title.length==3){
int rows = Integer.valueOf(title[1]);
int cols = Integer.valueOf(title[2]);
CellRangeAddress cra=new CellRangeAddress(rowNum, rowNum+rows-1, colNum, colNum+cols-1);
s.addMergedRegion(cra);
RegionUtil.setBorderBottom(CellStyle.BORDER_THIN,cra, s, wb);
RegionUtil.setBorderLeft(CellStyle.BORDER_THIN,cra, s, wb);
RegionUtil.setBorderRight(CellStyle.BORDER_THIN,cra, s, wb);
RegionUtil.setBorderTop(CellStyle.BORDER_THIN,cra, s, wb);
for(int u=rowNum;u<rowNum+rows;u++){
for(int v=colNum;v<colNum+cols;v++){
if(u==rowNum && v==colNum)
continue;
usedList.add(u+","+v);
}
}
}
//s.autoSizeColumn(i);// 设定所有Column宽度自动配合内容宽度
Cell c1 = r.createCell(colNum++);
c1.setCellValue(title[0]);
c1.setCellStyle(headerStyle);
}
rowNum++;
}
// 写入标题结束 ................................
// 开始写入数据
int size = entityList.size();
// 数据量超过 15000 条,就分多个 excel 导出
if (size > 15000) {
int count = 2;
File dir = new File("excel");
dir.mkdir();
FileOutputStream out = new FileOutputStream("excel"+File.separator+"1.xls");
Object obj = null;
int k = 0;
for (int j = 0; j < entityList.size(); j++) {
obj = (Object) entityList.get(j);
r = s.getRow(rowNum);
if(r==null){
r = s.createRow(rowNum++);
}
Object ss = null;
for (int i = 0; i < attrnames.length; i++) {
Cell c1 = r.createCell(i);
c1.setCellStyle(dataStyle);
if(clazz!=null){
String getterMethodName = "get" + StringUtils.capitalize(attrnames[i]);
ss = clazz.getMethod(getterMethodName).invoke(obj);
}else{
Map data = (Map) obj;
ss = data.get(attrnames[i]);
}
if (null == ss)
c1.setCellValue("");
else
c1.setCellValue(ss.toString());
}
k++;
if (k == 15000) {
wb.write(out);
out.close();
out = new FileOutputStream("excel"+ File.separator + count + ".xls");
k = 0;
count++;
wb = new HSSFWorkbook();
s = wb.createSheet(sheetTitle);
dataStyle = wb.createCellStyle();
dataStyle.setBorderTop(CellStyle.BORDER_THIN);
dataStyle.setBorderRight(CellStyle.BORDER_THIN);
dataStyle.setBorderBottom(CellStyle.BORDER_THIN);
dataStyle.setBorderLeft(CellStyle.BORDER_THIN);
// 写入标题
usedList = new ArrayList<String>();
// 标题字体加粗
boldFont = wb.createFont();
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerStyle = wb.createCellStyle();
headerStyle.setFont(boldFont);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headerStyle.setBorderTop(CellStyle.BORDER_THIN);
headerStyle.setBorderRight(CellStyle.BORDER_THIN);
headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
// 写入标题
rowNum = 0;
for(int t=0;t<titleArr.length;t++){// 遍历每行 标题
String[] titles = titleArr[t].split(",");
r = s.getRow(rowNum);
if(r==null){
r = s.createRow(rowNum);
}
int colNum = 0;
for (int i = 0; i < titles.length; i++) {
String[] title = titles[i].split("\\|");
// 首先判断本单元格是否已经使用
while(usedList.contains(rowNum+","+colNum)){
colNum++;// 被使用就到下一个单元格
}
if(title.length==3){
int rows = Integer.valueOf(title[1]);
int cols = Integer.valueOf(title[2]);
CellRangeAddress cra=new CellRangeAddress(rowNum, rowNum+rows-1, colNum, colNum+cols-1);
s.addMergedRegion(cra);
RegionUtil.setBorderBottom(CellStyle.BORDER_THIN,cra, s, wb);
RegionUtil.setBorderLeft(CellStyle.BORDER_THIN,cra, s, wb);
RegionUtil.setBorderRight(CellStyle.BORDER_THIN,cra, s, wb);
RegionUtil.setBorderTop(CellStyle.BORDER_THIN,cra, s, wb);
for(int u=rowNum;u<rowNum+rows;u++){
for(int v=colNum;v<colNum+cols;v++){
if(u==rowNum && v==colNum)
continue;
usedList.add(u+","+v);
}
}
}
//s.autoSizeColumn(i);// 设定所有Column宽度自动配合内容宽度
Cell c1 = r.createCell(colNum++);
c1.setCellValue(title[0]);
c1.setCellStyle(headerStyle);
}
rowNum++;
}
// 写入标题结束
}
}
wb.write(out);
out.close();
response.setContentType("application/x-download");
response.addHeader("Content-Disposition","attachment;filename=" + myFormatter.format(new Date())
+ new SecureRandom().nextInt() + ".zip");
InputStream in = null;
OutputStream ou = null;
int len = -1;
byte[] buffer = new byte[1024];
Decompression decompression = new Decompression();
decompression.zip("excel", "excel.zip");
in = new FileInputStream("excel.zip");
ou = response.getOutputStream();
while ((len = in.read(buffer)) != -1) {
ou.write(buffer);
}
ou.flush();
in.close();
ou.close();
clear("excel");
clear("excel.zip");
return;
} else {
// 数据量小于 15000 单个excel导出
Object obj = null;
for (int j = 0; j < entityList.size(); j++) {
obj = entityList.get(j);
r = s.getRow(rowNum);
if(r==null){
r = s.createRow(rowNum++);
}
Object ss = null;
for (int i = 0; i < attrnames.length; i++) {
Cell c1 = r.createCell(i);
c1.setCellStyle(dataStyle);
if(clazz!=null){
String getterMethodName = "get" + StringUtils.capitalize(attrnames[i]);
ss = clazz.getMethod(getterMethodName).invoke(obj);
}else{
Map data = (Map) obj;
ss = data.get(attrnames[i]);
}
if (null == ss)
c1.setCellValue("");
else
c1.setCellValue(ss.toString());
}
}
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment; filename=" + myFormatter.format(new Date())
+ new SecureRandom().nextInt() + ".xls");
wb.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
}
}
public static void clear(String path) {
File file = new File(path);
if (file.isDirectory()) {
File[] files = file.listFiles();
for (File f : files) {
f.delete();
}
}
file.delete();
}
public static void deleteFile(String path) {
File file = new File(path);
// 路径为文件且不为空则进行删除
if (file.isFile() && file.exists()) {
file.delete();
}
}
}