使用poi实现excle导入到数据库实战

最新推荐文章于 2022-08-10 15:57:20 发布

⁽⁽ଘlingଓ⁾⁾

最新推荐文章于 2022-08-10 15:57:20 发布

阅读量688

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/ljh602755608/article/details/82748234

版权

java 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

导入到数据库关键在于将excel文件中的数据读取为一个list集合

1.jsp

<%@page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html style="height:100%;background-color:white;">
<head>
<meta name="Copyright" content="" />
<%@include  file="../../../jsp/title.jsp" %>
<jsp:include page="../../../jsp/headCss.jsp"></jsp:include>

</head>
<body  class="horizontal-menu-page"
	style=" background-color: white;" translate="no">
	<div id="dcMain" >
		<form enctype="multipart/form-data" id="batchUpload"  action="/excel/import" method="post" class="form-horizontal">    
           <!-- <button class="btn btn-success btn-xs" id="uploadEventBtn" onclick="" style="height:26px;"  type="button" >选择文件</button>   -->
          <!--  <input type="file" name="file"  style="width:0px;height:0px;" id="uploadEventFile">   -->
           <input type="file" id="uploadEventFile" name="file">
          <!--  <input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 200px;" />  -->                                          
       </form>
       <button type="button" class="btn btn-success btn-sm"  onclick="uploadBtn()" >上传</button>
				
			</div>
	<jsp:include page="../../../jsp/headJs.jsp"></jsp:include>
	
	<script type="text/javascript">
	//上传
	function uploadBtn() {
		var uploadEventFile = $("#uploadEventFile").val();
		if (uploadEventFile == '') {
			alert("请择excel,再上传");
		} else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel  
			alert("只能上传Excel文件");
		} else {
			var url = "import.shtml";
			var formData = new FormData($('form')[0]);
			uploadExc(url,"POST",formData);
		}
	};
	 function uploadExc(url, type, data) {
		$.ajax({
			url : url,
			type : type,
			data : data,
			dataType : "json",
			success : function(data) {
				addRefreshAjax(data);
			},
			/* error : function(result) {
				alert("error");
			}, */
			cache : false,
			contentType : false,
			processData : false
		});
	};
	
	/* var user;
	$(function() {
		user = new User();
		user.init();
	});
   	var User = function() {
		this.init = function() {
			//模拟上传excel  
			$("#uploadEventBtn").unbind("click").bind("click", function() {
				$("#uploadEventFile").click();
			});
			$("#uploadEventFile").bind("change", function() {
				$("#uploadEventPath").attr("value",	$("#uploadEventFile").val());
			});
		};
		//点击上传钮  
		this.uploadBtn = function() {
			var uploadEventFile = $("#uploadEventFile").val();
			if (uploadEventFile == '') {
				alert("请择excel,再上传");
			} else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel  
				alert("只能上传Excel文件");
			} else {
				var url = "excel/import.shtml";
				var formData = new FormData($('form')[0]);
				user.sendAjaxRequest(url, "POST", formData);
			}
		};
		this.sendAjaxRequest = function(url, type, data) {
			$.ajax({
				url : url,
				type : type,
				data : data,
				dataType : "json",
				success : function(result) {
					alert(result.message);
				},
				error : function(result) {
					alert(result.message);
				},
				cache : false,
				contentType : false,
				processData : false
			});
		};
	}; */
       	
       </script>
</body>
</html>

2.controller 控制转向层

package com.youge.shop.action;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.youge.api.service.ProductCenterApiService;
import com.youge.shop.service.ShopService;
import com.youge.util.JSONSerializer;
import com.youge.util.PageUtil;
import com.youge.util.StringTools;
import com.youge.util.UploadUtil;
import com.youge.util.WebUtil;

/**
 * 经销商管理
 *
 */
@Controller
@RequestMapping("shop")
public class ShopController {

	private final static Log log = LogFactory.getLog(ShopController.class);
	@Autowired
	private ShopService shopService;
	@Autowired
	private ProductCenterApiService productCenterService;

	
	/**
	 * 导入excel
	 *
	 * @return
	 */
	@RequestMapping("excel")
	public String excel(HttpServletRequest request, HttpServletResponse response) {
		// 验证是否登录和是否有权限(跳转页面专用)
		String returnUrl = WebUtil.getReturn(request, "");
		// 如果长度大于0则验证失败
		if (returnUrl.length() > 0) {
			return returnUrl;
		}
		try {
		} catch (Exception e) {
			log.error("导入excel:" + e.getMessage());
		}
		return "shop/excel2";
	}
	
	
	// 导入excel
		@RequestMapping(value = "/import", method = RequestMethod.POST)
		@ResponseBody
		public String importExcel(@RequestParam(value = "file", required = false) MultipartFile file,
				HttpServletRequest request, HttpServletResponse response) {
			String ret_data = "";
			// 返回值（ajax专用）
			Map<String, Object> returnMap = WebUtil.getReturnMap_A(request, "");
			// 验证是否登录和是否有权限
			if ((boolean) returnMap.get(WebUtil.RETURM_ERR)) {
				return JSONSerializer.serialize(returnMap);
			}
			try {
				String result = shopService.readExcelFile(file);
				returnMap.put("describe", result);
				if (result.equals("上传成功")) {
					returnMap.put("msg", true);
				} else {
					returnMap.put("msg", false);
				}
			} catch (Exception e) {
				log.error(e.getMessage());
			}
			//4.map转json
			ret_data = JSONSerializer.serialize(returnMap);
			return ret_data;
		}
}

3.service层

public String readExcelFile(MultipartFile file) {
		String result = "";
		// 创建处理EXCEL的类
		ReadExcel2 readExcel = new ReadExcel2();
		// 解析excel，获取上传的事件单
		List<Map<String, Object>> shopList = readExcel.getExcelInfo(file);
		// 至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
		for (Map<String, Object> shop : shopList) {
			Map<String, Object> params = new HashMap<>();
			params.put("shop_province", shop.get("shop_province"));
			params.put("shop_city", shop.get("shop_city"));
			params.put("shop_area", shop.get("shop_area"));
			params.put("shop_name", shop.get("shop_name"));
			params.put("shop_linkman", shop.get("shop_linkman"));
			params.put("shop_phone", shop.get("shop_phone"));
			params.put("shop_address", shop.get("shop_address"));
			int ret = shopDao.createShop(params);
			if (ret == 0) {
				result = "插入数据库失败";
			}
		}
		if (shopList != null && !shopList.isEmpty()) {
			result = "上传成功";
		} else {
			result = "上传失败";
		}
		return result;
	}

4.最后是读取文件工具类

package com.youge.shop.service;

import java.io.IOException;  
import java.io.InputStream;  
import java.util.ArrayList;  
import java.util.HashMap;  
import java.util.List;  
import java.util.Map;  
  
import org.apache.poi.hssf.usermodel.HSSFCell;  
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;  
  
/** 
 *  
 * @author hewangtong 
 *  
 */  
public class ReadExcel2 {  
    // 总行数  
    private int totalRows = 0;  
    // 总条数  
    private int totalCells = 0;  
    // 错误信息接收器  
    private String errorMsg;  
  
    // 构造方法  
    public ReadExcel2() {  
    }  
  
    // 获取总行数  
    public int getTotalRows() {  
        return totalRows;  
    }  
  
    // 获取总列数  
    public int getTotalCells() {  
        return totalCells;  
    }  
  
    // 获取错误信息  
    public String getErrorInfo() {  
        return errorMsg;  
    }  
  
    /** 
     * 读EXCEL文件，获取信息集合 
     *  
     * @param fielName 
     * @return 
     */  
    public List<Map<String, Object>> getExcelInfo(MultipartFile mFile) {  
        String fileName = mFile.getOriginalFilename();// 获取文件名  
//      List<Map<String, Object>> userList = new LinkedList<Map<String, Object>>();  
        try {  
            if (!validateExcel(fileName)) {// 验证文件名是否合格  
                return null;  
            }  
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本  
            if (isExcel2007(fileName)) {  
                isExcel2003 = false;  
            }  
            return createExcel(mFile.getInputStream(), isExcel2003);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        return null;  
    }  
  
    /** 
     * 根据excel里面的内容读取客户信息 
     *  
     * @param is      输入流 
     * @param isExcel2003   excel是2003还是2007版本 
     * @return 
     * @throws IOException 
     */  
    public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003) {  
        try {  
            Workbook wb = null;  
            if (isExcel2003) {// 当excel是2003时,创建excel2003  
                wb = new HSSFWorkbook(is);  
            } else {// 当excel是2007时,创建excel2007  
                wb = new XSSFWorkbook(is);  
            }  
            return readExcelValue(wb);// 读取Excel里面的信息  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        return null;  
    }  
  
    /** 
     * 读取Excel里面客户的信息 
     *  
     * @param wb 
     * @return 
     */  
    private List<Map<String, Object>> readExcelValue(Workbook wb) {  
        // 得到第一个shell  
        Sheet sheet = wb.getSheetAt(0);  
        // 得到Excel的行数  
        this.totalRows = sheet.getPhysicalNumberOfRows();  
        // 得到Excel的列数(前提是有行数)  
        if (totalRows > 1 && sheet.getRow(0) != null) {  
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();  
        }  
        List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();  
        // 循环Excel行数  
        for (int r = 1; r < totalRows; r++) {  
            Row row = sheet.getRow(r);  
            if (row == null) {  
                continue;  
            }  
            // 循环Excel的列  
            Map<String, Object> map = new HashMap<String, Object>();  
            for (int c = 0; c < this.totalCells; c++) {  
                Cell cell = row.getCell(c);  
                if (null != cell) {  
                    if (c == 0) {  
                        // 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
                            String shop_province = String.valueOf(cell.getNumericCellValue());  
                            map.put("shop_province", shop_province.substring(0, shop_province.length() - 2 > 0 ? shop_province.length() - 2 : 1));// 省  
                        } else {  
                            map.put("shop_province", cell.getStringCellValue());// 省
                        }  
                    } else if (c == 1) {  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
                            String shop_city = String.valueOf(cell.getNumericCellValue());  
                            map.put("shop_city",shop_city.substring(0, shop_city.length() - 2 > 0 ? shop_city.length() - 2 : 1));// 市  
                        } else {  
                            map.put("shop_city",cell.getStringCellValue());// 市
                        }  
                    } else if (c == 2) {  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
                            String shop_area = String.valueOf(cell.getNumericCellValue());  
                            map.put("shop_area", shop_area.substring(0, shop_area.length() - 2 > 0 ? shop_area.length() - 2 : 1));// 区
                        } else {  
                            map.put("shop_area", cell.getStringCellValue());// 区
                        }  
                    } else if (c == 3) {  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
                            String shop_name = String.valueOf(cell.getNumericCellValue());  
                            map.put("shop_name", shop_name.substring(0, shop_name.length() - 2 > 0 ? shop_name.length() - 2 : 1));// 经销商名称
                        } else {  
                            map.put("shop_name", cell.getStringCellValue());// 经销商名称
                        }  
                    } else if (c == 4) {  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
                            String shop_linkman = String.valueOf(cell.getNumericCellValue());  
                            map.put("shop_linkman", shop_linkman.substring(0, shop_linkman.length() - 2 > 0 ? shop_linkman.length() - 2 : 1));// 负责人
                        } else {  
                            map.put("shop_linkman", cell.getStringCellValue());// 负责人
                        }  
                    } else if (c == 5) {  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
                            String shop_phone = String.valueOf(cell.getNumericCellValue());  
                            map.put("shop_phone", shop_phone.substring(0, shop_phone.length() - 2 > 0 ? shop_phone.length() - 2 : 1));// 电话
                        } else {  
                            map.put("shop_phone", cell.getStringCellValue());// 电话
                        }  
                    }  else if (c == 6) {  
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {  
                            String shop_address = String.valueOf(cell.getNumericCellValue());  
                            map.put("shop_address", shop_address.substring(0, shop_address.length() - 2 > 0 ? shop_address.length() - 2 : 1));//地址 
                        } else {  
                            map.put("shop_address", cell.getStringCellValue());//地址
                        }  
                    } 
                }  
            }  
            // 添加到list  
            userList.add(map);  
        }  
        return userList;  
    }  
  
    /** 
     * 验证EXCEL文件 
     *  
     * @param filePath 
     * @return 
     */  
    public boolean validateExcel(String filePath) {  
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {  
            errorMsg = "文件名不是excel格式";  
            return false;  
        }  
        return true;  
    }  
  
    // @描述：是否是2003的excel，返回true是2003  
    public static boolean isExcel2003(String filePath) {  
        return filePath.matches("^.+\\.(?i)(xls)$");  
    }  
  
    // @描述：是否是2007的excel，返回true是2007  
    public static boolean isExcel2007(String filePath) {  
        return filePath.matches("^.+\\.(?i)(xlsx)$");  
    }  
      
}

5.本文使用到的jar包

(1)dom4j-1.6.1.jar

(2)poi-3.8-20120326.jar

(3)poi-examples-3.8-20120326.jar

(4)poi-excelant-3.8-20120326.jar

(5)poi-ooxml-3.8-20120326.jar

(6)poi-ooxml-schemas-3.8-20120326.jar

(7)poi-scratchpad-3.8-20120326.jar

(8)xmlbeans-2.6.0.jar

导入时可能遇到的问题:电话号码格式被转换成了1.3E的格式存入数据库,使用以下读取excel的格式即可

if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){   //数字
                            if(String.valueOf(cell.getNumericCellValue()).indexOf("E")==-1){
                                map.put("shop_phone", String.valueOf(cell.getNumericCellValue()));
                            }else {
                            	map.put("shop_phone", new DecimalFormat("#").format(cell.getNumericCellValue()));
                            }
                        }

⁽⁽ଘlingଓ⁾⁾

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
使用poi实现excle导入到数据库实战

导入到数据库关键在于将excel文件中的数据读取为一个list集合1.jsp&lt;%@page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%&gt;&lt;%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c
复制链接

扫一扫

专栏目录