c#快速導出到excel

導出到Excel，基本是很多單據的標配功能了。筆者之前將Datagridview的數據導出到Excel時，將數據一個單元格一個單元格的寫入，效率奇慢，

1030條數據花費了將近70s的時間。後來借鑒了前輩們的其它寫法，將數據先生成到string變數，再寫入，效率提升了150多倍。分享如下：

1、改善後結果：

2、導出到Excel使用的方法：

簡要說明：

TableName：數據源數據表

FileName：要保存的Excel文件名

lblStatus：ToolStripStatusLable

barStatus：ToolStripProgressBar

引用及代碼：

using System.IO;
using System.Data.SqlClient;
using System.Threading;
using System.Diagnostics;

public void ExportDataToExcel(DataTable TableName, string FileName)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            //設置文件標題
            saveFileDialog.Title = "導出Excel文件";
            //設置文件類型
            saveFileDialog.Filter = "Microsoft Office Excel 工作簿(*.xls)|*.xls";
            //設置預設文件類型顯示順序  
            saveFileDialog.FilterIndex = 1;
            //是否自動在文件名中添加擴展名
            saveFileDialog.AddExtension = true;
            //是否記憶上次打開的目錄
            saveFileDialog.RestoreDirectory = true;
            //設置預設文件名
            saveFileDialog.FileName = FileName;
            //按下確定選擇的按鈕  
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                //獲得文件路徑 
                string localFilePath = saveFileDialog.FileName.ToString();

                //數據初始化
                int TotalCount;     //總行數
                int RowRead = 0;    //已讀行數
                int Percent = 0;    //百分比

                TotalCount = TableName.Rows.Count;
                lblStatus.Text = "共有" + TotalCount + "條數據";
                lblStatus.Visible = true;
                barStatus.Visible = true;

                //數據流
                Stream myStream = saveFileDialog.OpenFile();
                StreamWriter sw = new StreamWriter(myStream, Encoding.GetEncoding("gb2312"));
                string strHeader = "";

                //秒鐘
                Stopwatch timer = new Stopwatch();
                timer.Start();

                try
                {
                    //寫入標題
                    for (int i = 0; i < TableName.Columns.Count; i++)
                    {
                        if (i > 0)
                        {
                            strHeader += "\t";
                        }
                        strHeader += TableName.Columns[i].ColumnName.ToString();
                    }
                    sw.WriteLine(strHeader);

                    //寫入數據
                    //string strData;
                    for (int i = 0; i < TableName.Rows.Count; i++)
                    {
                        RowRead++;
                        Percent = (int)(100 * RowRead / TotalCount);
                        barStatus.Maximum = TotalCount;
                        barStatus.Value = RowRead;
                        lblStatus.Text = "共有" + TotalCount + "條數據，已寫入" + Percent.ToString() + "%的數據，共耗時" + timer.ElapsedMilliseconds + "毫秒。";
                        Application.DoEvents();

                        string strData = "";
                        for (int j = 0; j < TableName.Columns.Count; j++)
                        {
                            if (j > 0)
                            {
                                strData += "\t";
                            }
                            strData += TableName.Rows[i][j].ToString();
                        }
                        sw.WriteLine(strData);
                    }
                    //關閉數據流
                    sw.Close();
                    myStream.Close();
                    //關閉秒鐘
                    timer.Reset();
                    timer.Stop();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                finally
                {
                    //關閉數據流
                    sw.Close();
                    myStream.Close();
                    //關閉秒鐘
                    timer.Stop();
                }

                //成功提示
                if (MessageBox.Show("導出成功，是否立即打開？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
                {
                    System.Diagnostics.Process.Start(localFilePath);
                }

                //賦初始值
                lblStatus.Visible = false;
                barStatus.Visible = false;
            }
        }

後記：

美中不足的時，上述代碼僅能導出到xls格式，若需導出到xlsx格式的話，仍需調整代碼。