Hello everyone, meet again. I'm Jun Quan. I wish every programmer can learn more languages.

. NET to export to Office documents (word,excel). There are two methods I understand. One is to store the exported files under a directory on the server and use response to output them to the browser address bar and open them directly; the other is to directly use javascript to export tags in html.

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 area to import
                //New word object
                var wordObj = new ActiveXObject("Word.Application"); //Specify 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 the file to the specified folder
            }
            catch (e) {
                alert("Save failed, please refresh this page and try again!");
            }
            finally {
                window.location.reload();
            }
        }

This method requires the browser to create Activex and check the Activex control with unknown signature. However, this will reduce the security of the browser, so you will always be prompted to restore the default security settings when you open the browser. Assuming you don't know very well, if you still can't create an ActiveXObject object after restore, you can't create a word object; Therefore, this method has great limitations.

2. Utilization NET com component

Function: will data The fields in the library are exported to generate a template for importing oral English scores

Query specified database fields

/// <summary>
        ///Query database table fields
        /// </summary>
        ///< param name = "tablename" > database table name < / param >
        /// <returns></returns>
        public DataSet GetTableName(string tablename) {

            //sql statement for querying database table fields
            StringBuilder str = new StringBuilder("SELECT name FROM syscolumns WHERE (id = (SELECT id FROM sysobjects WHERE name = '"+tablename+"'))");

            //Return 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 many interesting sql statements that have not been touched before. Such as how to query all the database names of the whole server.

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

Import to excel

/// <summary>
        ///Download the template for importing grades
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void DownLoadTemplate(object sender, EventArgs e)
        {
            //Database fields found
            QuerySpokenEnglishScoreBLL spokenEnglishScorebll = new QuerySpokenEnglishScoreBLL();
            string strTablename = "T_SpokenScores";
            DataSet ds = spokenEnglishScorebll.QueryTableName(strTablename);
            ArrayList tablename = new ArrayList();

            tablename.Add("Student 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 fields to excel
            //Create excel object
            Excel.Application excel = new Excel.Application();
            //Create a Microsoft Excel Workbook
            Excel.Workbook Nowbook;
            //Inference server does not have Excel application installed
            if (excel == null)
            {
                throw new Exception("server Not installed Excel Application, this feature cannot be used");
            }

            //Create a new workbook and assign values 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 the server
            string strpath = Server.MapPath("~/UploadFile/DownFile") + "/" + "Import oral achievement template" + ".xls";
            //The specified directory is actually a copy of the source file
            Nowbook.SaveCopyAs(strpath);



            Nowbook.Close(SaveChanges: false);
            Nowbook = null;
            excel.Quit();
            excel = null;


            //Directly turn to the file path and open it directly
            Response.Redirect("../../UploadFile/DownFile/" + "Import oral achievement template.xls");
        }

It is also interesting to encounter some problems during the test.

Problem 1: error "exception in HRESULT: 0X800A03EC" occurred when reading Excel file.

Check MSDN. That's what Microsoft comrades told me. It's because I add too many and too long cells to the workbook every time. I tried, because I used Chinese before, and finally found that it was not the problem. It starts at 0 in my loop. Nowbook.Sheets[1].Cells[1, i], which makes it impossible to create this cell at all, let alone insert content. So it seems that the big cow of Microsoft fooled me! ha-ha

Question 2: when I see this, the first thing I want to call is whether it may be a permission problem. I quickly deny this. Because of the code, I have created this xls file. Finally, I found that my file was not placed in the specified file path I visited. That's right. So I use the path of the server so that the specified physical path can exist.

By default: use nowbook SaveAs is saved in my document directory.

Use server address

//Save the physical path file specified in the server string strpath = Server.MapPath("~ / UploadFile/DownFile") + "/" + "import oral performance template" + ". xls"; //The specified directory is actually a copy of the source file Nowbook.SaveCopyAs(strpath);

You can.

summary

In fact, the latter method is also defective. When opening, we will always be prompted whether to open files with good file format and mismatched extension. This method is mainly implemented for an unmanaged class such as excel. There are other methods in the following links, which can be studied if you are interested.

From Baidu Library:

MSDN: how to use Visual C# 2005 or visual c# Net to Excel workbook data transfer http://support.microsoft.com/kb/306023/zh-cn

How to: create Excel spreadsheets using COM Interop (C# Programming Guide) http://msdn.microsoft.com/zh-cn/library/ms173186(VS.80).aspx

How to in Microsoft Visual c# Net http://support.microsoft.com/kb/302084/zh-cn

Create, open, read, write and save the general code of Excel in C # http://hi.baidu.com/zhaochenbo/blog/item/f6d70ff7bf32fa2a730eec39.html

Generating Excel 2003 workbooks using Visual Basic and ASP with XML http://msdn.microsoft.com/zh-cn/library/aa203722(office.11).aspx

Publisher: full stack programmer, stack length, please indicate the source for Reprint: https://javaforall.cn/118452.html Original link: https://javaforall.cn