Export the. NET database to EXCEL

. NET export to Office documents (word, excel) I understand the two methods. one is to store the exported file under a folder on the server, and use response to output the file to the address bar of the browser to open it directly. Also, you can use javascript to export tags in html directly.

1. javascript Export

Function AllAreaWord (areaId) {var myDate = new Date (); // Date function try {var fileName = myDate. getYear () + (myDate. getMonth () + 1) + myDate. getDate () + myDate. getMinutes () + myDate. getSeconds (); // file name var areaRes = document. getElementById ("showPage"); // specify the region to be entered // create the word object var wordObj = new ActiveXObject ("Word. application "); // specify the output type var docObj = wordObj. documents. add ("", 0, 1); var oRange = docObj. rang E (0, 1); var sel = document. body. createTextRange (); sel. moveToElementText (areaRes); sel. select (); sel.exe cCommand ("Copy"); oRange. paste (); wordObj. application. visible = true; docObj. saveAs ("D: //" + fileName + ". doc ") // export the file to the specified directory} catch (e) {alert (" failed to save. Please refresh this page and try again! ") ;}Finally {window. location. reload ();}}

This method requires the browser to create Activex, And the Activex control with unknown signature needs to be checked. However, this will reduce the security of the browser, so there is always a prompt that the user restores the default security settings when opening the browser. If you are not familiar with it, you cannot create an ActiveXObject object after restoration, so you cannot create a word object. Therefore, this method has great limitations.

 

2. Use the. NET com Component

Function: Export fields in the database to generate a template for importing the oral English score.

Query specified database fields

/// <Summary> /// query the database table field /// </summary> /// <param name = "tablename"> database table name </param> // /<returns> </returns> public DataSet GetTableName (string tablename) {// query database table fields SQL statement StringBuilder str = new StringBuilder ("SELECT name FROM syscolumns WHERE (id = (SELECT id FROM sysobjects WHERE name = '" + tablename + "')) "); // The returned table name 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 that have never been used before. Such as querying all the database names on the server.

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

Import to excel

/// <Summary> /// download the template of the import score /// </summary> /// <param name = "sender"> </param> // <param name = "e"> </param> protected void DownLoadTemplate (object sender, eventArgs e) {// find the database Field maid (); string strTablename = "t_spokenscoreblres"; DataSet ds = spokenEnglishScorebll. queryTableName (strTablename); ArrayList tablename = new ArrayLi St (); tablename. add ("student ID"); tablename. add ("score"); // for (int I = 0; I <ds. tables [0]. rows. count; I ++) // {// tablename. add (ds. tables [0]. rows [I] [0]); // write the database fields to excel // create an excel object. application excel = new Excel. application (); // create a Microsoft Excel Workbook Excel. workbook Nowbook; // determines that the Excel application is not installed on the server if (excel = null) {throw new Exception ("the Excel application is not installed on the server, this function cannot be used ");} // create a workbook and assign it to Nowbook = exc El. workbooks. add (); int lengthTableName = 3; for (int I = 1; I <lengthTableName; I ++) {Nowbook. sheets [1]. cells [1, I] = tablename [I-1];} // the physical path file string strpath = Server. mapPath ("~ /UploadFile/DownFile ") +"/"+" Import oral score template "+ ". xls "; // specifies the folder to store. In fact, a copy of the source file Nowbook is actually copied. saveCopyAs (strpath); Nowbook. close (SaveChanges: false); Nowbook = null; excel. quit (); excel = null; // directly redirects to the file path and directly opens Response. redirect (".. /.. /UploadFile/DownFile/"+" Import syntax template.xls ");}

Some problems encountered during the test are also interesting.

 

Question 1:

 

When reading an Excel file, the error "exception in HRESULT: 0X800A03EC" is displayed ".

After reading MSDN, Microsoft's colleagues told me this. That is, every time I add too much content to cells in the work thin, it is too long. I tried it because the problem was not found in Chinese. It starts from 0 in my loop. Nowbook. Sheets [1]. Cells [1, I]. As a result, the cell cannot be created at all, so let alone insert content. In this case, I was fooled by Microsoft's Daniel! Haha

 

Question 2:

 

 

The first thing I want to do is whether it is a permission issue. I quickly deny this. According to the code, I have created this xls file. Finally, I found that my file was not placed in the specified path of the file I accessed. This is right. So I use the server path so that the specified physical path can exist.

 

By default, Nowbook. saveas is saved in my document folder.

 

 

Use server address

 

// Save the specified physical path file in the server
String strpath = Server. MapPath ("~ /UploadFile/DownFile ") +"/"+" Import oral score template "+". xls ";
// Specifies the folder to store. In fact, copying a source file
Nowbook. SaveCopyAs (strpath );

You can.

Summary

In fact, the latter method is also flawed. When opening it, it always prompts us whether we want to open a file with a well-formatted file that does not match the extension. This method is mainly implemented for an unmanaged class such as excel. There are other methods in the link below. If you are interested, you can study them.

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: use COM Interop to create an Excel spreadsheet (C # programming guide)
Http://msdn.microsoft.com/zh-cn/library/ms173186 (VS.80). aspx


How to automate Microsoft Excel in Microsoft Visual C #. NET
Http://support.microsoft.com/kb/302084/zh-cn

C # create, open, read, write, and save General Excel Code
Http://hi.baidu.com/zhaochenbo/blog/item/f6d70ff7bf32fa2a730eec39.html

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