Springboot使用POI进行excel文件的导出与下载方式

<dependency>
    <groupid>com.deepoove</groupid>
    <artifactid>poi-tl</artifactid>
    <version>1.5.0</version>
</dependency>

import lombok.extern.slf4j.slf4j;
import org.apache.poi.hssf.usermodel.hssfcellstyle;
import org.apache.poi.hssf.usermodel.hssffont;
import org.apache.poi.hssf.util.hssfcolor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.sxssfcell;
import org.apache.poi.xssf.streaming.sxssfrow;
import org.apache.poi.xssf.streaming.sxssfsheet;
import org.apache.poi.xssf.streaming.sxssfworkbook;
import org.apache.poi.xssf.usermodel.xssfrichtextstring;
import javax.servlet.http.httpservletresponse;
import java.io.file;
import java.io.fileoutputstream;
import java.net.urlencoder;
import java.util.list;
import java.util.optional;

@slf4j
public final class excelexport {


	/**
	 * description: 依据excel模板，导出数据excel
	 * @param sheetname sheet 页名称
	 * @param headers 数据header部分
	 * @param datalist table 数据集合
	 * @param destfile	excel模板文件路径
	 */
	public static void export(string sheetname, string[] headers, list<list<object>> datalist, file destfile) throws exception {
		sxssfworkbook workbook = new sxssfworkbook();
		createsheet(sheetname, headers, datalist, workbook);
		workbook.write(new fileoutputstream(destfile));
	}


	/**
	 * 根据header与data的集合，动态地创建并生成excel数据流进行下载操作
	 * description: 导出excel --- 支持web
	 * @param sheetname  sheet表名字
	 * @param headers 表头
	 * @param datalist 表数据
	 * @param filename  导出文件名
	 * @param response
	 */
	public static void export(string sheetname , string[] headers , list<list<object>> datalist ,string filename
			 								   , httpservletresponse response) throws exception {
		sxssfworkbook workbook = new sxssfworkbook();
		createsheet(sheetname, headers, datalist, workbook);
		response.reset();
		//response.setcontenttype("application/vnd.ms-excel; charset=utf-8");
        response.setcontenttype("application/octet-stream; charset=utf-8");
		response.setheader("content-disposition", "attachment; filename=" + urlencoder.encode(filename, "utf-8"));
		workbook.write(response.getoutputstream());
        // 删除临时文件
        workbook.dispose();
	}

	/**
	 * description: 创建sheet表格
	 * @param sheetname  表sheet 名字
	 * @param headers  表头
	 * @param datalist 表数据
	 * @param wb
	 */
	public static void createsheet(string sheetname , string[] headers , list<list<object>> datalist , sxssfworkbook wb) {
		sxssfsheet sheet = wb.createsheet(sheetname);
		// 设置表头和单元格格式
		cellstyle headstyle = setheaderstyle(wb);
		cellstyle bodystyle = setbodystyle(wb);
		// 创建表头和单元格数据
        createheader(headers, sheet, headstyle);
        createbody(datalist, sheet, bodystyle,wb);
	}

	/**
	 * description: 创建表头
	 * @param headers
	 * @param sheet
	 * @param headstyle
	 */
	private static void createheader(string[] headers, sxssfsheet sheet, cellstyle headstyle) {
		sxssfrow row = sheet.createrow(0);
		row.setheightinpoints(16f);
		for (int i = 0; i < headers.length; i++) {
			// 创建单元格
			sxssfcell cell = row.createcell(i);
			cell.setcellstyle(headstyle);
			xssfrichtextstring text = new xssfrichtextstring(headers[i]);
            cell.setcellvalue(text);
            sheet.trackallcolumnsforautosizing();
            sheet.autosizecolumn(i);
		}
	}

	/**
	 * description: 表格中填充数据
	 * @param datalist
	 * @param sheet
	 * @param bodystyle
	 * @param wb 主要是更改文本格式
	 */
	private static void createbody(list<list<object>> datalist, sxssfsheet sheet, cellstyle bodystyle,sxssfworkbook wb) {
		if (datalist == null) {
			return;
		}
		for (int i = 0; i < datalist.size(); i++) {
			// 从第二行开始，第一行做表头
			sxssfrow row = sheet.createrow(i+1);
			list<object> rowlist = datalist.get(i);
			if (rowlist == null) {
				continue;
			}
			for (int j = 0; j < rowlist.size(); j++) {
				sxssfcell cell = row.createcell(j);
				object data = rowlist.get(j);
				// 如果数据是  double 类型，则需要保证金额数转换，否则导出数据显示为科学计数法  如： 8e7
				if(data instanceof double){
					dataformat format= wb.createdataformat();
					bodystyle.setdataformat(format.getformat("#,##0.00")); // 千位符
					// bodystyle.setdataformat(format.getformat("#0.00")); // 小数
					// bodystyle.setdataformat(format.getformat("0.00%")); // 百分比  数据必须是小数，如：0.59 -> 59%
					cell.setcellstyle(bodystyle);
					cell.setcellvalue(double.parsedouble(string.valueof(data)));
				}else{
					string textstr = optional.ofnullable(rowlist.get(j)).orelse("").tostring();
					xssfrichtextstring text = new xssfrichtextstring(textstr);
					cell.setcellvalue(text);
				}

				sheet.trackallcolumnsforautosizing();
				//设置内容列为列的最大值
				sheet.autosizecolumn(j);
			}
		}
	}

	/**
	 * description: 设置单元格内容样式
	 * @param wb
	 * @return hssfcellstyle
	 */
	private static cellstyle setbodystyle(sxssfworkbook wb) {
		// 设置表格单元格格式
		cellstyle style = wb.createcellstyle();
        style.setfillforegroundcolor(hssfcolor.white.index);
        style.setfillpattern(hssfcellstyle.solid_foreground);
        style.setborderbottom(hssfcellstyle.border_thin);
        style.setborderleft(hssfcellstyle.border_thin);
        style.setborderright(hssfcellstyle.border_thin);
        style.setbordertop(hssfcellstyle.border_thin);
        style.setalignment(hssfcellstyle.align_left);

        // 设置字体格式
        font font = wb.createfont();
        font.setfontname("宋体");
        font.setboldweight(hssffont.boldweight_normal);
        style.setfont(font);
        return style;
	}

	/**
	 * description: 设置表头样式
	 * @param wb
	 * @return
	 * @return hssfcellstyle
	 */
	private static cellstyle setheaderstyle(sxssfworkbook wb) {
		// 设置表格单元格格式
		cellstyle style = wb.createcellstyle();
		style.setverticalalignment(cellstyle.vertical_center);
		style.setborderright(borderstyle.thin);
		style.setrightbordercolor(indexedcolors.grey_50_percent.getindex());
		style.setborderleft(borderstyle.thin);
		style.setleftbordercolor(indexedcolors.grey_50_percent.getindex());
		style.setbordertop(borderstyle.thin);
		style.settopbordercolor(indexedcolors.grey_50_percent.getindex());
		style.setborderbottom(borderstyle.thin);
		style.setbottombordercolor(indexedcolors.grey_50_percent.getindex());
//		style.setfillbackgroundcolor(indexedcolors.light_yellow.getindex());
		style.setfillforegroundcolor(indexedcolors.light_yellow.getindex());
		style.setfillpattern(fillpatterntype.solid_foreground);
	    // 设置字体格式
	    font font = wb.createfont();
	    font.setfontname("宋体");
	    font.setfontheightinpoints((short) 10);
	    style.setfont(font);
	    return style;
	}
}

@getmapping("/test-xls")
public void contractintrqrypayxls(httpservletresponse response) throws exception {
    string[] headers = new string[]{"编号","名称","金额"};
    list<list<object>> datalists = new arraylist<>();
    for (int i = 0; i < 10; i++) {
        list<object> data = new arraylist<>();
        data.add("xj_"+i);
        data.add("xiangjiao "+i);
        data.add(10000.00*i);
        datalists.add(data);
    }
    excelexport.export("sheetname",headers,datalists,"xiangjiao测试.xls",response);
}