Asp.net Excel export and server deployment

Export and download key code! (Webexcel. ashx. CS)

 

        private void NAR(object o)
        {
try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
finally
            {
                o = null;
            }
        }

public void ExportExcel(HttpContext context, DataSet ds, string DownloadFilePath)
        {


object objOpt = Missing.Value;
            Application excel = new Application();
            excel.Visible = false;
            excel.DisplayAlerts = false;            
            Workbook   wkb = excel.Workbooks.Add(objOpt);
            Worksheet wks = (Worksheet)wkb.ActiveSheet;

            wks.Visible = XlSheetVisibility.xlSheetVisible;


int rowIndex = 1;
int colIndex = 0;

// DataTable table = ds.Tables[0];
            foreach (DataColumn col in ds.Tables[0].Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

foreach (DataRow row in ds.Tables[0].Rows)
            {
                rowIndex++;
                colIndex = 0;
foreach (DataColumn col in ds.Tables[0].Columns)
                {
                    colIndex++;
                    excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                }
            }
//excel.Sheets[0] = "sss";


            wkb.SaveAs(DownloadFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            NAR(wks);
            wkb.Close(false, objOpt, objOpt);
            NAR(wkb);            
            excel.Quit();
            NAR(excel);


if (File.Exists(DownloadFilePath))
            {


using (FileStream fs = new FileStream(DownloadFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
const int size = 4096;
byte[] buffer = new byte[size];
int pos = 0;
long dataToRead = fs.Length;
long arrange = 0;
if (context.Request.Headers["Range"] != null)
                    {
                        context.Response.StatusCode = 206;
                        arrange = long.Parse(context.Request.Headers["Range"].Replace("bytes=", "").Replace("-", ""));
                    }
if (arrange != 0)
                    {
                        context.Response.AddHeader("Content-Range", "bytes " + arrange.ToString() + "-" + ((long)(dataToRead - 1)).ToString() + "/" + dataToRead.ToString());
                    }
                    context.Response.AddHeader("Content-Length", ((long)(dataToRead - arrange)).ToString());
//context.Response.ContentType = "application/octet-stream";
                    context.Response.ContentType = "application/ms-excel";
                    context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.GetEncoding(65001).GetBytes(Path.GetFileName(DownloadFilePath))));

                    fs.Position = arrange;
                    dataToRead = dataToRead - arrange;

while (dataToRead > 0)
                    {
if (context.Response.IsClientConnected)
                        {
                            pos = fs.Read(buffer, 0, size);

                            context.Response.OutputStream.Write(buffer, 0, pos);
                            context.Response.Flush();

                            dataToRead = dataToRead - pos;
                        }
else
                        {
                            dataToRead = -1;
                        }
                    }
                }
            }

        }

1. No office is installed on the server

If you want to use MS, you don't have to worry about this problem. Only the installation can solve it. There is no other way! (Even if some cool people make it out, it is estimated that they will find trouble)

However, I only installed a 2003 Lite version on the server, and I used 2007 for development.

Ii. "80080005 exceptions"

"80080005 exception" is caused by insufficient permissions of the application to operate the COM component. The following operations are available:

1) control panel-> Administrative Tools-> component services-> Computer-> my computer-> DCOM-> Microsoft Excel applications

2) Click properties to open the Properties dialog box for this application.

3) Click the ID tab and select an interactive user.

4) Click the Default Security tab. Set the access permissions of ASP. NET users on the current server.

5) Click Edit default value for the startup permission. Set the access permissions of ASP. NET users.

6) mainly network service accounts

3. failed to retrieve CLSID components in COM class factory 80070005

The method above is similar to the specific configuration method below:

1: Install the Excel software for office on the server.

2: Enter dcomcnfg.exe in "start"-> "run" to start "Component Service"

3: Double-click "component service"> "computer"> "my computer"> "DCOM configuration"

4. In "DCOM configuration", find "Microsoft Excel application", right-click it, and click "properties". The "Microsoft Excel application properties" dialog box is displayed.

5. Click the "ID" tab and select "Interactive User"

6. Click the "Security" tab, click "Custom" on "Start and activate Permissions", and then click the corresponding "edit" button, in the pop-up "security" dialog box, add a "Network Service" user (note that you must select the computer name) and grant it the "local start" and "Local activation" permissions.

7: the "Security" label remains. Click "Custom" on "Access Permissions", and then click "edit ", in the pop-up "security" dialog box, add a "Network Service" user and grant "local access" permission.

8. If the error 8000401a occurs after the interactive user is set, you can cancel the interactive user and set it to administratr. This problem can be solved temporarily. Further solutions are to be discussed.

 

In addition, simply following the above settings does not completely guarantee that the problem persists, because calling office operations may involve other system resources and may still cause problems due to insufficient permissions, after setting the Excel application, the system may still report a 80070005 error. The most safe thing is to select my computer in dcomcnfg and then right-click Properties> com security to grant the access permission, all the start and activation permissions are edited by default. Add network service (Asp.net user is required for IIS 5.0) to it, and set the same permissions for NetworkService as before, this basically guarantees that no problem will occur.

 

Iv. Cause of "error from hresult: 0x800a03ec"

In either case, 1. Excel columns start from 1. If we habitually give worksheet. cells [0, 0] Or worksheet. cells [1, 0] Or worksheet. if cells [0, 1] is assigned a value, this exception is thrown.

2. error-prone

"Excel. activeworkbook. saveas (filepath + filename, Excel. xlfileformat. xlexcel7, null, null, false, false, Excel. xlsaveasaccessmode. xlnochange, null, null );"

When the above Code is saved, the format is very important, and the values of each version are different. At first, I saved another version of 2007 to get 56 (that is, xlexcel8 ), however, in the Code, it is 39 (that is, xlexcel7)