简述
这段时间开发后台,接触了一部分文件处理,主要包括,从excel文件导入数据到数据库,从数据库拉取数据,以excel形式导出,上传文件,以base64形式保存到数据库,从数据库读取base64数据,以文件形式下载,对多个base64形式的文件进行压缩下载。spring boot操作数据库使用的是spring data jpa
前台页面
对应的html
<div class="modal fade" id="myModal" role="dialog" aria-labelledby="myModalLabel"
aria-hidden="true">
<div class="modal-dialog modal-lg" role="document">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="myModalLabel">
新增申请
</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">
×
</span>
</button>
</div>
<form class="m-form m-form--fit m-form--label-align-right" id="myModalForm" action="">
<div class="m-portlet__body">
<div class="form-group m-form__group row" style="margin-top: 10px">
<label class="col-form-label col-lg-4 col-sm-12">
人员名单 *
</label>
<div class="col-lg-7 col-md-9 col-sm-12">
<div class="custom-file">
<input type="file" class="custom-file-input" id="excelFileParameter"
name="excelFileParameter" accept=".csv, application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
<label class="custom-file-label" for="excelFileParameter">
</label>
</div>
</div>
</div>
<div class="form-group m-form__group row">
<label class="col-form-label col-lg-4 col-sm-12">
企业防控承诺书 *
</label>
<div class="col-lg-7 col-md-9 col-sm-12">
<div class="custom-file">
<input type="file" class="custom-file-input" id="fileParameter"
name="fileParameter">
<label class="custom-file-label" id="fileParameter-label" for="fileParameter">
</label>
</div>
</div>
</div>
<div class="form-group m-form__group row">
<label class="col-form-label col-lg-4 col-sm-12">
申请表 *
</label>
<div class="col-lg-7 col-md-9 col-sm-12">
<div class="custom-file">
<input type="file" class="custom-file-input" id="fileApplication"
name="fileApplication">
<label class="custom-file-label" id="fileApplication-label" for="fileApplication">
</label>
</div>
</div>
</div>
<div class="form-group m-form__group row">
<label class="col-form-label col-lg-4 col-sm-12">
防控方案 *
</label>
<div class="col-lg-7 col-md-9 col-sm-12">
<div class="custom-file">
<input type="file" class="custom-file-input" id="filePlan"
name="filePlan">
<label class="custom-file-label" id="filePlan-label" for="filePlan">
</label>
</div>
</div>
</div>
<div class="row" style="margin-left: 120px">
<ul class="m-widget28__nav-items nav nav-pills nav-fill" role="tablist">
<li class="m-widget28__nav-item nav-item">
<a class="nav-link" th:href="${#servletContext.getAttribute('thymeleaf_resource_root')} + 'resources/quickWayStaff.xlsx'">
<span><i class="fa flaticon-clipboard"></i></span>
<span style="font-weight: bold">点击下载:人员名单批量导入(模板)</span>
</a>
</li>
</ul>
</div>
<div style="margin-left: 120px">
<h5>注意事项</h5>
<ul style="color:red;">
<li>1.excel格式请严格按照人员名单模板来导入</li>
</ul>
</div>
<input id="hiddenId" name="id" type="hidden">
<input id="isNew" type="hidden">
</div>
<div class="m-portlet__foot m-portlet__foot--fit">
<div class="m-form__actions m-form__actions">
<div class="row">
<div class="col-lg-9 ml-lg-auto">
<button type="submit" class="btn btn-success" id="submit">
提交
</button>
<button type="button" class="btn btn-secondary" data-dismiss="modal">
取消
</button>
</div>
</div>
</div>
</div>
</form>
<!-- END FORM-->
</div>
</div>
</div>
对应的js
//ajax 后台保存
save: function () {
// var form = $("#myModalForm");
var isNew = $.trim($("#isNew").val());
var id = $.trim($("#hiddenId").val());
let isClick = true; //防止过快点击
if(isClick) {
if($("#excelFileParameter").val()===''){
swal({title: "提示", text:"请上传人员名单", type: "error",}
).then(function () {
});
return
}
if( $("#fileParameter").val()===''){
swal({title: "提示", text:"请上传企业防控承诺书", type: "error",}
).then(function () {
});
return
}
if( $("#fileApplication").val()===''){
swal({title: "提示", text:"请上传申请表", type: "error",}
).then(function () {
});
return
}
if( $("#filePlan").val()===''){
swal({title: "提示", text:"请上传防控方案", type: "error",}
).then(function () {
});
return
}
var formData = new FormData($('#myModalForm')[0]);
//create
var index;
if (isNew == 1) {
$.ajax({
type: "POST",
url: _CONTEXT_PATH + "mg/quickWay/save",
data: formData,
processData: false,
contentType: false,
beforeSend:function(){
index = layer.load(1, {
shade: [0.1,'#fff'] //0.1透明度的白色背景
});
},
error: function (errorRes) {
layer.close(index);
if (errorRes.status == 401) {
window.location.replace(_CONTEXT_PATH + errorRes.responseText);
} else {
swal({title: "oops !!", text: errorRes.responseText, type: "error"}, function () {
location.reload();
});
}
},
success: function (responseData) {
layer.close(index);
if(responseData.result){
swal({title: "创建成功", text:"", type: "success",}
).then(function () {
$("#myModal").modal("hide");
location.reload();
// $('#dataTable').dataTable().api().table().draw();
});
}else{
swal({title: "添加", text:responseData.message, type: "error",}
).then(function () {
location.reload();
});
}
}
});
}
setTimeout(function() {
isClick = true;
}, 1000);
}
}
ajax上传分两种,一个纯数据,一个包括文件,区别就在于,纯数据的
$.ajax({
type: method,
url: url,
data: form.serialize(),
包括文件的
$.ajax({
type: "POST",
url: _CONTEXT_PATH + "mg/quickWay/save",
data: formData,
processData: false,
contentType: false,
后台保存文件的数据库设计
文件上传以后,转换为base64数据,保存到数据库中,可以再定义一个id,关联其他功能,如我做的,一条记录对应多个文件,所以设置一个quickWayId,与记录对应
保存字符类型大对象存储,使用@Lob注解
MySQL支持大量的列类型,它可以被分为3类:数字类型、日期和时间类型以及字符串(字符)类型。Mysql针对字符类型大对象存储的解决方案是Lob对象。
@Lob 注解属性将被持久化为 Blog 或 Clob 类型
Clob(Character Large Ojects)类型是长字符串类型,具体的java.sql.Clob, Character[], char[] 和 java.lang.String 将被持久化为 Clob 类型。
Blob(Binary Large Objects)类型是字节类型,具体的java.sql.Blob, Byte[], byte[] 和 serializable type 将被持久化为 Blob 类型。
@Lob 持久化为Blob或者Clob类型,根据get方法的返回值不同,自动进行Clob和Blob的转换。
因为这两种类型的数据一般占用的内存空间比较大,所以通常使用延迟加载的方式,与@Basic标记同时使用,设置加载方式为FetchType.LAZY。
对应的entity
@Entity
@Table(name = "t_file_manage_m")
@JsonIgnoreProperties(ignoreUnknown = true)
public class FileManageEntity extends BaseEntity {
@Id
@TableGenerator(name = "FileManage_gen", table = "t_com_id_generator_r", pkColumnName = "seq_name", pkColumnValue = "FileManage_id", valueColumnName = "seq_value", allocationSize = Constants.SQE_ALLOCATION)
@GeneratedValue(strategy = GenerationType.TABLE, generator = "FileManage_gen")
private Long id;
/**
* 承诺书
*/
@Column(name = "file_content")
@Lob
private String fileContent;
/**
* 承诺书名称
*/
@Column(name = "file_name")
private String fileName;
/**
* 申请表
*/
@Column(name = "file_application")
@Lob
private String fileApplication;
/**
* 申请表名称
*/
@Column(name = "file_application_name")
private String fileApplicationName;
/**
* 防控方案
*/
@Column(name = "file_plan")
@Lob
private String filePlan;
/**
* 防控方案名称
*/
@Column(name = "file_plan_name")
private String filePlanName;
/**
* 附件名称
*/
@Column(name = "file_area")
@Lob
private String fileArea;
/**
* 附件名称
*/
@Column(name = "file_area_name")
private String fileAreaName;
/**
* 通道id
*/
@Column(name = "quick_way_id")
private Long quickWayId;
.....get set方法
如果之前创建过数据表,后来又加了@Lob字段,需要手动修改一下数据表的样式,或者删除表,运行代码重新生成
对应的controller
/**
* 存储
*
* @return
*/
@PostMapping(value = "/save")
@ResponseBody
public ResponseEntity add(
@RequestParam(value="fileParameter") MultipartFile file,
@RequestParam(value="excelFileParameter") MultipartFile excelFileParameter,
@RequestParam(value="fileApplication") MultipartFile fileApplication,
@RequestParam(value="filePlan") MultipartFile filePlan
) {
if(file==null){
return ResponseEntity.badRequest().body("请上传企业防控承诺书");
}
if(fileApplication==null){
return ResponseEntity.badRequest().body("请上传申请表");
}
if(filePlan==null){
return ResponseEntity.badRequest().body("请上传防控方案");
}
return ResponseEntity.ok(quickWayService.save( file, excelFileParameter,
fileApplication, filePlan));
}
从excel导入数据
做数据导入的功能,建议提供一个模板,让用户下载,按照模板进行填写,否则各种格式,程序没办法解析。数据导入思路:得到MultipartFile,然后判断是否为excel,然后逐行读取,进行数据判断,然后保存到数据库
/**
*
* 判断文件版本
*/
String fileName = excelFile.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
InputStream ins = null;
Workbook wb = null;
try {
ins = excelFile.getInputStream();
if (suffix.equals("xlsx")) {
wb = new XSSFWorkbook(ins);
} else {
wb = new HSSFWorkbook(ins);
}
} catch (IOException e) {
e.printStackTrace();
return ComServiceResVo.badRequest("导入失败");
}
/**
* 获取excel表单
*/
Sheet sheet = wb.getSheetAt(0);
/**
* line = 2 :从表的第三行开始获取记录
*
*/
String regex = "^[a-z0-9A-Z]+$";
if (null != sheet) {
for (int line = 2; line <= sheet.getLastRowNum(); line++) {
QuickWayItemEntity uploadItemEntity = new QuickWayItemEntity();
Row row = sheet.getRow(line);
if (null == row) {
continue;
}
boolean isEmpty = true;
for (int i = 1; i < 8; i++) {
if (null != row.getCell(i)) {
System.out.println("getCellType:" + row.getCell(i).getCellType());
if (row.getCell(i).getCellType() != Cell.CELL_TYPE_BLANK) {
isEmpty = false;
}
}
}
if (isEmpty) {
continue;
}
//申请单位
if (null != row.getCell(1)) {
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(1).getStringCellValue();
if (StringUtils.isEmpty(value)) {
return ComServiceResVo.badRequest("导入失败,申请单位不能为空,请检查第" + (line + 1) + "行");
} else {
if (value.length() > 128) {
return ComServiceResVo.badRequest("导入失败,申请单位过长,请检查第" + (line + 1) + "行");
}
uploadItemEntity.setCompany(value);
}
} else {
return ComServiceResVo.badRequest("导入失败,申请单位不能为空,请检查第" + (line + 1) + "行");
}
//姓名
if (null != row.getCell(2)) {
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(2).getStringCellValue();
if (StringUtils.isEmpty(value)) {
return ComServiceResVo.badRequest("导入失败,受邀人姓名不能为空,请检查第" + (line + 1) + "行");
} else {
if (value.length() > 64) {
return ComServiceResVo.badRequest("导入失败,受邀人姓名过长,请检查第" + (line + 1) + "行");
}
uploadItemEntity.setName(value);
}
} else {
return ComServiceResVo.badRequest("导入失败,受邀人姓名不能为空,请检查第" + (line + 1) + "行");
}
//国籍
if (null != row.getCell(3)) {
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(3).getStringCellValue();
if (StringUtils.isEmpty(value)) {
return ComServiceResVo.badRequest("导入失败,国籍不能为空,请检查第" + (line + 1) + "行");
} else {
if (value.length() > 32) {
return ComServiceResVo.badRequest("导入失败,国籍过长,请检查第" + (line + 1) + "行");
}
uploadItemEntity.setNationality(value);
}
} else {
return ComServiceResVo.badRequest("导入失败,国籍不能为空,请检查第" + (line + 1) + "行");
}
//身份证/护照号
Cell cIdCard = row.getCell(4);//身份证/护照号
if (null != cIdCard) {
cIdCard.setCellType(Cell.CELL_TYPE_STRING);
if (cIdCard.getStringCellValue().equals("")) {
return ComServiceResVo.badRequest("导入失败,身份证/护照号不能为空,请检查第" + (line + 1) + "行");
}
String idCard = cIdCard.getStringCellValue().trim();
if (idCard.length() > 18 || !idCard.matches(regex)) {
return ComServiceResVo.badRequest("导入失败,身份证/护照号格式不符,请检查第" + (line + 1) + "行");
}
uploadItemEntity.setIdCard(idCard);
} else {
return ComServiceResVo.badRequest("导入失败,身份证不能为空,请检查第" + (line + 1) + "行");
}
//人员类型
Cell personnelCategory = row.getCell(5);//人员类型
if (null != personnelCategory) {
cIdCard.setCellType(Cell.CELL_TYPE_STRING);
if (personnelCategory.getStringCellValue().equals("")) {
return ComServiceResVo.badRequest("导入失败,人员类型不能为空,请检查第" + (line + 1) + "行");
}
String personnelCategoryStr = personnelCategory.getStringCellValue().trim();
if (personnelCategoryStr.length() > 8) {
return ComServiceResVo.badRequest("导入失败,人员类型过长,请检查第" + (line + 1) + "行");
}
uploadItemEntity.setPersonnelCategory(personnelCategoryStr);
} else {
return ComServiceResVo.badRequest("导入失败,人员类型不能为空,请检查第" + (line + 1) + "行");
}
//职务
if (null != row.getCell(6)) {
row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(6).getStringCellValue();
if (StringUtils.isEmpty(value)) {
return ComServiceResVo.badRequest("导入失败,职务不能为空,请检查第" + (line + 1) + "行");
} else {
uploadItemEntity.setJob(value);
}
} else {
if ("员工".equals(uploadItemEntity.getPersonnelCategory())) {
return ComServiceResVo.badRequest("导入失败,职务不能为空,请检查第" + (line + 1) + "行");
} else {
uploadItemEntity.setJob("");
}
}
//航班号
if (null != row.getCell(7)) {
row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(7).getStringCellValue();
if (StringUtils.isEmpty(value)) {
return ComServiceResVo.badRequest("导入失败,航班号不能为空,请检查第" + (line + 1) + "行");
} else {
uploadItemEntity.setFlightNumber(value);
}
} else {
return ComServiceResVo.badRequest("导入失败,航班号不能为空,请检查第" + (line + 1) + "行");
}
Cell ctime = row.getCell(8);//拟定入境日期
if (null != ctime) {
logger.debug("ctime={}", ctime.getCellType());
String[] timeValues = getCellTimeValue(ctime);
if ("".equals(timeValues[0])) {
return ComServiceResVo.badRequest("excle内提交的拟定入境日期不符合要求" + ",请检查第" + (line + 1) + "行");
} else {
uploadItemEntity.setPlannedEntryDateLong(Long.parseLong(timeValues[0]));
uploadItemEntity.setPlannedEntryDate(timeValues[1]);
}
}
//口岸
if (null != row.getCell(9)) {
row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
String value = row.getCell(9).getStringCellValue();
if (StringUtils.isEmpty(value)) {
return ComServiceResVo.badRequest("导入失败,口岸不能为空,请检查第" + (line + 1) + "行");
} else {
uploadItemEntity.setEntryPort(value);
}
} else {
return ComServiceResVo.badRequest("导入失败,口岸不能为空,请检查第" + (line + 1) + "行");
}
//备注
Cell remark = row.getCell(10);
if (null != remark) {
remark.setCellType(Cell.CELL_TYPE_STRING);
String remarkString = remark.getStringCellValue().trim();
uploadItemEntity.setRemarks(remarkString);
} else {
uploadItemEntity.setRemarks("");
}
uploadItemEntities.add(uploadItemEntity);
}
QuickWayEntity dataUploadEntity = addDataUpload();
for (QuickWayItemEntity uploadItemEntity : uploadItemEntities) {
uploadItemEntity.setQuickWayId(dataUploadEntity.getId());
quickWayItemRepository.save(uploadItemEntity);
}
fileManageService.save(entity, dataUploadEntity.getId());
}
数据保存逻辑,请按照自己需求进行修改,我这边逻辑就是一条记录QuickWayEntity,对应多条导入数据QuickWayItemEntity,分别保存,两张表以QuickWayItemEntity中通过quickWayId保存QuickWayEntity的id,进行关联
导出数据
导出对应的controller
@RequestMapping(value = "/excel", method = RequestMethod.GET)
public ComServiceResVo writeExcel(HttpServletResponse response) {
response.setContentType("application/binary;charset=ISO8859_1");
try {
//获取选中的column
ServletOutputStream outputStream = response.getOutputStream();
String fileName = new String(("日报表").getBytes(), "ISO8859_1") +
DateUtil.fromInstantToString("MMddHHmm", Instant.ofEpochMilli(System.currentTimeMillis()));
response.setCharacterEncoding("utf-8");
// 组装附件名称和格式
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
isolatorService.writeExcel(DateUtil.fromInstantToString("MMddHHmm", Instant.ofEpochMilli(System.currentTimeMillis())),
outputStream);
} catch (IOException e) {
log.error(e.getMessage());
}
return null;
// return service.writeExcel();
}
对应的service
/**
* 写入excel
* @return
*/
public ComServiceResVo writeExcel(String fileName, ServletOutputStream outputStream) {
// 创建一个workbook 对应一个excel应用文件
XSSFWorkbook workBook = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = workBook.createSheet(fileName);
sheet.setDefaultColumnWidth((short) 30);
ExportUtil exportUtil = new ExportUtil(workBook, sheet);
XSSFCellStyle headStyle = exportUtil.getHeadStyle();
XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
// 构建表头
XSSFRow headRow = sheet.createRow(0);//第一行表头
XSSFCell cell = null;
cell = headRow.createCell(0);
cell.setCellStyle(headStyle);
cell.setCellValue("日期");
cell = headRow.createCell(1);
cell.setCellStyle(headStyle);
cell.setCellValue("地区");
cell = headRow.createCell(2);
cell.setCellStyle(headStyle);
cell.setCellValue("总数");
cell = headRow.createCell(3);
cell.setCellStyle(headStyle);
cell.setCellValue("床位总数");
cell = headRow.createCell(4);
cell.setCellStyle(headStyle);
cell.setCellValue("人员总数");
cell = headRow.createCell(5);
cell.setCellStyle(headStyle);
cell.setCellValue("保障人员总数");
cell = headRow.createCell(6);
cell.setCellStyle(headStyle);
cell.setCellValue("观察人数");
cell = headRow.createCell(7);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(8);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(9);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(10);
cell.setCellStyle(headStyle);
cell.setCellValue("异常人员数");
cell = headRow.createCell(11);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(12);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(13);
cell.setCellStyle(headStyle);
cell.setCellValue("");
headRow = sheet.createRow(1);//第二行表头
cell = headRow.createCell(0);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(1);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(2);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(3);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(4);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(5);
cell.setCellStyle(headStyle);
cell.setCellValue("");
cell = headRow.createCell(6);
cell.setCellStyle(headStyle);
cell.setCellValue("合计");
cell = headRow.createCell(7);
cell.setCellStyle(headStyle);
cell.setCellValue("接触者");
cell = headRow.createCell(8);
cell.setCellStyle(headStyle);
cell.setCellValue("人员");
cell = headRow.createCell(9);
cell.setCellStyle(headStyle);
cell.setCellValue("其他");
cell = headRow.createCell(10);
cell.setCellStyle(headStyle);
cell.setCellValue("合计");
cell = headRow.createCell(11);
cell.setCellStyle(headStyle);
cell.setCellValue("测试人员");
cell = headRow.createCell(12);
cell.setCellStyle(headStyle);
cell.setCellValue("测试人员数");
cell = headRow.createCell(13);
cell.setCellStyle(headStyle);
cell.setCellValue("其他");
//第1,2,3,4,5,6列,第一行第二行,合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,1,0,0));
sheet.addMergedRegion(new CellRangeAddress(0,1,1,1));
sheet.addMergedRegion(new CellRangeAddress(0,1,2,2));
sheet.addMergedRegion(new CellRangeAddress(0,1,3,3));
sheet.addMergedRegion(new CellRangeAddress(0,1,4,4));
sheet.addMergedRegion(new CellRangeAddress(0,1,5,5));
//第7,8,9,10列,第一行,合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,6,9));
//第11,12,13,14列,第一行,合并单元格
sheet.addMergedRegion(new CellRangeAddress(0,0,10,13));
// 构建表体数据
ArrayList<IsolatorEntity> datas = new ArrayList<>(excelDatas);
for (int j = 0; j < datas.size(); j++) {
IsolatorEntity order=datas.get(j);
XSSFRow bodyRow = sheet.createRow(j + 2);
cell = bodyRow.createCell(0);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getRegisterDate() == null ? "" : order.getRegisterDate());
cell = bodyRow.createCell(1);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getRegisterOrgan() == null ? "" : order.getRegisterOrgan());
cell = bodyRow.createCell(2);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getIsolatorPointNum());
cell = bodyRow.createCell(3);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getIsolatorBedNum());
cell = bodyRow.createCell(4);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getDoctorNum());
cell = bodyRow.createCell(5);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getWaiterNum());
cell = bodyRow.createCell(6);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getSurveyTotalNum());
cell = bodyRow.createCell(7);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getSurveyContactNum());
cell = bodyRow.createCell(8);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getSurveyFromDangNum());
cell = bodyRow.createCell(9);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getSurveyOtherNum());
cell = bodyRow.createCell(10);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getAnomalyTotalNum());
cell = bodyRow.createCell(11);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getAnomalyContactNum());
cell = bodyRow.createCell(12);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getAnomalyFromDangNum());
cell = bodyRow.createCell(13);
cell.setCellStyle(bodyStyle);
cell.setCellValue(order.getAnomalyOtherNum());
}
try {
workBook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
logger.error(e.getMessage());
return ComServiceResVo.badRequest("导出失败");
} finally {
try {
outputStream.close();
} catch (IOException e) {
logger.error(e.getMessage());
}
}
return ComServiceResVo.badRequest("导出成功");
}
js调用
window.location.href = _CONTEXT_PATH + "mg/test/download/excel";
上传文件
我这里是把上传的文件,转换为base64保存到数据库,当然也可以根据需要,保存到服务器
public String fileToBase64(MultipartFile file) throws Exception {
return Base64.getEncoder().encodeToString(file.getBytes());
}
private String[] getFileContentAndName(MultipartFile file){
if(null==file){
return new String[]{"",""};
}
String base64 = "";
try {
base64 = fileToBase64(file);
} catch (Exception e) {
e.printStackTrace();
}
String originalFilename = file.getOriginalFilename();
if (originalFilename != null && originalFilename.contains("/")) {
originalFilename = originalFilename.substring(originalFilename.lastIndexOf("/") + 1);
}
return new String[]{base64,originalFilename};
}
//保存到数据库操作
FileManageEntity entity = new FileManageEntity();
entity.setFileApplication(getFileContentAndName(fileApplication)[0]);
entity.setFileApplicationName(getFileContentAndName(fileApplication)[1]);
entity.setFilePlan(getFileContentAndName(filePlan)[0]);
entity.setFilePlanName(getFileContentAndName(filePlan)[1]);
entity.setFileContent(getFileContentAndName(file)[0]);
entity.setFileName(getFileContentAndName(file)[1]);
controller中的add方法,已经获取到了MultipartFile参数,保存到数据库,还是保存到服务器,可以根据自己需求处理
导出文件
下载文件,主要思路就是将文件以流的形式写入输出流中
对应的controller
@RequestMapping(value = "/download/{quickWayId}", method = RequestMethod.GET)
public ComServiceResVo downLoadFile(@PathVariable("quickWayId") Long quickWayId, HttpServletResponse response) {
response.setContentType("application/binary;charset=ISO8859_1");
response.setCharacterEncoding("utf-8");
quickWayService.exportFile(response,quickWayId);
return null;
}
对应的service
/**
* 下载文件
*
* @return
*/
public ComServiceResVo exportFile(HttpServletResponse response, Long quickWayId) {
FileManageEntity entity = fileManageService.findByQuickWayIdAndLogicDelFalse(quickWayId);
if (entity == null) {
return ComServiceResVo.badRequest("没有查询到文件");
}
if (StringUtils.isEmpty(entity.getFileContent())) {
return ComServiceResVo.badRequest("文件内容为空");
}
String base64 = entity.getFileContent();
String fileName = entity.getFileName();
String downLoadName = fileName;
try {
downLoadName = new String(fileName.getBytes(), "ISO8859_1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
;
response.setCharacterEncoding("utf-8");
// 组装附件名称和格式
response.setHeader("Content-disposition", "attachment; filename=" + downLoadName);
BufferedOutputStream bos = null;
OutputStream outputStream = null;
try {
byte[] bytes = Base64.getDecoder().decode(base64);
outputStream = response.getOutputStream();
bos = new BufferedOutputStream(outputStream);
bos.write(bytes);
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage());
return ComServiceResVo.badRequest("下载失败");
} finally {
if (bos != null) {
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
logger.error(e.getMessage());
}
}
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
logger.error(e.getMessage());
}
}
}
return ComServiceResVo.badRequest("下载成功");
}
我这里直接将数据库中保存的base64,转为 byte[] 写入流中。
多文件批量压缩下载
将多个文件,通过ZipOutputStream进行压缩,以流的形式把压缩文件下载下来
// 获取输出流
BufferedOutputStream bos = null;
try {
bos = new BufferedOutputStream(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
if (bos == null) {
return ComServiceResVo.badRequest("解析出错");
}
fileNames = inspectSame(fileNames);
try {
response.reset(); // 重点突出
// 不同类型的文件对应不同的MIME类型
response.setContentType("application/x-msdownload");
response.setCharacterEncoding("utf-8");
//给压缩文件命名
response.setHeader("Content-disposition", "attachment;filename="+quickWayEntity.getApplicationTime()
.replace(":","")
.replace("-","")
.replace(" ","")+ ".zip");
// ZipOutputStream类:完成文件或文件夹的压缩
ZipOutputStream out = new ZipOutputStream(bos);
for (int i = 0; i < fileNames.size(); i++) {
byte[] bytes = Base64.getDecoder().decode(fileContents.get(i));
out.putNextEntry(new ZipEntry(fileNames.get(i)));
out.write(bytes);
out.closeEntry();
}
out.close();
bos.close();
System.out.println("压缩完成.");
} catch (Exception e) {
e.printStackTrace();
return ComServiceResVo.badRequest("下载失败");
} finally {
try {
bos.close();
} catch (IOException e) {
e.printStackTrace();
logger.error(e.getMessage());
}
}
/**
* 检查文件名是否有重复,如果重复,重命名: 数字_文件名
*
* @param list
*/
public ArrayList<String> inspectSame(ArrayList<String> list) {
String[] indexArr;
Map<String, String> map = new HashMap();
for (int i = 0; i < list.size(); i++) {
String key = list.get(i);
String old = map.get(key);
if (old != null) {
map.put(key, old + "," + (i + 1));
} else {
map.put(key, "" + (i + 1));
}
}
Iterator<String> it = map.keySet().iterator();
int index = -1;
while (it.hasNext()) {
String key = it.next();
String value = map.get(key);
if (value.indexOf(",") != -1) {
System.out.println(key + " 重复,行: " + value);
indexArr = value.split(",");
for (int i = 0; i < indexArr.length; i++) {
index = Integer.parseInt(indexArr[i]) - 1;
list.set(index, (1 + i) + "_" + list.get(index));
}
}
}
return list;
}
还有一些其他方法,比如获取服务器文件,然后把文件以流的方式写入输出流
Java后台文件批量压缩下载
Java实现zip文件压缩与解压缩