导出Excel

最新推荐文章于 2021-05-17 09:36:00 发布

Cxx941112

最新推荐文章于 2021-05-17 09:36:00 发布

阅读量329

点赞数

本文链接：https://blog.csdn.net/Cxx941112/article/details/86750226

版权

必需的4个类：操作工具类、excel工具类、注解类、导出报表类

操作工具类：

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.collections.CollectionUtils;
import org.apache.log4j.Logger;

/**

excel 操作工具类
*/
public class JxlUtil {

private static final Logger LOGGER = Logger.getLogger(JxlUtil.class);

//每个工作簿存放的记录数
private static final int SHEET_SIZE = 50000;

/**

相关说明：在指定路径下生成excel文件
业务逻辑：

时间：2015年7月14日下午10:59:18
*/
public static void write(String savePath, String title, String sheetName, String[] heads, List<String[]> dataList, int[] columnWidth) {
File saveFile = new File(savePath);
try {
if (!saveFile.exists()) {
WritableWorkbook workBook = Workbook.createWorkbook(saveFile);
WritableSheet workSheet = null;

     int dataIndex = 0;
     int sheetIndex = 0;
     if (CollectionUtils.isNotEmpty(dataList)) {
         for (int row = 0; row < dataList.size(); row++) {
             if (dataIndex % SHEET_SIZE == 0) {
                 workSheet = workBook.createSheet(sheetName + sheetIndex, sheetIndex);
                 workSheet.addCell(new Label(0, 0, title, getTitleHeaderFormat()));
                 //合并标题行
                 workSheet.mergeCells(0, 0, dataList.get(0).length - 1, 0);

                 if (heads != null && heads.length > 0) {
                     for (int col = 0; col < heads.length; col++) {
                         workSheet.addCell(new Label(col, 1, heads[col]));
                         workSheet.setColumnView(1, columnWidth[col]);
                     }
                 }
                 dataIndex = 0;
                 sheetIndex++;
             }

             String[] data = dataList.get(row);
             for (int col = 0; col < data.length; col++) {
                 workSheet.addCell(new Label(col, dataIndex + 2, data[col]));
             }
             dataIndex++;
         }
     }
     workBook.write();
     workBook.close();
 } else {
     Workbook book = Workbook.getWorkbook(saveFile);
     Sheet sheet = book.getSheet(0);
     // 获取行
     int length = sheet.getRows();
     System.out.println(length);
     WritableWorkbook wbook = Workbook.createWorkbook(saveFile, book); // 根据book创建一个操作对象
     WritableSheet sh = wbook.getSheet(0);// 得到一个工作对象

     int dataIndex = 1;
     for (int row = 0; row < dataList.size(); row++) {
         String[] data = dataList.get(row);
         for (int col = 0; col < data.length; col++) {
             sh.addCell(new Label(col, length + dataIndex, data[col]));
         }
         dataIndex++;
     }
     wbook.write();
     wbook.close();
 }

} catch (IOException e) {
LOGGER.error(e);
} catch (WriteException e) {
LOGGER.error(e);
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

/**

相关说明：excel下载
时间：2015年8月28日下午3:47:00

@throws Exception
*/
public static void export(OutputStream os, String title, String sheetName, List headList, List<List> dataList, int[] columnWidth) throws Exception {
WritableWorkbook workBook;
try {
workBook = Workbook.createWorkbook(os);
WritableSheet workSheet = null;
int dataIndex = 0;
int sheetIndex = 0;
if (CollectionUtils.isNotEmpty(dataList)) {
for (int row = 0; row < dataList.size(); row++) {
if (dataIndex % SHEET_SIZE == 0) {
workSheet = workBook.createSheet(sheetName + sheetIndex, sheetIndex);
//workSheet.addCell(new Label(0, 0, title, getTitleHeaderFormat()));
//合并标题行
//workSheet.mergeCells(0, 0, dataList.get(0).size() - 1, 0);

             if (CollectionUtils.isNotEmpty(headList)) {
                 for (int headIndex = 0; headIndex < headList.size(); headIndex++) {
                     workSheet.addCell(new Label(headIndex, 0, headList.get(headIndex)));
                     if (columnWidth != null && columnWidth.length > 0) {
                         workSheet.setColumnView(0, columnWidth[headIndex]);
                     }
                 }
             }
             dataIndex = 0;
             sheetIndex++;
         }

         List<Object> data = dataList.get(row);
         for (int col = 0; col < data.size(); col++) {
             workSheet.addCell(new Label(col, dataIndex + 1, String.valueOf(data.get(col))));
         }
         dataIndex++;
     }
 } else {
     workSheet = workBook.createSheet(sheetName + sheetIndex, sheetIndex);
     //  workSheet.addCell(new Label(0, 0, title, getTitleHeaderFormat()));
     //合并标题行
     // workSheet.mergeCells(0, 0, headList.size() - 1, 0);

     if (CollectionUtils.isNotEmpty(headList)) {
         for (int headIndex = 0; headIndex < headList.size(); headIndex++) {
             workSheet.addCell(new Label(headIndex, 0, headList.get(headIndex)));
             if (columnWidth != null && columnWidth.length > 0) {
                 workSheet.setColumnView(0, columnWidth[headIndex]);
             }
         }
     }
 }
 workBook.write();// 写入数据
 workBook.close();

} catch (Exception e) {
e.printStackTrace();
throw e;
}
}

/**

相关说明：格式化标题
业务逻辑：
时间：2015年7月20日下午4:30:04
*/
private static WritableCellFormat getTitleHeaderFormat() {
WritableCellFormat headerFormat = null;
try {
//1.设置excel标题
headerFormat = new WritableCellFormat();
//水平居中对齐
headerFormat.setAlignment(Alignment.CENTRE);
//竖直方向居中对齐
headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
WritableFont font = new WritableFont(WritableFont.createFont(“宋体”), 20,
WritableFont.BOLD,
false,
UnderlineStyle.NO_UNDERLINE);
headerFormat.setFont(font);
} catch (WriteException e) {
LOGGER.error(e);
}
return headerFormat;
}

/**

相关说明：excel下载(无标题)
开发者：zhangpenghui
时间：2015年8月28日下午3:47:00

@throws Exception
*/
public static void export(OutputStream os, String sheetName, List headList, List<List> dataList, int[] columnWidth) throws Exception {
WritableWorkbook workBook;
try {
workBook = Workbook.createWorkbook(os);
WritableSheet workSheet = null;
int dataIndex = 0;
int sheetIndex = 0;

 if (CollectionUtils.isNotEmpty(dataList)) {
     for (int row = 0; row < dataList.size(); row++) {
         if (dataIndex % SHEET_SIZE == 0) {
             workSheet = workBook.createSheet(sheetName + sheetIndex, sheetIndex);
             if (CollectionUtils.isNotEmpty(headList)) {
                 for (int headIndex = 0; headIndex < headList.size(); headIndex++) {
                     workSheet.addCell(new Label(headIndex, 0, headList.get(headIndex)));
                     if (columnWidth != null && columnWidth.length > 0) {
                         workSheet.setColumnView(1, columnWidth[headIndex]);
                     }
                 }
             }
             dataIndex = 0;
             sheetIndex++;
         }

         List<Object> data = dataList.get(row);
         for (int col = 0; col < data.size(); col++) {
             workSheet.addCell(new Label(col, dataIndex + 1, String.valueOf(data.get(col))));
         }
         dataIndex++;
     }
 } else {
     workSheet = workBook.createSheet(sheetName + sheetIndex, sheetIndex);
     if (CollectionUtils.isNotEmpty(headList)) {
         for (int headIndex = 0; headIndex < headList.size(); headIndex++) {
             workSheet.addCell(new Label(headIndex, 0, headList.get(headIndex)));
             if (columnWidth != null && columnWidth.length > 0) {
                 workSheet.setColumnView(1, columnWidth[headIndex]);
             }
         }
     }
 }
 workBook.write();// 写入数据
 workBook.close();

} catch (Exception e) {
e.printStackTrace();
throw e;
}
}

public static void export1(OutputStream os, String sheetName, List<Map<String, Object>> dataList, int[] columnWidth) throws Exception {
List<List> datas = new LinkedList<>();
List headList = new LinkedList<>();

 Set<String> strings = new HashSet<>();
 for (Map<String, Object> map : dataList) {
     strings = map.keySet();

     Iterator it = map.values().iterator();
     List<Object> data = new LinkedList<>();
     while (it.hasNext()) {

         data.add(it.next());

     }
     datas.add(data);
 }
 headList.addAll(strings);
 export(os, sheetName, headList, datas, columnWidth);

}

excel工具类

import cn.com.njits.api.util.excel.ExcelAnnotation;
import org.apache.poi.hssf.usermodel.;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

/**

excel工具类
@param
@author wangkecheng
*/
public class ExcelUtil {

public static final int EXPORT_07_LEAST_SIZE = 50000;

/**
- 功能 :获取表单导出数据
- 开发：wangkecheng
- @param list 数据列表
- @param title 首行标题
- @param className 实体对象class
- @param exportType 模板标号
- @return
- @throws Exception
  */
  public HSSFWorkbook exportExcel(List list, String title, Class className, Integer exportType) throws Exception {
  // 获取属性
  Field[] fields = className.getDeclaredFields();
  List fieldList = new ArrayList();
  for (Field fie : fields) {
  if (fie.isAnnotationPresent(ExcelAnnotation.class)) {
  fieldList.add(fie);
  }
  }
  // 按照id进行排序
  Collections.sort(fieldList, new Comparator() {
  @Override
  public int compare(Field f1, Field f2) {
  return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();
  }
  });
  int columnsize = fieldList.size(), rowindex = 0;
  // 创建一个HSSFWorbook对象（excel的文档对象）
  HSSFWorkbook hWorkbook = new HSSFWorkbook();
  // 创建一个HSSFSheet对象（excll的表单）
  HSSFSheet hSheet = hWorkbook.createSheet();
  // 创建行（excel的行）
  HSSFRow hRow = hSheet.createRow(rowindex++);
  //设置行高度
  hRow.setHeight((short) 380);
  // 创建单元格（从0开始）
  HSSFCell hCell = hRow.createCell((short) 0);
  //样式对象
  HSSFCellStyle cellStyle = getCellStyle(hWorkbook, (short) 300, (short) 500);
  // 将上面获得的样式对象给对应单元格
  hCell.setCellStyle(cellStyle);
  //设置标题行
  hCell.setCellValue(title);
  
  if (getHuoResult(fieldList.isEmpty(), list == null, list.isEmpty())) {
  return hWorkbook;
  }
  
  //创建第二行，代表列名
  hRow = hSheet.createRow(rowindex++);
  cellStyle = getCellStyle(hWorkbook, (short) 270, (short) 500);
  generateTitle(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);
  
  //组装excel的数据
  cellStyle = getCellStyle(hWorkbook, (short) 220, (short) 500);// 设置单元格格式
  generateData(list, fieldList, columnsize, rowindex, hSheet, cellStyle);
  
  /**
  - 第1个参数：从哪一行开始
  - 第2个参数：到哪一行结束
  - 第3个参数：从哪一列开始
  - 第4个参数：到哪一列结束
    */
    hSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnsize - 1));
  // 固定表头（前一个参数代表列，后一个参数单表行）
  hSheet.createFreezePane(0, 1);
  return hWorkbook;
  }
/**
- 功能：組裝列明
- @param exportType 模板编号
- @param fieldList 列名
- @param columnsize 列数
- @param hSheet sheet页
- @param hRow 行
- @param cellStyle 样式
  */
  private void generateTitle(Integer exportType, List fieldList, int columnsize, HSSFSheet hSheet, HSSFRow hRow,
  HSSFCellStyle cellStyle) {
  HSSFCell hCell;
  for (int i = 0; i < columnsize; i++) {
  Field field = fieldList.get(i);
  if (field.isAnnotationPresent(ExcelAnnotation.class)) {
  // 获取该字段的注解对象
  ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);
  hCell = hRow.createCell((short) i);
  String colName = field.getAnnotation(ExcelAnnotation.class).name().length > exportType
  ? field.getAnnotation(ExcelAnnotation.class).name()[exportType]
  : field.getAnnotation(ExcelAnnotation.class).name()[0];
  hCell.setCellValue(colName);
  hCell.setCellStyle(cellStyle);
  hSheet.setColumnWidth((short) i, (short) anno.width());
  }
  }
  }
/**
- 组装excel的数据
- @param list 具体数据
- @param fieldList 列名
- @param columnsize 列数
- @param rowindex 行数计数
- @param hSheet sheet页
- @param cellStyle 样式
- @return
- @throws NoSuchMethodException
- @throws IllegalAccessException
- @throws InvocationTargetException
  */
  private int generateData(List list, List fieldList, int columnsize, int rowindex, HSSFSheet hSheet,
  HSSFCellStyle cellStyle) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
  HSSFRow hRow;
  HSSFCell hCell;
  for (Object model : list) {
  hRow = hSheet.createRow(rowindex++);
  //获取该类
  Class clazz = model.getClass();
  for (int i = 0; i < columnsize; i++) {
  Field field = fieldList.get(i);
  //获取方法名
  String methodName = “get” + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1);
  Method method = clazz.getMethod(methodName);
  try {
  // 获取该字段的注解对象
  Object result = method.invoke(model);
  hCell = hRow.createCell((short) i);
  if (result != null) {
  if (result.getClass().isAssignableFrom(Date.class)) {
  SimpleDateFormat format = new SimpleDateFormat(“yyyy年MM月dd日 HH时mm分ss秒”);
  result = format.format(result);
  }
  hCell.setCellValue(new HSSFRichTextString(result.toString()));
  } else {
  hCell.setCellValue(new HSSFRichTextString("-"));
  }
  hCell.setCellStyle(cellStyle);
  } catch (IllegalArgumentException e) {
  System.out.println(e.getMessage());
  }
  }
  }
  return rowindex;
  }
/**
- 生成07格式的excel对象使用流方式防止内存溢出
- @param list
- @param title
- @param className
- @param exportType
- @return
- @throws Exception
  */
  public SXSSFWorkbook exportExcel07S(List list, String title, Class className, Integer exportType) throws Exception {
  // 获取属性
  Field[] fields = className.getDeclaredFields();
  List fieldList = new ArrayList();
  for (Field fie : fields) {
  if (fie.isAnnotationPresent(ExcelAnnotation.class)) {
  fieldList.add(fie);
  }
  }
  // 按照id进行排序
  Collections.sort(fieldList, new Comparator() {
  @Override
  public int compare(Field f1, Field f2) {
  return f1.getAnnotation(ExcelAnnotation.class).id() - f2.getAnnotation(ExcelAnnotation.class).id();
  }
  });
  
  int columnsize = fieldList.size(), rowindex = 0;
  // 创建一个HSSFWorbook对象s
  SXSSFWorkbook hWorkbook = new SXSSFWorkbook();
  // 创建一个HSSFSheet对象（sheet页）
  Sheet hSheet = hWorkbook.createSheet();
  // 创建第一行(此行作为头)
  Row hRow = hSheet.createRow(rowindex++);
  hRow.setHeight((short) 380);
  // 创建单元格（第一（0）个）
  Cell hCell = hRow.createCell((short) 0);
  // 设置样式
  CellStyle cellStyle = getCellStyle07S(hWorkbook, (short) 300, (short) 500);
  // 将上面获得的样式对象给对应单元格
  hCell.setCellStyle(cellStyle);
  //设置标题行
  hCell.setCellValue(title);
  
  if (getHuoResult(fieldList.isEmpty(), list == null, list.isEmpty())) {
  return hWorkbook;
  }
  
  // 创建第二列，列名
  hRow = hSheet.createRow(rowindex++);
  cellStyle = getCellStyle07S(hWorkbook, (short) 270, (short) 500);
  createTitle07S(exportType, fieldList, columnsize, hSheet, hRow, cellStyle);
  
  //生成数据
  cellStyle = getCellStyle07S(hWorkbook, (short) 220, (short) 500);// 设置单元格格式
  dealCreateRow07S(list, fieldList, columnsize, rowindex, hSheet, cellStyle);
  
  /**
  - 第1个参数：从哪一行开始
  - 第2个参数：到哪一行结束
  - 第3个参数：从哪一列开始
  - 第4个参数：到哪一列结束
    */
    hSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnsize - 1));
  // 固定表头（前一个参数代表列，后一个参数单表行）
  hSheet.createFreezePane(0, 1);
  return hWorkbook;
  }
private int dealCreateRow07S(List list, List fieldList, int columnsize, int rowindex, Sheet hSheet,
CellStyle cellStyle) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException {
Row hRow;
Cell hCell;
for (Object model : list) {
hRow = hSheet.createRow(rowindex++);
// 获取该类并获取自身方法
Class clazz = model.getClass();
for (int i = 0; i < columnsize; i++) {
Field field = fieldList.get(i);
String methodName = “get” + field.getName().substring(0, 1).toUpperCase()
+ field.getName().substring(1);
Method method = clazz.getMethod(methodName);
try {
// 获取该字段的注解对象
Object result = method.invoke(model);
hCell = hRow.createCell((short) i);
if (result != null) {
if (result.getClass().isAssignableFrom(Date.class)) {
SimpleDateFormat format = new SimpleDateFormat(“yyyy年MM月dd日”);
result = format.format(result);
}
hCell.setCellValue(new XSSFRichTextString(result.toString()));
} else {
hCell.setCellValue(new XSSFRichTextString("-"));
}
hCell.setCellStyle(cellStyle);
} catch (IllegalArgumentException e) {
System.out.println(e.getMessage());
}
}
}
return rowindex;
}

/**
- 生成列名
- @param exportType 模板编号
- @param fieldList 列名
- @param columnsize 列数
- @param hSheet
- @param hRow
- @param cellStyle
  */
  private void createTitle07S(Integer exportType, List fieldList, int columnsize, Sheet hSheet, Row hRow,
  CellStyle cellStyle) {
  Cell hCell;
  for (int i = 0; i < columnsize; i++) {
  Field field = (Field) fieldList.get(i);
  if (field.isAnnotationPresent(ExcelAnnotation.class)) {
  // 获取该字段的注解对象
  ExcelAnnotation anno = field.getAnnotation(ExcelAnnotation.class);
  hCell = hRow.createCell((short) i);
  String colName = field.getAnnotation(ExcelAnnotation.class).name().length > exportType
  ? field.getAnnotation(ExcelAnnotation.class).name()[exportType]
  : field.getAnnotation(ExcelAnnotation.class).name()[0];
  hCell.setCellValue(colName);
  hCell.setCellStyle(cellStyle);
  hSheet.setColumnWidth((short) i, (short) anno.width());
  }
  }
  }
/**
- 功能 :设置excel表格默认样式
- @param hWorkbook 需导出Excel数据
- @param fontHeight 字体粗度
- @param boldWeight 表格线的粗度
- @return
  */
  public HSSFCellStyle getCellStyle(HSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {
  HSSFCellStyle cellStyle;
  HSSFFont font;
  cellStyle = hWorkbook.createCellStyle();
  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
  font = hWorkbook.createFont();
  font.setFontHeight(fontHeight);
  font.setBoldweight(boldWeight);
  font.setFontName(“宋体”);
  cellStyle.setFont(font);
  cellStyle.setWrapText(true);
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  return cellStyle;
  }
/**
- 功能 :设置excel 07表格默认样式
- @param hWorkbook 需导出Excel数据
- @param fontHeight 字体粗度
- @param boldWeight 表格线的粗度
- @return
  */
  public CellStyle getCellStyle07S(SXSSFWorkbook hWorkbook, short fontHeight, short boldWeight) {
  CellStyle cellStyle;
  Font font;
  cellStyle = hWorkbook.createCellStyle();
  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
  font = hWorkbook.createFont();
  font.setFontHeight(fontHeight);
  font.setBoldweight(boldWeight);
  font.setFontName(“宋体”);
  cellStyle.setFont(font);
  cellStyle.setWrapText(true);
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  return cellStyle;
  }
/*
- 获取或运算结果
  */
  private static boolean getHuoResult(Boolean… bs) {
  for (boolean b : bs) {
  if (b) {
  return b;
  }
  }
  return false;
  }
  }

注解类
import java.lang.annotation.*;
/**

功能：excel模板设置

*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {
//Excel列ID(Excel列排序序号)
int id();
//Excel列名
String[] name();
//Excel列宽
int width() default 5000;
}

导出excel类
import cn.com.njits.api.util.MyExcelUtil;
import cn.com.njits.api.util.excel.util.ExcelUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
功能描述：导出报表
/
public class ReportExcel {
/
* 功能: Excel导出公共方法
* 记录条数大于50000时导出.xlsx文件(excel07+) 小于等于50000时导出 .xls文件(excel97-03)
* 开发：wangkecheng
* @param list 需要导出的列表数据
* @param title 导出文件的标题
* @param className 导出对象的类名
* @param exportType 针对同一个pojo可能有多个不同的导出模板时,可以通过此属性来决定导出哪一套模板，默认第一套
* @param response 用来获取输出流
* @param request 针对火狐浏览器导出时文件名乱码的问题,也可以不传入此值
* @throws IOException
*/
public void excelExport(List list, String title, Class className, Integer exportType, HttpServletResponse response, HttpServletRequest request) throws IOException {
OutputStream out = response.getOutputStream();
try {
ExcelUtil excel = new ExcelUtil();
if(list!=null && list.size()>ExcelUtil.EXPORT_07_LEAST_SIZE){
dealBigNumber(list, title, className, exportType, response, request, out, excel);
}else{
HSSFWorkbook hss = new HSSFWorkbook();
if(exportTypenull){
hss = excel.exportExcel(list,title,className,0);
}else{
hss = excel.exportExcel(list, title, className, exportType);
}
String disposition = “attachment;filename=”;
if(request!=null&&request.getHeader(“USER-AGENT”)!=null&& StringUtils.contains(request.getHeader(“USER-AGENT”), “Firefox”)){
disposition += new String((title+".xls").getBytes(),“ISO8859-1”);
}else{
disposition += URLEncoder.encode(title+".xls", “UTF-8”);
}
response.setContentType(“application/vnd.ms-excel;charset=UTF-8”);
response.setHeader(“Content-disposition”, disposition);
hss.write(out);
}
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
out.close();
}
}
private void dealBigNumber(List list, String title, Class className, Integer exportType,
HttpServletResponse response, HttpServletRequest request, OutputStream out, ExcelUtil excel)
throws Exception{
SXSSFWorkbook hss;
if(exportTypenull){
hss = excel.exportExcel07S(list,title,className,0);
}else{
hss = excel.exportExcel07S(list, title, className, exportType);
}
String disposition = “attachment;filename=”;
if(request!=null && request.getHeader(“USER-AGENT”) != null && StringUtils.contains(request.getHeader(“USER-AGENT”), “Firefox”)){
disposition += new String((title+".xlsx").getBytes(),“ISO8859-1”);
}else{
disposition += URLEncoder.encode(title+".xlsx", “UTF-8”);
}
response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8”);
response.setHeader(“Content-disposition”, disposition);
hss.write(out);
}
}

导出事例（SpringBoot框架）：
实体类：
package cn.com.njits.api.entity.system;

import cn.com.njits.api.util.excel.ExcelAnnotation;
import io.swagger.annotations.ApiModel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.Value;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ApiModel(value = “CustomerSysLogQueryPO”, description = “系统日志搜索类”)
public class CustomerSysLogQueryPO {
//@ExcelAnnotation(id=1,name={“编号”},width = 10000)
private String id;
@ExcelAnnotation(id=1,name={“用户名”},width = 5000)
private String userName;
@ExcelAnnotation(id=1,name={“姓名”},width = 5000)
private String fullName;
@ExcelAnnotation(id=1,name={“操作时间”},width = 10000)
// @DateTimeFormat(pattern = “yyyy-MM-dd HH:mm:ss”)
private Date createTime;
@ExcelAnnotation(id=1,name={“操作描述”},width = 15000)
private String processContent;
@ExcelAnnotation(id=1,name={“IP地址”},width = 5000)
private String ip;
}

控制器：
@ApiOperation(value = “系统设置-日志管理-导出”)
@ApiImplicitParams({
@ApiImplicitParam(name = “processContent”, value = “操作描述”, type = “query”),
@ApiImplicitParam(name = “userName”, value = “登录名”, type = “query”),
@ApiImplicitParam(name = “fullName”, value = “用户名”, type = “query”),
@ApiImplicitParam(name = “startTime”, value = “开始时间”, type = “query”),
@ApiImplicitParam(name = “endTime”, value = “结束时间”, type = “query”)})
@RequestMapping(value = “sysLog/export”, method = RequestMethod.GET)
@ResponseBody
public void export(@RequestParam(name = “processContent”, required = false) String processContent,
@RequestParam(name = “userName”, required = false) String userName,
@RequestParam(name = “fullName”, required = false) String fullName,
@RequestParam(name = “startTime”, required = false) Long startTime,
@RequestParam(name = “endTime”, required = false) Long endTime,
HttpServletRequest request, HttpServletResponse response) throws Exception {
List customerSysLogQueryPOS = customerSysLogMapper.querySysLog(processContent, userName, fullName, startTime, endTime);
ReportExcel reportExcel = new ReportExcel();
reportExcel.excelExport(customerSysLogQueryPOS, “系统日志列表”, CustomerSysLogQueryPO.class, 1, response, request);
}