工具类
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileInputStream;
import java.io.Serializable;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
public class ExcelUtil implements Serializable {
private static int lastRow=50000;
public static XSSFWorkbook getExcel(List<List<String>> list) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth(15);
for (int i = 0; i < list.size(); i++) {
List<String> strings = list.get(i);
XSSFRow row = sheet.createRow(i);
for (int j = 0; j < strings.size(); j++) {
String s = strings.get(j);
row.createCell(j).setCellValue(s);
}
}
return workbook;
}
public static <T> XSSFWorkbook exportExcel(List<T> list) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet();
sheet.setDefaultColumnWidth(15);
int a = 0;
int b = 0;
for (int j = 0; j < list.size(); j++) {
T t = list.get(j);
XSSFRow row = null;
if (a == 0) {
row = sheet.createRow(j);
} else {
row = sheet.createRow(j + 1);
}
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].isAnnotationPresent(Excel.class)) {
if (a == 0) {
Excel annotation = fields[i].getAnnotation(Excel.class);
row.createCell(i - b).setCellValue(annotation.title());
} else {
fields[i].setAccessible(true);
String s = null;
try {
s = String.valueOf(fields[i].get(t));
} catch (IllegalAccessException e) {
e.printStackTrace();
}
if (s == null || "null".equals(s)) {
s = "";
}
row.createCell(i - b).setCellValue(s);
}
} else {
b++;
}
if (a == 0 && i == fields.length - 1) {
a = 1;
j--;
}
}
b = 0;
}
return workbook;
}
public static <T> SXSSFWorkbook exportExcelBySXSS(List<T> list, SXSSFWorkbook workbook) {
int a = 0;
int b = 0;
int c = list.size();
int d = 0;
SXSSFSheet sheet = null;
if (workbook == null) {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
} else {
sheet = workbook.getSheetAt(workbook.getNumberOfSheets() - 1);
a = 1;
b = 0;
d = sheet.getLastRowNum();
c = c + d;
}
sheet.setDefaultColumnWidth(15);
int temp = 0;
for (int j = d; j < c; j++) {
T t = list.get(j - d);
SXSSFRow row = null;
if (a == 0) {
row = sheet.createRow(j - temp);
} else {
row = sheet.createRow(j + 1 - temp);
}
int lastRowNum = workbook.getSheetAt(workbook.getNumberOfSheets() - 1).getLastRowNum();
if (lastRowNum >= lastRow) {
temp = j;
a = 0;
sheet = workbook.createSheet();
row = sheet.createRow(0);
}
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].isAnnotationPresent(Excel.class)) {
if (a == 0) {
Excel annotation = fields[i].getAnnotation(Excel.class);
row.createCell(i - b).setCellValue(annotation.title());
} else {
fields[i].setAccessible(true);
String s = null;
try {
PropertyDescriptor descriptor = new PropertyDescriptor(fields[i].getName(), t.getClass());
Method readMethod = descriptor.getReadMethod();
Object o = readMethod.invoke(t);
s = String.valueOf(o);
} catch (Exception e) {
e.printStackTrace();
}
if (s == null || "null".equals(s)) {
s = "";
}
row.createCell(i - b).setCellValue(s);
}
} else {
b++;
}
if (a == 0 && i == fields.length - 1) {
a = 1;
j--;
}
}
b = 0;
}
return workbook;
}
public static <T> List<T> readExcel(XSSFWorkbook workbook, Class<T> t) throws Exception {
int count= workbook.getNumberOfSheets() ;
List<T> objects = new ArrayList<>();
for (int temp = 0; temp <count; temp++) {
XSSFSheet sheet = workbook.getSheetAt(temp);
int lastRowNum = sheet.getLastRowNum();
List<String> list = new ArrayList<>();
if (lastRowNum == 0) {
return null;
}
for (int i = 0; i < 1; i++) {
XSSFRow row = sheet.getRow(i);
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
XSSFCell cell = row.getCell(j);
String s = cell.toString();
list.add(s);
}
}
for (int i = 1; i < lastRowNum+1; i++) {
XSSFRow row = sheet.getRow(i);
int lastCellNum = row.getLastCellNum();
Constructor constructor = t.getConstructor();
T o = (T) constructor.newInstance();
for (int j = 0; j < lastCellNum; j++) {
XSSFCell cell = row.getCell(j);
String s = cell.toString();
Class c = o.getClass();
Field[] fields = c.getDeclaredFields();
for (int k = 0; k < fields.length; k++) {
if (fields[k].isAnnotationPresent(Excel.class)) {
Excel annotation = fields[k].getAnnotation(Excel.class);
String title = annotation.title();
if (title.equals(list.get(j))) {
Field f = c.getDeclaredField(fields[k].getName());
Class<?> type = f.getType();
f.setAccessible(true);
Object obj=s;
if (type.equals(Integer.class)) {
obj=Integer.valueOf(s);
}
if (type.equals(Double.class)) {
obj=Double.valueOf(s);
}
if (type.equals(Long.class)) {
obj=Long.valueOf(s);
}
if (type.equals(Byte.class)) {
obj=Byte.valueOf(s);
}
if (type.equals(Short.class)) {
obj=Short.valueOf(s);
}
if (type.equals(Boolean.class)) {
obj=Boolean.valueOf(s);
}
if (type.equals(Float.class)) {
obj=Float.valueOf(s);
}
if (type.equals(BigDecimal.class)) {
obj=BigDecimal.valueOf(Long.valueOf(s));
}
if (type.equals(Date.class)) {
obj=DateFormat.getInstance().parse(s);
}
f.set(o, obj);
}
}
}
}
objects.add(o);
}
}
return objects;
}
public static <T> SXSSFWorkbook exportExcelBySXSS(List<T> list, SXSSFWorkbook workbook, HashMap<String,ExcelTableToString> hashMap) {
int a = 0;
int b = 0;
int c = list.size();
int d = 0;
SXSSFSheet sheet = null;
if (workbook == null) {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
} else {
sheet = workbook.getSheetAt(workbook.getNumberOfSheets() - 1);
a = 1;
b = 0;
d = sheet.getLastRowNum();
c = c + d;
}
sheet.setDefaultColumnWidth(15);
int temp = 0;
for (int j = d; j < c; j++) {
T t = list.get(j - d);
SXSSFRow row = null;
if (a == 0) {
row = sheet.createRow(j - temp);
} else {
row = sheet.createRow(j + 1 - temp);
}
int lastRowNum = workbook.getSheetAt(workbook.getNumberOfSheets() - 1).getLastRowNum();
if (lastRowNum >= lastRow) {
temp = j;
a = 0;
sheet = workbook.createSheet();
row = sheet.createRow(0);
}
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].isAnnotationPresent(Excel.class)) {
Excel annotation = fields[i].getAnnotation(Excel.class);
String title = annotation.title();
if (a == 0) {
row.createCell(i - b).setCellValue(title);
} else {
fields[i].setAccessible(true);
String s = null;
try {
PropertyDescriptor descriptor = new PropertyDescriptor(fields[i].getName(), t.getClass());
Method readMethod = descriptor.getReadMethod();
Object o = readMethod.invoke(t);
ExcelTableToString excelTableToString = hashMap.get(title);
if (excelTableToString!=null){
o = excelTableToString.tableToString(o);
}
s = String.valueOf(o);
} catch (Exception e) {
e.printStackTrace();
}
if (s == null || "null".equals(s)) {
s = "";
}
row.createCell(i - b).setCellValue(s);
}
} else {
b++;
}
if (a == 0 && i == fields.length - 1) {
a = 1;
j--;
}
}
b = 0;
}
return workbook;
}
public static XSSFWorkbook writeExcel(File file ,HashMap<String,ExcelTableToString> map) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = workbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum == 0) {
return null;
}
for (int i = 1; i < lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
int lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
XSSFCell cell = row.getCell(j);
String s = cell.toString();
if (s!=null&&!"".equals(s)){
int n = s.indexOf("#{");
int m = s.indexOf("}");
if (n!=-1&&m!=-1){
String substring = s.substring(n+2, m);
ExcelTableToString excelTableToString = map.get(substring);
Object o = excelTableToString.tableToString(substring);
cell.setCellValue(String.valueOf(o));
}
}
}
}
return workbook;
}
public static <T> SXSSFWorkbook exportExcelBySXSS(List<T> list, SXSSFWorkbook workbook, HashMap<String,ExcelTableToString> hashMap,Boolean flag) {
int a = 0;
int b = 0;
int c = list.size();
int d = 0;
SXSSFSheet sheet = null;
if (workbook == null) {
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet();
} else {
sheet = workbook.getSheetAt(workbook.getNumberOfSheets() - 1);
if (flag!=null&& flag==true){
a=0;
flag=false;
}else {
a = 1;
}
b = 0;
d = sheet.getLastRowNum();
c = c + d;
}
sheet.setDefaultColumnWidth(15);
int temp = 0;
for (int j = d; j < c; j++) {
T t = list.get(j - d);
SXSSFRow row = null;
if (a == 0) {
row = sheet.createRow(j - temp);
} else {
row = sheet.createRow(j + 1 - temp);
}
int lastRowNum = workbook.getSheetAt(workbook.getNumberOfSheets() - 1).getLastRowNum();
if (lastRowNum >= lastRow) {
temp = j;
a = 0;
sheet = workbook.createSheet();
row = sheet.createRow(0);
}
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].isAnnotationPresent(Excel.class)) {
Excel annotation = fields[i].getAnnotation(Excel.class);
String title = annotation.title();
if (a == 0) {
row.createCell(i - b).setCellValue(title);
} else {
fields[i].setAccessible(true);
String s = null;
try {
PropertyDescriptor descriptor = new PropertyDescriptor(fields[i].getName(), t.getClass());
Method readMethod = descriptor.getReadMethod();
Object o = readMethod.invoke(t);
ExcelTableToString excelTableToString = hashMap.get(title);
if (excelTableToString!=null){
o = excelTableToString.tableToString(o);
}
s = String.valueOf(o);
} catch (Exception e) {
e.printStackTrace();
}
if (s == null || "null".equals(s)) {
s = "";
}
row.createCell(i - b).setCellValue(s);
}
} else {
b++;
}
if (a == 0 && i == fields.length - 1) {
a = 1;
j--;
}
}
b = 0;
}
return workbook;
}
public static <T> XSSFWorkbook exportExcelBySXSS(List<T> list, XSSFWorkbook workbook, HashMap<String,ExcelTableToString> hashMap,Boolean flag) {
int a = 0;
int b = 0;
int c = list.size();
int d = 0;
XSSFSheet sheet = null;
if (workbook == null) {
workbook = new XSSFWorkbook();
sheet = workbook.createSheet();
} else {
sheet = workbook.getSheetAt(workbook.getNumberOfSheets() - 1);
if (flag!=null&& flag==true){
a=0;
flag=false;
}else {
a = 1;
}
b = 0;
d = sheet.getLastRowNum();
c = c + d;
}
sheet.setDefaultColumnWidth(15);
int temp = 0;
for (int j = d; j < c; j++) {
T t = list.get(j - d);
XSSFRow row = null;
if (a == 0) {
row = sheet.createRow(j - temp);
} else {
row = sheet.createRow(j + 1 - temp);
}
int lastRowNum = workbook.getSheetAt(workbook.getNumberOfSheets() - 1).getLastRowNum();
if (lastRowNum >= lastRow) {
temp = j;
a = 0;
sheet = workbook.createSheet();
row = sheet.createRow(0);
}
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
if (fields[i].isAnnotationPresent(Excel.class)) {
Excel annotation = fields[i].getAnnotation(Excel.class);
String title = annotation.title();
if (a == 0) {
row.createCell(i - b).setCellValue(title);
} else {
fields[i].setAccessible(true);
String s = null;
try {
PropertyDescriptor descriptor = new PropertyDescriptor(fields[i].getName(), t.getClass());
Method readMethod = descriptor.getReadMethod();
Object o = readMethod.invoke(t);
ExcelTableToString excelTableToString = hashMap.get(title);
if (excelTableToString!=null){
o = excelTableToString.tableToString(o);
}
s = String.valueOf(o);
} catch (Exception e) {
e.printStackTrace();
}
if (s == null || "null".equals(s)) {
s = "";
}
row.createCell(i - b).setCellValue(s);
}
} else {
b++;
}
if (a == 0 && i == fields.length - 1) {
a = 1;
j--;
}
}
b = 0;
}
return workbook;
}
public static void main(String[] args) throws Exception {
}
}
注解
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
String title();
}
函数接口
public interface ExcelTableToString {
public Object tableToString(Object o);
}
实体类
public class User implements Serializable{
@Excel(title = "id")
Integer id ;
@Excel(title = "姓名")
String name;
private String sex;
@Excel(title = "年龄")
String age;
public User() {
}
public User(Integer id, String name, String age) {
this.id = id;
this.name = name;
this.age = age;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
}
测试
public class test {
public static void main(String[] args) throws Exception {
List<User> list = new ArrayList<>();
list.add(new User(1,"小明","12"));
list.add(new User(2,"小李","13"));
list.add(new User(3,"小张","14"));
XSSFWorkbook sheets = ExcelUtil.exportExcel(list);
FileOutputStream fileOutputStream = new FileOutputStream("D:/test.xlsx");
sheets.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
sheets.close();
}
}