封装一个poi导出excel的标准程序,excel样式没做过多处理,直接贴代码
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.xssf.streaming.SXSSFWorkbook;
/**
* @Author luohongchao
* @Date 下午3:29:49
*
*/
public class ExcelUtil {
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 功能描述:excel生成工具
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>public static <T> void toExcelOutStream(OutputStream out,String codedFileName,List<ExcelVo> header,List<T> datas){
<span style="white-space:pre"> </span>try
<span style="white-space:pre"> </span> {
<span style="white-space:pre"> </span> String oldCodedFileName = codedFileName;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> // 产生工作簿对象
<span style="white-space:pre"> </span> SXSSFWorkbook workbook = new SXSSFWorkbook(100);
<span style="white-space:pre"> </span> //产生工作表对象
<span style="white-space:pre"> </span> int sheetNum=1;
<span style="white-space:pre"> </span> Sheet sheet = workbook.createSheet(codedFileName +"-"+sheetNum++);
<span style="white-space:pre"> </span> // 进行转码,使其支持中文文件名
<span style="white-space:pre"> </span> codedFileName = java.net.URLEncoder.encode(codedFileName+DateUtil.getDateTimeStamp("_"), "UTF-8");
<span style="white-space:pre"> </span> generateExcelHeader(sheet, header, workbook);
<span style="white-space:pre"> </span> //插入数据
<span style="white-space:pre"> </span> Field field;
<span style="white-space:pre"> </span> int sheetLength=0;
<span style="white-space:pre"> </span> int rowNum = 1;
<span style="white-space:pre"> </span> for (int i = 1; i <= datas.size(); i++){
<span style="white-space:pre"> </span> T obj = datas.get(i-1);
<span style="white-space:pre"> </span> if(obj != null){
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> //加入超过单个Sheet的最大长度,则新城一个新的sheet页
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> sheetLength++;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> if(sheetLength>=65535){
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> sheetLength = 0;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> rowNum = 1;
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> sheet = workbook.createSheet(oldCodedFileName+"-" + sheetNum++);
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> generateExcelHeader(sheet, header, workbook);
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> Row row = sheet.createRow(rowNum++);//创建一行
<span style="white-space:pre"> </span> for(int j=0;j<header.size();j++){
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> Cell cell = row.createCell(j);//创建一列
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> cell.setCellType(header.get(j).getRowType());
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> field = obj.getClass().getDeclaredField(header.get(j).getRowNameEg());
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> field.setAccessible(true);
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> Object value = field.get(obj);
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> setCellValue(cell, value, header.get(j).getRowType());
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> workbook.write(out);
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span>out.flush();
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> catch (Exception e)
<span style="white-space:pre"> </span> {e.printStackTrace();}
<span style="white-space:pre"> </span> finally
<span style="white-space:pre"> </span> {
<span style="white-space:pre"> </span> try
<span style="white-space:pre"> </span> {
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> if(out != null){
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> out.close();
<span style="white-space:pre"> </span> <span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span> catch (IOException e)
<span style="white-space:pre"> </span> {e.printStackTrace();}
<span style="white-space:pre"> </span> }
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>private static void generateExcelHeader(Sheet sheet,List<ExcelVo> header,SXSSFWorkbook workbook){
<span style="white-space:pre"> </span> //单元格样式
CellStyle headerStyle = workbook.createCellStyle();
//字体样式
Font font= workbook.createFont();
font.setFontHeightInPoints((short)12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(font);
//生成头行
if(header !=null && header.size()>0){
<span style="white-space:pre"> </span> Row row = sheet.createRow(0);//创建一行
<span style="white-space:pre"> </span> for(int i=0;i<header.size();i++){
<span style="white-space:pre"> </span>Cell cell = row.createCell(i);//创建一列
<span style="white-space:pre"> </span>cell.setCellType(header.get(i).getRowType());
<span style="white-space:pre"> </span>cell.setCellValue(header.get(i).getRowNameCn());
<span style="white-space:pre"> </span>cell.setCellStyle(headerStyle);
<span style="white-space:pre"> </span>sheet.setColumnWidth(i, header.get(i).getCellWith());
<span style="white-space:pre"> </span> }
}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * @param response 响应流
<span style="white-space:pre"> </span> * @param codedFileName 文件名称,默认生成时间戳格式
<span style="white-space:pre"> </span> * @param header 头行数据
<span style="white-space:pre"> </span> * @param datas 表格数据
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>public static <T> void toExcelOutStream(HttpServletResponse response,String codedFileName,List<ExcelVo> header,List<T> datas){
<span style="white-space:pre"> </span> // 生成提示信息,
<span style="white-space:pre"> </span> response.setContentType("application/vnd.ms-excel");
<span style="white-space:pre"> </span> response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xlsx");
<span style="white-space:pre"> </span> try {
<span style="white-space:pre"> </span>toExcelOutStream(response.getOutputStream(), codedFileName, header, datas);
<span style="white-space:pre"> </span>} catch (IOException e) {
<span style="white-space:pre"> </span>e.printStackTrace();
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * @param cell 单元格
<span style="white-space:pre"> </span> * @param value 值
<span style="white-space:pre"> </span> * @param cellType 单元格类型
<span style="white-space:pre"> </span> * 设置单元格的值
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>private static void setCellValue(Cell cell,Object value,int cellType){
<span style="white-space:pre"> </span>if(cellType==HSSFCell.CELL_TYPE_STRING){
<span style="white-space:pre"> </span>cell.setCellValue(String.valueOf(value));
<span style="white-space:pre"> </span>}else if(cellType==HSSFCell.CELL_TYPE_NUMERIC){
<span style="white-space:pre"> </span>cell.setCellValue((double)value);
<span style="white-space:pre"> </span>}else if(cellType==HSSFCell.CELL_TYPE_BOOLEAN){
<span style="white-space:pre"> </span>cell.setCellValue((boolean)value);
<span style="white-space:pre"> </span>}else if(cellType==HSSFCell.CELL_TYPE_BLANK){
<span style="white-space:pre"> </span>cell.setCellValue("");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * @Author luohongchao
<span style="white-space:pre"> </span> * @Date 下午3:30:28
<span style="white-space:pre"> </span> * 生成excel所需的头信息
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>@Data
<span style="white-space:pre"> </span>@AllArgsConstructor
<span style="white-space:pre"> </span>@NoArgsConstructor
<span style="white-space:pre"> </span>public static class ExcelVo{
<span style="white-space:pre"> </span>public ExcelVo(String rowNameCn,int rowType,String rowNameEg){
<span style="white-space:pre"> </span>this.rowNameCn = rowNameCn;
<span style="white-space:pre"> </span>this.rowNameEg = rowNameEg;
<span style="white-space:pre"> </span>this.rowType = rowType;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 字段中文描述
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>private String rowNameCn;
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 字段类型
<span style="white-space:pre"> </span> * */
<span style="white-space:pre"> </span>private int rowType;
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 字段名
<span style="white-space:pre"> </span> * */
<span style="white-space:pre"> </span>private String rowNameEg;
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 列宽
<span style="white-space:pre"> </span> * */
<span style="white-space:pre"> </span>private int cellWith = 25 * 256;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
}