使用Microsoft Access数据库引擎通过OleDb将DataTable插入到Excel中 [英] Insert DataTable into Excel Using Microsoft Access Database Engine via OleDb
问题描述
我今天遇到了使用本网站上的 OleDbConnection
从Microsoft Excel文件读取数据的说明: OLE DB提供程序
I came across instructions today for reading data from a Microsoft Excel file using an OleDbConnection
on this website: OLE DB Providers
这允许我读取所有的数据Excel文件:
This allows me to read in all the data from an Excel file:
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数据库在列添加,更改或删除的情况下:
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:
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();
}
}
}
<$ c $执行c> DROP TABLE 命令(在需要此代码
部分),我需要以某种方式将信息插入到 DataTable 。
After the DROP TABLE
command is executed (in the CODE NEEDED HERE
section), I need to somehow insert the information in the DataTable.
如果我没有任何类型的主键,列名或列数据类型,我将如何插入表格?
How would I insert the table if I don't have any kind of Primary Key, column names, or column data types?
OleDbParameter 已经有 AddWithValue 方法允许添加数据而无需知道数据类型。 有没有类似的东西可以用来转储整个 DataTable
(或 DataSet
)? strong>
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
)?
推荐答案
这是一个静态类
我已经通过合并构建我已经找到或开发了各种代码。这个你要注意的主要方法是 ExportToExcelOleDb
,给定一个 DataSet
和一个连接字符串将写入 DataSet
到您选择的Excel文件,格式为 DataSet
。
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.
注意,Access Engine写入Excel的方式存在错误 - 在写入Excel工作簿时无法保留数据类型,这意味着所有数据类型都以Excel格式写入 TEXT
/ STRING
。无论如何,这是...
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...
// 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。这是一个耻辱不分享!
这篇关于使用Microsoft Access数据库引擎通过OleDb将DataTable插入到Excel中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!