ASP.Net海量数据导出到Excel

 

protected void CreateExecl(string title)
        {
            string saveFileName = Server.MapPath("~/" + title);
           
            Application xlApp = new Application();

            if (xlApp == null)
            {
                return;
            }

            Workbooks workbooks = xlApp.Workbooks;
            Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet worksheet = (Worksheet)workbook.Worksheets[1];//取得sheet1

            System.Data.DataTable table = DbHelper.ExecuteDataTable("SELECT * FROM ctest");
            long rows = table.Rows.Count;
            Int32 _pagecount = 30000;

            if (rows > _pagecount)
            {
                long pageRows = _pagecount;//定义每页显示的行数,行数必须小于
                int scount = (int)(rows / pageRows);

                if (scount * pageRows < table.Rows.Count)//当总行数不被pageRows整除时，经过四舍五入可能页数不准
                {
                    scount = scount + 1;
                }

                for (int sc = 1; sc <= scount; sc++)
                {
                    if (sc > 1)
                    {
                        object missing = System.Reflection.Missing.Value;
                        worksheet = (Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet
                    }
                    else
                    {
                        worksheet = (Worksheet)workbook.Worksheets[sc];//取得sheet1
                    }

                    FillSheet(worksheet, table, pageRows, sc);
                }
            }

            else
            {
                FillSheet(worksheet, table, table.Rows.Count+1, 1);
            }

            workbook.SaveAs(saveFileName);
            workbook.Close();
            xlApp.Application.Workbooks.Close();
            xlApp.Application.Quit();
            xlApp.Quit();
            worksheet = null;
            workbook = null;
            workbooks = null;
            xlApp = null;

            GC.Collect();
        }

        private static void FillSheet(Worksheet worksheet, System.Data.DataTable table, long pageRows, int sc)
        {
            int maxCount = 5000;
            string[,] datas = new string[maxCount + 2, table.Columns.Count];
            for (int i = 0; i < table.Columns.Count; i++)
            {
                object obj = table.Columns[i].ColumnName;
                datas[0, i] = obj == null ? "" : "'" + obj.ToString().Trim();
            }

            Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, table.Columns.Count]);
            range.Interior.ColorIndex = 15;//15代表灰色
            range.Font.Bold = true;
            range.Font.Size = 9;

            int init = int.Parse(((sc - 1) * pageRows).ToString());
            int r = 0;
            int index = 0;
            Int32 result;

            if (pageRows * sc >= table.Rows.Count)
            {
                result = table.Rows.Count;
            }
            else
            {
                result = int.Parse((pageRows * sc).ToString());
            }

            for (r = init; r < result; r++)
            {
                index = index + 1;
                int rIndex = index % maxCount;
               
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    object obj = table.Rows[r][i];
                    datas[rIndex, i] = obj == null ? "" : "'" + obj.ToString().Trim();
                }

                if (index % maxCount == 0)
                {
                    int pIndex = maxCount / index;
                    Range fchR = worksheet.get_Range(worksheet.Cells[(pIndex * maxCount) + 1, 1], worksheet.Cells[(pIndex * maxCount) + rIndex + 1, table.Columns.Count]);
                    fchR.Value2 = datas;
                    datas = new string[maxCount + 1, table.Columns.Count];
                }
            }

            range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, table.Columns.Count]);
            range.Font.Size = 9;
            range.RowHeight = 14.25;
            range.Borders.LineStyle = 1;
            range.HorizontalAlignment = 1;

            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
        }