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

雨点打透心脏的1/2处 2022-04-05 13:37 552阅读 0赞

**EXCEL数据上传到Mysql,从Mysql下载数据到EXCEL**  
总结一下最近项目中用到的excel和mysql数据同步流程(Springboot里面使用的jdbcTemplate,如果要集成mybatis或者jpa可以更改下写入/查询部分的代码即可),实现EXCEL数据上传到Mysql,从Mysql下载数据到EXCEL,不喜勿喷.  
maven工具,使用spring Boot  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1NDM3Mw_size_16_color_FFFFFF_t_70]  
**1.测试表**  
![在这里插入图片描述][2018121217572987.png]  
**2.pom.xml**

<parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.0.2.RELEASE</version>
            <relativePath /> 
        </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 = "insert into dept values (?,?,?)";
    
    			for (Map<String, String> dataMap : listData) { 
    
    				//输出到控制台校验
    				System.out.println(keys[0] + ":" + dataMap.get(keys[0]));
    				System.out.println(keys[1] + ":" + dataMap.get(keys[1]));
    				System.out.println(keys[2] + ":" + dataMap.get(keys[2]));
    
    				jdbcTemplate.update(sql,dataMap.get(keys[0]),dataMap.get(keys[1]),dataMap.get(keys[2]));
    
    			}
    			map.put("listData", listData);
    			map.put("code", 1);
    			map.put("message", "导入成功");
    		} catch (Exception e) { 
    			e.printStackTrace();
    		}
    		return map;
    	}
    
    	/** * 出具导出excel * @param request * @param response * @return * @throws IOException */
    	@RequestMapping(value = "/excelExport", method = {  RequestMethod.GET, RequestMethod.POST })
    	public Map<String,Object> excelExport(HttpServletRequest request, HttpServletResponse response) throws IOException { 
    
    		Map<String,Object> data = new HashMap<>();
    		String fileName = CommUtil.formatTime("yyyyMMddHHmmss", new Date()) +".xls";
    		
    		String columnNames[] = { "编号","名称","地址"};
    		String keys[] = { "id","dname","dlocal"};
    		
    		List<Map<String,Object>> listMap = new ArrayList<>();
    		Map<String,Object> map = new HashMap<>();
    
    		/*测试数据 map.put("id", "50"); map.put("dname", "物流部"); map.put("dlocal", "深圳"); listMap.add(map); map = new HashMap<>(); map.put("id", "60"); map.put("dname", "订单部"); map.put("dlocal", "香港"); listMap.add(map);*/
    
    		//查询写出
    		String sql = "select * from dept";
    
    		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
    
    		for (Map<String, Object> map_d : list) { 
    
    			map = new HashMap<>();
    
    			map.putAll(map_d);
    
    			listMap.add(map);
    		}
    
    		try { 
    			//创建Workbook
    			Workbook wb = ExcelUtil.createWorkBook(listMap, keys, columnNames);
    			//保存路径
    			String savePath = request.getServletContext().getRealPath("/") + File.separator + fileName;
    			// 创建文件流
    			OutputStream stream = new FileOutputStream(savePath);
    			// 写入数据
    			wb.write(stream);
    			// 关闭文件流
    			stream.close();
    			
    			//返回结果
    			data.put("code", 1);
    			String downloadUrl = request.getScheme() + "://"+request.getServerName() + ":" + 
    					request.getServerPort() + "/" + fileName;
    			data.put("download", downloadUrl);
    			data.put("message", "文件流输出成功");
    
    			//控制台输出下载路径
    			System.out.println("\n数据导出成功，下载路径：" + downloadUrl);
    
    		} catch (IOException e) { 
    			System.err.println(e.getMessage());
    			data.put("code", -1);
    			data.put("message", "下载出错");
    			return data;
    		}
    		return data;
    	  }
    	}

**5.commUtils**

package com.huawei.utils;
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    public class CommUtil { 
    
    	public static boolean isNotNull(Object obj) { 
    		if ((obj != null) && (!obj.toString().equals(""))) { 
    			return true;
    		}
    		return false;
    	}
    	
    	public static Date formatDate(String s, String format) { 
    		Date d = null;
    		try { 
    			SimpleDateFormat dFormat = new SimpleDateFormat(format);
    			d = dFormat.parse(s);
    		} catch (Exception localException) { 
    		}
    		return d;
    	}
    	
    	public static String formatShortDate(Object v) { 
    		if (v == null)
    			return null;
    		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
    		return df.format(v);
    	}
    	
    	public static String formatTime(String format, Object v) { 
    		if (v == null)
    			return null;
    		if (v.equals(""))
    			return "";
    		SimpleDateFormat df = new SimpleDateFormat(format);
    		return df.format(v);
    	  }
    	}

**6.ExcelUtils(核心)**

package com.huawei.utils;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    import org.springframework.web.multipart.MultipartHttpServletRequest;
    
    import javax.servlet.http.HttpServletRequest;
    import java.io.IOException;
    import java.lang.reflect.Field;
    import java.lang.reflect.Modifier;
    import java.math.BigDecimal;
    import java.util.*;
    
    /** * Excel导出 * * @author song */
    public class ExcelUtil { 
    
        /** * @param listMap 导出的数据 * @param keys 取每列数据的key * @param columnNames 列名 * @return */
        public static Workbook createWorkBook(List<Map<String, Object>> listMap, String keys[], String columnNames[]) { 
            // 创建excel工作簿
            Workbook wb = new HSSFWorkbook();
            // 创建第一个sheet（页），并命名
            Sheet sheet = wb.createSheet("Sheet1");
            // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
            for (int i = 0; i < keys.length; i++) { 
                sheet.setColumnWidth((short) i, (short) (35 * 100));
            }
    
            /** // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式（用于列名） f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建第二种字体样式（用于值） f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // 设置第一种单元格的样式（用于列名） cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // 设置第二种单元格的样式（用于值） cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); */
            // 创建第一行
            Row row = sheet.createRow((short) 0);
    
            //设置列名
            for (int i = 0; i < columnNames.length; i++) { 
                Cell cell = row.createCell(i);
                cell.setCellValue(columnNames[i]);
                /**cell.setCellStyle(cs);*/
            }
    
            //设置每行每列的值
            for (short i = 0; i < listMap.size(); i++) { 
                // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
                // 创建一行，在页sheet上
                Row row1 = sheet.createRow((short) i + 1);
                // 在row行上创建一个方格
                for (short j = 0; j < keys.length; j++) { 
                    Cell cell = row1.createCell(j);
                    cell.setCellValue(listMap.get(i).get(keys[j]) == null ? " " : listMap.get(i).get(keys[j]).toString());
                    /**cell.setCellStyle(cs2);*/
                }
            }
    
            return wb;
        }
    
    
        /** * 获取导入Excel的数据 * * @param request * @param filePro * @param keys * @return */
        public static List<Map<String, String>> getExcelData(HttpServletRequest request, String filePro, String keys[]) { 
            List<Map<String, String>> data = new ArrayList<>();
            try { 
                if (!CommUtil.isNotNull(filePro)) { 
                    filePro = "file";
                }
                MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
                MultipartFile file = (MultipartFile) multipartRequest.getFile(filePro);
                //获得文件名
                String fileName = file.getOriginalFilename();
                Workbook wb = null;
                if (fileName.endsWith("xlsx")) { 
                    wb = new XSSFWorkbook(file.getInputStream());
                } else { 
                    wb = new HSSFWorkbook(file.getInputStream());
                }
                //获得第一个表单
                Sheet sheet = wb.getSheetAt(0);
                //获得第一个表单的迭代器
                Iterator<Row> rows = sheet.rowIterator();
                int i = 0;
                while (rows.hasNext()) { 
                    i++;
                    Map<String, String> rowMap = new HashMap<>();
                    //获得行数据
                    Row row = rows.next();
                    //跳过头部
                    if (row.getRowNum() == 0) { 
                        continue;
                    }
                    Iterator<Cell> cells = row.cellIterator();
                    //获得行的迭代器
                    int j = 0, k = 0;
                    while (cells.hasNext()) { 
                        Cell cell = cells.next();
                        //类型判断
                        String key = "";
                        //防止越界
                        if (keys.length > cell.getColumnIndex()) { 
                            key = keys[cell.getColumnIndex()];
                        }
                        if (CommUtil.isNotNull(key)) { 
                            String value = formatCell(cell);
                            rowMap.put(key, value);
                            if (!CommUtil.isNotNull(value)) { 
                                j++; //记录空值得数量
                            }
                            k++; //记录多少列
                        }
                    }
                    //如果i=j，说明一行都是空的
                    if (j == k) { 
                        break;
                    } else { 
                        data.add(rowMap);
                    }
                    if (i > 50001) { 
                        System.out.println("\n============导入数据大于五万条，立即停止===============");
                        break;
                    }
                }
            } catch (IOException e) { 
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return data;
        }
    
        /** * 反射机制 map转实体 * * @param classPath java类路径 * @param listData map数据 * @return */
        public static List<Object> factoryMapToJavaObj(String classPath, List<Map<String, String>> listData) { 
            List<Object> list = new ArrayList<Object>();
            try { 
                //获取类
                Class<?> c = Class.forName(classPath);
                //获取类的所有属性
                Field[] fs = c.getDeclaredFields();
                Object valObj = null;
                for (Map<String, String> mapData : listData) { 
                    //获取类的一个实例
                    Object o = c.newInstance();
                    for (Field field : fs) { 
                        String fieldName = field.getName();
                        String fieldType = field.getType().getName();
                        String value = mapData.get(fieldName);
                        if (CommUtil.isNotNull(value)) { 
                            valObj = value.trim();
                        } else { 
                            valObj = null;
                        }
                        if (Modifier.toString(field.getModifiers()).indexOf("static") != -1 || fieldName.equals("id")) { 
                            continue;
                        }
                        if (fieldType.equals("java.math.BigDecimal")) { 
                            if (CommUtil.isNotNull(valObj)) { 
                                if (valObj.toString().indexOf("%") != -1) { 
                                    String newVal = valObj.toString().replaceAll("%", "");
                                    Double num = Double.valueOf(newVal) / 100;
                                    valObj = BigDecimal.valueOf(num);
                                } else { 
                                    valObj = BigDecimal.valueOf(Double.valueOf(value.replace(",", "")));
                                }
                            } else { 
                                valObj = BigDecimal.valueOf(0);
                            }
                        }
                        if (fieldType.equals("java.util.Date") && CommUtil.isNotNull(value)) { 
                            valObj = CommUtil.formatDate(value.replace("/", "-"), "yyyy-MM-dd");
                        }
                        if (fieldType.equals("java.lang.Integer")) { 
                            if (CommUtil.isNotNull(valObj)) { 
                                valObj = Integer.valueOf(valObj.toString());
                            } else { 
                                valObj = Integer.valueOf(0);
                            }
                        }
                        if (fieldType.equals("java.lang.Long")) { 
                            if (CommUtil.isNotNull(valObj)) { 
                                valObj = Long.valueOf(valObj.toString());
                            } else { 
                                valObj = Long.valueOf(0);
                            }
                        }
                        if (CommUtil.isNotNull(valObj)) { 
                            //设置可访问私有属性
                            field.setAccessible(true);
                            //给o对象的属性赋值
                            field.set(o, valObj);
                        }
                    }
                    list.add(o);
                }
            } catch (ClassNotFoundException | InstantiationException | IllegalAccessException e) { 
                e.printStackTrace();
            }
            return list;
        }
    
        public static void main(String[] args) { 
    // Map<String, String> map = new HashMap<>();
    // List l = new ArrayList<>();
    // l.add(map);
    // new ExcelUtil().factoryMapToJavaObj("com.qhiex.foundation.domain.po.system.BusMediInterface", l);
      }
    
    
        /** * 按类型取值 * @param cell * @return */
        public static String formatCell(Cell cell) { 
            if (cell == null) { 
                return "";
            }
    
            switch (cell.getCellType()) { 
    
                //数值格式
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) { 
                        //如果是日期格式
                        return CommUtil.formatShortDate(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    } else { 
                        //字符时
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        return cell.getStringCellValue();
                    }
    
                    //字符串
                case HSSFCell.CELL_TYPE_STRING:
                    return cell.getStringCellValue();
    
                // 公式
                case HSSFCell.CELL_TYPE_FORMULA:
                    return cell.getCellFormula();
    
                // 空白
                case HSSFCell.CELL_TYPE_BLANK:
                    return "";
    
                // 布尔取值
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    return cell.getBooleanCellValue() + "";
    
                //错误类型
                case HSSFCell.CELL_TYPE_ERROR:
                    break;
                    }
                    return "";
                    }
                    }

**7.jdbcUtils**

package com.huawei.utils;
    
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    
    import javax.sql.DataSource;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    /** * @author YangShuixiang`_` * @date 2018/12/12 16:05 */
    public class JdbcUtils { 
    
        private static DataSource ds;
    
        // 2. 创建连接池对象
        static { 
            // 加载配置文件中的数据
            InputStream is = JdbcUtils.class.getResourceAsStream("/druid.properties");
            Properties pp = new Properties();
            try { 
                pp.load(is);
                // 创建连接池，使用配置文件中的参数
                ds = DruidDataSourceFactory.createDataSource(pp);
            } catch (IOException e) { 
                e.printStackTrace();
            } catch (Exception e) { 
                e.printStackTrace();
            }
        }
    
        // 3. 定义公有的得到数据源的方法
        public static DataSource getDataSource() { 
            return ds;
        }
    
        // 4. 定义得到连接对象的方法
        public static Connection getConnection() throws SQLException { 
            return ds.getConnection();
        }
    
        // 5.定义关闭资源的方法
        public static void close(Connection conn, Statement stmt, ResultSet rs) { 
            if (rs != null) { 
                try { 
                    rs.close();
                } catch (SQLException e) { }
            }
    
            if (stmt != null) { 
                try { 
                    stmt.close();
                } catch (SQLException e) { }
            }
    
            if (conn != null) { 
                try { 
                    conn.close();
                } catch (SQLException e) { }
            }
        }
        // 6.重载关闭方法
        public static void close(Connection conn, Statement stmt) { 
            close(conn, stmt, null);
        }
    }

**8.Html页面,随便写个,下面截图仅供参考**  
访问路径:http://127.0.0.1:8080/index.html  
页面a标签需要注意id,与代码区保持一致  
样式要不要无所谓  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1NDM3Mw_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1NDM3Mw_size_16_color_FFFFFF_t_70]: /images/20220405/2da08f2f67274cbbbbc4eb2c71324354.png
[2018121217572987.png]: /images/20220405/205340a5e21444d3807e8fd39436004f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1NDM3Mw_size_16_color_FFFFFF_t_70 1]: /images/20220405/b1cdd4f48384410cbe03ca34674a06a1.png