表格导出导入-白红宇

表格导出导入

阅读量：5278 次

发布时间：2019-06-14

本文共 11616 字，大约阅读时间需要 38 分钟。

1.导出

地址和查询条件：

window.open('mng/developer/export?authState=' + authState + '&devStyle=' + devStyle + '&keywords=' + keywords);

public void export(HttpServletRequest request, HttpServletResponse response, String authState, String devStyle, String keywords) throws IOException, ParseException {

//第一行标题

public static final String[] header= {"name","age"｝

//要导出的集合

List<User> users;

//存放数据的容器

String[][] body = new String[users.size()][header.length];

String name = "表名"；

//数据处理

for (int i = 0; i < devList.size(); i++) {

body[i][0] = users.get(i).getName();

body[i][1] = users.get(i).getAge();

}

POIExportUtils.exportExcel(header, body, name, request, response);

｝

public class POIExportUtils {

public static void exportExcel(String[] header,String[][] body,String fileName,

HttpServletRequest request,HttpServletResponse response) throws IOException{

//创建HSSFWorkbook对象

XSSFWorkbook wb = new XSSFWorkbook();

//创建HSSFSheet对象

XSSFSheet sheet = wb.createSheet("sheet0");

//设置字体:

XSSFFont font = wb.createFont();

XSSFFont font1 = wb.createFont();

//表头样式

XSSFCellStyle style =ExcelStyleUtils.getTableHeaderStyle(wb) ;

style.setFont(ExcelStyleUtils.getTableHeaderFont(font));

//表体样式

XSSFCellStyle style1 =ExcelStyleUtils.getTableBodyStyle(wb) ;

style1.setFont(ExcelStyleUtils.getTableBodyFont(font1));

for(int j = 1;j<body.length+1;j++){

XSSFRow bodyRow = sheet.createRow(j);

for(int k = 0;k<body[j-1].length;k++){

XSSFCell cell=bodyRow.createCell(k);

cell.setCellValue(body[j-1][k]);

cell.setCellStyle(style1);

}

//创建HSSFRow对象

XSSFRow headerRow = sheet.createRow(0);

headerRow.setHeightInPoints(20);

for(int i = 0;i<header.length;i++){

//创建HSSFCell对象

XSSFCell cell=headerRow.createCell(i);

//设置单元格的值

cell.setCellValue(header[i]);

cell.setCellStyle(style);

sheet.autoSizeColumn(i);

}

String name = StringUtil.processFileName(request,fileName);

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-disposition", "attachment;filename="+name+".xlsx");

//输出Excel文件

OutputStream output = response.getOutputStream();

wb.write(output);

output.flush();

output.close();

}

2.导入

/**  *合作方信息导入  *  * @param request  * @param file  * @author: yaozhenhua  2019/4/1 20:01  */ @PostMapping("user/import") public Result importUser(HttpServletRequest request, @RequestParam("file") MultipartFile file) throws Exception {
      UserVO currentUser = (UserVO) request.getSession().getAttribute(Constant.CURR_USER);     Result result = userService.parseUserFile(file, currentUser.getId());     return result; }

public Result parseUserFile(MultipartFile file, long parentId) throws Exception {
      String fileName = file.getOriginalFilename();     String[] splits = fileName.split("\\.");     String fileType = splits[splits.length - 1];     // 解析Excel文件     List
    
      sheetList = parseUserService.readExcel(file.getInputStream(), fileType);     if (EmptyUtils.isEmpty(sheetList)) {
          throw AobpException.notFoundException("合作方信息清单:" + file.getOriginalFilename() + "上传失败!");     }     for (Sheet sheet : sheetList) {
          if ("合作方信息".equals(sheet.getSheetName())) {
              return importUser(sheet, parentId);         }     }     return Result.error(); }

@Transactional(rollbackFor = {RuntimeException.class, Exception.class}) protected Result importUser(Sheet sheet, long parentId) {
      ExcelFlg excelFlg = this.getUserListIndex(sheet);     List
    
      userList = new ArrayList<>();     List
     
       enterpriseList = new ArrayList<>();     int endRow = sheet.getLastRowNum();     for (int i = 1; i <= endRow; i++) {
          //取值         String userPhone = parseUserService.getCellValue(sheet.getRow(i).getCell(                 excelFlg.getUserPhoneFlg()));         String enterpriseName = parseUserService.getCellValue(sheet.getRow(i).getCell(                 excelFlg.getEnterpriseNameFlg()));         String enterpriseDesc = parseUserService.getCellValue(sheet.getRow(i).getCell(                 excelFlg.getEnterpriseDescFlg()));         String enterpriseLicenseNum = parseUserService.getCellValue(sheet.getRow(i).getCell(                 excelFlg.getEnterpriseLicenseNumFlg()));         String registerCapital = parseUserService.getCellValue(sheet.getRow(i).getCell(                 excelFlg.getEnterpriseRegisterCapitalFlg()));         String registerAddress = parseUserService.getCellValue(sheet.getRow(i).getCell(                 excelFlg.getEnterpriseRegisterAddressFlg()));         //字段校验：数据库校验         if (EmptyUtils.isEmpty(userPhone) || EmptyUtils.isEmpty(enterpriseName) || EmptyUtils.isEmpty(enterpriseDesc)                 || EmptyUtils.isEmpty(enterpriseLicenseNum) || EmptyUtils.isEmpty(registerCapital) || EmptyUtils.isEmpty(registerAddress)) {
              return Result.error("字段非空");         }         if (!Pattern.matches(Constant.REGEX_PHONE_NUM, userPhone)) {
              return Result.error("请输入正确的手机号码：" + userPhone + "！");         }         if (!this.checkPhoneNumber(userPhone)) {
              return Result.error("此手机号码:" + userPhone + "已被使用！");         }         if (!Pattern.matches(Constant.REGEX_ENTERPRISE_NAME, enterpriseName)) {
              return Result.error("公司名应为2到50位中文字符！");         }         if (!Pattern.matches(Constant.REGEX_ENTERPRISE_LICENSE_NUM, enterpriseLicenseNum)) {
              return Result.error("社会信用码应为18位数字和大写字母！");         }         if (!Pattern.matches(Constant.REGEX_REGISTER_CAPITAL, registerCapital)) {
              return Result.error("注册资金最大10位整数！");         }         if (registerAddress.length() > 100) {
              return Result.error("注册地址长度应小于100");         }         //构建数据         com.agree.aobp.common.entity.User user = new com.agree.aobp.common.entity.User();         String userId = UUID.randomUUID().toString().replaceAll("-", "");         user.setUserId(userId);         user.setUsername(userPhone);         user.setUserPhone(userPhone);         Long roleId = roleDao.selectIdByRoleName(Constant.ROLE_INDEPENDENT_MERCHANT);         user.setRoleId(roleId);         user.setParentId(parentId);         UserEnterpriseDTO userEnterpriseDTO = new UserEnterpriseDTO();         //userEnterprise.setUserId(userId);         userEnterpriseDTO.setEnterpriseName(enterpriseName);         userEnterpriseDTO.setEnterpriseDesc(enterpriseDesc);         userEnterpriseDTO.setEnterpriseLicenseNum(enterpriseLicenseNum);         userEnterpriseDTO.setRegisterCapital(new BigDecimal(registerCapital));         userEnterpriseDTO.setRegisterAddress(registerAddress);         userEnterpriseDTO.setIsWillLoan(Constant.WILL_LOAN_YES);         userList.add(user);         enterpriseList.add(userEnterpriseDTO);     }     for (int j = 0; j < userList.size(); j++) {
          for (int h = j + 1; h < userList.size(); h++) {
              if (userList.get(j).getUserPhone().equals(userList.get(h).getUserPhone())) {
                  return Result.error("传入的手机号码：" + userList.get(j).getUserPhone() + " 重复！");             }         }     }     //入库     for (int i = 0; i < userList.size(); i++) {
          this.insertUser(userList.get(i));         enterpriseList.get(i).setUserId(userList.get(i).getId());         this.insertUserEnterprise(enterpriseList.get(i));     }     return Result.ok("合作方信息导入成功"); } /**  * @param sheet  * @Description: 字段排序  * @return: void  * @author: yaozhenhua  2019/1/6 10:22  */ private ExcelFlg getUserListIndex(Sheet sheet) {
      Row row = sheet.getRow(0);     int endCellNum = row.getLastCellNum();     ExcelFlg excelFlg = new ExcelFlg();     for (int i = 0; i < endCellNum; i++) {
          if (Constant.USER_PHONE.equals(parseUserService.getCellValue(row.getCell(i)))) {
              excelFlg.setUserPhoneFlg(i);             continue;         } else if (Constant.ENTERPRISE_NAME.equals(parseUserService.getCellValue(row.getCell(i)))) {
              excelFlg.setEnterpriseNameFlg(i);             continue;         } else if (Constant.ENTERPRISE_DESC.equals(parseUserService.getCellValue(row.getCell(i)))) {
              excelFlg.setEnterpriseDescFlg(i);             continue;         } else if (Constant.ENTERPRISE_REGISTER_ADDRESS.equals(parseUserService.getCellValue(row.getCell(i)))) {
              excelFlg.setEnterpriseRegisterAddressFlg(i);             continue;         } else if (Constant.ENTERPRISE_REGISTER_CAPITAL.equals(parseUserService.getCellValue(row.getCell(i)))) {
              excelFlg.setEnterpriseRegisterCapitalFlg(i);             continue;         } else if (Constant.ENTERPRISE_LICENSE_NUM.equals(parseUserService.getCellValue(row.getCell(i)))) {
              excelFlg.setEnterpriseLicenseNumFlg(i);             continue;         }     }     return excelFlg; }

/**  * @ClassName: ParseUserServiceImpl  * @Description: 读取xls, xlsx文件实现  * @author: yaozhenhua  * @date: 2019/1/6 0:45  */ @Service public class ParseUserServiceImpl implements ParseUserService {
      @Override     public List
    
      readExcel(InputStream inputStream, String type) throws AobpException, IOException {
          Workbook wb = null;         try {
              switch (type) {
                  case Constant.EXCEL_XLS:                     wb = new HSSFWorkbook(inputStream);                     break;                 case Constant.EXCEL_XLSX:                     wb = new XSSFWorkbook(inputStream);                     break;                 default:                     throw AobpException.paramsInvalidException("上传文件不正确");             }             List
     
       sheetList = parseSheet(wb);             return sheetList;         } catch (IOException e) {
              throw e;         } finally {
              IOUtils.closeQuietly(inputStream);         }     }     public List
      
        parseSheet(Workbook wb) {
          List
       
         sheetList = new ArrayList
        
         ();         for (int i = 0; i < wb.getNumberOfSheets(); i++) {
              sheetList.add(wb.getSheetAt(i));         }         return sheetList;     }     @Override     public String getCellValue(Cell cell) {
          String value = "";         if (!EmptyUtils.isEmpty(cell)) {
              switch (cell.getCellTypeEnum()) {
                  case STRING:                     value = cell.getRichStringCellValue().getString();                     break;                 case NUMERIC:                     if (HSSFDateUtil.isCellDateFormatted(cell)) {
                          Date date = cell.getDateCellValue();                         value = DateFormatUtils.format(date, "yyyy-MM-dd");                     } else {
                          long dd = (long) cell.getNumericCellValue();                         value = dd + "";                     }                     break;                 case BLANK:                     value = "";                     break;                 case FORMULA:                     value = String.valueOf(cell.getCellFormula());                     break;                 case BOOLEAN:                     value = String.valueOf(cell.getBooleanCellValue());                     break;                 case ERROR:                     value = String.valueOf(cell.getErrorCellValue());                     break;                 default:                     throw new IllegalStateException("Unexpected cell type (" + cell.getCellTypeEnum() + ")");             }         }         return value.trim();     } } 附录：

String USER_PHONE = "手机号码"; String ENTERPRISE_NAME = "公司名"; String ENTERPRISE_DESC = "公司描述"; String ENTERPRISE_REGISTER_ADDRESS = "注册地址"; String ENTERPRISE_REGISTER_CAPITAL = "注册资金"; String ENTERPRISE_LICENSE_NUM = "社会信用码";