请教vs2008 web导出Excel报表

[fangma2001]

我在win7下用vs2008 web导出Excel报表，在开发环境中运行正常.
发布后运行出错：Microsoft excel不能访问文件 c:\inetpub\wwwroot\aaa\rep1.xls
部署到Windows server 2008下，显示：未能加载文件或程序集microsoft.offfice.interop.excel...
我已添加desktop目录，结束excel进程，但打开报表模板还是出错。请教高手如何解决。谢谢！

[灰太狼已不再单身]

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace FlatManage.WebSite.Tools
{
    public class ExcelHelper
    {
        #region 实例化  
        /// <summary>  
        /// 文件名称  
        /// </summary>  
        public string _fileName { get; private set; }
        /// <summary>  
        /// 工作薄名称，多工作薄以";"隔开，例如：测试1;测试2;测试3  
        /// </summary>  
        public string _sheetName { get; private set; }
        /// <summary>  
        /// 每个工作薄只能有一个标题，多工作薄时，标题以";"隔开  
        /// </summary>  
        public string _title { get; private set; }
        /// <summary>  
        /// 表头格式  
        /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')  
        /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名   
        /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值   
        /// 三行时请注意：列头要重复    
        /// </summary>  
        public string _header { get; private set; }
        /// <summary>  
        /// 文件路径 如果WEB导出时，路径可为空；  
        /// </summary>  
        public string _filePath { get; private set; }
        /// <summary>  
        /// 导出方式 1：WEB导出 2：路径导出  
        /// </summary>  
        public int _exportMode { get; private set; }
        /// <summary>  
        /// 数据源  
        /// </summary>  
        public DataSet _dsSource { get; private set; }
        /// <summary>  
        /// 数据库采用字段  
        /// </summary>  
        public string _filed { get; private set; }
        /// <summary>  
        /// HSSFWorkbook 对象  
        /// </summary>  
        private NPOI.HSSF.UserModel.HSSFWorkbook _workbook;
        /// <summary>  
        /// 实例化  
        /// </summary>  
        /// <param name="fileName">文件名称</param>  
        /// <param name="sheetName">工作薄名称，多工作薄以";"隔开，例如：测试1;测试2;测试3</param>  
        /// <param name="header">  
        /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')  
        /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名   
        /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值   
        /// 三行时请注意：列头要重复  
        /// </param>  
        /// <param name="ds">数据来源</param>  
        /// <param name="filed">数据字段</param>  
        public ExcelHelper(string fileName, string sheetName, string header, DataSet ds, string filed)
        {
            // 判断文件后缀名是否存在  
            if (string.IsNullOrEmpty(fileName))
                fileName = "新建Excel.xls";
            else if (fileName.IndexOf(".xls") == -1 && fileName.IndexOf(".xlsx") == -1)
                fileName += ".xls";

            this._workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            this._fileName = fileName;
            this._header = header;
            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentNullException("sheetName", "工作薄名称不能为空");
            this._sheetName = sheetName;
            this._exportMode = 1;
            this._dsSource = ds;
            this._filed = filed;
        }
        /// <summary>  
        ///   
        /// </summary>  
        /// <param name="fileName">文件名称</param>  
        /// <param name="sheetName">工作薄名称，多工作薄以";"隔开，例如：测试1;测试2;测试3</param>  
        /// <param name="header">  
        /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')  
        /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名   
        /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值   
        /// 三行时请注意：列头要重复  
        /// </param>  
        /// <param name="title">每个工作薄只能有一个标题，多工作薄时，标题以";"隔开</param>  
        /// <param name="filePath">文件路径</param>  
        /// <param name="exportMode">导出方式 1：WEB导出 2：路径导出</param>  
        /// <param name="ds">数据来源</param>  
        /// <param name="filed">数据字段</param>  
        public ExcelHelper(string fileName, string sheetName, string header, string title, DataSet ds, string filed, string filePath = null, int exportMode = 1)
        {
            // 判断文件后缀名是否存在  
            if (string.IsNullOrEmpty(fileName))
                fileName = "新建Excel.xls";
            else if (fileName.IndexOf(".xls") == -1 && fileName.IndexOf(".xlsx") == -1)
                fileName += ".xls";

            this._workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            this._fileName = fileName;

            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentNullException("sheetName", "工作薄名称不能为空");
            this._sheetName = sheetName;
            this._header = header;
            this._title = title;

            if (2 == exportMode && string.IsNullOrEmpty(filePath))
                filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

            this._filePath = filePath;
            this._exportMode = exportMode;
            this._dsSource = ds;
            this._filed = filed;
        }
        #endregion

        #region 功能实现  
        /// <summary>  
        /// 实现导出功能  
        /// </summary>  
        public void Export()
        {
            #region 变量声明  
            string[] tableTitle = this._header.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            // 表头数组  
            string[] newHeaders = null;
            // 数据字段  
            string[] files = this._filed.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            // 数据字段  
            string[] file = null;
            // 临时数组  
            string[] temp = null;
            // 临时表头  
            string tempHeader = string.Empty;
            // 工作薄名称  
            string[] sheetNames = this._sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            // 表头名称  
            string[] titles = this._title == null ? null : this._title.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            // 获取行数  
            int rows = GetRowCount(this._header);
            // 列数计数器  
            int cols = 0;
            // 列头跨行数  
            int rowSpans = 0;
            // 列头跨列数  
            int colSpans = GetColCount(this._header);
            // HSSFSheet 对象  
            NPOI.HSSF.UserModel.HSSFSheet sheet = null;
            // IRow 对象  
            IRow row = null;
            // 表头行添加  
            int trow = (string.IsNullOrEmpty(this._title) ? 0 : 1);
            DataTable dt;
            #endregion

            #region 单元格样式  
            ICellStyle style = _workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中  
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中   
            style.WrapText = true;//自动换行  
            // 边框  
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            // 字体  
            IFont font = _workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.FontName = "宋体";
            style.SetFont(font);

            ICellStyle titleType = _workbook.CreateCellStyle();
            titleType.Alignment = HorizontalAlignment.Center; //居中  
            titleType.VerticalAlignment = VerticalAlignment.Center;//垂直居中   
            titleType.WrapText = true;//自动换行  
            // 边框  
            titleType.BorderBottom = BorderStyle.Thin;
            titleType.BorderLeft = BorderStyle.Thin;
            titleType.BorderRight = BorderStyle.Thin;
            titleType.BorderTop = BorderStyle.Thin;

            IFont font2 = _workbook.CreateFont();
            font2.FontHeightInPoints = 14;
            font2.FontName = "宋体";
            font2.Boldweight = (short)FontBoldWeight.Bold;
            titleType.SetFont(font2);
            #endregion

            // 表格绘制  
            for (int k = 0; k < sheetNames.Length; k++)
            {
                #region 表头绘制  
                newHeaders = tableTitle[k].Split(new char[] { '#' }, StringSplitOptions.RemoveEmptyEntries);
                sheet = (NPOI.HSSF.UserModel.HSSFSheet)_workbook.CreateSheet(sheetNames[k]);
                for (int m = 0; m < rows + trow; m++) // 创建行  
                {
                    if (m == 0 && trow > 0)
                    {
                        row = sheet.CreateRow(0);
                        CellRangeAddress region = new CellRangeAddress(0, 0, 0, colSpans - 1);
                        sheet.AddMergedRegion(region);
                        row.CreateCell(0).SetCellValue(titles[k]);
                        row.GetCell(0).CellStyle = titleType;
                        row.Height = 20 * 20;
                        continue;
                    }
                    cols = 0;
                    for (int i = 0; i < newHeaders.Length; i++) // 创建列  
                    {

                        tempHeader = newHeaders[i];
                        // 获取列头跨行数  
                        rowSpans = GetRowSpan(tempHeader, rows);
                        // 获取列头跨列数  
                        colSpans = GetColSpan(tempHeader);

                        // 如果表头还可以划分  
                        temp = tempHeader.Split(new char[] { ' ' });
                        if (temp.Length == rows)
                            tempHeader = temp[m - trow];
                        else
                            tempHeader = temp[0];



                        if (1 == rowSpans)
                        {
                            // 获取行  
                            row = sheet.GetRow(m);
                            if (row == null)
                                row = sheet.CreateRow(m);

                            // 未跨列  
                            if (1 == colSpans)
                            {
                                row.CreateCell(cols).SetCellValue(tempHeader);
                                row.GetCell(cols).CellStyle = style

[灰太狼已不再单身]

没注意，上面代码都没复制完

public static void ToExcel(DataSet ds, string savePath, string title)
        {
            if (ds.Tables.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    var dt = ds.Tables[i];
                    var str = dt.TableName;

                    NPOI.HSSF.UserModel.HSSFSheet sheet = (NPOI.HSSF.UserModel.HSSFSheet)workbook.CreateSheet(str);

                    //设置标题行样式
                    NPOI.HSSF.UserModel.HSSFFont font = (NPOI.HSSF.UserModel.HSSFFont)workbook.CreateFont();
                    font.Boldweight = 600;

                    NPOI.HSSF.UserModel.HSSFCellStyle styleTitle = (NPOI.HSSF.UserModel.HSSFCellStyle)workbook.CreateCellStyle();
                    styleTitle.SetFont(font);

                    ICellStyle titleType = workbook.CreateCellStyle();
                    titleType.Alignment = HorizontalAlignment.Center; //居中  
                    titleType.VerticalAlignment = VerticalAlignment.Center;//垂直居中   
                    titleType.WrapText = true;//自动换行  

                    ICellStyle cellStyle = workbook.CreateCellStyle();
                    cellStyle.Alignment = HorizontalAlignment.Center; //居中  
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中   
                    cellStyle.WrapText = true;//自动换行 

                    ICellStyle cellRight = workbook.CreateCellStyle();
                    cellRight.Alignment = HorizontalAlignment.Right; //右对齐  
                    cellRight.VerticalAlignment = VerticalAlignment.Center;//垂直居中   
                    cellRight.WrapText = true;//自动换行 

                    //首先生成标题
                    var headRow = sheet.CreateRow(0);
                    headRow.Height = 18 * 20;
                    for (int col = 0; col < dt.Columns.Count; col++)
                    {
                        string colName = dt.Columns[col].ColumnName.Trim();//获取标题名
                        //sheet.CreateRow(0).CreateCell(col).SetCellValue(colName);
                        headRow.CreateCell(col).SetCellValue(colName);
                        headRow.GetCell(col).CellStyle = titleType;
                        sheet.SetColumnWidth(col, 22 * 256);//设置列宽
                    }
                    //遍历所有数据并填充
                    for (int row = 0; row < dt.Rows.Count; row++)
                    {
                        int currentRow = row + 1;//因为第0行是标题
                        var dataRow = sheet.CreateRow(currentRow);
                        dataRow.Height = 18 * 20;
                        for (int colC = 0; colC < dt.Columns.Count; colC++)
                        {
                            string drValue = dt.Rows[row][colC].ToString();
                            var column = dt.Columns[colC];

                            //sheet.GetRow(currentRow).CreateCell(colC).SetCellValue(drValue);

                            var newCell = dataRow.CreateCell(colC);

                            newCell.CellStyle = cellStyle;

                            switch (column.DataType.ToString())
                            {
                                case "System.String"://字符串类型  
                                    newCell.SetCellValue(drValue);
                                    break;
                                case "System.DateTime"://日期类型  
                                    DateTime dateV;
                                    DateTime.TryParse(drValue, out dateV);
                                    newCell.SetCellValue(dateV);

                                    //newCell.CellStyle = dateStyle;//格式化显示  
                                    break;
                                case "System.Boolean"://布尔型  
                                    bool boolV = false;
                                    bool.TryParse(drValue, out boolV);
                                    newCell.SetCellValue(boolV);
                                    break;
                                case "System.Int16"://整型  
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;
                                    int.TryParse(drValue, out intV);
                                    newCell.SetCellValue(intV);
                                    newCell.CellStyle = cellRight;
                                    break;
                                case "System.Decimal"://浮点型  
                                case "System.Double":
                                    double doubV = 0;
                                    double.TryParse(drValue, out doubV);
                                    newCell.SetCellValue(doubV);
                                    newCell.CellStyle = cellRight;
                                    break;
                                case "System.DBNull"://空值处理  
                                    newCell.SetCellValue("");
                                    break;
                                default:
                                    newCell.SetCellValue("");
                                    break;
                            }
                        }
                    }
                }

                //保存excel
                FileStream fileSave = new FileStream(savePath, FileMode.Create);
                workbook.Write(fileSave);
                fileSave.Close();
            }

        }

        /// <summary>
        /// 获取excel内容
        /// </summary>
        /// <param name="filePath">excel文件路径</param>
        /// <returns></returns>
        public static DataSet ImportExcel(string filePath)
        {
            DataSet ds = new DataSet();
            using (FileStream fsRead = System.IO.File.OpenRead(filePath))
            {
                IWorkbook wk = null;
                //获取后缀名
                string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower();
                //判断是否是excel文件
                if (extension == ".xlsx" || extension == ".xls")
                {
                    //判断excel的版本
                    if (extension == ".xlsx")
                    {
                        wk = new NPOI.XSSF.UserModel.XSSFWorkbook(fsRead);
                    }
                    else
                    {
                        wk = new NPOI.HSSF.UserModel.HSSFWorkbook(fsRead);
                    }

                    //遍历sheet
                    for (int n = 0; n < wk.NumberOfSheets; n++)
                    {
                        ISheet sheet = wk.GetSheetAt(n);

                        DataTable dt = new DataTable(sheet.SheetName);

                        //获取第一行
                        IRow headrow = sheet.GetRow(0);
                        //创建列
                        for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
                        {
                            DataColumn datacolum = new DataColumn(headrow.GetCell(i).StringCellValue);
                            //DataColumn datacolum = new DataColumn("F" + (i + 1));
                            dt.Columns.Add(datacolum);
                        }
                        //读取每行,从第二行起
                        for (int r = 1; r <= sheet.LastRowNum; r++)
                        {
                            bool result = false;
                            DataRow dr = dt.NewRow();
                            //获取当前行
                            IRow row = sheet.GetRow(r);
                            //读取每列
                            for (int j = 0; j < row.Cells.Count; j++)
                            {
                                ICell cell = row.GetCell(j); //一个单元格
                                dr[j] = GetCellValue(cell); //获取单元格的值
                                                            //全为空则不取
                                if (dr[j].ToString() != "")
                                {
                                    result = true;
                                }
                            }
                            if (result == true)
                            {
                                dt.Rows.Add(dr); //把每行追加到DataTable
                            }
                        }
                        ds.Tables.Add(dt);
                    }
                }

            }
            return ds;
        }

        //对单元格进行判断取值
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
                    return string.Empty;
                case CellType.Boolean: //bool类型
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric: //数字类型
                    if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        return cell.DateCellValue.ToString();
                    }
                    else //其它数字
                    {
                        return cell.NumericCellValue.ToString();
                    }
                case CellType.Unknown: //无法识别类型
                default: //默认类型
                    return cell.ToString();//
                case CellType.String: //string 类型
                    return cell.StringCellValue;
                case CellType.Formula: //带公式类型
                    try
                    {
                        NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
            

[全栈极简]

用Npoi操作excel。https://www.cnblogs.com/zhaozhengyan/p/webform_operExcel.html