|
前面一篇EasyExcel使用经验分享：https://blog.csdn.net/weixin_43916074/article/details/122184747.
Poi和升级版的EasyExcel对比
- EasyExcel要指定Entiy去接受，而Poi则是一行一行解析
- 没有好用不好用，还是看自己的需求，客制化多一点就是Poi，快速上手就是EasyExcel
1.依赖
<!--打印log -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.12.0</version>
</dependency>
<!--Json转换 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
2.页面，上传Excel
自己下载资源，参考 https://blog.csdn.net/weixin_43916074/article/details/122184747.
<html xmlns="http://www.w3.org/1999/xhtml" xmlns="http://www.w3.org/1999/html">
<head>
<meta charset="UTF-8">
<title>Excel Transposition</title>
<!-- 新 Bootstrap 核心 CSS 文件 -->
<!--<link rel="stylesheet" href="http://cdn.bootcss.com/bootstrap/3.3.0/css/bootstrap.min.css">-->
<link rel="stylesheet" type="text/css" href="css/bootstrap.min.css">
<!-- jQuery文件。务必在bootstrap.min.js 之前引入 -->
<script src="js/jquery-3.4.1.js"></script>
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script src="./js/bootstrap.min.js"></script>
</head>
<style type="text/css">
#content_div {
position: absolute;
left: 0px;
top: 0px;
right: 0px;
bottom: 0px;
text-align: center
}
#header {
box-sizing: border-box;
position: absolute;
width: 100%;
height: 55px;
display: table-cell;
vertical-align: middle;
}
#header > img {
margin: auto;
height: 45px;
position: absolute;
top: 20%;
left: 1%;
}
.upload_dialog_div {
position: fixed;
margin: auto;
left: 0px;
right: 0px;
top: 55px;
bottom: 0px;
overflow: auto;
visibility: hidden;
background-color: white;
}
#style_content_div {
box-sizing: border-box;
position: relative;
margin: auto;
border-radius: 10px;
border: 2px solid;
margin-top: 100px;
width: 450px;
height: 300px;
background: #FBFFFD;
}
.style_content_upper_div {
position: absolute;
border-radius: 10px;
left: 0px;
top: 0px;
width: 446px;
height: 178px;
background: #FBFFFD;
}
.style_content_lower_div {
position: absolute;
bottom: 0;
border-radius: 10px;
left: 0px;
width: 446px;
height: 66px;
background: #FBFFFD;
}
.style_content_file_div {
box-sizing: border-box;
position: absolute;
left: 15px;
top: 20px;
width: 380px;
height: 60px;
font-size: large;
}
.style_file_span {
float: left;
width: 496px;
height: 36px;
font-size: 20px;
text-align: left;
}
.style_file_content {
margin-top: 5px;
}
.style_content_prog_div {
position: absolute;
left: 18px;
top: 120px;
width: 400px;
height: 40px;
}
#style_copyright_a {
margin-top: 120px;
border-radius: 8px;
text-decoration: none;
width: 180px;
height: 80px;
font-size: large;
background-color: #F0F0F0;
}
#progress {
visibility: hidden;
}
table {
height: 66px;
width: 446px;
position: absolute;
}
th > button {
border: 1px solid;
border-radius: 5px;
margin: auto;
position: relative;
width: 120px;
height: 50px;
vertical-align: middle;
background-color: #F0F0F0;
}
#precautions {
margin-top: 55px;
position: relative;
margin-right: auto;
margin-left: auto;
width: 400px;
text-align: left;
}
#alert {
margin: auto;
position: absolute;
margin-top: 10px;
width: 100%;
height: 60px;
visibility: hidden;
}
#warning {
position: relative;
margin-left: auto;
margin-right: auto;
width: 50%;
background-color: #FF5151;
text-align: center;
}
#warning > button {
text-align: right;
width: 20px;
height: 20px;
}
m {
color: red;
font-weight: bold;
}
#footer > p {
position: relative;
margin: auto;
color: gray;
}
</style>
<script type="text/javascript">
function uploadFileToServer() {
var file = document.getElementById("upload_file_id").value;
var fieldNum = document.getElementById("fieldNum").value;
if ($.trim(file) == "") {
var alert = document.getElementById("alert");
alert.style.visibility = "visible";
} else if (fieldNum <= 1 || fieldNum >= 10) {
$('#context').html('数字超出范围，请重新填写');
var alert = document.getElementById("alert");
alert.style.visibility = "visible";
} else {
$('#uploadFile').submit();
var alert = document.getElementById("alert");
alert.style.visibility = "hidden";
}
}
function innerMethod() {
var process01 = document.getElementById("progress");
process01.style.visibility = "hidden";
}
function showUploadDialog() {
var up_dialog = document.getElementById("upload_dialog");
up_dialog.style.visibility = "visible";
var precautions = document.getElementById("precautions");
precautions.style.visibility = "hidden"
}
function hideUploadDialog() {
var up_dialog = document.getElementById("upload_dialog");
up_dialog.style.visibility = "hidden";
var precautions = document.getElementById("precautions");
precautions.style.visibility = "visible";
var alert = document.getElementById("alert");
alert.style.visibility = "hidden";
var file = document.getElementById("upload_file_id");
file.value = ''
}
function closeAlert() {
var alert = document.getElementById("alert");
alert.style.visibility = "hidden";
}
</script>
<body>
<div id="header">
<img src="./img/logo.svg">
</div>
<div id="content_div">
<!--href="javascript:void(0);" 表示页面不动-->
<button id="style_copyright_a" href="javascript:void(0);"
onclick="showUploadDialog()"
onmouseover="this.style.background='#2A9CE2'" onmouseout="this.style.background=''">Upload File
</button>
<div class="list-group" id="precautions">
<a href="#" class="list-group-item disabled"> <strong>Note:</strong></a>
<a href="#" class="list-group-item ">1.xxxx
</a>
<a href="#" class="list-group-item ">2.xxxxx
</a>
</div>
</div>
<div id="upload_dialog" class="upload_dialog_div">
<div id="style_content_div">
<form method="post" action="/upload" enctype="multipart/form-data" id="uploadFile" name="uploadFile">
<div class="style_content_upper_div">
<div class="style_content_prog_div">
<span class="style_file_span"> Category的栏位数量(level count):</span>
<input class="fieldNum" type="number" name="filedNum" id="fieldNum"/>
</div>
<div class="style_content_file_div">
<span class="style_file_span"> Select the file path :</span>
<input class="style_file_content" type="file" name="file" id="upload_file_id"/>
</div>
</div>
<div class="style_content_lower_div">
<table>
<tr>
<th style="text-align: center">
<button type="button"
onmouseover="this.style.background='#9D9D9D'"
onmouseout="this.style.background=''" onclick="hideUploadDialog()">Cancel
</button>
</th>
<th style="text-align: center">
<button type="button"
onmouseover="this.style.background='#2A9CE2'"
onmouseout="this.style.background=''" onclick="uploadFileToServer()">Submit
</button>
</th>
</tr>
</table>
</div>
</form>
</div>
</div>
<div id="alert">
<div class="alert alert-dismissible" role="alert" id="warning">
<button type="button" class="close" onclick="closeAlert()"><span aria-hidden="true">×</span></button>
<strong id="context">Warning!Please select a file.</strong>
</div>
</div>
</body>
</html>
3.controller
对接页面发过来的请求
import java.io.*;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;
@RestController
public class FileController {
@Autowired
private FileService fileService;
@Autowired
private ConvertUtil convertUtil;
private static final Logger logger = LoggerFactory.getLogger(FileController.class);
@RequestMapping(value = "/upload")
public void upload(@RequestParam("file") MultipartFile file, String filedNum) {
List<BusinessTerms> businessTermsList = new ArrayList<>();
List<ErrorEntity> errorList = new ArrayList<>();
if (file.isEmpty()) {
System.out.println("No file is received");
} else {
try {
Workbook workbook = null;
InputStream is = file.getInputStream();
String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
if (suffix.equals(".xlsx")) {
workbook = new XSSFWorkbook(is);
} else if (suffix.equals(".xls")) {
workbook = new HSSFWorkbook(is);
}
Sheet sheet = workbook.getSheetAt(0);
String sheetName = sheet.getSheetName();
logger.info(sheetName);
Row headRow = sheet.getRow(0);
int colCount = sheet.getRow(0).getPhysicalNumberOfCells();
int typeNum = 0;
int businessTermNum = 0;
int businessTermChinese = 0;
int descNum = 0;
int codingRuleNum = 0;
int validValue = 0;
int dataOwnerNum = 0;
int referenceDocuNum = 0;
int remarkNum = 0;
for (int j = 0; j < colCount; j++) {
Cell cell = headRow.getCell(j);
if (cell != null && !"null".equals(cell)) {
String cellValue = cell.getStringCellValue().trim();
if (cellValue.equals("Type")) {
typeNum = j;
} else if (cellValue.equals("Business Term")) {
businessTermNum = j;
} else if (cellValue.equals("Business Term 中文")) {
businessTermChinese = j;
} else if (cellValue.equals("Description")) {
descNum = j;
} else if (cellValue.equals("Coding Rule")) {
codingRuleNum = j;
} else if (cellValue.equals("Valid Value")) {
validValue = j;
} else if (cellValue.equals("Data Owner")) {
dataOwnerNum = j;
} else if (cellValue.equals("Reference Document")) {
referenceDocuNum = j;
} else if (cellValue.equals("Remark")) {
remarkNum = j;
} else {
}
} else {
break;
}
}
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 1; i < rows; i++) {
Row row = sheet.getRow(i);
if (row.getCell(typeNum) != null && !"null".equals(row.getCell(typeNum))) {
if ("New Glossary".equals(row.getCell(typeNum).getRichStringCellValue().toString())) {
BusinessTerms terms = new BusinessTerms();
terms.setApprover("Administrators");
terms.setAuthor("Administrators");
terms.setStatus("Approved");
String businessTermContent = row.getCell(businessTermNum).getRichStringCellValue().toString();
if (businessTermContent != null && businessTermContent.length() > 0) {
terms.setTechnicalName(businessTermContent);
terms.setName(businessTermContent);
} else {
ErrorEntity errEntity = new ErrorEntity();
errEntity.setRowId("原Excel第" + (i + 1) + "行");
errEntity.setContent("Business Term name is required");
errorList.add(errEntity);
logger.info(errEntity.toString());
continue;
}
String descContent = row.getCell(descNum).getRichStringCellValue().toString();
if (descContent != null && descContent.length() > 0) {
terms.setDescription(descContent);
}else{
ErrorEntity errEntity = new ErrorEntity();
errEntity.setRowId("原Excel第" + (i + 1) + "行");
errEntity.setContent("Description is required");
errorList.add(errEntity);
logger.info(errEntity.toString());
continue;
}
String codingRuleContent = row.getCell(codingRuleNum).getRichStringCellValue().toString();
if (codingRuleContent != null && codingRuleContent.length() > 0) {
terms.setCA_codingRule(codingRuleContent);
}
String validValueContent = row.getCell(validValue).getRichStringCellValue().toString();
if (validValueContent != null && validValueContent.length() > 0) {
terms.setCA_validValue(validValueContent);
}
String dataOwnerContent = row.getCell(dataOwnerNum).getRichStringCellValue().toString();
if (dataOwnerContent != null && dataOwnerContent.length() > 0) {
terms.setCA_dataowner(dataOwnerContent);
}
String referenceDocuContent = row.getCell(referenceDocuNum).getRichStringCellValue().toString();
if (referenceDocuContent != null && referenceDocuContent.length() > 0) {
terms.setCA_referenceDocument(referenceDocuContent);
}
String remarkContent = row.getCell(remarkNum).getRichStringCellValue().toString();
if (remarkContent != null && remarkContent.length() > 0) {
terms.setCA_remark(remarkContent);
}
StringBuffer categoryBuffer = new StringBuffer();
for (int j = 0; j < Integer.parseInt(filedNum); j++) {
if (row.getCell(j) != null && !"null".equals(row.getCell(j))) {
String category = row.getCell(j).getRichStringCellValue().toString();
if (category.length() > 0) {
categoryBuffer.append(category).append("/");
} else {
break;
}
}
}
String finalCategory = categoryBuffer.toString();
terms.setCategories(finalCategory.substring(0, finalCategory.length() - 1));
businessTermsList.add(terms);
}
}
}
} catch (Exception e) {
e.printStackTrace();
logger.info(ExceptionUtils.getStackTrace(e));
}
}
Set<BusinessCategories> businessCategoriesSet = new HashSet<>();
businessTermsList.forEach(businessTerms -> {
BusinessCategories businessCategories = new BusinessCategories();
businessCategories.setName(businessTerms.getCategories());
businessCategoriesSet.add(businessCategories);
});
fileService.dataBuffer(businessTermsList, businessCategoriesSet,errorList);
}
@RequestMapping(value = "/download")
public void download() throws Exception {
ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = attributes.getResponse();
OutputStream outputStream = fileService.createFile(response);
fileService.exportForTemplate(outputStream);
outputStream.close();
}
}
4.service
我的需求是将解析的数据放入一个新的Excel并下载
public OutputStream createFile(HttpServletResponse response) throws Exception {
String fileName = createFileName();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
OutputStream outputStream = response.getOutputStream();
return outputStream;
}
将数据写入模板
public void exportForTemplate(OutputStream outputStream) {
logger.info("Write data to template");
InputStream inputStream = FileController.class.getResourceAsStream("/template/Template_xxxx.xlsx");
ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(inputStream).build();
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
WriteSheet firstSheet = EasyExcel.writerSheet(0, "Version").build();
excelWriter.fill(fileService.getVersionList(),fillConfig,firstSheet);
excelWriter.finish();
}
|