如何避免使用C＃从ASP.NET数据集中的数据写入excel文件的问题？ [英] How to avoid issue of writing to excel file from data in a dataset in ASP.NET with C#?

查看：49 发布时间：2019/6/11 9:10:35 C# ASP.NET

本文介绍了如何避免使用C＃从ASP.NET数据集中的数据写入excel文件的问题？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

使用C＃从ASP.NET中的数据集写入excel文件时遇到问题？

System.UnauthorizedAccessException：检索组件的COM类工厂CLSID {00024500-0000-0000-C000-000000000046}由于以下错误而失败：80070005。

Ny代码如下：

I am having issue while writing to excel file from a dataset in ASP.NET with C#?

System.UnauthorizedAccessException: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005.

Ny code is as below:

public bool SaveExcelFile(DataSet ds, string strFileName, string strUploadType)
    {
        //bool bolResult = 0;
        // ReturnError err;
        string strCurrentDir = "";
        switch (strUploadType)
        {
            case "EmpList": { strCurrentDir = GetWebPath("EMPLOYEE_LIST_UPLOAD"); break; }
            case "SalaryList": { strCurrentDir = GetWebPath("SalaryFilePath"); break; }
            case "RFR": { strCurrentDir = GetWebPath("RFRInitiateFilePath"); break; }
        }
        if (!File.Exists(strCurrentDir + strFileName))
        {

            Microsoft.Office.Interop.Excel._Application oXL;
            Microsoft.Office.Interop.Excel._Workbook oWB;
            Microsoft.Office.Interop.Excel._Worksheet oSheet;
            Microsoft.Office.Interop.Excel.Range oRng;

            try
            {
                oXL = new Microsoft.Office.Interop.Excel.Application();
                oXL.Visible = false;
                //Get a new workbook.
                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

                int iRow = 2;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        //oSheet.Cells.NumberFormat = "@";
                        oSheet.Cells[1, j + 1] = ds.Tables[0].Columns[j].ColumnName;
                    }

                    // For each row, print the values of each column.
                    for (int rowNo = 0; rowNo < ds.Tables[0].Rows.Count; rowNo++)
                    {
                        for (int colNo = 0; colNo < ds.Tables[0].Columns.Count; colNo++)
                        {
                            oSheet.Cells.NumberFormat = "@";
                            oSheet.Cells[iRow, colNo + 1] = ds.Tables[0].Rows[rowNo][colNo].ToString();
                        }
                        iRow++;
                    }


                }

                oRng = oSheet.get_Range("A1", "IV1");
                oRng.EntireColumn.AutoFit();
                oRng.EntireRow.Hidden = true;
                oXL.Visible = false;
                oXL.UserControl = false;
                string strFile = strFileName;//+
                oWB.SaveAs(strCurrentDir +
                       strFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
                // Need all following code to clean up and remove all references!!!
                oWB.Close(null, null, null);
                oXL.Workbooks.Close();
                oXL.Quit();
                Marshal.ReleaseComObject(oRng);
                Marshal.ReleaseComObject(oXL);
                Marshal.ReleaseComObject(oSheet);
                Marshal.ReleaseComObject(oWB);
            }

            catch (Exception theException)
            {
                Common.WriteApplicationLogs(this.ToString(), "save excel", theException.Message + theException.StackTrace, "webservice");
                return false;
            }
            return true;
        }
        else
        {
            Common.WriteApplicationLogs(this.ToString(), "save excel", strCurrentDir + strFileName, "webservice");
            return false;
        }
    }

我的尝试：

我的代码如下：

public bool SaveExcelFile（DataSet ds，string strFileName，string strUploadType）

{

// bool bolResult = 0;

// ReturnError错误;

string strCurrentDir =;

switch（strUploadType ）

{

caseEmpList：{strCurrentDir = GetWebPath（EMPLOYEE_LIST_UPLOAD）;打破; }

caseSalaryList：{strCurrentDir = GetWebPath（SalaryFilePath）;打破; }

caseRFR：{strCurrentDir = GetWebPath（RFRInitiateFilePath）;打破; }

}

if（！File.Exists（strCurrentDir + strFileName））

{

Microsoft.Office.Interop.Excel._Application oXL;

Microsoft.Office.Interop.Excel._Workbook oWB;

Microsoft.Office.Interop.Excel ._Worksheet oSheet;

Microsoft.Office.Interop.Excel.Range oRng;

try

{

oXL = new Microsoft.Office.Interop.Excel.Application（）;

oXL.Visible = false;

//获取新工作簿。

oWB =（Microsoft.Office.Interop.Excel._Workbook）（oXL.Workbooks.Add（Missing.Value））;

oSheet =（Microsoft.Office。 Interop.Excel._Worksheet）oWB.ActiveSheet;

int iRow = 2;

if（ds.Tables [0] .Rows。数> 0）

{

for（int j = 0; j< ds.Tables [0] .Columns.Count; j ++）

{

//oSheet.Cells.NumberFormat =@;

oSheet.Cells [1，j + 1] = ds.Tables [0] .Columns [j] .ColumnName;

}

//对于每一行，打印每个值列。

for（int rowNo = 0; rowNo< ds.Tables [0] .Rows.Count; rowNo ++）

{

for（int colNo = 0; colNo< ds.Tables [0] .Columns.Count; colNo ++）

{

oSheet.Cells.NumberFormat =@;

oSheet.Cells [iRow，colNo + 1] = ds.Tables [0] .Rows [rowNo] [colNo] .ToString（）;

}

iRow ++;

}

}

oRng = oS heet.get_Range（A1，IV1）;

oRng.EntireColumn.AutoFit（）;

oRng.EntireRow.Hidden = true;

oXL.Visible = false;

oXL.UserControl = false;

string strFile = strFileName; // +

oWB.SaveAs （strCurrentDir +

strFile，Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal，null，null，false，false，Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared，false，false，null ，null，null）;

//需要以下所有代码来清理和删除所有引用!!!

oWB.Close（null，null，null）;

oXL.Workbooks.Close（）;

oXL.Quit（）;

Marshal.ReleaseComObject（oRng）;

Marshal.ReleaseComObject（oXL）;

Marshal.ReleaseComObject（oSheet）;

Marshal.ReleaseComObject（oWB）;

}

catch（Exception theException）

{

Common.WriteApplicationLogs（this.ToString（），save excel，theException.Message + theException.StackTrace，webservice）;

返回false;

}

返回true;

}

其他

{

Common.WriteApplicationLogs（this.ToString（），save excel，strCurrentDir + strFileName，webservice）;

返回false;

}

}

What I have tried:

My code is as below:
public bool SaveExcelFile(DataSet ds, string strFileName, string strUploadType)
{
//bool bolResult = 0;
// ReturnError err;
string strCurrentDir = "";
switch (strUploadType)
{
case "EmpList": { strCurrentDir = GetWebPath("EMPLOYEE_LIST_UPLOAD"); break; }
case "SalaryList": { strCurrentDir = GetWebPath("SalaryFilePath"); break; }
case "RFR": { strCurrentDir = GetWebPath("RFRInitiateFilePath"); break; }
}
if (!File.Exists(strCurrentDir + strFileName))
{

Microsoft.Office.Interop.Excel._Application oXL;
Microsoft.Office.Interop.Excel._Workbook oWB;
Microsoft.Office.Interop.Excel._Worksheet oSheet;
Microsoft.Office.Interop.Excel.Range oRng;

try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

int iRow = 2;
if (ds.Tables[0].Rows.Count > 0)
{
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
//oSheet.Cells.NumberFormat = "@";
oSheet.Cells[1, j + 1] = ds.Tables[0].Columns[j].ColumnName;
}

// For each row, print the values of each column.
for (int rowNo = 0; rowNo < ds.Tables[0].Rows.Count; rowNo++)
{
for (int colNo = 0; colNo < ds.Tables[0].Columns.Count; colNo++)
{
oSheet.Cells.NumberFormat = "@";
oSheet.Cells[iRow, colNo + 1] = ds.Tables[0].Rows[rowNo][colNo].ToString();
}
iRow++;
}

}

oRng = oSheet.get_Range("A1", "IV1");
oRng.EntireColumn.AutoFit();
oRng.EntireRow.Hidden = true;
oXL.Visible = false;
oXL.UserControl = false;
string strFile = strFileName;//+
oWB.SaveAs(strCurrentDir +
strFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
// Need all following code to clean up and remove all references!!!
oWB.Close(null, null, null);
oXL.Workbooks.Close();
oXL.Quit();
Marshal.ReleaseComObject(oRng);
Marshal.ReleaseComObject(oXL);
Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oWB);
}

catch (Exception theException)
{
Common.WriteApplicationLogs(this.ToString(), "save excel", theException.Message + theException.StackTrace, "webservice");
return false;
}
return true;
}
else
{
Common.WriteApplicationLogs(this.ToString(), "save excel", strCurrentDir + strFileName, "webservice");
return false;
}
}

推荐答案

Microsoft Office Interop并不打算在服务器上使用（使用ASP.net之类的服务器端技术）使用库喜欢： EPPlus - 在服务器上创建高级Excel电子表格 - 主页 [ ^ ]

Microsoft Office Interop was not meant to be used on a server (with server side technologies like ASP.net etc.) use libraries like : EPPlus-Create advanced Excel spreadsheets on the server - Home[^]

问题可能出在Excel ..

您可以尝试以下方法：

1.确保在服务器上安装了Office运行时。

2.如果您使用的是Windows Server 2008，那么使用办公室互操作是一个长期配置，以下是步骤。

更好的是转移到Open XML，或者您可以配置如下

安装MS Office Pro最新版（我使用的是2010专业版）

创建用户ExcelUser。使用管理员组分配WordUser

转到计算机 - >管理

添加以下选项的用户

用户选项密码永不过期

密码无法更改

Com + Configuration < br $> b $ b

转到控制面板 - >管理员 - >组件服务 - > DCOM配置

打开Microsoft Word 97 - 2003属性

一般 - >身份验证级别：无

安全性 - >自定义所有3个权限以允许每个人

身份 - >此用户 - >使用ExcelUser /密码

启动Excel应用程序以确保一切正常

3.在DCOM中更改Microsoft Excel应用程序的安全设置配置。

控制面板 - >管理工具 - >组件服务 - >电脑 - > myComputer - > DCOM配置 - > Microsoft Excel应用程序。

右键单击以获取属性对话框。转到安全选项卡并自定义权限

The issue may be with Excel..

You may try the following:

1. Make sure that you have Office runtime installed on the server.
2. If you are using Windows Server 2008 then using office interops is a lenghty configuration and here are the steps.
Better is to move to Open XML or you can configure as below

Install MS Office Pro Latest (I used 2010 Pro)
Create User ExcelUser. Assign WordUser with Admin Group
Go to Computer -> Manage
Add User with below options
User Options Password Never Expires
Password Cannot Be Change
Com+ Configuration

Go to Control Panel - > Administrator -> Component Services -> DCOM Config
Open Microsoft Word 97 - 2003 Properties
General -> Authentication Level : None
Security -> Customize all 3 permissions to allow everyone
Identity -> This User -> Use ExcelUser /password
Launch the Excel App to make sure everything is fine

3.Change the security settings of Microsoft Excel Application in DCOM Config.

Controlpanel --> Administrative tools --> Component Services --> computers --> myComputer --> DCOM Config --> Microsoft Excel Application.

Right click to get properties dialog. Go to Security tab and customize permissions

正如Mehdi所说，ASP.NET正式支持Office Interop：

As Mehdi said, Office Interop is not officially supported in ASP.NET:

服务器端Office自动化的注意事项 [ ^ ]

Microsoft目前不推荐，也不支持，从任何无人参与的非交互式客户端应用程序或组件（包括ASP，ASP.NET，DCOM和NT服务）自动化Microsoft Office应用程序，因为Office在此环境中运行时可能会出现不稳定的行为和/或死锁。 br />

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

你必须跳过各种各样的篮球才能让它发挥作用，并且不能保证它不会突然停止工作未来的一些随机点。

有vario我们在不使用Office互操作的情况下在服务器上创建Excel电子表格的方法。例如：

EPPlus [ ^ ];
ClosedXML [ ^ ];
OpenXML SDK [ ^ ];

There are various hoops you have to jump through to stand any chance of making it work, and there's no guarantee that it won't suddenly stop working at some random point in the future.

There are various ways to create Excel spreadsheets on the server without using Office interop. For example:

EPPlus[^];
ClosedXML[^];
The OpenXML SDK[^];

这篇关于如何避免使用C＃从ASP.NET数据集中的数据写入excel文件的问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

如何避免使用C＃从ASP.NET数据集中的数据写入excel文件的问题？ [英] How to avoid issue of writing to excel file from data in a dataset in ASP.NET with C#?

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录关闭

如何避免使用C＃从ASP.NET数据集中的数据写入excel文件的问题？ [英] How to avoid issue of writing to excel file from data in a dataset in ASP.NET with C#?

问题描述

推荐答案

相关文章

其他开发语言最新文章

热门教程

热门工具

登录 关闭

登录关闭