Java 下载 Excel（.xlsx） 文档

一、准备工作

Java项目： maven项目添加依赖。web项目可以下载该jar包后导入项目

<!-- excel 下载 -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.12</version>
</dependency>
<dependency>
	<groupId>org.jxls</groupId>
	<artifactId>jxls-poi</artifactId>
	<version>1.0.9</version>
</dependency>

二、java 工具类

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
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.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 导出Excel文档工具类
 */
public class ExcelUtil {

	/**
	 * 创建excel文档，
	 *
	 * @param list        数据
	 * @param keys        list中map的key数组集合
	 * @param columnNames excel的列名
	 */
	public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {
		// 创建excel工作簿
		Workbook wb = new HSSFWorkbook();
		// 创建第一个sheet（页），并命名
		Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString());
		// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
		for (int i = 0; i < keys.length; i++) {
			sheet.setColumnWidth((short) i, (short) (35.7 * 150));
		}

		// 创建第一行
		Row row = sheet.createRow((short) 0);

		// 创建两种单元格格式
		CellStyle cs = wb.createCellStyle();
		CellStyle cs2 = wb.createCellStyle();

		// 创建两种字体
		Font f = wb.createFont();
		Font f2 = wb.createFont();

		// 创建第一种字体样式（用于列名）
		f.setFontHeightInPoints((short) 10);
		f.setColor(IndexedColors.BLACK.getIndex());
		f.setBoldweight(Font.BOLDWEIGHT_BOLD);

		// 创建第二种字体样式（用于值）
		f2.setFontHeightInPoints((short) 10);
		f2.setColor(IndexedColors.BLACK.getIndex());

		// 设置第一种单元格的样式（用于列名）
		cs.setFont(f);
		cs.setBorderLeft(CellStyle.BORDER_THIN);
		cs.setBorderRight(CellStyle.BORDER_THIN);
		cs.setBorderTop(CellStyle.BORDER_THIN);
		cs.setBorderBottom(CellStyle.BORDER_THIN);
		cs.setAlignment(CellStyle.ALIGN_CENTER);

		// 设置第二种单元格的样式（用于值）
		cs2.setFont(f2);
		cs2.setBorderLeft(CellStyle.BORDER_THIN);
		cs2.setBorderRight(CellStyle.BORDER_THIN);
		cs2.setBorderTop(CellStyle.BORDER_THIN);
		cs2.setBorderBottom(CellStyle.BORDER_THIN);
		cs2.setAlignment(CellStyle.ALIGN_CENTER);
		// 设置列名
		for (int i = 0; i < columnNames.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(columnNames[i]);
			cell.setCellStyle(cs);
		}
		// 设置每行每列的值
		for (short i = 1; i < list.size(); i++) {
			// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
			// 创建一行，在页sheet上
			Row row1 = sheet.createRow(i);
			// 在row行上创建一个方格
			for (short j = 0; j < keys.length; j++) {
				Cell cell = row1.createCell(j);
				cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());
				cell.setCellStyle(cs2);
			}
		}
		return wb;
	}

	public static List<Map<String, Object>> createExcelRecord(String sheetName) {
		List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("sheetName", sheetName);
		listmap.add(map);
		return listmap;
	}

	/*
	 * 新疆空白excel文件
	 */
	public static Workbook createEmptyWorkBook() {
		Workbook wb = new HSSFWorkbook();
		return wb;
	}

	/**
	 * 新建只有列名的表
	 * 
	 * @param workbook
	 * @param tatleSheetName 表名
	 * @param columnNames    列名
	 * @return
	 */
	public static Workbook createSheet(Workbook workbook, String sheetName, String[] columnNames) {
		Sheet sheet = workbook.createSheet(sheetName);
		// 创建第一行
		Row row = sheet.createRow((short) 0);
		// 设置列名
		for (int i = 0; i < columnNames.length; i++) {
			sheet.setColumnWidth(i, 256 * 20 + 184);
			Cell cell = row.createCell(i);
			cell.setCellValue(columnNames[i]);
			cell.setCellStyle(getColHeadStyle(workbook));
		}
		return workbook;
	}

	/**
	 * 根据key值将数据填入excel表格
	 * 
	 * @param workbook 输出的excel
	 * @param datas    数据
	 * @param colMap   key值
	 * @return
	 */
	public static Workbook insertData(Workbook workbook, List<Map<String, Object>> datas, String[] colMap,
			String sheetName) {
		Sheet sheet = workbook.getSheet(sheetName);
		for (int i = 0; i < datas.size(); i++) {
			Map<String, Object> data = datas.get(i);
			Row row = sheet.createRow((i + 1));
			for (int j = 0; j < colMap.length; j++) {
				Cell cell = row.createCell(j);
				cell.setCellValue(data.get(colMap[j]) + "");
				cell.setCellStyle(getColDataStyle(workbook));
			}
		}
		return workbook;
	}

	/**
	 * 内容表格样式
	 * 
	 * @param workbook
	 * @return
	 */
	private static CellStyle getColDataStyle(Workbook workbook) {
		CellStyle cs2 = workbook.createCellStyle();
		Font f2 = workbook.createFont();
		f2.setFontHeightInPoints((short) 14);
		f2.setColor(IndexedColors.BLACK.getIndex());
		cs2.setFont(f2);
		cs2.setBorderLeft(CellStyle.BORDER_THIN);
		cs2.setBorderRight(CellStyle.BORDER_THIN);
		cs2.setBorderTop(CellStyle.BORDER_THIN);
		cs2.setBorderBottom(CellStyle.BORDER_THIN);
		cs2.setAlignment(CellStyle.ALIGN_CENTER);
		return cs2;
	}

	/**
	 * 第一行表格样式
	 */
	private static CellStyle getColHeadStyle(Workbook workbook) {
		CellStyle cs = workbook.createCellStyle();
		Font f = workbook.createFont();
		f.setFontHeightInPoints((short) 11);
		cs.setFont(f);
		cs.setBorderLeft(CellStyle.BORDER_THIN);
		cs.setBorderRight(CellStyle.BORDER_THIN);
		cs.setBorderTop(CellStyle.BORDER_THIN);
		cs.setBorderBottom(CellStyle.BORDER_THIN);
		cs.setAlignment(CellStyle.ALIGN_CENTER);
		return cs;
	}

	/**
	 * 
	 * @param contentMaps 数据内容
	 * @param keyNames 内容的key值
	 * @param colNames   列名
	 * @param sheetName  表名
	 * @return
	 */
	public static XSSFWorkbook createXssfWb(Map<String, Object>[] contentMaps, List<String> keyNames,
			List<String> colNames, String sheetName) {
		XSSFWorkbook book = new XSSFWorkbook();
		XSSFSheet sheet = book.createSheet(sheetName);
		// 列名
		sheet = createXsheetHeader(book, sheet, colNames);
		// 内容
		sheet = createXsheetContent(book, sheet, contentMaps, keyNames);
		return book;
	}

	private static XSSFSheet createXsheetContent(XSSFWorkbook book, XSSFSheet sheet, Map<String, Object>[] contentMaps,
			List<String> keyNames) {
		if(keyNames == null || keyNames.size() < 1 || contentMaps == null || contentMaps.length < 1) {return sheet;}
		for(int i=0; i<contentMaps.length; i++) {
			XSSFRow row = sheet.createRow((i+1));
			Map<String, Object> map = contentMaps[i];
			for (String key : map.keySet()) {
				for(int j=0; j<keyNames.size(); j++) {
					if(keyNames.get(j).equalsIgnoreCase(key)) {
						XSSFCell cell = row.createCell(j);
						if(map.get(key) == null){
							cell.setCellValue("");
						}else {
							cell.setCellValue(map.get(key).toString());
						}
						cell.setCellStyle(getXstyleContent(book));
						break;
					}
				}
			}
		}
		
		/*
		 * 合并单元格， 数据为第一个cell 的数据
		 * CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);  // startRow, endRow, startCol, endCol。起始行，结束行，起始列，结束列
		 * sheet.addMergedRegion(region);
		 */
		return null;
	}

	private static XSSFSheet createXsheetHeader(XSSFWorkbook book, XSSFSheet sheet, List<String> colNames) {
		if(colNames == null) {return sheet;}
		XSSFRow row = sheet.createRow(0);
		for(int i=0; i<colNames.size(); i++) {
			String name = colNames.get(i);
			int length = name.length();
			sheet.setColumnWidth(i, 256*3*length); 
			XSSFCell cell = row.createCell(i);
			cell.setCellValue(name);
			cell.setCellStyle(getXstyleHeader(book));
		}
		/* 
		 * 合并表头
		 * CellRangeAddress region = new CellRangeAddress(0, 0, 0, 1); // startRow, endRow, startCol, endCol。起始行，结束行，起始列，结束列 
		 * sheet.addMergedRegion(region);
		 */
		return sheet;
	}

	private static CellStyle getXstyleHeader(XSSFWorkbook book) {
		XSSFCellStyle contentStyle = book.createCellStyle();
		// 内容字体样式
        XSSFFont contFont = book.createFont();
        // 加粗
        contFont.setBold(true);
        // 字体名称
        contFont.setFontName("楷体");
        // 字体大小
        contFont.setFontHeight(13);
        // 设置字体css
        contentStyle.setFont(contFont);
        // 竖向居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 横向居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);
		return contentStyle;
	}

	private static CellStyle getXstyleContent(XSSFWorkbook book) {
		XSSFCellStyle contentStyle = book.createCellStyle();
		// 内容字体样式
        XSSFFont contFont = book.createFont();
        // 加粗
        contFont.setBold(false);
        // 字体名称
        contFont.setFontName("楷体");
        // 字体大小
        contFont.setFontHeight(12);
        // 设置字体css
        contentStyle.setFont(contFont);
        // 横向居中
        contentStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        contentStyle.setBorderBottom(BorderStyle.THIN);
        contentStyle.setBorderLeft(BorderStyle.THIN);
        contentStyle.setBorderRight(BorderStyle.THIN);
        contentStyle.setBorderTop(BorderStyle.THIN);
		return contentStyle;
	}

    // 下载到电脑
    public void downloadToLocalExcel(String filePath, String fileName, XSSFWorkbook book) {
	 	if(StringUtils.isEmptyAll(filePath)) {
	 		filePath = this.filePath;
	 	}
	 	File file = new File(filePath);
	 	if(!file.exists()) {
	 		file.mkdirs();
	 	}
	    FileOutputStream os = null;
	    try {
	        os = new FileOutputStream(filePath + fileName + ".xlsx");  // 文件夹名称叫text.xls
	        book.write(os);
	    } catch (FileNotFoundException e) {
	        e.printStackTrace();
	    } catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (os != null) os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
}

三、下载到本地

1.springboot 测试：或者直接调用downloadExcelAndMail 方法

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

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.monitor.email.MailService;
import com.monitor.service.ExcelService;
import com.monitor.utils.ExcelUtil;

@RunWith(SpringRunner.class)
@SpringBootTest
public class ExcelDownloadTest {

    @Test
    public void downloadExcelAndMail() {
        String filePath = "D:/excel2/";
        //主题
        String fileName = "来了,小老弟";
        
        String sheetName = "重分配记录明细";
		
		// key
		List<String> keyNames = new ArrayList<>();
		keyNames.add("modifyDate");
		keyNames.add("notes");
		keyNames.add("oldName");
		keyNames.add("newName");
		keyNames.add("userName");
		keyNames.add("casnum");
		keyNames.add("womanName");
		keyNames.add("manName");
		// 列名
		List<String> headerNames = new ArrayList<>();
		headerNames.add("重命名时间");
		headerNames.add("重命名备注");
		headerNames.add("原名称");
		headerNames.add("新名称");
		headerNames.add("操作者");
		headerNames.add("病历编号");
		headerNames.add("女方姓名");
		headerNames.add("男方姓名");
		// 内容
		Map<String, Object>[] contentMaps = new HashMap[2];
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("modifyDate", "2019-12-12 10:56:20");
		map.put("notes", "notes");
		map.put("oldName", "oldName");
		map.put("newName","oldName");
		map.put("userName", "oldName");
		map.put("casnum", "oldName");
		map.put("womanName", "oldName");
		map.put("manName", "oldName");
		contentMaps[0] = map;
		
		map = new HashMap<String, Object>();
		map.put("modifyDate", "2019-12-13 10:56:20");
		map.put("notes", "notes1");
		map.put("oldName", "oldName1");
		map.put("newName","oldName1");
		map.put("userName", "oldName1");
		map.put("casnum", "oldName1");
		map.put("womanName", "oldName1");
		map.put("manName", "oldName1");
		contentMaps[1] = map;
		
		XSSFWorkbook book = ExcelUtil.createXssfWb(contentMaps, keyNames, headerNames, sheetName);
        
        ExcelUtil.downloadToLocalExcel(filePath, fileName, book);

    }

    
}

2.使用浏览器下载，使用HttpServletResponse

原理：后端处理数据，生成文件。在将文件传输到前端下载。（常用方法）

import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.monitor.utils.ExcelUtil;

@Controller
@RequestMapping("/excelDwn/")
public class ExcelController {
	
	/**
	 * response 响应浏览器，文件传回前端，使用浏览器下载
	 * @param response
	 * @param book
	 * @throws Exception
	 */
	public void outputExcel(HttpServletResponse response, XSSFWorkbook book) throws Exception {
		OutputStream output = null;
		BufferedOutputStream bufferedOutPut = null;
		try {
			output = response.getOutputStream();
			bufferedOutPut = new BufferedOutputStream(output);
			// 执行 flush 操作， 将缓存区内的信息更新到文件上
			bufferedOutPut.flush();
			// 将最新 的 Excel 文件写入到文件输出流中，更新文件信息
			book.write(bufferedOutPut);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (output != null) {
				output.close();
			}
			if (bufferedOutPut != null) {
				// 关闭输出流对象
				bufferedOutPut.close();
			}
		}
	}
	
	/**
	 * 设置响应信息
	 * @param response
	 * @return
	 */
	private HttpServletResponse getResponse(HttpServletResponse response) {
		// 如果使用该ContentType则浏览器自动下载到默认下载地址
		// response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		// 如果使用这两行代码则浏览器会弹出另存为的弹窗
		response.setHeader("Content-Type", "application/force-download");
		response.setContentType("application/x-download;charset=UTF-8");
		response.setHeader("Pragma", "no-cache");
		response.setHeader("Cache-Control", "no-cache");
		response.setDateHeader("Expires", 0);
		return response;
	}
	
	
	/**
	 * 测试
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	@RequestMapping(value = "exportRedistributionDetail", method = {RequestMethod.GET, RequestMethod.POST})
	@ResponseBody
	public void exportRedistributionDetail(HttpServletRequest request, HttpServletResponse response) throws Exception {
		String sheetName = "重分配记录明细";
		response.setHeader("Content-Disposition",
				"attachment;filename=" + new String(("测试文件" + "2019-12-13" + ".xlsx").getBytes("GBK"), "ISO-8859-1")); // 名称
		response = getResponse(response);
		
		// key
		List<String> keyNames = new ArrayList<>();
		keyNames.add("modifyDate");
		keyNames.add("notes");
		keyNames.add("oldName");
		keyNames.add("newName");
		keyNames.add("userName");
		keyNames.add("casnum");
		keyNames.add("womanName");
		keyNames.add("manName");
		// 列名
		List<String> headerNames = new ArrayList<>();
		headerNames.add("重命名时间");
		headerNames.add("重命名备注");
		headerNames.add("原名称");
		headerNames.add("新名称");
		headerNames.add("操作者");
		headerNames.add("病历编号");
		headerNames.add("女方姓名");
		headerNames.add("男方姓名");
		// 内容
		Map<String, Object>[] contentMaps = new HashMap[2];
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("modifyDate", "2019-12-12 10:56:20");
		map.put("notes", "notes");
		map.put("oldName", "oldName");
		map.put("newName","oldName");
		map.put("userName", "oldName");
		map.put("casnum", "oldName");
		map.put("womanName", "oldName");
		map.put("manName", "oldName");
		contentMaps[0] = map;
		
		map = new HashMap<String, Object>();
		map.put("modifyDate", "2019-12-13 10:56:20");
		map.put("notes", "notes1");
		map.put("oldName", "oldName1");
		map.put("newName","oldName1");
		map.put("userName", "oldName1");
		map.put("casnum", "oldName1");
		map.put("womanName", "oldName1");
		map.put("manName", "oldName1");
		contentMaps[1] = map;
		
		XSSFWorkbook book = ExcelUtil.createXssfWb(contentMaps, keyNames, headerNames, sheetName);
		
		// 输出EXCEL IO流数据
		outputExcel(response, book);
	}

}

三、前端请求接口，axios 为例

 Vue.prototype.$axios({
        method: 'post',
        timeout: 10000,
        url: '下载地址',
        responseType: 'blob',  // 响应类型，必须
        data: {
        }
      }).then(response => {
        console.log(response);
          // 通过 a 标签创建一个虚拟链接下载文件
          var blob = new Blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' });  // application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 这里表示xlsx类型
          var downloadElement = document.createElement('a');
          var href = window.URL.createObjectURL(blob);  // 创建下载的链接
          downloadElement.href = href;
          downloadElement.download = '文件名';  // 下载后文件名
          document.body.appendChild(downloadElement);
          downloadElement.click();  // 点击下载
          document.body.removeChild(downloadElement);  // 下载完成移除元素
          window.URL.revokeObjectURL(href);  // 释放掉blob对象
      }).catch(function(error) {
        console.log(error);
      });

responseType: 'blob',  // 响应类型，必须  bolb 是啥么鬼？？