EXcel导入导出
一、概述在工作过程中，都会遇到这样一个需求，就是将相关的Excel中的数据导入数据库，这里写成通用的导入Excel的工具。二、项目实现1、构建pom.xml我的工程是利用Maven来构建的，这里仅给出最核心的包<dependency><groupId>commons-lang</groupId><ar
·
前言
在工作过程中，都会遇到这样一个需求，就是将相关的Excel中的数据导入或导出，这里写成通用的Excel的工具。
Java实现Excel导入和导出，看这一篇就够了(珍藏版)_小白一个-CSDN博客_java导出excel
这篇博客写的不错，但是有一些BUG
1.1 数据导入
1.1.1 导入解析为JSON
Controller 代码：
@PostMapping("/importJson")
public JSONArray importJson(@RequestPart("file")MultipartFile file) {
JSONArray array = ExcelUtils.readExcel(file);
System.out.println("导入数据为:" + array);
//List<UserInfo> list = JSONObject.parseArray(array.toJSONString(), UserInfo.class);
return array;
}1.1.2 导入解析为 List<List<String>>
/**
* <p>
* 导入解析为List<List<String>>
* </p>
*/
@RequestMapping(value = "/importlistString", method = {RequestMethod.POST})
public ResponseUtils importlistString(@RequestParam("file") MultipartFile file) {
String fileName = file.getOriginalFilename();
if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
throw new IllegalArgumentException("必须使用模板上传");
}
List<List<String>> dataSet = ExcelTools.readExcel(file, 2);
//List<List<String>> 转换成 List<Map<String, Object>>
List<Map<String, Object>> dataList = new ArrayList<>();
//判断集合是否为空
if (!CollectionUtils.isEmpty(dataSet)) {
for (int i = 0; i < dataSet.size(); i++) {
List<String> list = dataSet.get(i);
Map<String, Object> map = new HashMap<>(16);
for (int j = 0; j < list.size(); j++) {
map.put("userId", list.get(0));
map.put("userName", list.get(1));
map.put("phone", list.get(2));
map.put("hometown", list.get(3));
map.put("email", list.get(4));
map.put("address", list.get(5));
map.put("creatTime", list.get(6));
map.put("modifyDate", list.get(7));
}
dataList.add(map);
}
}
return ResponseUtils.success(dataList);
}1.1.3 导入解析为对象
首先，你需要创建一个与导入表格对应的Java实体对象，并打上对应的Excel解析的导入注解，@ExcelImport注解的value则为表头名称。
注意事项:excel标题和 @ExcelImport注解的value值必须一致，顺序也要一致，
import com.common.excel.annotation.ExcelImport;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
@Data
public class UserInfo implements Serializable {
@ExcelImport("id")
private Long id;
@ExcelImport("userId")
private String userId;
@ExcelImport("userName")
private String userName;
@ExcelImport("phone")
private String phone;
@ExcelImport("hometown")
private String hometown;
@ExcelImport("email")
private String email;
@ExcelImport("address")
private String address;
@ExcelImport("creatTime")
private Date creatTime;
private Date modifyDate;
}Controller 代码：
/**
* <p>
* excel导入数据库,导入解析为对象: List<T>
* 反射解析
* </p>
*/
@RequestMapping(value = "/importlistT", method = {RequestMethod.POST})
public ResponseUtils importlistT(@RequestParam("file") MultipartFile file) {
String fileName = file.getOriginalFilename();
if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
throw new IllegalArgumentException("必须使用模板上传");
}
//根据注解解析的列
List<UserInfo> userInfoList = ExcelTools.readExcel(file, UserInfo.class, 2);
return ResponseUtils.success(userInfoList);
}1.1.4 导入解析为对象（推荐这种）
首先，你需要创建一个与导入表格对应的Java实体对象，并打上对应的Excel解析的导入注解，@ExcelImport注解的value则为表头名称。
注意事项:excel标题和 @ExcelImport注解的value值必须一致
Controller 代码：
/**
* <p>
* excel导入数据库,导入解析为: List<T>，支持.xls和.xlsx格式
* 反射动态解析
* 注意事项:
* (1.)@ExcelImport注解的value则为表头名称,(excel标题名称和@ExcelImport注解的value值必须一致)
* (2.)Java实体对象字段名称要和excel表头名称要一致，
* </p>
*/
@RequestMapping(value = "/importList", method = {RequestMethod.POST})
public ResponseUtils importListT(@RequestParam("file") MultipartFile file) {
String fileName = file.getOriginalFilename();
if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) {
throw new IllegalArgumentException("必须使用模板");
}
//根据注解解析的列
List<UserInfo> userInfoList = ExcelTools.readMultipartFile(file, UserInfo.class);
return ResponseUtils.success(userInfoList);
}1.2 数据导出
1.2.1 上传模板（基础）
我们在做数据导入的时候，往往首先会提供一个模版供其下载或者上传模板，这样用户在导入的时候才知道如何去填写数据。
Controller 代码：
/**
* @Desc 上传Excel模板
* @Param [file]
* @Param filePath :文件路径，上传到哪里
*/
@RequestMapping("/uploadExcel")
public void uploadExcel(@RequestParam("file") MultipartFile file, String filePath) {
if (file.isEmpty()) {
throw new IllegalArgumentException("请选择上传文件");
}
if (StringUtils.isBlank(filePath)) {
throw new IllegalArgumentException("上传路径为空");
}
//这里给默认路径,实际应用中前端传文件路径
filePath = "D://";
String fileName = file.getOriginalFilename();
File dir = new File(filePath + fileName);
if (!dir.exists() || !dir.isDirectory()) {
dir.mkdirs();
}
try {
file.transferTo(dir);
} catch (IOException e) {
e.printStackTrace();
}
}1.2.2 导出模板（基础）
我们在做数据导入的时候，往往首先会提供一个模版供其下载，这样用户才知道如何去填写数据。
前端可以动态选择想要导出的模板字段,需要传数组
Controller 代码：
/**
* @Desc 导出Excel模板 xlsx
* @Param [response]
*/
@RequestMapping(value = "/exportExcelTemplate", method = {RequestMethod.POST})
public void exportExcelTemplate(HttpServletResponse response) {
//标题
String[] titles = new String[]{"姓名", "年龄", "性别", "电话", "城市"};
String fileName = "报表";
try {
ExcelTools.exportTemplate(fileName, titles, response);
response.getOutputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
}1.2.3 动态导出，导出list<Map>数据
这种方式十分灵活，表中的数据，完全自定义设置。
Controller 代码：
/**
* 导出list<Map>,导出字段可选
*/
@RequestMapping(value = "/exportListMap", method = {RequestMethod.GET})
public ResponseUtils exportListMap(HttpServletResponse response) {
String fileName = "报表.xlsx";
String[] titles = new String[]{"id", "userName", "phone", "hometown", "email", "address"};
List<Map> data = new ArrayList<>();
Map<String, Object> map1 = new HashMap<>(16);
map1.put("id", "1");
map1.put("userName", "杨过");
map1.put("phone", "13611303412");
map1.put("email", "13613@163.com");
map1.put("address", "古墓");
Map<String, Object> map2 = new LinkedHashMap();
map2.put("id", "2");
map2.put("userName", "小龙女");
map2.put("phone", "13611303413");
map2.put("email", "13611@163.com");
map2.put("address", "古墓");
data.add(map1);
data.add(map2);
ExcelTools.exportListMap(fileName, titles, data, response);
return ResponseUtils.success();
}1.2.4 动态导出（导出list<T>）
这种方式十分灵活，可以动态选择想要导出的数据
Controller 代码：
/**
* 反射动态导出list<T>,导出字段可选
*/
@RequestMapping(value = "/exportExcel", method = {RequestMethod.POST})
public ResponseUtils exportExcel(HttpServletResponse response) {
List<UserInfo> list = userInfoService.selectAll();
String[] columnNames = {"id", "userId", "userName", "phone", "hometown", "email", "address", "creatTime", "modifyDate"};
ExcelTools.reflectExport("用户导出", columnNames, list, response);
return ResponseUtils.success();
}
/**
* 反射动态导出list<T>, 导出字段可选
*/
@RequestMapping(value = "/chooseExport", method = {RequestMethod.GET})
public ResponseUtils chooseExport(HttpServletResponse response) {
List<UserInfo> list = userInfoService.selectAll();
String[] columnNames = {"id", "userName", "phone", "hometown", "email", "address", "creatTime", "modifyDate"};
List<String> column = new ArrayList<String>(Arrays.asList(columnNames));
ExcelTools.reflectDynamicExport("用户导出", columnNames, list, response);
return ResponseUtils.success();
}2. 环境准备
2.1 Maven 依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.common</groupId>
<artifactId>excel</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>excel</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--生成代码插件-->
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.6</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.41</version>
</dependency>
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<!-- servlet 依赖 -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-beanutils/commons-beanutils -->
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.9.3</version>
</dependency>
</dependencies>2.2 配置文件
application.properties
#启动端口
server.port=8082
#项目启动路径
#server.servlet.context-path=/excel
#数据库连接
spring.datasource.url=jdbc:mysql://localhost:3306/test?generateSimpleParameterMetadata=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
mybatis.type-aliases-package=
mybatis.mapper-locations=classpath:sqlmap/*.xml
#jsp
#spring.mvc.view.prefix=/WEB-INF/jsp/
#spring.mvc.view.suffix=.jsp
#静态文件访问配置
#spring.mvc.static-path-pattern=/static/**3 .工具类
ExcelUtils
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.common.excel.annotation.ExcelImport;
import lombok.SneakyThrows;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelTools {
private static final String XLS = ".xls";
private static final String XLSX = ".xlsx";
private static final String ROW_NUM = "rowNum";
private static final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
private static final NumberFormat NUMBER_FORMAT = NumberFormat.getNumberInstance();
private ExcelTools() {
}
/**
* <p>
* 导出Excel模板
* 此版本生成2007以上版本的文件 (文件后缀：xlsx)
* </p>
*
* @param fileName 表格标题名
* @param titles 表格头部标题集合（对应pojo里面的属性）
* @param response response
*/
public static void exportTemplate(String fileName, String[] titles, HttpServletResponse response) {
// 1.创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 2.在workbook中添加一个sheet,对应Excel文件中的fileName
XSSFSheet sheet = workbook.createSheet(fileName);
//设置标题和单元格样式
XSSFCellStyle titleStyle = getTitleCellStyle(workbook);
//设置列单元格样式
XSSFCellStyle columnStyle = getColumnCellStyle(workbook);
//合并第一行单元格
// Region region1 = new Region(0, (short) 0, 0, (short) (titles.length - 1));
// sheet.addMergedRegion(region1);
// //2.创建顶行
// HSSFRow rowhead = sheet.createRow(0);
// rowhead.setHeight((short) 700);
// HSSFCell cellhead = rowhead.createCell(0);
// cellhead.setCellValue(fileName.substring(0, fileName.lastIndexOf(".")));
// cellhead.setCellStyle(cellStyle);
//3.创建标题行
XSSFRow row = sheet.createRow(0);
// row.setHeight((short) 550);
for (int i = 0; i < titles.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(titleStyle);
sheet.autoSizeColumn(i);
}
//5.输出到Excel表格
write(fileName, workbook, response);
}
/**
* <p>
* Excel导出方法,可导出List<Map>数据，导出字段可选<br>
* 此版本生成2007以上版本的文件 (文件后缀：xlsx)
* </p>
*
* @param fileName 表格的名称
* @param titles 导出excel显示的列头（对应pojo里面的属性）
* @param data 需要显示的数据集合
* @param response
*/
public static void exportListMap(String fileName, String[] titles, List<Map> data, HttpServletResponse response) {
// 声明一个工作薄
XSSFWorkbook workbook = new XSSFWorkbook();
// 生成一个表格
XSSFSheet sheet = workbook.createSheet(fileName);
//设置标题和单元格样式
XSSFCellStyle titleStyle = getTitleCellStyle(workbook);
//设置列单元格样式
XSSFCellStyle columnStyle = getColumnCellStyle(workbook);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (short i = 0; i < titles.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(titleStyle);
XSSFRichTextString text = new XSSFRichTextString(titles[i]);
cell.setCellValue(text);
sheet.autoSizeColumn(i);
}
//4.遍历集合数据，产生数据行
for (int i = 0; i < data.size(); i++) {
XSSFRow rowi = sheet.createRow(i + 1);
for (int j = 0; j < titles.length; j++) {
XSSFCell celli = rowi.createCell(j);
celli.setCellStyle(columnStyle);
celli.setCellValue(data.get(i).get(titles[j]).toString());
sheet.autoSizeColumn(j);
}
}
//5.创建尾部行
XSSFRow rowtail = sheet.createRow(data.size() + 4);
XSSFCell celltaill = rowtail.createCell(0);
celltaill.setCellValue("文档创建时间" + formatDate(new Date()));
celltaill.setCellStyle(columnStyle);
write(fileName, workbook, response);
}
/**
* <p>
* 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
* 此版本生成2007以上版本的文件 (文件后缀：xlsx)
* </p>
*
* @param fileName 表格标题名
* @param excelTitle 表格头部标题集合
* @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的
* JavaBean属性的数据类型有基本数据类型及String,Date
* @param response
*/
public static <T> void dynamicExport(String fileName, String[] excelTitle, List<T> dataset, HttpServletResponse response) {
// 创建工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
//设置标题和单元格样式
XSSFCellStyle titleStyle = getTitleCellStyle(workbook);
//设置列单元格样式
XSSFCellStyle columnStyle = getColumnCellStyle(workbook);
// 创建工作表
XSSFSheet sheet = workbook.createSheet(fileName);
// 创建行
XSSFRow row = sheet.createRow(0);
//创建标题行
for (int i = 0; i < excelTitle.length; i++) {
XSSFCell cellHeader = row.createCell(i);
//设置标题样式
cellHeader.setCellStyle(titleStyle);
cellHeader.setCellValue(new XSSFRichTextString(excelTitle[i]));
sheet.autoSizeColumn(i);
}
T t;
Object value = null;
// 遍历集合数据，设置到sheet对应的单元格
for (int i = 0; i < dataset.size(); i++) {
t = dataset.get(i);
//创建行
row = sheet.createRow(i + 1);
for (int j = 0; j < excelTitle.length; j++) {
//创建行列
XSSFCell cell = row.createCell(j);
//设置列自适应
sheet.autoSizeColumn(j);
//设置单元格样式
cell.setCellStyle(columnStyle);
String fieldName = excelTitle[j];
value = getFieldValueByFieldName(fieldName, t);
if (value instanceof Date) {
value = formatDate(value);
}
if (value != null) {
cell.setCellValue(String.valueOf(value));
}
}
}
write(fileName, workbook, response);
}
/**
* <p>
* https://www.jb51.net/article/81053.htm
* 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br>
* 此版本生成2007以上版本的文件 (文件后缀：xlsx)
* </p>
*
* @param fileName 表格标题名
* @param excelHeader 表格头部标题数组,存放"name"格式字符串，"name"为对象字段名
* @param dataList 数据集合，需与表头数组中的字段名一致，并且符合javabean规范
* @param response
*/
public static <T> void dynamicExport(String fileName, String[] excelHeader, Collection<T> dataList, HttpServletResponse response) {
// 创建工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建工作表
XSSFSheet sheet = workbook.createSheet(fileName);
//设置标题和单元格样式
XSSFCellStyle titleStyle = getTitleCellStyle(workbook);
//设置列单元格样式
XSSFCellStyle columnStyle = getColumnCellStyle(workbook);
// 在sheet中添加标题行
XSSFRow row = sheet.createRow((int) 0);// 行数从0开始
// XSSFCell sequenceCell = row.createCell(0);// cell列 从0开始 第一列添加序号
// sequenceCell.setCellValue("序号");
// sequenceCell.setCellStyle(titleStyle);
// sheet.autoSizeColumn(0);// 自动设置宽度
// 创建标题行
for (int i = 0; i < excelHeader.length; i++) {
XSSFCell titleCell = row.createCell(i);
titleCell.setCellValue(excelHeader[i]);
titleCell.setCellStyle(titleStyle);
sheet.autoSizeColumn(i);
}
try {
// 遍历集合数据，产生数据行
Iterator<T> it = dataList.iterator();
T t = (T) it.next();
int index = 0;
while (it.hasNext()) {
index++;// 0号位被占用 所以+1
row = sheet.createRow(index);
// 为序号赋值
// XSSFCell sequenceCellValue = row.createCell(0);// 序号值永远是第0列
// sequenceCellValue.setCellValue(index);
// sequenceCellValue.setCellStyle(dataStyle);
// sheet.autoSizeColumn(0);
// 利用反射，根据传过来的字段名数组，动态调用对应的getXxx()方法得到属性值
for (int i = 0; i < excelHeader.length; i++) {
XSSFCell dataCell = row.createCell(i);
dataCell.setCellStyle(columnStyle);
sheet.autoSizeColumn(i);
String fieldName = excelHeader[i];
String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);// 取得对应getXxx()方法
Class<? extends Object> clazz = t.getClass();// 泛型为Object以及所有Object的子类
Method getMethod = clazz.getMethod(methodName, new Class[]{});// 通过方法名得到对应的方法
Object value = getMethod.invoke(t, new Object[]{});// 动态调用方,得到属性值
if (value != null) {
if (value instanceof Date) {
value = formatDate(value);
}
// 为当前列赋值
dataCell.setCellValue(String.valueOf(value));
}
}
}
write(fileName, workbook, response);
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
/**
* <p>
* Excel导出方法,导出字段可选<br>
* 此版本生成2007以上版本的文件 (文件后缀：xlsx)
* </p>
*
* @param fileName 表格标题名
* @param header 表格头部标题集合
* @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的
* JavaBean属性的数据类型有基本数据类型及String,Date
* @param response response
*/
@SneakyThrows
public static <T> void dynamicExport(String fileName, List<String> header, Collection<T> dataset, HttpServletResponse response) {
List<Map<String, Object>> objData = new ArrayList<>();
for (Iterator<T> iterator = dataset.iterator(); iterator.hasNext(); ) {
Map<String, Object> map = new HashMap<>(16);
T t = iterator.next();
Field[] declaredFields = t.getClass().getDeclaredFields();
for (int i = 0; i < declaredFields.length; i++) {
Field field = declaredFields[i];
field.setAccessible(true);
Object key = field.getName();
Object value = field.get(t);
map.put(String.valueOf(key), value);
}
objData.add(map);
}
// 创建工作簿对象
XSSFWorkbook workbook = new XSSFWorkbook();
//设置标题和单元格样式
XSSFCellStyle titleStyle = getTitleCellStyle(workbook);
//设置列单元格样式
XSSFCellStyle columnStyle = getColumnCellStyle(workbook);
// 创建工作表
XSSFSheet sheet = workbook.createSheet(fileName);
// 创建行
XSSFRow row = sheet.createRow(0);
// 创建列
XSSFCell cellHeader;
// 产生表格标题行
for (int i = 0; i < header.size(); i++) {
cellHeader = row.createCell(i);
//设置单元格样式
cellHeader.setCellStyle(titleStyle);
cellHeader.setCellValue(new XSSFRichTextString(header.get(i)));
sheet.autoSizeColumn(i);
}
//遍历集合数据，产生数据行
for (int i = 0; i < objData.size(); i++) {
row = sheet.createRow(i + 1);
Map<String, Object> map = objData.get(i);
for (int j = 0; j < header.size(); j++) {
XSSFCell cell = row.createCell(j);
cell.setCellStyle(columnStyle);
sheet.autoSizeColumn(j);
Object val = map.get(header.get(j));
String value = null;
if (val instanceof Date) {
value = formatDate(val);
} else {
value = String.valueOf(map.get(header.get(j)));
}
cell.setCellValue(value);
}
}
write(fileName, workbook, response);
}
/**
* @Desc excel导入，支持.xls和.xlsx格式
* @Param file 文件
* @Param t 解析的對象
* @Param startRow 开始行，从第几行开始
* @Return java.util.List<T>
*/
public static List<List<String>> readExcel(MultipartFile file, int startRow) {
List<List<String>> dataList = new ArrayList<List<String>>();
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
String Filename = file.getOriginalFilename();
//导入excel文件，获取工作簿
Workbook workbook = null;
if (Filename.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (Filename.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
}
/** 得到第一个sheet */
Sheet sheet = workbook.getSheetAt(0);
/** 得到Excel的行数 */
int lastRowNum = sheet.getLastRowNum();
/** 循环Excel的行 */
for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) {
if (sheet.getRow(rowNum) != null) {
Row row = sheet.getRow(rowNum);
if (row == null)
continue;
//第一个单元格号
short firstCellNum = row.getFirstCellNum();
//获取最后一个单元格号
short lastCellNum = row.getLastCellNum();
List<String> rowList = new ArrayList<String>();
if (firstCellNum != lastCellNum) {
// String[] values = new String[lastCellNum];
/** 循环Excel的列 */
for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
String cellValue = getCellValue(cell);
rowList.add(cellValue);
}
dataList.add(rowList);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return dataList;
}
/**
* @Desc excel导入，解析为对象（字段自动映射)，支持.xls和.xlsx格式
* @Param file 文件
* @Param t 解析的對象
* @Param startRow 开始行，从第几行开始
* @Return java.util.List<T>
*/
public static <T> List<T> readExcel(MultipartFile file, Class aClass, int startRow) {
if (null == file) {
throw new IllegalArgumentException("文件不能为空");
}
InputStream inputStream = null;
Workbook workbook = null;
List<T> dataList = new ArrayList<>();
try {
inputStream = file.getInputStream();
String Filename = file.getOriginalFilename();
if (Filename.endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
} else if (Filename.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
}
/** 得到第一个shell */
Sheet sheet = workbook.getSheetAt(0);
/** 得到Excel的行数 */
int totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
if (totalRows >= 1 && sheet.getRow(0) != null) {
int totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
T t = (T) aClass.newInstance();
//Object newInstance = t.getClass().newInstance();
/** 循环Excel的行,startRow从第几行开始 */
for (int rowNum = startRow; rowNum < totalRows; rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 成员变量的值
Object value = null;
// 列开始下标(解析的列)
int startCell = 0;
Field[] fields = t.getClass().getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
String fieldName = field.getName();
ExcelImport annotation = field.getAnnotation(ExcelImport.class);
if (annotation == null || StringUtils.isBlank(annotation.value())) {
startCell++;
continue;
}
// Excel需要赋值的列
Cell cell = row.getCell(startCell);
String cellValue = getCellValue(cell);
value = getEntityMemberValue(field, cellValue);
// 赋值
PropertyUtils.setProperty(t, fieldName, value);
// 列的下标加1
startCell++;
}
dataList.add((T) t);
}
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
return dataList;
}
public static <T> List<T> readMultipartFile(MultipartFile mFile, Class<T> clazz) {
JSONArray array = readExcel(mFile);
return getBeanList(array, clazz);
}
/**
* @Desc excel导入，解析为对象（字段自动映射)，支持.xls和.xlsx格式
* @Param file 文件
* @Param t 解析的對象
* @Param startRow 开始行，从第几行开始
* @Return java.util.List<T>
*/
public static JSONArray readExcel(MultipartFile file) {
if (null == file) {
throw new IllegalArgumentException("文件不能为空");
}
InputStream inputStream = null;
Workbook workbook = null;
JSONArray jsonArray = null;
try {
inputStream = file.getInputStream();
String Filename = file.getOriginalFilename();
if (Filename.endsWith(".xls")) {
POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
workbook = new HSSFWorkbook(poifsFileSystem);
} else if (Filename.endsWith(".xlsx")) {
workbook = new XSSFWorkbook(inputStream);
}
/** 得到第一个shell */
Sheet sheet = workbook.getSheetAt(0);
// 首行下标
int rowStart = sheet.getFirstRowNum();
// 尾行下标
int rowEnd = sheet.getLastRowNum();
// 获取表头行
Row headRow = sheet.getRow(rowStart);
if (headRow == null) {
return new JSONArray();
}
int cellStart = headRow.getFirstCellNum();
int cellEnd = headRow.getLastCellNum();
// 获取表头数据
Map<Integer, String> keyMap = new HashMap<>();
for (int j = cellStart; j < cellEnd; j++) {
String val = getCellValue(headRow.getCell(j));
if (val != null && val.trim().length() != 0) {
keyMap.put(j, val);
}
}
// 如果表头没有数据则不进行解析
if (keyMap.isEmpty()) {
return (JSONArray) Collections.emptyList();
}
// 获取每行JSON对象的值
jsonArray = new JSONArray();
for (int i = rowStart + 1; i <= rowEnd; i++) {
Row row = sheet.getRow(i);
if (row == null)
continue;
JSONObject jsonObject = new JSONObject();
// 添加行号
jsonObject.put("rowNum", i + 1);
/** 循环Excel的列 */
for (int cellNum = cellStart; cellNum < cellEnd; cellNum++) {
String val = getCellValue(row.getCell(cellNum));
jsonObject.put(keyMap.get(cellNum), val);
}
jsonArray.add(jsonObject);
}
} catch (IOException e) {
e.printStackTrace();
}
return jsonArray;
}
/**
* @param workbook
* @return XSSFCellStyle
* @desc 标题行的单元格样式
*/
private static XSSFCellStyle getTitleCellStyle(XSSFWorkbook workbook) {
//设置字体
XSSFFont font = workbook.createFont();
//设置字体名字
font.setFontName("Courier New");
//字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
//设置字体大小
font.setFontHeightInPoints((short) 12);
font.setColor(HSSFColor.RED.index);
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置自动换行
style.setWrapText(true);
//在样式用应用设置的字体;
style.setFont(font);
return style;
}
/**
* @param workbook
* @return XSSFCellStyle
* @desc 列数据信息单元格样式
*/
public static XSSFCellStyle getColumnCellStyle(XSSFWorkbook workbook) {
// 设置字体
XSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
XSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/**
* 获取Excel单元格值
* 1、数值格式(CELL_TYPE_NUMERIC):
* 1.纯数值格式：getNumericCellValue() 直接获取数据
* <p>
* 2.日期格式：处理yyyy-MM-dd, d/m/yyyy h:mm, HH:mm 等不含文字的日期格式
* <p>
* 1).判断是否是日期格式：HSSFDateUtil.isCellDateFormatted(cell)
* 2).判断是日期或者时间
* cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")
* <p>
* OR:cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd")
* <p>
* 3.自定义日期格式：处理yyyy年m月d日,h时mm分,yyyy年m月等含文字的日期格式
* 判断cell.getCellStyle().getDataFormat()值，解析数值格式
* yyyy年m月d日----->31
* m月d日---->58
* h时mm分--->32
* 2、字符格式(CELL_TYPE_STRING):直接获取内容
*/
private static String getCellValue(Cell cell) {
// 空白或空
if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
return "";
}
// String类型
if (cell.getCellTypeEnum() == CellType.STRING) {
String val = cell.getStringCellValue();
return val;
}
// 数字类型
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
// 科学计数法类型
return NUMBER_FORMAT.format(cell.getNumericCellValue()) + "";
}
// 布尔值类型
if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
return cell.getBooleanCellValue() + "";
}
// 错误类型
return cell.getCellFormula();
}
/**
* 根据属性名获取属性值
*
* @param fieldName
* @param object
* @return
*/
private static Object getFieldValueByFieldName(String fieldName, Object object) {
Object value = null;
try {
Field field = object.getClass().getDeclaredField(fieldName);
//设置对象的访问权限，保证对private的属性的访问
field.setAccessible(true);
value = field.get(object);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return value;
}
/**
* 根据实体成员变量的类型得到成员变量的值
*
* @param field
* @param cellValue
* @return
*/
private static Object getEntityMemberValue(Field field, String cellValue) {
String type = field.getType().getName();
String fieldTypeSimpleName = field.getType().getSimpleName();
Object realValue = null;
switch (fieldTypeSimpleName) {
case "byte":
case "Byte":
realValue = Byte.valueOf(cellValue);
break;
case "short":
realValue = Short.parseShort(cellValue);
break;
case "int":
case "Integer":
realValue = Integer.valueOf(cellValue);
break;
case "Long":
realValue = Long.valueOf(cellValue);
break;
case "float":
realValue = Float.valueOf(cellValue);
break;
case "double":
realValue = Double.valueOf(cellValue);
break;
case "char":
case "Character":
realValue = String.valueOf(cellValue.toCharArray());
break;
case "boolean":
realValue = Boolean.valueOf(cellValue);
break;
case "String":
realValue = cellValue;
break;
case "BigDecimal":
realValue = new BigDecimal(cellValue);
break;
case "Date":
try {
realValue = StringUtils.isBlank(cellValue) ? null : (Object) new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(cellValue);
} catch (ParseException e) {
e.printStackTrace();
}
break;
case "Object":
case "Class":
realValue = (Object) cellValue;
break;
default:
break;
}
return realValue;
}
private static String formatDate(Object date) {
if (date == null) {
return "";
}
SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);
return format.format(date);
}
private static <T> List<T> getBeanList(JSONArray array, Class<T> clazz) {
List<T> list = new ArrayList<>();
Map<Integer, String> uniqueMap = new HashMap<>(16);
for (int i = 0; i < array.size(); i++) {
list.add(getBean(array.getJSONObject(i), clazz, uniqueMap));
}
return list;
}
/**
* 获取每个对象的数据
* sourceObject :源对象
* TargetClazz 目标对象class(也就是要转换的对象)
* uniqueMap : 校验每个对象的数据是否唯一
*/
private static <T> T getBean(JSONObject sourceObject, Class<T> TargetClass, Map<Integer, String> uniqueMap) {
List<String> errorMessageList = new ArrayList<>();
T t = null;
try {
t = TargetClass.newInstance();
Field[] fields = TargetClass.getDeclaredFields();
boolean rowTipsFlag = false;
int rowNum = 0;
for (Field field : fields) {
// 行号
if (field.getName().equals(ROW_NUM)) {
rowNum = sourceObject.getInteger(ROW_NUM);
field.setAccessible(true);
field.set(t, rowNum);
continue;
}
//给TargetClazz设置属性值
setFieldValue(field, sourceObject, t, errorMessageList);
}
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return t;
}
/**
* @desc 给目标对象设置属性值
* @Param field 声明的对象
* @Param source 源对象
* @Param Target 目标对象(也就是要转换的对象)
* @Param errMsgList 异常消息列表
*/
private static <T> void setFieldValue(Field field, JSONObject source, T Target, List<String> errMsgList) {
// 获取 ExcelImport 注解属性
ExcelImport annotation = field.getAnnotation(ExcelImport.class);
if (annotation == null || StringUtils.isBlank(annotation.value())) {
return;
}
String val = null;
//解析的字段名称
String columnNames = annotation.value();
// 根据解析的字段名称获取值
if (source.containsKey(columnNames)) {
val = getString(source.getString(columnNames));
}
if (val == null) {
return;
}
typeConversion(field, Target, val);
}
private static String getString(String s) {
if (StringUtils.isBlank(s)) {
return null;
}
return s.trim();
}
//类型转换并设置字段值
private static <T> void typeConversion(Field field, T Target, String val) {
//字段类型名
String fieldTypeSimpleName = field.getType().getSimpleName();
try {
// 字段类型转换
field.setAccessible(true);
if ("byte".equalsIgnoreCase(fieldTypeSimpleName) || "Byte".equals(fieldTypeSimpleName)) {
field.set(Target, Byte.valueOf(val));
} else if ("short".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, Short.parseShort(val));
} else if ("int".equalsIgnoreCase(fieldTypeSimpleName) || "Integer".equals(fieldTypeSimpleName)) {
field.set(Target, Integer.valueOf(val));
} else if ("long".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, Long.valueOf(val));
} else if ("float".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, Float.valueOf(val));
} else if ("double".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, Double.valueOf(val));
} else if ("char".equalsIgnoreCase(fieldTypeSimpleName) || "Character".equals(fieldTypeSimpleName)) {
field.set(Target, String.valueOf(val.toCharArray()));
} else if ("boolean".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, Boolean.valueOf(val));
} else if ("String".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, val);
} else if ("Date".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(val));
} else if ("BigDecimal".equalsIgnoreCase(fieldTypeSimpleName)) {
field.set(Target, new BigDecimal(val));
} else {
field.set(Target, (Object) val);
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
}
}
private static void write(String fileName, XSSFWorkbook workbook, HttpServletResponse response) {
OutputStream out = null;
String name = fileName + formatDate(new Date()) + XLSX;
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.addHeader("Content-disposition", "attachment; fileName=" + URLEncoder.encode(name + XLSX, "UTF-8"));
out = response.getOutputStream();
workbook.write(out);
out.flush();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (workbook != null) {
workbook.close();
}
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
ResponseUtils
import java.io.Serializable;
/**
* REST接口统一返回数据工具类封装RestResponse
*/
public class ResponseUtils<T> implements Serializable {
private static final long serialVersionUID = 3728877563912075885L;
private int code;
private String msg;
private T data;
public ResponseUtils() {
}
public ResponseUtils(int code, String message, T data) {
this.code = code;
this.setMsg(message);
this.data = data;
}
public ResponseUtils(int code, T data) {
this.code = code;
this.data = data;
}
public ResponseUtils(int code, String message) {
this.code = code;
this.setMsg(message);
}
public int getCode() {
return code;
}
public String getMsg() {
return msg;
}
public T getData() {
return data;
}
public void setCode(int code) {
this.code = code;
}
public void setMsg(String msg) {
this.msg = msg;
}
public void setData(T data) {
this.data = data;
}
/**
* 成功时-返回data
*
* @param <T>
* @return
*/
public static <T> ResponseUtils<T> success() {
return new ResponseUtils<T>(200, null, null);
}
/**
* 成功时-返回data
*
* @param <T>
* @return
*/
public static <T> ResponseUtils<T> success(T data) {
return new ResponseUtils<T>(200, null, data);
}
/**
* 成功-不返回data
*
* @param <T>
* @return
*/
public static <T> ResponseUtils<T> success(String msg) {
return new ResponseUtils<T>(200, msg);
}
/**
* 成功-返回data+msg
*
* @param <T>
* @return
*/
public static <T> ResponseUtils<T> success(String msg, T data) {
return new ResponseUtils<T>(200, msg, data);
}
/**
* 失败
*
* @param <T>
* @return
*/
public static <T> ResponseUtils<T> fail(String msg) {
return new ResponseUtils<T>(500, msg, null);
}
/**
* 失败-code
*
* @param <T>
* @return
*/
public static <T> ResponseUtils<T> fail(int code, String msg) {
return new ResponseUtils<T>(code, msg, null);
}
@Override
public String toString() {
return "RestResponse{" + "code=" + code + ", msg='" + msg + '\'' + ", data=" + data + '}';
}
}
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
1
0- 0
已为社区贡献5条内容
所有评论(0)