EXCEL与Mysql数据相互同步---EXCEL数据上传到Mysql,从Mysql下载数据到EXCEL__2

使用mybatis,maven,springboot

排版比较乱,但是都复制全了,不调了自己记录下
导出是全部导出的需求场景,正常业务中有按时间/操作人/等等等条件的…

依赖

 <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.2.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.0.2.RELEASE</version>
        </dependency>
        <!--Excel导出导入-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10.1</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10.1</version>
        </dependency>
        <!--c3p0,使用mybatis的时候使用-->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
            <type>jar</type>
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

controller

import com.tcl.test.pojo.Content;
import com.tcl.test.service.ContentService;
import com.tcl.test.utils.CommUtil;
import com.tcl.test.utils.ExcelUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;


/**
 * 使用Mybatis完成Excel导入导出操作
 * @author Yangshuxiiang
 *
 */
@RestController
public class MybatisExcelController {
   

	@Autowired
	private ContentService contentService;
	
	/**
	 * Excel数据导入
	 * @param request
	 * @param response
	 * @param filePro
	 * @return
	 */
	@RequestMapping(value="/importExcel")
	public Map<String, Object> importExcel(HttpServletRequest request,HttpServletResponse response, String filePro){
   

		Map<String, Object> map = new HashMap<>();
		String keys[] = {
   "id","category_id","title","url","pic","status","sort_order"};
		try {
   
			List<Map<String,String>> listData = ExcelUtil.getExcelData(request, "file",keys);
			if(listData.size() == 0){
   
				map.put("status",-