ASP.NET导出EXCEl方法使用EXCEl对象

导出功能必须使用  office中EXCEl对象，整个操作如同在操作EXCEl一样，建立EXCEl应用----建立工作簿---建立sheet表单页，

代码实现过程中，如果想对单元格实现一些操作，或者汇总功能，可以先使用，EXCEl中的宏来操作。

使用方法  第一步：工具---宏----录制新宏---保存-----第二步：工具---宏---宏---编辑（编辑框中的代码就是在程序中所要使用的对象，和参数设置，可以通过excel对象来调用对象，直接在代码中操作excel）

  protected string PrintExcel(string _printType, string _userid)
    {
        Excel.Application xApp = null;
        Excel.Workbook xBook = null;
        Excel.Worksheet xSheet = null;
        Excel.Range xRange = null;
        try
        {
            string targetDic = System.Web.HttpContext.Current.Server.MapPath("../") + "ReportFile\";

            string targetName = Session["RWTJHZ_txtDateStart"].ToString() + "至" + Session["RWTJHZ_txtDateEnd"].ToString() + "工作量汇总统计.xls";

            xApp = new Excel.ApplicationClass();
            xBook = xApp.Workbooks.Add(true);
            xSheet = (Excel.Worksheet)xBook.Sheets[1];

            DataTable _dt;

            int _rowIndex = 0;

            switch (_printType)
            {
                case "detail":
                    DataSet _dsContent = (DataSet)Session["RWTJHZ_dsContent"];
                    _dsContent.Tables[0].DefaultView.RowFilter = "userid='" + _userid + "'";
                    _dt = _dsContent.Tables[0].DefaultView.ToTable();

                    _dsContent.Tables[0].DefaultView.RowFilter = "";
                    string _biaozhi = "", _proType = "";
                    //如果是查询个人的工作量则修改文件名称
                    targetName = Session["RWTJHZ_txtDateStart"].ToString() + "至" + Session["RWTJHZ_txtDateEnd"].ToString() + "工作量汇总统计" + "(" + _dt.Rows[0]["username"] + ")" + ".xls";

                    //在填入内容之前先合并单元格
                    xRange = xSheet.get_Range(xApp.Cells[1, 1], xApp.Cells[1, 8]);
                    xRange.MergeCells = true;
                    xRange = xSheet.get_Range(xApp.Cells[3, 1], xApp.Cells[9, 1]);
                    xRange.MergeCells = true;

                    #region 标题
                    xRange = xSheet.get_Range(xApp.Cells[1, 1], xApp.Cells[1, 8]);
                    //合并标题单元格
                    xRange.MergeCells = true;
                    //内容
                    xRange.Value2 = targetName.TrimEnd('.', 'x', 'l', 's');
                    //设置居中
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    //字体大小为14
                    xRange.Font.Size = 16;
                    //字体加粗
                    xRange.Font.Bold = true;
                    #endregion

                    #region 表头
                    //姓名
                    xRange = xSheet.get_Range(xApp.Cells[2, 1], xApp.Cells[2, 1]);
                    xRange.Value2 = "姓名";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    xSheet.Columns.get_Range("A:A", Type.Missing).ColumnWidth = 21;

                    //工作名称
                    xRange = xSheet.get_Range(xApp.Cells[2, 2], xApp.Cells[2, 2]);
                    xRange.Value2 = "工作名称";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    xSheet.Columns.get_Range("B:B", Type.Missing).ColumnWidth = 18;
                    //类型
                    xRange = xSheet.get_Range(xApp.Cells[2, 3], xApp.Cells[2, 3]);
                    xRange.Value2 = "类型";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    xSheet.Columns.get_Range("C:C", Type.Missing).ColumnWidth = 10;
                    //地点
                    xRange = xSheet.get_Range(xApp.Cells[2, 4], xApp.Cells[2, 4]);
                    xRange.Value2 = "地点";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    xSheet.Columns.get_Range("D:D", Type.Missing).ColumnWidth = 15;
                    //工作开始时间
                    xRange = xSheet.get_Range(xApp.Cells[2, 5], xApp.Cells[2, 5]);
                    xRange.Value2 = "工作开始时间";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    xSheet.Columns.get_Range("E:E", Type.Missing).ColumnWidth = 16;
                    //工作结束时间
                    xRange = xSheet.get_Range(xApp.Cells[2, 6], xApp.Cells[2, 6]);
                    xRange.Value2 = "工作结束时间";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    xSheet.Columns.get_Range("F:F", Type.Missing).ColumnWidth = 16;
                    //工时
                    xRange = xSheet.get_Range(xApp.Cells[2, 7], xApp.Cells[2, 7]);
                    xRange.Value2 = "工时";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    //备注
                    xRange = xSheet.get_Range(xApp.Cells[2, 8], xApp.Cells[2, 8]);
                    xRange.Value2 = "备注";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    #endregion

                    #region 内容
                    //人员汇总信息
                    string _outlineMsg = "";
                    _outlineMsg += _dt.Rows[0]["username"].ToString();
                    _outlineMsg += "(" + _dt.Rows[0]["department"].ToString() + ") ";
                    _outlineMsg += "节目制作工时：" + GetShowLength(_userid) + " ";
                    _outlineMsg += "节目制作场次：" + GetShowCount(_userid) + " ";
                    _outlineMsg += "值班工时：" + GetDutyLength(_userid) + " ";
                    _outlineMsg += "派单出机工时：" + GetDispatchLength(_userid) + " ";
                    _outlineMsg += "其他任务工时：" + GetRenWuLength(_userid) + " ";
                    _outlineMsg += "总工时：" + GetWorkLength(_userid);

                    //xSheet.get_Range(xApp.Cells[3, 1], xApp.Cells[GVData.Rows.Count + 3, 1]).NumberFormatLocal = "@";
                    xSheet.get_Range(xApp.Cells[3, 1], xApp.Cells[9, 1]).Value2 = _outlineMsg;

                    for (int i = 0; i < _dt.Rows.Count; i++)
                    {
                        _rowIndex = i + 3;
                        //工作名称
                        xSheet.get_Range(xApp.Cells[_rowIndex, 2], xApp.Cells[_rowIndex, 2]).Value2 = _dt.Rows[i]["pro_Title"].ToString();
                        //类型
                        _biaozhi = _dt.Rows[i]["tableType"].ToString().Trim();
                        _proType = _dt.Rows[i]["pro_type"].ToString().Trim();
                        switch (_biaozhi)
                        {
                            case "pro":
                                if (_proType == "节目制作单")
                                {
                                    xSheet.get_Range(xApp.Cells[_rowIndex, 3], xApp.Cells[_rowIndex, 3]).Value2 = "节目制作";
                                }
                                else if (_proType == "简单任务")
                                {
                                    xSheet.get_Range(xApp.Cells[_rowIndex, 3], xApp.Cells[_rowIndex, 3]).Value2 = "其他任务";
                                }
                                break;
                            case "duty":
                                xSheet.get_Range(xApp.Cells[_rowIndex, 3], xApp.Cells[_rowIndex, 3]).Value2 = "值班";
                                break;
                            case "dis":
                                xSheet.get_Range(xApp.Cells[_rowIndex, 3], xApp.Cells[_rowIndex, 3]).Value2 = "出机";
                                break;
                        }
                        //地点
                        xSheet.get_Range(xApp.Cells[_rowIndex, 4], xApp.Cells[_rowIndex, 4]).Value2 = _dt.Rows[i]["pro_Place"].ToString();
                        //工作开始时间
                        xSheet.get_Range(xApp.Cells[_rowIndex, 5], xApp.Cells[_rowIndex, 5]).Value2 = _dt.Rows[i]["timeFr"].ToString();
                        //工作结束时间
                        xSheet.get_Range(xApp.Cells[_rowIndex, 6], xApp.Cells[_rowIndex, 6]).Value2 = _dt.Rows[i]["timeTo"].ToString();
                        //工时
                        xSheet.get_Range(xApp.Cells[_rowIndex, 7], xApp.Cells[_rowIndex, 7]).Value2 = _dt.Rows[i]["ot"].ToString();
                        //备注
                        xSheet.get_Range(xApp.Cells[_rowIndex, 8], xApp.Cells[_rowIndex, 8]).Value2 = _dt.Rows[i]["note"].ToString();
                    }
                    #endregion

                    //显示边框
                    xRange = xSheet.get_Range(xApp.Cells[1, 1], xApp.Cells[_rowIndex, 8]);
                    xRange.Borders.LineStyle = 1;
                    //取向
                    xSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;

                    break;
                case "outline":
                    _dt = ((DataTable)Session["RWTJHZ_dtOutline"]).DefaultView.ToTable();

                    #region 标题
                    xRange = xSheet.get_Range(xApp.Cells[1, 1], xApp.Cells[1, 8]);
                    //合并标题单元格
                    xRange.MergeCells = true;
                    //内容
                    xRange.Value2 = targetName.TrimEnd('.', 'x', 'l', 's');
                    //设置居中
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    //字体大小为14
                    xRange.Font.Size = 16;
                    //字体加粗
                    xRange.Font.Bold = true;
                  
                    #endregion

                    #region 表头
                    //组别
                    xRange = xSheet.get_Range(xApp.Cells[2, 1], xApp.Cells[2, 1]);
                    xRange.Value2 = "组别";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    xSheet.Columns.get_Range("A:A", Type.Missing).ColumnWidth = 16;

                    //姓名
                    xRange = xSheet.get_Range(xApp.Cells[2, 2], xApp.Cells[2, 2]);
                    xRange.Value2 = "姓名";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    //节目制作工时
                    xRange = xSheet.get_Range(xApp.Cells[2, 3], xApp.Cells[2, 3]);
                    xRange.Value2 = "节目制作工时";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    //节目制作场次
                    xRange = xSheet.get_Range(xApp.Cells[2, 4], xApp.Cells[2, 4]);
                    xRange.Value2 = "节目制作场次";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    //值班工时
                    xRange = xSheet.get_Range(xApp.Cells[2, 5], xApp.Cells[2, 5]);
                    xRange.Value2 = "值班工时";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    //派单出机工时
                    xRange = xSheet.get_Range(xApp.Cells[2, 6], xApp.Cells[2, 6]);
                    xRange.Value2 = "派单出机工时";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    //其他任务工时
                    xRange = xSheet.get_Range(xApp.Cells[2, 7], xApp.Cells[2, 7]);
                    xRange.Value2 = "其他任务工时";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    //总工时
                    xRange = xSheet.get_Range(xApp.Cells[2, 8], xApp.Cells[2, 8]);
                    xRange.Value2 = "总工时";
                    xRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xRange.WrapText = true;
                    xRange.Font.Size = 12;
                    xRange.Font.Bold = true;
                    #endregion

                    #region 内容
                    for (int i = 0; i < _dt.Rows.Count; i++)
                    {
                        _rowIndex = i + 3;

                        //组别
                        xSheet.get_Range(xApp.Cells[_rowIndex, 1], xApp.Cells[_rowIndex, 1]).Value2 = _dt.Rows[i]["department"].ToString();
                        //姓名
                        xSheet.get_Range(xApp.Cells[_rowIndex, 2], xApp.Cells[_rowIndex, 2]).Value2 = _dt.Rows[i]["username"].ToString();
                        //节目制作工时
                        xSheet.get_Range(xApp.Cells[_rowIndex, 3], xApp.Cells[_rowIndex, 3]).Value2 = _dt.Rows[i]["proLength"].ToString();
                        //节目制作场次
                        xSheet.get_Range(xApp.Cells[_rowIndex, 4], xApp.Cells[_rowIndex, 4]).Value2 = _dt.Rows[i]["proCount"].ToString();
                        //值班工时
                        xSheet.get_Range(xApp.Cells[_rowIndex, 5], xApp.Cells[_rowIndex, 5]).Value2 = _dt.Rows[i]["dutyLength"].ToString();
                        //派单出机工时
                        xSheet.get_Range(xApp.Cells[_rowIndex, 6], xApp.Cells[_rowIndex, 6]).Value2 = _dt.Rows[i]["disLength"].ToString();
                        //其他任务工时
                        xSheet.get_Range(xApp.Cells[_rowIndex, 7], xApp.Cells[_rowIndex, 7]).Value2 = _dt.Rows[i]["otherLength"].ToString();
                        //总工时
                        xSheet.get_Range(xApp.Cells[_rowIndex, 8], xApp.Cells[_rowIndex, 8]).Value2 = _dt.Rows[i]["totalLength"].ToString();
                    }
                    #endregion
                    break;
            }

            xBook.SaveCopyAs(targetDic + targetName);

            #region 代码回收
            try
            {
                if (xSheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xSheet);
                    xSheet = null;
                }
                if (xBook != null)
                {
                    try
                    {
                        xBook.Close(false, System.Type.Missing, System.Type.Missing);
                    }
                    catch { }
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xBook);
                    xBook = null;
                }
                if (xApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
                    xApp = null;
                }
            }
            catch { }
            xApp = null;
            GC.Collect();
            #endregion

            return targetDic + targetName;
        }
        catch (Exception ex)
        {
            ZWL.Common.PublicMethod.errorLog("PrintExcel", ex);

            #region 代码回收
            try
            {
                if (xSheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xSheet);
                    xSheet = null;
                }
                if (xBook != null)
                {
                    try
                    {
                        xBook.Close(false, System.Type.Missing, System.Type.Missing);
                    }
                    catch { }
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xBook);
                    xBook = null;
                }
                if (xApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
                    xApp = null;
                }
            }
            catch { }
            xApp = null;
            GC.Collect();
            #endregion

            return "";
        }
    }