导入excel

来源：互联网发布：阿里云 aws对比编辑：程序博客网时间：2024/05/22 09:30

该文章是关于导入excel的前台和后台相关程序。

页面代码：

<html><head><base href="<%=basePath%>"><title>导入excel</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"><meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="导入excel"><script type="text/javascript" src="view/js/jquery-1.8.2.js"></script></head><body>       <form enctype="multipart/form-data" id="batchUpload"  action="/excel/import" method="post" class="form-horizontal">               <button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;"  type="button" >选择文件</button>             <input type="file" name="file"  style="width:0px;height:0px;" id="uploadEventFile">             <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="user.uploadBtn()" >上传</button> </body></html>

js代码：

<script type="text/javascript">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.do";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});};};var user;$(function() {user = new User();user.init();});</script>

后台代码：

controller

import java.util.HashMap;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;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 service.ImportService;@Controller@RequestMapping("/excel")public class ImportExcelController{@Autowired(required=true)private ImportService importService;//导入excel@RequestMapping(value = "/import", method=RequestMethod.POST)@ResponseBodypublic Map<String, Object> importExcel(@RequestParam(value="file",required = false) MultipartFile file, HttpServletRequest request,HttpServletResponse response){Map<String, Object> map = new HashMap<String, Object>();String result = importService.readExcelFile(file);          map.put("message", result);        return map;  }  }

service

import org.springframework.web.multipart.MultipartFile;public interface ImportService {       /**      * 读取excel中的数据,生成list      */      String readExcelFile(MultipartFile file);    }

serviceImpl

package service.Impl;import java.util.List;import java.util.Map;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import org.springframework.web.multipart.MultipartFile;import service.ImportService;import controller.ReadExcel;import dao.UserDao;@Service  public class ImportServiceImpl implements ImportService {@Autowired(required = true)     private UserDao userDao;@Overridepublic String readExcelFile(MultipartFile file) {String result = "";          //创建处理EXCEL的类          ReadExcel readExcel = new ReadExcel();          //解析excel，获取上传的事件单          List<Map<String, Object>> userList = readExcel.getExcelInfo(file);          //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,          for(Map<String, Object> user:userList){        int ret = userDao.insertUser(user.get("name").toString(), user.get("sex").toString(), Integer.parseInt(user.get("age").toString()));        if(ret == 0){        result = "插入数据库失败";        }        }        if(userList != null && !userList.isEmpty()){              result = "上传成功";          }else{              result = "上传失败";          }          return result;  }}

dao

public interface UserDao {public int insertUser(String name, String sex, int age);}

daoImpl

import org.springframework.beans.factory.annotation.Autowired;import org.springframework.dao.DataAccessException;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.stereotype.Component;import dao.UserDao;@Componentpublic class UserDaoImpl implements UserDao {@Autowired(required = true)     private JdbcTemplate jdbcTemplate;@Overridepublic int insertUser(String name, String sex, int age) {String sql = "insert into user(name,sex,age) values('"+ name +"','"+ sex +"',"+age+")"; int ret = 0;try {ret = jdbcTemplate.update(sql);} catch (DataAccessException e) {e.printStackTrace();}return ret;}}

ReadExcel

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 ReadExcel {// 总行数private int totalRows = 0;// 总条数private int totalCells = 0;// 错误信息接收器private String errorMsg;// 构造方法public ReadExcel() {}// 获取总行数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时,创建excel2003wb = new HSSFWorkbook(is);} else {// 当excel是2007时,创建excel2007wb = 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) {// 得到第一个shellSheet 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获得25if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {String name = String.valueOf(cell.getNumericCellValue());map.put("name", name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));// 名称} else {map.put("name", cell.getStringCellValue());// 名称}} else if (c == 1) {if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {String sex = String.valueOf(cell.getNumericCellValue());map.put("sex",sex.substring(0, sex.length() - 2 > 0 ? sex.length() - 2 : 1));// 性别} else {map.put("sex",cell.getStringCellValue());// 性别}} else if (c == 2) {if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {String age = String.valueOf(cell.getNumericCellValue());map.put("age", age.substring(0, age.length() - 2 > 0 ? age.length() - 2 : 1));// 年龄} else {map.put("age", cell.getStringCellValue());// 年龄}}}}// 添加到listuserList.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是2003public static boolean isExcel2003(String filePath) {return filePath.matches("^.+\\.(?i)(xls)$");}// @描述：是否是2007的excel，返回true是2007public static boolean isExcel2007(String filePath) {return filePath.matches("^.+\\.(?i)(xlsx)$");}}

excel表格

导入excel成功后数据库表

阅读全文

1 0