Java中excel表数据的批量导入方法

2023-09-12 14:58:05 60

本文实例为大家分享了Java中excel表数据的批量导入，供大家参考，具体内容如下

首先看下工具类：

importjava.awt.Color;
importjava.io.ByteArrayInputStream;
importjava.io.ByteArrayOutputStream;
importjava.io.File;
importjava.io.FileInputStream;
importjava.io.InputStream;
importjava.lang.reflect.Field;
importjava.text.DateFormat;
importjava.text.DecimalFormat;
importjava.text.SimpleDateFormat;
importjava.util.*;

importjavax.swing.text.AttributeSet;
importjavax.swing.text.Element;
importjavax.swing.text.html.CSS;
importjavax.swing.text.html.HTMLDocument;
importjavax.swing.text.html.HTMLEditorKit;

importcn.vrview.dev.common.exception.BusinessException;
importorg.apache.commons.lang3.StringUtils;
importorg.apache.logging.log4j.LogManager;
importorg.apache.logging.log4j.Logger;
importorg.apache.poi.hssf.usermodel.*;
importorg.apache.poi.hssf.util.HSSFColor;
importorg.apache.poi.ss.usermodel.*;
importorg.apache.poi.ss.util.CellRangeAddress;
importorg.apache.poi.xssf.usermodel.XSSFColor;
importorg.apache.poi.xssf.usermodel.XSSFFont;
importorg.apache.poi.xssf.usermodel.XSSFWorkbook;
importorg.springframework.web.util.HtmlUtils;

importcn.vrview.dev.common.util.StringUtil;


publicclassExcelTools{

/**log*/
privatestaticLoggerlog=LogManager.getLogger();

/**
*导出excel
*
*使用方法：

*List>dataList=newArrayList>();

*is=ExcelTools.exportXLS(dataList,newString[]{"createTime:日期","name:名称","sex:性别","remark:备注"});
*
*
*@paramcollect
*待导出的数据集合
*@paramheader
*要导出的列
*@returnInputStream返回文件流
*/
publicstaticInputStreamexportXLS(Collection>collect,String[]header){
ByteArrayOutputStreamout=newByteArrayOutputStream();
HSSFWorkbookbook=newHSSFWorkbook();
try{
//添加一个sheet
HSSFSheetsheet=book.createSheet("Sheet1");
//定义要导出的列名集合
Setcolumns=newHashSet();

//设置单元格背景色
HSSFCellStylecellStyle=book.createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(newHSSFColor.YELLOW().getIndex());

//生成表头
HSSFRowrow=sheet.createRow(0);
HSSFCellcell=row.createCell(0);
cell.setCellStyle(cellStyle);
cell.setCellValue("序号");
//列号从1开始
intn=1;
//解析头字符串
for(Stringstr:header){
String[]arr=str.split(":");
columns.add(n+","+arr[0]);//添加要导出的字段名并且与列号n绑定
cell=row.createCell(n);
cell.setCellStyle(cellStyle);
cell.setCellValue(arr[1]);
n++;
}

//生成数据行从1开开始，0为表头
inti=1;
//生成数据行列
for(Mapmap:collect){
HSSFRowdataRow=sheet.createRow(i);

//生成序号
dataRow.createCell(0).setCellValue(i);
//生成其他列
for(Stringcolumn:columns){
//用逗号分割获得字段名，[0]为列号用于和表头标题对应上
StringcolumnName=column.split(",")[1];
//生成序号列
cell=dataRow.createCell(Integer.parseInt(column.split(",")[0]));
Stringvalue="";
value=map.get(columnName)+"";
//当value为null时转换为""
if("null".equals(value)){
value="";
}
RichTextStringrichTextString=processHtml(book,value);
cell.getCellStyle().setWrapText(false);
cell.setCellValue(richTextString);
}
i++;
}
book.write(out);
out.close();
}catch(Exceptione){
e.printStackTrace();
}
returnnewByteArrayInputStream(out.toByteArray());
}

/**
*获得excel文件数据

*用法：

*SheetInfosheetInfo=newExcelTools().newSheetInfo();

*sheetInfo.setRowTitle(0);Listsheets=newArrayList();

*StringsheetName="Sheet1";sheets.add(sheetName);

*sheetInfo.setSheetNames(sheets);

*sheetInfo.setColumnsMapping(newString[]{"prodName:商品名称",
*"prodSpec:规格","collectPrice:价格:"+{@linkRegExpEnum}
*RegExpEnum.NOTEMPTY_ISNUMBER,"priceUnit:单位","collectMarket:报价市场",
*"prodLevel:等级"});

*Mapdata=ExcelTools.getExcel(newFile(path),sheetInfo);
*
*@param
*
*@paramsheetInfo
*初始化信息
*@returnMap{sheet1:List}
*@throwsException
*Exception
*/
@SuppressWarnings("rawtypes")
publicstaticMapgetExcel(Filef,SheetInfosheetInfo,StringexcelType)throwsException{
returngetExcel(newFileInputStream(f),sheetInfo,excelType);
}

@SuppressWarnings({"rawtypes","unchecked"})
publicstaticMapgetExcel(InputStreamin,SheetInfosheetInfo,StringexcelType)throwsException{
MapcolumnsMap=newHashMap();
//列验证表达式map
ListerrMsg=newArrayList();
interrNum=0;//错误总数
interrLimit=10;//限制错误提示数
/**用于存储Excel根据指定规则读取的所有内容*/
MapexcelInfo=newHashMap();
Workbookbook=null;
try{
if(excelType.equals("xls")){
book=newHSSFWorkbook(in);
//thrownewBusinessException("excel版本太低，请使用2007以上版本（扩展名为：xlsx）");
}else{
book=newXSSFWorkbook(in);
}
}catch(OutOfMemoryErrore){
thrownewRuntimeException("当前excel文件过大，请检查各个sheet表格中是否有无效空数据，包括带有格式和线框的行列数据，请删除这些无效数据！正常excle文件大小【1M】以内");
}
//checkTitle(book,sheetInfo);
//获得工作表数量
intsheetNum=sheetInfo.getSheetNames().size();
//循环所有的工作表，读取里面的数据
for(intsheetIndex=0;sheetIndexvalidateMap=newHashMap();
for(Stringmapstr:sheetInfo.getColumnsMapping().get(sheetName)){
String[]arr=mapstr.split(":");
columnsMap.put(arr[1],arr[0]);
if(arr.length==3){//若果验证表达式不为空，则添加到map中
validateMap.put(arr[1],arr[2]);
}
}
Sheetsheet=book.getSheet(sheetName);
if(null==sheet){
thrownewRuntimeException(String.format("获取表失败，请确认Sheet《%s》是否存在于excel中",sheetName));
}
//用于存储所工作表中的数据内容
ListsheetList=newArrayList();
//获取当前表格的行数
introws=sheet.getLastRowNum();
//获取当前表格的列数
intcolumns=sheet.getRow(sheetInfo.getRowTitle()).getLastCellNum();
if(rows<=sheetInfo.getRowTitle()){//如果当前表格没有需要的数据就继续下一次循环
continue;
}
//获得当前工作表标题内容
ListtitleList=newArrayList();
//循环每一行中的每一个单元格，读取单元格内的值
RowtitleRow=sheet.getRow(sheetInfo.getRowTitle());
for(intjj=0;jj表头内容为："+titleRow+"，请检查是否正确，如有异常，可删除表头格式，重新粘贴表头！");
}
}
//System.out.println(titleList);
//验证表头
String[]titles=sheetInfo.getColumnsMapping().get(sheetName);
for(Strings:titles){
String[]colArr=s.split(":");
//如果Excel表格中的表头缺少该字段
booleaninclude=false;
for(Stringt:titleList){
if(StringUtils.deleteWhitespace(t).equalsIgnoreCase(colArr[1])){
include=true;
break;
}
}
if(!include){
thrownewRuntimeException("【"+colArr[1]+"】'列不存在！当前Excel表头："+titleList);
}
}
//开始循环每一行，读取每一行的值,从标题下面一行开始读取
for(inti=sheetInfo.getRowTitle()+1;i<=rows;i++){
MaprowMap=newHashMap();
RowdataRow=sheet.getRow(i);
if(dataRow==null){
thrownewRuntimeException(String.format("excel第[%d]行为空，请检查!",i+1));
}
for(intj=0;j0&&!StringUtil.isEmpty(value)){
Stringrowspan="";
if(rowMap.get("rowspan")!=null){
rowspan=rowMap.get("rowspan")+",";
}
rowMap.put("rowspan",rowspan+columnMapping+"-"+value+"-"+(mergRow+1));
}
if(cell.getCellComment()!=null){
//System.out.println(columnMapping+"@comment:"+cell.getCellComment().getString());
rowMap.put(columnMapping+"@comment",cell.getCellComment().getString());
}
}

//StringcolumnMapping=columnsMap.get(columnTitle);
StringvalidateReg="";
StringvalidateRegMsg="";
if(null!=validateMap.get(columnTitle)){
//验证正则表达式
RegExpEnumeum=RegExpEnum.valueOf(validateMap.get(columnTitle));
validateReg=eum.getValue();
validateRegMsg=eum.getText();
}
if(!StringUtil.isEmpty(validateReg)){
if(value.matches(validateReg)){
rowMap.put(columnMapping,value);
}else{
errNum++;
if(errNum<=errLimit){
errMsg.add("第"+i+"行：【"+columnTitle+"】数据为:'"+value.trim()+"'不匹配！【"+validateRegMsg+"】
\n");
}
}
}else{
if(StringUtil.isEmpty(columnMapping)){
continue;
}else{
//introw=cell.getRowIndex();
///intcolumn=cell.getColumnIndex();
//if(isMergedRegion(sheet,row,column)){
//rowMap.put(columnMapping,getMergedRegionValue(sheet,row,column));
//}else{
rowMap.put(columnMapping,value);
//}
}
}
}
}
sheetList.add(rowMap);
}
excelInfo.put(sheet.getSheetName(),sheetList);
}
in.close();

if(errMsg.size()>0){
if(errNum>errLimit){
errMsg.add("您导入的数据模板格式错误过多(共"+errNum+"个)，请仔细检查模板数据是否正确！");
}
thrownewRuntimeException(errMsg.toString().replaceAll("\\[|\\]",""));
}
//if(true)thrownewRuntimeException("测试");
returnexcelInfo;
}


publicstaticList>getExcel(InputStreamin,SheetInfosheetInfo)throwsException{
MapcolumnsMap=newHashMap();
//列验证表达式map
MapvalidateMap=newHashMap();
ListerrMsg=newArrayList();
interrNum=0;//错误总数
interrLimit=10;//限制错误提示数
for(Stringmapstr:sheetInfo.getColumnsMapping().get("columns")){
String[]arr=mapstr.split(":");
columnsMap.put(arr[1],arr[0]);
if(arr.length==3){//若果验证表达式不为空，则添加到map中
validateMap.put(arr[1],arr[2]);
}
}
/**用于存储Excel根据指定规则读取的所有内容*/
ListexcelInfo=newArrayList();
Workbookbook=WorkbookFactory.create(in);
//checkTitle(book,sheetInfo);
//获得工作表数量
intsheetNum=book.getNumberOfSheets();
//循环所有的工作表，读取里面的数据
for(intsheetIndex=0;sheetIndextitleList=newArrayList();
//循环每一行中的每一个单元格，读取单元格内的值
for(intjj=0;jj\n");
}
}
}else{
rowMap.put(columnMapping,value);
}
}
}
excelInfo.add(rowMap);
}
//excelInfo.put(sheet.getSheetName(),sheetList);
}
in.close();
if(errMsg.size()>0){
//if(errNum>errLimit){
//errMsg.add("您导入的数据模板格式错误过多(共"+errNum+"个)，请仔细检查模板数据是否正确！");
//}
thrownewRuntimeException(errMsg.toString().replaceAll("\\[|\\]",
""));
}
returnexcelInfo;
}

/**
*
*用于excel操作，表格初始化信息
*
*@author:季乐
*@date:2013-12-2下午1:43:04
*@since:1.0
*/
publicclassSheetInfo{

/**标题所在的行，起始行是0，不是1*/
privateintrowTitle=1;

/**需要读取数据字段中文名对应的entity属性名*/
privateMapcolumnsMapping;

/**需要读取数据的sheet的名字*/
publicListsheetNames=newArrayList();

publicSheetInfo(ListsheetNames){
//假如没有定义sheetNames，则给予其默认值”Sheet1“
if(null==sheetNames||sheetNames.size()==0){
this.sheetNames.add("Sheet1");
}else{
this.sheetNames=sheetNames;
}
}

publicSheetInfo(){
//假如没有定义sheetNames，则给予其默认值”Sheet1“
if(null==sheetNames||sheetNames.size()==0){
sheetNames.add("Sheet1");
}
}

publicintgetRowTitle(){
returnrowTitle;
}

publicvoidsetRowTitle(introwTitle){
this.rowTitle=rowTitle;
}

publicMapgetColumnsMapping(){
returncolumnsMapping;
}

publicvoidsetColumnsMapping(MapcolumnsMapping){
this.columnsMapping=columnsMapping;
}

publicListgetSheetNames(){
returnsheetNames;
}

publicvoidsetSheetNames(ListsheetNames){
this.sheetNames=sheetNames;
}
}

/**
*
*内部枚举类
*
*@author:季乐
*@date:2013-12-2下午1:43:24
*@since:1.0
*/
publicenumRegExpEnum{
/**不为空*/
NOTEMPTY("不能为空","(?!+$).+"),
/**必须为数字*/
ISNUMBER("必须为数字","\\d*"),
/**不为空并且为数字*/
NOTEMPTY_ISNUMBER("不能为空且必须为数字","\\d+");

/**text*/
privateStringtext;
/**level*/
privateStringvalue;

publicStringgetText(){
returntext;
}

publicStringgetValue(){
returnvalue;
}

privateRegExpEnum(Stringtext,Stringvalue){
this.text=text;
this.value=value;
}
}

/**
*将html转为RichTextString
*
*@paramwb
*HSSFWorkbook
*@paramhtml
*html
*@returnRichTextString
*/
@SuppressWarnings("unused")
privatestaticRichTextStringprocessHtml(HSSFWorkbookwb,Stringhtml){
RichTextStringrt=null;
HTMLEditorKitkit=newHTMLEditorKit();
HTMLDocumentdoc=(HTMLDocument)kit.createDefaultDocument();
try{
kit.insertHTML(doc,doc.getLength(),html,0,0,null);
StringBuffersb=newStringBuffer();
for(intlines=0,lastPos=-1;lastPos0){
//sb.append('\n');
//}
Elementline=doc.getParagraphElement(lastPos+1);
lastPos=line.getEndOffset();
for(intelIdx=0;elIdxae=as.getAttributeNames();

while(ae.hasMoreElements()){
finalObjectattrib=ae.nextElement();

if(CSS.Attribute.COLOR.equals(attrib)){
Fieldf=as.getAttribute(attrib).getClass().getDeclaredField("c");
f.setAccessible(true);
Colorc=(Color)f.get(as.getAttribute(attrib));
if(fontinstanceofXSSFFont){
((XSSFFont)font).setColor(newXSSFColor(c));
}elseif(fontinstanceofHSSFFont&&wbinstanceofHSSFWorkbook){
HSSFPalettepal=((HSSFWorkbook)wb).getCustomPalette();
HSSFColorcol=pal.findSimilarColor(c.getRed(),c.getGreen(),c.getBlue());
((HSSFFont)font).setColor(col.getIndex());
}
}elseif(CSS.Attribute.FONT_WEIGHT.equals(attrib)){
if("bold".equals(as.getAttribute(attrib).toString())){
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
}
}
}

returnfont;
}

publicstaticintgetMergedRegionRow(Sheetsheet,Cellcell){
//得到一个sheet中有多少个合并单元格
intsheetmergerCount=sheet.getNumMergedRegions();
for(inti=0;i=firstC){
if(cell.getRowIndex()==firstR){
returnlastR-firstR;
}
}
}
return0;
}

/**
*获取合并单元格的值
*
*@paramsheet
*@paramrow
*@paramcolumn
*@return
*/
publicstaticStringgetMergedRegionValue(Sheetsheet,introw,intcolumn){
intsheetMergeCount=sheet.getNumMergedRegions();

for(inti=0;i=firstRow&&row<=lastRow){

if(column>=firstColumn&&column<=lastColumn){
RowfRow=sheet.getRow(firstRow);
CellfCell=fRow.getCell(firstColumn);
returngetCellValue(fCell);
}
}
}

returnnull;
}

/**
*判断指定的单元格是否是合并单元格
*
*@paramsheet
*@paramrow
*行下标
*@paramcolumn
*列下标
*@return
*/
publicstaticbooleanisMergedRegion(Sheetsheet,introw,intcolumn){
intsheetMergeCount=sheet.getNumMergedRegions();
for(inti=0;i=firstRow&&row<=lastRow){
if(column>=firstColumn&&column<=lastColumn){
returntrue;
}
}
}
returnfalse;
}

/**
*判断sheet页中是否含有合并单元格
*
*@paramsheet
*@return
*/
@SuppressWarnings("unused")
privatebooleanhasMerged(Sheetsheet){
returnsheet.getNumMergedRegions()>0?true:false;
}

/**
*合并单元格
*
*@paramsheet
*@paramfirstRow
*开始行
*@paramlastRow
*结束行
*@paramfirstCol
*开始列
*@paramlastCol
*结束列
*/
@SuppressWarnings("unused")
privatevoidmergeRegion(Sheetsheet,intfirstRow,intlastRow,intfirstCol,intlastCol){
sheet.addMergedRegion(newCellRangeAddress(firstRow,lastRow,firstCol,lastCol));
}

/**
*获取单元格的值
*
*@paramcell
*@return
*/
publicstaticStringgetCellValue(Cellcell){

if(cell==null)
return"";

if(cell.getCellType()==Cell.CELL_TYPE_STRING){

returncell.getStringCellValue();

}elseif(cell.getCellType()==Cell.CELL_TYPE_BOOLEAN){

returnString.valueOf(cell.getBooleanCellValue());

}elseif(cell.getCellType()==Cell.CELL_TYPE_FORMULA){

returncell.getCellFormula();

}elseif(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){

if(HSSFDateUtil.isCellDateFormatted(cell)){//处理日期格式、时间格式
SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-dd");
Datedate=cell.getDateCellValue();
returnString.valueOf(sdf.format(date));
}elseif(cell.getCellStyle().getDataFormat()==31){
//处理自定义日期格式：yy年mm月dd日(通过判断单元格的格式id解决，id的值是31)
SimpleDateFormatsdf=newSimpleDateFormat("yyyy-MM-dd");
doublevalue=cell.getNumericCellValue();
Datedate=org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
returnString.valueOf(sdf.format(date));
}else{
doublevalue=cell.getNumericCellValue();
CellStylestyle=cell.getCellStyle();
DecimalFormatformat=newDecimalFormat();
returnString.valueOf(format.format(value));
}
}
return"";
}

publicstaticStringfilterStr(Stringstr){
str=str.replace(String.valueOf((char)160),"").replace(String.valueOf((char)65279),"");
str=str.trim();
returnstr;
}

publicstaticvoidmain(String[]args){
System.out.println(convertRGBToHex(HSSFColor.YELLOW.triplet));
System.out.println(newXSSFColor(Color.YELLOW).getARGBHex().substring(2));
System.err.println(HtmlUtils.htmlUnescape("汇总（电视&盒子&路由器）"));
}

staticStringconvertRGBToHex(short[]rgb){
intr=rgb[0],g=rgb[1],b=rgb[2];
StringrFString,rSString,gFString,gSString,bFString,bSString,result;
intred,green,blue;
intrred,rgreen,rblue;
red=r/16;
rred=r%16;
if(red==10)rFString="A";
elseif(red==11)rFString="B";
elseif(red==12)rFString="C";
elseif(red==13)rFString="D";
elseif(red==14)rFString="E";
elseif(red==15)rFString="F";
elserFString=String.valueOf(red);

if(rred==10)rSString="A";
elseif(rred==11)rSString="B";
elseif(rred==12)rSString="C";
elseif(rred==13)rSString="D";
elseif(rred==14)rSString="E";
elseif(rred==15)rSString="F";
elserSString=String.valueOf(rred);

rFString=rFString+rSString;

green=g/16;
rgreen=g%16;

if(green==10)gFString="A";
elseif(green==11)gFString="B";
elseif(green==12)gFString="C";
elseif(green==13)gFString="D";
elseif(green==14)gFString="E";
elseif(green==15)gFString="F";
elsegFString=String.valueOf(green);

if(rgreen==10)gSString="A";
elseif(rgreen==11)gSString="B";
elseif(rgreen==12)gSString="C";
elseif(rgreen==13)gSString="D";
elseif(rgreen==14)gSString="E";
elseif(rgreen==15)gSString="F";
elsegSString=String.valueOf(rgreen);

gFString=gFString+gSString;

blue=b/16;
rblue=b%16;

if(blue==10)bFString="A";
elseif(blue==11)bFString="B";
elseif(blue==12)bFString="C";
elseif(blue==13)bFString="D";
elseif(blue==14)bFString="E";
elseif(blue==15)bFString="F";
elsebFString=String.valueOf(blue);

if(rblue==10)bSString="A";
elseif(rblue==11)bSString="B";
elseif(rblue==12)bSString="C";
elseif(rblue==13)bSString="D";
elseif(rblue==14)bSString="E";
elseif(rblue==15)bSString="F";
elsebSString=String.valueOf(rblue);
bFString=bFString+bSString;
result=rFString+gFString+bFString;
returnresult;

}
}

再看下from.jsp页面






下载模板



$(function(){
$('#mainform').form({
onSubmit:function(){
varisValid=$(this).form('validate');
returnisValid;//返回false终止表单提交
},
success:function(data){
successTip(data,dg,d);
}
});
});

主界面jsp

导入

//导入
functionimportAction(){
d=$("#dlg").dialog({
title:'案由导入',
width:500,
height:500,
href:'${ctx}/bom/ciscaseaction/importAction/',
maximizable:true,
modal:true,
buttons:[{
text:'导入',
handler:function(){
$('#mainform').submit();
}
},{
text:'取消',
handler:function(){
d.panel('close');
}
}]
});
}

页面点击的效果是，点击导入会跳入from.jsp页面

再看controller层

/**
*导入页面
*/
@RequestMapping(value="importAction",method=RequestMethod.GET)
publicStringimportForm(Modelmodel){
model.addAttribute("action","import");
return"system/cisCaseActionImoportForm";
}

/**
*导入
*/
@RequestMapping(value="import",method=RequestMethod.POST)
@ResponseBody
publicStringimportForm(@RequestParam("file")MultipartFilemultipartFile,Modelmodel)throwsException{
cisCaseActionService.upload(multipartFile);
return"success";
}

service层

/**
*导入案由
*/
@SuppressWarnings({"rawtypes","unchecked"})
publicvoidupload(MultipartFilemultipartFile)throwsException{
InputStreaminputStream=multipartFile.getInputStream();
ExcelToolsexcelTools=newExcelTools();
ExcelTools.SheetInfosheetInfo=excelTools.newSheetInfo();
sheetInfo.setRowTitle(0);
Mapcolumns=newHashMap();
columns.put("columns",newString[]{"name:案由名称","violateLaw:违反法律","punishBasis:处罚依据"});
sheetInfo.setColumnsMapping(columns);
List>mapList=ExcelTools.getExcel(inputStream,sheetInfo);
for(inti=0;imap=mapList.get(i);

Stringname=map.get("name");
if(StringUtils.isEmpty(name)){
thrownewBusinessException("第"+(i+2)+"案由名称不能为空");
}
StringviolateLaw=map.get("violateLaw");
StringpunishBasis=map.get("punishBasis");
CisCaseActioncisCaseAction=newCisCaseAction();
cisCaseAction.setName(name);
cisCaseAction.setViolateLaw(violateLaw);
cisCaseAction.setPunishBasis(punishBasis);
this.insert(cisCaseAction);//调用同一层的插入方法
}
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。

Java中excel表数据的批量导入方法

热门推荐

随机推荐