正文
Springboot Excle导入导出
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
Springboot Excle导入导出
导入操作：Excle批量导入
导出操作：下载模版
开发笔记
pom.xml
<!-- Excle相关jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.11</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
工具类
ExcleUtils：
package com.wechat.cwbt.util; import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile; /**
*
*
* @Package: com.wechat.cwbt.util
* @ClassName: ExcleUtils
* @Description:Excle相关工具
* @author: zk
* @date: 2019年8月30日 下午4:29:22
*/
public final class ExcleUtils {
/**
*
* @description Excle导入
* @author zk
* @date 2019年8月30日
* @param file
* @return 返回数据对象格式为： 表头->数据
*/
public static Map<String, Object> getDataList(MultipartFile file){
Map<String, Object> result = new HashMap<String, Object>();
// System.out.println(file.getName()+"\t"+file.getOriginalFilename());
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>(); //判断Excel文件是否有内容
boolean notNull=false;
String filename=file.getOriginalFilename();
if (!filename.matches("^.+\\.(?i)(xls)$") && !filename.matches("^.+\\.(?i)(xlsx)$")) {
result.put("msg","上传文件格式不正确！");
result.put("SUCCESS", false);
return result;
}
//判断Excel文件的版本
boolean isExcel2003=true;
if (filename.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
Workbook wb = null;
try {
InputStream fis=file.getInputStream();
if (isExcel2003) {
wb = new HSSFWorkbook(fis);
} else {
wb = new XSSFWorkbook(fis);
}
} catch (Exception e) {
e.printStackTrace();
} // 总共有多少张sheet表
int sheetnum = wb.getNumberOfSheets();
//判断有无数据
if(sheetnum == 1) {
Sheet sheet = wb.getSheetAt(0);
// 第一行数据数据
int rowNum = sheet.getPhysicalNumberOfRows();
if(rowNum==1){
result.put("msg","无数据");
result.put("SUCCESS", false);
return result;
}
}
//获取数据
for (int i = 0; i < sheetnum; i++) {
Sheet sheet = wb.getSheetAt(i);
// 表头数据
Row namerow = sheet.getRow(0); // 第一行数据
Row headrow = sheet.getRow(1); if (null != headrow) { // 总行数
int rowNum = sheet.getPhysicalNumberOfRows();
// int rowNum = sheet.getLastRowNum();
// System.out.println(file.getName() + "共：" + rowNum + " 行！"); // 总列数
int colNum = headrow.getPhysicalNumberOfCells(); // 判断工作表是否为空
if (rowNum == 0) {
continue;
} // 循环行
for (int j = 1; j <= rowNum; j++) {
Row row = sheet.getRow(j);
if (null != row) { Map<String, Object> rowmap = new HashMap<String, Object>();
for(int h=0;h<colNum;h++) {
String str1 = namerow.getCell(h).getStringCellValue();
row.getCell(h).setCellType(Cell.CELL_TYPE_STRING);//设置Cell的类型,按照String取出数据
String str2 = row.getCell(h).getStringCellValue();
rowmap.put(str1,str2);
}
dataList.add(rowmap); //continue;
}
}
}
}
result.put("data", dataList);
result.put("SUCCESS", true);
return result;
} /**
*
* @description Excle导出
* @author zk
* @date 2019年8月30日
* @param filename 文件名称
* @param namearr 表头
* @param datalist 数据
* @return
*/
public static Workbook exportDailyBill(String filename,String[] namearr,List<Map<String, Object>> datalist){
// 创建excel工作簿
Workbook workbook = new XSSFWorkbook();
// 创建第一个sheet（页），并命名
Sheet sheet = workbook.createSheet(filename);
//设置首行标题标题
Row row = sheet.createRow(0);
for(int i=0;i<namearr.length;i++) {
row.createCell(i).setCellValue(namearr[i]);
}
if(datalist!=null) {
//新增数据行，并且设置单元格数据
int rowNum = 1;
for(Map<String, Object> data : datalist)
{
row = sheet.createRow(rowNum);
for(int i=0;i<namearr.length;i++) {
row.createCell(i).setCellValue((String)data.get(namearr[i]));
}
rowNum++;
}
}
return workbook;
}
}
调用：
controller:
/**
*
* @description 下载模版
* @author zk
* @date 2019年9月9日
* @param request
* @param response
* @return
* @throws Exception
*/
@RequestMapping("/downloadExcle")
public void downloadExcle(HttpServletRequest request, HttpServletResponse response)throws Exception{
String fileName = "按键录入表";//文件名
String[] namearr = {"按键类型","按键名称","按键编码"};//标题
List<Map<String, Object>> datalist = null;//数据
Workbook workbook = ExcleUtils.exportDailyBill(fileName, namearr, datalist);
String name = new String(fileName.getBytes("utf-8"), "ISO-8859-1");//处理文件名称
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
OutputStream out = response.getOutputStream();
workbook.write(response.getOutputStream());
out.flush();
out.close();
} /**
*
* @description 批量导入Excle
* @author 左坤
* @date 2019年9月10日
* @param file
* @param request
* @param response
* @return
* @throws Exception
*/
@Transactional
@RequestMapping("/importExcle")
@ResponseBody
public Map<String, Object> importExcle(MultipartFile file,HttpServletRequest request, HttpServletResponse response){
Map<String, Object> result = new HashMap<String, Object>();
Map<String, Object> resmap = ExcleUtils.getDataList(file);
if((boolean)resmap.get("SUCCESS")) {//有数据
List<Map<String, Object>> list = (List<Map<String, Object>>) resmap.get("data");
List<Keyboard> keyboardlist = new ArrayList<Keyboard>();
if(list!=null) {
for(int i=0;i<list.size();i++) {
Map<String, Object> mapstr = list.get(i);
Keyboard keyboard = new Keyboard();
keyboard.setName((String)mapstr.get("按键名称"));
keyboard.setKeycode(Integer.parseInt((String)mapstr.get("按键编码")));
keyboard.setTypeid((String)mapstr.get("按键类型"));
keyboard.setCreatetime(new Date());
keyboard.setUpdatetime(new Date());
keyboard.setIsshow("0");
keyboard.setIsdel("0");
keyboardlist.add(keyboard);
}
int count = keyboardService.insertlist(keyboardlist);//成功条数
if(count<keyboardlist.size()) {//成功条数小于插入条数
TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();//手动回滚
result.put("SUCCESS", false);
result.put("msg", "Excle第"+(count+2)+"行存在问题");
return result;
}
result.put("SUCCESS", true);
result.put("msg", "操作成功");
}
}else {//无数据
result.put("SUCCESS", false);
result.put("msg", "Excle无数据");
}
return result;
}
结束
