Spring Boot 使用POI创建并下载多个工作表sheet的Excel文件

Apache POI

Apache POI：Microsoft文档的Java API。
poi官网

生成Excel文件

根据数据库或缓冲中数据，生成并下载包含多个sheet的Excel

pom.xml

		<!-- 生成excel文件 -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
		<dependency>
 		   <groupId>org.apache.commons</groupId>
 		   <artifactId>commons-lang3</artifactId>
 		   <version>3.9</version>
		</dependency>

controller

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.sijing.downExcel.service.ExcelService;

@Controller
@RequestMapping("/export")
public class ExcelController {
	
	@Autowired
	ExcelService excelService;

    @GetMapping
    public ModelAndView exprot(){
        return excelService.getExcelObj();
    }
}

Service

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;
import org.springframework.web.servlet.ModelAndView;

import com.sijing.downExcel.excelUtil.ExcelView;

@Service
public class ExcelService {
	
	public ModelAndView getExcelObj() {
		/**
		 * 每个map对应一个excel sheet界面
		 * 
		 * Map的key为excel sheet的名称
		 * 
		 * Map的value为excel的数
		 */
		Map<String, Object> datas = new LinkedHashMap<String, Object>();
		List<Map<String, String>> data1 = new ArrayList<Map<String,String>>();
		Map<String, String> map = new HashMap<String, String>();
		map.put("测试", "测试内容");
		data1.add(map);
		datas.put("测试sheet", data1);
		return new ModelAndView(new ExcelView(),datas);
	}

}

ExcelUtil

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
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.ss.usermodel.Workbook;
import org.apache.poi.util.StringUtil;
import org.springframework.http.ContentDisposition;
import org.springframework.http.HttpHeaders;
import org.springframework.web.servlet.view.document.AbstractXlsView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.Charset;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Spring Web - XLS视图
 */
public class ExcelView extends AbstractXlsView {

    /** 字符集 */
    private static final Charset UTF8 = StringUtil.UTF8;

    @Override
    protected void buildExcelDocument(
    		Map<String, Object> model, Workbook workbook, 
    		HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
    
    	// 创建字体对象
    	CellStyle boldStyle =  createBoldStyle(workbook);
    	
    	// 新增sheet
    	int sheetIndex = 0;
    	for(Map.Entry<String, Object> dataTypeAry : model.entrySet()) {
    		@SuppressWarnings("unchecked")
			List<Map<String, String>> datas = (List<Map<String, String>>)dataTypeAry.getValue();
    		addSheet(workbook, boldStyle, datas, sheetIndex, dataTypeAry.getKey());
    		sheetIndex++;
    	}
    	
        //获取文件名
        String fileName = DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + ".xls";
        
        setHeadersAndCharacterEncoding(request, response, fileName);
        
    }
    
    /**
     * 创建字体样式对象
     * @param workbook
     * @return
     */
    private CellStyle createBoldStyle (Workbook workbook) {
    	 // 标题样式-粗体
        CellStyle boldStyle = workbook.createCellStyle();
        Font fontStyle = workbook.createFont();
        fontStyle.setBold(true);
        boldStyle.setFont(fontStyle);
        return boldStyle;
    }
    
    /**
     * 向excel文件中新增sheet
     * @param workbook
     * @param boldStyle
     * @param datas
     * @param sheetIndex
     * @param sheetName
     */
    private void addSheet(Workbook workbook,CellStyle boldStyle,List<Map<String, String>> datas,int sheetIndex,String sheetName) {
    	// 写入excel
        Sheet sheet = workbook.createSheet();
        // 行号
        int rowNumber = 0;
        //写入标题
        if (CollectionUtils.isNotEmpty(datas)) {
            Row headerRow = sheet.createRow(rowNumber++);
            int cellNumber = 0;
            Map<String, String> data = datas.get(0);
            for (Map.Entry<String, String> dataCellName : data.entrySet()) {
                Cell cell = headerRow.createCell(cellNumber++);
                cell.setCellStyle(boldStyle);
                cell.setCellValue(dataCellName != null ? dataCellName.getKey() : null);
                // 列宽自适应
                sheet.autoSizeColumn(cell.getColumnIndex(), true);
            }
        }
        // 写入内容
        if (CollectionUtils.isNotEmpty(datas)) {
            for (Map<String, String> data : datas) {
                Row dataRow = sheet.createRow(rowNumber++);
                int cellNumber = 0;
                for (Map.Entry<String, String> dataCellData : data.entrySet()) {
                    Cell cell = dataRow.createCell(cellNumber++);
                    cell.setCellValue(data != null && dataCellData != null ? String.valueOf(data.get(dataCellData.getKey())) : null);
                }
            }
        }
        workbook.setSheetName(sheetIndex, sheetName);
    }
    
    /**
     * 设置文件名称、文件名编码，文件内容字符集编码
     * @param request
     * @param response
     * @param fileName
     * @throws UnsupportedEncodingException
     */
    private void setHeadersAndCharacterEncoding(HttpServletRequest request, HttpServletResponse response,String fileName) throws UnsupportedEncodingException {
    	//文件名编码
        String name;
        String userAgent = request.getHeader("user-agent").toLowerCase();
        if (userAgent.contains("msie") || userAgent.contains("like gecko")) {
            // IE
            name = URLEncoder.encode(fileName, UTF8.name());
        } else {
            // 非IE
            name = new String(fileName.getBytes(UTF8.name()), "ISO-8859-1");
        }
        // 设置header 下载
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, ContentDisposition.builder("attachment").filename(name).build().toString());
        // 字符集
        response.setCharacterEncoding(UTF8.name());
    }
}