1、表格导出样例
@ApiOperation(value = "新增用户导出接口")
@RequestMapping(value = "/listExcel", method = RequestMethod.GET)
public void listExcel(
@ApiParam(name = "keyWords", value = "姓名或者用户名", required = false) @RequestParam(value = "keyWords", required = false) String keyWords,
HttpServletRequest request,
HttpServletResponse response
) {
People people = new People();
people.setPeopleLoginname(keyWords);
List<People> liabilities = peopleService.getPeopleDtoLikeNameOrDeptIds(people);
try {
String fileName = "系统用户信息" + ".xlsx";
// 设置响应类型
response.setContentType("application/vnd.ms-excel");
// 支持中文名称文件,需要对header进行单独设置,不然下载的文件名会出现乱码或者无法显示的情况
String downloadFileName = new String(fileName .getBytes(),
"ISO-8859-1");
//String downloadFileName = URLEncoder.encode(fileName, "UTF-8");
// 用URLEncoder.encode方法会把空格变成加号(+)在前台页面显示的时候会多出加号。
downloadFileName = downloadFileName.replace("+", "%20");
// 设置响应头,控制浏览器下载该文件
response.setHeader("Content-Disposition", "attachment;filename="
+ downloadFileName);
//表明当前正在使用的tcp链接在请求处理完毕后会被断掉。以后client再进行新的请求时就必须创建新的tcp链接了。
response.setHeader("Connection", "close");
//禁止游览器缓存
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", -1);
List<List<String>> data = new ArrayList<List<String>>();
if (null != liabilities && liabilities.size() > 0) {
for (People peopleList : liabilities) {
List<String> rowData = new ArrayList<String>();
rowData.add(peopleList.getEntcode() == null ? "" : peopleList.getEntcode());
rowData.add(peopleList.getPeopleMobile() == null ? "" : peopleList.getPeopleMobile());
rowData.add(peopleList.getPeopleName() == null ? "" : peopleList.getPeopleName());
Integer peopleSex = peopleList.getPeopleSex();
String sex = changeSexToString(peopleSex);
rowData.add(peopleList.getPeopleSex() == null ? "" : sex);
rowData.add(peopleList.getPeopleMail() == null ? "" : peopleList.getPeopleMail());
data.add(rowData);
}
}
String[] headers0 = {
// "序号",
"企业编号", "手机号", "姓名", "用户性别", "邮箱"
};
ExportExcelUtils eeu = new ExportExcelUtils();
// 创建新的Excel工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
eeu.exportExcel(workbook, 0, "企业信息", headers0, data);
OutputStream outputStream = response.getOutputStream();
BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(
outputStream);
bufferedOutputStream.flush();
workbook.write(bufferedOutputStream);
bufferedOutputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
2、表格导入模板下载
@GetMapping(value = "/getTemplate")
@ApiOperation(value = "模板下载", httpMethod = "GET")
public void getTemplate(HttpServletRequest request, HttpServletResponse response) throws IOException {
List<DeptmentDto> deptmentDtos = deptmentService.list(new DeptmentDto()).getData();
Map<Integer, String []> map=new HashMap<Integer, String []>();
//String[][] name = new String[3][];
String[] deptments = new String[deptmentDtos.size()];
for (int i = 0; i < deptmentDtos.size(); i++) {
deptments[i] = deptmentDtos.get(i).getDeptname();
}
int total=0;
if (null != deptmentDtos && deptmentDtos.size() > 0) {
total++;
map.put(total - 1, deptments);
}
//name[0] = deptments;
Map<String, List<DictionaryValueDto>> valuesByType = dictionaryService.getValuesByType("education,work_status").getData();
List<DictionaryValueDto> education = valuesByType.get("education");
String[] educations = new String[education.size()];
for (int i = 0; i < education.size(); i++) {
educations[i] = education.get(i).getDictValue();
}
if (null != education && education.size() > 0) {
total++;
map.put(total - 1, educations);
}
//name[1] = educations;
List<DictionaryValueDto> work_status = valuesByType.get("work_status");
String[] workStatus = new String[work_status.size()];
for (int i = 0; i < work_status.size(); i++) {
workStatus[i] = work_status.get(i).getDictValue();
}
if (null != work_status && work_status.size() > 0) {
total++;
map.put(total - 1, workStatus);
}
// name[2] = workStatus;
String[][] name = null;
if (null != map && map.size() > 0) {
name = new String[map.size()][];
for (Integer key : map.keySet()) {
name[key] = map.get(key);
}
}
XSSFWorkbook wb = ExcelOutputUtil.getEmployeeTemplate(name);
ServletOutputStream out = null;
try {
String fileName = "员工导入模板";
fileName = URLEncoder.encode(fileName, "UTF8");
response.setContentType("application/vnd.ms-excel;chartset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
response.setHeader("filename", fileName + ".xlsx");
response.addHeader("Access-Control-Expose-Headers", "filename");
out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
3、根据模板导入数据
@PostMapping("/uploadExcel")
@ApiOperation(value = "导入员工数据", httpMethod = "POST")
public CommonResponse uploadExcel(MultipartFile file) {
List<Map<String, String>> list = new ArrayList<>();
XSSFWorkbook workbook = null;
List<DeptmentDto> deptmentDtos = deptmentService.list(new DeptmentDto()).getData();
Map<String, List<DictionaryValueDto>> valuesByType = dictionaryService.getValuesByType("education,work_status").getData();
List<EmployeeDto> employeeDtos = employeeService.listEmployees(Integer.parseInt(UserRequestUtil.getLoginUser().getEntcode()));
Map<String, Object> employees = new HashMap<>();
for (EmployeeDto dto : employeeDtos) {
employees.put(dto.getEmnm(), employeeDtos);
employees.put(dto.getEname() + "&" + dto.getPhone(), employeeDtos);
}
List<DictionaryValueDto> education = valuesByType.get("education");
List<DictionaryValueDto> work_status = valuesByType.get("work_status");
CommonResponse response = null;
try {
// 读取Excel文件
InputStream inputStream = file.getInputStream();
workbook = new XSSFWorkbook(inputStream);
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
XSSFSheet hssfSheet = workbook.getSheetAt(0);
String separate = ",";
// 循环行
list:
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
String errorMsg = "";
Map<String, String> map = new HashMap<>();
XSSFRow hssfRow = hssfSheet.getRow(rowNum);
map.put("number", rowNum + "");
if (hssfRow == null) {
map.put("msg", "无数据");
list.add(map);
continue list;
}
// 将单元格中的内容存入集合
EmployeeDto employee = new EmployeeDto();
//姓名
XSSFCell cell = hssfRow.getCell(0);
if (cell == null) {
errorMsg += errorMsg.equals("") ? "姓名为空" : separate + "姓名为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue().equals("")) {
errorMsg += errorMsg.equals("") ? "姓名为空" : separate + "姓名为空";
} else {
employee.setEname(cell.getStringCellValue());
}
}
//工号
cell = hssfRow.getCell(1);
if (cell == null) {
errorMsg += errorMsg.equals("") ? "工号为空" : separate + "工号为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue().equals("")) {
errorMsg += errorMsg.equals("") ? "工号为空" : separate + "工号为空";
} else {
employee.setEmnm(cell.getStringCellValue());
}
}
//职位
cell = hssfRow.getCell(2);
if (cell == null || cell.getStringCellValue().equals("")) {
//errorMsg += errorMsg.equals("") ? "职位为空" : separate + "职位为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue().equals("")) {
errorMsg += errorMsg.equals("") ? "职位为空" : separate + "职位为空";
} else {
employee.setPositionid(cell.getStringCellValue());
}
}
//部门
cell = hssfRow.getCell(3);
if (cell == null) {
errorMsg += errorMsg.equals("") ? "部门为空,请填写部门" : separate + "部门为空,请填写部门";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
deptment:
for (DeptmentDto dept : deptmentDtos) {
if (dept.getDeptname().equals(cell.getStringCellValue())) {
employee.setDeptid(dept.getDeptid());
break deptment;
}
}
if (employee.getDeptid() == null) {
errorMsg += errorMsg.equals("") ? "未找到相关部门, 请创建部门" : separate + "未找到相关部门,请创建部门";
}
}
//学历
cell = hssfRow.getCell(4);
if (cell == null) {
//errorMsg += errorMsg.equals("") ? "学历为空" : separate + "学历为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
education:
for (DictionaryValueDto dto : education) {
if (dto.getDictValue().equals(cell.getStringCellValue())) {
employee.setEducation(dto.getDictCode());
break education;
}
}
if (employee.getEducation() == null) {
errorMsg += errorMsg.equals("") ? "学历不符合规范" : separate + "学历不符合规范";
}
}
//在职状态
cell = hssfRow.getCell(5);
if (cell == null) {
errorMsg += errorMsg.equals("") ? "在职状态为空" : separate + "在职状态为空";
} else {
if (cell.getStringCellValue().equals("")) {
errorMsg += errorMsg.equals("") ? "在职状态为空" : separate + "在职状态为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
status:
for (DictionaryValueDto dto : work_status) {
if (dto.getDictValue().equals(cell.getStringCellValue())) {
employee.setEcondition(dto.getDictCode());
break status;
}
}
if (employee.getEcondition() == null) {
errorMsg += errorMsg.equals("") ? "在职状态不符合规范" : separate + "在职状态不符合规范";
}
}
}
//手机号
cell = hssfRow.getCell(6);
if (cell == null) {
//errorMsg += errorMsg.equals("") ? "手机号为空" : separate + "手机号为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue().equals("")) {
errorMsg += errorMsg.equals("") ? "手机号为空" : separate + "手机号为空";
} else if (!ValidatorUtil.isMobile(cell.getStringCellValue())) {
errorMsg += errorMsg.equals("") ? "手机号格式错误" : separate + "手机号格式错误";
} else {
employee.setPhone(cell.getStringCellValue());
}
}
//邮箱
cell = hssfRow.getCell(7);
if (cell == null) {
//errorMsg += errorMsg.equals("") ? "邮箱为空" : separate + "邮箱为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue().equals("")) {
errorMsg += errorMsg.equals("") ? "邮箱为空" : separate + "邮箱为空";
} else if (!ValidatorUtil.isEmail(cell.getStringCellValue())) {
errorMsg += errorMsg.equals("") ? "邮箱格式错误" : separate + "邮箱格式错误";
} else {
employee.setEmail(cell.getStringCellValue());
}
}
//身份证号
cell = hssfRow.getCell(8);
if (cell == null) {
//errorMsg += errorMsg.equals("") ? "身份证号为空" : separate + "身份证号为空";
} else {
cell.setCellType(Cell.CELL_TYPE_STRING);
if (cell.getStringCellValue().equals("")) {
errorMsg += errorMsg.equals("") ? "身份证号为空" : separate + "身份证号为空";
} else if (!ValidatorUtil.isIDCard(cell.getStringCellValue())) {
errorMsg += errorMsg.equals("") ? "身份证号格式错误" : separate + "身份证号格式错误";
} else {
employee.setIdcard(cell.getStringCellValue());
}
}
//入职时间
cell = hssfRow.getCell(9);
if (cell == null) {
//errorMsg += errorMsg.equals("") ? "入职时间为空" : separate + "入职时间为空";
} else {
try {
employee.setHiredate(LocalDateTimeUtil.dateToLocalDate(cell.getDateCellValue()));
} catch (Exception e) {
errorMsg += errorMsg.equals("") ? "入职时间不符合规范,示例:2021/10/30" : separate + "入职时间不符合规范,示例:2021/10/30";
map.put("msg", errorMsg);
list.add(map);
continue list;
}
}
if (employees.get(employee.getEmnm()) != null) {
errorMsg += errorMsg.equals("") ? "工号重复" : separate + "工号重复";
}
if (employees.get(employee.getEname() + "&" + employee.getPhone()) != null) {
errorMsg += errorMsg.equals("") ? "员工信息重复" : separate + "员工信息重复";
}
if (errorMsg.equals("")) {
employee.setJobstatus("incumbency");
CommonResponse save = employeeService.save(employee);
if (!save.getCode().equals("SUCCESS")) {
map.put("name", employee.getEname());
map.put("code", employee.getEmnm());
map.put("msg", save.getMsg());
} else {
employees.put(employee.getEmnm(), employee);
employees.put(employee.getEname() + "&" + employee.getPhone(), employee);
}
} else {
map.put("msg", errorMsg);
list.add(map);
}
}
if (list.size() > 0) {
response = CommonResponseFactory.getInstance().error("失败" + list.size() + "条");
response.setData(list);
} else response = CommonResponseFactory.getInstance().success("成功", null);
return response;
}
表格工具类
package com.china.soft.commons.utils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;
import java.util.List;
public class ExportExcelUtils {
private static XSSFCellStyle headStyle;
private static XSSFCellStyle bodyStyle;
/**
* 字体样式
*
* @param workbook
* 工作簿
*/
public static void createFont(XSSFWorkbook workbook) {
// 1:表头
headStyle = workbook.createCellStyle();
// 1)字体
XSSFFont headFont = workbook.createFont();
headFont.setColor(HSSFColor.BLACK.index);
headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headFont.setFontHeightInPoints((short) 12);
headStyle.setFont(headFont);
headStyle.setWrapText(true);// 指定当单元格内容显示不下时自动换行
// 2)边框
headStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headStyle.setBorderBottom(BorderStyle.THIN);// 下边框
headStyle.setBorderTop(BorderStyle.THIN);// 上边框
headStyle.setBorderLeft(BorderStyle.THIN);// 左边框
headStyle.setBorderRight(BorderStyle.THIN);// 右边框
headStyle.setAlignment(HorizontalAlignment.CENTER);// 字体居中
// 2:内容
bodyStyle = workbook.createCellStyle();
// 1)字体
XSSFFont bodyFont = workbook.createFont();
headFont.setColor(HSSFColor.BLACK.index);
bodyFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
bodyFont.setFontHeightInPoints((short) 11);// 字体大小
bodyStyle.setFont(bodyFont);
bodyStyle.setWrapText(true);// 指定当单元格内容显示不下时自动换行
// 2)边框
bodyStyle.setBorderBottom(BorderStyle.THIN);// 下边框
bodyStyle.setBorderTop(BorderStyle.THIN);// 上边框
bodyStyle.setBorderLeft(BorderStyle.THIN);// 左边框
bodyStyle.setBorderRight(BorderStyle.THIN);// 右边框
bodyStyle.setAlignment(HorizontalAlignment.CENTER);// 字体居中
}
/**
*
*
* @param workbook
* @param sheetNum
* (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetName
* sheet的名称)
* @param headers
* (表格的标题)
* @param result
* (表格的数据)
* @throws Exception
*/
public void exportExcel(XSSFWorkbook workbook, int sheetNum,
String sheetName, String[] headers, List<List<String>> result)
throws Exception {
// 在Excel工作簿中创建一张工作表,其名为缺省值,也可以指定Sheet名称
XSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetName);
// Excel样式配置
createFont(workbook);
// 表头 rowIndex=0
int rowIndex = 0;
// 创建标题头
createTableHeader(sheet, rowIndex, headers);
// 数据内容从 rowIndex=1开始
rowIndex = 1;
// 创建内容
createTableRows(sheet, rowIndex, result);
}
private void createTableHeader(XSSFSheet sheet, int rowIndex,
String[] headers) {
XSSFRow headerRow = sheet.createRow(rowIndex);
for (int i = 0; i < headers.length; i++) {
XSSFCell cell = headerRow.createCell((short) i);
cell.setCellStyle(headStyle);// 设置格式
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());// 设置内容
sheet.autoSizeColumn(i);// 先设置自动列宽
sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 25 / 10);
}
}
public static void createTableRows(XSSFSheet sheet, int rowIndex,
List<List<String>> result) {
if (result != null) {
for (int i = 0; i < result.size(); i++) {
XSSFRow row = sheet.createRow(rowIndex + i);
int cellIndex = 0;
for (String str : result.get(i)) {
XSSFCell cell = row.createCell((short) cellIndex);
cell.setCellStyle(bodyStyle);
cell.setCellValue(str);
cellIndex++;
}
}
}
}
}