excel下载

框架：springboot, poi, freemarker, jquery

<parent>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>1.5.10.RELEASE</version>

</parent>

<dependencies>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-freemarker</artifactId>

</dependency>

<dependency>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-web</artifactId>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>4.0.1</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>4.0.1</version> </dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml-schemas</artifactId>

<version>4.0.1</version>

</dependency>

</dependencies>

 

前端代码：

customer-export.ftl

<a id="btnExport" class="easyui-linkbutton" data-options="iconCls:'icon-save'" style="width:120px;border:none;">Export</a>

 

customer-export.js

$("#btnExport").click(downloadReport);

function downloadReport(){

var queryParams = function(){...};

if(!queryParams) return;

// failed to open excel file when using ajax to request, so here i use XMLHttpRequest

var xhr = new XMLHttpRequest();

xhr.open('GET', '/customer/download' + stringifyParams(queryParams,true), true);

xhr.responseType = 'blob';

xhr.onload = function(){

var content = xhr.response;

var el = document.createElement('a');

el.download = 'reports.xls';

el.style.display = 'none';

var blob = new Blob([content]);

el.href = URL.createObjectURL(blob);

document.body.appendChild(el);

el.click();

document.body.removeChild(el);

};

xhr.send();

}

// format params into url parameter format

function stringifyParams (data, isPrefix) {

var prefix = isPrefix ? '?' : '',

result = [];

 

for (var key in data) {

var value = data[key];

 

if (['', undefined, null].includes(value)) {

continue;

}

if (value.constructor === Array) {

value.forEach(function(val, key){

result.push(encodeURIComponent(key) + '[]=' + encodeURIComponent(val));

});

} else {

result.push(encodeURIComponent(key) + '=' + encodeURIComponent(value))

}}

return result.length ? prefix + result.join('&') : '';

}

 

// besides, we can use form to request the download, code as follow:

/*

function downloadReport(){

var queryParams = getQueryParams();

if(!queryParams) return;

 

var downloadForm = $("<form method='get'></form>");

downloadForm.attr("action","/customer/download");

$(document.body).append(downloadForm);

downloadForm.submit();

}

*/

 

后端代码：

 

@RestController

@RequestMapping("/customer")

public class CustomerController {

@Autowired

private ExcelUtil<Customer> excelUtil;

 

@GetMapping("/download")

public void download(QueryCriteria criteria, HttpServletResponse response) {

response.setContentType("application/octet-stream");

response.setHeader("Content-Disposition", "attachment;filename=reports.xls");

List<String> titles = ...;

List<String> fields = ...;

List<Customer> customers = customerService.find(criteria);

Workbook workbook = excelUtil.write(titles,fields,customers);

try (OutputStream os = response.getOutputStream()) {

workbook.write(os);

} catch (IOException e) {

//...}}}

/**

excel handler type

*/

@Component

public class ExcelUtil<T> {

/**

* write out excel file

* @param titles title of every column

* @param fields field name of type T

* @param data list data to be written in Workbook object

* @return an instance of Workbook

*/

public Workbook write(List<String> titles, List<String> fields, List<T> data) {

if (null == titles || null == fields || null == data)

return null;

 

Workbook wb = new HSSFWorkbook();

Sheet s1 = wb.createSheet();

 

// create title row

Row row = s1.createRow(0);

for (int i = 0; i < titles.size(); i++) {

Cell cell = row.createCell(i);

String title = titles.get(i);

cell.setCellValue(title);

}

int index = 1;

for (T t : data){

row = s1.createRow(index);

for (int i=0;i<fields.size();i++){

try {

Field field = t.getClass().getDeclaredField(fields.get(i));

field.setAccessible(true);

setCell(row.createCell(i), field.get(t));

} catch (NoSuchFieldException | IllegalAccessException e) {

throw new RuntimeException(e);

}

}

index++;

}

return wb;

}

 

private void setCell(Cell c, Object val) {

SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

if (val instanceof Double) {

c.setCellValue((Double) val);

} else if (val instanceof Date) {

String time = simpleDateFormat.format((Date) val);

c.setCellValue(time);

} else if (val instanceof Calendar) {

Calendar calendar = (Calendar) val;

String time = simpleDateFormat.format(calendar.getTime());

c.setCellValue(time);

} else if (val instanceof Boolean) {

c.setCellValue((Boolean) val);

} else {

if (val != null) {

c.setCellValue(String.valueOf(val));

}}}}