先将excel上传,获取excel的IO流之后,通过POI解析excel。
第一步、Vue代码index.html
<!DOCTYPE HTML>
<html style="height: 100%;">
<head>
<meta charset="utf-8">
<title>POI</title>
<script src="https://cdn.jsdelivr.net/npm/vue@2.5.17/dist/vue.js"></script>
<script src="https://cdn.staticfile.org/vue-resource/1.5.1/vue-resource.min.js"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>
</head>
<div id="app">
<input type="file" @change="getFile($event)" /><button @click="upload">上传</button>
<div>{{ result }}</div>
<div v-show="uping==1">正在上传中</div>
</div>
<script>
new Vue({
el: '#app',
data: {
upath: '',
result: '',
uping:0
},
methods: {
upload: function () {
//console.log(this.upath);
var zipFormData = new FormData();
zipFormData.append('file', this.upath);//file要和@RequestParam("file")MultipartFile file对应好
let config = { headers: { 'Content-Type': 'multipart/form-data' } };
this.uping = 1;
this.$http.post('http://localhost:80/file/excel', zipFormData,config).then(function (response) {
console.log(response);
console.log(response.data);
console.log(response.bodyText);
var resultobj = response.data;
this.uping = 0;
this.result = resultobj.msg;
});
},
getFile: function (even) {
this.upath = event.target.files[0];
},
}
});
</script>
</body>
</html>
第二步、maven依赖pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.yarm</groupId>
<artifactId>SringBoot-Poi</artifactId>
<version>1.0-0</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.0.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<configuration>
<failOnMissingWebXml>false</failOnMissingWebXml>
</configuration>
</plugin>
</plugins>
</build>
</project>
第三步、controller代码
package com.yarm.poi.controller;
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.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* Created with IDEA
* author:Yarm.Yang
* Date:2019/1/21
* Time:10:01
* Des:文件
*/
@Controller
@RequestMapping("file")
public class FileController {
private final static String EXCEL_2003_DOWN = ".xls"; // 2003- 版本的excel
private final static String EXCEL_2007_UP = ".xlsx"; // 2007+ 版本的excel
@RequestMapping("excel")
@ResponseBody
public List<WhiteListExcelVo> fileUpload(@RequestParam("file")MultipartFile file){
String fileName = file.getOriginalFilename();
try {
InputStream in = file.getInputStream();
List<WhiteListExcelVo> list = new ArrayList<>();
// 创建Excel工作薄
Workbook work = this.getWorkbook(in, fileName);
if (null == work) {
throw new Exception("创建Excel工作薄为空或excel格式不对!");
}
Sheet sheet = work.getSheetAt(0);
//遍历行
for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) {
Row row= sheet.getRow(i);
if (row == null) {
continue;
}
//遍历列
WhiteListExcelVo wev = new WhiteListExcelVo();
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
Object cellValue = this.getCellValue(cell);
if(cell != null){
switch (j){
case 0 : //第一列
wev.setFirst(cellValue.toString());
break;
case 1: //第二列,依此类推
wev.setSecond(cellValue.toString());
break;
default:
continue;
}
}
}
if(wev != null){
list.add(wev);
}
}
return list;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
if (EXCEL_2003_DOWN.equals(fileType)) {
wb = new HSSFWorkbook(inStr); // 2003-
} else if (EXCEL_2007_UP.equals(fileType)) {
wb = new XSSFWorkbook(inStr); //2007+
}
return wb;
}
private Object getCellValue(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("General".equals(cell.getCellStyle().getDataFormatString())) {
value = df.format(cell.getNumericCellValue());
} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
value = sdf.format(cell.getDateCellValue());
} else {
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}
第四步、实体类,供参考
package com.yarm.poi.controller;
/**
* Created with IDEA
* author:Yarm.Yang
* Date:2019/1/22
* Time:10:01
* Des:
*/
public class WhiteListExcelVo {
private String first;
private String second;
public String getFirst() {
return first;
}
public void setFirst(String first) {
this.first = first;
}
public String getSecond() {
return second;
}
public void setSecond(String second) {
this.second = second;
}
}