本文设计程序可以在百度云盘中进行下载
链接:https://pan.baidu.com/s/18IVuJw51WzhbxMO2YkvQ9A
提取码:olug
在项目开发的过程中,经常会遇到需要使用excel导出数据的场景,之前使用spring自带的那套ResponseEntity,表示实在是有点难用,后面参考网上的一些教程,自己封装了一个excel导出工具,无论是对开发新手还是程序老鸟,都比较友好,只需要将字段数据做一些封装即可,而且速度相比较于sping的ResponseEntity那套要快不少。刚好有时间,就顺便整理一下,话不多说,上代码。
1.pom文件中添加一些依赖(可以看实际项目的情况增加或者删减)
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.hdd</groupId>
<artifactId>export-test</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<springboot.version>1.5.7.RELEASE</springboot.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<java.version>1.8</java.version>
<mybatis-plus-boot-starter.version>2.1.9</mybatis-plus-boot-starter.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<exclusions><!-- 去掉默认配置 -->
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j2</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
<version>2.4.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.6</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-text</artifactId>
<version>1.6</version>
</dependency>
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.0.0-RELEASE</version>
</dependency>
</dependencies>
</project>
2.application.properties里面的配置(配置端口号,不要和其他程序冲突即可)
server.port=8080
3.工具类的具体代码如下:
package com.hdd.exportTest.excelUtil;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;
public class SimpleExcelUtil {
private static Logger logger= LoggerFactory.getLogger(SimpleExcelUtil.class);
/**
* 简单excel导出方法,网页下载excel
* @param response response对象,用来获取输出流
* @param fileName 文件名,如果为空,默认为日期
* @param contentList 内容集合,根据每个元素的key与表头集合的key匹配,相同则在同一列
* */
public static void simpleDownLoadExcel(HttpServletResponse response,
String fileName,
List<Map<String,Object>> contentList,
LinkedHashMap<String, String> columnNameMap) throws Exception{
response.setContentType("application/ms-excel; charset=UTF-8");//传输类型
if(StringUtils.isBlank(fileName)){
fileName="";
}
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());//日期
response.addHeader("Content-Disposition","attachment;filename=" +fileName+"_"+date + ".xlsx");
List<List<Map<String, Object>>> contentLists = new ArrayList<List<Map<String,Object>>>();//要导出的内容
contentLists.add(contentList);
List<LinkedHashMap<String, String>> columnNameMapLists = new LinkedList<LinkedHashMap<String, String>>();
columnNameMapLists.add(columnNameMap);
ExcelUtil.exportExcelForHugeData(response.getOutputStream(), null, null, columnNameMapLists,contentLists);
}
public static void simpleDownLoadExcelNew(HttpServletResponse response,
String fileName,
List<Map<String,Object>> contentList,
LinkedHashMap<String, String> columnNameMap) throws Exception{
response.setContentType("application/ms-excel; charset=UTF-8");//传输类型
if(StringUtils.isBlank(fileName)){
fileName="";
}
logger.info("simpleDownLoadExcelNew,fileName:{}",fileName);
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
response.addHeader("Content-Disposition","attachment;filename=" +fileName + ".xlsx");
List<List<Map<String, Object>>> contentLists = new ArrayList<List<Map<String,Object>>>();//要导出的内容
contentLists.add(contentList);
List<LinkedHashMap<String, String>> columnNameMapLists = new LinkedList<LinkedHashMap<String, String>>();
columnNameMapLists.add(columnNameMap);
ExcelUtil.exportExcelForHugeData(response.getOutputStream(), null, null, columnNameMapLists,contentLists);
}
public static void simpleDownLoadOrderExcel(HttpServletResponse response,
String fileName,
List<Map<String,Object>> contentList,
LinkedHashMap<String, String> columnNameMap) throws Exception{
response.setContentType("application/ms-excel; charset=UTF-8");//传输类型
if(StringUtils.isBlank(fileName)){
fileName="";
}
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
response.addHeader("Content-Disposition","attachment;filename=" +fileName+ ".xlsx");
List<List<Map<String, Object>>> contentLists = new ArrayList<List<Map<String,Object>>>();//要导出的内容
contentLists.add(contentList);
List<LinkedHashMap<String, String>> columnNameMapLists = new LinkedList<LinkedHashMap<String, String>>();
columnNameMapLists.add(columnNameMap);
ExcelUtil.exportExcelForHugeData(response.getOutputStream(), null, null, columnNameMapLists,contentLists);
}
}
以及下面这个类:
package com.hdd.exportTest.excelUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import javax.servlet.ServletOutputStream;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
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.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Execl工具类
*
* <pre>
* 支持.xls 和 .xlsx 后缀名的 Excel文件,本工具类针对第一行为表头的Excel文件. xls默认导出最大行数限制为5万
* xlsx默认导出最大行数限制为100万
* </p>
*/
public class ExcelUtil {
// xls导出行数区间 0-65535
private static Integer MAX_ROW_XLS = 50000;
// xlsx导出行数区间 0-1048576
private static Integer MAX_ROW_XLSX = 1000000;
//SXSSFWorkbook 输出缓冲
private static Integer rowAccessWindowSize = 1000;
// 后缀名
public final static String XLS = "xls";
public final static String XLSX = "xlsx";
//行号
private final static String DATA_NUMBER = "$DataNumber";
//数字格式化,格式化后的数字将变为字符串
private final static DecimalFormat NUMBER_DATA_FORMAT = new DecimalFormat("#");
private static Logger logger= LoggerFactory.getLogger(ExcelUtil.class);
// 获取每个工作表导出的最大行数
public static Integer getMaxRoW(String excelExtName) throws Exception {
if (excelExtName.equals(XLS)) {
return MAX_ROW_XLS;
} else if (excelExtName.equals(XLSX)) {
return MAX_ROW_XLSX;
} else {
throw new Exception("excel文件后缀名错误!");
}
}
// 设置每个工作表导出的最大行数
public static void setMaxRoW(String excelExtName, Integer maxRow) throws Exception {
if (excelExtName.equals(XLS)) {
MAX_ROW_XLS = maxRow;
} else if (excelExtName.equals(XLSX)) {
MAX_ROW_XLSX = maxRow;
} else {
throw new Exception("excel文件后缀名错误!");
}
}
/**
* 获取Excel文件里面所有工作表的内容,只支持有表头的工作表结构,第一行为Key,列名如若出现重复,将导致数据出错,特别注意空字符串表头
* 导入时,我们不能知道该字段想表达的是一个数字信息还是一个编号信息,所以请将不是数字意义的字段在Excel中设置为"文本"
* <pre>
* 支持的参数形式:
* 1.本地读取:filePathOrFileName = 文件路径,inputStream = null;
* 2.网络传输:filePathOrFileName = 文件路径或者文件名,inputStream = 文件输入流 (表单类型为MultipleData)
* </pre>
*
* @param filePathOrFileName
* 文件路径或文件名:构建流,确认文件类性
* @param inputStream
* 文件输入流
* @param
*
* @return
* @throws Exception
*/
public static List<List<Map<String, Object>>> getExcelContent(String filePathOrFileName,InputStream inputStream ) throws Exception {
String fileType = filePathOrFileName.substring(filePathOrFileName.lastIndexOf(".") + 1, filePathOrFileName.length());
InputStream is = null;
Workbook workBook = null;
try {
if (null==inputStream) {
is = new FileInputStream(filePathOrFileName);
}else{
is= inputStream;
}
if (fileType.equals(XLS)) {
workBook = new HSSFWorkbook(is);
} else if (fileType.equals(XLSX)) {
workBook = new XSSFWorkbook(is);
} else {
throw new Exception("excel文件后缀名错误!");
}
List<List<Map<String, Object>>> result = new ArrayList<List<Map<String, Object>>>();// 对应excel文件
int sheetSize = workBook.getNumberOfSheets();
for (int i = 0; i < sheetSize; i++) {// 遍历sheet页
Sheet sheet = workBook.getSheetAt(i);
List<Map<String, Object>> sheetList = new ArrayList<Map<String, Object>>();// 对应sheet页
List<String> titles = new ArrayList<String>();// 放置所有的标题
int rowSize = sheet.getLastRowNum() + 1;
for (int j = 0; j < rowSize; j++) {// 遍历行
Row row = sheet.getRow(j); //行对象
Map<String, Object> rowMap = new LinkedHashMap<String, Object>();// 对应一行数据
rowMap.put(DATA_NUMBER, Integer.valueOf(j)); //添加行号
if (row == null) {// 略过空行的内容设置
// sheetList.add(rowMap); //将空行的行号记录纳入数据List中
continue;
}
int cellSize = row.getLastCellNum();// 行中有多少个单元格,也就是有多少列
if (j == 0) {// 第一行是标题行
for (int k = 0; k < cellSize; k++) {
Cell cell = row.getCell(k);
titles.add((null != cell) ? cell.toString() : "");
}
} else {// 其他行是数据行
for (int k = 0; k < titles.size(); k++) {
Cell cell = row.getCell(k);
String key = titles.get(k);
Object value = null;
if (cell != null) {
//value = cell.toString();
value=getValueFromExcel(cell);
}
rowMap.put(key, value);
}
sheetList.add(rowMap);
}
}
result.add(sheetList);
}
return result;
} catch (FileNotFoundException e) {
throw e;
} finally {
if (is != null) {
workBook.close();
is.close();
}
}
}
/**
* 在控制体打印Excel表格的内容
*
* <pre>
* 支持的参数形式:
* 1.本地读取:filePathOrFileName = 文件路径,inputStream = null;
* 2.网络传输:filePathOrFileName = 文件路径或者文件名,inputStream = 文件输入流 (表单类型为MultipleData)
* </pre>
* @param filePathOrFileName
* 文件路径或文件名:构建流,确认文件类性
* @param inputStream
* 文件输入流
* @param
*
*
* @throws Exception
*/
public static void showExcelContent(String filePathOrFileName ,InputStream inputStream) throws Exception {
int i = 0;
List<List<Map<String, Object>>> list;
if (null==inputStream) {
list= getExcelContent(filePathOrFileName,null);
}else{
list= getExcelContent(filePathOrFileName,inputStream);
}
for (List<Map<String, Object>> Content : list) {
System.out.println("Sheet" + ++i);
for (Map<String, Object> row : Content) {
System.out.println(row);
}
}
}
/**
* 导出Excel表格 old;
*
* @deprecated 此方法不推荐使用,请使用低内存消耗的缓冲输出方法exportExcelForHugeData()
*
* @param os
* 输出流(默认为FileOutPutStream)
* @param outputPath
* 本地文件输出路径(默认位置为项目根目录下的Excel文件夹里,如果输出流为ServletOutputStream,
* 填null)
* @param fileName
* 文件名(默认为UUID),直接IO流传输时填null
* @param excelExtName
* Excel文件扩展名("xls"或者"xlsx"),必填
* @param sheetNames
* (工作表名列表)
* @param columnNameMapLists
* 表头映射映射关系列表,决定了列的先后顺序及对数据集的过滤,输出到工作表的列数量以这个的Size为准,必填
* @param contentLists
* 工作表内容,(JavaBean 或者 Map )
* @throws Exception
*/
public static <T> void exportExcel(OutputStream os, String outputPath, String fileName, String excelExtName,
List<String> sheetNames, List<LinkedHashMap<String, String>> columnNameMapLists, List<List<T>> contentLists)
throws Exception {
if (null == columnNameMapLists || 0 == columnNameMapLists.size()) {
throw new Exception("存在表头映射关系为NULL,请给出有效表头映射关系!");
} else {
for (LinkedHashMap<String, String> columnNameMap : columnNameMapLists) {
if (null == columnNameMap) {
throw new Exception("存在表头映射关系为NULL,请给出有效表头映射关系!");
}
}
}
// 设置工作簿参数
Workbook workBook = null;
// 单元格样式
CellStyle cellStyle = null;
// ---制定Excel---
// 文件根目录路径为空时,指定为当前根目录下的dir文件夹里
if (null == outputPath || "".equals(outputPath)) {
String path = "./Excel";
File dir = new File(path);
if (!dir.exists()) {
dir.mkdirs();
}
outputPath = path;
}
// 文件名为空时,使用随机文件名
if (null == fileName || "".equals(fileName)) {
UUID uuid = UUID.randomUUID();
fileName = uuid.toString();
}
// 声明文件
File file = null;
// 声明输出流
OutputStream outputStream = null;
// 流不为null时使用外部流
if (null != os && os instanceof ServletOutputStream) {
outputStream = os;
} else {
// 建立文件
file = new File("" + outputPath + "/" + fileName + "." + excelExtName);
// 建立输出流
outputStream = new FileOutputStream(file);
}
// 建立对应后缀名的工作表
try {
if (XLS.equals(excelExtName)) {
workBook = new HSSFWorkbook();
// isXls = true;
} else if (XLSX.equals(excelExtName)) {
workBook = new XSSFWorkbook();
// isXls = false;
} else {
throw new Exception("excel文件后缀名错误!");
}
// 设置水平居中和垂直居中
cellStyle = workBook.createCellStyle();
// 单元格水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 单元格垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 工作表名
for (int i = 0; i < columnNameMapLists.size(); i++) {
// 设置工作表命名
Sheet sheet = workBook
.createSheet(((null != sheetNames && 0 < sheetNames.size() && !"".equals(sheetNames.get(i)))
? sheetNames.get(i) : "Sheet" + (i + 1)));
// Excel坐标
int x = 0;
int y = 0;
// 设置工作表列名对应的成员变量名列表
List<String> fieldNameList = null;
// 列名不为空
if (null != columnNameMapLists.get(i)) {
// 获取工作表列名
Map<String, String> columnNameMap = columnNameMapLists.get(i);
Set<String> fieldNames = columnNameMap.keySet();
// 设置工作表列名
fieldNameList = new LinkedList<String>();
Row row = sheet.createRow(y);
for (String fieldName : fieldNames) {
// 设置列名
Cell cell = row.createCell(x);
cell.setCellStyle(setColumnNameStyle(workBook, excelExtName));
cell.setCellValue(columnNameMap.get(fieldName));
//设置列宽
setColumnWidth(sheet, x);
fieldNameList.add(fieldName);
x++;
}
y++;
}
// 内容不为空
if (null != contentLists && i < contentLists.size()) {
// 设置内容
for (T object : contentLists.get(i)) {
// 忽略空对象
if (null != object) {
x = 0;
Row row = sheet.createRow(y);
for (String fieldName : fieldNameList) {
Object attribute = null;
if (object instanceof Map) {
// 获取Map中的值
attribute = ((Map<?, ?>) object).get(fieldName);
} else {
// 获取对象中的值
Field field = object.getClass().getDeclaredField(fieldName);
field.setAccessible(true);
attribute = field.get(object);
}
Object value = getFormatValue(object, attribute);
Cell cell = row.createCell(x);
cell.setCellStyle(cellStyle);
if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
}
x++;
}
}
y++;
// 导出上限
if (y > ExcelUtil.getMaxRoW(excelExtName)) {
break;
}
}
}
}
// 写出Excel
workBook.write(outputStream);
// 关闭输出流
if (null != outputStream) {
outputStream.flush();
outputStream.close();
}
} catch (Exception e) {
logger.error("exportExcel exception:{}", ExceptionUtils.getMessage(e));
// 关闭输出流
if (null != outputStream) {
outputStream.flush();
outputStream.close();
}
if (!(os instanceof ServletOutputStream)) {
// 删除文件
if (null != file) {
boolean success = file.delete();
if (!success){
logger.info("删除文件失败");
}
}
}
throw new Exception(e);
}
}
/**
* 导出Excel表格大数据量使用,后缀名:xlsx;
*
* @param os
* 输出流(默认为FileOutPutStream)
* @param outputPath
* 本地文件输出路径(默认位置为项目根目录下的Excel文件夹里,如果输出流为ServletOutputStream,
* 填null)
* @param fileName
* 文件名(默认为UUID),直接IO流形式传输时填null
* @param columnNameMapLists
* 表头映射映射关系列表,决定了列的先后顺序及对数据集的过滤,输出到工作表的列数量以这个的Size为准,必填
* @param contentLists
* 工作表内容,(JavaBean 或者 Map )
* @return 文件绝对路径,文件不存在时返回 null
* @throws Exception
*/
public static <T> String exportExcelForHugeData(OutputStream os, String outputPath, String fileName,
List<LinkedHashMap<String, String>> columnNameMapLists, List<List<T>> contentLists)
throws Exception {
//后缀名
String excelExtName = XLSX;
//文件路径
String excelPath=null;
if (null == columnNameMapLists || 0 == columnNameMapLists.size()) {
throw new Exception("存在表头映射关系为NULL,请给出有效表头映射关系!");
} else {
for (LinkedHashMap<String, String> columnNameMap : columnNameMapLists) {
if (null == columnNameMap) {
throw new Exception("存在表头映射关系为NULL,请给出有效表头映射关系!");
}
}
}
// 设置工作簿参数
SXSSFWorkbook sxssfWorkbook = null;
// 单元格样式
CellStyle cellStyle = null;
// ---制定Excel---
// 文件根目录路径为空时,指定为当前根目录下的dir文件夹里
if (null == outputPath || "".equals(outputPath)) {
String path = "./Excel";
File dir = new File(path);
if (!dir.exists()) {
dir.mkdirs();
}
outputPath = path;
}
// 文件名为空时,使用随机文件名
if (null == fileName || "".equals(fileName)) {
UUID uuid = UUID.randomUUID();
fileName = uuid.toString();
}
// 声明文件
File file = null;
// 声明输出流
OutputStream outputStream = null;
// 建立文件
String path = "" + outputPath + "\\" + fileName + "." + excelExtName;
file = new File(path);
//获取文件全路径
excelPath = file.getCanonicalPath();
// 流不为null时使用外部流
if (null != os && os instanceof ServletOutputStream) {
outputStream = os;
} else {
// 建立文件
file = new File("" + outputPath + "/" + fileName + "." + excelExtName);
// 建立输出流
outputStream = new FileOutputStream(file);
}
// 建立.xlsx工作表
try {
//创建工作簿
sxssfWorkbook = new SXSSFWorkbook(rowAccessWindowSize);
// 设置水平居中和垂直居中
cellStyle = sxssfWorkbook.createCellStyle();
// 单元格水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 单元格垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 工作表名
for (int i = 0; i < columnNameMapLists.size(); i++) {
// 设置工作表命名
Sheet sheet = sxssfWorkbook.createSheet("Sheet" + (i + 1));
// Excel坐标
int x = 0;
int y = 0;
// 设置工作表列名对应的成员变量名列表
List<String> fieldNameList = null;
// 列名不为空
if (null != columnNameMapLists.get(i)) {
// 获取工作表列名
Map<String, String> columnNameMap = columnNameMapLists.get(i);
Set<String> fieldNames = columnNameMap.keySet();
// 设置工作表列名
fieldNameList = new LinkedList<String>();
Row row = sheet.createRow(y);
for (String fieldName : fieldNames) {
// 设置列名
Cell cell = row.createCell(x);
cellStyle = sxssfWorkbook.createCellStyle();
// 单元格水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 单元格垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(setColumnNameStyle(sxssfWorkbook, excelExtName));
cell.setCellValue(columnNameMap.get(fieldName));
//设置列宽
setColumnWidth(sheet, x);
fieldNameList.add(fieldName);
x++;
}
y++;
}
// 内容不为空
if (null != contentLists && i < contentLists.size()) {
// 设置内容
for (T object : contentLists.get(i)) {
// 忽略空对象
if (null != object) {
x = 0;
Row row = sheet.createRow(y);
for (String fieldName : fieldNameList) {
Object attribute = null;
if (object instanceof Map) {
// 获取Map中的值
attribute = ((Map<?, ?>) object).get(fieldName);
} else {
// 获取对象中的值
Field field = object.getClass().getDeclaredField(fieldName);
field.setAccessible(true);
attribute = field.get(object);
}
Object value = getFormatValue(object, attribute);
Cell cell = row.createCell(x);
cell.setCellStyle(cellStyle);
if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
}
x++;
}
}
y++;
// 导出上限
if (y > ExcelUtil.getMaxRoW(excelExtName)) {
break;
}
}
}
}
// 写出Excel
sxssfWorkbook.write(outputStream);
// 关闭输出流
if (null != outputStream) {
outputStream.flush();
outputStream.close();
sxssfWorkbook.dispose();
}
} catch (Exception e) {
logger.error("exportExcelForHugeData exception:{}", ExceptionUtils.getMessage(e));
// 关闭输出流
if (null != outputStream) {
outputStream.flush();
outputStream.close();
sxssfWorkbook.dispose();
//删除无效文件
if (null != file) {
boolean success = file.delete();
if (!success){
logger.info("删除无效文件失败");
}
excelPath=null;
}
}
throw new Exception(e);
}
return excelPath;
}
//----------------------导入辅助方法---------------------------
private static Object getValueFromExcel(Cell cell){
Object value = null;
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING: // 字符串
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: // 数字类型
//TODO 这里可能会有问题
if (DateUtil.isCellDateFormatted(cell)) { //日期
value = cell.getDateCellValue();
} else {
value = NUMBER_DATA_FORMAT.format(cell.getNumericCellValue()); //纯数字会最终解析为String,此做法无法完全兼容有小数点的数字
}
break;
case Cell.CELL_TYPE_BLANK: // 空值
value = null;
break;
case Cell.CELL_TYPE_BOOLEAN: // Boolean
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA: // 公式
value = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR: // 故障
// value = null;
break;
default:
// value = null;
break;
}
}
return value;
}
//----------------------导出辅助方法---------------------------
/**
* 数据处理
*
* @param object
* 对象JavaBean 或者 Map
* @param attribute
* 属性值
* @return 处理后的值
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
private static Object getFormatValue(Object object, Object attribute)
throws IllegalArgumentException, IllegalAccessException {
if (null != attribute) {
// 日期格式处理
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-hh-mm hh:MM:ss");
if (attribute instanceof Date) {
return dateFormat.format(attribute);
// 数字类型
} else if (attribute instanceof Number) {
if (attribute instanceof Double) {
return attribute;
}
return Double.parseDouble(attribute.toString());
}
}
return (null != attribute) ? (attribute.toString()) : "";
}
/**
* 表头样式调整
*
* @param
* 样式
* @return
* @throws Exception
*/
private static CellStyle setColumnNameStyle(Workbook workBook, String excelExtName) throws Exception {
CellStyle cellStyle = null;
if (excelExtName.equals(XLS)||excelExtName.equals(XLSX)) {
cellStyle = workBook.createCellStyle();
// 单元格水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 单元格垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font font = workBook.createFont();
// 粗体显示
font.setBold(true);
cellStyle.setFont(font);
} else {
throw new Exception("excel文件后缀名错误!");
}
return cellStyle;
}
/**
* 设置表格的宽度
* @param sheet
* @param columnIndex
*/
private static void setColumnWidth(Sheet sheet, int columnIndex){
//sheet.autoSizeColumn(x, true); //自动宽度
Integer with = 30; //设置列宽,30个字符宽
sheet.setColumnWidth(columnIndex, with * 256);
}
}
如果想要修改excel到处数据的最大行数,可以修改ExcelUtil.java这个类里面的参数。
4.添加一个controller类,写我们的接口程序
package com.hdd.exportTest.controller;
import com.hdd.exportTest.excelUtil.SimpleExcelUtil;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
/**
* @ClassName ExportTestController
* @DesCription TODO
* @Author 584654
* @Date 2021/10/16 15:24
* @Version 1.0
* @PackageName com.hdd.exportTest.controller
**/
@RestController
@RequestMapping("/export")
public class ExportTestController {
private static Logger log= LoggerFactory.getLogger(ExportTestController.class);
@RequestMapping("/hello")
public String hello(){
return "hello world!";
}
/**
* 史上最简单的数据数据导出demo
* 开箱即会
*/
@RequestMapping("/test")
public void export(HttpServletResponse response){
LinkedHashMap<String, String> columnNameMap = new LinkedHashMap<String, String>(16);
columnNameMap.put("name", "姓名");
columnNameMap.put("age", "年龄");
columnNameMap.put("sex", "性别");
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
for(int i=0;i<10;i++){
Map<String,Object> map=new HashMap<String,Object>(16);
map.put("name", "张三"+i+"号");
map.put("age", 20+i);
map.put("sex", i%2==0?"男":"女");
list.add(map);
}
String exportName="用户信息下载";
try {
SimpleExcelUtil.simpleDownLoadExcelNew(response, exportName, list, columnNameMap);
} catch (Exception e) {
log.error("exportList exception:{}", ExceptionUtils.getMessage(e));
}
}
}
5.写一个springBoot程序的入口类,启动程序
package com.hdd.exportTest;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* @ClassName App
* @DesCription TODO
* @Author 584654
* @Date 2021/10/16 15:14
* @Version 1.0
* @PackageName com.hdd.exportTest
**/
@SpringBootApplication
public class App {
public static void main(String[] args) {
SpringApplication.run(App.class);
}
}
6.在浏览器中输入http://127.0.0.1:8080/export/test即可下载到demo里面的excel表格
感兴趣的小伙伴们可以去研究一下这个demo,有任何问题欢迎私信!!!