OLEDB
definition:
OLE DB (OLEDB) is a low-level application program interface designed by Microsoft to lead to different data sources. OLE DB not only includes the structured query language (SQL) capabilities of Microsoft-funded standard data interface open database connectivity (ODBC), but also has pathways for other non-SQL data types. As a design of Microsoft's Component Object Model (COM), OLE DB is a set of ways to read and write data (which may have been called channels in the past). Objects in OLE DB mainly include data source objects, stage objects, command objects and line group objects.
Pros and cons:
Advantages: Simple and fast, able to operate higher versions of Excel
Disadvantages: Only limited operations can be performed (read, write)
application
Use OLEDB to write DataTable data into Excel files. If the amount of data is too high, the execution efficiency is very slow. This method is not recommended for large data volumes.
/// <summary>
/// Create DataTable /// </summary>
/// <returns></returns>
private DataTable CreateDataTable()
{
var dt = new DataTable();
("Field1");
("Field2");
return dt;
}
/// <summary>
///Judge Excel version based on file suffix name Link string /// The value of parameter HDR: /// HDR=Yes, which means that the first line is the title and is not used as data. If HDR=NO is used, it means that the first line is not the title and is used as data. /// Value of parameter IMEX: /// When IMEX = 0 is "export mode". The Excel files enabled in this mode can only be used for "write" purposes. /// When IMEX = 1 is "Incoming Mode". The Excel file enabled in this mode can only be used for "reading". /// When IMEX = 2 is "link mode", the Excel file enabled in this mode can support both "read" and "write" purposes. /// </summary>
/// <param name="filepath">File directory and file name</param> /// <param name="pagesize">Number of records per page</param> /// <returns></returns>
public static string GetExcelConnStr(string filepath, out int pagesize)
{
StringBuilder sb = new StringBuilder();
string extension = (filepath);
if (extension == ".xlsx")
{
pagesize = 1048575; //The actual number of lines 1048576 ("Provider=.12.0;Data Source=");
(filepath);
(";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2'");
}
else
{
pagesize = 65535; //The actual number of lines 65536 ("Provider=.4.0;Data Source=");
(filepath);
(";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'");
}
return ();
}
/// <summary>
/// Export Excel using OLEDB /// Excel programs support more than one file type. /// In earlier versions of Excel, the default workbook extension was ".xls". Files in this format can contain up to 255 worksheets, each work page contains 65535 rows (Row) and 256 columns (Column). /// Since the Office 2007 version, the default workbook extension of Excel is ".xlsx". Each work page in this format contains 1048576 rows (Row) and 16384 columns (Column). /// </summary>
/// <param name="dt">Dataset</param> /// <param name="filepath">File directory and file name</param> /// <param name="tablename">SHEET page name</param> /// <param name="pagesize">Number of records per page</param> public static void Export(DataTable dt, string filepath, string tablename, int pagesize = 0)
{
int pagecount = 0;
string connString = GetExcelConnStr(filepath, out pagecount);
if (pagesize > 0)
{
pagecount = pagesize;
}
try
{
using (OleDbConnection con = new OleDbConnection(connString))
{
();
DataTable dtSheet = (, null);
var sheetCount = ;
//Total number of records var recordCount = ;
//Number of columns var columnCount = ;
OleDbCommand cmd = new OleDbCommand();
= con;
//Start pagination int page = (recordCount + pagecount - 1) / pagecount; //Total page count for (int i = 0; i < page; i++)
{
//New Sheet name string tabname = tablename + (i + 1).ToString();
//Get the existing table if (sheetCount > 0)
{
int m = 0;
foreach (DataRow dr in )
{
if (m == i)
{
tabname = dr["TABLE_NAME"].ToString();
= "DROP TABLE [" + tabname + "]";
();// Execute the statement to create a sheet }
m++;
}
}
//Create a new sheet and header StringBuilder createSQL = new StringBuilder();
("CREATE TABLE ").Append("[" + tabname + "]"); //Build a page for every 60,000 items ("(");
for (int j = 0; j < columnCount; j++)
{
("[" + [j].ColumnName + "] text,");
}
createSQL = ( - 1, 1);
(")");
= ();
();
StringBuilder strfield = new StringBuilder();
for (int z = 0; z < columnCount; z++)
{
if (z > 0)
{
(",");
}
("[" + [z].ColumnName + "]");
}
//Prepare to insert data one by one for (int j = i * pagecount; j < (i + 1) * pagecount; j++)
{
if (i == 0 || j < recordCount)
{
StringBuilder insertSQL = new StringBuilder();
StringBuilder strvalue = new StringBuilder();
for (int z = 0; z < columnCount; z++)
{
if (z > 0)
{
(",");
}
("'" + [j][z].ToString() + "'");
}
(" insert into [" + tabname + "]( ")
.Append(())
.Append(") values (").Append(strvalue).Append(") ");
= ();
();
}
}
}
();
}
}
catch (Exception ex)
{
();
}
}Specific usage method:
DataTable dt = CreateDataTable();
for (int i = 0; i < 70000; i++)
{
DataRow dr = ();
dr[0] = i;
dr[1] = "Value" + ();
(dr);
}
string filepath = ("~/") + "data_" + ("yyyyMMddhhmmssffff") + ".xls";
Export(dt, filepath, "data");This is the article about C# using OLEDB to write DataTable into Excel files. For more related C# OLEDB DataTable to write Excel content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!