. NET path (13) export database to Excel

. NET Export Office files (Word,excel) There are two ways I know. One is stored in the exported file under the server record. Use response to output to the browser address bar, open directly, and directly use JavaScript to export tags in HTML.

1.javascript Export

function Allareaword (areaid) {var mydate = new Date ();//Date function try {var fileName = m Ydate.getyear () + (Mydate.getmonth () + 1) + mydate.getdate () + mydate.getminutes () + mydate.getseconds (); File name var areares = document.getElementById ("ShowPage"); Specify the field to enter//New Word object var wordobj = new ActiveXObject ("Word.Application");                Specifies the output type var docobj = WordObj.Documents.Add ("", 0, 1);                var oRange = docobj.range (0, 1);                var sel = Document.body.createTextRange ();                Sel.movetoelementtext (Areares);                Sel.select ();                Sel.execcommand ("Copy");                Orange.paste ();                WordObj.Application.Visible = true; Docobj.saveas ("d://" + FileName + ". Doc")//export file to the specified folder} catch (e) {alert ("Save failed, refresh this The page tries again!
");            } finally {window.location.reload (); }        }

This method requires the browser to create an ActiveX, which requires an ActiveX control with an unknown signature checked. However, this will reduce the security of the browser. So it's always a hint to prompt the user to restore the default security settings when the browser is opened. Suppose you are not very aware that you cannot create an object of Word after you restore or you cannot create a ActiveXObject object. So this approach has a lot of limitations.

2. Using. NET COM Components

function: Export the field in the database to generate a template for importing English speaking scores

 

Querying the specified database fields

<summary>///        Query database table fields///</summary>//        <param name= "tablename" > Database tables name </param >/        //<returns></returns> public DataSet gettablename (string tablename) {            // Query database table field SQL statement            StringBuilder str = new StringBuilder ("Select name from syscolumns WHERE (id = (SELECT ID from Sysobject s WHERE name = ' "+tablename+"));            Returns the table name of the            DataSet ds = DBUtility.DbHelperSQL.Query (str. ToString ());            return ds;        }

Core code: Select name from syscolumns where (id = (SELECT ID from sysobjects where name = tablename)

There are also a lot of interesting SQL statements here. Have not been contacted before. Like what to query out the entire server all database names and so on.

Interested to see: http://www.cnblogs.com/eter/archive/2011/08/15/2139063.html

Import to Excel

<summary>///download templates for imported results///</summary>//<param name= "Sender" ></param>        <param name= "E" ></param> protected void Downloadtemplate (object sender, EventArgs e)            {//Find database field QUERYSPOKENENGLISHSCOREBLL SPOKENENGLISHSCOREBLL = new QUERYSPOKENENGLISHSCOREBLL ();            String strtablename = "T_spokenscores";            DataSet ds = Spokenenglishscorebll.querytablename (strtablename);            ArrayList tablename = new ArrayList (); TableName.            ADD ("study number"); TableName.            ADD ("Score"); for (int i = 0; I < ds. Tables[0]. Rows.Count; i++)//{//tablename. Add (ds. Tables[0].            Rows[i][0]);            }//write database field to Excel//Create Excel object Excel.Application Excel = new Excel.Application ();            Create a Microsoft Excel Workbook Excel.Workbook Nowbook; Infer that the server does not have an Excel application installed if (Excel = = nulL) {throw new Exception ("Server does not have Excel application installed, this feature cannot be used"); }//New workbook assigned to Nowbook Nowbook = Excel.            Workbooks.Add ();            int lengthtablename = 3; for (int i = 1; i < lengthtablename; i++) {nowbook.sheets[1].            Cells[1, I] = tablename[i-1]; }//Save the physical path file specified in Server string strpath = Server.MapPath ("~/uploadfile/downfile") + "/" + "Import spoken score Template"            + ". xls"; Specifies the directory to store.            In fact, a copy of the source file Nowbook.savecopyas (strpath);            Nowbook.close (Savechanges:false);            Nowbook = null; Excel.            Quit ();            Excel = null; directly to the file path, directly open the Response.Redirect (". /..        /uploadfile/downfile/"+" to import the oral score template. xls "); }

In the test process. It is also more interesting to meet some of the problems.

Question one:

An error "exception in HRESULT: 0x800a03ec" occurred while reading an Excel file.

Check out MSDN, as Microsoft's comrades have told me. Is that I add too much of the contents of the cell to the workbook every time, too long.

I tried it. Because I used the Chinese last found not the problem. It's my loop that starts from 0.

NOWBOOK.SHEETS[1]. Cells[1, I], which makes it impossible to create this cell at all, not to mention what to insert. So it seems. Microsoft's Daniel has fooled me!

Ha ha

Question two:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvy2hlbmzhbmdsaw5jzmw=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma== /dissolve/70/gravity/southeast "width=" 529 "height=" 107 ">

Seeing this is the first thing I want to play is probably not a matter of authority, I am very quick to deny this. Because of the code, I have created this XLS file. Finally found that my file was not placed in the specified file path of my visit, so it was right. So I use the server path so that the specified physical path can exist.

By default: Save with Nowbook.saveas is in the My Documents directory.

using the server address

//Save the physical path file specified in the server
String strpath = Server.MapPath ("~/uploadfile/downfile") + "/" + "Import spoken score Template" + ". xls";
Specifies that the directory is stored, in fact, a copy of the source file
Nowbook.savecopyas (strpath);

will be able to.

Summary

in fact, for the latter way is also flawed, when opened will always prompt us whether to open the file format good extension does not match the file. This approach, mainly for Excel such an unmanaged class to implement. In the following links there are other methods, we are interested in the ability to study research.

from Baidu Library:

MSDN: How to use Visual C # 2005 or Visual C #. NET to transfer data to an Excel workbook
HTTP://SUPPORT.MICROSOFT.COM/KB/306023/ZH-CN


How to: Create an EXCEL spreadsheet using COM Interop (C # Programming Guide)
http://msdn.microsoft.com/zh-cn/library/ms173186 (vs.80). aspx


How to implement Microsoft Excel self-proactively in Microsoft Visual C #. NET
HTTP://SUPPORT.MICROSOFT.COM/KB/302084/ZH-CN

General code for creating, opening, reading, writing, and saving Excel in C #
http://hi.baidu.com/zhaochenbo/blog/item/f6d70ff7bf32fa2a730eec39.html

Using Visual Basic and ASP to build an Excel 2003 workbook together with XML
http://msdn.microsoft.com/zh-cn/library/aa203722 (office.11). aspx

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

. NET path (13) export database to Excel