Excel4J
Read
package base;
import com.github.crab2died.ExcelUtils;
import com.github.crab2died.exceptions.Excel4JException;
import modules.Student1;
import modules.Student2;
import modules.StudentScore;
import org.junit.Test;
import java.nio.file.Paths;
import java.util.Collections;
import java.util.List;
public class Excel2Module {
@Test
public void excel2Object() throws Exception {
String path = "D:\\JProject\\Excel4J\\src\\test\\resources\\students_01.xlsx";
System.out.println("读取全部：");
List<Student1> students = ExcelUtils.getInstance().readExcel2Objects(path, Student1.class);
for (Student1 stu : students) {
System.out.println(stu);
}
System.out.println("读取指定行数：");
students = ExcelUtils.getInstance().readExcel2Objects(path, Student1.class, 0, 3, 0);
for (Student1 stu : students) {
System.out.println(stu);
}
}
@Test
public void excel2Object2() {
String path = "D:\\JProject\\Excel4J\\src\\test\\resources\\students_02.xlsx";
try {
// 1)
// 不基于注解,将Excel内容读至List<List<String>>对象内
List<List<String>> lists = ExcelUtils.getInstance().readExcel2List(path, 1, 2, 0);
System.out.println("读取Excel至String数组：");
for (List<String> list : lists) {
System.out.println(list);
}
// 2)
// 基于注解,将Excel内容读至List<Student2>对象内
// 验证读取转换函数Student2ExpelConverter
// 注解 `@ExcelField(title = "是否开除", order = 5, readConverter = Student2ExpelConverter.class)`
List<Student2> students = ExcelUtils.getInstance().readExcel2Objects(path, Student2.class, 0, 0);
System.out.println("读取Excel至对象数组(支持类型转换)：");
for (Student2 st : students) {
System.out.println(st);
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 基于注解,将Excel内容读至List<Student2>对象内
// 验证读取转换函数Student2ExpelConverter，注解 `@ExcelField(title = "是否开除", order = 5, readConverter = Student2ExpelConverter.class)`
@Test
public void testReadConverter() throws Exception {
String path = "D:\\JProject\\Excel4J\\src\\test\\resources\\students_02.xlsx";
List<Student2> students = ExcelUtils.getInstance().readExcel2Objects(path, Student2.class, 0, 0);
System.out.println("读取Excel至对象数组(支持类型转换)：");
for (Student2 st : students) {
System.out.println(st);
}
}
// 测试读取带有公式的单元格，并返回公式的值
@Test
public void testReadExcel_XLS() throws Exception {
String path = "D:\\JProject\\Excel4J\\src\\test\\resources\\StudentScore.xlsx";
System.out.println(Paths.get(path).toUri().getPath());
List<StudentScore> projectExcelModels = ExcelUtils.getInstance().readExcel2Objects(path, StudentScore.class);
System.out.println(projectExcelModels);
}
// 测试读取CSV文件
@Test
public void testReadCSV() throws Excel4JException {
List<Student2> list = ExcelUtils.getInstance().readCSV2Objects("J.csv", Student2.class);
for (Student2 student2 : list) {
System.out.println(student2);
}
}
}
Write
package base;
import com.github.crab2died.ExcelUtils;
import com.github.crab2died.exceptions.Excel4JException;
import com.github.crab2died.sheet.wrapper.MapSheetWrapper;
import com.github.crab2died.sheet.wrapper.NoTemplateSheetWrapper;
import com.github.crab2died.sheet.wrapper.NormalSheetWrapper;
import com.github.crab2died.sheet.wrapper.SimpleSheetWrapper;
import modules.Student1;
import modules.Student2;
import org.junit.Test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
public class Module2Excel {
//@Test
public void testObject2Excel() throws Exception {
String tempPath = "/normal_template2.xlsx";
List<Student1> list = new ArrayList<>();
list.add(new Student1("1010001", "盖伦", "六年级三班"));
list.add(new Student1("1010002", "古尔丹", "一年级三班"));
list.add(new Student1("1010003", "蒙多(被开除了)", "六年级一班"));
list.add(new Student1("1010004", "萝卜特", "三年级二班"));
list.add(new Student1("1010005", "奥拉基", "三年级二班"));
list.add(new Student1("1010006", "得嘞", "四年级二班"));
list.add(new Student1("1010007", "瓜娃子", "五年级一班"));
list.add(new Student1("1010008", "战三", "二年级一班"));
list.add(new Student1("1010009", "李四", "一年级一班"));
Map<String, String> data = new HashMap<>();//
//data.put("title", "战争学院花名册");
//data.put("info", "学校统一花名册");
// 基于模板导出Excel
//FileOutputStream os = new FileOutputStream(new File("A.xlsx"));
//ExcelUtils.getInstance().exportObjects2Excel(tempPath, list, data, Student1.class, false, os);
FileOutputStream temp = new FileOutputStream(new File("A1.xlsx"));
ExcelUtils.getInstance().exportObjects2Excel(list, Student1.class, temp);
//os.close();
//ExcelUtils.getInstance().exportObjects2Excel(tempPath, 0, list, data, Student1.class, true, "B1111111.xlsx");
//ExcelUtils.getInstance().exportObjects2Excel(templatePath, sheetIndex, data, extendMap, clazz, isWriteHeader, targetPath);
// 不基于模板导出Excel
//ExcelUtils.getInstance().exportObjects2Excel(list, Student1.class, true, null, true, "B.xlsx");
//ExcelUtils.getInstance().exportObjects2Excel(list, Student1.class, sheetName, isXSSF, targetPath);
//ExcelUtils.getInstance().exportObjects2Excel(list, Student1.class, true, "qqq", true, "B11.xlsx");
//System.out.println("333");
//ExcelUtils.getInstance().exportObjects2CSV(list, Student1.class, "JJ.csv");
}
// 基于模板、注解的多sheet导出
//@Test
public void testObject2BatchSheet() throws Exception {
List<NormalSheetWrapper> sheets = new ArrayList<>();
for (int i = 0; i < 2; i++) {
List<Student1> list = new ArrayList<>();
list.add(new Student1("1010001", "盖伦", "六年级三班"));
list.add(new Student1("1010002", "古尔丹", "一年级三班"));
list.add(new Student1("1010003", "蒙多(被开除了)", "六年级一班"));
list.add(new Student1("1010004", "萝卜特", "三年级二班"));
list.add(new Student1("1010005", "奥拉基", "三年级二班"));
list.add(new Student1("1010006", "得嘞", "四年级二班"));
list.add(new Student1("1010007", "瓜娃子", "五年级一班"));
list.add(new Student1("1010008", "战三", "二年级一班"));
list.add(new Student1("1010009", "李四", "一年级一班"));
Map<String, String> data = new HashMap<>();
data.put("title", "战争学院花名册");
data.put("info", "学校统一花名册");
sheets.add(new NormalSheetWrapper(i, list, data, Student1.class, false));
}
String tempPath = "D:\\JProject\\Excel4J\\src\\test\\resources\\normal_batch_sheet_template.xlsx";
FileOutputStream os = new FileOutputStream(new File("JK.xlsx"));
// 基于模板导出Excel
ExcelUtils.getInstance().normalSheet2Excel(sheets, tempPath, "AA.xlsx");
ExcelUtils.getInstance().normalSheet2Excel(sheets, tempPath, os);
os.close();
}
//@Test
public void testMap2Excel() throws Exception {
Map<String, List<?>> classes = new HashMap<>(); //不同的样式数据
Map<String, String> data = new HashMap<>();
data.put("title", "战争学院花名册");
data.put("info", "学校统一花名册");
classes.put("class_one", Arrays.asList(
new Student1("1010009", "李四", "一年级一班"),
new Student1("1010002", "古尔丹", "一年级三班")
));
classes.put("class_two", Collections.singletonList(
new Student1("1010008", "战三", "二年级一班")
));
classes.put("class_three", Arrays.asList(
new Student1("1010004", "萝卜特", "三年级二班"),
new Student1("1010005", "奥拉基", "三年级二班")
));
classes.put("class_four", Collections.singletonList(
new Student1("1010006", "得嘞", "四年级二班")
));
classes.put("class_six", Arrays.asList(
new Student1("1010001", "盖伦", "六年级三班"),
new Student1("1010003", "蒙多", "六年级一班")
));
//ExcelUtils.getInstance().exportMap2Excel("/map_template.xlsx",0, classes, data, Student1.class, false, "C.xlsx");
ExcelUtils.getInstance().exportMap2Excel("/map_template.xlsx", 0, classes, data, Student1.class, false, "c.xlsx");
//ExcelUtils.getInstance().exportMap2Excel
}
// Map数据的多sheet导出
//@Test
public void testMap2BatchSheet() throws Exception {
List<MapSheetWrapper> sheets = new ArrayList<>();
for (int i = 0; i < 2; i++) {
Map<String, List<?>> classes = new HashMap<>();
Map<String, String> data = new HashMap<>();
data.put("title", "战争学院花名册");
data.put("info", "学校统一花名册");
classes.put("class_one", Arrays.asList(
new Student1("1010009", "李四", "一年级一班"),
new Student1("1010002", "古尔丹", "一年级三班")
));
classes.put("class_two", Collections.singletonList(
new Student1("1010008", "战三", "二年级一班")
));
classes.put("class_three", Arrays.asList(
new Student1("1010004", "萝卜特", "三年级二班"),
new Student1("1010005", "奥拉基", "三年级二班")
));
classes.put("class_four", Collections.singletonList(
new Student1("1010006", "得嘞", "四年级二班")
));
classes.put("class_six", Arrays.asList(
new Student1("1010001", "盖伦", "六年级三班"),
new Student1("1010003", "蒙多", "六年级一班")
));
//sheets.add(new MapSheetWrapper(i, classes, data, Student1.class, false));
//new MapSheetWrapper(i, classes, data, Student1.class, false);
sheets.add(new MapSheetWrapper(i, classes, data, Student1.class, false));
}
//ExcelUtils.getInstance().mapSheet2Excel(sheets, "/map_batch_sheet_template.xlsx", "CC.xlsx");
ExcelUtils.getInstance().mapSheet2Excel(sheets, "/map_batch_sheet_template.xlsx", "CC1.xlsx");
System.out.println("CC1");
}
//@Test
public void testList2Excel() throws Exception {
List<List<String>> list2 = new ArrayList<>();
List<String> header = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<String> _list = new ArrayList<>();
for (int j = 0; j < 10; j++) {
_list.add(i + " -- " + j);
}
list2.add(_list);
header.add(i + "---栏");
}
ExcelUtils.getInstance().exportObjects2Excel(list2, header, "D.xlsx");
}
//@Test
public void uuid() throws IOException {
List<String> list = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
list.add(UUID.randomUUID().toString());
}
ExcelUtils.getInstance().exportObjects2Excel(list, new ArrayList<String>() {{
add("uuid");
}}, "J.xlsx");
}
// 验证日期转换函数 Student2DateConverter
// 注解 `@ExcelField(title = "入学日期", order = 3, writeConverter = Student2DateConverter.class)`
//@Test
public void testWriteConverter() throws Exception {
List<Student2> list = new ArrayList<>();
for (int i = 0; i < 1000; i++) {
list.add(new Student2(10000L + i, "学生" + i, new Date(), 201, false));
}
ExcelUtils.getInstance().exportObjects2Excel(list, Student2.class, true, "sheet0", true, "E.xlsx");
}
// 多sheet无模板、基于注解的导出
//@Test
public void testBatchNoTemplate2Excel() throws Exception {
List<NoTemplateSheetWrapper> sheets = new ArrayList<>();
for (int s = 0; s < 3; s++) {
List<Student2> list = new ArrayList<>();
for (int i = 0; i < 1000; i++) {
list.add(new Student2(10000L + i, "学生" + i, new Date(), 201, false));
}
//sheets.add(new NoTemplateSheetWrapper(list, Student2.class, true, "sheet_" + s));
//sheets.add(index, element);
sheets.add(new NoTemplateSheetWrapper(list, Student2.class, true, "sheet_" + s));
}
//ExcelUtils.getInstance().noTemplateSheet2Excel(sheets, "EE.xlsx");
ExcelUtils.getInstance().noTemplateSheet2Excel(sheets, "EE.xlsx");
}
// 多sheet无模板、无注解导出
//@Test
public void testBatchSimple2Excel() throws Exception {
// 生成sheet数据
List<SimpleSheetWrapper> list = new ArrayList<>();
for (int i = 0; i <= 2; i++) {
//表格内容数据
List<String[]> data = new ArrayList<>();
for (int j = 0; j < 1000; j++) {
// 行数据(此处是数组) 也可以是List数据
String[] rows = new String[5];
for (int r = 0; r < 5; r++) {
rows[r] = "sheet_" + i + "row_" + j + "column_" + r;
}
data.add(rows);
}
// 表头数据
List<String> header = new ArrayList<>();
for (int h = 0; h < 5; h++) {
header.add("column_" + h);
}
list.add(new SimpleSheetWrapper(data, header, "sheet_" + i));
}
ExcelUtils.getInstance().simpleSheet2Excel(list, "K.xlsx");
}
// 导出csv
//@Test
public void testExport2CSV() throws Excel4JException {
List<Student2> list = new ArrayList<>();
list.add(new Student2(1000001L, "张三", new Date(), 1, true));
list.add(new Student2(1010002L, "古尔丹", new Date(), 2, false));
list.add(new Student2(1010003L, "蒙多(被开除了)", new Date(), 6, true));
list.add(new Student2(1010004L, "萝卜特", new Date(), 3, false));
list.add(new Student2(1010005L, "奥拉基", new Date(), 4, false));
list.add(new Student2(1010006L, "得嘞", new Date(), 4, false));
list.add(new Student2(1010007L, "瓜娃子", new Date(), 5, true));
list.add(new Student2(1010008L, "战三", new Date(), 4, false));
list.add(new Student2(1010009L, "李四", new Date(), 2, false));
ExcelUtils.getInstance().exportObjects2CSV(list, Student2.class, "J.csv");
}
// 超大数据量导出csv
// 9999999数据本地测试小于1min
//@Test
public void testExport2CSV2() throws Excel4JException {
List<Student2> list = new ArrayList<>();
for (int i = 0; i < 9999999; i++) {
list.add(new Student2(1000001L + i, "路人 -" + i, new Date(), i % 6, true));
}
ExcelUtils.getInstance().exportObjects2CSV(list, Student2.class, "L.csv");
}
}
nomal_template
map_template
