springboot使用jxls导出excel

1.引入依赖

        <!--springboot版本-->	
        <parent>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-parent</artifactId>
		    <version>2.0.6.RELEASE</version>
		    <relativePath />
	    </parent>
		
        <!-- jxls@excel -->
		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls</artifactId>
			<version>2.3.0</version>
		</dependency>

		<dependency>
			<groupId>org.jxls</groupId>
			<artifactId>jxls-poi</artifactId>
			<version>1.0.9</version>
		</dependency>

		<dependency>
			<groupId>net.sf.jxls</groupId>
			<artifactId>jxls-core</artifactId>
			<version>1.0.5</version>
		</dependency>

2.excel工具类

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;
import java.util.UUID;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;

public class ExcelUtil {

	/**
	 * 下载excel
	 *
	 * @author Wu JiaXin
	 * @date 2018年12月6日
	 * @param sourcePath	模板路径
	 * @param beanParams	excel内容
	 * @return
	 * @throws ParsePropertyException
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	public static ResponseEntity<byte[]> downLoadExcel(String sourcePath, Map<String, Object> beanParams)
			throws ParsePropertyException, InvalidFormatException, IOException {
	ByteArrayOutputStream os = new ByteArrayOutputStream();
	//读取模板
	InputStream is =ExcelUtil.class.getClassLoader().getResourceAsStream(sourcePath);
	XLSTransformer transformer = new XLSTransformer();
	//向模板中写入内容
	Workbook workbook = transformer.transformXLS(is, beanParams);
	//写入成功后转化为输出流
	workbook.write(os);
	//配置Response信息
	HttpHeaders headers = new HttpHeaders();
	String downloadFileName = UUID.randomUUID().toString() + ".xlsx";
	//防止中文名乱码
	downloadFileName = new String(downloadFileName.getBytes("UTF-8"), "ISO-8859-1");
	headers.setContentDispositionFormData("attachment", downloadFileName);
	headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
	//返回
	return new ResponseEntity<byte[]>(os.toByteArray(), headers, HttpStatus.CREATED);
}

3.controller调用

	@RequestMapping("/export")
	//返回ResponseEntity<byte[]>使浏览器下载
	public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
		//查询参数
		Map<String, Object> params = new HashMap<String, Object>();
		//结果集
		List<Aip_std> list = stdService.selectAllForExportExcel(params);
		Map<String, Object> beanParams = new HashMap<String, Object>();
		beanParams.put("list", list);
		//下载表格
		return ExcelUtil.downLoadExcel("static/excel/aaa.xlsx",beanParams);
	}

4.模板存放位置

5.模板

6.导出的excel