Big Data Import Excel

In peacetime projects, the need to export data to Excel is common, where some common methods are summarized and a large data export implementation is provided.

• OLE DB

Using OLE DB can easily export Excel, the idea is simple, processing excel as access processing, using SQL to build tables, insert data. No more talking, just look at the code.

Export Excel using OLE DB

public static void Export (DataTable dt, string filepath, string tablename)

{

Excel 2003 format

String connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filepath + "; Extended Properties=excel 8.0; ";

Excel 2007 format

String connstring = "Provider=microsoft.ace.oledb.12.0;data source=" + filepath + "; Extended Properties=excel 12.0 Xml; ";

Try

{

using (OleDbConnection con = new OleDbConnection (connstring))

{

Con. Open ();

StringBuilder strSQL = new StringBuilder ();

Strsql.append ("CREATE TABLE"). Append ("[" + tablename + "]");

Strsql.append ("(");

for (int i = 0; i < dt. Columns.count; i++)

{

Strsql.append ("[" + dt. Columns[i]. ColumnName + "] text,");

}

strSQL = Strsql.remove (strsql.length-1, 1);

Strsql.append (")");

OleDbCommand cmd = new OleDbCommand (strsql.tostring (), con);

Cmd. ExecuteNonQuery ();

for (int i = 0; i < dt. Rows.Count; i++)

{

Strsql.clear ();

StringBuilder Strfield = new StringBuilder ();

StringBuilder strvalue = new StringBuilder ();

for (int j = 0; j < dt. Columns.count; J + +)

{

Strfield. Append ("[" + dt. COLUMNS[J]. ColumnName + "]");

strvalue. Append ("'" + dt. ROWS[I][J]. ToString () + "'");

if (j! = dt. COLUMNS.COUNT-1)

{

Strfield. Append (",");

strvalue. Append (",");

}

Else

{

}

}

Cmd.commandtext = Strsql.append ("INSERT INTO [" + TableName + "] (")

. Append (Strfield. ToString ())

. Append (") VALUES ("). Append (strvalue). Append (")"). ToString ();

Cmd. ExecuteNonQuery ();

}

Con. Close ();

}

Console.WriteLine ("OK");

}

catch (Exception ex)

{

Console.WriteLine (ex. Message);

}

}

public static void Export (DataTable dt, string filepath, string tablename)
{
Excel 2003 format
String connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filepath + "; Extended Properties=excel 8.0; ";
Excel 2007 format
String connstring = "Provider=microsoft.ace.oledb.12.0;data source=" + filepath + "; Extended Properties=excel 12.0 Xml; ";
Try
{
using (OleDbConnection con = new OleDbConnection (connstring))
{
Con. Open ();
StringBuilder strSQL = new StringBuilder ();
Strsql.append ("CREATE TABLE"). Append ("[" + tablename + "]");
Strsql.append ("(");
for (int i = 0; i < dt. Columns.count; i++)
{
Strsql.append ("[" + dt. Columns[i]. ColumnName + "] text,");
}
strSQL = Strsql.remove (strsql.length-1, 1);
Strsql.append (")");

OleDbCommand cmd = new OleDbCommand (strsql.tostring (), con);
Cmd. ExecuteNonQuery ();

for (int i = 0; i < dt. Rows.Count; i++)
{
Strsql.clear ();
StringBuilder Strfield = new StringBuilder ();
StringBuilder strvalue = new StringBuilder ();
for (int j = 0; j < dt. Columns.count; J + +)
{
Strfield. Append ("[" + dt. COLUMNS[J]. ColumnName + "]");
strvalue. Append ("'" + dt. ROWS[I][J]. ToString () + "'");
if (j! = dt. COLUMNS.COUNT-1)
{
Strfield. Append (",");
strvalue. Append (",");
}
Else
{
}
}
Cmd.commandtext = Strsql.append ("INSERT INTO [" + TableName + "] (")
. Append (Strfield. ToString ())
. Append (") VALUES ("). Append (strvalue). Append (")"). ToString ();
Cmd. ExecuteNonQuery ();
}
Con. Close ();
}
Console.WriteLine ("OK");
}
catch (Exception ex)
{
Console.WriteLine (ex. Message);
}
}

Whether the generated Excel is 2003 or 2007 is controlled by the connection string (connstring in the Code), and the file name is also passed (XLS or xlsx), otherwise the run is unsuccessful or the resulting file cannot be opened.

• Excel Com

Excel itself provides COM components to operate on Excel, its advantages are obvious, you can control the operation of any cell in Excel (content + format), using OLE DB is not able to do this. This method is handy when you need to use an existing template to generate Excel in your project. However, the method is slow in performance and requires the installation of Excel-related components, which sometimes still have an Excel process in memory after the file is generated. If this is the Web, this method is not recommended, otherwise the administrator and the server will be crazy.

COM generates excel

public static void Export (DataTable dt, string filepath)

{

Excelapp.application myexcel = new Excelapp.application ();

New file

Excelapp._workbook MyBook = MyExcel.Workbooks.Add ();

Open an existing file

Excelapp._workbook MyBook = MyExcel.Workbooks.Open (filepath, Type.Missing, Type.Missing, Type.Missing,

Type.missing,type.missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,

Type.Missing, type.missing,type.missing, Type.Missing);

Open a file in. net4.0, using named arguments and optional arguments

Excelapp._workbook MyBook = MyExcel.Workbooks.Open (Filename:filepath);

Myexcel.visible = true;

Try

{

MyBook. Activate ();

Excelapp._worksheet MySheet = MyBook. Worksheets.add ();

for (int i = 0; i < dt. Rows.Count; i++)

{

for (int j = 0; j < dt. Columns.count; J + +)

{

Excelapp.range cell = Mysheet.get_range ((char) (+ + j). ToString () + (i + 1). ToString ());

Cell. Select ();

Cell. CELLS.FORMULAR1C1 = dt. ROWS[I][J]?? "";

}

}

MyBook. SaveAs (Filename:filepath);

MyBook. Save ();

}

catch (Exception ex)

{

}

Finally

{

MyBook. Close ();

Myexcel.quit ();

Gc. Collect ();

}

}

public static void Export (DataTable dt, string filepath)
{
Excelapp.application myexcel = new Excelapp.application ();
New file
Excelapp._workbook MyBook = MyExcel.Workbooks.Add ();
Open an existing file
Excelapp._workbook MyBook = MyExcel.Workbooks.Open (filepath, Type.Missing, Type.Missing, Type.Missing,
Type.missing,type.missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, type.missing,type.missing, Type.Missing);
Open a file in. net4.0, using named arguments and optional arguments
Excelapp._workbook MyBook = MyExcel.Workbooks.Open (Filename:filepath);
Myexcel.visible = true;
Try
{
MyBook. Activate ();
Excelapp._worksheet MySheet = MyBook. Worksheets.add ();
for (int i = 0; i < dt. Rows.Count; i++)
{
for (int j = 0; j < dt. Columns.count; J + +)
{
Excelapp.range cell = Mysheet.get_range ((char) (+ + j). ToString () + (i + 1). ToString ());
Cell. Select ();
Cell. CELLS.FORMULAR1C1 = dt. ROWS[I][J]?? "";
}
}
MyBook. SaveAs (Filename:filepath);
MyBook. Save ();
}
catch (Exception ex)
{
}
Finally
{
MyBook. Close ();
Myexcel.quit ();
Gc. Collect ();
}
}

When working with COM components, you need to first add a "Microsoft.Office.Interop.Excel" reference to your project. (The Excelapp in the code is the alias I provide to the relevant namespace, using Excelapp = Microsoft.Office.Interop.Excel;)

• Open XML

Excel can also be easily generated using the Open XML SDK provided by Microsoft. (the file suffix of office2007 (Word, Excel, PPT) is modified to ". zip" and the resulting compressed package is extracted and found to be an XML file. ), the specific code I will not post, in the use of the initial load is found to be relatively slow.

It is important to note that this method can only operate on more than Office 2007 versions and does not support 0ffice 2003.http://www.libxl.com/

• Npoi

This is a friend introduced, an open source component, use without the need to install Office(great Advantage), can also be specific to a cell control. Npoi is the. NET version of POI, a set of Java-written libraries that can help developers read and write office 97-2003 files without Microsoft Office installed, including XLS, Doc, ppt, and more. The official website (migrated to Googlecode) provides a number of practical examples, and Atao also gives introductory and introductory tutorials, code I will not copy.

It is important to note that the method can only operate office2003 at this time.

• Large volume data

The above methods are more common, but when it comes to large quantities of data, the first two are too much of a force, especially when using COM components, which are all generated as a single piece. However, QueryTable can be used in COM to improve efficiency.

QueryTable Exporting data in batches

public static void Export (String filepath,string strSQL)

{

Excelapp.application myexcel = new Excelapp.application ();

Excelapp._workbook MyBook = MyExcel.Workbooks.Add ();

Try

{

MyBook. Activate ();

Excelapp._worksheet MySheet = MyBook. Worksheets.add ();

String conn = "Odbc;driver=sql Server; Server=.; Uid=sa;pwd=sa;database=sample; ";

Excelapp.querytable QueryTable = MySheet. QUERYTABLES.ADD (Conn, Mysheet.get_range ("A1"), strSQL);

QueryTable. Refresh (FALSE);

MyBook. SaveAs (Filename:filepath,accessmode:excelapp.xlsaveasaccessmode.xlnochange);

}

catch (Exception ex)

{

Console.WriteLine (ex. Message);

}

Finally

{

MyBook. Close ();

Myexcel.quit ();

}

}

This method is more than a single insert do not know how many times faster, but in my test found instability, the same code the first day is very fast, to the next day is very slow (very large), but also there is the problem of Excel process shut down.

In this case, change a way of thinking, since one of the insertion is relatively slow, then bulk INSERT, SQL statement There is a "select into", can you use this implementation? After finding the data, it is possible to have OpenDataSource(and OPENROWSET) in SQL Server to read the data in Excel directly.

SELECT *
From OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data Source=d:\book.xls; Extended properties=excel 5.0 ') ... [sheet1$];

Can also be written,

Insert into OpenDataSource (' microsoft.jet.oledb.4.0 ',
' Data Source=d:\book.xls; Extended properties=excel 5.0 ') ... [sheet1$]
Select Customer. Name, Product. FullName
from [V_order]

Writing requires the file to exist, and the first row has data (header row).

Before using OpenDataSource, you need to perform

exec sp_configure ' Ad Hoc distributed Queries ', ' 1 '
RECONFIGURE

Otherwise an error will be made: "SQL Server blocked access to the STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries '"

However, the use of OpenDataSource is performed in the SQL Server process, which also causes the generated files to be generated on the SQL Server server side and not directly on the client.

BCP can also be used to export, very fast, but the exported Excel is not the standard format, if the customer does not need a standard format, you can use this to implement.

Now that you can do this in SQL Server, is it possible in access? To find help in this way, it is also possible to find

Select  * from Productin "" [Odbc;driver=sql Server; Server=.; Uid=sa;pwd=sa;database=sample;]

After you run the above code in Access, you can query the data in the SQL Server database, but the connection query (in view) is not supported, and you should pay attention to the processing of reserved words (fields add "[]"). Of course, using in does not only support SQL Server, but it is also supported for other databases, either at MSDN or Acmain_chm.

At the beginning, it has been said that Excel can be used as access when using OLE DB for Excel, so it is only necessary to export OLE DB to the CREATE table in Excel and the SQL statement that inserts the records to replace the above query statement (the code must be changed to select INTO. Otherwise the Excel file is not born), see the Code

 Public Static voidExport (stringstrSQL,stringfilepath) {    //string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + filepath + "; Extended Properties=excel 8.0; ";    stringConnString ="Provider=microsoft.ace.oledb.12.0;data source="+ filepath +"; Extended properties=excel 12.0 Xml;"; Try    {        using(OleDbConnection conn =NewOleDbConnection (connstring)) {Conn.            Open (); OleDbCommand cmd=NewOleDbCommand (strSQL, conn); Cmd.            ExecuteNonQuery (); Conn.        Close (); } Console.WriteLine ("OK"); }    Catch(Exception ex) {Console.WriteLine (ex).    Message); }}

When using the method above, the passed SQL

SELECT * INTO Products
From product
In "" [Odbc;driver=sql Server; Server=.; Uid=sa;pwd=sa;database=sample;]

If you need to add a where, by the end of the sentence is placed behind the SQL (into the table name in Excel is the name of sheet)

Select [FullName] as name, [alias] as abbreviation, [Price] as
Into products
From product
In "" [Odbc;driver=sql Server; Server=.; Uid=sa;pwd=sa;database=sample;]
where Id_product >1
ORDER BY FullName

High-volume data export, you need to pay attention to such a problem, Excel2003 format a sheet only support 65536 line, Excel 2007 is more, is 1048576.

Big Data Import Excel