一、导出
1、后端
1.1、导入依赖
Maven存储库
<!-- excel生成-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>1.2、工具类的实现
代码：
public class POIUtils {
public static ResponseEntity<byte[]> employee2Excel(List<Employee> list) {
//1、创建一个excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
//2、创建文档摘要
workbook.createInformationProperties();
//3、获取并配置文档摘要信息
DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
//文档类别
docInfo.setCategory("员工信息");
//设置管理员
docInfo.setManager("Tony");
//设置公司信息
docInfo.setCompany("mind-warning.top");
//4、获取文档摘要信息
SummaryInformation summaryInfo = workbook.getSummaryInformation();
//设置文档标题
summaryInfo.setTitle("员工信息表");
//设置作者
summaryInfo.setAuthor("皮蛋布丁");
//设置备注信息
summaryInfo.setComments("本文档由皮蛋布丁提供");
//5、创建样式
//创建标题行的样式
HSSFCellStyle headerStyle = workbook.createCellStyle();
//背景颜色
headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
//填充模式
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//创建日期格式
HSSFCellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
//创建sheet
HSSFSheet sheet = workbook.createSheet("员工信息1");
//region 设置列宽（几列，5个字符）
sheet.setColumnWidth(0,5*256);
sheet.setColumnWidth(1,12*256);
sheet.setColumnWidth(2,10*256);
sheet.setColumnWidth(3,5*256);
sheet.setColumnWidth(4,12*256);
sheet.setColumnWidth(5,20*256);
// endregion
//region 创建所有标题行
//6、创建标题行
HSSFRow row0 = sheet.createRow(0);
//创建列
HSSFCell cell0 = row0.createCell(0);
//设置列名
cell0.setCellValue("编号");
//设置背景颜色
cell0.setCellStyle(headerStyle);
HSSFCell cell1 = row0.createCell(1);
cell1.setCellStyle(headerStyle);
cell1.setCellValue("姓名");
HSSFCell cell2 = row0.createCell(2);
cell2.setCellStyle(headerStyle);
cell2.setCellValue("工号");
HSSFCell cell3 = row0.createCell(3);
cell3.setCellStyle(headerStyle);
cell3.setCellValue("性别");
HSSFCell cell4 = row0.createCell(4);
cell4.setCellStyle(headerStyle);
cell4.setCellValue("出生日期");
HSSFCell cell5 = row0.createCell(5);
cell5.setCellStyle(headerStyle);
cell5.setCellValue("身份证号码");
//endregion
//遍历数据
for (int i = 0; i < list.size(); i++) {
Employee employee = list.get(i);
//创建行(去除表头)
HSSFRow row = sheet.createRow(i + 1);
//region 创建列
row.createCell(0).setCellValue(employee.getId());
row.createCell(1).setCellValue(employee.getName());
row.createCell(2).setCellValue(employee.getWorkID());
row.createCell(3).setCellValue(employee.getGender());
HSSFCell c4 = row.createCell(4);
c4.setCellStyle(dateCellStyle);
c4.setCellValue(employee.getBirthday());
row.createCell(5).setCellValue(employee.getIdCard());
//endregion
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
HttpHeaders header = new HttpHeaders();
try {
header.setContentDispositionFormData("attachment",new String("员工表.xls".getBytes("UTF-8"),"ISO-8859-1"));
header.setContentType(MediaType.APPLICATION_OCTET_STREAM);
workbook.write(baos);
} catch (IOException e) {
e.printStackTrace();
}
return new ResponseEntity<byte[]>(baos.toByteArray(),header, HttpStatus.CREATED);
}
}1.3、接口的实现
/**
* @Description: exportData 导出员工数据excel
* @Param: []
* @return: org.springframework.http.ResponseEntity<byte[]>
* @Author: 皮蛋布丁
* @Date: 2021/6/17 22:33
*/
@GetMapping("/export")
public ResponseEntity<byte[]> exportData() {
//获取员工list
List<Employee> list = (List<Employee>) employeeService.getEmployeeByPage(null,null,null).getData();
return POIUtils.employee2Excel(list);
}2、前端
代码：
<el-button type="success" @click="exportData" icon="el-icon-download">导出数据</el-button>
<script>
export default {
methods: {
exportData() {
window.open('/employee/basic/export',"_parent");
},
}
}
</script>二、导入
1、后端
1.1、工具类实现
代码：
/**
* @Description: employee2Employ excel解析成员工集合
* @Param: [file, allNations, allPoliticsstatus, allDepartments, allPositions, allJobLevels]
* @return: java.util.List<com.tony.mindhr.model.Employee>
* @Author: 皮蛋布丁
* @Date: 2021/6/19 14:50
*/
public static List<Employee> employee2Employ(MultipartFile file, List<Nation> allNations, List<Politicsstatus> allPoliticsstatus, List<Department> allDepartments, List<Position> allPositions, List<JobLevel> allJobLevels) {
List<Employee> list = new ArrayList<>();
try {
//1、创建一个workbook对象
HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
//2、获取workbook中表单的数量
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
//3、获取表单
HSSFSheet sheet = workbook.getSheetAt(i);
//4、获取表单中的行数
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
for (int j = 0; j < physicalNumberOfRows; j++) {
if (j == 0) {
continue; //跳过标题行
}
//5、获取行
HSSFRow row = sheet.getRow(j);
if (row == null) {
continue; //防止数据中间有空行
}
//6、获取列
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
Employee employee = new Employee();
for (int k = 0; k < physicalNumberOfCells; k++) {
HSSFCell cell = row.getCell(k);
switch (cell.getCellType()) {
case STRING:
String cellValue = cell.getStringCellValue();
//根据列数决定如何处理
switch (k) {
case 1:
employee.setName(cellValue);
break;
case 2:
employee.setWorkID(cellValue);
break;
case 3:
employee.setGender(cellValue);
break;
case 5:
employee.setIdCard(cellValue);
break;
case 6:
employee.setWedlock(cellValue);
break;
case 7:
//无需遍历
//籍贯在数据库中存的是id，在excel是name
//只需判断name一致就能获取到下标(重写name方法以及无参、有参构造方法)
int nationIndex = allNations.indexOf(new Nation(cellValue));
employee.setNationId(allNations.get(nationIndex).getId());
break;
}
break;
default: {
//类型不是String类型的处理
switch (k) {
case 4:
employee.setBirthday(cell.getDateCellValue());
break;
}
}
break;
}
}
list.add(employee);
}
}
} catch (IOException e) {
e.printStackTrace();
}
return list;
}2.1、接口的实现
代码：
@PostMapping("/import")
public RespBean importData(MultipartFile file) throws IOException {
//解析excel数据为list
List<Employee> list = POIUtils.employee2Employ(file,nationService.getAllNations(),
politicsstatusService.getAllPoliticsstatus(),departmentService.getAllDepartmentsWithOutChildren(),
positionService.getAllPositions(),
jobLevelService.getAllJobLevels());
//插入数据库
if (employeeService.addEmps(list) == list.size()) {
return RespBean.ok("上传成功！");
}
return RespBean.error("上传失败！");
}2、前端
前端使用element组件
<el-upload
:show-file-list="false"
:before-upload="beforeUpload"
:on-success="onSuccess"
:on-error="onError"
:disabled="importDataDisabled"
style="display: inline-flex;margin-right: 10px"
action="/employee/basic/import">
<el-button :disabled="importDataDisabled" type="success" :icon="importDataBtnIcon">
{{importDataBtnText}}
</el-button>
</el-upload>
<script>
export default {
data() {
return {
importDataBtnText:'导入数据',
importDataBtnIcon:'el-icon-upload2',
importDataDisabled:false,
}
},
methods: {
onError(err,file,fileList) {
this.importDataBtnText='导入数据';
this.importDataBtnIcon='el-icon-update2';
this.importDataDisabled=false;
},
onSuccess(response,file,fileList) {
this.importDataBtnText='导入数据';
this.importDataBtnIcon='el-icon-update2';
this.importDataDisabled=false;
},
beforeUpload() {
this.importDataBtnText='正在导入';
this.importDataBtnIcon='el-icon-loading';
this.importDataDisabled=true;
},
}
}
</script>注：能力有限，还请谅解，争取早日能够写出有质量的文章！
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
