ASP.NET导入Excel到DataTable时混合数据类型列值丢失问题

使用OleDb实现Excel数据导入DataTable的功能，整体功能运行正常，但当Excel文件中同一列存在不同数据类型的值时，导入到DataTable过程中会出现数据丢失的问题。

举例说明：若A列A1单元格值为数字1，A2单元格值为文本"Text"，导入完成后DataTable中A2单元格会返回空值。

涉及的原有代码如下：

ASPX 前端代码 #

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:FileUpload ID="FileUpload1" runat="server" />  

        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />  
        <br />  
        <asp:Label ID="Label1" runat="server" Text="Has Header ?" />  

        <asp:RadioButtonList ID="rbHDR" runat="server">  
            <asp:ListItem Text="Yes" Value="Yes" Selected="True">  
            </asp:ListItem>  
            <asp:ListItem Text="No" Value="No"></asp:ListItem>  
        </asp:RadioButtonList>  

        <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging" AllowPaging="true">  
        </asp:GridView>  

    </form>
</body>
</html>

后端C#代码 #

using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btnUpload_Click(object sender, EventArgs e)

    {

        if (FileUpload1.HasFile)

        {

            string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

            string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

            string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

            string FilePath = Server.MapPath(FolderPath + FileName);

            FileUpload1.SaveAs(FilePath);

            Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);

        }

    }

    private void Import_To_Grid(string FilePath, string Extension, string isHDR)

    {

        string conStr = "";

        switch (Extension)

        {

            case ".xls": //Excel 97-03  

                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]

                .ConnectionString;

                break;

            case ".xlsx": //Excel 07  

                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]

                .ConnectionString;

                break;

        }

        conStr = String.Format(conStr, FilePath, isHDR);

        OleDbConnection connExcel = new OleDbConnection(conStr);

        OleDbCommand cmdExcel = new OleDbCommand();

        OleDbDataAdapter oda = new OleDbDataAdapter();

        DataTable dt = new DataTable();

        cmdExcel.Connection = connExcel;

        //获取第一个Sheet名称
        connExcel.Open();

        DataTable dtExcelSchema;

        dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

        connExcel.Close();

        //从第一个Sheet读取数据
        connExcel.Open();

        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

        oda.SelectCommand = cmdExcel;

        oda.Fill(dt);

        connExcel.Close();

        //绑定数据到GridView
        GridView1.Caption = Path.GetFileName(FilePath);

        GridView1.DataSource = dt;

        GridView1.DataBind();

    }

    protected void PageIndexChanging(object sender, GridViewPageEventArgs e)

    {

        string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

        string FileName = GridView1.Caption;

        string Extension = Path.GetExtension(FileName);

        string FilePath = Server.MapPath(FolderPath + FileName);

        Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);

        GridView1.PageIndex = e.NewPageIndex;

        GridView1.DataBind();

    }
}

Web.config 配置代码 #

<configuration>

  <appSettings>

    <add key="FolderPath" value="Files/" />

  </appSettings>

  <connectionStrings>
    <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
    <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'" />
  </connectionStrings>

  <system.web>
      <compilation debug="true" targetFramework="4.5.2" />
      <httpRuntime targetFramework="4.5.2" />
    </system.web>
</configuration>

OleDb读取Excel时默认会扫描列的前N行数据判断列的数据类型，和判定类型不匹配的单元格值会被置为空。原有代码存在三个核心问题：

1. 连接字符串缺少IMEX=1参数，未开启混合数据类型列按文本读取的模式
2. .xlsx格式的连接字符串Extended Properties配置错误，错用了.xls对应的Excel 8.0配置
3. 默认配置下驱动仅扫描前8行判断类型，若前8行均为数字，后续行的文本值会被判定为类型不匹配而置空

方案1：修复OleDb配置（无需修改业务逻辑） #

第一步：修改Web.config中的连接字符串 #

添加IMEX=1参数，同时修正.xlsx格式的Extended Properties配置，修改后的连接字符串如下：

<connectionStrings>
  <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'" />
  <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR={1};IMEX=1'" />
</connectionStrings>

IMEX=1作用是告知驱动将包含混合数据类型的列统一按文本类型处理

第二步：修改注册表配置（若第一步修改后仍存在数据丢失） #

默认配置下驱动仅扫描前8行判断列类型，将TypeGuessRows值设为0可让驱动扫描全量行判断类型，避免因采样行数不足导致的类型误判：

• 处理.xls的Jet驱动：定位到注册表路径HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel，将TypeGuessRows值改为0
• 处理.xlsx/.xls的ACE驱动：64位驱动定位到HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel，32位驱动在64位系统下定位到HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel，将TypeGuessRows值改为0

方案2：使用专用Excel解析库（推荐，无环境依赖问题） #

OleDb方案依赖服务器安装对应数据驱动，还需要修改注册表配置，部署成本高。可以改用NPOI、EPPlus等不依赖Office组件的开源库直接解析Excel文件，这类库直接读取单元格原始存储值，不会做自动类型推断，从根源上避免混合类型数据丢失的问题，也不需要额外配置服务器环境。

内容的提问来源于stack exchange，提问作者Sixthsense