<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
package cn.hsa.req.common.utils;
import cn.hsa.req.annotation.ExcelField;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class ExcelUtil<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> 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;
}
public static<T> File createXlsxExcel(Class<T> aClass, List<T> list, String fileName) throws Exception{
Workbook wb = new XSSFWorkbook();
CellStyle textType = wb.createCellStyle();
CellStyle dateType = wb.createCellStyle();
DataFormat dataFormat = wb.createDataFormat();
textType.setDataFormat(dataFormat.getFormat("@"));
dateType.setDataFormat(dataFormat.getFormat("yyyy年m月d日"));
Sheet sheet = wb.createSheet("sheet1");
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 {
cell = row.createCell(excelNo++);
cell.setCellValue(fields[i].getName());
excelField.add(fields[i]);
}
}
int i = 0;
for (int j = 0; j < list.size(); j++) {
T t = list.get(i);
i++;
row = sheet.createRow(i );
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) throws Exception {
JSONArray array = new JSONArray();
Workbook work = new XSSFWorkbook(new FileInputStream(file.getAbsolutePath()));
Sheet sheet = work.getSheetAt(0);
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);
}
return array;
}
public static<T> File createXlsxExcel(Class<T> aClass, List<T> list, String fileName,String password) throws Exception{
Workbook wb = new XSSFWorkbook();
CellStyle textType = wb.createCellStyle();
CellStyle dateType = wb.createCellStyle();
DataFormat dataFormat = wb.createDataFormat();
textType.setDataFormat(dataFormat.getFormat("@"));
dateType.setDataFormat(dataFormat.getFormat("yyyy年m月d日"));
Sheet sheet = wb.createSheet("sheet1");
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 {
cell = row.createCell(excelNo++);
cell.setCellValue(fields[i].getName());
excelField.add(fields[i]);
}
}
int i = 0;
for (int j = 0; j < list.size(); j++) {
T t = list.get(i);
i++;
row = sheet.createRow(i );
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();
}
File file = new File(fileName);
POIFSFileSystem fs = new POIFSFileSystem();
EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
Encryptor enc = encryptionInfo.getEncryptor();
enc.confirmPassword(password);
OPCPackage opc = OPCPackage.open(file, PackageAccess.READ_WRITE);
OutputStream os = enc.getDataStream(fs);
opc.save(os);
opc.close();
FileOutputStream fos = new FileOutputStream(file);
fs.writeFilesystem(fos);
fos.close();
return new File(fileName);
}
public static<T> JSONArray readXlsxExcel(Class<T> aClass,File file,String password) throws IOException, InvalidFormatException {
JSONArray array = new JSONArray();
Workbook work = WorkbookFactory.create(file, password);
Sheet sheet = work.getSheetAt(0);
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);
}
return array;
}
}