根据实体excel导入导出百万数据，可修改表头名称
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档
表格导入导出实现效果展示
根据实体类导出模板
所有对excel都是根据实体类进行操作
根据实体导出的excel模板展示
读取表格数据
读取结果返回，和表格上传数据一致
导出数据为excel
也支持将已有的数据导出为表格
进阶：修改表格导出的列头
部分情况，表头需要为中文，可以使用注解，对表格进行标注，导出的模板和导出的数据列头就是注解的内容了
controller示例
一下示例代码实现了表格模板导出、数据导出、数据读取和百万数据读取
package com.mabo.controller;
import com.alibaba.fastjson.JSONArray;
import com.mabo.entity.ChatInfo;
import com.mabo.util.ExcelHSSFUtil;
import com.monitorjbl.xlsx.StreamingReader;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.io.File;
/**
* TODO your comment
*
* @author Yujiaqi
* @date 2020/12/2 19:20
*/
@Slf4j
@RestController
@RequestMapping("/excel")
public class ExcelController {
/**
* 读取excel
*/
@PostMapping("/upload")
public Object upload(MultipartHttpServletRequest file) throws Exception {
MultipartFile file1 = file.getFile("file");
byte[] bytes = IOUtils.toByteArray(file1.getInputStream());
File excelFile = new File("test1.xlsx");
FileOutputStream fos = new FileOutputStream(excelFile);
fos.write(bytes);
fos.close();
JSONArray jsonArray = ExcelHSSFUtil.readXlsxExcel(ChatInfo.class, excelFile);
return jsonArray;
}
/**
* 下载excel模板
*/
@GetMapping("/download")
public void download(HttpServletResponse response) throws Exception {
String fileName="downloadModel.xlsx";
fileName = URLEncoder.encode(fileName,"UTF-8");
File file=new File(fileName);
file = ExcelHSSFUtil.createXlsxExcel(ChatInfo.class, new ArrayList<>(), file.getAbsolutePath());
// 以流的形式下载文件。
InputStream fis = null;
try {
fis = new BufferedInputStream(new FileInputStream(file.getAbsoluteFile()));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + new String(file.getName().getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream;charset=utf-8");
toClient.write(buffer);
toClient.flush();
toClient.close();
}
/**
* 超大数据量上传
* @param file
* @return
* @throws Exception
*/
@PostMapping("/uploadBatch")
public Object uploadBatch(MultipartHttpServletRequest file) throws Exception {
MultipartFile file1 = file.getFile("file");
byte[] bytes = IOUtils.toByteArray(file1.getInputStream());
File excelFile = new File("test1.xlsx");
FileOutputStream fos = new FileOutputStream(excelFile);
fos.write(bytes);
fos.close();
InputStream inputStream1 = new FileInputStream(excelFile);
JSONArray jsonArray = null;
Workbook work= StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数，默认是10
.bufferSize(4096) //读取资源时，缓存到内存的字节大小，默认是1024
.open(inputStream1);
Sheet sheet = work.getSheetAt(0);//得到第一个sheet
int excelSize = sheet.getLastRowNum();
int max =3;
if (excelSize < max) {
jsonArray = ExcelHSSFUtil.readXlsxExcel(ChatInfo.class, excelFile);
System.out.println(jsonArray);
}else {
//大数据进行多线程处理，并且直接返回数据
int size=max;
for (int i = 1; i < excelSize; ) {
log.info("当前为第" + i);
jsonArray = ExcelHSSFUtil.readXlsxExcelCache(ChatInfo.class, excelFile, i, size);
System.out.println(jsonArray);
i+=max;
if (i+max>excelSize){
jsonArray = ExcelHSSFUtil.readXlsxExcelCache(ChatInfo.class, excelFile,i,excelSize-i);
System.out.println(jsonArray);
}
}
}
return jsonArray;
}
/**
* 下载excel批量数据
*/
@GetMapping("/downloadBatch")
public void downloadBatch(HttpServletResponse response) throws Exception {
String fileName="downloadBatch.xlsx";
fileName = URLEncoder.encode(fileName,"UTF-8");
File file=new File(fileName);
List list = new ArrayList<>();
ChatInfo chatInfo = new ChatInfo();
chatInfo.setMsg("1");
chatInfo.setId("1");
//写入业务数据
list.add(chatInfo);
list.add(chatInfo);
list.add(chatInfo);
file = ExcelHSSFUtil.createXlsxExcel(ChatInfo.class, list, file.getAbsolutePath());
// 以流的形式下载文件。
InputStream fis = null;
try {
fis = new BufferedInputStream(new FileInputStream(file.getAbsoluteFile()));
} catch (FileNotFoundException e) {
e.printStackTrace();
}
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + new String(file.getName().getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream;charset=utf-8");
toClient.write(buffer);
toClient.flush();
toClient.close();
}
}
工具类
package com.mabo.util;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mabo.annotation.ExcelField;
import com.monitorjbl.xlsx.StreamingReader;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Slf4j
public class ExcelHSSFUtil<T> {
//日期支持以下以下格式
private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
private static SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");
private static SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy.MM.dd");
private static SimpleDateFormat sdf3 = new SimpleDateFormat("yyyy年MM月dd日");
public static <T> File create(Class<T> aClass, List<T> list, String fileName) throws IOException {
// 创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle textType = wb.createCellStyle();
HSSFCellStyle dateType = wb.createCellStyle();
HSSFDataFormat format = wb.createDataFormat();
textType.setDataFormat(format.getFormat("@"));
dateType.setDataFormat(format.getFormat("yyyy年m月d日"));
// 在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("sheet1");
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 添加标题行
HSSFCell cell = null;
Field[] fields = aClass.getDeclaredFields();
List<Field> excelField = new ArrayList<>();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
// 获取行内对应单元格
cell = row.createCell(excelNo++);
// 单元格赋值
String value = annotation.value();
if (value.equals("")) {
cell.setCellValue(fields[i].getName());
} else {
cell.setCellValue(value);
}
}
} else {
cell = row.createCell(excelNo++);
cell.setCellValue(fields[i].getName());
}
}
// 写入实体数据，实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
int i = 0;
for (int j = 0; j < list.size(); j++) {
T t = list.get(i);
i++;
row = sheet.createRow(i);
// 添加数据行
//数据转为Json
String json = JSON.toJSONString(t);//关键
JSONObject parse = (JSONObject) JSONObject.parse(json);
for (int z = 0; z < excelField.size(); z++) {
Field field = excelField.get(z);
ExcelField annotation = field.getAnnotation(ExcelField.class);
boolean ignore = false;
if (annotation != null) {
ignore = annotation.ignore();
}
if (!ignore) {
cell = row.createCell(z);
cell.setCellStyle(textType);
// 获取行内对应单元格
String name = field.getName();
Object o = parse.get(name);
// 单元格赋值
if (o instanceof Long) {
long o1 = (long) o;
Date date = null;
SimpleDateFormat simpleDateFormat = null;
try {
date = new Date();
date.setTime(o1);
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(simpleDateFormat.format(date));
} catch (Exception e) {
e.printStackTrace();
cell.setCellValue(o1);
}
} else if (o instanceof String) {
cell.setCellValue((String) o);
} else if (o instanceof Double) {
cell.setCellValue((double) o);
} else if (o instanceof Boolean) {
cell.setCellValue((boolean) o);
}
}
}
}
// // 第六步，将文件存到指定位置
FileOutputStream fout = new FileOutputStream(fileName);
try {
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
fout.close();
}
return new File(fileName);
}
/**
* @Description : 读取excel为实体集合
* @Author : mabo
*/
public static <T> JSONArray readExcel(Class<T> aClass, File file) {
JSONArray array = new JSONArray();
try {
FileInputStream fileInputStream = new FileInputStream(file.getAbsolutePath());
HSSFWorkbook work = new HSSFWorkbook(fileInputStream);// 得到这个excel表格对象
HSSFSheet sheet = work.getSheetAt(0); //得到第一个sheet
int rowNo = sheet.getLastRowNum(); //得到行数
//获取首行列头
HSSFRow row = sheet.getRow(0);
short lastCellNum = row.getLastCellNum();
List<String> fieldNames = new ArrayList<>();
for (int i = 0; i < lastCellNum; i++) {
HSSFCell cell = row.getCell(i);
if (cell != null) {
String stringCellValue = cell.getStringCellValue();
fieldNames.add(stringCellValue);
}
}
JSONObject jsonField = getJsonField(aClass);
for (int i = 1; i <= rowNo; i++) {
row = sheet.getRow(i);
JSONObject jsonObject = new JSONObject();
for (int j = 0; j < fieldNames.size(); j++) {
HSSFCell cell = row.getCell(j);
if (cell != null) {
Object value = null;
CellType cellTypeEnum = cell.getCellTypeEnum();
if (cellTypeEnum.equals(CellType.STRING)) {
value = cell.getStringCellValue();
// try {
// value = simpleDateFormat.parse(value.toString());
// } catch (ParseException e) {
// try {
// value = sdf1.parse(value.toString());
// } catch (ParseException e1) {
// try {
// value = sdf2.parse(value.toString());
// } catch (ParseException e2) {
// try {
// value = sdf3.parse(value.toString());
// } catch (ParseException e3) {
// }
// }
// }
// }
} else if (cellTypeEnum.equals(CellType.NUMERIC)) {
value = cell.getNumericCellValue();
} else if (cellTypeEnum.equals(CellType.BOOLEAN)) {
value = cell.getBooleanCellValue();
}
String string = jsonField.getString(fieldNames.get(j));
jsonObject.put(string, value);
}
}
array.add(jsonObject);
}
} catch (Exception e) {
e.printStackTrace();
}
return array;
}
/**
* @Description : 获取表格列头和实体的对应关系
* @Author : mabo
*/
public static <T> JSONObject getJsonField(Class<T> aClass) {
Field[] fields = aClass.getDeclaredFields();
JSONObject js = new JSONObject();
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
if (!annotation.value().equals("")) {
String value = annotation.value();
js.put(value, fields[i].getName());
} else {
js.put(fields[i].getName(), fields[i].getName());
}
}
} else {
js.put(fields[i].getName(), fields[i].getName());
}
}
return js;
}
/**
* @Description : 生成xlsx格式表格文件，可上传的数据量更大
*/
public static <T> File createXlsxExcel(Class<T> aClass, List<T> list, String fileName) throws Exception {
// 创建一个webbook，对应一个Excel文件
// Workbook wb = WorkbookFactory.create(new File(fileName));
Workbook wb = new SXSSFWorkbook(3000);
CellStyle textType = wb.createCellStyle();
CellStyle dateType = wb.createCellStyle();
DataFormat dataFormat = wb.createDataFormat();
textType.setDataFormat(dataFormat.getFormat("@"));
dateType.setDataFormat(dataFormat.getFormat("yyyy年m月d日"));
// 在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet("sheet1");
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
Row row = sheet.createRow(0);
// 添加标题行
Cell cell = null;
Field[] fields = aClass.getDeclaredFields();
List<Field> excelField = new ArrayList<>();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
// 获取行内对应单元格
cell = row.createCell(excelNo++);
// 单元格赋值
String value = annotation.value();
if (value.equals("")) {
cell.setCellValue(fields[i].getName());
} else {
cell.setCellValue(value);
}
}
} else {
excelField.add(fields[i]);
cell = row.createCell(excelNo++);
cell.setCellValue(fields[i].getName());
}
}
// 写入实体数据，实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
int i = 0;
for (int j = 0; j < list.size(); j++) {
T t = list.get(i);
i++;
row = sheet.createRow(i);
// 添加数据行
//数据转为Json
String json = JSON.toJSONString(t);//关键
JSONObject parse = (JSONObject) JSONObject.parse(json);
for (int z = 0; z < excelField.size(); z++) {
Field field = excelField.get(z);
ExcelField annotation = field.getAnnotation(ExcelField.class);
boolean ignore = false;
if (annotation != null) {
ignore = annotation.ignore();
}
if (!ignore) {
cell = row.createCell(z);
cell.setCellStyle(textType);
// 获取行内对应单元格
String name = field.getName();
Object o = parse.get(name);
// 单元格赋值
if (o instanceof Long) {
long o1 = (long) o;
Date date = null;
SimpleDateFormat simpleDateFormat = null;
try {
date = new Date();
date.setTime(o1);
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(simpleDateFormat.format(date));
} catch (Exception e) {
e.printStackTrace();
cell.setCellValue(o1);
}
} else if (o instanceof String) {
cell.setCellValue((String) o);
} else if (o instanceof Double) {
cell.setCellValue((double) o);
} else if (o instanceof Boolean) {
cell.setCellValue((boolean) o);
}
}
}
}
// // 第六步，将文件存到指定位置
FileOutputStream fout = new FileOutputStream(fileName);
try {
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
fout.close();
}
return new File(fileName);
}
public static <T> JSONArray readXlsxExcel(Class<T> aClass, File file) {
JSONArray array = new JSONArray();
try {
Workbook work = new XSSFWorkbook(new FileInputStream(file.getAbsolutePath()));// 得到这个excel表格对象
Sheet sheet = work.getSheetAt(0);//得到第一个sheet
int rowNo = sheet.getLastRowNum(); //得到行数
//获取首行列头
Row row = sheet.getRow(0);
short lastCellNum = row.getLastCellNum();
List<String> fieldNames = new ArrayList<>();
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
if (cell != null) {
String stringCellValue = cell.getStringCellValue();
fieldNames.add(stringCellValue);
}
}
JSONObject jsonField = getJsonField(aClass);
for (int i = 1; i <= rowNo; i++) {
row = sheet.getRow(i);
JSONObject jsonObject = new JSONObject();
for (int j = 0; j < fieldNames.size(); j++) {
Cell cell = row.getCell(j);
if (cell != null) {
Object value = null;
CellType cellTypeEnum = cell.getCellTypeEnum();
if (cellTypeEnum.equals(CellType.STRING)) {
value = cell.getStringCellValue();
// try {
// value = simpleDateFormat.parse(value.toString());
// } catch (ParseException e) {
// try {
// value = sdf1.parse(value.toString());
// } catch (ParseException e1) {
// try {
// value = sdf2.parse(value.toString());
// } catch (ParseException e2) {
// try {
// value = sdf3.parse(value.toString());
// } catch (ParseException e3) {
// }
// }
// }
// }
} else if (cellTypeEnum.equals(CellType.NUMERIC)) {
value = cell.getNumericCellValue();
} else if (cellTypeEnum.equals(CellType.BOOLEAN)) {
value = cell.getBooleanCellValue();
}
String string = jsonField.getString(fieldNames.get(j));
jsonObject.put(string, value);
}
}
array.add(jsonObject);
}
} catch (Exception e) {
e.printStackTrace();
}
return array;
}
public static <T> JSONArray readXlsxExcel(Class<T> aClass, File file, int start, int size) {
JSONArray array = new JSONArray();
try {
Workbook work = new XSSFWorkbook(new FileInputStream(file.getAbsolutePath()));// 得到这个excel表格对象
Sheet sheet = work.getSheetAt(0);//得到第一个sheet
int rowNo = sheet.getLastRowNum(); //得到行数
if (rowNo > start + size) {
rowNo = start + size;
}
//获取首行列头
Row row = sheet.getRow(0);
short lastCellNum = row.getLastCellNum();
List<String> fieldNames = new ArrayList<>();
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
if (cell != null) {
String stringCellValue = cell.getStringCellValue();
fieldNames.add(stringCellValue);
}
}
JSONObject jsonField = getJsonField(aClass);
//从数据行开始
start++;
for (int i = start; i <= rowNo; i++) {
row = sheet.getRow(i);
JSONObject jsonObject = new JSONObject();
for (int j = 0; j < fieldNames.size(); j++) {
Cell cell = row.getCell(j);
if (cell != null) {
Object value = null;
CellType cellTypeEnum = cell.getCellTypeEnum();
if (cellTypeEnum.equals(CellType.STRING)) {
value = cell.getStringCellValue();
// try {
// value = simpleDateFormat.parse(value.toString());
// } catch (ParseException e) {
// try {
// value = sdf1.parse(value.toString());
// } catch (ParseException e1) {
// try {
// value = sdf2.parse(value.toString());
// } catch (ParseException e2) {
// try {
// value = sdf3.parse(value.toString());
// } catch (ParseException e3) {
// }
// }
// }
// }
} else if (cellTypeEnum.equals(CellType.NUMERIC)) {
value = cell.getNumericCellValue();
} else if (cellTypeEnum.equals(CellType.BOOLEAN)) {
value = cell.getBooleanCellValue();
}
String string = jsonField.getString(fieldNames.get(j));
jsonObject.put(string, value);
}
}
array.add(jsonObject);
}
} catch (Exception e) {
e.printStackTrace();
}
return array;
}
public static <T> JSONArray readXlsxExcelCache(Class<T> aClass, File file, int start, int size) throws FileNotFoundException {
InputStream inputStream1 = new FileInputStream(file);
Workbook work = StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数，默认是10
.bufferSize(4096) //读取资源时，缓存到内存的字节大小，默认是1024
.open(inputStream1);
Sheet sheet = work.getSheetAt(0);//得到第一个sheet
int excelSize = sheet.getLastRowNum();
//大数据进行多线程处理，并且直接返回数据
log.info("当前数据量大小为" + excelSize);
List<String> fieldNames = new ArrayList<>();
JSONArray array = new JSONArray();
JSONObject jsonField = ExcelHSSFUtil.getJsonField(aClass);
for (Row row : sheet) {
if (row.getRowNum() == 0) {
short lastCellNum = row.getLastCellNum();
for (int i = 0; i < lastCellNum; i++) {
Cell cell = row.getCell(i);
if (cell != null) {
String stringCellValue = cell.getStringCellValue();
fieldNames.add(stringCellValue);
}
}
}
int maxSize = start + size;
if (row.getRowNum() >= start && row.getRowNum()<maxSize) { //从设定的行开始取值
//对当前行逐列进行循环取值
JSONObject jsonObject = new JSONObject();
for (int j = 0; j < fieldNames.size(); j++) {
Cell cell = row.getCell(j);
if (cell != null) {
Object value = null;
CellType cellTypeEnum = cell.getCellTypeEnum();
if (cellTypeEnum.equals(CellType.STRING)) {
value = cell.getStringCellValue();
// try {
// value = simpleDateFormat.parse(value.toString());
// } catch (ParseException e) {
// try {
// value = sdf1.parse(value.toString());
// } catch (ParseException e1) {
// try {
// value = sdf2.parse(value.toString());
// } catch (ParseException e2) {
// try {
// value = sdf3.parse(value.toString());
// } catch (ParseException e3) {
// }
// }
// }
// }
} else if (cellTypeEnum.equals(CellType.NUMERIC)) {
value = cell.getNumericCellValue();
} else if (cellTypeEnum.equals(CellType.BOOLEAN)) {
value = cell.getBooleanCellValue();
}
String string = jsonField.getString(fieldNames.get(j));
jsonObject.put(string, value);
}
}
array.add(jsonObject);
}
if (row.getRowNum()>maxSize){
break;
}
}
return array;
}
public static int getExcelSize(File file) throws IOException {
// Workbook work = new XSSFWorkbook(new FileInputStream(file.getAbsolutePath()));// 得到这个excel表格对象
// Sheet sheet = work.getSheetAt(0);//得到第一个sheet
// return sheet.getLastRowNum(); //得到行数
InputStream inputStream1 = new FileInputStream(file);
Workbook work= StreamingReader.builder()
.rowCacheSize(100) //缓存到内存中的行数，默认是10
.bufferSize(4096) //读取资源时，缓存到内存的字节大小，默认是1024
.open(inputStream1);
Sheet sheet = work.getSheetAt(0);//得到第一个sheet
int excelSize = sheet.getLastRowNum();
return excelSize;
}
/**
* @Description : 生成xlsx格式表格文件，可上传的数据量更大
*/
public static <T> File createXlsxExcelCache(Class<T> aClass, List<T> list, String fileName,int startRow) throws Exception {
// 创建一个webbook，对应一个Excel文件
// File file = new File(fileName);
// XSSFWorkbook tplWorkBook = new XSSFWorkbook(new FileInputStream(file));
Workbook wb = new SXSSFWorkbook( 3000);
//
// InputStream inputStream1 = new FileInputStream(file);
// Workbook wb = new XSSFWorkbook(inputStream1);
// wb = new SXSSFWorkbook(wb,3000);
CellStyle textType = wb.createCellStyle();
CellStyle dateType = wb.createCellStyle();
DataFormat dataFormat = wb.createDataFormat();
textType.setDataFormat(dataFormat.getFormat("@"));
dateType.setDataFormat(dataFormat.getFormat("yyyy年m月d日"));
// 在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.createSheet("sheet1");
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
List<Field> excelField = new ArrayList<>();
Row row =null;
Cell cell = null;
if (startRow==0){
row = sheet.createRow(0);
// 添加标题行
Field[] fields = aClass.getDeclaredFields();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
// 获取行内对应单元格
cell = row.createCell(excelNo++);
// 单元格赋值
String value = annotation.value();
if (value.equals("")) {
cell.setCellValue(fields[i].getName());
} else {
cell.setCellValue(value);
}
}
} else {
excelField.add(fields[i]);
cell = row.createCell(excelNo++);
cell.setCellValue(fields[i].getName());
}
}
}else {
// 不添加标题行，只获取数
Field[] fields = aClass.getDeclaredFields();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
}
} else {
excelField.add(fields[i]);
}
}
}
// 写入实体数据，实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
int i = startRow;
for (int j = 0; j < list.size(); j++) {
T t = list.get(j);
i++;
row = sheet.createRow(i);
// 添加数据行
//数据转为Json
String json = JSON.toJSONString(t);//关键
JSONObject parse = (JSONObject) JSONObject.parse(json);
for (int z = 0; z < excelField.size(); z++) {
Field field = excelField.get(z);
ExcelField annotation = field.getAnnotation(ExcelField.class);
boolean ignore = false;
if (annotation != null) {
ignore = annotation.ignore();
}
if (!ignore) {
cell = row.createCell(z);
cell.setCellStyle(textType);
// 获取行内对应单元格
String name = field.getName();
Object o = parse.get(name);
// 单元格赋值
if (o instanceof Long) {
long o1 = (long) o;
Date date = null;
SimpleDateFormat simpleDateFormat = null;
try {
date = new Date();
date.setTime(o1);
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(simpleDateFormat.format(date));
} catch (Exception e) {
e.printStackTrace();
cell.setCellValue(o1);
}
} else if (o instanceof String) {
cell.setCellValue((String) o);
} else if (o instanceof Double) {
cell.setCellValue((double) o);
} else if (o instanceof Boolean) {
cell.setCellValue((boolean) o);
}
}
}
}
// // 第六步，将文件存到指定位置
FileOutputStream fout = new FileOutputStream(fileName);
try {
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
fout.close();
}
return new File(fileName);
}
public static <T> File appendExcelDataWithCache(Class<T> aClass, List<T> list, String fileName,int startRow) throws Exception {
FileInputStream input = new FileInputStream(fileName);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(input);
//构建SXSSF,设置模板和内存保留行数Workbook wb = new SXSSFWorkbook(xssfWorkbook,100);
// 创建一个webbook，对应一个Excel文件
Workbook wb = new SXSSFWorkbook( xssfWorkbook,3000);
CellStyle textType = wb.createCellStyle();
CellStyle dateType = wb.createCellStyle();
DataFormat dataFormat = wb.createDataFormat();
textType.setDataFormat(dataFormat.getFormat("@"));
dateType.setDataFormat(dataFormat.getFormat("yyyy年m月d日"));
// 在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.getSheet("sheet1");
if (sheet==null){
sheet= wb.createSheet("sheet1");
}
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
List<Field> excelField = new ArrayList<>();
Row row =null;
Cell cell = null;
if (startRow==0){
row = sheet.createRow(0);
// 添加标题行
Field[] fields = aClass.getDeclaredFields();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
// 获取行内对应单元格
cell = row.createCell(excelNo++);
// 单元格赋值
String value = annotation.value();
if (value.equals("")) {
cell.setCellValue(fields[i].getName());
} else {
cell.setCellValue(value);
}
}
} else {
excelField.add(fields[i]);
cell = row.createCell(excelNo++);
cell.setCellValue(fields[i].getName());
}
}
}else {
// 不添加标题行，只获取数
Field[] fields = aClass.getDeclaredFields();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
}
} else {
excelField.add(fields[i]);
}
}
}
// 写入实体数据，实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
int i = startRow;
for (int j = 0; j < list.size(); j++) {
T t = list.get(j);
i++;
row = sheet.createRow(i);
// 添加数据行
//数据转为Json
String json = JSON.toJSONString(t);//关键
JSONObject parse = (JSONObject) JSONObject.parse(json);
for (int z = 0; z < excelField.size(); z++) {
Field field = excelField.get(z);
ExcelField annotation = field.getAnnotation(ExcelField.class);
boolean ignore = false;
if (annotation != null) {
ignore = annotation.ignore();
}
if (!ignore) {
cell = row.createCell(z);
cell.setCellStyle(textType);
// 获取行内对应单元格
String name = field.getName();
Object o = parse.get(name);
// 单元格赋值
if (o instanceof Long) {
long o1 = (long) o;
Date date = null;
SimpleDateFormat simpleDateFormat = null;
try {
date = new Date();
date.setTime(o1);
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(simpleDateFormat.format(date));
} catch (Exception e) {
e.printStackTrace();
cell.setCellValue(o1);
}
} else if (o instanceof String) {
cell.setCellValue((String) o);
} else if (o instanceof Double) {
cell.setCellValue((double) o);
} else if (o instanceof Boolean) {
cell.setCellValue((boolean) o);
}
}
}
}
// // 第六步，将文件存到指定位置
FileOutputStream fout = new FileOutputStream(fileName);
try {
wb.write(fout);
fout.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
fout.close();
}
return new File(fileName);
}
/**
* @Description : 向excel中追加数据，不影响原来数据
* @Author : mabo
*/
public static <T> Workbook appendExcelDataWithCache( Workbook wb,Class<T> aClass, List<T> list,int startRow) throws Exception {
CellStyle textType = wb.createCellStyle();
CellStyle dateType = wb.createCellStyle();
DataFormat dataFormat = wb.createDataFormat();
textType.setDataFormat(dataFormat.getFormat("@"));
dateType.setDataFormat(dataFormat.getFormat("yyyy年m月d日"));
// 在webbook中添加一个sheet,对应Excel文件中的sheet
Sheet sheet = wb.getSheet("sheet1");
if (sheet==null){
sheet= wb.createSheet("sheet1");
}
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
List<Field> excelField = new ArrayList<>();
Row row =null;
Cell cell = null;
if (startRow==0){
row = sheet.createRow(0);
// 添加标题行
Field[] fields = aClass.getDeclaredFields();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
// 获取行内对应单元格
cell = row.createCell(excelNo++);
// 单元格赋值
String value = annotation.value();
if (value.equals("")) {
cell.setCellValue(fields[i].getName());
} else {
cell.setCellValue(value);
}
}
} else {
excelField.add(fields[i]);
cell = row.createCell(excelNo++);
cell.setCellValue(fields[i].getName());
}
}
}else {
// 不添加标题行，只获取数
Field[] fields = aClass.getDeclaredFields();
int excelNo = 0;
for (int i = 0; i < fields.length; i++) {
ExcelField annotation = fields[i].getAnnotation(ExcelField.class);
if (annotation != null) {
if (annotation.ignore() == true) {
continue;
} else {
excelField.add(fields[i]);
}
} else {
excelField.add(fields[i]);
}
}
}
// 写入实体数据，实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
int i = startRow;
for (int j = 0; j < list.size(); j++) {
T t = list.get(j);
i++;
row = sheet.createRow(i);
// 添加数据行
//数据转为Json
String json = JSON.toJSONString(t);//关键
JSONObject parse = (JSONObject) JSONObject.parse(json);
for (int z = 0; z < excelField.size(); z++) {
Field field = excelField.get(z);
ExcelField annotation = field.getAnnotation(ExcelField.class);
boolean ignore = false;
if (annotation != null) {
ignore = annotation.ignore();
}
if (!ignore) {
cell = row.createCell(z);
cell.setCellStyle(textType);
// 获取行内对应单元格
String name = field.getName();
Object o = parse.get(name);
// 单元格赋值
if (o instanceof Long) {
long o1 = (long) o;
Date date = null;
SimpleDateFormat simpleDateFormat = null;
try {
date = new Date();
date.setTime(o1);
simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
cell.setCellValue(simpleDateFormat.format(date));
} catch (Exception e) {
e.printStackTrace();
cell.setCellValue(o1);
}
} else if (o instanceof String) {
cell.setCellValue((String) o);
} else if (o instanceof Double) {
cell.setCellValue((double) o);
} else if (o instanceof Boolean) {
cell.setCellValue((boolean) o);
}
}
}
}
return wb;
}
}
测试实体
package com.mabo.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.mabo.annotation.ExcelField;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import java.io.Serializable;
import java.util.Date;
/**
* (ChatInfo)实体类
*
* @author makejava
* @since 2022-08-01 16:18:08
*/
@Data
public class ChatInfo implements Serializable {
/**
* 主键
*/
@ExcelField("主键id")
private String id;
/**
* 消息
*/
@ExcelField("消息")
private String msg;
}
实体注解
package com.mabo.annotation;
import java.lang.annotation.*;
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelField {
String value() default "";
boolean ignore() default false;
}
maven依赖
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.3.22</version>
</dependency>
<!-- 07版本以后的格式 .xlsx -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- 读取大量excel数据时使用 -->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>