通过OleDb使用Microsoft Access数据库引擎将DataTable插入Excel

I came across instructions today for reading data from a Microsoft Excel file using an OleDbConnection on this website: OLE DB Providers

今天我遇到了使用本网站上的OleDbConnection从Microsoft Excel文件中读取数据的说明：OLE DB Providers

This allows me to read in all the data from an Excel file:

这允许我读取Excel文件中的所有数据：

private const string EXCEL_CON =
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};" +
  @"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";

    public DataTable ExtractExcel(string fullFilename, string tableName)
    {
        var table = new DataTable();
        string strCon = string.Format(EXCEL_CON, fullFilename);
        using (var xlConn = new System.Data.OleDb.OleDbConnection(strCon))
        {
            ConnectionState initialState = xlConn.State;
            try
            {
                if ((initialState & ConnectionState.Open) != ConnectionState.Open)
                {
                    xlConn.Open();
                }
                string sql = string.Format("SELECT * FROM `{0}`;", tableName);
                using (var cmd = new System.Data.OleDb.OleDbCommand(sql, xlConn))
                {
                    table.Load(cmd.ExecuteReader());
                }
            }
            finally
            { // it seems like Access does not always close the connection
                if ((initialState & ConnectionState.Open) != ConnectionState.Open)
                {
                    xlConn.Close();
                }
            }
        }
        return table;
    }

When I insert data, Step 1 is to blow away the existing table in the Microsoft Access database in case columns have been added, changed or removed:

当我插入数据时，步骤1是吹掉Microsoft Access数据库中的现有表，以防添加，更改或删除列：

public void InsertExcel(OleDbConnection dbConn, DataTable table) {
  ConnectionState initState = dbConn.State;
  try {
    if ((initState & ConnectionState.Open) != ConnectionState.Open) {
      dbConn.Open();
    }
    string sql = string.Format("SELECT * FROM {0};", table.TableName);
    DataTable original = new DataTable();
    using (OleDbCommand cmd = new OleDbCommand(sql, dbConn)) {
      try {
        original.Load(cmd.ExecuteReader());
      } catch (Exception) { // table does not exist
      }
    }
    if (0 < original.Rows.Count) {
      sql = string.Format("DROP TABLE {0};", table.TableName);
      using (OleDbCommand cmd = new OleDbCommand(sql, dbConn)) {
        cmd.ExecuteNonQuery();
      }
    }
    // ****************
    // CODE NEEDED HERE
    // ****************
  } finally {
    if ((initState & ConnectionState.Open) != ConnectionState.Open) {
      dbConn.Close();
    }
  }
}

After the DROP TABLE command is executed (in the CODE NEEDED HERE section), I need to somehow insert the information in the DataTable.

在执行DROP TABLE命令之后（在此处的代码需要部分中），我需要以某种方式在DataTable中插入信息。

How would I insert the table if I don't have any kind of Primary Key, column names, or column data types?

如果我没有任何类型的主键，列名或列数据类型，我将如何插入表？

The OleDbParameter has has the AddWithValue method that allows data to be added without having to know the data type. Is there something similar that I could use to dump in the entire DataTable (or DataSet)?

OleDbParameter具有AddWithValue方法，该方法允许添加数据而无需知道数据类型。是否有类似的东西可以用来转储整个DataTable（或DataSet）？

1 个解决方案

#1

Here is a static class I have constructed by amalgamating various pieces of code I have either found or developed. This main method for you to take note of is ExportToExcelOleDb which, given a DataSet and a connection string will write that DataSet to the Excel file of your choice, formatted as the DataSet was.

这是我通过合并我发现或开发的各种代码构建的静态类。您需要注意的主要方法是ExportToExcelOleDb，给定DataSet和连接字符串会将该DataSet写入您选择的Excel文件，格式为DataSet。

Note, there is a "bug" with the way the Access Engine writes to Excel - it can't persist data types when writing to the Excel workbook, this means that all data types are written in as Excel TEXT/STRING. Anyway here it is...

请注意，Access Engine写入Excel的方式存在“错误” - 在写入Excel工作簿时无法保留数据类型，这意味着所有数据类型都以Excel TEXT / STRING形式编写。无论如何这里是......

    // Structures used for conversion between data-types.
    private struct ExcelDataTypes
    {
        public const string NUMBER = "NUMBER";
        public const string DATETIME = "DATETIME";
        public const string TEXT = "TEXT"; // also works with "STRING".
    }

    private struct NETDataTypes
    {
        public const string SHORT = "int16";
        public const string INT = "int32";
        public const string LONG = "int64";
        public const string STRING = "string";
        public const string DATE = "DateTime";
        public const string BOOL = "Boolean";
        public const string DECIMAL = "decimal";
        public const string DOUBLE = "double";
        public const string FLOAT = "float";
    }

    /// <summary>
    /// Routine to export a given DataSet to Excel. For each DataTable contained 
    /// in the DataSet the overloaded routine will create a new Excel sheet based 
    /// upon the currently selected DataTable. The proceedure loops through all 
    /// DataRows in the selected DataTable and pushes each one to the specified 
    /// Excel file using ADO.NET and the Access Database Engine (Excel is not a 
    /// prerequisit).
    /// </summary>
    /// <param name="dataSet">The DataSet to be written to Excel.</param>
    /// <param name="connectionString">The connection string.</param>
    /// <param name="fileName">The Excel file name to export to.</param>
    /// <param name="deleteExistFile">Delete existing file?</param>
    public static void ExportToExcelOleDb(DataSet dataSet, string connectionString, 
                                                      string fileName, bool deleteExistFile)
    {
        // Support for existing file overwrite.
        if (deleteExistFile && File.Exists(fileName))
            File.Delete(fileName);
        ExportToExcelOleDb(dataSet, connectionString, fileName);
    }

    /// <summary>
    /// Overloaded version of the above.
    /// </summary>
    /// <param name="dataSet">The DataSet to be written to Excel.</param>
    /// <param name="connectionString">The SqlConnection string.</param>
    /// <param name="fileName">The Excel file name to export to.</param>
    public static bool ExportToExcelOleDb(DataSet dataSet, string connectionString, string fileName)
    {
        try
        {
            // Check for null set.
            if (dataSet != null && dataSet.Tables.Count > 0)
            {
                using (OleDbConnection connection = new OleDbConnection(String.Format(connectionString, fileName)))
                {
                    // Initialise SqlCommand and open.
                    OleDbCommand command = null;
                    connection.Open();

                    // Loop through DataTables.
                    foreach (DataTable dt in dataSet.Tables)
                    {
                        // Build the Excel create table command.
                        string strCreateTableStruct = BuildCreateTableCommand(dt);
                        if (String.IsNullOrEmpty(strCreateTableStruct))
                            return false;
                        command = new OleDbCommand(strCreateTableStruct, connection);
                        command.ExecuteNonQuery();

                        // Puch each row into Excel.
                        for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                        {
                            command = new OleDbCommand(BuildInsertCommand(dt, rowIndex), connection);
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
            return true;
        }
        catch (Exception eX)
        {
            Utils.ErrMsg(eX.Message);
            return false;
        }
    }

    /// <summary>
    /// Build the various sheet names to be inserted based upon the 
    /// number of DataTable provided in the DataSet. This is not required
    /// for XCost purposes. Coded for completion.
    /// </summary>
    /// <param name="connectionString">The connection string.</param>
    /// <returns>String array of sheet names.</returns>
    private static string[] BuildExcelSheetNames(string connectionString)
    {
        // Variables.
        DataTable dt = null;
        string[] excelSheets = null;

        using (OleDbConnection schemaConn = new OleDbConnection(connectionString))
        {
            schemaConn.Open();
            dt = schemaConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            // No schema found.
            if (dt == null)
                return null;

            // Insert 'TABLE_NAME' to sheet name array.
            int i = 0;
            excelSheets = new string[dt.Rows.Count];
            foreach (DataRow row in dt.Rows)
                excelSheets[i++] = row["TABLE_NAME"].ToString();
        }
        return excelSheets;     
    }

    /// <summary>
    /// Routine to build the CREATE TABLE command. The conversion of 
    /// .NET to Excel data types is also handled here (supposedly!). 
    /// Help: http://support.microsoft.com/kb/316934/en-us.
    /// </summary>
    /// <param name="dataTable"></param>
    /// <returns>The CREATE TABLE command string.</returns>
    private static string BuildCreateTableCommand(DataTable dataTable)
    {
        // Get the type look-up tables.
        StringBuilder sb = new StringBuilder();
        Dictionary<string, string> dataTypeList = BuildExcelDataTypes();

        // Check for null data set.
        if (dataTable.Columns.Count <= 0)
            return null;

        // Start the command build.
        sb.AppendFormat("CREATE TABLE [{0}] (", BuildExcelSheetName(dataTable));

        // Build column names and types.
        foreach (DataColumn col in dataTable.Columns)
        {
            string type = ExcelDataTypes.TEXT;
            if (dataTypeList.ContainsKey(col.DataType.Name.ToString().ToLower()))
            {
                type = dataTypeList[col.DataType.Name.ToString().ToLower()];
            }
            sb.AppendFormat("[{0}] {1},", col.Caption.Replace(' ', '_'), type);
        }
        sb = sb.Replace(',', ')', sb.ToString().LastIndexOf(','), 1);
        return sb.ToString();   
    }

    /// <summary>
    /// Routine to construct the INSERT INTO command. This does not currently 
    /// work with the data type miss matches.
    /// </summary>
    /// <param name="dataTable"></param>
    /// <param name="rowIndex"></param>
    /// <returns></returns>
    private static string BuildInsertCommand(DataTable dataTable, int rowIndex)
    {
        StringBuilder sb = new StringBuilder();

        // Remove whitespace.
        sb.AppendFormat("INSERT INTO [{0}$](", BuildExcelSheetName(dataTable));
        foreach (DataColumn col in dataTable.Columns)
            sb.AppendFormat("[{0}],", col.Caption.Replace(' ', '_'));
        sb = sb.Replace(',', ')', sb.ToString().LastIndexOf(','), 1);

        // Write values.
        sb.Append("VALUES (");
        foreach (DataColumn col in dataTable.Columns)
        {
            string type = col.DataType.ToString();
            string strToInsert = String.Empty;
            strToInsert = dataTable.Rows[rowIndex][col].ToString().Replace("'", "''");
            sb.AppendFormat("'{0}',", strToInsert);
            //strToInsert = String.IsNullOrEmpty(strToInsert) ? "NULL" : strToInsert;
            //String.IsNullOrEmpty(strToInsert) ? "NULL" : strToInsert);
        }
        sb = sb.Replace(',', ')', sb.ToString().LastIndexOf(','), 1);
        return sb.ToString();
    }

    /// <summary>
    /// Build the Excel sheet name.
    /// </summary>
    /// <param name="dataTable"></param>
    /// <returns></returns>
    private static string BuildExcelSheetName(DataTable dataTable)
    {
        string retVal = dataTable.TableName;
        if (dataTable.ExtendedProperties.ContainsKey(TABLE_NAME_PROPERTY))
            retVal = dataTable.ExtendedProperties[TABLE_NAME_PROPERTY].ToString();
        return retVal.Replace(' ', '_');
    }

            /// <summary>
    /// Dictionary for conversion between .NET data types and Excel 
    /// data types. The conversion does not currently work, so I am 
    /// puching all data upto excel as Excel "TEXT" type.
    /// </summary>
    /// <returns></returns>
    private static Dictionary<string, string> BuildExcelDataTypes()
    {
        Dictionary<string, string> dataTypeLookUp = new Dictionary<string, string>();

        // I cannot get the Excel formatting correct here!?
        dataTypeLookUp.Add(NETDataTypes.SHORT, ExcelDataTypes.NUMBER);
        dataTypeLookUp.Add(NETDataTypes.INT, ExcelDataTypes.NUMBER);
        dataTypeLookUp.Add(NETDataTypes.LONG, ExcelDataTypes.NUMBER);
        dataTypeLookUp.Add(NETDataTypes.STRING, ExcelDataTypes.TEXT);
        dataTypeLookUp.Add(NETDataTypes.DATE, ExcelDataTypes.DATETIME);
        dataTypeLookUp.Add(NETDataTypes.BOOL, ExcelDataTypes.TEXT);
        dataTypeLookUp.Add(NETDataTypes.DECIMAL, ExcelDataTypes.NUMBER);
        dataTypeLookUp.Add(NETDataTypes.DOUBLE, ExcelDataTypes.NUMBER);
        dataTypeLookUp.Add(NETDataTypes.FLOAT, ExcelDataTypes.NUMBER);
        return dataTypeLookUp;
    }

I hope this is of some use to you.

我希望这对你有用。

Note. I am aware that this type of answer is frowned upon, but I spent a bit of time developing this and in the end had no use for it - moving instead to COM/interop. It is a shame not to share!

注意。我知道这种类型的答案是不受欢迎的，但我花了一些时间来开发它，最后没有用它 - 转而使用COM / interop。不分享是一种耻辱！