将excel文件导入数据库,很简单,但是不会的却很愁人,在网上游荡好久,终于找到一个好方法,经过改进,终于可以使用了,方法很简单,也很实用,现与大家分享: 



首先将excel文件导成 csv 文件(方法:另存为,选csv格式就可以了)， 


然后以附件程序即可导入excel文件了 



<?php 

$hostname = 'localhost'; 

$username = 'root'; 


$password = ''; 

$dbname = 'php'; 

$connect = 
mysql_connect($hostname,$username,$password); 


mysql_select_db("$dbname",$connect); 

//使用时请将下面编码自动设计 


//mysql_query("SET character_set_connection=utf8, 
character_set_results=utf8, character_set_client=binary", $connect); 

$f = 
file("1.csv"); 

for($i=0;$i<count($f);$i++) 

{ 


$aaa=explode(",",$f[$i]); 

$str="insert into 
php_content(catid,title,userid,status,searchid) values ('31','$aaa[0]',1,99,6)"; 


mysql_query($str); 

$iid = mysql_insert_id(); 

$str1="insert into 
php_c_news(contentid,fuzeren,tel,address,zhiwei,riqi) values 
('$iid','$aaa[1]','$aaa[2]','$aaa[3]','$aaa[4]','$aaa[5]')"; 


mysql_query($str1); 

$str2="insert into php_content_count(contentid) 
values ('$iid')"; 

} 


?> 

EasyExcel
EasyExcel是什么？

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

github地址
1、EasyExcel+Spring+Mybatis的简单使用
## 1.1新建一个Module，在pom文件添加需要的依赖
 	<dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.58</version>
      <scope>test</scope>
    </dependency>
    <!--Alibaba-EasyExcel-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.1.1</version>
    </dependency>
    <!--单元测试-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <!--mybatis-spring集成依赖-->
 	<dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.2.5.RELEASE</version>
      <scope>compile</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-aspects</artifactId>
      <version>5.2.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>5.2.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.2.5.RELEASE</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.1</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.1</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.20</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.1.12</version>
    </dependency>
    
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.8</version>
      <scope>compile</scope>
    </dependency>

1.2、设计与excel对应的表
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `sex` varchar(3) DEFAULT NULL,
  `email` varchar(32) DEFAULT NULL,
  
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.3在resources目录下创建jdbc.properties，设置jdbc连接数据库的参数
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=abc123

1.4在resources目录下创建applicationContext.xml
 <?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

    <!--把数据库的配置信息写在独立的文件，便于修改数据库的配置内容
    -->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--声明数据源dataSource，作用连接数据库-->
    <bean id="myDataSource" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">

       <!--使用属性配置文件中的数据，语法${key}-->
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!--声明是mybatis中提供的sqlsession-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--set注入，把数据库连接池付给了datasource属性-->
        <property name="dataSource" ref="myDataSource"/>
        <!--mybatis主配置文件的位置
            configLocation的属性是resource类型，读取配置文件的
            的赋值时使用value ，classpath：文件位置
        -->
        <property name="configLocation" value="classpath:mybatis.xml"/>
    </bean>

    <!--创建dao对象使用SQL session的getmapper
        在内部调用getmapeer生成每个到接口的代理对象
    -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--指定SQL sessionfactory对象的id-->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <!--指定包名包名是dao接口所在的包名
            MapperScannerConfigurer会扫描包中接口的所有接口，把每个接口都执行
            一次getmapper方法得到每个dao接口的对象,创建好的对象放到spring容器里
        -->
        <property name="basePackage" value="你的dao接口所在的包名"/>
    </bean>
</beans>

2、创建与数据库表对应的java实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import java.util.Date;
@Getter
@Setter
@ToString
@NoArgsConstructor
public class DemoData {
	/**
     * 用名字去匹配，这里需要注意，如果名字重复，会导致只有一个字段读取到数据
    */
    @ExcelProperty("id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("生日")
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;
    @ExcelProperty("性别")
    private String sex;
    @ExcelProperty("邮箱")
    private String email;
}

3、创建DAO目录，编写与数据库交互的DemoDAO接口
import org.springframework.stereotype.Service;

import java.util.List;
@Service
public interface DemoDAO {
	//保存数据到数据库的方法
    int save(DemoData demoData);
}

3.1在同目录下创建DemoDAO.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="DemoDAO的绝对路径">
    <insert id="save">
        insert into user values (#{id},#{name},#{birthday},#{sex},#{email})
    </insert>
</mapper>

4、创建Service目录，编写操作DAO的ExcelService接口
public interface ExcelService {
    int addUser(DemoData demoData);
}

4.1、实现ExcelService接口，并重写方法操作DAO
import java.util.List;

public class ExcelServiceImpl implements ExcelService {
    private DemoDAO demoDAO;

    public void setDemoDAO(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    @Override
    public int addUser(DemoData demoData) {
        int nums = demoDAO.save(demoData);
        return nums;
    }
}

5、编写DemoDataListener监听类
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import java.util.ArrayList;
import java.util.List;

public class DemoDataListener extends AnalysisEventListener<DemoData> {
    /**
     * 每隔10条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 10;
    List<DemoData> list = new ArrayList<>();
    
    /**
     * 这个每一条数据解析都会来调用
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println("解析到一条数据:"+data);
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }

    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        System.out.println("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        String config = "applicationContext.xml";
        ApplicationContext ac = new ClassPathXmlApplicationContext(config);
        ExcelService demodataService = (ExcelService) ac.getBean("demodataService");
        
        System.out.println("{}条数据，开始存储数据库！"+ list.size());
        for (DemoData demoData : list) {
            System.out.println("list里的："+demoData);
            demodataService.addUser(demoData);
        }
        System.out.println("存储数据库成功！");
    }
}

5.1、回到applicationContext.xml添加bean标签声明Service
 	<!--声明service-->
	<bean id="demodataService" class="ExcelServiceImpl的全限定名称">
        <property name="demoDAO" ref="demoDAO"/>
    </bean>

6、编写测试类
public class Readtest {
    @Test
    public void readTest() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        String fileName ="excel文件地址";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

    }
}

7.测试结果
Excel表格数据：



数据库表导入结果图：

-- =============================================
if object_id('sp_procExcelImport','p')
is not 
null
drop procsp_procExcelImport
go
create PROCEDURE[dbo].[sp_procExcelImport]
    @strExcelPathnvarchar(1000)
= NULL,        
--Excel的绝对路径
    @strExcelSheetNamenvarchar(50)
= 'Sheet1',
--Excel的工作表名,不用加$
符号
    @strSqlTableNamenvarchar(100)
= NULL,      
--导入表名
    @table_isdelbit
= 1,                      
--是否删除表
    @is_pass varchar(20)
output                 
--是否成功
    
AS
set nocount 
on
--开启导入功能
exec sp_configure'show advanced options',1

reconfigure 
exec sp_configure'Ad Hoc Distributed Queries',1

reconfigure
--允许在进程中使用ACE.OLEDB.12
exec master.dbo.sp_MSset_oledb_propN'Microsoft.ACE.OLEDB.12.0',
N'AllowInProcess', 1
--允许动态参数
exec master.dbo.sp_MSset_oledb_propN'Microsoft.ACE.OLEDB.12.0',
N'DynamicParameters', 1
begin try
    if (not 
object_id(@strSqlTableName)
is null) 
and @table_isdel=1
        exec('drop table '+@strSqlTableName)                                                  
    DECLARE @strSql
nvarchar(4000)
    /* 导入*/
    if charindex('.xlsx',@strExcelPath,0)>0
        SET @strExcelPath
= '''Microsoft.Ace.OLEDB.12.0'',''Data Source="'
+ @strExcelPath 
+ '";User ID=Admin;Password=;Extendedproperties=Excel 8.0'''
    else
        SET @strExcelPath
= '''Microsoft.Jet.OLEDB.4.0'',''Data Source="'
+ @strExcelPath 
+ '";UserID=Admin;Password=;Extended properties=Excel 8.0'''
    --将数据存放到临时表(一)
    if (object_id(@strSqlTableName)
is null)
    begin
        SET @strSql
=
            'SELECT *INTO '+@strSqlTableName+' FROM '+
            'OpenDataSource('+
@strExcelPath +')...'+
@strExcelSheetName +'$ '
    end
    else
    begin
        SET @strSql
=
            'insert INTO'+@strSqlTableName+' SELECT *  FROM '+
            'OpenDataSource('+
@strExcelPath +')...'+
@strExcelSheetName +'$ '
    end
    set @is_pass='导入成功！'
    EXEC(@strSql)

end try
begin catch
    set @is_pass='导入失败！'
end catch
go
 
/*
调用存储过程的方法,按顺序的参数依次是：
1.EXCEL的路径,
2.sheet,
3.导进去的表名，
4.是否删除表,
5.输出参数，是否成功
*/
declare @avarchar(20)
exec sp_procExcelImport'd:\job.xlsx','sheet3','job','1',@a
output
select @a

使用当前最新版本phpExcel，版本号为 1.7.7
1。提交excel文件的页面：
..........
选择需导入的明细表：
..........
2。处理excel文件的页面(addata.php)：
//判断文件类型，如果不是"xls"或者"xlsx"，则退出
if ( $_FILES["file"]["type"] == "application/vnd.ms-excel" ){
$inputFileType = 'Excel5';
}
elseif ( $_FILES["file"]["type"] == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ){
$inputFileType = 'Excel2007';
}
else {
echo "Type: " . $_FILES["file"]["type"] . "
";
echo "Invalid file type";
exit();
}
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "
";
exit();
}
$inputFileName = "uploadData/" . $_FILES["file"]["name"];
if (file_exists($inputFileName))
{
//echo $_FILES["file"]["name"] . " already exists. 
";
unlink($inputFileName);    //如果服务器上存在同名文件，则删除
}
else
{
}
move_uploaded_file($_FILES["file"]["tmp_name"],        $inputFileName);
echo "Stored in: " . $inputFileName;
//连接数据库
include_once 'conn.php';
//导入phpExcel
include_once 'Classes/PHPExcel/IOFactory.php';
//设置php服务器可用内存，上传较大文件时可能会用到
ini_set('memory_limit', '1024M');
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$WorksheetInfo = $objReader->listWorksheetInfo($inputFileName);
//读取文件最大行数、列数，偶尔会用到。
$maxRows = $WorksheetInfo[0]['totalRows'];
$maxColumn = $WorksheetInfo[0]['totalColumns'];
//列数可用于粗略判断所上传文件是否符合模板要求
//设置只读，可取消类似"3.08E-05"之类自动转换的数据格式，避免写库失败
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
$sheetData = $objPHPExcel->getSheet(0)->toArray(null,true,true,true);
//excel2003文件，可使用'$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);'
//excel2007文件使用"getActiveSheet()"方法时会提示出错：对non-object使用了"toArray"方法。
//才疏学浅，原因未明。。。
//获取表头，并判断是否符合格式
$keywords = $sheetData[1];
$warning = '上传文件格式不正确，请修改后重新上传！
';
$columns = array ( 'A', 'B', 'C', 'D', 'E', 'F', 'G' );
$keysInFile = array ( 'KEY1', 'KEY2', 'KEY3', 'KEY4', 'KEY5', 'KEY6', 'KEY7' );
foreach( $columns as $keyIndex => $columnIndex ){
if ( $keywords[$columnIndex] != $keysInFile[$keyIndex] ){
echo $warning . $columnIndex . '列应为' . $keysInFile[$keyIndex] . '，而非' . $keywords[$columnIndex];
exit();
}
}
//数据库字段
$keywords = array ( 'key1', 'key2', 'key3', 'key4', 'key5', 'key6', 'key7' );
//设置数据库字符集
mysql_query("set names utf8");
foreach ( $sheetData as $key => $words ){
if ( $key != 1 ){
//忽略表头
$query = "insert into fcopy (" . implode( $keywords, "," ) . ") values ('" . implode ( $words, "','" ) . "')";
if ( ! ($result = mysql_query ( $query ) ) ){
echo '第' . $key . '行数据导入错误' . mysql_error();
exit();
}
}
else {
}
}
echo '
数据导入完毕。
';
?>