Fetch the repository succeeded.
This action will force synchronization from 625/chain-poi-excel, which will overwrite any changes that you have made since you forked the repository, and can not be recovered!!!
Synchronous operation will process in the background and will refresh the page when finishing processing. Please be patient.
<!-- excel导入导出 -->
<dependency>
<groupId>com.github.stupdit1t</groupId>
<artifactId>poi-excel</artifactId>
<version>1.1</version>
</dependency>
// 1.获取源文件
Workbook wb = WorkbookFactory.create(new FileInputStream("src\\test\\java\\excel\\imports\\import.xlsx"));
// 2.获取sheet0导入
Sheet sheet = wb.getSheetAt(0);
// 3.生成VO数据
//参数:1.生成VO的class类型;2.校验规则;3.导入的sheet;3.从第几行导入;4.尾部非数据行数量
ImportRspInfo<ProjectEvaluate> list = ExcelUtils.parseSheet(ProjectEvaluate.class, EvaluateVerifyBuilder.getInstance(), sheet, 3, 2);
if (list.isSuccess()) {
// 导入没有错误,打印数据
System.out.println(JSON.toJSONString(list.getData()));
} else {
// 导入有错误,打印输出错误
System.out.println(list.getMessage());
}
// 1.获取源文件
Workbook wb = WorkbookFactory.create(new FileInputStream("src\\test\\java\\excel\\imports\\import.xlsx"));
// 2.获取sheet0导入
Sheet sheet = wb.getSheetAt(0);
// 3.生成VO数据
//参数:1.生成VO的class类型;2.校验规则;3.导入的sheet;3.从第几行导入;4.尾部非数据行数量;5.导入每条数据的回调
ImportRspInfo<ProjectEvaluate> list = ExcelUtils.parseSheet(ProjectEvaluate.class, ProjectVerifyBuilder.getInstance(), sheet, 3, 2, (row, rowNum) -> {
//1.此处可以完成更多的校验
if(row.getAreaName() == "中青旅"){
throw new POIException("第"+rowNum+"行,区域名字不能为中青旅!");
}
//2.图片导入,再ProjectEvaluate定义类型为byte[]的属性就可以,ProjectVerifyBuilder定义ImgVerfiy校验列.就OK了
});
if (list.isSuccess()) {
// 导入没有错误,打印数据
System.out.println(JSON.toJSONString(list.getData()));
//打印图片byte数组长度
byte[] img = list.getData().get(0).getImg();
System.out.println(img);
} else {
// 导入有错误,打印输出错误
System.out.println(list.getMessage());
}
public class ProjectVerifyBuilder extends AbstractVerifyBuidler {
private static ProjectVerifyBuilder builder = new ProjectVerifyBuilder();
public static ProjectVerifyBuilder getInstance() {
return builder;
}
/**
* 定义列校验实体:提取的字段、提取列、校验规则
*/
private ProjectVerifyBuilder() {
cellEntitys.add(new CellVerifyEntity("projectName", "B", new StringVerify("项目名称", true)));
cellEntitys.add(new CellVerifyEntity("areaName", "C", new StringVerify("所属区域", true)));
cellEntitys.add(new CellVerifyEntity("province", "D", new StringVerify("省份", true)));
cellEntitys.add(new CellVerifyEntity("city", "E", new StringVerify("市", true)));
cellEntitys.add(new CellVerifyEntity("people", "F", new StringVerify("项目所属人", true)));
cellEntitys.add(new CellVerifyEntity("leader", "G", new StringVerify("项目领导人", true)));
cellEntitys.add(new CellVerifyEntity("scount", "H", new IntegerVerify("总分", true)));
cellEntitys.add(new CellVerifyEntity("avg", "I", new DoubleVerify("历史平均分", true)));
cellEntitys.add(new CellVerifyEntity("createTime", "J", new DateTimeVerify("创建时间", "yyyy-MM-dd HH:mm", true)));
cellEntitys.add(new CellVerifyEntity("img", "K", new ImgVerify("图片", false)));
// 必须调用
super.init();
}
}
// 1.获取导出的数据体
List<ProjectEvaluate> data = new ArrayList<ProjectEvaluate>();
for (int i = 0; i < 10; i++) {
ProjectEvaluate obj = new ProjectEvaluate();
obj.setProjectName("中青旅" + i);
obj.setAreaName("华东长三角");
obj.setProvince("河北省");
obj.setCity("保定市");
obj.setPeople("张三" + i);
obj.setLeader("李四" + i);
obj.setScount(50);
obj.setAvg(60.0);
obj.setCreateTime(new Date());
obj.setImg(ExcelUtils.ImageParseBytes(new File("src/test/java/excel/export/1.png")));
data.add(obj);
}
// 2.导出标题设置,可为空
String title = "项目资源统计";
// 3.导出的hearder设置
String[] hearder = { "序号", "项目名称", "所属区域", "省份", "市", "项目所属人", "项目领导人", "得分", "平均分", "创建时间", "项目图片" };
// 4.导出hearder对应的字段设置
Column[] column = {
Column.field("projectName"),
Column.field("areaName"),
Column.field("province"),
Column.field("city"),
Column.field("people"),
Column.field("leader"),
Column.field("scount"),
Column.field("avg"),
Column.field("createTime"),
// 项目图片
Column.field("img")
};
// 5.执行导出到工作簿
// ExportRules:1.是否序号;2.列设置;3.标题设置可为空;4.表头设置;5.表尾设置可为空
Workbook bean = ExcelUtils.createWorkbook(data, new ExportRules(true, column, title, hearder, null));
// 6.写出文件
bean.write(new FileOutputStream("src/test/java/excel/export/export1.xls"));
// 1.获取导出的数据体
List<ProjectEvaluate> data = new ArrayList<ProjectEvaluate>();
for (int i = 0; i < 50; i++) {
ProjectEvaluate obj = new ProjectEvaluate();
obj.setProjectName("中青旅" + i);
obj.setAreaName("华东长三角");
obj.setProvince("河北省");
obj.setCity("保定市");
obj.setPeople("张三" + i);
obj.setLeader("李四" + i);
obj.setScount(50);
obj.setAvg(60.0);
obj.setCreateTime(new Date());
obj.setImg(ExcelUtils.ImageParseBytes(new File("src/test/java/excel/export/1.png")));
data.add(obj);
}
// 2.表头设置,可以对应excel设计表头,一看就懂
HashMap<String, String> headerRules = new HashMap<>();
headerRules.put("1,1,A,K", "项目资源统计");
headerRules.put("2,3,A,A", "序号");
headerRules.put("2,2,B,E", "基本信息");
headerRules.put("3,3,B,B", "项目名称");
headerRules.put("3,3,C,C", "所属区域");
headerRules.put("3,3,D,D", "省份");
headerRules.put("3,3,E,E", "市");
headerRules.put("2,3,F,F", "项目所属人");
headerRules.put("2,3,G,G", "市项目领导人");
headerRules.put("2,2,H,I", "分值");
headerRules.put("3,3,H,H", "得分");
headerRules.put("3,3,I,I", "平均分");
headerRules.put("2,3,J,J", "创建时间");
headerRules.put("2,3,K,K", "项目图片");
// 3.尾部设置,一般可以用来设计合计栏
HashMap<String, String> footerRules = new HashMap<>();
footerRules.put("1,2,A,C", "注释:");
footerRules.put("1,2,D,K", "导出参考代码!");
// 4.导出hearder对应的字段设置
Column[] column = {
Column.field("projectName"),
// 4.1设置此列宽度为10
Column.field("areaName")
.width(10),
// 4.2设置此列下拉框数据
Column.field("province")
.width(5)
.dorpDown(new String[] { "陕西省", "山西省", "辽宁省" }),
// 4.3设置此列水平居右
Column.field("city")
.align(HorizontalAlignment.RIGHT),
// 4.4 设置此列垂直居上
Column.field("people")
.valign(VerticalAlignment.TOP),
// 4.5 设置此列单元格 自定义校验 只能输入文本为数字文本,具体参考excel函数,不同的地方在于参数的下标从当前单元格A和1开始
Column.field("leader")
.verifyCustom("VALUE(F3:F500)","请输入数字!"),
// 4.6设置此列单元格 整数 数据校验 ,同时设置背景色为棕色
Column.field("scount")
.verifyIntNum("10~20")
.backColor(IndexedColors.BROWN),
// 4.7设置此列单元格 浮点数 数据校验, 同时设置字体颜色红色
Column.field("avg")
.verifyFloatNum("10.0~20.0")
.color(IndexedColors.RED),
// 4.8设置此列单元格 日期 数据校验 ,同时宽度为20、限制用户表格输入、水平居中、垂直居中、背景色、字体颜色
Column.field("createTime")
.width(20)
.verifyDate("2000-01-03 12:35~3000-05-06 23:23")
.align(HorizontalAlignment.LEFT)
.valign(VerticalAlignment.CENTER)
.backColor(IndexedColors.YELLOW)
.color(IndexedColors.GOLD),
// 4.9项目图片
Column.field("img")
};
// 5.执行导出到工作簿
// ExportRules:1.是否序号;2.列设置;3.标题设置可为空;4.表头设置;5.表尾设置可为空
Workbook bean = ExcelUtils.createWorkbook(data, new ExportRules(true, column, headerRules, footerRules), (fieldName, value, row, col) -> {
if ("projectName".equals(fieldName) && row.getProjectName().equals("中青旅23")) {
col.align(HorizontalAlignment.LEFT);
col.valign(VerticalAlignment.CENTER);
col.height(2);
col.backColor(IndexedColors.RED);
col.color(IndexedColors.YELLOW);
}
return value;
});
// 6.写出文件
bean.write(new FileOutputStream("src/test/java/excel/export/export2.xls"));
// 1.获取导出的数据体
List<Student> data = new ArrayList<Student>();
for (int i = 0; i < 10; i++) {
// 學生
Student stu = new Student();
// 學生所在的班級,用對象
stu.setClassRoom(new ClassRoom("六班"));
// 學生的更多信息,用map
Map<String, Object> moreInfo = new HashMap<>();
moreInfo.put("parent", new Parent("張無忌"));
stu.setMoreInfo(moreInfo);
stu.setName("张三");
data.add(stu);
}
// 2.导出标题设置,可为空
String title = "學生基本信息";
// 3.导出的hearder设置
String[] hearder = { "學生姓名", "所在班級", "所在學校", "更多父母姓名" };
// 4.导出hearder对应的字段设置,列宽设置
Column[] column = { Column.field("name"), Column.field("classRoom.name"), Column.field("classRoom.school.name"), Column.field("moreInfo.parent.name"), };
// 5.执行导出到工作簿
// ExportRules:1.是否序号;2.字段信息;3.标题设置可为空;4.表头设置;5.表尾设置可为空
Workbook bean = ExcelUtils.createWorkbook(data, new ExportRules(false, column, title, hearder, null));
// 6.写出文件
bean.write(new FileOutputStream("src/test/java/excel/export/export3.xls"));
// 1.获取导出的数据体
List<Map<String, String>> data = new ArrayList<Map<String, String>>();
for (int i = 0; i < 10; i++) {
// 學生
Map<String, String> map = new HashMap<>();
map.put("name", "张三");
map.put("classRoomName", "三班");
map.put("school", "世纪中心");
map.put("parent", "张无忌");
data.add(map);
}
// 2.导出标题设置,可为空
String title = "學生基本信息";
// 3.导出的hearder设置
String[] hearder = { "學生姓名", "所在班級", "所在學校", "更多父母姓名" };
// 4.导出hearder对应的字段设置,列宽设置
Column[] column = { Column.field("name"), Column.field("classRoomName"), Column.field("school"), Column.field("parent"), };
// 5.执行导出到工作簿
// ExportRules:1.是否序号;2.字段信息;3.标题设置可为空;4.表头设置;5.表尾设置可为空
Workbook bean = ExcelUtils.createWorkbook(data, new ExportRules(false, column, title, hearder, null));
// 6.写出文件
bean.write(new FileOutputStream("src/test/java/excel/export/export4.xls"));
// 1.导出标题设置,可为空
String title = "客户导入";
// 2.导出的hearder设置
String[] hearder = { "宝宝姓名", "宝宝昵称", "家长姓名", "手机号码","宝宝生日","月龄","宝宝性别","来源渠道","市场人员","咨询顾问","客服顾问","分配校区","备注" };
// 3.导出hearder对应的字段设置,列宽设置
Column[] column = {
Column.field("宝宝姓名"),
Column.field("宝宝昵称"),
Column.field("家长姓名"),
Column.field("手机号码").verifyText("11~11", "请输入11位的手机号码!"),
Column.field("宝宝生日").verifyDate("2000-01-01~3000-12-31"),
Column.field("月龄").width(4).verifyCustom("VALUE(F3:F6000)", "月齡格式:如1年2个月则输入14"),
Column.field("宝宝性别").dorpDown(new String[] {"男","女"}),
Column.field("来源渠道").width(12).dorpDown(new String[] { "品推", "市场" }), Column.field("市场人员").width(6).dorpDown(new String[] { "张三", "李四" }),
Column.field("咨询顾问").width(6).dorpDown(new String[] { "张三", "李四" }), Column.field("客服顾问").width(6).dorpDown(new String[] { "大唐", "银泰" }),
Column.field("分配校区").width(6).dorpDown(new String[] { "大唐", "银泰" }),
Column.field("备注")
};
// 5.执行导出到工作簿
Workbook bean = ExcelUtils.createWorkbook(Collections.emptyList(), new ExportRules(false, column, title, hearder, null).setXlsx(true));
// 6.写出文件
bean.write(new FileOutputStream("src/test/java/excel/export/export5.xlsx"));
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。