---
title: 'Spring Boot 下載excel'
disqus: hackmd
---
###### tags: `SpringBoot`
Spring Boot 多環境配置
===

[TOC]

## 適用場景

這個controller只要一訪問
http://localhost:8080/excel
就會直接下載excel

## 先備條件

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

## 檔案結構目錄


## 程式碼部分

### 簡單的範例

package com.allproducts.ai_center.controller;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import com.allproducts.ai_center.pojo.People;

@Controller
public class WriteExcelController {
	// https://www.tpisoftware.com/tpu/articleDetails/1872
	@GetMapping("/excel")
    public void get(HttpServletResponse response){
        List<People>peoples = new ArrayList<>();
        peoples.add(new People("史丹利",33,"男"));
        peoples.add(new People("瑪莉",20,"女"));
        peoples.add(new People("布萊恩",25,"男"));

        try (InputStream is = this.getClass().getClassLoader().getResourceAsStream("templates/test1.xlsx")) {
            //設置檔頭資訊 編碼
            String fileName = URLEncoder.encode("測試表", "UTF-8");
            System.out.println("fileName: "+fileName);
            response.setHeader("Content-Disposition", "attachment; filename=" +
                    new String(fileName.getBytes("UTF-8"), "ISO-8859-1") + ".xlsx");
            response.setContentType("application/vnd.ms-excel;charset=utf8");

            OutputStream os = response.getOutputStream();
            Context context = new Context();
            context.putVar("peoples", peoples); //名稱對應excel的items
            JxlsHelper.getInstance().setEvaluateFormulas(true).processTemplate(is, os, context);
            os.flush();
            os.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

package com.allproducts.ai_center.pojo;

public class People{
    private String name;
    private Integer age;
    private String sex;

    public People(String name, Integer age, String sex) {
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
	//getter and setter

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}
    
}

### 使用的模板


### 複雜的範例

	@RequestMapping(value="/", method=RequestMethod.POST)
	public void processFormRequest(
			@RequestParam("CustomerId") String AdvertiserID,
			@RequestParam("StartDate") String start_date,
			@RequestParam("EndDate") String end_date,
			@RequestParam("type") String form_type,
			HttpServletRequest request,HttpServletResponse response){
		
		AdvertiserID = AdvertiserID.trim();
		System.out.println("Get in submit");
		System.out.println(AdvertiserID);
		System.out.println(start_date);
		System.out.println(end_date);
		System.out.println(form_type);
		
		//1.先取得報表週期
		String todayStr = ToolsPackage.getDateString(0L, "yyyy-MM-dd");
		String currentProcessDateStr = start_date;
		Long currentProcessdateMillseconds = ToolsPackage.getDateToMillseconds(start_date);
		Long endDateMillseconds = ToolsPackage.getDateToMillseconds(end_date);
		Long todayDateMillseconds = ToolsPackage.getDateToMillseconds(todayStr);
		boolean request_interval_isQualified = false;
		int howManyDays = 0;
		HashMap<Integer, String> ReportDate_Map = new HashMap<Integer, String>();//<1,2018-01-01>
		while(currentProcessdateMillseconds <= endDateMillseconds && currentProcessdateMillseconds < todayDateMillseconds) {
			request_interval_isQualified = true;
			howManyDays++;
			currentProcessDateStr = ToolsPackage.convertMillsecondsToDateFormat(currentProcessdateMillseconds, "yyyy-MM-dd");
			System.out.println("currentProcessDateStr : " + currentProcessDateStr);
			ReportDate_Map.put(howManyDays, currentProcessDateStr);
			currentProcessdateMillseconds = currentProcessdateMillseconds + (1000L * 60L * 60L * 24L);
		}//End while
		
		ProcessFormRobot pfRobot = new ProcessFormRobot();
		String resultPath = pfRobot.DownloadPerformanceReport(AdvertiserID, start_date, end_date, form_type, ReportDate_Map);
		System.out.println("resultPath: "+resultPath);// 2021-12-23
		ServletContext context = request.getServletContext();
		File downloadFile = new File(resultPath);
		FileInputStream inputStream = null;
		OutputStream outStream = null;
		try {
			inputStream = new FileInputStream(downloadFile);
			response.setContentLength((int) downloadFile.length());
			response.setContentType(context.getMimeType(resultPath));
			
			String userAgent = request.getHeader("User-Agent");
			String fileName = "";
			if((userAgent.contains("MSIE"))||(userAgent.contains("Trident"))){
				fileName= java.net.URLEncoder.encode(downloadFile.getName(),"UTF-8");
				//IE6.11正常、FF的中文部分會出現%XX%XX的代碼
			}else{
				fileName= new String(downloadFile.getName().getBytes("UTF-8"),"ISO-8859-1");
				//Firefox / google Chrome正常，IE6檔名整個亂碼 (連副檔名都看不見)
			}
 
			// response header
			String headerKey = "Content-Disposition";
			String headerValue = String.format("attachment; filename=\"%s\"",fileName);
			response.setHeader(headerKey, headerValue);
 
			// Write response
			outStream = response.getOutputStream();
			IOUtils.copy(inputStream, outStream);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e.toString());
		} finally {
			try {
				if (null != inputStream)
					inputStream.close();
				if (null != inputStream)
					outStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
 
		}
		
	}//End function

## 參考連結

https://www.tpisoftware.com/tpu/articleDetails/1872