當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

表格导出导入

發布時間：2025/3/14 编程问答 17 豆豆

生活随笔收集整理的這篇文章主要介紹了表格导出导入小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

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<Sheet> 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<com.agree.aobp.common.entity.User> userList = new ArrayList<>();
List<UserEnterpriseDTO> 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<Sheet> 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<Sheet> sheetList = parseSheet(wb);
return sheetList;
} catch (IOException e) {
throw e;
} finally {
IOUtils.closeQuietly(inputStream);
}
}

public List<Sheet> parseSheet(Workbook wb) {
List<Sheet> sheetList = new ArrayList<Sheet>();
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 = "社會信用碼";

轉載于:https://www.cnblogs.com/gavin-yao/p/10638537.html

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的表格导出导入的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

表格

上一篇：百炼1001: Exponentiati
下一篇： 2019.4.1 数据库连接池