浏览器导出excel

做java web项目时，经常遇到需要在页面上点击导出按钮，然后直浏览器接下载下来一个excel文档。

比如一个List<Person>的集合，需要将每个Person当做一行，输出到excel中去。其中Person实体类如下：

import lombok.Data;/*** className Person* description** @author * @version 1.0* @date **/
@Data
public class Person {/*** 姓名*/private String name;/*** 年龄*/private Integer age;/*** 性别*/private Integer sex;/*** 身份证号*/private String certificateNumber;/*** 手机号*/private String phoneNumber;
}

可以使用 HttpServletResponse 实现浏览器下载，写一个ExcelUtils，如下：

import com.test.cn.entity.dto.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.httpclient.util.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.List;/*** @ClassName ExcelUtils* @Deacription excel工具类* @Author wangcaoxiu* @Date 2022/5/9 15:41* @Version 1.0**/
@Component
@Slf4j
public class ExcelUtils {/*** 方法名：exportExcel* 功能：导出Excel* 描述：* 创建人：typ* 创建时间：2018/10/19 16:00* 修改人：* 修改描述：* 修改时间：*/public static void exportExcel(HttpServletResponse response, ExcelData data) {//实例化XSSFWorkbookXSSFWorkbook workbook = new XSSFWorkbook();//创建一个Excel表单，参数为sheet的名字XSSFSheet sheet = workbook.createSheet("sheet");//设置表头setTitle(workbook, sheet, data.getHead());//设置单元格并赋值setData(sheet, data.getData());//设置浏览器下载setBrowser(response,workbook, data.getFileName());}/*** 方法名：setTitle* 功能：设置表头* 描述：* 创建人：typ* 创建时间：2018/10/19 10:20* 修改人：* 修改描述：* 修改时间：*/private static void setTitle(XSSFWorkbook workbook, XSSFSheet sheet, String[] str) {XSSFRow row = sheet.createRow(0);//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度for (int i = 0; i <= str.length; i++) {sheet.setColumnWidth(i, 15 * 256);}//设置为居中加粗,格式化时间格式XSSFCellStyle style = workbook.createCellStyle();XSSFFont font = workbook.createFont();font.setBold(true);style.setFont(font);style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//创建表头名称XSSFCell cell;for (int j = 0; j < str.length; j++) {cell = row.createCell(j);cell.setCellValue(str[j]);cell.setCellStyle(style);}}private static void setData(XSSFSheet sheet, List<String[]> data) {int rowNum = 1;for (int i = 0; i < data.size(); i++) {XSSFRow row = sheet.createRow(rowNum);for (int j = 0; j < data.get(i).length; j++) {row.createCell(j).setCellValue(data.get(i)[j]);}rowNum++;}}/*** 方法名：setBrowser* 功能：使用浏览器下载* 描述：* 创建人：typ* 创建时间：2018/10/19 16:20* 修改人：* 修改描述：* 修改时间：*/private static void setBrowser(HttpServletResponse response,XSSFWorkbook workbook, String fileName) {try {//清空responseresponse.reset();response.setContentType("application/octet-stream");response.setHeader("Content-Disposition","attachment;filename="+fileName+".xlsx");OutputStream os = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/x-download");response.setCharacterEncoding("utf-8");//将excel写入到输出流中workbook.write(os);os.flush();os.close();} catch (Exception e) {log.error("excel error :{}", e);}}}

其中ExcelData的实体类如下：

import com.alibaba.fastjson.JSONObject;import java.util.List;/*** @description excel导出类* @className ExcelData* @author* @date**/
public class ExcelData {private String fileName;private String[] head;private List<JSONObject> heads;private List<String[]> data;public String getFileName() {return fileName;}public void setFileName(String fileName) {this.fileName = fileName;}public String[] getHead() {return head;}public void setHead(String[] head) {this.head = head;}public List<JSONObject> getHeads() {return heads;}public void setHeads(List<JSONObject> heads) {this.heads = heads;}public List<String[]> getData() {return data;}public void setData(List<String[]> data) {this.data = data;}
}

然后在代码里调用ExcelUtils实现下载：

import com.test.cn.entity.dto.ExcelData;
import com.test.cn.entity.dto.Person;
import com.test.cn.util.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;/*** className PersonController* description** @author * @version 1.0* @date **/
@Slf4j
@RestController
@RequestMapping("/person")
public class PersonController {@PostMapping("/exportExcel")public void exportExcel(HttpServletResponse response) {try {List<String[]> list = new ArrayList<>();Person person1 = new Person("张三",23, "男", "111111111111111111","18888888888");Person person2 = new Person("李四",34, "男", "111111111111111111","18888888888");Person person3 = new Person("王五",28, "男", "111111111111111111","18888888888");String[] data1 = new String[]{person1.getName(),person1.getAge().toString(),person1.getSex(),person1.getCertificateNumber(),person1.getPhoneNumber()};list.add(data1);String[] data2 = new String[]{person2.getName(),person2.getAge().toString(),person2.getSex(),person2.getCertificateNumber(),person2.getPhoneNumber()};list.add(data2);String[] data3 = new String[]{person3.getName(),person3.getAge().toString(),person3.getSex(),person3.getCertificateNumber(),person3.getPhoneNumber()};list.add(data3);//构造ExcelDataExcelData excelData = new ExcelData();excelData.setData(list);SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");String fileName = "人员信息表_" + df.format(new Date());excelData.setFileName(URLEncoder.encode(fileName, "UTF-8"));String[] head = {"姓名", "年龄", "性别", "身份证号", "手机号"};excelData.setHead(head);//导出ExcelUtils.exportExcel(response, excelData);log.info("请求 exportExcel end ......");} catch (Exception e) {log.error("exportExcel error:", e);}}
}

postman调用这个接口，即可看下成功下载的excel文件如下：