Spring Boot学习笔记----POI（Excel导入导出）
业务:动态生成模板导出Excel,用户修改完再导入Excel.
Spring boot + bootstrap + poi
1.添加Dependence
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
前者用于引入HSSFWorkbook；后者用于引入XSSFWorkbook.
2.生成模板并导出Excel
//生成模板 function modleCreate(){ //清空数据 var oaId= $("#adjOAId").val(); var adjOrg= $("#adjOrg").val(); var adjDepart=$("#adjDepart").val(); var adjSubject=$("#adjSubject").val(); window.location.href="budgetOaController/exportTemplate?adjOrg="+ adjOrg +"&&adjDepart=" +adjDepart +"&&adjSubject="+adjSubject+"&&oaId="+oaId; }
@RequestMapping(value="/exportTemplate",method=RequestMethod.GET) public void exportTemplate(HttpServletResponse response,@RequestParam String adjOrg,@RequestParam String adjSubject, @RequestParam String adjDepart,@RequestParam String oaId) throws IOException{ // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个Excel表单,参数为sheet的名字 HSSFSheet sheet = workbook.createSheet("模板表"); //创建表头 setTitle(workbook, sheet); List<Map<String, Object>> oalist = budgetAdjustService.getOainform(oaId); //新增数据行，并且设置单元格数据 HSSFRow hssfRow = sheet.createRow(1); for(Map map :oalist) { hssfRow.createCell(0).setCellValue(map.get("adjustType")+""); hssfRow.createCell(1).setCellValue(map.get("applyDate")+""); hssfRow.createCell(2).setCellValue(map.get("processCode")+""); hssfRow.createCell(3).setCellValue(map.get("applyOrganization")+""); hssfRow.createCell(4).setCellValue(map.get("applyDepartment")+""); hssfRow.createCell(5).setCellValue(map.get("flag")+""); } hssfRow.createCell(6).setCellValue(adjOrg); hssfRow.createCell(7).setCellValue(adjDepart); hssfRow.createCell(8).setCellValue(adjSubject); /*hssfRow.createCell(1).setCellValue(budgetadjust.getApplyDate()); hssfRow.createCell(2).setCellValue(budgetadjust.getProcessCode()); hssfRow.createCell(3).setCellValue(budgetadjust.getApplyOrganization()); hssfRow.createCell(4).setCellValue(budgetadjust.getApplyDepartment()); hssfRow.createCell(5).setCellValue(budgetadjust.getFlag()); hssfRow.createCell(6).setCellValue(budgetadjust.getExportorganization()); hssfRow.createCell(7).setCellValue(budgetadjust.getExportdepartment()); hssfRow.createCell(8).setCellValue(budgetadjust.getExportsubject()); }*/ // SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd"); String fileName = "Template -" + new Date().getTime() + ".xls"; //清空response response.reset(); //设置response的Header response.addHeader("Content-Disposition", "attachment;filename="+ fileName); OutputStream os = new BufferedOutputStream(response.getOutputStream()); response.setContentType("application/vnd.ms-excel;charset=gb2312"); //将excel写入到输出流中 workbook.write(os); os.flush(); os.close(); } // 创建表头 private void setTitle(HSSFWorkbook workbook, HSSFSheet sheet) { HSSFRow row = sheet.createRow(0); // 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度 sheet.setColumnWidth(8, 60 * 256); // 设置为居中加粗 HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBold(true); style.setFont(font); //导出的Excel头部 String[] headers = { "调整类型", "申请日期", "OA流程编号", "申请组织", "申请部门", "是否涉及人力成本", "调出组织", "调出部门", "调出科目", "调出月份", "调出金额", "查询费控系统", "调入组织", "调入部门", "调入科目", "调入月份", "调入金额", "调整原因" }; // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 16); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); cell.setCellStyle(style); } }
2.1 导出结果
3.导入Excel
<div class="row">
<div class="col-md-12">
<section class="panel">
<header class="panel-heading">
OA预算调整模板导入
</header>
<div class="modal-body">
<div class="row">
<div class="col-lg-12">
<form id="defaultForm" method="" class="form-horizontal recoveryNodeForm" action="">
<div class="col-lg-12">
<div class="form-group">
<label class="col-lg-3 control-label">导入文件</label>
<div class="col-lg-6">
<input type="file" class="form-control" style="height:36px;" name="uploadFile" id="uploadFile"/>
</div>
<button type="button" class="btn btn-primary" id="uploadExcel">上传</button>
</div>
</div>
<input type="hidden" name="pkId" value="" />
</form>
</div>
</div>
<div>
<span><b>导入结果反馈</b></span>
<ul id="exportResult">
</ul>
</div>
</div>
</section>
</div>
</div>
<!-- 工具JS由开发人员编写 -->
<script th:inline="javascript">
/*<![CDATA[*/
$(function(){
$(".recoveryNodeForm").bootstrapValidator({
message: 'This value is not valid',
live: 'submitted',
fields: {/*验证*/
uploadFile: {
message: '导入文件无效',
validators: {
notEmpty: {/*非空提示*/
message: '导入文件不能为空'
},
regexp: {
regexp: /\.xl(s[xmb]|t[xm]|am|s)$/,
//regexp: /.xls$/,
// extension: 'zip,rar,doc,docx,pdf',
message: '导入文件类型必须是excel'
}
/* uploadFile: {
extension: 'zip,rar,doc,docx,pdf',
// type:'zip,rar,doc,docx,pdf',
maxSize: 1024*100,
minSize: 1024,
message: '仅支持大小在1M~5M之间,类型是zip,rar,doc,docx,pdf文件!'
} */
}
}
}
})
});
$("#uploadExcel").on("click","",function () {
$(".recoveryNodeForm").data("bootstrapValidator").validate();
var flag = $(".recoveryNodeForm").data("bootstrapValidator").isValid();
alert(flag+"===========flag===========");
if(!flag){
//未通过验证
return false;
}
var fileObj = document.getElementById("uploadFile").files[0];
var formFile = new FormData();
formFile.append("file", fileObj);
var data = formFile;
$.ajax({
url: "budgetOaController/upload",
data: data,
type: "Post",
dataType: "json",
cache: false,//上传文件无需缓存
processData: false,//用于对data参数进行序列化处理 这里必须false
contentType: false, //必须
success: function (result) {
console.log(JSON.stringify(result))
var htmlstr = '';
if(result.ajaxResultJson.success==false){
htmlstr = '<li>上传失败</li>';
} else {
htmlstr = '<li>上传成功</li>';
}
$('#exportResult').html(htmlstr);
},
error: function(XMLHttpRequest, textStatus, errorThrown){
layer.msg("系统错误",{icon: 2});
}
});
});
/*]]>*/
</script>
// 导入Excel
@RequestMapping(value = "upload", method = RequestMethod.POST)
public AjaxResultJson upload(MultipartFile file) {
AjaxResultJson result = new AjaxResultJson();
if (file == null) {
result.setMsg("file不能为空");
result.setObj("Excel导入失败");
return result;
}
List<Budgetadjust> list = new ArrayList<>();
String fileName = file.getOriginalFilename(); //获取文件名
try {
//解决You need to call a different part of POI to process this data (eg XSSF instead of HSSF)问题
// Workbook workbook=WorkbookFactory.create(file.getInputStream());
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
// 有多少个sheet
int sheets = workbook.getNumberOfSheets();
for (int i = 0; i < sheets; i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
// 获取多少行
int rows = sheet.getPhysicalNumberOfRows();
Budgetadjust budgetadjust = null;
// 遍历每一行，注意：第 0 行为标题
for (int j = 1; j < rows; j++) {
budgetadjust = new Budgetadjust();
// 获得第 j 行
HSSFRow row = sheet.getRow(j);
budgetadjust.setAdjustType(row.getCell(0).toString());// 调整类型
budgetadjust.setApplyDate(row.getCell(1).toString());// 申请日期
budgetadjust.setProcessCode(row.getCell(2).toString());// OA流程编号
budgetadjust.setApplyOrganization(row.getCell(3).toString());// 申请组织
budgetadjust.setApplyDepartment(row.getCell(4).toString());// 申请部门
budgetadjust.setFlag(row.getCell(5).toString());// 是否涉及人力成本
budgetadjust.setExportorganization(row.getCell(6).toString());// 调出组织
budgetadjust.setExportdepartment(row.getCell(7).toString());// 调出部门
budgetadjust.setExportsubject(row.getCell(8).toString());// 调出科目
budgetadjust.setExportmonth(row.getCell(9).toString());// 调出月份
budgetadjust.setExportmoney(row.getCell(10).toString());// 调出金额
budgetadjust.setCostControl(row.getCell(11).toString());// 查询费控系统
budgetadjust.setImportorganization(row.getCell(12).toString());// 调入组织
budgetadjust.setImportdepartment(row.getCell(13).toString());// 调入部门
budgetadjust.setImportsubject(row.getCell(14).toString());// 调入科目
budgetadjust.setImportmonth(row.getCell(15).toString());// 调入月份
budgetadjust.setImportmoney(row.getCell(16).toString());// 调入金额
budgetadjust.setAdjustreason(row.getCell(17).toString());// 调整原因
list.add(budgetadjust);
}
}
budgetAdjustService.saveOABudget(list);
} catch (IOException e) {
result.setSuccess(false);
result.setMsg(e.getMessage());
}
result.setSuccess(true);
result.setMsg("保存成功"); // 可以传文件名给页面 String fileName = file.getOriginalFilename(); //获取文件名
result.setObj(list);
return result;
}
注意：
1.要想让<input type="file">标签 能够上传多个文件，只需要在<input type="file">里添加 multiple或 multiple="multiple"属性。
2. Execl 导出锁定列和隐藏单元的值
1.Execl列的锁定 HSSFCellStyle style = workbook.createCellStyle(); style.setLocked(true);//设置列的锁定状态为锁定
2.隐藏单元格的值 hssfRow.createCell(9).setCellValue("12月"); // 调出月
hssfRow.createCell(20).setCellValue("A1"); // 调出期间A1
sheet.setColumnHidden((short)20, true); //隐藏第单元格20的值A1
3.设置表头字体样式背景颜色
private void setTitle(HSSFWorkbook workbook, HSSFSheet sheet) { HSSFRow row = sheet.createRow(0); // 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度 sheet.setColumnWidth(8, 60 * 256); // 设置为居中加粗 HSSFCellStyle style = workbook.createCellStyle(); HSSFCellStyle style2 = workbook.createCellStyle(); style.setLocked(true);//设置列的锁定状态 style2.setLocked(true); HSSFFont font = workbook.createFont(); HSSFFont font2 = workbook.createFont(); font.setBold(true);//设置字体锁定状态 style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER);//水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 // 导出的Excel头部 String[] headers = { "调整类型", "申请日期", "OA流程编号", "申请组织", "申请部门", "是否涉及人力成本", "调出组织", "调出部门", "调出科目", "调出月份", "调出金额", "查询费控系统", "调入组织", "调入部门", "调入科目", "调入月份", "调入金额", "调整原因" }; // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 16); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); if(i==10 || i>11) { font2.setBold(true); font2.setColor(HSSFColor.RED.index); //颜色 style2.setFont(font2); style2.setAlignment(HorizontalAlignment.CENTER);//水平居中 style2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中 style2.setFillPattern(FillPatternType.SOLID_FOREGROUND); style2.setFillForegroundColor(IndexedColors.YELLOW.index); row.getCell(i).setCellStyle(style2); }else { cell.setCellStyle(style); } } }
效果图:
----------------------------------------------------
相关链接:https://blog.csdn.net/daihuimaozideren/article/details/78777656
https://www.cnblogs.com/zhuwenxia/p/9443742.html
文件上传链接: https://blog.csdn.net/chenxueshanBlog/article/details/78894838
POI 3.17版本生成excel的一些样式设置:
https://blog.csdn.net/m0_37353769/article/details/81872152
https://blog.csdn.net/phil_jing/article/details/78307819
获取单元格类型getCellType
POI:https://blog.csdn.net/huasxiaopeng/article/details/42641809
https://blog.csdn.net/jiankang66/article/details/89040742
注意POI4.0.0版本区别
posted on 2018-10-08 18:13 Steven5007 阅读(5464) 评论(0) 编辑 收藏 举报
