您的位置: 首页  >  文章  >  linux系统服务器下javaweb项目中实现excel导入数据到数据库中

linux系统服务器下javaweb项目中实现excel导入数据到数据库中

分类: 文章 • 2024-09-20 14:06:52

实现思路：

  1. 用户在浏览器上传xlsx或者xls文件
  2. servlet或controller将文件上传到servlet或controller将文件上传到项目文件夹
  3. 后端工具类将Excel文件解析并转换为csv文件并输出到系统数据库安全路径（"/var/lib/mysql-files/"）
  4. 通过mybatis拼接sql语句将csv数据导入数据库表中
    浏览器界面：
    linux系统服务器下javaweb项目中实现excel导入数据到数据库中

Excel模板
linux系统服务器下javaweb项目中实现excel导入数据到数据库中

jsp中

<!-- 上传考勤表的表单 -->
		<form id="excel" method="post" enctype="multipart/form-data">   
			<input id="file" class="easyui-filebox" name="file" style="width:300px" buttonText="选择本地考勤表" data-options="prompt:'请选择Excel文件'">	
			<input type="submit" value="确认上传">
			<input type="reset" value="重置">
		</form>

js中

$(function(){
		$('#excel').form({    
		    url:"<%=basePath%>attendance/upload",    
		    onSubmit: function() {
				var fileName= $('#file').filebox('getValue'); 
				  //对文件格式进行校验  
                 var d1=/\.[^\.]+$/.exec(fileName);
				if (fileName == "") {  
				      $.messager.alert('请选择将要上传的文件!'); 
				      return false;  
				 }else if(d1!=".xls" && d1!=".xlsx"){
					 $.messager.alert('提示','请选择xls或者xlsx格式文件！','info');  
					 return false; 
				 }
                return true;  
            }, 
		    success:function(data){ 
		    	var data = eval('(' + data + ')');
		    	if(!data["state"]){
		    		console.log(data)
		    		console.log(data.state);
		    		$.messager.alert('失败','文件格式是否规范呢？')
		    	}else{
		    		$.messager.alert('成功','请刷新考勤表')
		    	}
		    },
		    error:function(err){
		    	console.log(err)
		    }
		});
		
		// 规定为Excel文件类型
		$('#file').filebox({    
		    accept:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel'
		})

easyui中文件上传表单设置accept可以规定文件类型，但是用户可以强行上传其他类型的文件。所以还需要在onsubmit方法中再判断一次。

controller中

**
	 * 
	
	 * <p>Title: upload</p>  
	
	 * <p>Description: </p>  
		上传考勤表
	 * @param files	上传的文件数组
	 * @param req	request
	 * @return  
	 * <p> @date 2018年12月10日  </p>
	 */
	@RequestMapping("upload")
	@SystemControllerLog(type=2,description="上传考勤表")
	@ResponseBody
	public JsonData upload(@RequestParam("file")MultipartFile[] files, HttpServletRequest req){
		String upDirRealPath = req.getServletContext().getRealPath("/uploadDir/");
		String msg = null;
		Boolean state = null;
		Integer value = null;
		// 从session中获取操作者
		Admin admin = (Admin)(req.getSession().getAttribute("admin"));
		// 上传到指定路径
		for(MultipartFile file : files){
			// 将项目下的uploaddir文件夹绝对路径，连接文件名，得到文件的绝对路径
			String src = upDirRealPath.concat(file.getOriginalFilename());
				try {
					// 传进绝对路径，上传
					file.transferTo(new File(src));
					// 上传成功后，调用service中的方法，进行转换csv和导入数据库
					value = as.fileInsert(src, admin.getAdminId());
					state = true;
				} catch (IllegalStateException e) {
					msg = "IllegalStateException";
					e.printStackTrace();
				} catch (IOException e) {
					msg = "IOException";
					e.printStackTrace();
				}
		}
		return new JsonData("上传文件", value, msg, state);
	}

Service中

public Integer fileInsert(String src, Long adminId) {
		// 调用csvutil工具类，转换成csv文件并获取csv文件在安全路径下的绝对路径
		String csvPath = CsvUtil.excel(src, adminId);
		// 调用dao层的方法上传到数据库
		return ad.csvInsert(csvPath);
	}

CsvUtil中
写了三个方法。
前两个方法基本相同，是xls与xlsx格式的表格转换成csv文件并返回csv文件路径，
最后一个方法供调用，传入文件名后匹配后缀，决定执行前面的哪一个方法
所以，只需要搞定第一个方法即可

几个特别注意的地方：

  1. 数据库安全路径。linux系统中mysql默认安全上传路径是 “/var/lib/mysql-files/”
  2. linux系统和windows系统中路径用的斜杠符号不同。处理路径字符串时一定要用File.separator
  3. Excel 日期格式的判断。先判断是不是数值型，再判断是否为日期型
  4. 如果excel表第一行不是数据，而是表头，则要跳过第一行。
package com.bs.admin.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.ss.usermodel.Cell; 
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/* * 

* <p>Title: CsvUtil</p>  

* <p>Description: </p>  
	用于将excel文件转换成csv文件
* @author zhengjian  

* <p> @date 2018年12月11日</p>
 */
public class CsvUtil {

	// 输出路径（mysql安全路径。本地默认路径是在c盘ProgramData里面。而linux系统默认路径为"/var/lib/mysql-files/"。这里在枚举中获取到linux系统默认路径"/var/lib/mysql-files/"）
//	 private static String csvDir = "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/";
	private static String csvDir = PropertyUtil.CSVDIR.getName();

	/**
	 * 
	 * 
	 * <p>
	 * Title: excelToCsv
	 * </p>
	 * 
	 * <p>
	 * Description:
	 * </p>
	 * 将上传的xlsx表转为csv格式，然后将Excel文件删除
	 * 
	 * @param src
	 *            <p>
	 * @return 
	 * 			@date 2018年12月7日
	 *            </p>
	 */
	public static String xlsxToCsv(String src, Long adminId) {
		XSSFWorkbook workbook = null;
		BufferedWriter bw = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		File file = new File(src);
		FileInputStream fis = null;
		// linux与windows中的斜杠不同。这里一定要用File.separator。切割文件的绝对路径获取文件名
		String filename = src.substring(src.lastIndexOf(File.separator)+1,src.lastIndexOf("."));
		try {
			fis = new FileInputStream(file);
			workbook = new XSSFWorkbook(fis);
			bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvDir.concat(filename).concat(".csv")), "UTF-8"));
			XSSFSheet sheet = workbook.getSheetAt(0);
//			System.out.println(sheet.getLastRowNum());
			// 第一行表头跳过。一定要跳过，不然格式错误
			for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
				XSSFRow row = sheet.getRow(i);
				if(row == null){
					System.out.println("空行");
					continue;
				}
				for (Cell cell : row) {
					if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING){
						bw.write(cell.getStringCellValue());
					}
					else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
					// Excel 日期格式的判断。先判断是不是数值型，再判断是否为日期型
						if(DateUtil.isCellDateFormatted(cell)){
							bw.write(sdf.format(cell.getDateCellValue()));
						}else{
							bw.write("" + cell.getNumericCellValue());
						}
					}
					else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){
						bw.write("" + cell.getBooleanCellValue());
					}
					bw.write(",");
				}
				// 操作者id
				bw.write(adminId+",");
				bw.newLine();
			}

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (null != bw) {
					bw.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				try {
					if (null != fis) {
						fis.close();
					}
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		//删除Excel文件，返回csv文件路径
		boolean delete = file.delete();
//		System.out.println(delete);
		return csvDir.concat(filename).concat(".csv");
	}
	
	
	/**
	 * 
	
	 * <p>Title: xlsToCsv</p>  
	
	 * <p>Description: </p>  
		将xls文件转换为csv文件
	 * @param fileName  
	 * <p> @date 2018年12月10日  </p>
	 */
	public static String xlsToCsv(String src, Long adminId) {
		HSSFWorkbook workbook = null;
		BufferedWriter bw = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String filename = src.substring(src.lastIndexOf("\\")+1,src.lastIndexOf("."));
//		String csvPath = csvDir.concat(fileName.substring(0,fileName.indexOf("."))).concat(".csv");
		File file = new File(src);
		FileInputStream fis = null;
		try {
			fis = new FileInputStream(file);
			workbook = new HSSFWorkbook();
			bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvDir.concat(filename).concat(".csv")), "UTF-8"));
			HSSFSheet sheet = workbook.getSheetAt(0);
			// 第一行表头跳过 int i = 0; i < sheet.getLastRowNum(); i++
			for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
				HSSFRow row = sheet.getRow(i);
				for (Cell cell : row) {
					if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING){
						bw.write(cell.getStringCellValue());
					}
					else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell)){
							bw.write(sdf.format(cell.getDateCellValue()));
						}else{
							bw.write("" + cell.getNumericCellValue());
							
						}
					}
					else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN){
						bw.write("" + cell.getBooleanCellValue());
					}
					bw.write(",");
				}
				// 操作者id
				bw.write(adminId+",");
				bw.newLine();
			}

		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (null != bw) {
					bw.close();
				}
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				try {
					if (null != fis) {
						fis.close();
					}
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		//删除Excel文件，返回csv文件路径
		boolean delete = file.delete();
		return csvDir.concat(filename).concat(".csv");
	}
	
	/**
	 * 
	
	 * <p>Title: excel</p>  
	
	 * <p>Description: </p>  
		传入路径+文件全名（如“c://excel/data.xlsx”）根据文件后缀判断需要执行哪个方法。返回csv文件路径
	 * @param fileName  
	 * <p> @date 2018年12月10日  </p>
	 */
	public static String excel(String src, Long adminId){
		String suffix = src.substring(src.lastIndexOf(".")+1);
		if(suffix.equals("xlsx")){
			return xlsxToCsv(src, adminId);
		}else{
			return xlsToCsv(src, adminId);
		}
	}
}

dao层

public Integer csvInsert(String src) {
		return am.csvInsert(src);
	}

mapper接口层

Integer csvInsert(@Param("src") String src);

mapper.xml中

<select id="csvInsert" parameterType="map">
    load data local infile #{src}
	into table t_attendance character set gb2312
	fields terminated by ',' optionally enclosed by '"' escaped by '"'
	lines terminated by '\r\n'
	(emp_id,work_day,leave_day,late_day,early_day,att_date,admin_id);
</select>

相关推荐