海量数据导出excel

转自：http://www.pin5i.com/showtopic.aspx?topicid=28315&page=end

 

对于导出Execl文件大量时 一般做法时间会很长， 下面个人收藏方法，可导海量数据

 

protected void CreateExecl(string swhere,string title) {

    string saveFileName = Server.MapPath("http://www.cnblogs.com/uploads/file/" + title);

    bool fileSaved = false;



    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

    if (xlApp == null) {

      return;

    }



    Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得 sheet1

    System.Data.DataTable table = new System.Data.DataTable();

    table.Columns.Add("tagno", typeof(string));

    table.Columns.Add("AddDate", typeof(string));

    SqlDataReader dr = mydb.ExecuteReaderSQL("SELECT tagno,AddDate FROM tagno WHERE " + swhere + " ORDER BY tagnoid ASC", null);

    while (dr.Read()) {

      table.Rows.Add(dr["tagno"].ToString(), dr["AddDate"].ToString());

    }

    dr.Close();



    long rows = table.Rows.Count;

    Int32 _pagecount = 60000;

    if (rows > 60000) {

      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 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet

        }

        else {

          worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[sc];//取得 sheet1

        }

        string[,] datas = new string[pageRows + 1, 2];

        datas[0, 0] = "吊牌号";

        datas[0, 1] = "生成时间";





        Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);

        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;

          for (int i = 0; i < 2; i++) {

            object obj = table.Rows[r];

            datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();

          }

        }

        Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index +1, 2]);

        fchR.Value2 = datas;

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

        range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, 2]);



        //15代表灰色

        range.Font.Size = 9;

        range.RowHeight = 14.25;

        range.Borders.LineStyle = 1;

        range.HorizontalAlignment = 1;

      }

    }

    else {

      string[,] datas = new string[table.Rows.Count + 2, 2];

      datas[0, 0] = "吊牌号";

      datas[0, 1] = "生成时间";



      Microsoft.Office.Interop.Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 2]);

      range.Interior.ColorIndex = 15;//15代表灰色

      range.Font.Bold = true;

      range.Font.Size = 9;



      int r = 0;

      for (r = 0; r < table.Rows.Count; r++) {

        for (int i = 0; i < 2; i++) {

          //if (gridview.GetVisibleColumn(i).ColumnType == typeof(string) || gridview.GetVisibleColumn(i).ColumnType == typeof(Decimal) || gridview.GetVisibleColumn(i).ColumnType == typeof(DateTime))

          //{

          object obj = table.Rows[r];

          datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();

          //}

        }

      }

      Microsoft.Office.Interop.Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 2, 2]);



      fchR.Value2 = datas;

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

      range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1, 2]);



      //15代表灰色

      range.Font.Size = 9;

      range.RowHeight = 14.25;

      range.Borders.LineStyle = 1;

      range.HorizontalAlignment = 1;

    }



    if (saveFileName != "") {

      //try

      //{

      workbook.Saved = true;

      workbook.SaveCopyAs(saveFileName);

      fileSaved = true;

      //}

      //catch (Exception ex)

      //{

      //    fileSaved = false;

      //}

    }

    else {

      fileSaved = false;

    }



    xlApp.Quit();

    GC.Collect();//强行销毁

    if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL

  }

编辑器加载中...