1.前端使用fileinput插件上传
$("#file2").fileinput({
language: 'zh',
uploadUrl: basePath + 'uploadVehicleFile',
maxFileCount: 40, //表示允许同时上传的最大文件个数
maxFileSize: 20480000,
showCaption: true, //是否显示标题
browseClass: "btn btn-primary", //按钮样式
showPreview: true,
allowedFileExtensions: ["xls","xlsx"],
uploadAsync: true,
showUpload: true, //是否显示上传按钮
maxImageWidth: 80,
maxImageHeight: 80,
enctype: 'multipart/form-data', //不对发送的字符进行编码
previewFileIcon: "<i class='glyphicon glyphicon-king'></i>",
msgFilesTooMany: "选择上传的文件数量({n}) 超过允许的最大数值{m}！",
uploadExtraData: function(previewId, index) {
var data = {
bidId: null,
planProId: "项目主键",
belongProject: "所属项目",
firstMenu: "一级菜单",
secondMenu: "二级菜单",
fileType: "文件类型",
workingstageId: "工作空间",
parentTreeId: "父类树id"
};
return data;
},
previewFileIconSettings: {
'doc': '<i class="fa fa-file-word-o text-primary"></i>',
'xls': '<i class="fa fa-file-excel-o text-success"></i>',
'ppt': '<i class="fa fa-file-powerpoint-o text-danger"></i>',
'jpg': '<i class="fa fa-file-photo-o text-warning"></i>',
'pdf': '<i class="fa fa-file-pdf-o text-danger"></i>',
'zip': '<i class="fa fa-file-archive-o text-muted"></i>',
'htm': '<i class="fa fa-file-code-o text-info"></i>',
'txt': '<i class="fa fa-file-text-o text-info"></i>',
'mov': '<i class="fa fa-file-movie-o text-warning"></i>',
'mp3': '<i class="fa fa-file-audio-o text-warning"></i>',
},
previewFileExtSettings: {
'doc': function(ext) {
return ext.match(/(doc|docx)$/i);
},
'xls': function(ext) {
return ext.match(/(xls|xlsx)$/i);
},
'ppt': function(ext) {
return ext.match(/(ppt|pptx)$/i);
},
'zip': function(ext) {
return ext.match(/(zip|rar|tar|gzip|gz|7z)$/i);
},
'htm': function(ext) {
return ext.match(/(php|js|css|htm|html)$/i);
},
'txt': function(ext) {
return ext.match(/(txt|ini|md)$/i);
},
'mov': function(ext) {
return ext.match(/(avi|mpg|mkv|mov|mp4|3gp|webm|wmv)$/i);
},
'mp3': function(ext) {
return ext.match(/(mp3|wav)$/i);
},
}
});2.后端接收，使用springmvc方式
controller层
@RequestMapping("/uploadVehicleFile")
public String uploadAndParseRoadConfirmFile(@RequestParam("file") MultipartFile[] files, HttpServletRequest request,HttpServletResponse response) {
try {
String planProId = request.getParameter("planProId");
if(examineFileListService.chooseProId(planProId)) {
return robPlandRoadConfirmService.uploadAndParseRoadConfirmFile(files,request,response);
}else {
return new Message(100,"文件上传解析失败，请选择所属项目!").toJson();
}
} catch (Exception e) {
return new Message(Message.ERROR,"文件上传有误!").toJson();
}
}service层：
public String uploadVehicleFile(MultipartFile[] files, HttpServletRequest request, HttpServletResponse response) {
Message message = new Message();
try {
if(files!=null && files.length>0){
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
List<RobPlancqCplan> robPlancqCplanList = new ArrayList<RobPlancqCplan>();
for (int i = 0; i < files.length; i++) {
robPlancqCplanList.clear();
MultipartFile file = files[i];
String md5 = DigestUtils.md5Hex(file.getInputStream()); //这里不能用fileInput，试了好久
String filePath=examineFileListService.getSaveMinioPath(request); //保存在minio中的目录结构
String fileName = file.getOriginalFilename(); //文件名字
String hql = "select * from 文件表 where file_md5='"+md5+"' and file_path='"+filePath+"'"; //验证文件是否存在，不存在再上传
List<ExamineFileList> existExamineFileList = super.getDao().findBySql(hql, ExamineFileList.class);
if(existExamineFileList != null && existExamineFileList.size()>0) { //指定目录下已经存在此文件，不进行上传操作
message.setType(100);
message.setMsg(fileName+"已经上传过，不再重复上传，文件上传失败");
}else{
InputStream fileInput = file.getInputStream(); //输入流
Workbook workBook = examineFileListService.getWorkBook(fileInput,fileName);
if(workBook != null){
String planProId = request.getParameter("planProId");
message = parseFileAndSave(workBook,robPlancqCplanList,planProId,fileName); //解析和保存文件
if(message.getType()==200){ //解析没有问题才上传到Minio中和保存到数据库中
robPlancqCplanRepository.save(robPlancqCplanList);
message = examineFileListService.uploadFileOne(file,request,response);
}
}else {
message.setType(100);
message.setMsg(fileName+"为空，文件上传失败");
}
}
}
}else {
message.setType(100);
message.setMsg("上传文件数量少于1，文件上传失败");
}
} catch (Exception e) {
message.setType(Message.ERROR);
message.setMsg("请检测excel必填项目完善文档，文件上传失败");
}
return message.toJson();
}
/**
* @Description 解析和保存文件
* @author [zhanglizeng] Tel：
* @createDate 2018年11月22日 下午3:21:04
*/
private Message parseFileAndSave(Workbook workBook, List<RobPlancqCplan> robPlancqCplanList, String planProId,String fileName) {
Message message = new Message();
try {
for(int sheetNum=0;sheetNum< workBook.getNumberOfSheets();sheetNum++){
Sheet sheet = workBook.getSheetAt(sheetNum);
if(sheet.getLastRowNum() == 0 && sheet.getPhysicalNumberOfRows() == 0){
continue;
}
message = parseSheet(sheet,robPlancqCplanList,planProId,fileName);
if(message.getType() != 200){ //有一个sheet不符合判断此解析文档无效
return message;
}
}
} catch (Exception e) {
return new Message(Message.ERROR,fileName+"文件解析失败");
}
return message;
}
/**
* @Description 解析sheet
* @author [zhanglizeng] Tel：
* @createDate 2018年11月22日 下午3:23:09
*/
private Message parseSheet(Sheet sheet, List<RobPlancqCplan> robPlancqCplanList, String planProId,String fileName) {
Message message = new Message();
try {
String title = examineFileListService.parseCell(sheet.getRow(0).getCell(0)); //得到标题
if(title != null && title.length()>0){ //判断标题是否合法
if(checkColTitle(sheet)){ //判断excel的列标题是否和模板一致
message = parseCell(sheet,robPlancqCplanList,planProId,fileName); //解析文件
}else{
message.setType(100);
message.setMsg("文件中的列标题顺序不符合要求，请参照模板填写，"+fileName+"解析失败!");
}
}else {
return new Message(100,"sheet没有标题，"+fileName+"文件解析失败");
}
} catch (Exception e) {
return new Message(Message.ERROR,"存在为空的sheet，建议删除空sheet,文件解析失败");
}
return message;
}
/**
* @Description 解析文件
* @author [zhanglizeng] Tel：
* @createDate 2018年11月22日 下午3:43:34
*/
private Message parseCell(Sheet sheet, List<RobPlancqCplan> robPlancqCplanList, String planProId, String fileName) {
int temp=0;
try {
for(int rowNum = 2;rowNum <= sheet.getPhysicalNumberOfRows();++rowNum){
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
temp = rowNum;
parseRow(row,robPlancqCplanList,planProId); //解析每一行数据
}
} catch (Exception e) {
return new Message(Message.ERROR,(temp+1)+"行文本内容不符合模板要求，"+fileName+"解析失败");
}
return new Message(Message.OK,fileName+"文件上传成功");
}
/**
* @Description 解析每一行数据
* @author [zhanglizeng] Tel：
* @createDate 2018年11月22日 下午3:45:33
*/
private void parseRow(Row row, List<RobPlancqCplan> robPlancqCplanList, String planProId)throws Exception {
String carType = examineFileListService.parseCell(row.getCell(0)).trim();
String carUnit = examineFileListService.parseCell(row.getCell(1)).trim();
String carPlates = examineFileListService.parseCell(row.getCell(2)).trim();
String carVtime = examineFileListService.parseCell(row.getCell(3)).trim();
String carRemark = examineFileListService.parseCell(row.getCell(4)).trim();
if((carType!=null && carType.length()>0) || (carUnit!=null && carUnit.length()>0) ||
(carPlates!=null && carPlates.length()>0) || (carVtime!=null && carVtime.length()>0)
|| (carRemark!=null && carRemark.length()>0)) {
RobPlancqCplan robPlancqCplan = new RobPlancqCplan();
robPlancqCplan.setCarType(carType);
robPlancqCplan.setCarUnit(carUnit);
robPlancqCplan.setCarPlates(carPlates);
if(carVtime != null && carVtime.length()>0) {
robPlancqCplan.setCarVtime(examineFileListService.getDateByString(carVtime)); //转成日期格式
}
robPlancqCplan.setCarRemark(carRemark);
robPlancqCplan.setCreateTime(new Date());
robPlancqCplan.setCreateUserCode(getUserCode());
robPlancqCplan.setCreateUserName(getUserName());
robPlancqCplan.setGuid(getUUID());
robPlancqCplan.setPlanProId(planProId);
robPlancqCplanList.add(robPlancqCplan);
}
}
/**
* @Description 判断excel的列标题是否和模板一致
* @author [zhanglizeng] Tel：
* @createDate 2018年11月22日 下午3:25:28
*/
private boolean checkColTitle(Sheet sheet) {
String carType = examineFileListService.parseCell(sheet.getRow(1).getCell(0)).trim(); //车辆用途
String carUnit = examineFileListService.parseCell(sheet.getRow(1).getCell(1)).trim(); //车辆单位
String carPlates = examineFileListService.parseCell(sheet.getRow(1).getCell(2)).trim(); //车辆牌照
String carVtime = examineFileListService.parseCell(sheet.getRow(1).getCell(3)).trim(); //校验完成时间
String carRemark = examineFileListService.parseCell(sheet.getRow(1).getCell(4)).trim(); //备注
if("车辆用途".equals(carType) && "车辆单位".equals(carUnit) && "车辆牌照".equals(carPlates)
&& "校验完成时间".equals(carVtime) && "备注".equals(carRemark)) {
return true;
}else {
return false;
}
}
3.页面接收上传结果：
$("#file2").on('fileuploaded', function(event, data, previewId, index) {
var message = mini.decode(data);
if(message.response.type == 200) {
if(!flag2){ //手动点击上传
showMsg("xx文件上传成功","success",800,"center","center");
}else{ //点击确定时检测到没有上传，采取的上传操作
if(getFileTotal2()==1){
if(getFileTotal1()==0 && getFileTotal3()==0){ //其它文件都上传完了，可以在个方法中关闭窗口
showMsg("xx上传成功","success",1800,"center","center",closeWindow);
}else{
showMsg("xx件上传成功","success",800,"center","center");
}
}
}
}else if(message.response.type == 150){
showMsg("请选择所属项目再上传xx文件，文件上传失败","warning",2000,"center","center");
}else {
showMsg(message.response.msg, "warning", 1200, "center", "center");
}
});
4.导出excel时，给某个输入框添加下拉选项框的方式
（1）直接添加在输入框下，最多只能添加255个字符
private void setCellComboxForDict(int rowStart, int rowEnd, int colStart, int colEnd, List<PmsmYhfa> list,
Sheet sheet) throws Exception {
if (null != list && list.size() > 0) {
String[] subjects = new String[list.size()];
for (int i = 0; i < list.size(); i++) {
subjects[i] = list.get(i).getFaCodeName();
}
DataValidationHelper helper = sheet.getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(subjects);
CellRangeAddressList addressList = null;
DataValidation dataValidation = null;
addressList = new CellRangeAddressList(rowStart, rowEnd, colStart, colEnd);
dataValidation = helper.createValidation(constraint, addressList);
dataValidation.setShowErrorBox(true); // 输入非法数据时，弹窗警告框
sheet.addValidationData(dataValidation);
}
}
（2）使用隐藏sheet的方式
private void setCellComboxForDict(int rowStart, int rowEnd, int colStart, int colEnd, List<PmsmYhfa> list,
Sheet sheet, XSSFWorkbook workbook) throws Exception {
if (null != list && list.size() > 0) {
//使用隐藏sheet的方式进行下拉数据存储，可以无限存放下拉集合
XSSFSheet hidden = workbook.createSheet("hidden");
//数据源sheet页不显示
workbook.setSheetHidden(1, true);
XSSFRow row = null;
XSSFCell cell = null;
for (int i = 0, length = list.size(); i < length; i++) {
row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(list.get(i).getFaCodeName());
}
Name namedCell = workbook.createName();
namedCell.setNameName("hidden");
namedCell.setRefersToFormula("hidden!$A$1:$A$" + list.size());
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint("hidden");
CellRangeAddressList addressList = null;
addressList = new CellRangeAddressList(rowStart, rowEnd, colStart, colEnd);
XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
}
}5.导出excel时，添加标题，隐藏列
private void addTitle(Sheet sheet, CellStyle headStyle) {
String[] head = new String[] {"子模型名称","路面类型", "技术等级", "交通量","PCI","RQI","PSSI","RDI","SRI","PWI","路龄","养护方案","路面模型id","路面类型id","决策树id"};
Row row = sheet.createRow(0);
for (int i = 0; i < head.length; i++) {
sheet.setColumnWidth(i, 10*256);
Cell cell = row.createCell(i);
cell.setCellValue(head[i]);
cell.setCellStyle(headStyle);
row.setHeight((short)720);
}
sheet.setColumnWidth(0, 12*256);
sheet.setColumnWidth(11, 50*256);
sheet.setColumnHidden(head.length-1, true); // 隐藏列
sheet.setColumnHidden(head.length-2, true); // 隐藏列
sheet.setColumnHidden(head.length-3, true); // 隐藏列
}6.设置标题样式
private CellStyle getTitleCellStyle(XSSFWorkbook wb) {
CellStyle cellStyle = wb.createCellStyle();// 单元格样式
Font tfstyle = wb.createFont();
tfstyle.setBold(true);// 加粗
tfstyle.setFontName("宋体");// 宋体
tfstyle.setFontHeightInPoints((short) 12);// 字体大小
cellStyle.setFont(tfstyle);// 把字体格式添加到单元格中
cellStyle.setAlignment(HorizontalAlignment.CENTER); // 内容居中,添加边框
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setWrapText(true);
return cellStyle;
}7.设置文本行样式
private CellStyle getCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle(); // 获取当前单元格的样式对象
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());// 设置上锁的单元格背景色
cellStyle.setLocked(false); // 设定此单元格为非锁定状态
Font fontstyle = wb.createFont();
fontstyle.setBold(false);// 不加粗
fontstyle.setFontName("宋体");// 宋体
fontstyle.setFontHeightInPoints((short) 10);// 字体大小
cellStyle.setFont(fontstyle);// 把字体格式添加到单元格中
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 内容居中,添加边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
return cellStyle;
}8.设置锁定cell样式
private CellStyle getLockCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle(); // 获取当前单元格的样式对象
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());// 设置上锁的单元格背景色
cellStyle.setLocked(true); // 设定此单元格为非锁定状态
Font fontstyle = wb.createFont();
fontstyle.setBold(false);// 不加粗
fontstyle.setFontName("宋体");// 宋体
fontstyle.setFontHeightInPoints((short) 10);// 字体大小
cellStyle.setFont(fontstyle);// 把字体格式添加到单元格中
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 内容居中,添加边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
return cellStyle;
}9.导出excel的主要后台代码，包含设置表头，设置文本值，锁定sheet
public void exportFile(HttpServletRequest request, HttpServletResponse response) {
InputStream in = null;
OutputStream out = null;
String modelIdParent = request.getParameter("modelIdParent");
String modelIdParentText = request.getParameter("modelIdParentText");
String roadType = request.getParameter("roadType");
try {
response.setCharacterEncoding("UTF-8");
String fileNameNew = modelIdParentText+"-沥青路面-水泥路面.xlsx";
response.addHeader("Content-Disposition",
" attachment;filename=" + new String(fileNameNew.getBytes(), "iso-8859-1"));
response.setContentType("application/octet-stream");
XSSFWorkbook wb = new XSSFWorkbook();// 关闭自动刷新并累积内存中的所有行
Sheet sheet = wb.createSheet("养护决策模型");
sheet.protectSheet(""); // 设置表单保护密码
CellStyle titleCellStyle = getTitleCellStyle(wb); // 设置标题样式
CellStyle cellStyle = getCellStyle(wb); // 设置文本样式
CellStyle lockStyle = getLockCellStyle(wb); // 设置文本样式
addTitle(sheet, titleCellStyle); // 添加excel标题
List<PmsMModellist> pmsMModellist = getmMdelListByParentId(modelIdParent);//通过父项目id查询模型集合
if (null != pmsMModellist && pmsMModellist.size() > 0) {
int startNum = 1; //每次查询赋值的起始行
List<PmsmYhfa> pmsmYhfaList = getYhfaCodeList(null,roadType);
for (int i = 0;i < pmsMModellist.size();i++) {
String modelIdOne = pmsMModellist.get(i).getId();
String modelNameOne = pmsMModellist.get(i).getModelName();
List<PmsmJcs> pmsmJcsList = getPmsmJcsListByModelId(modelIdOne); //通过模型id查询决策树集合
if(null != pmsmJcsList && pmsmJcsList.size() > 0) {
setExcelValueOne(startNum,modelNameOne,pmsmJcsList,sheet,cellStyle,lockStyle,pmsmYhfaList);
startNum = startNum+pmsmJcsList.size(); //记录起始
}
}
setCellComboxForDict(1, startNum, 11, 11, pmsmYhfaList, sheet,wb); // 从数据字典中给空格下拉赋值
}
out = response.getOutputStream();
wb.write(out);
out.close();
//wb.dispose();// 处理在磁盘上支持此工作簿的临时文件
// 给出参数,进度条完成百分之百
} catch (Exception e) {
e.printStackTrace();
} finally {
if (in != null) {
try {
in.close();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}10.设置文本行值、以及样式值
private void setExcelValue(Sheet sheet, CellStyle cellStyle, CellStyle lockStyle, List<PmsmJcs> list, List<PmsmYhfa> pmsmYhfaList) throws IOException {
for (int i = 0; i < list.size(); i++) {
Row row = sheet.createRow(i + 1);// 第一行
String idExplain = list.get(i).getIdExplain();
String [] idExplainArr = idExplain.split("-");
for (int j = 0;j < idExplainArr.length;j++) {
row.createCell(j).setCellValue(String.valueOf(idExplainArr[j]));
}
row.createCell(idExplainArr.length).setCellValue(formatPmsmYhfaName(list.get(i).getFa1(),pmsmYhfaList));
row.createCell(idExplainArr.length+1).setCellValue(String.valueOf(list.get(i).getLmMxId()));
row.createCell(idExplainArr.length+2).setCellValue(String.valueOf(list.get(i).getRoadGrade()));
row.createCell(idExplainArr.length+3).setCellValue(String.valueOf(list.get(i).getLmLxId()));
row.createCell(idExplainArr.length+4).setCellValue(String.valueOf(list.get(i).getId()));
for (int j = 0; j < idExplainArr.length; j++) {// 给每个单元格设置格式
row.getCell(j).setCellStyle(lockStyle);
}
row.getCell(idExplainArr.length).setCellStyle(cellStyle);
for (int j = idExplainArr.length+1; j <= idExplainArr.length+4; j++) {// 给每个单元格设置格式
row.getCell(j).setCellStyle(lockStyle);
}
if (i % 500 == 0) {
((SXSSFSheet) sheet).flushRows();// 每500行刷新所有行
}
}
}
扫一扫
943
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
