ASP.NET 分Sheet导出EXCEL 2003

　　EXCEL 2003有65536行数据的限制，而企业数据往往超过65536行，多于65536行的Excel导出经常形成困扰。其实，只要一个简单的循环加一点点对Sheet操作的技巧，即可实现大于65536行数据分Sheet导出成Excel的效果。
首先，引用Excel.dll文件：
/Files/xiaomi7732/Excel.rar
　　然后，引用命名空间：using Excel;
　　最后，添加方法：

        /**//// <summary>
        /// ASP.NET，分Sheet导出Excel文件
        /// </summary>
        /// <param name="dv">用于导出的DataView</param>
        /// <param name="tmpExpDir">导出的文件夹，例如~/ExcelDownload/</param>
        /// <param name="refFileName">文件名，例如test.xls</param>
        /// <param name="sheetName">Sheet的名称，如果导出多个Sheet，会自动在名称后面加1、2、3</param>
        /// <param name="sheetSize">每个Sheet包含的数据行数，此数值不包括标题行。所以，对于65536行数据，请将此值设置为65535</param>
        /// <param name="setBorderLine">导出完成后，是否给数据加上边框线</param>
        public static void WebExportToExcel(DataView dv, string tmpExpDir, string refFileName, string sheetName, int sheetSize, bool setBorderLine)
        {
            //设置多少行为一个Sheet
            int RowsToDivideSheet = sheetSize;
            //计算Sheet数
            int sheetCount = (dv.Table.Rows.Count - 1) / RowsToDivideSheet + 1;

            GC.Collect();
            Application excel;
            _Workbook xBk;
            _Worksheet xSt=null;
            excel = new ApplicationClass();
            xBk = excel.Workbooks.Add(true);

            //申明循环中要使用的变量
                int dvRowStart;
                int dvRowEnd;
                int rowIndex = 0;
                int colIndex = 0;

            //对全部Sheet进行操作
            for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
            {
                //初始化Sheet中的变量
                rowIndex = 1;
                colIndex = 1;
                //计算起始行
                dvRowStart = sheetIndex * RowsToDivideSheet;
                dvRowEnd = dvRowStart + RowsToDivideSheet-1;
                if (dvRowEnd > dv.Table.Rows.Count-1)
                {
                    dvRowEnd = dv.Table.Rows.Count - 1;
                }

                //创建一个Sheet
                if (null == xSt)
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, Type.Missing, 1, Type.Missing);
                }
                else
                {
                    xSt = (_Worksheet)xBk.Worksheets.Add(Type.Missing, xSt, 1, Type.Missing);
                }
                
                //设置SheetName
                xSt.Name = sheetName;
                if (sheetCount > 1)
                {
                    xSt.Name += ((int)(sheetIndex + 1)).ToString();
                }

                //取得标题
                foreach (DataColumn col in dv.Table.Columns)
                {
                    //设置标题格式
                    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置标题居中对齐
                    xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;
                    //填值，并进行下一列
                    excel.Cells[rowIndex, colIndex++] = col.ColumnName;
                }
                
                //取得表格中数量
                int drvIndex;
                for(drvIndex=dvRowStart;drvIndex<=dvRowEnd;drvIndex++)
                {
                    DataRowView row=dv[drvIndex];
                    //新起一行，当前单元格移至行首
                    rowIndex++;
                    colIndex = 1;
                    foreach (DataColumn col in dv.Table.Columns)
                    {
                        if (col.DataType == System.Type.GetType("System.DateTime"))
                        {
                            excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
                        }
                        else if (col.DataType == System.Type.GetType("System.String"))
                        {
                            excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
                        }
                        else
                        {
                            excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                        colIndex++;
                    }
                }
                //使用最佳宽度
                Range allDataWithTitleRange = xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex-1]);
                allDataWithTitleRange.Select();
                allDataWithTitleRange.Columns.AutoFit();
                //xSt.get_Range(excel.Cells[1, 1], excel.Cells[rowIndex, colIndex-1]).Columns.AutoFit();
                if (setBorderLine)
                {
                    allDataWithTitleRange.Borders.LineStyle = 1;  
                }
            }

            
            //excel.Visible = true;
            string absFileName = HttpContext.Current.Server.MapPath(System.IO.Path.Combine(tmpExpDir, refFileName));
            xBk.SaveCopyAs(absFileName);

            xBk.Close(false, null, null);

            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);

            xBk = null;
            excel = null;
            xSt = null;
            GC.Collect();
        }