C# 使用 Microsoft Access 数据库引擎通过 OleDb 将数据表插入 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10591600/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert DataTable into Excel Using Microsoft Access Database Engine via OleDb
提问by jp2code
I came across instructions today for reading data from a Microsoft Excel file using an OleDbConnectionon 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 TABLEcommand is executed (in the CODE NEEDED HEREsection), I need to somehow insert the information in the DataTable.
DROP TABLE执行命令后（在CODE NEEDED HERE部分中），我需要以某种方式在DataTable 中插入信息。
How would I insert the table if I don't have any kind of Primary Key, column names, or column data types?
如果我没有任何类型的主键、列名或列数据类型，我将如何插入表？
The OleDbParameterhas has the AddWithValuemethod 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）中转储？
采纳答案by MoonKnight
Here is a static classI have constructed by amalgamating various pieces of code I have either found or developed. This main method for you to take note of is ExportToExcelOleDbwhich, given a DataSetand a connection string will write that DataSetto the Excel file of your choice, formatted as the DataSetwas.
这是static class我通过合并我发现或开发的各种代码而构建的。您需要注意的主要方法是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...
请注意，访问引擎写入 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
/// pushing 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。不分享太可惜了！
