There are a number of ways SQL Server data can be exported to Excel, such as DTS, SSIS, and call OPENROWSET with SQL statements. Here we develop ideas, use the CLR to generate Excel files, and consider some handy details.
Let me first demonstrate the effect of my implementation, first look at the test statements
--------------------------------------------------------------------------------
Copy Code code as follows:
EXEC bulkcopytoxls ' select * from TestTable ', ' d:/test ', ' testtable ',-1
/*
Start exporting data
File D:/test/testtable.0.xls, total 65,534, size 20, 450,868 bytes
File D:/test/testtable.1.xls, total 65,534, size 20, 101,773 bytes
File D:/test/testtable.2.xls, total 65,534, size 20, 040,589 bytes
File D:/test/testtable.3.xls, total 65,534, size 19, 948,925 bytes
File D:/test/testtable.4.xls, total 65,534, size 20, 080,974 bytes
File D:/test/testtable.5.xls, total 65,534, size 20, 056,737 bytes
File D:/test/testtable.6.xls, total 65,534, size 20, 590,933 bytes
File D:/test/testtable.7.xls, total 26,002, size 8,419,533 bytes
Export Data Completion
-------
Total 484,740 data, time consuming 23812ms
*/
--------------------------------------------------------------------------------
The Bulkcopytoxls stored procedure above is a custom CLR stored procedure. He has four parameters:
The first one is the SQL statement used to get the dataset
The second is the path to the file save
The third is the name of the result set, which we use to name the file
The fourth is to limit how many records a single file can hold, and less than or equal to 0 to represent up to 65,534.
The first three parameters are nothing special, and the last parameter setting allows a dataset to be saved in multiple Excel files. For example, the maximum capacity of traditional Excel is 65,535 data. Our argument here is set to-1 to indicate that when the export reaches this number, the next file is automatically written. If you set the example 100, then each export 100 will automatically write the next file.
In addition, each file can output a field name as a header, so a single file holds up to 65,534 data.
Using Microsoft's public BIFF8 format to generate Excel through binary streams, the server does not need to install Excel components, and performance will not be less than the function of SQL, more than 480,000 data, 150M, with 24 seconds to complete.
Let's look at the CLR code below. Get the DataReader through the SQL statement, and then write the XLS file in batches in biff format.
--------------------------------------------------------------------------------
Copy Code code as follows:
Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.SqlTypes;
Using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
<summary>
Export data
</summary>
<param name= "SQL" ></param>
<param name= "Savepath" ></param>
<param name= "tablename" ></param>
<param name= "Maxrecordcount" ></param>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Bulkcopytoxls (SqlString sql, SqlString Savepath, SqlString tablename, SqlInt32 maxrecordcount)
{
if (SQL. IsNull | | Savepath.isnull | | Tablename.isnull)
{
SqlContext. Pipe.send ("Input information is incomplete!") " );
}
ushort _maxrecordcount = ushort. MaxValue-1;
if (Maxrecordcount.isnull = = False && Maxrecordcount.value < ushort. MAXVALUE&&MAXRECORDCOUNT.VALUE>0)
_maxrecordcount = (ushort) maxrecordcount.value;
Exportxls (SQL. Value, Savepath.value, Tablename.value, _maxrecordcount);
}
<summary>
Querying data, generating files
</summary>
<param name= "SQL" ></param>
<param name= "Savepath" ></param>
<param name= "tablename" ></param>
<param name= "Maxrecordcount" ></param>
private static void Exportxls (String sql, String Savepath, String tablename, System.UInt16 maxrecordcount)
{
if (System.IO.Directory. Exists (Savepath) = = False)
{
System.IO.Directory. CreateDirectory (Savepath);
}
using (SqlConnection conn = new SqlConnection ("Context connection=true"))
{
Conn. Open ();
using (SqlCommand command = conn. CreateCommand ())
{
Command.commandtext = SQL;
using (SqlDataReader reader = command. ExecuteReader ())
{
int i = 0;
int totalcount = 0;
int tick = System.Environment. TickCount;
SqlContext. Pipe.send ("Start exporting data");
while (true)
{
String fileName = String. Format (@ "{0}/{1}.{ 2}.xls ", Savepath, TableName, i++);
int iexp = Write (reader, Maxrecordcount, fileName);
Long size = new System.IO.FileInfo (fileName). Length;
TotalCount + = Iexp;
SqlContext. Pipe.send (String. Format ("file {0}, total {1}, size {2} bytes", FileName, Iexp, size.) ToString ("###,###")));
if (Iexp < maxrecordcount) break;
}
Tick = System.Environment. Tickcount-tick;
SqlContext. Pipe.send ("Export Data Complete");
SqlContext. Pipe.send ("-------");
SqlContext. Pipe.send (String. Format ("Total {0} data, time-consuming {1}ms", totalcount,tick));
}
}
}
}
<summary>
Write cells
</summary>
<param name= "Writer" ></param>
<param name= "obj" ></param>
<param name= "x" ></param>
<param name= "Y" ></param>
private static void WriteObject (Excelwriter writer, object obj, System.UInt16 x, system.uint16 y)
{
String type = obj. GetType (). Name.tostring ();
Switch (type)
{
Case "SqlBoolean":
Case "SqlByte":
Case "SqlDecimal":
Case "SqlDouble":
Case "SqlInt16":
Case "SqlInt32":
Case "SqlInt64":
Case "SqlMoney":
Case "SqlSingle":
if (obj. ToString (). ToLower () = = "Null")
Writer. WriteString (x, y, obj). ToString ());
Else
Writer. Writenumber (x, Y, Convert.) ToDouble (obj. ToString ()));
break;
Default:
Writer. WriteString (x, y, obj). ToString ());
break;
}
}
<summary>
Write a batch of data to an Excel file
</summary>
<param name= "Reader" ></param>
<param name= "Count" ></param>
<param name= "FileName" ></param>
<returns></returns>
private static int Write (SqlDataReader reader, system.uint16 count, String fileName)
{
int iexp = count;
Excelwriter writer = new Excelwriter (fileName);
Writer. BeginWrite ();
for (System.UInt16 j = 0; J < reader.) FieldCount; J + +)
{
Writer. WriteString (0, J, reader.) GetName (j));
}
for (System.UInt16 i = 1; I <= count; i++)
{
if (reader. Read () = = False)
{
Iexp = i-1;
break;
}
for (System.UInt16 j = 0; J < reader.) FieldCount; J + +)
{
WriteObject (writer, reader. Getsqlvalue (j), I, j);
}
}
Writer. EndWrite ();
return iexp;
}
<summary>
Write an object for Excel
</summary>
public class Excelwriter
{
System.IO.FileStream _wirter;
Public Excelwriter (String strpath)
{
_wirter = new System.IO.FileStream (strpath, System.IO.FileMode. OpenOrCreate);
}
<summary>
Write short Array
</summary>
<param name= "Values" ></param>
private void _writefile (system.uint16 [] values)
{
foreach (System.UInt16 v in Values)
{
byte [] b = system.bitconverter. GetBytes (v);
_wirter. Write (b, 0, b.length);
}
}
<summary>
Write file Headers
</summary>
public void BeginWrite ()
{
_writefile (New system.uint16 [] {0x809, 8, 0, 0x10, 0, 0});
}
<summary>
Write End of File
</summary>
public void EndWrite ()
{
_writefile (New system.uint16 [] {0xa, 0});
_wirter. Close ();
}
<summary>
Write a number to cell x,y
</summary>
<param name= "x" ></param>
<param name= "Y" ></param>
<param name= "Value" ></param>
public void Writenumber (System.UInt16 x, system.uint16 y, Double value)
{
_writefile (New system.uint16 [] {0x203, x, y, 0});
byte [] b = system.bitconverter. GetBytes (value);
_wirter. Write (b, 0, b.length);
}
<summary>
Write a character to cell x,y
</summary>
<param name= "x" ></param>
<param name= "Y" ></param>
<param name= "Value" ></param>
public void WriteString (System.UInt16 x, system.uint16 y, String value)
{
byte [] b = System.Text.Encoding. Default.getbytes (value);
_writefile (New system.uint16 [] {0x204, (system.uint16) (B.length + 8), x, y, 0, (system.uint16) b.length});
_wirter. Write (b, 0, b.length);
}
}
};
Compile the above code to testexcel.dll,copy to the server directory. The stored procedure is then deployed through the following SQL statement.
--------------------------------------------------------------------------------
Copy Code code as follows:
CREATE ASSEMBLY testexcelforsqlclr from ' d:/sqlclr/testexcel.dll ' with permission_set = UnSAFE;
--
Go
CREATE proc dbo. Bulkcopytoxls
(
@sql nvarchar (max),
@savePath nvarchar (1000),
@tableName nvarchar (1000),
@bathCount int
)
As EXTERNAL NAME testexcelforsqlclr. StoredProcedures. Bulkcopytoxls
Go
--------------------------------------------------------------------------------
When this technology is in our own hands, we can customize it according to our own needs. For example, I can not write to Excel according to the ordinal number, but according to the value of a field (such as a table has 200 cities 80,000 records) to divide into n files, and this change just adjust the DataReader loop inside the code on the line.
