模版下载
/**
* 生成模版
*
* @param
* @return AppResponse
*/
public AppResponse ExcelFile() throws IOException {
// 创建一个新的Excel工作簿
Workbook workbook = new XSSFWorkbook();
// 创建一个工作表
Sheet sheet = workbook.createSheet("页面拨测模板");
// 创建行并设置单元格值
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("序号");
headerRow.createCell(1).setCellValue("拨测地址");
headerRow.createCell(2).setCellValue("正常状态码");
headerRow.createCell(3).setCellValue("页面大小（KB）");
headerRow.createCell(4).setCellValue("加载时间（S）");
// 设置列宽
sheet.setColumnWidth(0, 5000);
sheet.setColumnWidth(1, 5000);
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 5000);
FileOutputStream outputStream = null;
//获取字典项 存储路径
//TODO 数据库配置
String path = dialDictDataService.getValue(DictConstants.MODEL_FILE);
// 保存Excel文件
try {
outputStream = new FileOutputStream(path + "pageTemplate.xlsx");
workbook.write(outputStream);
logger.info("DialPageTaskComponent.downloadExcelFile-----------Excel模板生成成功");
} catch (IOException e) {
e.printStackTrace();
logger.info("DialPageTaskComponent.downloadExcelFile-----------Excel模板生成失败");
return AppResponse.failed("生成模版失败");
} finally {
IOUtils.closeQuietly(outputStream);
}
return AppResponse.ok();
}
/**
* 下载模版
* @param response
*/
public void downloadPageTemplate(HttpServletResponse response) {
OutputStream out = null;
InputStream in = null;
ByteArrayOutputStream bos = null;
String fileName = "pageTemplate导入模版";
try {
// 读取模板
Resource res = new ClassPathResource("pageTemplate.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(res.getInputStream());
// 转换为字节流
bos = new ByteArrayOutputStream();
workbook.write(bos);
byte[] barray = bos.toByteArray();
in = new ByteArrayInputStream(barray);
response.reset();
response.setContentType("application/octet-stream");
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
out = response.getOutputStream();
byte[] b = new byte[1024];
int len;
while ((len = in.read(b)) > 0) {
out.write(b, 0, len);
}
out.flush();
} catch (Exception e) {
logger.error("DialPageTaskComponent.downloadPageTemplate, 下载模板失败", e);
} finally {
if (null != in) {
try {
in.close();
} catch (IOException e) {
logger.error("DialPageTaskComponent.downloadPageTemplate, 关闭资源异常", e);
}
in = null;
}
if (null != out) {
try {
out.close();
} catch (IOException e) {
logger.error("DialPageTaskComponent.downloadPageTemplate, 关闭资源异常", e);
}
out = null;
}
if (null != bos) {
try {
bos.flush();
bos.close();
} catch (IOException e) {
logger.error("DialPageTaskComponent.downloadPageTemplate, 关闭资源异常", e);
}
out = null;
}
}
}
/**
* 导入数据
*
* @param file
* @return
*/
public AppResponse<List<DialPageAddressResponseDTO>> importDial(MultipartFile file) {
// 获取文件全名(包含扩展名)
String excelName = file.getOriginalFilename();
// 上传文件名格式校验
if (!".xlsx".equals(excelName.substring(excelName.lastIndexOf(".")))) {
logger.info("DialPageTaskComponent.importDial, 导入失败，请导入excel文件格式, {}", excelName);
return AppResponse.failed("导入失败，请导入正确的excel文件格式!");
}
List<DialPageAddressResponseDTO> resultData = null;
try {
ExcelReader excelReader = ExcelUtil.getReader(file.getInputStream()).setIgnoreEmptyRow(false);
if (excelReader.getSheets().size() != 1) {
return AppResponse.failed("导入失败，excel内容不标准!");
}
// 校验excel是否含有图片
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Map<String, PictureData> picMap = ExcelPicUtil.getPicMap(workbook, 0);
if (CollectionUtil.isNotEmpty(picMap)) {
return AppResponse.failed("导入失败，excel内容不标准!");
}
List<List<Object>> read = excelReader.read(0);
// 校验excel题头
AppResponse<Object> checkTitleAppResponse = checkTitle(read);
if (!checkTitleAppResponse.isOk()) {
logger.info("DialPageTaskComponent.importDial, 数据校验失败, data={}", checkTitleAppResponse);
return AppResponse.failed(checkTitleAppResponse.getMsg());
}
resultData = this.handleData(read);
} catch (Throwable e) {
logger.error("DialPageTaskComponent.importDial, 读取文件失败", e);
return AppResponse.failed("导入失败，excel读取异常！");
}
return AppResponse.ok(resultData);
}
/**
* 校验表头
*
* @param read
* @return AppResponse<Object>
*/
private AppResponse<Object> checkTitle(List<List<Object>> read) {
if (CollectionUtil.isEmpty(read) || CollectionUtil.size(read) < 4) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空, resultData={}", read);
return AppResponse.failed("导入失败，excel内容不可为空！");
}
if (StringUtils.isBlank(String.valueOf(read.get(0).get(0)))) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空, read={}", read);
return AppResponse.failed("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空！");
}
List<Object> secondRow = read.get(1);
if (CollectionUtil.isEmpty(secondRow)) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空, read={}", read);
return AppResponse.failed("导入失败，excel内容不可为空！");
}
if (secondRow.size() != 4) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确, secondRow={}", secondRow.size());
return AppResponse.failed("导入失败，excel表头不正确！");
}
if (!"拨测地址".equals(String.valueOf(secondRow.get(0)))) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-拨测地址, dialAddress={}", secondRow.get(0));
return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(0)));
}
if (!"正常响应码".equals(String.valueOf(secondRow.get(1)))) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-正常响应码, normalStatusCode={}", secondRow.get(1));
return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(1)));
}
if (!"页面大小".equals(String.valueOf(secondRow.get(2)))) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-页面大小, responseTime={}", secondRow.get(2));
return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(2)));
}
if (!"加载时间".equals(String.valueOf(secondRow.get(3)))) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-加载时间, responseTime={}", secondRow.get(3));
return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(2)));
}
// 导入数据为空校验
AppResponse<Object> checkDataIsEmptyappResponse = checkDataIsEmpty(read);
if (!checkDataIsEmptyappResponse.isOk()) {
logger.info("DialPageTaskComponent.checkTitle, 导入失败，内容填写不完整, responseData={}", checkDataIsEmptyappResponse);
return AppResponse.failed(checkDataIsEmptyappResponse.getMsg());
}
return AppResponse.ok();
}
/**
* 校验数据是否为空
*
* @param read
* @return
*/
private AppResponse<Object> checkDataIsEmpty(List<List<Object>> read) {
// 去除最后的空行
removeLastEmptyRow(read);
StringBuffer message = new StringBuffer();
StringBuffer emptyRowMsg = new StringBuffer();
for (int i = 2; i < read.size(); i++) {
if (read.get(i).size() == 0) {
logger.info("DialPageTaskComponent.checkDataIsEmpty, 导入失败，数据单元格不能为空，{}", read.get(i).toString());
emptyRowMsg.append("第").append(i - 1).append("行数据内容为空！").append("</br>");
}
if (read.get(i).size() < 4) {
message.append("第").append(i - 1).append("行，内容填写不完整！" + String.valueOf(read.get(i)).replace("null", "")).append("</br>");
}
for (int j = 0; j < read.get(i).size(); j++) {
if (StringUtils.equalsIgnoreCase(String.valueOf(read.get(i).get(j)), "null")) {
message.append("第").append(i - 1).append("行，第" + (j + 1) + "个单元格内容填写不完整！" + String.valueOf(read.get(i).get(j)).replace("null", "")).append("</br>");
}
}
}
if (StringUtils.isNotEmpty(emptyRowMsg.toString())) {
return AppResponse.failed(emptyRowMsg.toString());
}
if (StringUtils.isNotEmpty(message.toString())) {
return AppResponse.failed(message.toString());
}
return AppResponse.ok();
}
/**
* 去除最后的空行
*
* @param read
*/
public void removeLastEmptyRow(List<List<Object>> read) {
for (int i = read.size() - 1; i >= 0; i--) {
if (CollectionUtil.isNotEmpty(read.get(i))) {
break;
}
read.remove(i);
}
}
/**
* 处理数据
*
* @param read 参数
* @return List<WhiteList> 返回
*/
public List<DialPageAddressResponseDTO> handleData(List<List<Object>> read) {
List<DialPageAddressResponseDTO> resultData = new ArrayList<>();
for (int i = 2; i < read.size(); i++) {
DialPageAddressResponseDTO dialPageAddress = new DialPageAddressResponseDTO();
dialPageAddress.setDialAddress(String.valueOf(read.get(i).get(0)).replace("null", ""));
dialPageAddress.setNormalStatusCode(String.valueOf(read.get(i).get(1)).replace("null", ""));
dialPageAddress.setPageSize(Integer.valueOf(String.valueOf(read.get(i).get(2)).replace("null", "")));
dialPageAddress.setLoadTime(Integer.valueOf(String.valueOf(read.get(i).get(3)).replace("null", "")));
resultData.add(dialPageAddress);
}
return resultData;
}
文章来源地址https://www.toymoban.com/news/detail-637466.html
文章来源：https://www.toymoban.com/news/detail-637466.html
到了这里，关于模版下载和Excel文件导入的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
![[详细步骤]Java将Excel文件导入到数据库表中、并使用postman测试](https://imgs.yssmx.com/Uploads/2024/02/734060-1.png)
