jquery.form.js上传excel
安明亮
html页面
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script type='text/javascript' src='/jquery.min.js'></script>
<script type='text/javascript' src='/jquery.form.js'></script>
</head>
<body>
<form id="form" enctype="multipart/form-data" method="post" action="">
<select id="type" name="type" style="margin-top: 30px;margin-left: 500px;width: 300px">
<option value="Database_Episodes">Database_Episodes</option>
<option value="Database_Licence_Cost">Database_Licence_Cost</option>
<option value="Database_Licence_Payment">Database_Licence_Payment</option>
<option value="Database_MAM_AMS_Episodes">Database_MAM_AMS_Episodes</option>
<option value="Database_MAM_Episodes">Database_MAM_Episodes</option>
<option value="Database_MAM_Series">Database_MAM_Series</option>
<option value="Database_Series">Database_Series</option>
<option value="Database_Transmission_log">Database_Transmission_log</option>
</select>
<div style="margin-top: 30px;margin-left: 500px">
<input type="file" name="info">
</div>
<button type="button" class="btn btn-success radius" style="width: 100px;margin-left: 500px;margin-top: 30px" onclick="doSubmit()">确定</button>
</form>
<script type="text/javascript">
function doSubmit() {
debugger
var type = $("#type").val();
var option = {
url : 'add',
type : 'post',
dataType : 'json',
headers : {"ClientCallMode" : "ajax"}, //添加请求头部
success : function (result) {
},
error : function (result) {
}
}
$("#form").ajaxSubmit(option);
return false;
}
</script>
</body>
</html>
controller接收代码
@RequestMapping("/add")
@ResponseBody
public Map<String, Object> add(@RequestParam(value = "info",required = false) MultipartFile excelFile, HttpServletRequest request, String type){
try {
Map<String, Object> map = tetraMemberBatchAdd.add(excelFile,request,type);
return map;
} catch (IOException e) {
logger.error("e",e);
}
return null;
}读取excel值的方法
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;
@Service
public class TetraMemberBatchAdd {
private static Logger logger = LoggerFactory.getLogger(TetraMemberBatchAdd.class);
public Map<String,Object> add(MultipartFile excelFile, HttpServletRequest request, String type) throws IOException {
Map<String,Object> map = new HashMap<>();
String filePath;
String fileName = excelFile.getOriginalFilename();
String fileType = null;
if(fileName!=null&&fileName!=""){
fileType = fileName.split("\\.")[1];
}
logger.info(fileType);
if("xlsx".equals(fileType)){
long startTime=System.currentTimeMillis(); //获取开始时间
String realPath = request.getServletContext().getRealPath("")+"\\upload";
filePath = realPath+"\\"+excelFile.getOriginalFilename();
if(!excelFile.isEmpty()){
try {
File file = new File(realPath);
if(!file.exists()){
file.mkdirs();
file.createNewFile();
}
File newFile = new File(file,fileName);
if(!newFile.exists()){
try {
newFile.createNewFile();
} catch (IOException e) {
logger.error("",e);
}
}
FileOutputStream os = new FileOutputStream(filePath);
InputStream in = excelFile.getInputStream();
int b = 0;
while((b=in.read())!=-1){ //读取文件
os.write(b);
}
os.flush(); //关闭流
in.close();
os.close();
} catch (FileNotFoundException e) {
logger.error("",e);
} catch (IOException e) {
logger.error("",e);
}
}
long endTime=System.currentTimeMillis(); //获取结束时间
logger.info("上传文件共使用时间："+(endTime-startTime));
if ("Database_Episodes".equals(type)) {
return readDatabaseEpisodes(filePath);
}else if ("Database_Licence_Cost".equals(type)){
return readDatabaseLicenceCost(filePath);
}else if ("Database_Licence_Payment".equals(type)){
return readDatabaseLicencePayment(filePath);
}else if ("Database_MAM_AMS_Episodes".equals(type)){
return readDatabaseMAMAMSEpisodes(filePath);
}else if ("Database_MAM_Episodes".equals(type)){
return readDatabaseMAMEpisodes(filePath);
}else if ("Database_MAM_Series".equals(type)){
return readDatabaseMAMSeries(filePath);
}else if ("Database_Series".equals(type)){
return readDatabaseSeries(filePath);
}else if ("Database_Transmission_log".equals(type)){
return readDatabaseTransmissionlog(filePath);
}
map.put("success",false);
map.put("msg","文件格式不正确，请上传\".xlsx\"类型文件");
return map;
}else{
map.put("success",false);
map.put("msg","文件格式不正确，请上传\".xlsx\"类型文件");
return map;
}
}
//读取excel文件，创建表格实例
private Map<String,Object> readDatabaseEpisodes(String filePath) throws IOException {
Map<String,Object> map = new HashMap<>();
FileInputStream inStream = new FileInputStream(filePath);
XSSFWorkbook xssfWorkBook = new XSSFWorkbook(inStream);
for (Sheet xsffSheet : xssfWorkBook) {
if(xsffSheet == null){continue;}
//循环当前页每一行，并遍历当前行
int memberUniqueIx = 0;
int memberNameIx = 1;
int memberUnitCodeIx = 2;
logger.info("这一页的行数是："+xsffSheet.getLastRowNum());
try {
for (int rowNo = 1; rowNo <= xsffSheet.getLastRowNum(); rowNo++) {
Row row = xsffSheet.getRow(rowNo);
if(row == null){continue;}
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
//循环当前行的单元格，用下标判断所属字段
for (int colIx = minColIx ; colIx < maxColIx; colIx++) {
Cell cell = row.getCell(colIx);
if (cell == null){ continue; }
if(cell.getColumnIndex() == memberUniqueIx){
String unique = getCellValue(cell);
}
if(cell.getColumnIndex() == memberNameIx){
String name = getCellValue(cell);
}
if(cell.getColumnIndex() == memberUnitCodeIx){
String unitCode = getCellValue(cell);
}
}
}
} catch (Exception e) {
logger.error("",e);
}
try {
// terminalMemberService.save(memberList);
}catch (Exception e){
logger.error("",e);
}
map.put("success",true);
map.put("msg","批量添加成功!");
return map;
}
return null;
}
//获取单元格的值
private String getCellValue(Cell cell) {
String cellValue = "";
DataFormatter formatter = new DataFormatter();
DecimalFormat format = new DecimalFormat("#");
if (cell != null) {
//判断单元格数据的类型，不同类型调用不同的方法
switch (cell.getCellType()) {
//数值类型
case Cell.CELL_TYPE_NUMERIC:
//进一步判断 ，单元格格式是日期格式
if (DateUtil.isCellDateFormatted(cell)) {
cellValue = formatter.formatCellValue(cell);
} else {
//数值
double value = cell.getNumericCellValue();
int intValue = (int) value;
cellValue = format.format(value);
}
break;
case Cell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
//判断单元格是公式格式，需要做一种特殊处理来得到相应的值
case Cell.CELL_TYPE_FORMULA:{
try{
cellValue = String.valueOf(cell.getNumericCellValue());
}catch(IllegalStateException e){
cellValue = String.valueOf(cell.getRichStringCellValue());
}
}
break;
case Cell.CELL_TYPE_BLANK:
cellValue = "";
break;
case Cell.CELL_TYPE_ERROR:
cellValue = "";
break;
default:
cellValue = cell.toString().trim();
break;
}
}
return cellValue.trim();
}
需要引入的依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
类似资料：