ASP.ENT C#生成excl表格

部署在服务器上出现8000401a错误

解决方法

运行DCOMCNFG，DCOM策略，选择标识，取消交互用户，修改为启动用户

增加network service 用户，权限。

后台源码：

Code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Intel.Cms.BLL;
using Intel.Cms.Entity;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Drawing;
namespace Intel.Admin.Web.manage.eswc
{
    public partial class doExecl : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if(!this.IsPostBack)
            {

            }


        }

 

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="source">需要导出的数据</param>
        /// <param name="exportFullPath">导出Excel文件的全路径</param>
        /// <param name="hasHeadline">如果 true则生成表头 </param>
        private void ExportExcel(System.Data.DataTable source, string exportFullPath, bool hasHeadline)
        {


            #region 采用.Net Excel组件导出

            Application objExcel = new Application();
            if (objExcel == null)
            {
                throw new Exception("ERROR: You must install Microsoft Excel Application!");
            }

            //创建一个Excel文件（未保存，无文件名）
            Workbooks objWorkbooks = objExcel.Workbooks;
            _Workbook objWorkbook = objWorkbooks.Add(XlWBATemplate.xlWBATWorksheet); //默认创建sheet1

            //取得Sheet1
            Sheets objSheets = objWorkbook.Worksheets;
            _Worksheet objWorksheet = (_Worksheet)objSheets.get_Item(1);

            //写入标题
            int intDataBeginRow = 1;
            if (hasHeadline)
            {
                for (int i = 0; i < source.Columns.Count; i++)
                {


                    objWorksheet.Cells[1, i + 1] = GetTitleCN(source.Columns[i].ColumnName.Trim());//转换中文表头

                }

                //数据起始行加1
                intDataBeginRow++;
            }

            //写入数据,Excel的索引是从1开始的
            for (int j = 0; j < source.Rows.Count; j++)
            {
                for (int k = 0; k < source.Columns.Count; k++)
                {
                    //设置格式  
                    Range range = objWorksheet.get_Range(objWorksheet.Cells[j + intDataBeginRow, k + 1], objWorksheet.Cells[j + intDataBeginRow, k + 1]);
                //  range = worksheet.get_Range(worksheet.Cells[1, 1], xSt.Cells[ds.Tables[0].Rows.Count + 1, ds.Tables[0].Columns.Count]); 
                   range.NumberFormatLocal = "@";
                    objWorksheet.Cells[j + intDataBeginRow, k + 1] = source.Rows[j][k].ToString();
                }
            }
            objWorksheet.Hyperlinks.Delete(); //去掉超链

            //保存文件（如果使用objWorkbook.SaveAs将不兼容excel2000,excelxp）
            objWorkbook._SaveAs(exportFullPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            //关闭文件，释放资源
            objWorkbook.Close(false, exportFullPath, false);
            objExcel = null;

            #endregion
        }
        /// <summary>
        /// 将集合类转换成DataTable
        /// </summary>
        /// <param name="list">集合</param>
        /// <returns></returns>
        public System.Data.DataTable ToDataTable(List<FullTeamInfoModel> list)
        {
            System.Data.DataTable result = new System.Data.DataTable();
            if (list.Count > 0)
            {
                PropertyInfo[] propertys = list[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    result.Columns.Add(pi.Name, pi.PropertyType);
                }
                for (int i = 0; i < list.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        object obj = pi.GetValue(list[i], null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    result.LoadDataRow(array, true);
                }
            }
            return result;
        }

        public string GetGameType(int type)
        { 
            switch(type)
            {
                case 0: return "Dota"; break;
                case 1: return "CS"; break;
                case 2: return "女子CS"; break;
                case 3: return "魔兽争霸项目"; break;
                default: return "未定";
            }
        }

        public string GetJoinMatchType(int type)
        {
            if (type == 0)
            {
                return "线上";
            }
            else
            {
                return "线下";
            }
        }

        public string GetMatchType( int type)
        {
            if (type == 0)
            {
                return "团体";
            }
            else
            {
                return "个人";
            }
        }

        public string GetSqlString()
        {



            System.Text.StringBuilder sb = new System.Text.StringBuilder();

          

            if (!string.IsNullOrEmpty(GameType.SelectedValue))
            {

                sb.Append(" and GameType =" + GameType.SelectedValue + "");


            }

            if (!string.IsNullOrEmpty(CodefanCalendar1.Text.Trim()))
            {
                if (!string.IsNullOrEmpty(CodefanCalendar2.Text.Trim()))
                {
                    sb.Append(" and AddDate > '" + CodefanCalendar1.Text.Trim() + "'  and AddDate< '" + CodefanCalendar2.Text.Trim() + "'");
                }

            }




            if (!string.IsNullOrEmpty(sb.ToString()))
            {
                return sb.ToString();
            }
            else
            {
                return " ";
            }

        }

        public string GetTitleCN(string str)
        { 

            switch(str)
            {
                case "Gametypename": return "游戏类型"; break;
                case "PlayerName": return "队长/姓名"; break;
                case "LinkTel": return "电话"; break;
                case "LinkEmail": return "电子邮箱"; break;
                case "QQ": return "QQ"; break;
                case "PlayerIDCard": return "身份证"; break;
                case "TeamName": return "战队"; break;
                case "Joinmatchtypename": return "参赛方式"; break;
                case "Matchtypename": return "参赛类型"; break;
                case "MemberNameAndIDCard1": return "队员1"; break;
                case "MemberNameAndIDCard2": return "队员2"; break;
                case "MemberNameAndIDCard3": return "队员3"; break;
                case "MemberNameAndIDCard4": return "队员4"; break;
                case "AddDate": return "日期"; break;
                default: return "未知";
              

            }

        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(FileName.Text.Trim()))
            {
                error.Text = "必须填写文件名";
                error.ForeColor = Color.Red;

            }
            else
            {
                string sqlWhere = GetSqlString();
                List<TeamInfoModel> list = EswcBLL.GetEswcTeamList(sqlWhere);

                List<FullTeamInfoModel> listnew = new List<FullTeamInfoModel>();

                foreach (TeamInfoModel t in list)
                {
                    FullTeamInfoModel ft = new FullTeamInfoModel();
                    ft.Gametypename = GetGameType(t.GameType);
                    ft.PlayerName = t.PlayerName;
                    ft.PlayerIDCard = t.PlayerIDCard;
                    ft.LinkEmail = t.LinkEmail;
                    ft.LinkTel = t.LinkTel;
                    ft.QQ = t.QQ;
                    ft.Matchtypename = GetMatchType(t.MatchType);
                    ft.Joinmatchtypename = GetJoinMatchType(t.JoinMatchType);
                    ft.TeamName = t.TeamName;
                    if (t.MemberName != "无")
                    {
                        string[] idcard = t.MemberIDCard.Split('|');
                        string[] mname = t.MemberName.Split('|');
                        ft.MemberNameAndIDCard1 = mname[0] + ">>" + idcard[0];
                        ft.MemberNameAndIDCard2 = mname[1] + ">>" + idcard[1];
                        ft.MemberNameAndIDCard3 = mname[2] + ">>" + idcard[2];
                        ft.MemberNameAndIDCard4 = mname[3] + ">>" + idcard[3];
                    }
                    else
                    {
                        ft.MemberNameAndIDCard1 = "无";
                        ft.MemberNameAndIDCard2 = "无";
                        ft.MemberNameAndIDCard3 = "无";
                        ft.MemberNameAndIDCard4 = "无";
                    }

                    ft.AddDate = t.AddDate;

                    listnew.Add(ft);

                }


                System.Data.DataTable dt = ToDataTable(listnew);

                string fullpath = Server.MapPath("/manage/excl/") + "" + FileName.Text.Trim().ToString() + ".xls";

                ExportExcel(dt, fullpath, true);

                HyperLink1.Text = "下载:" + "" + FileName.Text.Trim().ToString() + ".xls";
                HyperLink1.NavigateUrl = "/manage/excl/" + FileName.Text.Trim().ToString() + ".xls";

                // Label1.Text ="生成"+ fullpath;
            }
          
        }
    }
}

转载于:https://www.cnblogs.com/mygood/archive/2009/03/11/1408924.html