. NET path (13) database Export to Excel

. NET Export to Office documents (word, Excel) There are two methods that I understand. One is to store the exported file under a directory in the server, use the response output to the browser address bar, open directly, and directly use JavaScript to export the tags in the 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 reduces the security of your browser, so you always have the hint to prompt the user to restore the default security settings when you open the browser. This approach has great limitations, assuming you do not know very well that you cannot create an object of Word after you restore or you cannot create a ActiveXObject object.

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 a lot of interesting SQL statements that haven't been touched 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";            Specify 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, encountered some of the problems are more interesting.

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 every time I add to the workbook, the contents of the cell are too much, too long. I tried, 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 that Microsoft's Daniel has fooled me! Haha

Question two:

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

. NET path (13) database Export to Excel