怎么在Java中批量导入excel表数据

这期内容当中小编将会给大家带来有关怎么在Java中批量导入excel表数据，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

Java有哪些集合类

Java中的集合主要分为四类：1、List列表：有序的，可重复的；2、Queue队列：有序，可重复的；3、Set集合：不可重复；4、Map映射：无序，键唯一，值不唯一。

首先看下工具类：
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
*<p>
*使用方法：<br>
*<code>List<Map<String,Object>>dataList=newArrayList<Map<String,Object>>();<br>
*is=ExcelTools.exportXLS(dataList,newString[]{"createTime:日期","name:名称","sex:性别","remark:备注"});
*</code>
*
*@paramcollect
*待导出的数据集合
*@paramheader
*要导出的列
*@returnInputStream返回文件流
*/
publicstaticInputStreamexportXLS(Collection<Map<String,Object>>collect,String[]header){
ByteArrayOutputStreamout=newByteArrayOutputStream();
HSSFWorkbookbook=newHSSFWorkbook();
try{
//添加一个sheet
HSSFSheetsheet=book.createSheet("Sheet1");
//定义要导出的列名集合
Set<String>columns=newHashSet<String>();

//设置单元格背景色
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(Map<String,Object>map: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文件数据<br>
*用法：<br>
*SheetInfosheetInfo=newExcelTools().newSheetInfo();<br>
*sheetInfo.setRowTitle(0);List<String>sheets=newArrayList<String>();<br>
*StringsheetName="Sheet1";sheets.add(sheetName);<br>
*sheetInfo.setSheetNames(sheets);<br>
*sheetInfo.setColumnsMapping(newString[]{"prodName:商品名称",
*"prodSpec:规格","collectPrice:价格:"+{@linkRegExpEnum}
*RegExpEnum.NOTEMPTY_ISNUMBER,"priceUnit:单位","collectMarket:报价市场",
*"prodLevel:等级"});<br>
*Map<String,List>data=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{
Map<String,String>columnsMap=newHashMap<String,String>();
//列验证表达式map
List<String>errMsg=newArrayList<String>();
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;sheetIndex<sheetNum;sheetIndex++){
//获得当前工作表对象
StringsheetName=HtmlUtils.htmlUnescape(sheetInfo.getSheetNames().get(sheetIndex));
Map<String,String>validateMap=newHashMap<String,String>();
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;
}
//获得当前工作表标题内容
List<String>titleList=newArrayList<String>();
//循环每一行中的每一个单元格，读取单元格内的值
RowtitleRow=sheet.getRow(sheetInfo.getRowTitle());
for(intjj=0;jj<columns;jj++){
CellcellTitle=titleRow.getCell(jj);
if(cellTitle!=null){
introw=cellTitle.getRowIndex();
intcolumn=cellTitle.getColumnIndex();
if(isMergedRegion(sheet,row,column)){
titleList.add(getMergedRegionValue(sheet,row,column));
}else{
titleList.add(getCellValue(cellTitle));
}
}else{
thrownewRuntimeException("表头读取错误,当前设置为第"+(sheetInfo.getRowTitle()+1)+"行<br/>表头内容为："+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;j<columns;j++){//循环每一行中的每一个单元格，读取单元格内的值
StringcolumnTitle=titleList.get(j);
if("".equals(columnTitle)){
continue;
}else{
Cellcell=dataRow.getCell(j);
Stringvalue="";

StringcolumnMapping="";
//单元列对应的entity属性名
for(Stringtitle:columnsMap.keySet()){
if(StringUtils.deleteWhitespace(columnTitle).equalsIgnoreCase(title)){
columnMapping=columnsMap.get(title);
break;
}
}

if(null!=cell){
cell.setCellType(Cell.CELL_TYPE_STRING);
CellStylecellStyle=cell.getCellStyle();
//单元格背景颜色
if(excelType.equals("xls")){
HSSFColorcolor=(HSSFColor)cellStyle.getFillForegroundColorColor();
if(j==0&&color!=null){
rowMap.put("rowColor",convertRGBToHex(color.getTriplet()));
}

}else{
XSSFColorcolor=(XSSFColor)cellStyle.getFillForegroundColorColor();
if(j==0&&color!=null){
rowMap.put("rowColor",color.getARGBHex().substring(2));
}
}


value=filterStr(cell+"");
intmergRow=getMergedRegionRow(sheet,cell);
if(mergRow>0&&!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+"】</br>\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<HashMap<String,String>>getExcel(InputStreamin,SheetInfosheetInfo)throwsException{
Map<String,String>columnsMap=newHashMap<String,String>();
//列验证表达式map
Map<String,String>validateMap=newHashMap<String,String>();
List<String>errMsg=newArrayList<String>();
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;sheetIndex<sheetNum;sheetIndex++){
//获得当前工作表对象
Sheetsheet=book.getSheetAt(sheetIndex);
//用于存储所工作表中的数据内容
//ListsheetList=newArrayList();
//获取当前表格的行数
introws=sheet.getLastRowNum();
//获取当前表格的列数
RowtitleRow=sheet.getRow(sheetInfo.getRowTitle());
if(titleRow==null){
thrownewBusinessException("文件格式不正确，请重新选择或者下载模板");
}
intcolumns=titleRow.getLastCellNum();
if(columns!=sheetInfo.getColumnsMapping().get("columns").length){
thrownewBusinessException("文件格式不正确，请重新选择或者下载模板");
}
if(rows<=sheetInfo.getRowTitle()){//如果当前表格没有需要的数据就继续下一次循环
thrownewBusinessException("文件格式不正确，请重新选择或者下载模板");
}

//获得当前工作表标题内容
List<String>titleList=newArrayList<String>();
//循环每一行中的每一个单元格，读取单元格内的值
for(intjj=0;jj<columns;jj++){
titleList.add(titleRow.getCell(jj).getStringCellValue());
}
//验证表头
String[]titles=sheetInfo.getColumnsMapping().get("columns");
for(Strings:titles){
//如果Excel表格中的表头缺少该字段
if(!titleList.contains(s.split(":")[1])){
//errMsg.add("该Excel表格的'"+sheet.getSheetName()+"'表的'"+s
//+"'列不存在！");
thrownewBusinessException("文件格式不正确，请重新选择或者下载模板");
}
}

//开始循环每一行，读取每一行的值,从标题下面一行开始读取
for(inti=sheetInfo.getRowTitle()+1;i<=rows;i++){
MaprowMap=newHashMap();
RowdataRow=sheet.getRow(i);
for(intj=0;j<columns;j++){//循环每一行中的每一个单元格，读取单元格内的值
StringcolumnTitle=titleList.get(j);
if("".equals(columnTitle)){
continue;
}else{
Cellcell=dataRow.getCell(j);
Stringvalue=getCellValue(cell);
//单元列对应的entity属性名
StringcolumnMapping=columnsMap.get(columnTitle);
StringvalidateReg="";
StringvalidateRegMsg="";
if(null!=validateMap.get(columnTitle)){
//验证正则表达式
RegExpEnumeum=RegExpEnum.valueOf(validateMap
.get(columnTitle));
validateReg=eum.getValue();
validateRegMsg=eum.getText();
}
if(!StringUtils.isEmpty(validateReg)){
if(value.matches(validateReg)){
rowMap.put(columnMapping,value);
}else{
errNum++;
if(errNum<=errLimit){
errMsg.add("第"+i+"行：【"+columnTitle
+"】数据为:'"+value.trim()
+"'不匹配！【"+validateRegMsg
+"】</br>\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属性名*/
privateMap<String,String[]>columnsMapping;

/**需要读取数据的sheet的名字*/
publicList<String>sheetNames=newArrayList<String>();

publicSheetInfo(List<String>sheetNames){
//假如没有定义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;
}

publicMap<String,String[]>getColumnsMapping(){
returncolumnsMapping;
}

publicvoidsetColumnsMapping(Map<String,String[]>columnsMapping){
this.columnsMapping=columnsMapping;
}

publicList<String>getSheetNames(){
returnsheetNames;
}

publicvoidsetSheetNames(List<String>sheetNames){
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;lastPos<doc.getLength();lines++){
//if(lines>0){
//sb.append('\n');
//}
Elementline=doc.getParagraphElement(lastPos+1);
lastPos=line.getEndOffset();
for(intelIdx=0;elIdx<line.getElementCount();elIdx++){
finalElementfrag=line.getElement(elIdx);
Stringsubtext=doc.getText(frag.getStartOffset(),frag.getEndOffset()-frag.getStartOffset());
if(!subtext.equals("\n")){
sb.append(subtext);
}
}
}
CreationHelperch=wb.getCreationHelper();
rt=ch.createRichTextString(sb.toString());
for(intlines=0,lastPos=-1;lastPos<doc.getLength();lines++){
Elementline=doc.getParagraphElement(lastPos+1);
lastPos=line.getEndOffset();
for(intelIdx=0;elIdx<line.getElementCount();elIdx++){
finalElementfrag=line.getElement(elIdx);
Fontfont=getFontFromFragment(wb,frag);
rt.applyFont(frag.getStartOffset()+lines,frag.getEndOffset()+lines,font);

}
}
}catch(Exceptione){
log.warn(e.getMessage());
//e.printStackTrace();
}
returnrt;
}

/**
*获取字体
*
*@paramwb
*Workbook
*@paramfrag
*frag
*@returnFont
*@throwsException
*Exception
*/
privatestaticFontgetFontFromFragment(Workbookwb,Elementfrag)throwsException{
Fontfont=wb.createFont();
finalAttributeSetas=frag.getAttributes();
finalEnumeration<?>ae=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<sheetmergerCount;i++){
//得出具体的合并单元格
CellRangeAddressca=sheet.getMergedRegion(i);
//得到合并单元格的起始行,结束行,起始列,结束列
intfirstC=ca.getFirstColumn();
intlastC=ca.getLastColumn();
intfirstR=ca.getFirstRow();
intlastR=ca.getLastRow();
//判断该单元格是否在合并单元格范围之内,如果是,则返回true
if(cell.getColumnIndex()<=lastC&&cell.getColumnIndex()>=firstC){
if(cell.getRowIndex()==firstR){
returnlastR-firstR;
}
}
}
return0;
}

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

for(inti=0;i<sheetMergeCount;i++){
CellRangeAddressca=sheet.getMergedRegion(i);
intfirstColumn=ca.getFirstColumn();
intlastColumn=ca.getLastColumn();
intfirstRow=ca.getFirstRow();
intlastRow=ca.getLastRow();

if(row>=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<sheetMergeCount;i++){
CellRangeAddressrange=sheet.getMergedRegion(i);
intfirstColumn=range.getFirstColumn();
intlastColumn=range.getLastColumn();
intfirstRow=range.getFirstRow();
intlastRow=range.getLastRow();
if(row>=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页面

<body>
<div>
<formid="mainform"action="${ctx}/bom/ciscaseaction/${action}"method="post"enctype="multipart/form-data">
<inputtype="file"name="file"/>

<ahref="${ctx}/static/案由导入模板.xls"rel="externalnofollow">下载模板</a>
</form>
</div>
<scripttype="text/javascript">
$(function(){
$('#mainform').form({
onSubmit:function(){
varisValid=$(this).form('validate');
returnisValid;//返回false终止表单提交
},
success:function(data){
successTip(data,dg,d);
}
});
});

</script>
</body>

主界面jsp

复制代码 代码如下:

<a href="javascript(0)" rel="external nofollow" class="easyui-linkbutton" plain="true" iconCls="icon-standard-application-go" onclick="importAction()">导入</a>

//导入
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<HashMap<String,String>>mapList=ExcelTools.getExcel(inputStream,sheetInfo);
for(inti=0;i<mapList.size();i++){
HashMap<String,String>map=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表数据了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注恰卡编程网行业资讯频道。