本文介绍两种Excel导入导出的方法。一种是通过自定义抽象类实现上传和处理逻辑，另一种是利用Apache POI库实现数据导出。文章详细展示了代码实现过程。
一、方式一
先导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
1、抽象类，把公共的方法抽出来
public abstract class AbstractExcelImportController {
public AbstractExcelImportController() {
}
public abstract SheetConfig getSheetConfig();
@RequestMapping({"showImportExcelDlg.do"})
public String showImportExcelDlg(Model model) {
Map<String, Object> result = new HashMap();
result.put("urlprofix", this.getUrlProfix());
model.addAttribute(UIConst.UIPAGEMODEL, result);
return "platform/excelimport/excelimport.ui";
}
public abstract String getUrlProfix();
@RequestMapping({"importExcel.do"})
public String importExcel(MultipartFile excelimport_importfile, String overrideFlat, Model model, HttpServletRequest request) throws Exception {
String errorMessage = null;
if (excelimport_importfile != null && !excelimport_importfile.isEmpty() && excelimport_importfile.getSize() != 0L) {
if (!excelimport_importfile.getOriginalFilename().endsWith("xls")) {
errorMessage = "上传文件必须是以xls为后缀";
} else {
ExcelImporter importer = ExcelImporter.newInstance(this.getSheetConfig());
try {
List<Map<String, Object>> datas = importer.readExcel(excelimport_importfile.getInputStream());
this.handleExcelData(datas, overrideFlat);
} catch (BusinessException var8) {
var8.printStackTrace();
errorMessage = var8.getMessage();
} catch (Exception var9) {
errorMessage = var9.getMessage();
}
}
} else {
errorMessage = "上传文件不能为空";
}
Map<String, Object> result = new HashMap();
if (errorMessage != null) {
result.put("success", false);
result.put("errorMessage", errorMessage);
} else {
result.put("success", true);
}
model.addAttribute("result", CustomObjectMapper.getInstance().writeValueAsString(result));
return "platform/excelimport/importresult";
}
protected abstract void handleExcelData(List<Map<String, Object>> var1, String var2) throws BusinessException;
@RequestMapping({"downloadExcelTemplate.do"})
public void downloadExcelTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception {
ExcelExporter exporter = ExcelExporter.newInstance(this.getSheetConfig());
OutputStream out = response.getOutputStream();
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(this.getExcelTemplateFileName().getBytes("gb2312"), "ISO8859-1"));
List<Map<String, Object>> datas = new ArrayList();
exporter.writeExcel(datas, out);
}
public abstract String getExcelTemplateFileName();
}
2、每一个需要上传excel的web层(Controller)继承上边的接口，实现自己的上传业务逻辑
public class DeptController extends AbstractExcelImportController {
/**
* add by sgw 20190325
*excel模板
* @return 返回excel模板
*/
@Override
public SheetConfig getSheetConfig() {
SheetConfig sheetConfig = new SheetConfig();
List<SheetConfig.ColumnDef> columnDefList = new ArrayList<SheetConfig.ColumnDef>();
SheetConfig.ColumnDef columnDef = null;
columnDef = new SheetConfig.ColumnDef();
columnDef.setTitle("部门编码");
columnDef.setName("fdeptCode");
columnDef.setComment("必填项!");
columnDef.setWidth(30);
columnDef.setColumnType(SheetConfig.ColumnDef.ColumnType.CODE);
columnDefList.add(columnDef);
columnDef = new SheetConfig.ColumnDef();
columnDef.setTitle("部门名称");
columnDef.setName("fdeptName");
columnDef.setComment("必填项!");
columnDef.setWidth(30);
columnDef.setColumnType(SheetConfig.ColumnDef.ColumnType.STRING);
columnDefList.add(columnDef);
columnDef = new SheetConfig.ColumnDef();
columnDef.setTitle("联系人");
columnDef.setName("flinkMan");
columnDef.setWidth(30);
columnDef.setColumnType(SheetConfig.ColumnDef.ColumnType.STRING);
columnDefList.add(columnDef);
columnDef = new SheetConfig.ColumnDef();
columnDef.setTitle("联系电话");
columnDef.setName("flinkTel");
columnDef.setWidth(50);
columnDef.setColumnType(SheetConfig.ColumnDef.ColumnType.STRING);
columnDefList.add(columnDef);
columnDef = new SheetConfig.ColumnDef();
columnDef.setTitle("联系地址");
columnDef.setName("faddr");
columnDef.setWidth(50);
columnDef.setColumnType(SheetConfig.ColumnDef.ColumnType.STRING);
columnDefList.add(columnDef);
columnDef = new SheetConfig.ColumnDef();
columnDef.setTitle("是否启用");
columnDef.setName("fisEnable");
columnDef.setComment("必填项!");
columnDef.setWidth(20);
columnDef.setColumnType(SheetConfig.ColumnDef.ColumnType.STRING);
columnDefList.add(columnDef);
sheetConfig.setColumnDefList(columnDefList);
return sheetConfig;
}
/**
* add by sgw 20190325
* 告诉前端公用的excelImport.js，处理上传excel的controller路径
* @return 处理excel的java类
*/
@Override
public String getUrlProfix() {
return "basicinfo/standard/dept";
}
/**
* add by sgw 20190325
* 批量上传部门excel
* @param datas excel数据
* @param overrideFlat 是否更新(覆盖)导入 "true":是
* @throws BusinessException
*/
@Override
protected void handleExcelData(List<Map<String, Object>> datas, String overrideFlat) throws BusinessException {
User user = (User) AppContext.getContext().getAttribute(
AppContext.SESSION, "user");
//1、更新(覆盖)导入：已存在部门编码时，将该部门编码下的部门信息进行更新(即无法更新部门编码，只能更新已有部门编码下的其他信息)
if (overrideFlat != null && "true".equals(overrideFlat)) {
//1.1、校验excel数据是否有误；如果传过来的部门编码在数据库里已经存在的了，则删除数据库里对应的数据
boolean isCorrectDates = this.deptService.checkExcelDates(datas, overrideFlat, user,"update");
//1.2、将新的部门数据插入到数据库里
if (isCorrectDates) {
//2.2、数据无误，将excel的数据循环导入到数据库
for (Map<String, Object> map : datas) {
Dept dept = new Dept();
dept.setFdeptCode(map.get("fdeptCode").toString());
dept.setFdeptName(map.get("fdeptName").toString());
if (map.get("flinkMan") != null) {
dept.setFlinkMan(map.get("flinkMan").toString());
}
if (map.get("flinkTel") != null) {
dept.setFlinkTel(map.get("flinkTel").toString());
}
if (map.get("faddr") != null) {
dept.setFaddr(map.get("faddr").toString());
}
if (map.get("fisEnable") != null) {
if ("是".equals(map.get("fisEnable").toString())) {
dept.setFisEnable(1);
} else {
dept.setFisEnable(0);
}
}
this.deptService.doInsert(dept, user.getId());
}
}
}
//2、新增导入：已存在部门编码时，强制要求修改部门编码后再进行上传
else {
//2.1、校验excel数据是否有误
boolean isCorrectDates = this.deptService.checkExcelDates(datas, overrideFlat, user,"add");
if (isCorrectDates) {
//2.2、数据无误，将excel的数据循环导入到数据库
for (Map<String, Object> map : datas) {
Dept dept = new Dept();
dept.setFdeptCode(map.get("fdeptCode").toString());
dept.setFdeptName(map.get("fdeptName").toString());
if (map.get("flinkMan") != null) {
dept.setFlinkMan(map.get("flinkMan").toString());
}
if (map.get("flinkTel") != null) {
dept.setFlinkTel(map.get("flinkTel").toString());
}
if (map.get("faddr") != null) {
dept.setFaddr(map.get("faddr").toString());
}
if (map.get("fisEnable") != null) {
if ("是".equals(map.get("fisEnable").toString())) {
dept.setFisEnable(1);
} else {
dept.setFisEnable(0);
}
}
this.deptService.doInsert(dept, user.getId());
}
}
}
}
/**
* add by sgw 20190325
* @return excel模板的文件名
*/
@Override
public String getExcelTemplateFileName() {
return "主管部门信息.xls";
}
}
二、方式二
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10.1</version>
</dependency>
mapper.xml:
<select id="exportUser" resultType="java.util.Map">
select user_id as userId, username, email, mobile, create_time as createTime from sys_user
</select>
接口：
List<Map<String, Object>> exportUser();
service：
@Override
public Workbook exportUser() {
//1，创建了一个空的excel文件
Workbook workbook = new HSSFWorkbook();
//2,填充数据：创建sheet页
Sheet sheet = workbook.createSheet("XXX公司的员工信息");
//标题数组
String titles[] ={"用户id","用户名","邮箱","电话","创建时间"};
String colums[] ={"userId", "username", "email", "mobile", "createTime"};
List<Map<String, Object>> maps = sysUserMapper.exportUser();
//创建行
Row rowTile = sheet.createRow(0);
//标题行
for (int i = 0; i <titles.length ; i++) {
Cell cell = rowTile.createCell(i);
cell.setCellValue(titles[i]);
}
//遍历数据，填充到单元格
for (int i = 0; i <maps.size() ; i++) {
//一条记录应该创建一个Row对象 这里从第二行开始所以+1
//这个是空的，需要填充数据
Row row = sheet.createRow(i+1);
//填充单元格
for (int j = 0; j < titles.length; j++) {
Cell cell = row.createCell(j);
//获取用户id的值
//这里获取的值可以是"userId"..
Map<String, Object> rowValue = maps.get(i);
//循环动态设置多个字段的值
Object o = rowValue.get(colums[j]);
//这里也就是为什么查询数据库使用map封装的原因。
cell.setCellValue(o+"");
}
}
return workbook;
}
controller：
/**
* 导出用户的excel数据
* @param response
*/
@RequestMapping("/sys/user/export")
public void exportUser(HttpServletResponse response){
Workbook workbook = sysUserService.exportUser();
try {
//设置响应头 所有文件都支持
response.setContentType("application/octet-stream");
String fileName = "员工信息.xls";
fileName = URLEncoder.encode(fileName,"utf-8");
response.setHeader("content-disposition","attachment;filename="+fileName);
//文件下载
workbook.write(response.getOutputStream());
} catch (IOException e) {
e.printStackTrace();
}
}
扫一扫
1394
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
