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

古城微笑少年丶 2022-03-20 05:12 231阅读 0赞

使用mybatis,maven,springboot

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

依赖

<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.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>
            
            <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>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",-1);
    				map.put("message","上传失败，上传数据必须大于一条");
    				return map;
    			}
    			Content content = new Content();;
    
    			for (Map<String, String> dataMap : listData) { 
    
    				//输出到控制台校验
    				//System.out.println(keys[1] + ":" + dataMap.get(keys[1]));
    				//System.out.println(keys[2] + ":" + dataMap.get(keys[2]));
    				//System.out.println(keys[3] + ":" + dataMap.get(keys[3]));
    				//System.out.println(keys[4] + ":" + dataMap.get(keys[4]));
    				//System.out.println(keys[5] + ":" + dataMap.get(keys[5]));
    				//System.out.println(keys[6] + ":" + dataMap.get(keys[6]));
    
    				content.setCategory_id(dataMap.get(keys[1]));
    				content.setTitle(dataMap.get(keys[2]));
    				content.setUrl(dataMap.get(keys[3]));
    				content.setPic(dataMap.get(keys[4]));
    				content.setStatus(dataMap.get(keys[5]));
    				content.setSort_order(dataMap.get(keys[6]));
    
    				contentService.insertDB(content);
    			}
    
    			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","category_id","title","url","pic","status","sort_order"};
    		
    		List<Map<String,Object>> listMap = new ArrayList<>();
    		Map<String,Object> map = null;
    
    		//查询写出
    		List<Map<String, Object>> list = contentService.findAll();
    
    		for (Map<String, Object> map_d : list) { 
    			//在循环里面新增hashmap用于装每一条数据,防止被覆盖生成同样的数据
    			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;
    	}
    	}

dao

import com.tcl.test.pojo.Content;
    import org.apache.ibatis.annotations.Insert;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Select;
    
    import java.util.List;
    import java.util.Map;
    
    /** * @Author Yangshuixiang'_' * @date 2019/1/25 17:53 * @Email shuixiang.yang@tcl.com */
    
    @Mapper
    public interface ContentDao { 
    
        @Insert("insert into tb_content (category_id,title,url,pic,status,sort_order) values (#{category_id},#{title},#{url},#{pic},#{status},#{sort_order})")
        public void insertDB(Content content);
    
        @Select("select * from tb_content")
        public List<Map<String, Object>> selectAll();
        }
    
    pojo
    
    import java.io.Serializable;
    
    /** * 广告实体类 */
    
    public class Content implements Serializable { 
    
        private Long id;
        private String category_id;
        private String title;
        private String url;
        private String pic;
        private String status;
        private String sort_order;
    
        @Override
        public String toString() { 
            return "Content{" +
                    "id=" + id +
                    ", category_id='" + category_id + '\'' +
                    ", title='" + title + '\'' +
                    ", url='" + url + '\'' +
                    ", pic='" + pic + '\'' +
                    ", status='" + status + '\'' +
                    ", sort_order='" + sort_order + '\'' +
                    '}';
        }
    
        public Long getId() { 
            return id;
        }
    
        public void setId(Long id) { 
            this.id = id;
        }
    
        public String getCategory_id() { 
            return category_id;
        }
    
        public void setCategory_id(String category_id) { 
            this.category_id = category_id;
        }
    
        public String getTitle() { 
            return title;
        }
    
        public void setTitle(String title) { 
            this.title = title;
        }
    
        public String getUrl() { 
            return url;
        }
    
        public void setUrl(String url) { 
            this.url = url;
        }
    
        public String getPic() { 
            return pic;
        }
    
        public void setPic(String pic) { 
            this.pic = pic;
        }
    
        public String getStatus() { 
            return status;
        }
    
        public void setStatus(String status) { 
            this.status = status;
        }
    
        public String getSort_order() { 
            return sort_order;
        }
    
        public void setSort_order(String sort_order) { 
            this.sort_order = sort_order;
        }
        }

service

@Service
    public class ContentService { 
    
        @Autowired
        private ContentDao contentDao;
    
        //导出查询的所有
        public List<Map<String, Object>> findAll(){ 
    
            List<Map<String, Object>> mapList = contentDao.selectAll();
    
            return mapList;
        }
    
        //导入数据
        public void insertDB(Content content){ 
    
            contentDao.insertDB(content);
        }
        }
    
    commutil//
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    /** * @author YangShuixiang`_` *用于转换时间格式 */
    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);
    	}
    }

ExcelUtil/

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 YangShuixiang`_` * @date 2019-1-25 */
    public class ExcelUtil { 
    
        /** * @param listMap 导出的数据 * @param keys 取每列数据的key * @param columnNames 列名 * @return Excel工作簿 */
        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 "";
        }
    }