C#高效导出Excel.docx

我的方法不是使用微软的excel操作类，因为那种实在爆慢，一个个单元格循环写，实在太慢了。【不信朋友们可以试试】而使用io写的话半分钟都不用我数据库数据量为：120000行 29列技术要点:1.使用的是StreamWriter sw = new StreamWriter(path, false,Encoding.GetEncoding(gb2312);最终生成文件2.使用StringBuilder sb = new StringBuilder();类把查询出来的数据组合为一句超长字符串一次性插入到excel中，sb.Append(ds.Tables0.Columnsk.ColumnName.ToString() + t);注 意，可不能漏了t 这个是非常重要的！ 因为c# t就等于 键盘上的Tab 朋友们可以试试：打开新的txt然后输入1按Tab，输入2按Tab，输入3按Tab保存，然后打开excel文件 把刚刚保存的txt文件拉进去打开你就发现原来。这样写的话1 2 3 会分别在每个单元格上的了。所以上面才使用 t连起来数据库出来的那堆数据，这样一次性导进去，他们就会按照每个单元格来填充！需要引用：using System.Threading;using System.IO;方法如下：C# code private void button1_Click(object sender, EventArgs e)saveFileDialog1.Title = 保存的excel文件;saveFileDialog1.InitialDirectory = c:;saveFileDialog1.Filter = Excel97-2003 (*.xls)|*.xls|All Files (*.*)|*.*;saveFileDialog1.ShowDialog();if (saveFileDialog1.FileName = | saveFileDialog1.FileName = null)MessageBox.Show(文件名不能为空!);return;string path = saveFileDialog1.FileName;string constr = Data Source=.;Initial Catalog=Exhibition;User ID=sa;Password=;string sql = GetStrSql();DataSet ds=new DataSet();using (SqlConnection con = new SqlConnection(constr)SqlDataAdapter da = new SqlDataAdapter(sql, con);da.Fill(ds);if (ds = null)MessageBox.Show(数据获取有误!);return;WriteExcel(ds, path); public void WriteExcel(DataSet ds, string path)trylong totalCount = ds.Tables0.Rows.Count;lblTip.Text = 共有 + totalCount + 条数据。;Thread.Sleep(1000);long rowRead = 0;float percent = 0;StreamWriter sw = new StreamWriter(path, false,Encoding.GetEncoding(gb2312);StringBuilder sb = new StringBuilder();for (int k = 0; k ds.Tables0.Columns.Count; k+)sb.Append(ds.Tables0.Columnsk.ColumnName.ToString() + t);sb.Append(Environment.NewLine);for (int i = 0; i ds.Tables0.Rows.Count; i+)rowRead+;percent = (float)(100 * rowRead) / totalCount;Pbar.Maximum = (int)totalCount;Pbar.Value = (int)rowRead;lblTip.Text = 正在写入 + percent.ToString(0.00) + %.的数据;System.Windows.Forms.Application.DoEvents();for (int j = 0; j ds.Tables0.Columns.Count; j+)sb.Append(ds.Tables0.Rowsij.ToString() + t);sb.Append(Environment.NewLine);sw.Write(sb.ToString();sw.Flush();sw.Close();MessageBox.Show(已经生成指定Excel文件!);catch (Exception ex)MessageBox.Show(ex.Message);public string GetStrSql()string strSql = select d.* from ( select cBarcode ,max(case halldooridWhen 36 then 1 else 0 end ) 1.1号馆,max(case halldooridWhen 37 then 1 else 0 end ) 2.1号馆,max(case halldooridWhen 38 then 1 else 0 end ) 3.1号馆,max(case halldooridWhen 39 then 1 else 0 end ) 4.1号馆,max(case halldooridWhen 40 then 1 else 0 end ) 5.1号馆,max(case halldooridWhen 41 then 1 else 0 end ) 6.1号馆,max(case halldoorid When 42 then 1 else 0 end ) 8.1号馆,max(case halldooridWhen 43 then 1 else 0 end ) 1.2号馆,max(case halldooridWhen 44 then 1 else 0 end ) 2.2号馆,max(case halldooridWhen 45 then 1 else 0 end ) 3.2号馆,max(case halldooridWhen 46 then 1 else 0 end ) 4.2号馆,max(case halldooridWhen 47 then 1 else 0 end ) 5.2号馆,max(case halldooridWhen 48 then 1 else 0 end ) 9.1号馆,max(case halldooridWhen 49 then 1 else 0 end ) 10.1号馆,max(case halldooridWhen 50 then 1 else 0 end ) 11.1号馆,max(case halldooridWhen 51 then 1 else 0 end ) 12.1号馆,max(case halldooridWhen 52 then 1 else 0 end ) 13.1号馆,max(case halldooridWhen 53 then 1 else 0 end ) 9.2号馆,max(case halldooridWhen 54 then 1 else 0 end ) 10.2号馆,max(case halldooridWhen 55 then 1 else 0 end ) 11.2号馆,max(case halldooridWhen 56 then 1 else 0 end ) 12.2号馆,max(case halldooridWhen 57 then 1 else 0 end ) 13.2号馆,max(case halldooridWhen 58 then 1 else 0 end ) 9.3号馆,max(case halldooridWhen 59 then 1 else 0 end ) 10.3号馆,max(case halldooridWhen 60 then 1 else 0 end ) 11.3号馆,max(case halldooridWhen 61 then 1 else 0 end ) VIP3.2号馆,max(case halldooridWhen 62 then 1 else 0 end ) VIP11.3号馆,max(case halldooridWhen 63 then 1 else 0 end ) VIP14.3号馆from tblReadCardgroup by cBarcode ) d left join ( select cBarcode 观众条码 from (select distinct cBarcode,cReadDate from tblReadCard where cReadDate between 2011-07-08 and 2011-07-11) a group by cBarcode) c on d.cBarcode=c.观众条码 group by cBa