我要评论1.引入依赖
<dependency>
<groupid>com.alibaba</groupid>
<artifactid>easyexcel</artifactid>
<version>2.2.11</version>
</dependency>2.读取器代码：
package com.visy.utils;
import com.alibaba.excel.easyexcel;
import com.alibaba.excel.cache.mapcache;
import com.alibaba.excel.context.analysiscontext;
import com.alibaba.excel.event.analysiseventlistener;
import lombok.builder;
import lombok.data;
import lombok.extern.slf4j.slf4j;
import org.springframework.web.multipart.multipartfile;
import java.io.ioexception;
import java.util.arraylist;
import java.util.hashmap;
import java.util.list;
import java.util.map;
import java.util.objects;
import java.util.function.consumer;
/**
* @author visy.wang
* @description: excel读取器
* @date 2023/8/18 11:03
*/
@slf4j
public class excelreader {
public static void read(
string filename,
consumer<rowdata> reader,
consumer<integer> terminator
){
nomodeldatalistener listener = nomodeldatalistener.create(reader, terminator);
easyexcel.read(filename, listener).readcache(new mapcache()).sheet().doread();
}
public static void read(
string filename,
consumer<rowdata> reader
){
read(filename, reader, null);
}
public static void read(
multipartfile file,
consumer<rowdata> reader,
consumer<integer> terminator
) throws ioexception {
nomodeldatalistener listener = nomodeldatalistener.create(reader, terminator);
easyexcel.read(file.getinputstream(), listener).readcache(new mapcache()).sheet().doread();
}
public static void read(
multipartfile file,
consumer<rowdata> reader
) throws ioexception {
read(file, reader, null);
}
private static class nomodeldatalistener extends analysiseventlistener<map<integer, string>> {
private int totalrows = 0;//读取总行数
private static final int batch_size = 1000; //分批次，每一次读取的行数
private final map<integer, string> headmap = new hashmap<>();
private final list<rowdata> list = new arraylist<>(batch_size);
private final consumer<rowdata> reader;
private final consumer<integer> terminator;
public static nomodeldatalistener create(
consumer<rowdata> reader,
consumer<integer> terminator
){
return new nomodeldatalistener(reader, terminator);
}
private nomodeldatalistener(
consumer<rowdata> reader,
consumer<integer> terminator
){
this.reader = reader;
this.terminator = terminator;
}
@override
public void invoke(map<integer, string> data, analysiscontext context) {
totalrows ++; //统计数据行数（排除表头）
invoke(data, context.readrowholder().getrowindex());
}
@override
public void invokeheadmap(map<integer, string> head, analysiscontext context) {
headmap.putall(head);//记录表头
invoke(head, context.readrowholder().getrowindex());
}
@override
public void doafterallanalysed(analysiscontext context) {
flush();
if(objects.nonnull(terminator)){
terminator.accept(totalrows);
}
}
private void flush(){
if(!list.isempty()){
doread();
}
}
private void invoke(map<integer, string> data, integer rowindex){
list.add(rowdata.builder().rowindex(rowindex).data(format(data)).build());
if(list.size() >= batch_size){
doread();
list.clear();
}
}
private void doread(){
list.foreach(reader);
}
private map<string,string> format(map<integer, string> row){
map<string,string> result = new hashmap<>();
if(headmap.isempty()){
row.foreach((colindex, value) -> {
result.put("column"+colindex, value);
});
return result;
}
headmap.foreach((colindex, title) -> {
string value = row.get(colindex);
result.put(title.trim(), value);
});
return result;
}
}
@data
@builder
public static class rowdata {
private integer rowindex;
private map<string, string> data;
/**
* 翻译
* @param dict 字典<excel标题，自定义字段名>
* @return 翻译后的data
*/
public map<string,string> translate(map<string,string> dict){
if(dict==null || dict.isempty()){
return data;
}
string newkey;
for (string key : data.keyset().toarray(new string[0])) {
if((newkey = dict.get(key)) != null){
data.put(newkey, data.remove(key));
}
}
return data;
}
}
}
3.测试
package com.visy.utils;
import com.alibaba.fastjson.json;
import lombok.extern.slf4j.slf4j;
import java.util.hashmap;
import java.util.map;
/**
* @author visy.wang
* @date 2024/9/29 18:11
*/
@slf4j
public class excelreadertest {
public static void main(string[] args) {
string filename = "d:\\test\\订单.xls";
map<string, string> dict = new hashmap<>();
dict.put("项目编码", "projectno");
dict.put("订单编号", "orderno");
excelreader.read(filename, rowdata -> {
int rowno = rowdata.getrowindex();
if(rowno == 0){
log.info("第{}行,表头：{}", rowno+1, json.tojsonstring(rowdata.translate(dict)));
}else{
log.info("第{}行，数据：{}", rowno+1, json.tojsonstring(rowdata.translate(dict)));
}
}, total -> {
log.info("所有数据已读取完毕！！！");
log.info("一共读取了{}行数据", total);
});
}
}
4.输出
18:16:48,806 info excelreadertest:23 - 第1行,表头：{"序号":"序号","orderno":"订单编号","projectno":"项目编码","本次打款总金额":"本次打款总金额","结算单号":"结算单号"}
18:16:48,808 info excelreadertest:25 - 第2行，数据：{"序号":"1","orderno":"zc1839511598451183623","projectno":"kpzcj1337083789442","本次打款总金额":"8.00元","结算单号":"gsd1839485349116825600"}
18:16:48,809 info excelreadertest:25 - 第3行，数据：{"序号":"2","orderno":"zc1839511598451183623","projectno":"kpzcj1337083789442","本次打款总金额":"8.00元","结算单号":"gsd1839485422676529152"}
18:16:48,809 info excelreadertest:25 - 第4行，数据：{"序号":"3","orderno":"zc1839504517895606281","projectno":"xdfw1409599599521","本次打款总金额":"660.00元","结算单号":"gsd1839504154316558336"}
18:16:48,809 info excelreadertest:25 - 第5行，数据：{"序号":"4","orderno":"zc1839504517895606281","projectno":"xdfw1409599599521","本次打款总金额":"660.00元","结算单号":"gsd1839504199178833920"}
18:16:48,810 info excelreadertest:25 - 第6行，数据：{"序号":"5","orderno":"zc1839504517895606281","projectno":"xdfw1409599599521","本次打款总金额":"660.00元","结算单号":"gsd1839504234847195136"}
18:16:48,810 info excelreadertest:25 - 第7行，数据：{"序号":"6","orderno":"zc1821415146298589186","projectno":"dy11432043204258","本次打款总金额":"10.30元","结算单号":"gsd1821414066009456640"}
18:16:48,810 info excelreadertest:25 - 第8行，数据：{"序号":"7","orderno":"zc1808791262713888770","projectno":"kpzcj14131713172905","本次打款总金额":"2.40元","结算单号":"gsd1808752241082122240"}
18:16:48,811 info excelreadertest:25 - 第9行，数据：{"序号":"8","orderno":"zc1803003717728751618","projectno":"kpzcj17264326435074","本次打款总金额":"4.22元","结算单号":"gsd1803002522666684416"}
18:16:48,811 info excelreadertest:25 - 第10行，数据：{"序号":"9","orderno":"zc1802909083988021250","projectno":"kpzcj10285028505852","本次打款总金额":"140.18元","结算单号":"gsd1802908824310272000"}
18:16:48,811 info excelreadertest:25 - 第11行，数据：{"序号":"10","orderno":"zc1752938634642669569","projectno":"dy16231023106597","本次打款总金额":"326.94元","结算单号":"gsd1752938314889904128"}
18:16:48,811 info excelreadertest:25 - 第12行，数据：{"序号":"11","orderno":"zc1751809098261639169","projectno":"dy11211521153170","本次打款总金额":"165.11元","结算单号":"gsd1751806326732709888"}
18:16:48,811 info excelreadertest:25 - 第13行，数据：{"序号":"12","orderno":"zc1751803125170135042","projectno":"dy11211521153170","本次打款总金额":"990.66元","结算单号":"gsd1751802752413949952"}
18:16:48,811 info excelreadertest:25 - 第14行，数据：{"序号":"13","orderno":"zc1749719747436433410","projectno":"dy16524552459874","本次打款总金额":"79.66元","结算单号":"gsd1749719630004310016"}
18:16:48,811 info excelreadertest:25 - 第15行，数据：{"序号":"14","orderno":"zc1748169486398042114","projectno":"dy1004364367372","本次打款总金额":"199.13元","结算单号":"gsd1748167443449032704"}
18:16:48,812 info excelreadertest:25 - 第16行，数据：{"序号":"15","orderno":"zc1747882440458670080","projectno":"dy11121112110728","本次打款总金额":"36.32元","结算单号":"gsd1747882279347064832"}
18:16:48,813 info excelreadertest:25 - 第17行，数据：{"序号":"16","orderno":"zc1747874805231616001","projectno":"dy11121112110728","本次打款总金额":"36.32元","结算单号":"gsd1747874605553385472"}
18:16:48,813 info excelreadertest:25 - 第18行，数据：{"序号":"17","orderno":"zc1734088151878545408","projectno":"dy11104310438950","本次打款总金额":"22.00元","结算单号":"gsd1734087575031721984"}
18:16:48,813 info excelreadertest:25 - 第19行，数据：{"序号":"18","orderno":"zc1734049237461590018","projectno":"dy11104310438950","本次打款总金额":"22.00元","结算单号":"gsd1734049137159004160"}
18:16:48,813 info excelreadertest:25 - 第20行，数据：{"序号":"19","orderno":"zc16999558515156526","projectno":"kpzcj16141014108222","本次打款总金额":"7.07元","结算单号":"gsd16996681699402290"}
18:16:48,813 info excelreadertest:25 - 第21行，数据：{"序号":"20","orderno":"zc16999548202909446","projectno":"kpzcj16141014108222","本次打款总金额":"2.02元","结算单号":"gsd16996686241470609"}
18:16:48,813 info excelreadertest:25 - 第22行，数据：{"序号":"21","orderno":"zc16994333820644684","projectno":"dy16445644569459","本次打款总金额":"6.00元","结算单号":"gsd16994333480323046"}
18:16:48,813 info excelreadertest:25 - 第23行，数据：{"序号":"22","orderno":"zc16994329855599490","projectno":"dy16361336133168","本次打款总金额":"12.00元","结算单号":"gsd16994328986634071"}
18:16:48,813 info excelreadertest:25 - 第24行，数据：{"序号":"23","orderno":"zc16994311498202085","projectno":"dy15303630362489","本次打款总金额":"6.00元","结算单号":"gsd16994289014792143"}
18:16:48,814 info excelreadertest:25 - 第25行，数据：{"序号":"24","orderno":"zc16994307629649811","projectno":"dy15261026100064","本次打款总金额":"12.00元","结算单号":"gsd16994302164484906"}
18:16:48,814 info excelreadertest:25 - 第26行，数据：{"序号":"25","orderno":"zc16927707974275190","projectno":"dy110400409587","本次打款总金额":"79.66元","结算单号":"gsd16927707572422541"}
18:16:48,815 info excelreadertest:25 - 第27行，数据：{"序号":"26","orderno":"zc16925872256331116","projectno":"dy110400409587","本次打款总金额":"79.66元","结算单号":"gsd16925872037735498"}
18:16:48,815 info excelreadertest:25 - 第28行，数据：{"序号":"27","orderno":"zc16925846964571442","projectno":"dy110400409587","本次打款总金额":"39.83元","结算单号":"gsd16925844343207431"}
18:16:48,815 info excelreadertest:25 - 第29行，数据：{"序号":"28","orderno":"zc16925846510308639","projectno":"dy110400409587","本次打款总金额":"39.83元","结算单号":"gsd16925844596560052"}
18:16:48,815 info excelreadertest:25 - 第30行，数据：{"序号":"29","orderno":"zc16919940906941897","projectno":"xdfw14144314434895","本次打款总金额":"10500.00元","结算单号":"gsd16787892369452546"}
18:16:48,815 info excelreadertest:25 - 第31行，数据：{"序号":"30","orderno":"zc16905399739424309","projectno":"dy17584958493645","本次打款总金额":"9.00元","结算单号":"gsd16905399441575256"}
18:16:48,816 info excelreadertest:25 - 第32行，数据：{"序号":"31","orderno":"zc16905396284758057","projectno":"dy10152215222400","本次打款总金额":"3.06元","结算单号":"gsd16738389 649603486"}
18:16:48,816 info excelreadertest:25 - 第33行，数据：{"序号":"32","orderno":"zc16899050424420841","projectno":"dy1406526528402","本次打款总金额":"1864.80元","结算单号":"gsd16786909746185396"}
18:16:48,816 info excelreadertest:25 - 第34行，数据：{"序号":"33","orderno":"zc16892358221182248","projectno":"dy1728032832239","本次打款总金额":"866.55元","结算单号":"gsd16892357362093868"}
18:16:48,816 info excelreadertest:25 - 第35行，数据：{"序号":"34","orderno":"zc16892354367835727","projectno":"kpzcj14564156418064","本次打款总金额":"3999.98元","结算单号":"gsd16892353931173843"}
18:16:48,816 info excelreadertest:25 - 第36行，数据：{"序号":"35","orderno":"zc16890449150466564","projectno":"kpzcj17354735471383","本次打款总金额":"29.91元","结算单号":"gsd16890448923257346"}
18:16:48,816 info excelreadertest:25 - 第37行，数据：{"序号":"36","orderno":"zc16872536243521878","projectno":"dy1058035839472","本次打款总金额":"2.00元","结算单号":"gsd16872535451495391"}
18:16:48,817 info excelreadertest:25 - 第38行，数据：{"序号":"37","orderno":"zc16868187396317669","projectno":"szbf11313331330765","本次打款总金额":"1.02元","结算单号":"gsd16868183981406607"}
18:16:48,817 info excelreadertest:25 - 第39行，数据：{"序号":"38","orderno":"zc16855920288559728","projectno":"xdfw10492049206753","本次打款总金额":"3.00元","结算单号":"gsd16632261371210492"}
18:16:48,817 info excelreadertest:25 - 第40行，数据：{"序号":"39","orderno":"zc16850945519224107","projectno":"kpzcj1006476478025","本次打款总金额":"4.00元","结算单号":"gsd16850943918013630"}
18:16:48,817 info excelreadertest:25 - 第41行，数据：{"序号":"40","orderno":"zc16850834105212888","projectno":"kpzcj09285228529226","本次打款总金额":"30.00元","结算单号":"gsd16850832789336942"}
18:16:48,817 info excelreadertest:25 - 第42行，数据：{"序号":"41","orderno":"zc16842058367487716","projectno":"dy17591159110698","本次打款总金额":"75.60元","结算单号":"gsd16842057388913427"}
18:16:48,817 info excelreadertest:25 - 第43行，数据：{"序号":"42","orderno":"zc16800783925056545","projectno":"kpzcj11104710473286","本次打款总金额":"20.00元","结算单号":"gsd16800783250594232"}
18:16:48,818 info excelreadertest:25 - 第44行，数据：{"序号":"43","orderno":"zc16795435813626761","projectno":"kpzcj1406516514663","本次打款总金额":"10.00元","结算单号":"gsd16723051679897456"}
18:16:48,818 info excelreadertest:25 - 第45行，数据：{"序号":"44","orderno":"zc16790400403067732","projectno":"dy10102010200793","本次打款总金额":"1050.00元","结算单号":"gsd16790398836718985"}
18:16:48,819 info excelreadertest:25 - 第46行，数据：{"序号":"45","orderno":"zc16788812382862096","projectno":"kpzcj16341034103659","本次打款总金额":"3500.00元","结算单号":"gsd16787998193851173"}
18:16:48,819 info excelreadertest:25 - 第47行，数据：{"序号":"46","orderno":"zc16788812382862096","projectno":"kpzcj16341034103659","本次打款总金额":"3500.00元","结算单号":"gsd16788809089372484"}
18:16:48,819 info excelreadertest:25 - 第48行，数据：{"序号":"47","orderno":"zc16788812382862096","projectno":"kpzcj16341034103659","本次打款总金额":"3500.00元","结算单号":"gsd16788809206582070"}
18:16:48,819 info excelreadertest:25 - 第49行，数据：{"序号":"48","orderno":"zc16788743433892843","projectno":"szwj1608298295724","本次打款总金额":"17.85元","结算单号":"gsd16788743263213068"}
18:16:48,819 info excelreadertest:25 - 第50行，数据：{"序号":"49","orderno":"zc16788740588721943","projectno":"kpzcj16341034103659","本次打款总金额":"500.00元","结算单号":"gsd16787998193851173"}
18:16:48,819 info excelreadertest:25 - 第51行，数据：{"序号":"50","orderno":"zc16788739134573304","projectno":"kpzcj16341034103659","本次打款总金额":"500.00元","结算单号":"gsd16788738549722418"}
18:16:48,820 info excelreadertest:25 - 第52行，数据：{"序号":"51","orderno":"zc16788716782212770","projectno":"dy1103153151761","本次打款总金额":"1.00元","结算单号":"gsd16788715186262758"}
18:16:48,820 info excelreadertest:25 - 第53行，数据：{"序号":"52","orderno":"zc16788654801253827","projectno":"kpzcj16341034103659","本次打款总金额":"500.00元","结算单号":"gsd16787998193851173"}
18:16:48,820 info excelreadertest:25 - 第54行，数据：{"序号":"53","orderno":"zc16787979223672877","projectno":"xdfw18294329432132","本次打款总金额":"10000.00元","结算单号":"gsd16787978625297159"}
18:16:48,820 info excelreadertest:25 - 第55行，数据：{"序号":"54","orderno":"zc16787886778675181","projectno":"xdfw14144314434895","本次打款总金额":"10500.00元","结算单号":"gsd16787774522225825"}
18:16:48,820 info excelreadertest:25 - 第56行，数据：{"序号":"55","orderno":"zc16787653644827069","projectno":"szwj1504284280423","本次打款总金额":"500.00元","结算单号":"gsd16787653162134561"}
18:16:48,820 info excelreadertest:25 - 第57行，数据：{"序号":"56","orderno":"zc16787612066579887","projectno":"dy10282728275505","本次打款总金额":"1.00元","结算单号":"gsd16787611608613990"}
18:16:48,820 info excelreadertest:25 - 第58行，数据：{"序号":"57","orderno":"zc16786771793559126","projectno":"dy1537003703480","本次打款总金额":"330.22元","结算单号":"gsd16686513487010319"}
18:16:48,820 info excelreadertest:25 - 第59行，数据：{"序号":"58","orderno":"zc16775798382046422","projectno":"dy17221322139928","本次打款总金额":"1.03元","结算单号":"gsd16639253044471437"}
18:16:48,821 info excelreadertest:25 - 第60行，数据：{"序号":"59","orderno":"zc16775798382040667","projectno":"dy13435143514308","本次打款总金额":"8.24元","结算单号":"gsd16637505266694295"}
18:16:48,822 info excelreadertest:25 - 第61行，数据：{"序号":"60","orderno":"zc16768736820910414","projectno":"dy1734043445892","本次打款总金额":"12.00元","结算单号":"gsd16768736573639000"}
18:16:48,822 info excelreadertest:25 - 第62行，数据：{"序号":"61","orderno":"zc16738489062053782","projectno":"dy09541754174685","本次打款总金额":"4.22元","结算单号":"gsd16738487696523049"}
18:16:48,822 info excelreadertest:25 - 第63行，数据：{"序号":"62","orderno":"zc16732451795398771","projectno":"dy13575957594749","本次打款总金额":"2.04元","结算单号":"gsd16732450923115297"}
18:16:48,822 info excelreadertest:25 - 第64行，数据：{"序号":"63","orderno":"zc16717828381432892","projectno":"kpzcj09531953193955","本次打款总金额":"100.99元","结算单号":"gsd16697992239288898"}
18:16:48,822 info excelreadertest:29 - 所有数据已读取完毕！！！
18:16:48,822 info excelreadertest:30 - 一共读取了63行数据
到此这篇关于java基于easyexcel实现读取excel文件详解的文章就介绍到这了,更多相关java easyexcel读取excel内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论