private void parseSubCategoryExcel(String baseUrl, File excelFile,String versionControl) throws IOException {
FileInputStream fis = new FileInputStream(excelFile);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook
.get
SheetAt(0);
try (SqlSession sqlSession = sqlSessionFactory
.openSession(false)) {
SubCategoryMapper subCategoryMapper = sqlSession
.getMapper(SubCategoryMapper
.class);
StringBuilder pdfName = new StringBuilder();
String currentSubValue = "";
String mianCategory = "";
// 解析Excel内容
StringBuilder htmlContent = new StringBuilder();
boolean isSecurityDetailOpen = false;
for (Row row :
sheet) {
Cell cell = row
.getCell(1);
if(cell != null || cell
.getCellType() == CellType
.STRING){
String cellValue = cell
.toString()
.trim();
if (cellValue
.contains("1
.")) {
mianCategory = cellValue
.substring(cellValue
.indexOf("1
.") + 2)
.trim();
continue;
} else if (cellValue
.contains("2
.")) {
// Take only the text after "2
."
htmlContent
.append("<div class=\"go-back-bar\">\n" +
" <div class=\"container title\"><i class=\"iconfont icon-fanhui go-back-icon\" onclick=\"goBack()\"></i>");
String textAfter2 = cellValue
.substring(cellValue
.indexOf("2
.") + 2)
.trim();
currentSubValue = textAfter2;
htmlContent
.append(textAfter2);
htmlContent
.append("</div>\n" +
" </div>" +
" <div class=\"container security-detail\">");
isSecurityDetailOpen = true;
continue;
} else if(cellValue
.contains("：") && cellValue
.contains("《")){
StringBuilder sb = new StringBuilder();
String[] parts = cellValue
.split("：", 2);
pdfName
.append(parts[1]
.trim());
if (parts
.length == 2) {
Pattern pattern = Pattern
.compile("《([^》]*)》");
Matcher matcher = pattern
.matcher(parts[1]
.trim());
List<String> filenames = new ArrayList<>();
while (matcher
.find()) {
String name = matcher
.group(1);
if (!name
.trim()
.isEmpty()) { // 过滤空字符串
filenames
.add(name+"
.pdf");
}
}
for (String name : filenames) {
sb
.append("<a class=\"link-item\" href=\"");
sb
.append(baseUrl);
sb
.append("/cas/file/");
sb
.append(name);
sb
.append("\">《");
sb
.append(name);
sb
.append("》</a>");
}
String formattedContent = String
.format(
"<div class=\"info-block\">\n" +
" <div class=\"info-title\">%s</div>\n" +
" <div class=\"info-content\"> %s</div>\n" +
" </div>",
parts[0]
.trim() + "：", sb
.toString()
);
htmlContent
.append(formattedContent);
}
continue;
}else if (cellValue
.contains("：") && !cellValue
.contains("《")){
// Split the cell value by ":"
String[] parts = cellValue
.split("：", 2);
if (parts
.length == 2) {
String formattedContent = String
.format(
"<div class=\"info-block\">\n" +
" <div class=\"info-title\">%s</div>\n" +
" <div class=\"info-content\">%s</div>\n" +
" </div>",
parts[0]
.trim() + "：", parts[1]
.trim()
);
htmlContent
.append(formattedContent);
}
continue;
}
}
}
if (isSecurityDetailOpen) {
htmlContent
.append("</div>");
}
SubCategoryDto subCategoryDto = new SubCategoryDto();
subCategoryDto
.setVersionControl(versionControl);
subCategoryDto
.setSubCategory(currentSubValue);
subCategoryDto
.setPdfName(pdfName
.toString());
subCategoryDto
.setClob(htmlContent
.toString());
subCategoryDto
.setMainCategory(mianCategory);
subCategoryMapper
.insertSubCategory(subCategoryDto);
workbook
.close();
fis
.close();
}
}我要使用一种新的方法来替换这种使用Workbook workbook = new XSSFWorkbook(fis);的写法实现这个功能
在使用SXSSFWorkbook处理动态行数的Excel时，遇到设置行组(groupRow)不生效的问题。原因是行数超过SXSSFWorkbook的内存窗口大小，导致已写的行被写入磁盘，内存中找不到对应行。解决方法包括设置窗口参数为-1以保存所有行，或者先写入磁盘再用XSSFWorkbook读取并设置行组。对于超大量数据，如10w+行，建议采用后者避免内存压力。
最低0.47元/天 解锁文章
扫一扫
4766
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
