jquery.form.js上传excel

最新推荐文章于 2023-02-24 18:46:52 发布

yili0000a

最新推荐文章于 2023-02-24 18:46:52 发布

阅读量553

点赞数

本文链接：https://blog.csdn.net/yili0000a/article/details/84614745

版权

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>

yili0000a

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
jquery.form.js上传excel

html页面&lt;!DOCTYPE html&gt;&lt;html lang="en"&gt;&lt;head&gt; &lt;meta charset="UTF-8"&gt; &lt;title&gt;Title&lt;/title&gt; &lt;script type='text/javascript' src='/
复制链接

扫一扫