报表导出功能在JavaWeb 开发中非常常见,网上搜索相应的代码也非常多,大多都相似:利用POI生成excel 文件到服务器,再利用InputStream和Response 返回给前端做处理。无聊中发现WorkBook的write方法API如下:
随有更改stream为Response.getOutputStream()来减少创建文件的开销。实现工具类如下:
package com.newtouch.dssp.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;
/**
* @description: 基于POI的Excel工具类
* @author: xiao.wang@newtouch.com
* @date: 2019/8/15
**/
public class ExcelUtil {
private static final String XML ="xls";
private static final String XLSX ="xlsx";
/**
* @description: //todo 下载Excel (样式为通用样式,sheet页是一个)
* @author: xiao.wang@newtouch.com
* @param resp response返回
* @param fileName 文件名 不需要后缀 默认是xlsx
* @param datas 数据
* @param titles 表头
* */
public static void exportExcel(HttpServletResponse resp, String fileName,
Collection<?> datas, List<String> titles)throws Exception{
resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
resp.addHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8")+ ".xlsx");
exportExcel(resp.getOutputStream(),datas,titles);
}
/**
* @description: //todo Excel 写入OutputStream
* @author: xiao.wang@newtouch.com
* @param out 输出流
* @param datas 数据
* @param titles 表头
* */
public static void exportExcel(OutputStream out,Collection<?> datas, List<String> titles){
XSSFWorkbook wb = new XSSFWorkbook();
try {
XSSFSheet sheet = wb.createSheet("new sheet");
writeDataToExcel(wb,sheet,datas,titles);
wb.write(out);
}catch (IOException ioe){
ioe.printStackTrace();
}
}
/**
* @description: // todo 读取上传的Excel文件
* @author: xiao.wang@newtouch.com
* @param file 上传的文件
* @param startRow 开始行 0开始
* @param startCell 开始列 0开始
* */
public static List<String[]> readExcel(MultipartFile file, int startRow, int startCell)throws Exception{
checkFile(file);
String fileName = file.getOriginalFilename();
Workbook workbook = getWorkBook(file,fileName);
List<S