Excel 原生POI模板数据导出

使用office 原生POI进行Excel模板数据导出，简单方便，.xls、.xlsx均支持，代码结构清晰明了，导出即下载。

ExcelTempleExport.java

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

/** 
               __------__
              /~   程序猿  ~\
             |    //^\\//^\|
           /~~\  ||  o| |o|:~\
          | |6   ||___|_|_||:| 
           \__.  /     o   \/'
            |   (  ~   O  ~ )      Buddha Bless, No Bug !
   /~~~~\    \  \          /
  | |~~\ |     )  ~------~\
 /' |  | |   /     ____ /~~~)\
(_/'   | | |     /'    |    ( |
       | | |     \    /   __)/ \
       \  \ \      \/    /' \   \
         \  \|\        /   | |\___|
           \ |  \____/     | |
           /^~>  \        _/ <
          |  |         \       \
          |  | \        \       \
          -^-\  \        |       )
               \_______/^\______/
 * @ClassName: ExcelTempleExport 
 * @Description: Excel模板导出
 * @author guojx
 * @version V1.0   
 * @date 2020-05-13 
 */
public class ExcelTempleExport {
	
	//private static final String FILE_PATH = Thread.currentThread().getContextClassLoader().getResource("").getPath()+"excel";
	
	/**
	 *******************************************
	 * @title:Excel模板数据导出  
	 * @date:2020-05-13 20:41
	 * @param: @param response
	 * @param: @param dataList 数据集
	 * @param: @param excelTemple 「模板中有index标识序号」
	 * @param: @param startRow  参数说明 「数据写入开始行」
	 * @return:void
	 * @throws 
	 *******************************************
	 */
    public static void export(HttpServletResponse response,List<Map<String,Object>> dataList, String excelTemple, Integer startRow, String saveFile) {
    	XSSFWorkbook xwb = null;
    	HSSFWorkbook hwb = null;
//        String pathStr = FILE_PATH+File.separator+excelTemple;
//        File file = new File(pathStr);
		/*if(file.exists()) {*/
             try {
            	 //创建workbook
                 //FileInputStream fileInputStream = new FileInputStream(file);
            	 FileInputStream fileInputStream = getTemplates(excelTemple);
                 //响应到客户端
                 setResponseHeader(response, saveFile);
                 OutputStream os = response.getOutputStream();
                 if(excelTemple.contains(".xlsx")) {
                	 xwb = new XSSFWorkbook(fileInputStream);
                	 writeExcelProcess(xwb, dataList, startRow);
                	 xwb.write(os);
                 }else {
                	 hwb = new HSSFWorkbook(fileInputStream);
                	 writeExcelProcess(hwb, dataList, startRow);
                	 hwb.write(os);
                 }
                 os.flush();
                 os.close();
             }catch(Exception e){
                 e.printStackTrace();
             }
        /* }*/
    }
	/**
	 *******************************************
	 * @title:读模板写数据  
	 * @date:2020-05-13 19:13
	 * @param: @param wb
	 * @param: @param dataList
	 * @param: @param file  参数说明 
	 * @return:void
	 * @throws 
	 *******************************************
	 */
	private static void writeExcelProcess(Workbook wb, List<Map<String,Object>> dataList, Integer startRow) {
		Sheet sheet = wb.getSheetAt(0);
		Row row = sheet.getRow(startRow-1);
		int x=0;
		List<String> keyList = new ArrayList<String>();
		while (row.getCell(x)!=null) {
			String key = row.getCell(x).getStringCellValue();
			keyList.add(key);
			x++;
		}
		for (int i = 0; i < dataList.size(); i++) {
			Map<String, Object> map = dataList.get(i);
			Row writeRow = sheet.createRow(i+startRow-1);
			for (int j = 0; j < keyList.size(); j++) {
				Cell cell = writeRow.createCell(j);
				if(keyList.get(j).equals("index")) {
					cell.setCellValue(i+1);
				}else if(map.get(keyList.get(j))!=null) {
					try {
						cell.setCellValue(map.get(keyList.get(j)).toString());
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	//发送响应流方法
	public static void setResponseHeader(HttpServletResponse response, String fileName) {
	    try {
	        try {
	                fileName = new String(fileName.getBytes(),"ISO8859-1");
	            } catch (UnsupportedEncodingException e) {
	                e.printStackTrace();
	            }
	            response.setContentType("application/octet-stream;charset=ISO8859-1");
	            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
	            response.addHeader("Pargam", "no-cache");
	            response.addHeader("Cache-Control", "no-cache");
	       } catch (Exception ex) {
	           ex.printStackTrace();
	    }
	}
	
	public static FileInputStream getTemplates(String tempName) throws IOException {
        ClassPathResource classPathResource = new ClassPathResource("excel/" + tempName);
        InputStream inputStream = classPathResource.getInputStream();
        // 生成目标文件
        File targetFile = File.createTempFile("template_export", ".xls");
        try {
            FileUtils.copyInputStreamToFile(inputStream, targetFile);
        } finally {
            IOUtils.closeQuietly(inputStream);
        }
        return new FileInputStream(targetFile);
    }
	
}

注：以上代码中模板读取路径需要进行调整
Controller调用，代码如下

	@ResponseBody
	@RequestMapping("/cityScreenExport")
	public void screenExport(HttpServletRequest request,HttpServletResponse response,String areaId,String start,String end) throws IOException {
		Map<String,Object> map = new HashMap<String, Object>();
		if(areaId == null) {
			areaId=ShiroUtils.getAreaId();
		}
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		list = service.cityScreenCount(areaId,start,end);
		map.put("data",list);
		//ExcelExport.index(request,"市州截图统计",ExcelExport.CITY_SCREEN_COUNT_EXPORT,response,map);
		String fileName = "市州截图统计.xlsx";
		ExcelTempleExport.export(response, list, ExcelExport.CITY_SCREEN_COUNT_EXPORT, 3, fileName);
	}

主要调用语句为：
ExcelTempleExport.export(response, list, ExcelExport.CITY_SCREEN_COUNT_EXPORT, 3, fileName);
数据采用List<Map<String, Object>>进行封装
因系统中导出数据模板多，这里使用枚举，ExcelExport.CITY_SCREEN_COUNT_EXPORT,为模板存储路径，可灵活配置

模板样式如下：

模板中英文名称为Map中的key,只需要在写数据起始行进行定义就可以。