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

EXCEL数据上传到Mysql,从Mysql下载数据到EXCEL
总结一下最近项目中用到的excel和mysql数据同步流程(Springboot里面使用的jdbcTemplate,如果要集成mybatis或者jpa可以更改下写入/查询部分的代码即可),实现EXCEL数据上传到Mysql,从Mysql下载数据到EXCEL,不喜勿喷.
maven工具,使用spring Boot

1.测试表

2.pom.xml

<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.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>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
            <version>2.0.2.RELEASE</version>
        </dependency>

    </dependencies>

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

3.springboot启动

package com.huawei;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
public class ExcelDemoApplication {
   
	public static void main(String[] args) {
   
		SpringApplication.run(ExcelDemoApplication.class, args);
	}
}

4.controller代码

package com.huawei.controller;


import com.huawei.utils.CommUtil;
import com.huawei.utils.ExcelUtil;
import com.huawei.utils.JdbcUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.jdbc.core.JdbcTemplate;
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.*;


/**
 * Excel导入导出操作
 * @author songhj
 *
 */
@RestController
public class IndexViewController {
   

	private JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtils.getDataSource());
	
	/**
	 * 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","dname","dlocal"};
		try {
   
			List<Map<String,String>> listData = ExcelUtil.getExcelData(request, "file",keys);
			if(listData.size() == 0){
   
				map.put("status",-1);
				map.put("message","上传失败，上传数据必须大于一条");
				return map;
			}

			//sql语句
			String sql = "i