MAVEN依赖
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.12</version>
</dependency>
java代码
package com.songguoliang.springboot.controller;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Base64;
import java.util.List;
/**
* 读入2007以上excel的工具类
*/
public class ExcelUtils {
/**
* 导出excel
*
* @param fos 导出流
* @param headList excel的标题备注名称
* @param dataList excel数据
*/
public static void createExcel(OutputStream fos, List<String> headList, List<List<String>> dataList) throws Exception {
// 创建新的Excel 工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 在Excel工作簿中建一工作表,其名为缺省值
XSSFSheet sheet = workbook.createSheet();
// 在索引0的位置创建行(最顶端的行)
XSSFRow row = sheet.createRow(0);
// 设置excel头(第一行)的头名称
for (int i = 0; i < headList.size(); i++) {
// 在索引0的位置创建单元格(左上端)
XSSFCell cell = row.createCell(i);
// 定义单元格为字符串类型
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
// 在单元格中输入一些内容
cell.setCellValue(headList.get(i));
}
// ===============================================================
//添加数据
for (int n = 0; n < dataList.size(); n++) {
List<String> rowData = dataList.get(n);
// 在索引1的位置创建行(最顶端的行)
XSSFRow row_value = sheet.createRow(n + 1);
// ===============================================================
for (int i = 0; i < rowData.size(); i++) {
// 在索引0的位置创建单元格(左上端)
XSSFCell cell = row_value.createCell(i);
// 定义单元格为字符串类型
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
// 在单元格中输入一些内容
cell.setCellValue((rowData.get(i)));
}
// ===============================================================
}
// 新建一输出文件流
// 把相应的Excel 工作簿存盘
workbook.write(fos);
fos.flush();
// 操作结束,关闭文件
fos.close();
}
//读取excel
public static List<List<String>> readExcelBy(File file) throws Exception {
ArrayList<List<String>> sheetValue = new ArrayList<>();
try {
//创建工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file);
//读取第一个工作表
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
//获取最后一行的num,即总行数。此处从0开始计数
int maxRow = sheet.getLastRowNum();
for (int row = 0; row <= maxRow; row++) {
//获取最后单元格num,即总单元格数 ***注意:此处从1开始计数***
int maxRol = sheet.getRow(row).getLastCellNum();
ArrayList<String> rowValue = new ArrayList<>();
for (int rol = 0; rol < maxRol; rol++) {
String str = sheet.getRow(row).getCell(rol) + "";
rowValue.add(str);
}
sheetValue.add(rowValue);
}
} catch (IOException e) {
e.printStackTrace();
}
return sheetValue;
}
public static String encoderFileName(String fileName, HttpServletRequest request) throws UnsupportedEncodingException {
//处理中文文件名乱码问题
// 获取浏览器类型,通过请求头中的User-Agent来判断
String ua = request.getHeader("User-Agent");
boolean IE_LT11 = ua.contains("MSIE"); // IE11以下版本
boolean IE11 = ua.contains("rv:11.0) like Gecko"); // IE11
boolean Edge = ua.contains("Edge"); // win10自带的Edge浏览器
// 如果是微软的浏览器,直接进行UTF-8编码
if (IE_LT11 || IE11 || Edge) {
fileName = URLEncoder.encode(fileName, "UTF-8");
// java的编码方式和浏览器有略微的不同:对于空格,java编码后的结果是加号,
// 而浏览器的编码结果是%20,因此将+替换成%20, 这样浏览器才能正确解析空格
fileName = fileName.replace("+", "%20");
}
// 标准浏览器使用Base64编码
else {
Base64.Encoder encoder = Base64.getEncoder();
fileName = encoder.encodeToString(fileName.getBytes(StandardCharsets.UTF_8));
// =?utf-8?B?文件名?= 是告诉浏览器以Base64进行解码
fileName = "=?utf-8?B?" + fileName + "?=";
}
return fileName;
}
public static void main(String[] args) throws Exception {
File file = new File("C:\\Users\\22051\\Desktop\\data\\Excell.xlsx");
List<List<String>> lists = readExcelBy(file);
System.out.println(lists);
}
}
java导出excel到servlet
@GetMapping("/download")
private void download( HttpServletRequest request,HttpServletResponse response) throws Exception {
List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");
List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);
String fileName = ExcelUtils.encoderFileName("中文名excel.xlsx", request);
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename="+fileName);
ServletOutputStream out = response.getOutputStream();
ExcelUtils.createExcel(out, row1, rows);
}