Java 实现 Excel 与数据库的数据转换

谁借莪１个温暖的怀抱￠ 2021-06-26 16:06 345阅读 0赞

# 一、准备 #

## （一）环境 ##

需要用到下面两个jar包，可以自行到网上下载或者从我提供的百度云链接下载。

![20190609162911517.png][]

链接: [https://pan.baidu.com/s/1pFLM7VEKM4WNSQ3FBbHKXQ][https_pan.baidu.com_s_1pFLM7VEKM4WNSQ3FBbHKXQ] 提取码: bhje

## （二）数据库 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70][]

# 二、数据库中的数据导入 Excel #

## （一）原理 ##

通过调用工具类，先判断在服务器中指定的文件夹中有没有存在同名的 excle 表，有的话就先删除掉，没有的话，就在指定的文件夹中生成一份新的 excle 表格，再调用浏览器的下载接口，把 excle 表下载到自己电脑上的指定位置，然后删除掉服务器上的 excle 表格。

## （二）源码 ##

### 1. MakeExcel：自定义的工具类 ###

package com.utils;
    
    import java.io.BufferedInputStream;
    import java.io.BufferedOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletResponse;
    
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.format.Alignment;
    import jxl.format.Border;
    import jxl.format.BorderLineStyle;
    import jxl.format.VerticalAlignment;
    import jxl.read.biff.BiffException;
    import jxl.write.Label;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    
    public class MakeExcel {
    
    	/**
    	 * 创建 excel 表格
    	 * 
    	 * @param list
    	 *            一条数据存一个map对象，map对象中存列和值得对应关系
    	 * @param destFile（目标文件）
    	 *            当然就是要存的文件信息,即表格保存的路径
    	 * @param headList
    	 *            很重要，它是列的展示，当然和数据的列要对应不然找不到对应的地方存储
    	 * @param message
    	 *            表格第一行的表头信息
    	 * @throws WriteException
    	 * @throws IOException
    	 */
    	public static void CreateExcelFile(List<Map<String, Object>> list, File destFile, List<String> headList,
    			String message) throws WriteException, IOException {
    		// 获取传入 list 的大小，即有多少条数据
    		int sizeAll = list.size();
    		// 设置每页最大条数 65534 ，求出整数页 wholeNumber
    		int wholeNumber = sizeAll / 65534;
    		// 求出最后一页的条数
    		int yu = sizeAll % 65534;
    		// sheetSize:表示页数
    		// flagList:循环参数，后面输出数据的时候用到
    		int sheetSize = 1;
    		int flagList = 1;
    		// 判断要导出的数据需要存放在几页的 excel 表上
    		if (sizeAll <= 65534) {
    			sheetSize = 1;
    		} else {
    			if (yu > 0) {
    				sheetSize = wholeNumber + 1;
    			} else {
    				sheetSize = wholeNumber;
    			}
    		}
    		// 用 WritableWorkbook 创建一个可读写的工作簿
    		WritableWorkbook book = null;
    		// 以 destFile 为文件名来创建一个 workbook
    		// createWorkbook（） : 参数 destFile 为 new File(D:/example.xls)
    		book = Workbook.createWorkbook(destFile);
    		//
    		if (list.size() == 0) {
    			// list 中没有数据，直接操作空的工作簿
    			// 写进文档
    			book.write();
    		} else {
    			for (int j = 0; j < sheetSize; j++) {
    				int index;
    				System.out.println("*****sheet（excle的左下角）" + j + "*****");
    				// 创建工作表( excel 中的 sheet 表)
    				// createSheet(String str,int n)
    				// String 型参数 str 为 sheet 表的名字，一般命名为"sheet0"或"sheet1"即可
    				// int 型参数 n 代表sheet号，0是第一页，1是第二页，依次类推，打开Excel表格在底端可以看到
    				WritableSheet sheet = book.createSheet(destFile.getName().replace(".xls", "") + j, j);
    
    				// ARIAL : 字体样式 【WritableFont.createFont("宋体") : 宋体字体的设置】
    				// WritableFont.TIMES
    				// 19 :字体大小
    				// WritableFont.BOLD, false 是判断是否为斜体,选择true时为斜体 ,默认为 false
    				WritableFont BoldFont = new WritableFont(WritableFont.createFont("宋体"), 18);
    				WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
    				wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
    				wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
    				wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
    				wcf_center.setWrap(false); // 文字是否换行
    				// wcf_center.setBackground(Colour.LIGHT_GREEN);// 单元格背景颜色
    				for (int i = 0; i < headList.size() + 1; i++) {
    					sheet.setColumnView(i, 20);// 设置第i列的宽度
    				}
    				// 合并首行，设置首行信息
    				// Label():第一个参数表示第几列，第二个参数表示第几行
    				// Label(0, 0, message,wcf_center):表示第0列第0行
    				// message:填入表格的信息
    				// wcf_center:表格样式
    				sheet.mergeCells(0, 0, headList.size() - 1, 0);
    				sheet.addCell(new Label(0, 0, message, wcf_center));
    
    				// 输出列名
    				index = 0;
    				for (String name : headList) {
    					// 上面合并了首行，所有这里在第二行开始输出列名
    					sheet.addCell(new Label(index, 1, name, wcf_center));
    					index++;
    				}
    
    				// 输出数据
    				// i:表示输出的数据为第 i 条
    				// t:表示在 excle 中的第 t+1 行输出数据
    				int i = 0;
    				int t = 2;
    				// flagList 初始值为1，作为循环变量
    				// 当 flagList 大于需要输出的数据条数时，则终止循环
    				while (flagList <= list.size()) {
    					index = 0;
    					if (i < 65534) {
    						for (String name : headList) {
    							sheet.addCell(new Label(index, t, list.get(flagList - 1).get(name) + "", wcf_center));
    							index++;
    						}
    						i++;
    						t++;
    						flagList++;
    					} else {
    						break;
    					}
    				}
    			}
    		}
    		// 写入文档
    		book.write();
    		if (book != null) {
    			// 关闭Excel工作簿对象
    			book.close();
    		}
    	}
    
    	/**
    	 * 调用浏览器接口进行文件下载
    	 *
    	 * @param filepath
    	 *            文件路径
    	 * @param response
    	 */
    	public static void send(String filepath, HttpServletResponse response) {
    		try {
    			File file = new File(filepath);// filepath 是文件地址
    			String filename = file.getName();// 获取日志文件名称
    			InputStream fis = new BufferedInputStream(new FileInputStream(filepath));
    			byte[] buffer = new byte[fis.available()];
    			fis.read(buffer);
    			fis.close();
    			response.reset();
    			// 先去掉文件名称中的空格,然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
    			response.addHeader("Content-Disposition",
    					"attachment;filename=" + new String(filename.replaceAll(" ", "").getBytes("utf-8"), "iso8859-1"));
    			response.addHeader("Content-Length", "" + file.length());
    			OutputStream os = new BufferedOutputStream(response.getOutputStream());
    			response.setContentType("application/octet-stream");
    			os.write(buffer);// 输出文件
    			os.flush();
    			os.close();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    	/**
    	 * 删除单个文件
    	 * 
    	 * @param sPath
    	 *            被删除文件的文件名
    	 * @return 单个文件删除成功返回true，否则返回false
    	 */
    	public static boolean deleteFile(String sPath) {
    		boolean flag = false;
    		File file = new File(sPath);
    		// 路径为文件且不为空则进行删除
    		if (file.isFile() && file.exists()) {
    			file.delete();
    			flag = true;
    		}
    		return flag;
    	}
    }

### 2. Controller 层操作 ###

/**
    	 * 下载用户 excel 表接口
    	 * 
    	 * @param response
    	 * @throws IOException
    	 */
    	@RequestMapping("/download")
    	public void download(HttpServletResponse response) throws IOException {
    		// 指定一个地方临时存放生成的 excel 文件，然后后面调用浏览器接口下载完后再删除
    		String FILEPATH = "d:/test.xls";
    		// 判断 "c:/test.xls" 文件是否已经存在，如果存在就删除掉
    		MakeExcel.deleteFile(FILEPATH);
    		// 首行表头信息
    		List<String> ll = new ArrayList<>();
    		ll.add("用户ID");
    		ll.add("姓名");
    		ll.add("电话");
    		// 获取所有用户信息
    		List<User> allUserList = userService.getUserList();
    		// 将用户的相关信息遍历到 List<Map<String, Object>> 中
    		List<Map<String, Object>> list = new ArrayList<>();
    		for (User user : allUserList) {
    			Map<String, Object> map = new HashMap<>();
    
    			map.put("用户ID", user.getId());
    			map.put("姓名", user.getName());
    			map.put("电话", user.getPhone());
    			list.add(map);
    		}
    		try {
    			// 第一个参数：表格中的数据
    			// 第二个参数：表格保存的路径
    			// 第三个参数：表格第二行的列信息
    			// 第四个参数：表格第一行的表头信息
    			// 参照效果图看会清楚些
    			MakeExcel.CreateExcelFile(list, new File(FILEPATH), ll, "用户表");
    		} catch (WriteException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		// 调用浏览器下载接口
    		MakeExcel.send(FILEPATH, response);
    		// 删除临时存放的 excel 文件
    		boolean deleteFileState = MakeExcel.deleteFile(FILEPATH);
    		if (deleteFileState) {
    			System.out.println("服务器上文件删除成功！！！");
    		} else {
    			System.out.println("服务器上文件删除失败！！！");
    		}
    	}

### 3. service 层和 dao 层 ###

根据 controller 层中的提示自己写，主要是对数据库的操作。

### 4. JSP 页面 ###

## （三）测试 ##

数据库中的数据表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 1][]

数据库中的数据导入到 excel 表中的效果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 2][]

# 三、Excel 中的数据导入数据库 #

## （一）原理 ##

在页面上选中要上传的文件，点击上传按钮。后台接收到上传的 excel 文件，先临时存放到指定的路径，然后再读取临时存放的 excel 文件中数据，读取一条插入一条，或者可以把 excel 中的数据，全部读取出来放到 list 里面，再执行插入数据库操作，等 excel 数据都导入到数据库中后，再把临时存放的 excel 文件给删除掉即可。

提示：在插入数据前，可以检验当前这条数据是否已经存在数据库中，如果存在可以进行更新数据操作。

问：为什么要把 excel 文件临时存放起来？

答：因为浏览器的愿意，无法获取到上传文件的绝对路径。

## （二）源码 ##

### 1. Controller 层操作 ###

/**
    	 * 从 excel 中添加数据到数据库中
    	 * 
    	 * @param filename
    	 * @param request
    	 * @throws IOException
    	 * @throws BiffException
    	 */
    	@RequestMapping(value = "/getexcelfile", produces = "application/json;charset=utf-8")
    	@ResponseBody
    	public String getExcelFile(MultipartFile file, HttpServletRequest request) throws IOException, BiffException {
    		/** 临时存放 excel 文件 **/
    		// 设置相对路径
    		String realPath = request.getSession().getServletContext().getRealPath("/upload");
    		System.out.println(" realPath:" + realPath);
    		// 存放 excel 文件的绝对路径
    		String uploadPath = "";
    		// 获取文件的格式
    		String extention = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
    		// 对格式进行筛选
    		// 仅支持上传 excel 文件，xls 或 xlsx
    		if (extention.equalsIgnoreCase("xls") || extention.equalsIgnoreCase("xlsx")) {
    			File f = new File(realPath);// 在路径下创建文件夹
    			String fileName = file.getOriginalFilename();// 获取上传文件原命名
    			uploadPath = realPath + File.separator + fileName;// 拼接上传路径
    			System.out.println(" uploadPath:" + uploadPath);
    			// 如果指定文件 upload 不存在，则先新建文件夹
    			if (!f.exists()) {
    				f.mkdirs();
    			}
    			file.transferTo(new File(uploadPath));// 文件的传输
    		} else {
    			System.out.println("上传文件格式不对！");
    		}
    
    		/** 读取临时存放的 excel 文件，并将数据导入数据库 **/
    		// 获取 excel 表的文件流
    		File excelFile = new File(uploadPath);
    		Workbook book = Workbook.getWorkbook(excelFile);
    		// 获取 sheet 表
    		// 可以使用下面的写法，"test0" 表示 sheet 表的名字
    		// Sheet rs = book.getSheet("test0");
    		// getSheet(0):表示获取第一张 sheet 表（从左到右数起）
    		Sheet rs = book.getSheet(0);
    		int columns = rs.getColumns();// 得到所有的列
    		int rows = rs.getRows();// 得到所有的行
    		System.out.println(" columns:" + columns + " rows:" + rows);
    		List<User> list = new ArrayList<>();// 临时存放 excel 数据
    		/** 将 excel 数据存放到 List<User> **/
    		// i=2:表示第三行
    		for (int i = 2; i < rows; i++) {
    			User user = new User();
    			// j=0:表示第一列
    			// excel 中默认左边编号也算一列，所以需要从第二列开始获取数据，则下面都使用 j++
    			for (int j = 0; j < columns; j++) {
    				String id = rs.getCell(j++, i).getContents();
    				String name = rs.getCell(j++, i).getContents();
    				String phone = rs.getCell(j++, i).getContents();
    				System.out.println("id=" + id);
    				System.out.println("name=" + name);
    				System.out.println("phone=" + phone);
    				// 将数据 set 进 user 对象中
    				user.setId(Integer.parseInt(id));
    				user.setName(name);
    				user.setPhone(phone);
    				// 将 user 对象添加到 List<User> 里
    				list.add(user);
    			}
    		}
    		/** 将 List<User> 中的数据插入或者更新到数据库 **/
    		User userOne = new User();
    		User tempUser = new User();
    		for (int k = 0; k < list.size(); k++) {
    			userOne = list.get(k);
    			tempUser = userService.selectUserById(userOne.getId());
    			if (tempUser != null) {
    				// 查询返回值 tempUser 不为空，则说明当前这条信息已经存在数据库
    				// 进行数据更新操作即可
    				userService.updataUserByKey(userOne);
    			} else if (tempUser == null) {
    				// tempUser 为空，数据库没有当前信息
    				// 将数据插入即可
    				userService.insertUser(userOne);
    			}
    		}
    
    		/** 删除临时存放的 excel 文件 **/
    		boolean deleteFileState = MakeExcel.deleteFile(realPath + "/test.xls");
    		if (deleteFileState) {
    			System.out.println("服务器上文件删除成功！！！");
    		} else {
    			System.out.println("服务器上文件删除失败！！！");
    		}
    		return " excel 数据更新到数据库成功";
    	}

### 2. service 层和 dao 层 ###

根据 controller 层中的提示自己写，主要是对数据的更新和插入操作。

### 3. JSP 页面 ###

## （三）测试 ##

我上面 excel 数据插入的代码是基于第一部分生成的 excel 格式来写的。所以上传 excel 文件可以使用刚刚下载下来的那一个。然后直接修改或者新增数据即可。如下图所示。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 3][]

数据库中表的数据在导入 excel 数据前的前后对比图：左边是导入数据前，右边是导入数据后。

可以看到，如果数据已经存在，则直接更新数据，如果不存在则插入新的数据。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 4][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 5][]

# 四、Demo #

开发工具：Eclipse

直接 clone 或者下载下来导入即可。

github：[https://github.com/yyzheng1729/excelData][https_github.com_yyzheng1729_excelData]

[20190609162911517.png]: /images/20210626/7de61e7273f54e8c9e2ba98e5321120e.png
[https_pan.baidu.com_s_1pFLM7VEKM4WNSQ3FBbHKXQ]: https://pan.baidu.com/s/1pFLM7VEKM4WNSQ3FBbHKXQ
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70]: /images/20210626/a0a492546bf04d92a174a097d30a9940.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 1]: /images/20210626/a53c6c219f444216a86a65d5993a7100.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 2]: /images/20210626/e88756d9238844019f40b833c3e42db5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 3]: /images/20210626/8c33faad7e6d424c8b5867f28e924ea1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190609170307847.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaWRvbmdfeQ_size_16_color_FFFFFF_t_70 5]: /images/20210626/20f4964f445f495dad470dcc47bec17b.png
[https_github.com_yyzheng1729_excelData]: https://github.com/yyzheng1729/excelData