Вопрос по excel, c# – Вставьте DataTable в Excel, используя Microsoft Access Database Engine через OleDb

3

Я наткнулся сегодня на инструкции для чтения данных из файла Microsoft Excel с помощьюOleDbConnection на этом сайте:Поставщики OLE DB

Это позволяет мне читать все данные из файла 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;
    }

Когда я вставляю данные, Шаг 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();
    }
  }
}

ПослеDROP TABLE команда выполнена (вCODE NEEDED HERE раздел), мне нужно как-то вставить информацию вDataTable.

Как мне вставить таблицу, если у меня нет какого-либо типа первичного ключа, имен столбцов или типов данных столбцов?

OleDbParameter имеетAddWithValue метод, который позволяет добавлять данные без необходимости знать тип данных.Is there something similar that I could use to dump in the entire DataTable (or DataSet)?

Спасибо Killercam. Я не уверен, что это было раньше. jp2code
@ Remou: Это аккуратная строка кода, которая может хорошо работать, но мы стараемся, чтобы этот класс был модульным. Один метод извлекает данные из местоположения файла и возвращает их как DataSet (несколько листов) или DataTable. Другой метод (этот) вставляет данные из данных. jp2code
можно выполнить запрос по строкамSELECT * INTO NewTable FROM [Excel 8.0;HDR=YES;IMEX=1;DATABASE=Z:\Docs\Book1.xlsm].[Sheet1$] Fionnuala
Я обновил название для ясности. Надеюсь, ты не возражаешь ... Всего наилучшего. MoonKnight

Ваш Ответ

1   ответ
13

static class Я построил путем объединения различных фрагментов кода, которые я либо нашел, либо разработал. Этот основной метод для вас, чтобы принять к сведению этоExportToExcelOleDb который, учитываяDataSet и строка подключения напишет этоDataSet в файл Excel по вашему выбору, отформатированный какDataSet был

Примечание: есть ошибка в способе записи Access Engine в Excel - он не может сохранять типы данных при записи в книгу Excel, это означает, что все типы данных записываются как ExcelTEXT/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;
    }

Надеюсь, это тебе пригодится.

Заметка. Я знаю, что этот тип ответа не одобряется, но я потратил немного времени на его разработку, и в конце концов он не имел смысла - вместо этого я перешел к COM / interop. Стыдно не делиться!

Hi Killercam! Это очень мило. Мне все равно, если это не то, что ТАК нравится. Однако вы сказали, что вместо этого пошли в COM / interop. Было ли это потому, что у вас не было возможности вставить целочисленное значение (например)? Я был бы рад увидеть эту версию, если у вас будет возможность отредактировать ее и добавить другую версию. Я уверен, что со временем вы получите еще +1 голос по этому вопросу. jp2code
Причина перехода наMicrosoft.Office.Interop.Excel было связано с форматированием действительно. Путь OleDb очень быстрый, намного быстрее, чем через COM-интерфейс, но без базового форматирования чисел - он ограничен в косметическом отделе. Процедура, которую я настроил для экспорта COM, является многопоточной, чтобы избежать блокировки / зависания пользовательского интерфейса. Отправьте мне письмо, и я дам вам код ... MoonKnight
Мой адрес электронной почты должен быть указан в моем профиле, я проверил это (я думаю). В любом случае, это [email protected] - если вы напишите мне, я буду рад выслать вам полный класс ... MoonKnight
Я бы с удовольствием, но я не вижу контактную информацию в вашем профиле. У меня есть контактная информация в моем профиле, если вы хотите сначала написать мне по электронной почте: нажмите на ссылку для моего сайта и используйте контактную форму (она должна работать ...). jp2code
Где функция BuildExcelDatatypes ????? Madhu

Похожие вопросы