前言:
主要功能:做EXECL文件解析、EXECL数据单独校验(EXECL文档格式及主体数据的逻辑校验)及EXECL和TXT的数据联合校验。
1、POI 环境
1.1、软件
MAVEN+SPRING BOOT+MYBATIS jdk1.8
1.2、操作系统
Window X
2、jar包
2.1、poi.jar
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
2.2、文件上传下载
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
<!--文件工具包-->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.6</version>
</dependency>
3、解析步奏
3.1、文件上传需要表单异步提交
3.1.1、画面代码
<form action="${ctxPath}/merge/psiUpload" method="POST" enctype="mulitpart/form-data" id="upload_form_psi">
<input class="input-file-remove-style" type="file" name="psiFile" id="psiFile" onchange="addPsiFile(this)"/>
</form>
3.1.2后台代码
3.1.2.1 、controller代码
@RequestMapping(value = "/Convert/upload", method = RequestMethod.POST, produces="application/json;charset=UTF-8")
public void checkHead(@RequestParam(value = "filename") MultipartFile file, String fileType,
HttpServletResponse resp) throws Exception {
Date now = new Date();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String tmpName = dateFormat.format(now);
String fileName = file.getOriginalFilename();
String originalFilename = fileName.substring(fileName.lastIndexOf("\\")+1);
String fileId = originalFilename.substring(0, originalFilename.lastIndexOf("."))+"_"+tmpName;
String path = new UploadFile().writeFile(file, fileId);
Map<String, String> map = new HashMap<>();
if (path != null) {
map.put("fileId", fileId);
}
JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));
resp.getWriter().print(result);
}
3.1.2.2 、上传文件工具类UploadFile
public class UploadFile{
/**
*
* @param upf
* @param fileid 命名为源文件名+"_"+时间戳
* @return
*/
public String writeFile(MultipartFile upf,String fileid){
String address = null;
String fileName = upf.getOriginalFilename();
fileName = fileName.substring(fileName.lastIndexOf("\\")+1);
String suffixName = ToolUtil.getFileSuffix(fileName);
String tmp[] = fileid.split("_");
String folderName=ShiroKit.getUser().getAccount();
if("txt".equals(suffixName)){
//folderName = tmp[tmp.length-1].substring(0, 8);
address = tmp[tmp.length-1].substring(0, 8)+"/"+fileid+".txt";
}else{
if(fileid.contains("bak")){
address = tmp[tmp.length-2].substring(0, 8)+"/"+fileid+".xlsx";
}else{
address = tmp[tmp.length-1].substring(0, 8)+"/"+fileid+".xlsx";
}
}
try {
//原目录
File file = new File("D:/psi/temp/"+folderName+"/");
if (!file.exists()) file.mkdirs();
File file2 = new File("D:/psi/temp/"+folderName+"/"+ fileid + "."+suffixName);
FileUtils.copyInputStreamToFile(upf.getInputStream(), file2);
} catch (IOException e1) {
e1.printStackTrace();
}
return address;
}
}
3.2、文件下载
3.2.1、画面代码
<div class="col-sm-12 custom-alerts alert alert-warning fade in display-none" id="dowerrormessage">
<button type="button" class="close" data-dismiss="alert" aria-hidden="true"></button>
<i class="fa-lg fa fa-warning"></i> PSI file convert failed. You can check error message from <a onclick=downFile(‘100101’)>here</a>.
</div>
<!--在做一个隐藏表单-->
<form id="downForm" action="reference/down" method='get'>
<input type="hidden" name="fileId" id="fileId" value="" />
</form>
<script>
<!--通过js提交表单-->
function downFile(fileId){
$("#fileId").val(fileId);
$("#downForm").submit();
}
</script>
3.2.2、后台代码
3.2.2.1、controller代码
@Permission
@RequestMapping(value = "/down")
@ResponseBody
public void down(@RequestParam(required = false) String fileId,HttpServletResponse response) {
String path = referenceService.down(fileId);
File file = new File(path);
if(!file.exists()){
throw new ServiceException(BizExceptionEnum.DOWN_FILE);
}
//创建输出流
OutputStream out = null;
try {
out = response.getOutputStream();
response.reset();
response.setContentType(ToolUtil.getFileSuffix(path));
String fileName = path.substring(path.lastIndexOf("\\")+1);
File tempFile =new File(fileName);
fileName = java.net.URLEncoder.encode(tempFile.getName(), "UTF-8");
fileName = fileName.replace("+", "%20"); //encode后替换 解决空格问题
response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
} catch (IOException e) {
e.printStackTrace();
}
FileUtil.downloadFile(file, out);
}
3.2.2.2、工具类FileUtil
Public class FileUtil{
public static void downloadFile(File file, OutputStream output) {
FileInputStream fileInput = null;
BufferedInputStream inputStream = null;
try {
fileInput = new FileInputStream(file);
inputStream = new BufferedInputStream(fileInput);
byte[] buffer = new byte[8192];//1024*8
int i;
while ((i = inputStream.read(buffer)) != -1) {
output.write(buffer,0,i);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if (inputStream != null)
inputStream.close();
if (fileInput != null)
fileInput.close();
}catch (IOException e) {
e.printStackTrace();
}
}
}
}
4、特殊操作
4.1、获取合并单元格位置
//第五行的合并单元格
Map<Integer, Integer> map = new TreeMap<Integer, Integer>();
int sheetmergerCount = xssfSheet.getNumMergedRegions();
for (int i = 0; i < sheetmergerCount; i++) {
CellRangeAddress ca = xssfSheet.getMergedRegion(i);
if (ca.getFirstRow() == 5) {
map.put(ca.getFirstColumn(), ca.getLastColumn() - ca.getFirstColumn() + 1);
}
}
4.2、单元格数字列号转字母
public final class Columns {
/**
* POI读取EXECL第一行是0，第一列是0
*/
private Columns() {
}
private static String[] sources = new String[] { "A", "B", "C", "D", "E",
"F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
"S", "T", "U", "V", "W", "X", "Y", "Z" };
/**
* (256 for *.xls, 16384 for *.xlsx)
*
* @param columnNum
* 列的个数，从1开始
* @throws IllegalArgumentException
* 如果 columnNum 超出该范围 [1,16384]
* @return 返回[1,columnNum]共columnNum个对应xls列字母的数组
*/
public static String[] getColumnLabels(int columnNum) {
if (columnNum < 1 || columnNum > 16384)
throw new IllegalArgumentException();
String[] columns = new String[columnNum];
if (columnNum < 27) {
System.arraycopy(sources, 0, columns, 0, columnNum);
return columns;
}
int multiple = -1;
int remainder;
System.arraycopy(sources, 0, columns, 0, 26);
int currentLoopIdx = 0;
if (columnNum < 703) {
for (int i = 26; i < columnNum; i++) {
remainder = currentLoopIdx % 26;
if (remainder == 0) {
multiple++;
}
columns[i] = sources[multiple] + columns[remainder];
currentLoopIdx++;
}
} else {
int currentLen = 26;
int totalLen = 26;
int lastLen = 0;
for (int i = 26; i < columnNum; i++) {
remainder = currentLoopIdx % currentLen;
if (remainder == 0) {
multiple++;
int j = multiple % 26;
if (j == 0 && multiple != 0) {
lastLen = totalLen;
currentLen = 26 * currentLen;
totalLen = currentLen + lastLen;
currentLoopIdx = 0;
}
}
columns[i] = sources[multiple % 26]
+ columns[remainder + lastLen];
currentLoopIdx++;
}
}
return columns;
}
/**
* 返回该列号对应的字母
*
* @param columnNo
* (xls的)第几列（从1开始）
*/
private static String getCorrespondingLabel(int columnNo) {
if (columnNo < 1/** ||columnNo>16384 **/
)
throw new IllegalArgumentException();
StringBuilder sb = new StringBuilder(5);
int remainder = columnNo % 26;
if (remainder == 0) {
sb.append("Z");
remainder = 26;
} else {
sb.append(sources[remainder - 1]);
}
while ((columnNo = (columnNo - remainder) / 26 - 1) > -1) {
remainder = columnNo % 26;
sb.append(sources[remainder]);
}
return sb.reverse().toString();
}
/**
* 列号转字母
*
* @param columnIndex
* poi里xls的列号（从0开始）
* @throws IllegalArgumentException
* if columnIndex less than 0
* @return 该列对应的字母
*/
public static String getIndexLabel(int columnIndex) {
return getCorrespondingLabel(columnIndex + 1);
}
public static void main(String[] args) {
System.out.println(getIndexLabel(254));
}
4.3、获取execl最大行数
Int maxRowNum = Sheet.getPhysicalNumberOfRows();
注:该方法可以获取sheet中的最大单元格行数,但是被编辑后，再删除的单元格也会被计算在内。
4.4、获取execl最大列数
//需要循环每一行，比较每一行的最大列，比较后取最大的一个列作为sheet的最大列
int totalCells = 0;
int maxTotalCells = 0;
XSSFRow row=null;
For(int i=maxRowNum;i>0;i--){
row = sheet.getRow(i);
totalCells = row.getPhysicalNumberOfCells();
maxTotalCells = totalCells>maxTotalCells?totalCells:maxTotalCells;
}
4.5、获取单元格数据，并以字符串返回
/**
* 获取cell的值
* @param cell
* @return
*/
public static String getCellValue(XSSFCell cell){
String value = "";
if (ToolUtil.isEmpty(cell)) {
return value;
}else {
if(XSSFCell.CELL_TYPE_FORMULA != cell.getCellType() && ToolUtil.isEmpty(cell.toString())){
return value;
}
}
switch (cell.getCellType()) {
// 数值型
case XSSFCell.CELL_TYPE_NUMERIC:
// BigDecimal num = new BigDecimal(String.valueOf(cell.getNumericCellValue()));
// value = String.valueOf(num);
DecimalFormat df = new DecimalFormat("##.###");
value = df.format(cell.getNumericCellValue());
if (null != value && !"".equals(value.trim())) {
String item[] = value.split("\\.");
if (1 < item.length && "0".equals(item[1])) {
value = item[0];
}
}
break;
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue().toString();
break;
case XSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case XSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case XSSFCell.CELL_TYPE_FORMULA:
if (isNumeric(String.valueOf(cell.getNumericCellValue()))) {
DecimalFormat dfa = new DecimalFormat("##.###");
value = dfa.format(cell.getNumericCellValue());
if (null != value && !"".equals(value.trim())) {
String item[] = value.split("\\.");
if (1 < item.length && "0".equals(item[1])) {
value = item[0];
}
}
} else {
value = cell.getStringCellValue().toString();
}
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
default:
value = cell.getStringCellValue().toString();
}
return value.trim();
}
5、隐含问题
问题1：上传文件乱码
解决方案：后台接收上传文件的表单数据时可以设置编码格式
@RequestMapping(value = "/Convert/upload", method = RequestMethod.POST, produces="application/json;charset=UTF-8")
问题2：方法间传递xssfsheet对象，占用过多系统资源
解决方案：将数据取出后，存在java容器中，后续只对该容器操作
问题3：在循环中用集合下标获取集合对象，在取出对象属性，消耗内存
解决方案：在循环外声明一个未初始化的对象接收每次循环获得的对象,然后使用这个对象再获取所需的属性；
问题4：处理业务时使用过多list，实现逻辑消耗内存
解决方案：必须使用时减少循环嵌套，可以结合map一起处理业务
问题5：List/ArrayList/HashMap 线程不安全
说明:这几个容器当是局部标量时可以不考虑线程安全问题,当是当是成员变量时多并发时由于线程不安全会导致第一个请求可以正常执行,但是后面的请求操作失败(若没执行完一次把容器清空,会导致后面的数据错误；若置NULL，后面汇报NullPointException)
解决方案：List/ArrayList可以ThreadLocal<List<String>> tlCellList = new ThreadLocal<List<String>>()；HashMap可以使用ConcurrentHashMap替换
问题6：读execl时需要考虑execl版本
6、注意事项
(1) 异步提交,做上传文件时,form表单中的属性需要设置处理提交方式“method="POST"”；数据类型enctype="mulitpart/form-data"
(2) 取时间戳时注意设置时区：
TimeZone timeZone = TimeZone.getTimeZone("GMT+8:00");
Date now = Calendar.getInstance(timeZone,Locale.CHINA).getTime();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");
(3) 解析execl时创建的sheet对象、行对象、单元格对象时分别使用XSSFSheet/XSSFRow/XSSFCell对象
************有其他问题的解决方案，或是相同问题有更好的解决办法，欢迎提出****************
************下次发一个SAX解析EXECL的案例********************************************
