java使用excel上传下载操作

---

前言

开发的时候想做一个excel上传下载的操作，看了很多方法都比较麻烦，这里用了阿里的easyexcel包，当做记录下方便下次使用~框架是springmvc亲测可用

---

一、导入jar包

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.2.6</version>
    </dependency>

easyexcel是阿里封装好的，直接使用~
在springmvc.xml加入文件上传bean

<!-- SpringMVC上传文件时，需要配置MultipartResolver处理器 -->
    <bean id="multipartResolver"
          class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
        <property name="defaultEncoding" value="UTF-8" />
        <!-- 指定所上传文件的总大小不能超过10485760000B。注意maxUploadSize属性的限制不是针对单个文件，而是所有文件的容量之和 -->
        <property name="maxUploadSize" value="10485760000"></property>
        <property name="maxInMemorySize" value="40960"></property>

    </bean>

二、创建实体类

1.Book.java

根据自己的bean修改~要导入excel包，@ColumnWidth(10)设置宽；
@ExcelProperty(value = “编号”, index = 0)这就是你导出的命名；
@ExcelIgnore忽略导出时不显示

package com.system.po;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

import java.util.Date;

/**
 * @author ke.wenting
 */


public class Book {
    @ColumnWidth(10)
    @ExcelProperty(value = "编号", index = 0)
    private String ISBN;
    @ColumnWidth(10)
    @ExcelProperty(value = "书名" , index = 1)
    private String book_name;
    @ColumnWidth(10)
    @ExcelProperty(value = "作者", index = 2)
    private String author;
    @ColumnWidth(10)
    @ExcelProperty(value ="出版社" , index = 3)
    private String publish;
    @DateTimeFormat("yyyy-MM-dd")
    @ExcelIgnore
    private Date update_time;

    public String getISBN() {
        return ISBN;
    }

    public void setISBN(String ISBN) {
        this.ISBN = ISBN;
    }

    public String getBook_name() {
        return book_name;
    }

    public void setBook_name(String book_name) {
        this.book_name = book_name;
    }


    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public String getPublish() {
        return publish;
    }

    public void setPublish(String publish) {
        this.publish = publish;
    }

  

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

  
    public Date getUpdate_time() {
        return update_time;
    }

    public void setUpdate_time(Date update_time) {
        this.update_time = update_time;
    }
}

三、controller方法

需要导入包
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;

    //导出web下载
    @RequestMapping(value = "/export", method = {RequestMethod.POST})
    public void export2Web(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String excelName = URLEncoder.encode("模板", "UTF-8");//表名
            response.setHeader("Content-disposition", "attachment;filename=" + excelName + ExcelTypeEnum.XLSX.getValue());
            EasyExcel.write(response.getOutputStream(), Book.class).sheet("模板").doWrite(bookService.getAll());
            //sheet名
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //导入
    @RequestMapping(value = "/import", method = {RequestMethod.POST})
    public String ImportExcel(@RequestParam MultipartFile file , HttpServletRequest request) throws IOException {
        BookExample example = new BookExample();
        List<Book> ExcelList = null;//声明实体类
        InputStream inputStream = file.getInputStream();
        ExcelList = EasyExcel.read(inputStream)
                .head(Book.class)
                // 设置sheet,默认读取第一个
                .sheet()
                // 设置标题所在行数
                .headRowNumber(1)
                .doReadSync();

       // System.out.println(ExcelList);
        //持久化excel表
        for (Book excel : ExcelList) {
            bookService.InsertExcel(excel);
            //导入调用service
        }

四、service

 //把Excel插入到数据库
    int InsertExcel(Book record);

serviceimpl.java

  @Override
    public int InsertExcel(Book record) {
        return bookMapper.insert(record);
    }

五、mapper

int insert(Book record);
//插入方法

mapper.xml

<insert id="insert" parameterType="com.system.po.Book" > insert ignore into book (ISBN,book_name,author, publish,update_time ) values (#{ISBN,jdbcType=VARCHAR}, #{book_name,jdbcType=VARCHAR}, #{author,jdbcType=VARCHAR}, #{publish,jdbcType=VARCHAR},,now() ) </insert>

页面js

<form enctype="multipart/form-data" action="<%=request.getContextPath()%>/admin/import" id="formExcel" method="post" onsubmit="return check()" > <div class="input-group"> <input type="file" name="file" id="inputExcel" class="form-control " > <span onclick="document.getElementById('formExcel').submit" id="sub2" >导入 </div> </form> <form action="<%=request.getContextPath()%>/admin/export" style="margin-top: 10px" method="post"> <input type="submit" value="导出"> </form>

<script type="text/javascript">

        //excel导入post
		$("#sub2").click(function () {
			$("#formExcel").submit();
		});

		function check() {
			var excel = document.getElementById("inputExcel").value;
			if (excel == "" || excel == null) {
				alert("请上传文件");
				document.getElementById("inputExcel").focus();
				return false;
			}
		}

	</script>

到这里就结束啦~ 欢迎指正~