JAVA表格导入导出样例

JAVA表格导入导出样例

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 = PeopleDtoLikeNameOrDeptIds(people);try {String fileName = "系统用户信息" + ".xlsx";// 设置响应类型response.setContentType("application/vnd.ms-excel");// 支持中文名称文件,需要对header进行单独设置，不然下载的文件名会出现乱码或者无法显示的情况String downloadFileName = new String(fileName .getBytes(),"ISO-8859-1");//String downloadFileName = de(fileName, "UTF-8");// 用de方法会把空格变成加号（+）在前台页面显示的时候会多出加号。downloadFileName = place("+", "%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.Entcode() == null ? "" : Entcode());rowData.PeopleMobile() == null ? "" : PeopleMobile());rowData.PeopleName() == null ? "" : PeopleName());Integer peopleSex = PeopleSex();String sex = changeSexToString(peopleSex);rowData.PeopleSex() == null ? "" : sex);rowData.PeopleMail() == null ? "" : PeopleMail());data.add(rowData);}}String[] headers0 = {// "序号","企业编号", "手机号", "姓名", "用户性别", "邮箱"};ExportExcelUtils eeu = new ExportExcelUtils();// 创建新的Excel工作簿XSSFWorkbook workbook = new XSSFWorkbook();portExcel(workbook, 0, "企业信息", headers0, data);OutputStream outputStream = OutputStream();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] = (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 = ValuesByType("education,work_status").getData();List<DictionaryValueDto> education = ("education");String[] educations = new String[education.size()];for (int i = 0; i < education.size(); i++) {educations[i] = (i).getDictValue();}if (null != education && education.size() > 0) {total++;map.put(total - 1, educations);}//name[1] = educations;List<DictionaryValueDto> work_status = ("work_status");String[] workStatus = new String[work_status.size()];for (int i = 0; i < work_status.size(); i++) {workStatus[i] = (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] = (key);}}XSSFWorkbook wb = EmployeeTemplate(name);ServletOutputStream out = null;try {String fileName = "员工导入模板";fileName = de(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 = OutputStream();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 = ValuesByType("education,work_status").getData();List<EmployeeDto> employeeDtos = employeeService.listEmployees(Integer.LoginUser().getEntcode()));Map<String, Object> employees = new HashMap<>();for (EmployeeDto dto : employeeDtos) {employees.Emnm(), employeeDtos);employees.Ename() + "&" + Phone(), employeeDtos);}List<DictionaryValueDto> education = ("education");List<DictionaryValueDto> work_status = ("work_status");CommonResponse response = null;try {// 读取Excel文件InputStream inputStream = InputStream();workbook = new XSSFWorkbook(inputStream);inputStream.close();} catch (Exception e) {e.printStackTrace();}XSSFSheet hssfSheet = SheetAt(0);String separate = ",";// 循环行list:for (int rowNum = 1; rowNum <= LastRowNum(); rowNum++) {String errorMsg = "";Map<String, String> map = new HashMap<>();XSSFRow hssfRow = Row(rowNum);map.put("number", rowNum + "");if (hssfRow == null) {map.put("msg", "无数据");list.add(map);continue list;}// 将单元格中的内容存入集合EmployeeDto employee = new EmployeeDto();//姓名XSSFCell cell = Cell(0);if (cell == null) {errorMsg += errorMsg.equals("") ? "姓名为空" : separate + "姓名为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);if (StringCellValue().equals("")) {errorMsg += errorMsg.equals("") ? "姓名为空" : separate + "姓名为空";} else {employee.StringCellValue());}}//工号cell = Cell(1);if (cell == null) {errorMsg += errorMsg.equals("") ? "工号为空" : separate + "工号为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);if (StringCellValue().equals("")) {errorMsg += errorMsg.equals("") ? "工号为空" : separate + "工号为空";} else {employee.StringCellValue());}}//职位cell = Cell(2);if (cell == null || StringCellValue().equals("")) {//errorMsg += errorMsg.equals("") ? "职位为空" : separate + "职位为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);if (StringCellValue().equals("")) {errorMsg += errorMsg.equals("") ? "职位为空" : separate + "职位为空";} else {employee.StringCellValue());}}//部门cell = Cell(3);if (cell == null) {errorMsg += errorMsg.equals("") ? "部门为空，请填写部门" : separate + "部门为空，请填写部门";} else {cell.setCellType(Cell.CELL_TYPE_STRING);deptment:for (DeptmentDto dept : deptmentDtos) {if (Deptname().StringCellValue())) {employee.Deptid());break deptment;}}if (Deptid() == null) {errorMsg += errorMsg.equals("") ? "未找到相关部门, 请创建部门" : separate + "未找到相关部门，请创建部门";}}//学历cell = Cell(4);if (cell == null) {//errorMsg += errorMsg.equals("") ? "学历为空" : separate + "学历为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);education:for (DictionaryValueDto dto : education) {if (DictValue().StringCellValue())) {employee.DictCode());break education;}}if (Education() == null) {errorMsg += errorMsg.equals("") ? "学历不符合规范" : separate + "学历不符合规范";}}//在职状态cell = Cell(5);if (cell == null) {errorMsg += errorMsg.equals("") ? "在职状态为空" : separate + "在职状态为空";} else {if (StringCellValue().equals("")) {errorMsg += errorMsg.equals("") ? "在职状态为空" : separate + "在职状态为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);status:for (DictionaryValueDto dto : work_status) {if (DictValue().StringCellValue())) {employee.DictCode());break status;}}if (Econdition() == null) {errorMsg += errorMsg.equals("") ? "在职状态不符合规范" : separate + "在职状态不符合规范";}}}//手机号cell = Cell(6);if (cell == null) {//errorMsg += errorMsg.equals("") ? "手机号为空" : separate + "手机号为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);if (StringCellValue().equals("")) {errorMsg += errorMsg.equals("") ? "手机号为空" : separate + "手机号为空";} else if (!ValidatorUtil.StringCellValue())) {errorMsg += errorMsg.equals("") ? "手机号格式错误" : separate + "手机号格式错误";} else {employee.StringCellValue());}}//邮箱cell = Cell(7);if (cell == null) {//errorMsg += errorMsg.equals("") ? "邮箱为空" : separate + "邮箱为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);if (StringCellValue().equals("")) {errorMsg += errorMsg.equals("") ? "邮箱为空" : separate + "邮箱为空";} else if (!ValidatorUtil.StringCellValue())) {errorMsg += errorMsg.equals("") ? "邮箱格式错误" : separate + "邮箱格式错误";} else {employee.StringCellValue());}}//身份证号cell = Cell(8);if (cell == null) {//errorMsg += errorMsg.equals("") ? "身份证号为空" : separate + "身份证号为空";} else {cell.setCellType(Cell.CELL_TYPE_STRING);if (StringCellValue().equals("")) {errorMsg += errorMsg.equals("") ? "身份证号为空" : separate + "身份证号为空";} else if (!ValidatorUtil.StringCellValue())) {errorMsg += errorMsg.equals("") ? "身份证号格式错误" : separate + "身份证号格式错误";} else {employee.StringCellValue());}}//入职时间cell = Cell(9);if (cell == null) {//errorMsg += errorMsg.equals("") ? "入职时间为空" : separate + "入职时间为空";} else {try {employee.setHiredate(LocalDateTimeUtil.DateCellValue()));} catch (Exception e) {errorMsg += errorMsg.equals("") ? "入职时间不符合规范，示例：2021/10/30" : separate + "入职时间不符合规范，示例：2021/10/30";map.put("msg", errorMsg);list.add(map);continue list;}}if ((Emnm()) != null) {errorMsg += errorMsg.equals("") ? "工号重复" : separate + "工号重复";}if ((Ename() + "&" + Phone()) != null) {errorMsg += errorMsg.equals("") ? "员工信息重复" : separate + "员工信息重复";}if (errorMsg.equals("")) {employee.setJobstatus("incumbency");CommonResponse save = employeeService.save(employee);if (!Code().equals("SUCCESS")) {map.put("name", Ename());map.put("code", Emnm());map.put("msg", Msg());} else {employees.Emnm(), employee);employees.Ename() + "&" + Phone(), employee);}} else {map.put("msg", errorMsg);list.add(map);}}if (list.size() > 0) {response = Instance().error("失败" + list.size() + "条");response.setData(list);} else response = Instance().success("成功", null);return response;}

表格工具类

package com.china.softmons.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 = ateCellStyle();// 1)字体XSSFFont headFont = ateFont();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 = ateCellStyle();// 1)字体XSSFFont bodyFont = ateFont();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 = ateSheet();workbook.setSheetName(sheetNum, sheetName);// Excel样式配置createFont(workbook);// 表头 rowIndex=0int 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 = ateRow(rowIndex);for (int i = 0; i < headers.length; i++) {XSSFCell cell = ateCell((short) i);cell.setCellStyle(headStyle);// 设置格式HSSFRichTextString text = new HSSFRichTextString(headers[i]);cell.String());// 设置内容sheet.autoSizeColumn(i);// 先设置自动列宽sheet.setColumnWidth(i, ColumnWidth(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 = ateRow(rowIndex + i);int cellIndex = 0;for (String str : (i)) {XSSFCell cell = ateCell((short) cellIndex);cell.setCellStyle(bodyStyle);cell.setCellValue(str);cellIndex++;}}}}
}