Query analyzer comes with Excel quickly complete an example of exporting data from SQL Server (General class)
This is an example of using Excel query analyzer (MS Universal Query Analyzer) to complete the data from SQL Server 7.0 or later (through test) to Excel. It is done by two parameters, where the first is the SELECT query statement you want to query analysis. For the best, we give us the data plus a name. Name, passed by the second parameter :) Ok, don't say anything nonsense. Everyone see the code.
using System; using Excel; namespace library {public class Excel Export {public Excel export (string query, string title) {Excel.Application excel; Excel._Workbook xBk; Excel._Worksheet xSt; Excel._QueryTable xQt; string Conn = "Odbc; driver = SQL Server; server = [server address or name]; UID = sa; pwd = [password]; app = [application name (generally operating system name)]; WSID = [Workstation Name (client )]; Database = [database name] "; string select = query statement; Excel = new excel.applicationclass (); xbk = excel.workbooks.add (true); xst = (Excel._worksheet) XBK.Activesheet; Excel. Cells [2, 2] = Title; Xst.get_Range (Excel.cells [2, 2], Excel.cells [2, 2]). Font.bold = true; xst.get_range (Excel.cells [2, 2] Excel.cells [2, 2]). font.name = "black body"; xst.get_range (Excel.cells [2, 2, 2, Excel.cells [2, 2]). font.size = 22; xqt = Xst.QueryTables.Add (conn, xst.get_range (Excel.cells [4, 2], excel.cells [4, 2]), select); xqt.name = "Export example"; xqt.fieldnames = true; xQt.RowNumbers = false; xQt.FillAdjacentFormulas = false; xQt.PreserveFormatting = false; xQt.BackgroundQuery = true; xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells; xQt.AdjustColumnWidth = true; xQt.RefreshPeriod = 0; xQt.PreserveColumnInfo = True; xqt.refresh (xqt.backgroundQuery); Excel.visible = true;}}} All Chinese, don't explain it?
The original data export operation (related connection http://www.9cbs.net/develop/read_article.asp?id=21391), three hundred records, used for more than ten minutes. If this query tool comes with Excel, the 10,000 records are exported, with a time within ten seconds, and can complete the function of the format automatic version. Some people may ask: Is the query statement in Excel and the query statement in SQL Server? Here is a little bit here. With this class, you can directly use the query statement in SQL Server, including the stored procedure to pass SQL Server.
