Excel导入保存附件和解析数据

// 下载Excel模板
downLoadExcel: function () {
window.open(GLOBAL_CONFIG.webSiteRoot + "/main/common/files/xxx.xls");
},

<el-upload ref="upload"
:action="importFileUrl"
:before-upload="beforeUpload"
:multiple="false"
:on-success="uploadSuccess"
:on-error="uploadFail"
:show-file-list="false"
style="display: inline-block;">
<el-button type="primary" icon="el-icon-plus" size="medium">导入Excel</el-button>
</el-upload>

var vue = new Vue({
el: '#app',
data: {
// 导入的后台接口地址
importFileUrl : '/excellImport/importFile',methods：{
// 上传前对文件的类型和大小判断
beforeUpload : function (file) {
var self = this;
const extension = file.name.split('.')[1] === 'xls'
const extension2 = file.name.split('.')[1] === 'xlsx'
const isLt2M = file.size / 1024 / 1024 < 50
if (!extension && !extension2 ) {
alert('上传文件只能是 xls、xlsx 格式!')
}
if (!isLt2M) {
alert('上传文件大小不能超过 50MB!')
}
console.log(file);
return extension || extension2 && isLt2M
},
// 文件上传成功
uploadSuccess: function (response, file, flieList) {
this.initTable();
console.log(response);
alert(response.msg);
},
// 文件上传失败
uploadFail: function (err, file, fileList) {
alert('上传失败，请重试！');
},}

@SuppressWarnings({"unchecked", "finally" })
@ResponseBody
@RequestMapping(value="/importFile",method= RequestMethod.POST)
@ApiOperation("导入excel文件操作接口")
public JsonResult<XfImportexcelModel> importFile(HttpServletRequest request, HttpServletResponse response,
@RequestParam("file") MultipartFile[] excelFile, XfUser user) throws AccessExpiredException, DaoAccessException, Exception {
System.out.println("==========================================-进入导入excel文件操作接口==========================");
JsonResult<XfImportexcelModel> jsonResult = JsonResult.getDefaultResult();
try {
response.setCharacterEncoding("utf-8");
// 手动调用PrintWriter向客户端输入返回值，若本方法是有返回值的，则不需要
// PrintWriter out = response.getWriter(); //文件保存本地目录路径
String savePath = request.getSession().getServletContext().getRealPath(PATH_LINE) + "main"+PATH_LINE+"xxx"+PATH_LINE+"xxx"+PATH_LINE+"xxx"+PATH_LINE+"upload";
//文件保存目录URL
String saveUrl = request.getContextPath() + PATH_LINE +"upload"+PATH_LINE+"file"+PATH_LINE; if(!ServletFileUpload.isMultipartContent(request)){
// out.print(getError("请选择文件。"));
// out.close();
jsonResult.setMsg("请选择文件。");
return jsonResult;
}
//检查目录
File uploadDir = new File(savePath);
if(!uploadDir.isDirectory()){
uploadDir.mkdirs(); //目录不存在就创建目录
}
//检查目录写权限
if(!uploadDir.canWrite()){
// out.print(getError("上传目录没有写权限。"));
// out.close();
jsonResult.setMsg("上传目录没有写权限。");
return jsonResult;
} String dirName = request.getParameter("dir");
if (dirName == null) {
dirName = "file";
} //定义允许上传的文件扩展名
Map<String, String> extMap = new HashMap<String, String>();
extMap.put("image", "gif,jpg,jpeg,png,bmp");
extMap.put("flash", "swf,flv");
extMap.put("media", "swf,flv,mp3,wav,wma,wmv,mid,avi,mpg,asf,rm,rmvb");
extMap.put("file", "doc,docx,xls,xlsx,ppt,htm,html,xml,txt,zip,rar,gz,bz2"); if(!extMap.containsKey(dirName)){
// out.print(getError("目录名不正确。"));
// out.close();
jsonResult.setMsg("目录名不正确。");
return jsonResult;
}
//创建文件夹
savePath += PATH_LINE+dirName + PATH_LINE;
saveUrl += PATH_LINE;
File saveDirFile = new File(savePath);
if (!saveDirFile.exists()) {
saveDirFile.mkdirs(); //保存到项目工程文件夹指定目录中
} SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
String ymd = sdf.format(new Date());
savePath += ymd + PATH_LINE;
saveUrl += ymd + PATH_LINE;
File dirFile = new File(savePath); //存到本地之后在获取解析
if (!dirFile.exists()) {
dirFile.mkdirs();
} //最大文件大小
long maxSize = 10000000; // 保存文件
for(MultipartFile iFile : excelFile){
String fileName = iFile.getOriginalFilename(); //检查文件大小
if(iFile.getSize() > maxSize){
// out.print(getError("上传文件大小超过限制。"));
// out.close();
jsonResult.setMsg("上传文件大小超过限制。");
return jsonResult;
}
//检查扩展名
String fileExt = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
if(!Arrays.<String>asList(extMap.get(dirName).split(",")).contains(fileExt)){
//return getError("上传文件扩展名是不允许的扩展名。\n只允许" + extMap.get(dirName) + "格式。");
// out.print(getError("上传文件扩展名是不允许的扩展名。\n只允许" + extMap.get(dirName) + "格式。"));
// out.close();
jsonResult.setMsg("上传文件扩展名是不允许的扩展名。\n只允许" + extMap.get(dirName) + "格式。");
return jsonResult;
} // 解析Excel数据存入数据库
JsonResult<Object> jsonResultHandle = handleExcelData(iFile, user); //调用另外一个方法解析excel中的数据交互项目生产库
// 解析或者入库有问题则反馈到前端
if(!jsonResultHandle.getSuccess()){
// 返回导入信息到前端页面
jsonResult.setSuccess(false);
jsonResult.setMsg(jsonResultHandle.getMsg());
return jsonResult;
} // 返回导入信息到前端页面
jsonResult.setMsg(jsonResultHandle.getMsg()); SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
String newFileName = df.format(new Date()) + "_" + iFile.getName() + "." + fileExt;
try{
File uploadedFile = new File(savePath, newFileName); // 写入文件
FileUtils.copyInputStreamToFile(iFile.getInputStream(), uploadedFile);
}catch(Exception e){
// out.print(getError("上传文件失败。"));
// out.close();
jsonResult.setMsg("上传文件失败。");
return jsonResult;
} JSONObject obj = new JSONObject();
obj.put("error", 0);
obj.put("url", saveUrl + newFileName);// out.print(obj.toJSONString());
// out.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
logger.error("", e);
System.out.println(e); } finally {
System.out.println("===========================================结束导入excel文件操作接口==");
return jsonResult;
} } /**
* 解析Excel数据存入数据库
* @param file
* @param user
* @return
*/
@SuppressWarnings("unchecked")
public JsonResult<Object> handleExcelData(MultipartFile file, XfUser user) throws Exception{
System.out.println("==================================================开始解析Excel数据存入数据库==");
// 返回导入的结果信息
String resultString = "";
JsonResult<Object> jsonResult = JsonResult.getDefaultResult();
if (file != null) {
try {
List<ImportexcelParam> ImportexcelParamList = null; // 获取解析的excel数据
JsonResult<List<List<String>>> JsonResultList = ExcelUtil.parseExcel(file.getInputStream(),
file.getOriginalFilename());
// 解析有问题反馈到调用地方
if(!JsonResultList.getSuccess()){
jsonResult.setSuccess(false);
jsonResult.setMsg(JsonResultList.getMsg());
return jsonResult;
}
// excel数据解析成功，进行获取和入库处理
List<List<List<String>>> resultList = JsonResultList.getData(); // 目前只检查第一个sheet
if (resultList != null && !resultList.isEmpty()) {
for (List<List<String>> sheetList : resultList) {
ImportexcelParamList = new ArrayList<>();
if (sheetList != null && !sheetList.isEmpty()) {
if (sheetList.size() > 200) {
jsonResult.setSuccess(false);
jsonResult.setMsg("单次上传文件记录条数不能超过200条！");
return jsonResult;
}
if(sheetList.get(0).size() != 20){
jsonResult.setSuccess(false);
jsonResult.setMsg("请使用正确的导入模板！");
return jsonResult;
}
// 导入开始时间，毫秒
long startTime = System.currentTimeMillis();
// 记录导入成功的数据条数
int successImportNum = 0;
// 重复的编号记录
String repeatInfo="";
for (List<String> rowList : sheetList){
if (!rowList.isEmpty()) {
// 投诉人和投诉内容筛选有效行
if(StringUtils.isNullOrEmpty(rowList.get(13))||StringUtils.isNullOrEmpty(rowList.get(14))){
continue;
}
// 导入数据举报编号去重
XfImportexcel xfImportexcel = iXfImportexcelService.getXfImportexcelByBussinesNo(rowList.get(2));
if (xfImportexcel != null) {
repeatInfo += "【";
repeatInfo += rowList.get(2);
repeatInfo += "】";
continue;
}
ImportexcelParam ImportexcelParam = new ImportexcelParam();
ImportexcelParam.setStatus(rowList.get(0));//状态
ImportexcelParam.setEmergencyLevel(rowList.get(1));//紧急程度
ImportexcelParam.setBussinesNo(rowList.get(2));//业务编号
ImportexcelParam.setCallSysNo(rowList.get(3));//来电编号
ImportexcelParam.setRepeatFlag("初件".equals(rowList.get(4))?false:true);//重复标志
ImportexcelParam.setReplyFlag("未答复".equals(rowList.get(5))?false:true);//答复标志
ImportexcelParam.setProcessUnitName(rowList.get(6));//处理单位
ImportexcelParam.setOperator(rowList.get(7));//经办人
ImportexcelParam.setProcessamento(rowList.get(8));//处理方式
ImportexcelParam.setProcessStatus(rowList.get(9));//处理状态
ImportexcelParam.setCallPhoneNum(rowList.get(10));//来电号码
ImportexcelParam.setLinkTelphone(rowList.get(11));//联系电话
ImportexcelParam.setCreateBy(rowList.get(12));//创建人
ImportexcelParam.setCallPerson(rowList.get(13));//来电人
ImportexcelParam.setComplainContent(rowList.get(14));//投诉内容
ImportexcelParam.setCallDate(StringUtils.isNullOrEmpty(rowList.get(15))?null:TimeUtils.string2Timestamp(DateUtil.YYYY_MM_DD_HH_MM_SS, rowList.get(15)));//来电日期
ImportexcelParam.setCallPhoneNum(rowList.get(16));//来电人数
ImportexcelParam.setItemType(rowList.get(17));//事项分类
ImportexcelParam.setCallPurpose(rowList.get(18));//来电目的
ImportexcelParam.setProcessTimeLimit(StringUtils.isNullOrEmpty(rowList.get(19))?null:TimeUtils.string2Timestamp(DateUtil.YYYY_MM_DD_HH_MM_SS, rowList.get(19)));//处理时限
ImportexcelParamList.add(ImportexcelParam); }
}
// 调用excel数据导入保存业务实现方法
successImportNum = iXfImportexcelService.save(ImportexcelParamList, user);
// 导入失败条数
int failImportNum = ImportexcelParamList.size()-successImportNum;
// 导入结束时间，毫秒
long endTime = System.currentTimeMillis();
if(repeatInfo!=""){
resultString = "成功导入"+successImportNum+"条记录，失败"+failImportNum+"条；共耗时"+(endTime-startTime)+"毫秒;"+repeatInfo+"编号数据已存在";
}else{
resultString = "成功导入"+successImportNum+"条记录，失败"+failImportNum+"条；共耗时"+(endTime-startTime)+"毫秒。";
}
// 解析和入库都无问题，设置sucess和信息反馈
jsonResult.setSuccess(true, resultString);
System.out.println("================================================="+resultString+"========="); }else{
jsonResult.setSuccess(false, "文件没有数据记录");
}
}
// List<List<String>> sheetList = resultList.get(0); }else{
jsonResult.setSuccess(false, "文件没有数据记录");
} }catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// String path = "E:/springUpload" + file.getOriginalFilename();
// 上传
// file.transferTo(new File(path));
}else{
jsonResult.setSuccess(false, "无法获取文件");
}
System.out.println("==================================================结束解析Excel数据存入数据库======");
return jsonResult;
}

package com.xxx.xx.utils;import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;import com.mlsc.fw.common.exception.ParamDataException;
import com.mlsc.fw.common.utils.DateUtil;
import com.xxx.fw.web.JsonResult;public class ExcelUtil {
private static Logger logger = Logger.getLogger(ExcelUtil.class); public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String CELL_TYPE_STRING="String"; /**
* 适用于第一行是标题行的excel，
*
* resultList --> sheetList --> rowList
*
* @throws Exception
*/
@SuppressWarnings("unchecked")
public static JsonResult<List<List<String>>> parseExcel(InputStream in, String suffixName) throws ParamDataException {
JsonResult<List<List<String>>> jsonResult = JsonResult.getDefaultResult();
List<List<List<String>>> resultList = new ArrayList<>();
List<List<String>> sheetList = null;
List<String> rowList = null;
String fileType = suffixName.substring(suffixName.lastIndexOf(".") + 1, suffixName.length());
Workbook wb = null;
try {
if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2003_POSTFIX)) {
wb = new HSSFWorkbook(in);
} else if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2010_POSTFIX)) {
wb = new XSSFWorkbook(in);
} else {
jsonResult.setSuccess(false);
jsonResult.setMsg("请使用正确的导入模板");
return jsonResult;
} int sheetSize = wb.getNumberOfSheets();
// 文件中不止一张工作表
if(sheetSize>1){
jsonResult.setSuccess(false);
jsonResult.setMsg("请使用正确的导入模板");
return jsonResult;
}
for (int i = 0; i < sheetSize; i++) {// 遍历sheet页
Sheet sheet = wb.getSheetAt(i); int rowSize = sheet.getLastRowNum() + 1;
if(rowSize<=1){
jsonResult.setSuccess(false);
jsonResult.setMsg("导入模板没有数据记录");
return jsonResult;
}
sheetList = new ArrayList<>();
int columnSize = 0; // 从第一行标题行开始向下遍历行，包括第一行
for (int j = 0; j < rowSize; j++) {
Row row = sheet.getRow(j); // 遍历第一行作用是为了获取每一行的列数
if (j == 0) {
// bypass the 1st row
columnSize = row.getLastCellNum();
continue;
} // 略过空行
if (row == null) {
continue;
}
rowList = new ArrayList<>();
for (int m = 0; m < columnSize; m++) {
// if (m == 0 && (row.getCell(3)==null||row.getCell(3).equals("")||row.getCell(3).getCellType() ==HSSFCell.CELL_TYPE_BLANK) ) {
// break;
// }
if (row.getCell(m) != null){
rowList.add(getValue(row.getCell(m)));
} else {
rowList.add("");
}
}
if (rowList.size() == columnSize)
sheetList.add(rowList);
}
resultList.add(sheetList);
} } catch (Exception e) {
logger.error("", e);
throw new ParamDataException("上传文件内容格式不正确，请检查文件内容格式！");
} finally {
if (wb != null) {
try {
wb.close();
} catch (Exception e) {
logger.error("关闭Workbook出现异常！", e);
}
}
if (in != null) {
try {
in.close();
} catch (Exception e) {
logger.error("关闭输入流出现异常！", e);
}
}
}
jsonResult.setSuccess(true);
jsonResult.setData(resultList);
return jsonResult;
} public static void exportByTemplate(FileInfo fileInfo, List<DataInfo> dataInfoList, HttpServletResponse response) {
try {
setResponseHeader(fileInfo.getGenerateFileName(), response);
XSSFWorkbook workbook = createWorkBook(fileInfo, dataInfoList, response);
outputFile(fileInfo, workbook, response);
} catch (UnsupportedEncodingException e) {
logger.error("", e);
} } private static void outputFile(FileInfo fileInfo, XSSFWorkbook workbook, HttpServletResponse response) {
BufferedOutputStream bufferedOutPut = null;
try {
OutputStream output = response.getOutputStream();
bufferedOutPut = new BufferedOutputStream(output);
bufferedOutPut.flush();
workbook.write(bufferedOutPut);
} catch (IOException e) {
logger.error("", e);
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (IOException e) {
}
}
if (bufferedOutPut != null) {
try {
bufferedOutPut.close();
} catch (IOException e) {
}
} }
} @SuppressWarnings("deprecation")
private static XSSFWorkbook createWorkBook(FileInfo fileInfo, List<DataInfo> dataInfoList,
HttpServletResponse response) {
XSSFWorkbook workbook = null; try {
File fi = new File(fileInfo.getTemplatePath() + fileInfo.getTemplateName());
FileInputStream fs = new FileInputStream(fi);
// 读取excel模板
workbook = new XSSFWorkbook(fs);
if (dataInfoList == null || dataInfoList.size() == 0) {
return workbook;
}
int rowIndex = 0;
int columnIndex = 0;
// sheet
for (DataInfo dataInfo : dataInfoList) {
if(dataInfo.getSheetIndex()==null){
continue;
}
XSSFSheet sheet = workbook.getSheetAt(dataInfo.getSheetIndex());
rowIndex = dataInfo.getRowStart();
if(StringUtils.isNotEmpty(dataInfo.getModifiedTitle())){
CellStyle cs=sheet.getRow(0).getCell(0).getCellStyle();
sheet.getRow(0).getCell(0).setCellValue(dataInfo.getModifiedTitle());
sheet.getRow(0).getCell(0).setCellStyle(cs);
}
if (dataInfo.getData() == null || dataInfo.getData().isEmpty()) {
continue;
}
XSSFRow styleRow = null;
CellStyle style = null;
CellStyle style0 = null;
// row
for (int rIndex = 0; rIndex < dataInfo.getData().size(); rIndex++) {
if (dataInfo.getData().get(rIndex) == null || dataInfo.getData().get(rIndex).length == 0) {
continue;
}
columnIndex = dataInfo.getColumnStart();
XSSFRow row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
if(rIndex==0){
styleRow = sheet.getRow(rowIndex);
} // cell
for (int cIndex = 0; cIndex < dataInfo.getColumnLength(); cIndex++) {
if(styleRow.getCell(columnIndex)==null){
System.out.println(222);
}
style = styleRow.getCell(columnIndex).getCellStyle(); if(dataInfo.isGenIndex()){
XSSFCell cell0 = row.getCell(0);
if(cell0==null){
cell0=row.createCell(0);
}
cell0.setCellValue(rIndex+1);
style0 = styleRow.getCell(0).getCellStyle();
cell0.setCellStyle(style0);
}
XSSFCell cell = row.getCell(columnIndex);
if (cell == null) {
cell = row.createCell(columnIndex);
}
// if(cIndex==17){
// System.out.println(333);
// }
// System.out.println("sheet:"+dataInfo.getSheetIndex()+"/rIndex:"+rIndex+"/cIndex:"+cIndex);
// if(null == dataInfo.getData().get(rIndex)[cIndex]){
// System.out.println(111);
// }
if(dataInfo.getTypeMap()!=null && dataInfo.getTypeMap().containsKey(cIndex)){
if(CELL_TYPE_STRING.equals(dataInfo.getTypeMap().get(cIndex))){
cell.setCellValue(dataInfo.getData().get(rIndex)[cIndex]);
}
}else if(null != dataInfo.getData().get(rIndex)[cIndex]
&& dataInfo.getData().get(rIndex)[cIndex].matches("^(-?\\d+)(\\.\\d+)?$")){
cell.setCellValue(Double.parseDouble(dataInfo.getData().get(rIndex)[cIndex]));
}else{
cell.setCellValue(dataInfo.getData().get(rIndex)[cIndex]);
}
cell.setCellStyle(style);
columnIndex++;
}
rowIndex++;
}
}
List<Integer> hideSheetList=new ArrayList<>();
for(DataInfo dataInfo : dataInfoList){
hideSheetList.add(dataInfo.getSheetIndex());
} for(int i=0;i<workbook.getNumberOfSheets();i++){
if(!hideSheetList.contains(i)){
workbook.setSheetHidden(i, 2);
}
} } catch (IOException e) {
logger.error("", e);
}
return workbook;
} @SuppressWarnings("deprecation")
private static String getValue(Cell cell) {
DecimalFormat df = new DecimalFormat("#.###");
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat(DateUtil.YYYY_MM_DD);
return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
return df.format(cell.getNumericCellValue());
case HSSFCell.CELL_TYPE_STRING:
return cell.getStringCellValue();
case HSSFCell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case HSSFCell.CELL_TYPE_BLANK:
return ""; }
return "";
} @SuppressWarnings("unused")
public static String getExcelAcceptDepartment(InputStream in, String suffixName) throws ParamDataException {
String header = "";
String fileType = suffixName.substring(suffixName.lastIndexOf(".") + 1, suffixName.length());
Workbook wb = null;
try {
if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2003_POSTFIX)) {
wb = new HSSFWorkbook(in);
} else if (fileType.equalsIgnoreCase(OFFICE_EXCEL_2010_POSTFIX)) {
wb = new XSSFWorkbook(in);
} else {
throw new ParamDataException("读取的不是excel文件");
} int sheetSize = wb.getNumberOfSheets();
for (int i = 0; i < sheetSize; i++) {// 遍历sheet页
Sheet sheet = wb.getSheetAt(i);
header = getValue(sheet.getRow(2).getCell(1)).trim();
break;
}
} catch (Exception e) {
logger.error("", e);
throw new ParamDataException("上传文件内容格式不正确，请检查文件内容格式！");
} finally {
if (wb != null) {
try {
wb.close();
} catch (Exception e) {
logger.error("关闭Workbook出现异常！", e);
}
}
if (in != null) {
try {
in.close();
} catch (Exception e) {
logger.error("关闭输入流出现异常！", e);
}
}
}
return header;
} private static void setResponseHeader(String fileName, HttpServletResponse response)
throws UnsupportedEncodingException {
response.reset();
// SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
// String date = sdf.format(new Date());
// String newFileName=fileName+date;
// 指定下载的文件名
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
}
}