利用zui上传excel文件,并通过java后台读取excel中的内容
阮鸿煊
上传excel文件,并且利用java代码读取excel文件内容
1.html代码:
<div id="app" style="padding: 10px;margin-top: 50px;">
<form class="form-horizontal" id="form1">
<div id='uploader' class="uploader">
<div class="uploader-message text-center">
<div class="content"></div>
<button type="button" class="close">×</button>
</div>
<div class="uploader-files file-list file-list-lg" data-drag-placeholder="请拖拽文件到此处"></div>
<div class="uploader-actions">
<div class="uploader-status pull-right text-muted"></div>
<button type="button" class="btn btn-link uploader-btn-browse"><i class="icon icon-plus"></i> 选择文件</button>
<button type="button" class="btn btn-link uploader-btn-start"><i class="icon icon-cloud-upload"></i> 开始上传</button>
</div>
</div>
<input type="hidden" id="task_id" name="task_id" :value="task_id">
</form>
<div class="alert alert-success with-icon tsxx" style="display: none;">
<i class="icon-ok-sign"></i>
<div class="content">
<h4>导入成功</h4>
<hr>
<strong>{{msg}}</strong>
</div>
</div>
</div>
可选择拖拽或者点击上传的方式上传excel文件,本次上传可支持xls文件和xlsx文件格式.
var url = xxx;//这里是调用后端执行读取excel文件的url地址(controller)
var options = {
headers : {
'Uuid': generateUUID(),'Authorization' : sessionStorage.getItem("token")
},
url : url,
lang : 'zh_cn',
filters: {
mime_types: [{title: '表格', extensions: 'xls,xlsx'}]
},
// autoResetFails:true,
chunk_size : 0,
limitFilesCount : 1,
multipart_params:{
//此处是后端页面需要的参数的位置
//task_id : app.task_id,
},
onUploadFile : function() {
if (this.plupload.state != 1) {
$("#main_loading").show();
}
},
onFileUploaded : function(file, responseObject) {
if (responseObject.status == '200') {
var json = responseObject.response;
var obj = JSON.parse(json);
console.log(JSON.stringify(responseObject));
//导入结果的显示问题
if (obj.code == '-1') {
new $.zui.Messager(obj.msg, {
icon : 'remove-sign',
placement : 'center', // 定义显示位置
type : 'info',
time : 20000
}).show();
} else if (obj.code == '0') {//导入成功
//构造提示信息区域， 用于展示， 如果本次导入并没有完全匹配成功， 需要提供错误信息下载， 用于更改， 下次再次导入
$("#uploader").hide();
app.msg = obj.data.msg;
$(".tsxx").show();
}
}
$("#main_loading").hide();
}
};
其中可以根据自定义过滤条件展示出上传多少条,成功多少条,失败多少条数据.
2.后端解析excel文件
根据前端地址来后controller
@RequestMapping("/import/excel")
public JsonData importForExcel(@RequestHeader(name="Authorization") String token
, @RequestParam("file") MultipartFile file,HttpServletResponse response
, HttpServletRequest request ) {
response.addHeader("Access-Control-Allow-Origin", "*");
User user = userService.getLoginUser(request);//获取到当前用户信息
return xxxService.importForExcel(user,file);//导入数据
}
根据个人需求添加参数(从前端带过来的)
到service层的方法:
public JsonData importForExcel(User user, MultipartFile file) {
Map map_rtn = new HashMap();
String msg = "";
List<String> name = new ArrayList<String>();
List<Map> list_data = new ArrayList<Map>();
//获取主表
Map<String,String> xxx= xxxMapper.selectByTaskIdAndCjdw(xxx);
int rtn_code = 0;
try {
//一、解析excel数据
Map rtn_map = getDataFromExcel(user, file,xxx);//需要定义解析方法
//System.out.println("rtn_map:"+rtn_map);
String file_code = ConverterUtils.nullToStr(rtn_map.get("file_code"));
String list_size = ConverterUtils.nullToStr(rtn_map.get("list_size"));
String err_str = ConverterUtils.nullToStr(rtn_map.get("err_str"));
if("1".equals(file_code)) {
rtn_code = -1;
msg = err_str;
} else {
List<Map> list_hang = (List<Map>) rtn_map.get("list_hang");
boolean flag = saveDataFromExcel(user,xxx,ysb);//定义把读取数据保存到数据库的方法
if(flag) {
msg = "导入成功：总共" + list_size + "行数据，导入成功" +list_hang.size()+ "条";
}
}
} catch (Exception e) {
e.printStackTrace();
return JsonData.buildError("请求失败! + Exception: " + e);
}
if(rtn_code == -1) {
return JsonData.buildError(msg);
} else {
return JsonData.buildSuccess(msg,rtn_code);
}
}
定义解析excel文件的方法,此方法可解析xls和xlsx文件格式,方法比较复杂,但是比较全面.需要导入poi包,例如:import org.apache.poi.ss.usermodel.Workbook;
下面是解析excel的方法(重点)
public Map getDataFromExcel(User user, MultipartFile file, xxx) throws BiffException, IOException {
long t1 = System.currentTimeMillis();
String err_str = "";
int notAddNum = 0;
Map rtn_map = new HashMap();
Workbook workbook = getWorkBook(file);
//获取大年层级单位下所有的预算科目
Map map_param = new HashMap();
map_param.put("gkdw_bm", "");
List<Map<String,String>> list_yskm = selectListOrderByPbm(user,map_param);
//获取所有该任务涉及的部门
List<Map> list_dept = selectForBbSbDwByTaskId(task_id);
//保存所有的列数据
List<Map> list_lm = new ArrayList<Map>();
//保存所有的行数库
List<Map> list_hang = new ArrayList<Map>();
if(workbook != null) {
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
//获得当前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
continue;
}
//获得当前sheet的开始行
int firstRowNum = sheet.getFirstRowNum();
//获得当前sheet的结束行
int lastRowNum = sheet.getLastRowNum();
System.err.println("lastRowNum::"+lastRowNum);
//循环除了所有行,如果要循环除第一行以外的就firstRowNum+1
for (int rowNum = firstRowNum ; rowNum <=lastRowNum ; rowNum++) {
//获得当前行
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
//获得当前行的开始列
int firstCellNum = row.getFirstCellNum();
//获得当前行的列数
int lastCellNum = row.getLastCellNum();//每行有多少列
if (lastCellNum > 0) {
//第二行,第一行为标题,不需要判断
if(rowNum == 1) {
//循环当前行
for (int cellNum = 4; cellNum < lastCellNum; cellNum++) {//此处只需要每行的第4列及后面,前面的则不需要获取
Cell cell = row.getCell(cellNum);//获取内容
String xxx= getCellValue(cell).trim().replace("*", "");
//查看每一列是否符合数据库的匹配项
boolean flag = false;
for(int i_dept = 0 ; i_dept < list_dept.size() ; i_dept ++ ) {
Map map_dept = (HashMap) list_dept.get(i_dept);
if(ConverterUtils.nullToStr(map_dept.get("DEPT_MC")).contains(dept_mc)) {
map_dept.put("COL", cellNum);
list_lm.add(map_dept);
flag = true;
}
}
if(!flag) {
err_str += ",第" + (cellNum + 1) + "列（" + dept_mc + "）";
}
}
if(!"".equals(err_str)) {
rtn_map.put("file_code", "1");
rtn_map.put("err_str", "提示消息");
return rtn_map;
}
long t2 = System.currentTimeMillis();
System.out.println("TIME_1:"+(t2 - t1));
}else if(rowNum > 2) {//第2行结束第3行为合计行，不作处理
//判断当前列是否有效
int pxh = 0;
String yskm_bm = "",isleaf = "",lx = "",yskm_id = "";
Cell cell = row.getCell(0);
String yskm_name = getCellValue(cell).trim();
Map map_row = new HashMap();
for(Map m : list_yskm) {
String name_ys = ( m.get("QC") == null || "".equals(ConverterUtils.nullToStr(m.get("QC"))) ? ConverterUtils.nullToStr(m.get("MC")) : ConverterUtils.nullToStr(m.get("QC")) + "/" + ConverterUtils.nullToStr(m.get("MC")) );
if(yskm_name.equals(name_ys)) {
yskm_id = ConverterUtils.nullToStr(m.get("ID"));
}
}
if("".equals(isleaf) || "".equals(yskm_bm) || "".equals(yskm_id)) {
++notAddNum;
err_str += "，第"+(rowNum + 1)+"行（"+yskm_name+"）不存在";
} else {
if("0".equals(isleaf)) {
++notAddNum;
} else {
for(Map lm : list_lm) {
map_row = new HashMap();
String index_col = ConverterUtils.nullToStr(lm.get("COL"));
cell = row.getCell(Integer.parseInt(index_col));
double l_value2 = cell.getNumericCellValue();
//根据EXCEL内容化获取数据值
String reg_num0 = "^(?!0+\\.00)(?=.{1,9}(\\.|$))(?!0(?!\\.))\\d{1,3}(,\\d{3})*(\\.\\d+)?$";
String reg_num1 = "^0.{0,1}[0]*$";
String l_value = ConverterUtils.nullToStr(l_value2);
if(l_value.matches(reg_num0)) {
l_value = l_value.replaceAll(",", "").replace(",", "").replace(",", "");
}
if(!l_value.matches(reg_num1) && !"".equals(l_value)) {
map_row.put("PXH",""+ (++pxh));
map_row.put("DEPT_ID", ConverterUtils.nullToStr(lm.get("DEPT_BM")).trim());
map_row.put("DEPT_MC", ConverterUtils.nullToStr(lm.get("DEPT_MC")).trim());
if(l_value.trim() == null) {
map_row.put("JE", 0);
}else {
map_row.put("JE", l_value.trim());
}
if(yskm_name.contains("培训费")) {
map_row.put("ZFY", l_value.trim());
}
list_hang.add(map_row);
}
}
}
}
}
}
}
}
}
rtn_map.put("file_code", "0");
Sheet sheet = workbook.getSheetAt(0);
rtn_map.put("list_size", sheet.getLastRowNum() - ( notAddNum + 3 ));//数据总行数
rtn_map.put("list_hang", list_hang);//数据
rtn_map.put("err_str", "".equals(err_str) ? "" : err_str.substring(1) );//错误数据信息
//System.out.println("rtn_map:"+rtn_map.toString());
long t2 = System.currentTimeMillis();
System.out.println((t2 - t1));
return rtn_map;
}
上面是解析excel的主要方法,中间定义了需要过滤条件,下面是各种判断方法
/**
* xls,xlsx文件上传判断
* @param file
* @return
* @author ls
*/
public static Workbook getWorkBook(MultipartFile file) {
System.err.println("判断");
//获得文件名
String fileName = file.getOriginalFilename();
//创建Workbook工作薄对象，表示整个excel
Workbook workbook = null;
try {
//获取excel文件的io流
InputStream is = file.getInputStream();
//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
if (fileName.endsWith("xls")) {
//2003
workbook = new HSSFWorkbook(is);
} else if (fileName.endsWith("xlsx")) {
//2007 及2007以上
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
e.getMessage();
System.err.println("错误::"+e.getMessage());
}
System.err.println("结束return:"+workbook);
return workbook;
}
/**
* 判断单元格的数据类型
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return cellValue;
}
//判断数据的类型
//判断数据的类型
switch (cell.getCellTypeEnum()) {
case NUMERIC: //数字
cellValue = stringDateProcess(cell);
break;
case STRING: //字符串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case BLANK: //空值
cellValue = "";
break;
case ERROR: //故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
return cellValue;
}
public static String stringDateProcess(Cell cell) {
String result = new String();
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
return result;
}
如果单元格中有计算函数则需要用到getNumericCellValue()方法,此方法为获取单元格计算后的数值,例如单元格A3=A1+A2,A1为1,A2为2,获取到A3的数据为’1+2’,把该数据插入到数据库不符合常理,如果格式不对也会报错,使用改方法会自动计算1+2.
double l_value2 = cell.getNumericCellValue();
现在以上代码基本上可以解决所有数据问题,根据业务逻辑进行判断修改即可.
3.jxl包下导入xls格式excel文件
此方法使用起来比较简单,但是jxl包下工具类不支持上传xlsx文件.html页面还是以前的页面,controller不变,只需要改变解析excel主方法,判断方法也不需要.
service代码:
@Transactional
private List<Map> getNsrxxFromExcel(User user, MultipartFile file) throws Exception {//根据需要添加参数
//file.getInputStream();//获取输入流
//需要做基础检测
List<Map> datas = new ArrayList<Map>();
WorkbookSettings setting = new WorkbookSettings();
setting.setEncoding("GBK");
Workbook rwb = Workbook.getWorkbook(file.getInputStream(), setting);
Sheet[] sheet = rwb.getSheets();
JSONObject json = null;
Map yjrw = null;
Map titles = new HashMap();//所有的标题
Sheet rs = rwb.getSheet(0);
if(!"导入模板".equals(rs.getName())) {
for(Sheet s : sheet) {
if("导入模板".equals(s.getName())) {
rs = s;
break;
}
}
}
if(!"导入模板".equals(rs.getName())) {
throw new Exception("xxx提示消息");//此处逻辑我们定义了模板,如果不是该模板则报错!
}
for (int j = 0; j < rs.getRows(); j++) {
Cell[] cells = rs.getRow(j);//列
if(j == 0) {
continue;
}
if(j == 1) {//第一行需要记录自定义字段
if(cells.length < 13 || cells[0] == null || !"xxx".equals(cells[0].getContents().trim())) {//列数少于13列， 不是我们要的excel数据， 乱导入的数据.应该立马提示导入的excel数据非法.
throw new Exception("xxx提示消息");//自定义需求
}
//开始获取需要的自定义内容了....
int maxIndex = 0;//11
if("错误信息".equals(cells[cells.length - 1].getContents())) {//我们提供的错误数据列
maxIndex = cells.length - 1;
} else {
maxIndex = cells.length;
}
for(int i = 11; i < maxIndex; i ++) {
titles.put(i, cells[i].getContents());
}
continue;
}
yjrw = new HashMap<>();
//根据需求放入
String msg = "";
//yjrw.put("drzt", "1");
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
for(int k = 0; k < cells.length; k++) {//读取所有数据
System.err.println(cells);
String txsxId = PrimaryKeyDependent.nextKey();//提醒事项id
yjrw.put("txsxid", txsxId);
String val = cells[k].getContents().trim();
json = new JSONObject();
switch (k) {
case 0: //djxhStr
if(val.length() > MAX_LENGTH_DJXH) {//需求逻辑判断
msg+="XXX长度不能不能超过最大长度"+MAX_LENGTH_XXX+"位###";
String djxh = val.toString().substring(0, 20);
yjrw.put("djxh",djxh);
yjrw.put("drzt", "0");
}else {
yjrw.put("djxh", ConverterUtils.nullToStr(val));
}
break;
if("".equals(ConverterUtils.toString(yjrw.get("djxh"))) && "".equals(ConverterUtils.toString(yjrw.get("nsrsbh")))
&& "".equals(ConverterUtils.toString(yjrw.get("nsrmc")))
&& "".equals(ConverterUtils.toString(yjrw.get("sklx")))
&& "".equals(ConverterUtils.toString(yjrw.get("xxms")))){
continue;
}
// if("".equals(yjrw.get("djxh")) && "".equals(yjrw.get("nsrsbh")) && "".equals(yjrw.get("nsrmc"))) {
// msg+="【XXX】至少填写一项###";
// }
if("".equals(yjrw.get("skssqq"))) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
String ssqq = sdf.format(date);
yjrw.put("skssqq", ssqq);
}
datas.add(yjrw);
System.err.println(datas);
}
return datas;
}
该方法获取excel内容相对简单容易理解,唯一缺点是不支持xlsx文件格式,两种方案,根据需求来选择.
类似资料：