Excel上传

最新推荐文章于 2022-07-20 22:49:50 发布

原创最新推荐文章于 2022-07-20 22:49:50 发布 · 566 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #上传

JavaWeb 专栏收录该内容

22 篇文章

订阅专栏

前端页面

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <script type="text/javascript" src="/js/jquery.min.js"></script>
    <script type="text/javascript" src="/js/bootstrap.min.js"></script>

    <script type="text/javascript" src="/js/vue.min.js"></script>

</head>
<body>
<!--<form    id="batchUpload">-->

<form  enctype="multipart/form-data"  id="batchUpload"  action="/bclass/upload" method="post" class="form-horizontal">
    <button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;"  type="button" >选择文件</button>
    <input type="file" name="uploadFile" runat="server"  style="width:0px;height:0px;" id="uploadFile"/>
    <input id="uploadFilePath" name="uploadFilePath" disabled="disabled"  type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 200px;" />

    <button type="button" class="btn btn-success btn-sm"  onclick="user.uploadBtn()" >上传</button>
    <!--<input type="button" value="保存"/>-->
</form>

<script type="text/javascript" src="/js/jquery-1.6.2.min.js"></script>
<script type="text/javascript" src="http://apps.bdimg.com/libs/jqueryui/1.10.4/jquery-ui.min.js"></script>
<script type="text/javascript" src="/js/jquery.form.js"></script>
<script>
    var User = function(){

        this.init = function(){

            //模拟上传excel
            $("#uploadEventBtn").unbind("click").bind("click",function(){
                $("#uploadFile").click();
            });
            $("#uploadFile").bind("change",function(){
                $("#uploadFilePath").attr("value",$("#uploadFile").val());
            });

        };
       this.uploadBtn = function(){
           var uploadFilePath = $("#uploadFilePath").val();
          // alert(uploadFilePath);
            if(uploadFilePath == ''){
                alert("请选择excel,再上传");
            }else if(uploadFilePath.lastIndexOf(".xls")  == -1){//可判断以.xls和.xlsx结尾的excel
                alert("只能上传Excel文件");
            }else{
                $(".btn-sm").attr('disabled',true);
                        $("#batchUpload").ajaxSubmit(
                                {
                                    type : 'post',
                                    url : "/bclass/upload",

                                    //data:  //注意只要是写在表单里面的，都不需要加这个属性。在controller中可以根据@RequestParam String str获取到属性值。
                                    contentType : "application/x-www-form-urlencoded; charset=utf-8",
                                    success: function(data) {
                                        //接受到的data还只是一个字符串，需要转成json对象

                                        if(data.status == 0){
                                            alert("上传成功");
                                            location.href = '/bclass/list';
                                        }else{
                                            alert("error");
                                        }
                                        $(".btn-sm").attr('disabled',false);
                                    },
                                    error: function (data)//服务器响应失败处理函数
                                    {
                                        alert("出错");
                                        $(".btn-sm").attr('disabled',false);
                                    }
                                });
                    }

        }
    }




    var user;
    $(function(){
        user = new User();
        user.init();
    });
</script>
</body>
</html>

实体类


import java.util.Date;
import javax.persistence.*;

@Table(name = "class")
public class bclass {
    @Id
    @Column(name = "classId")
    private String classid;

    private String classname;

    @Column(name = "specialtyId")
    private Long specialtyid;

    @Column(name = "chargeTeacher")
    private String chargeteacher;

    @Column(name = "intoYear")
    private Date intoyear;
    @Transient
    private specialty spe;

    @Transient
    private Long departid;
    @Transient
    private String departmentname;
    @Transient
    private String specialtyname;

    public Long getDepartid() {
        return departid;
    }

    public void setDepartid(Long departid) {
        this.departid = departid;
    }

    public String getDepartmentname() {
        return departmentname;
    }

    public void setDepartmentname(String departmentname) {
        this.departmentname = departmentname;
    }

    public String getSpecialtyname() {
        return specialtyname;
    }

    public void setSpecialtyname(String specialtyname) {
        this.specialtyname = specialtyname;
    }

    /**
     * @return classID
     */
    public String getClassid() {
        return classid;
    }

    /**
     * @param classid
     */
    public void setClassid(String classid) {
        this.classid = classid;
    }

    /**
     * @return name
     */
    public String getClassname() {
        return classname;
    }

    /**
     * @param name
     */
    public void setClassname(String name) {
        this.classname = name;
    }

    /**
     * @return specialtyID
     */
    public Long getSpecialtyid() {
        return specialtyid;
    }

    /**
     * @param specialtyid
     */
    public void setSpecialtyid(Long specialtyid) {
        this.specialtyid = specialtyid;
    }

    /**
     * @return chargeTeacher
     */
    public String getChargeteacher() {
        return chargeteacher;
    }

    /**
     * @param chargeteacher
     */
    public void setChargeteacher(String chargeteacher) {
        this.chargeteacher = chargeteacher;
    }

    /**
     * @return intoYear
     */
    public Date getIntoyear() {
        return intoyear;
    }

    /**
     * @param intoyear
     */
    public void setIntoyear(Date intoyear) {
        this.intoyear = intoyear;
    }

    public specialty getSpe() {
        return spe;
    }

    public void setSpe(specialty spe) {
        this.spe = spe;
    }
}

Controller层



@Controller
@RequestMapping("/bclass")
public class bclassController extends BaseController<bclass> {
    @Autowired
    private departmentService departmentService;
    @Autowired
    private bclassService bclassService;
    @Autowired
    private specialtyService specialtyService;
    @Autowired
    private studentService studentService;
    @Autowired
    private bScoreService bScoreService;
    @Autowired
    private  bkService bkservice;
    @Autowired
    private fpService fpService;
    @Autowired
    private phService phService;
    @Autowired
    private HttpSession session;



    @PostMapping("/upload")
    @ResponseBody
    public R upload(@RequestParam(value="uploadFile",required = false)MultipartFile uploadFile) throws Exception {

        List<bclass> list = bclassService.readExcelFile(uploadFile);
        int count = 0;
        if (!list.isEmpty()){
            for (bclass s : list) {
                if (bclassService.getById(s.getClassid())==null) {
                    department d = new department();
                    d.setDepartname(s.getDepartmentname());
//            院不存在时
                    if (departmentService.findOne(d)==null) {
                        departmentService.add(d);
                    }

                    specialty sp = new specialty();
                    sp.setSpecialtyname(s.getSpecialtyname());
                    department listd1 = departmentService.findOne(d);
                    sp.setDepartid(listd1.getDepartid());

//系不存在时
                    if (specialtyService.findOne(sp)==null) {

                        sp.setDepartid(listd1.getDepartid());
                        specialtyService.add(sp);
                    }

                    specialty lists =specialtyService.findOne(sp);
                    s.setSpecialtyid(lists.getSpecialtyid());
                    s.setChargeteacher((String)session.getAttribute("session-user"));
                    s.setIntoyear(new Date());
                    bclassService.add(s);

                }
                count++;
            }
            if(count<list.size()){
                return R.error("出错了。");
            }
            System.out.println(uploadFile);
            return R.success();
        }
        return R.error("出错！！");
    }



}

Service层


public interface bclassService extends IService<bclass,String> {

    Page<bclass> findProduct(@RequestBody bclass product,
                                @RequestParam("pageNum") Integer pageNum,
                                @RequestParam("pageSize") Integer pageSize);

   // void delete1(@RequestBody String  classid);
   // List<bclass> findc(@RequestBody bclass product);
    List<bclass> readExcelFile(@RequestParam(value="file",required = false )MultipartFile file);
}

ServiceImpl层

@Service
public class bclassServiceImpl extends AbstractService<bclass,String > implements bclassService {


    @Autowired(required = false)
    private bclassMapper bclassMapper;
    @Override
    public Mapper<bclass> getMapper() {
        return bclassMapper;
    }


    @Override
    public List<bclass> readExcelFile(@RequestParam(value="file",required = false )MultipartFile file) {
        String result ="";
        //创建处理EXCEL的类
        ReadExcel1 readExcel1=new ReadExcel1();
        //解析excel，获取上传的事件单
        List<bclass> useList = readExcel1.getExcelInfo(file);
        //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
        //和你具体业务有关,这里不做具体的示范
//        for(bScore a:useList){
//
//
//            bScoreMapper.insert(a);
//
//        }
//        if(useList != null && !useList.isEmpty()){
//            result = "上传成功";
//        }else{
//            result = "上传失败";
//        }
        return useList;
    }


}

ReadExcel1工具类


public class ReadExcel1 {
    //总行数
    private int totalRows = 0;
    //总条数
    private int totalCells = 0;
    //错误信息接收器
    private String errorMsg;
    //构造方法
    public ReadExcel1(){}
    //获取总行数
    public int getTotalRows()  { return totalRows;}
    //获取总列数
    public int getTotalCells() {  return totalCells;}
    //获取错误信息
    public String getErrorInfo() { return errorMsg; }

    /**
     * 读EXCEL文件，获取信息集合
     * @param mFile
     * @return
     */
    public List<bclass> getExcelInfo(MultipartFile mFile) {
        String fileName = mFile.getOriginalFilename();//获取文件名
        List<bclass> userList=null;
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            userList = createExcel(mFile.getInputStream(), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return userList;
    }

    /**
     * 根据excel里面的内容读取客户信息
     * @param is 输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return
     * @throws IOException
     *
     *
     */
    public List<bclass> createExcel(InputStream is, boolean isExcel2003) {
        List<bclass> userList=null;
        try{
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            userList = readExcelValue(wb);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return userList;
    }

    /**
     * 读取Excel里面客户的信息
     * @param wb
     * @return
     */
    private List<bclass> readExcelValue(Workbook wb) {
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<bclass> userList = new ArrayList<bclass>();
        // 循环Excel行数
        for (int r = 2; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            bclass user = new bclass();
            // 循环Excel的列
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {
                        //如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                            DecimalFormat df = new DecimalFormat("#");
                            String name = String.valueOf(df.format(cell.getNumericCellValue()));
                            user.setClassid(name);//名称
                        }else{
                            user.setClassid(cell.getStringCellValue());//
                        }
                    } else if (c == 1) {
                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                            String sex = String.valueOf(cell.getNumericCellValue());
                            user.setClassname(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//性别
                        }else{
                            user.setClassname(cell.getStringCellValue());//
                        }
                    } else if (c == 2){
                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                            String age = String.valueOf(cell.getNumericCellValue());
                            user.setSpecialtyname(age.substring(0, age.length()-2>0?age.length()-2:1));//年龄
                        }else{
                            user.setSpecialtyname(cell.getStringCellValue());//
                        }
                    }
                    else if (c == 3){
                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                            String age = String.valueOf(cell.getNumericCellValue());
                            user.setDepartmentname(age.substring(0, age.length()-2>0?age.length()-2:1));//年龄
                        }else{
                            user.setDepartmentname(cell.getStringCellValue());//
                        }
                    }

                }
            }
            // 添加到list
            userList.add(user);
        }
        return userList;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}