private void button5_Click(object sender, EventArgs e) { Stopwatch watch = new Stopwatch(); BackgroundWorker bw = new BackgroundWorker(); bw.DoWork += new DoWorkEventHandler(delegate { watch.Start(); using (SqlConnection conn = new SqlConnection("server=192.168.255.1; database=db1;uid=sa;pwd=sa;")) { conn.Open(); SqlCommand cmd = new SqlCommand("select * from tblMRP", conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); var col = ds.Tables[0].Columns.Add("NO", typeof(string)); col.SetOrdinal(0); using (ExcelPackage p = new ExcelPackage(new FileInfo(AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid().ToString() + ".xlsx"))) { int dd = 1000000; int pages = (int)ds.Tables[0].Rows.Count / dd + 1; if (ds.Tables[0].Rows.Count % dd == 0) pages = pages - 1; for (int i = 1; i <= pages; i++) { DataTable dt = DtSelectTop((i - 1) * dd, Math.Min(i * dd, ds.Tables[0].Rows.Count), ds.Tables[0]); var ws = p.Workbook.Worksheets.Add(ds.Tables[0].TableName + i.ToString()); ws.Cells["A1"].LoadFromDataTable(dt, true); } p.Save(); } } watch.Stop(); }); bw.RunWorkerCompleted += new RunWorkerCompletedEventHandler(delegate { MessageBox.Show("用时:" + watch.Elapsed.ToString()); }); bw.RunWorkerAsync(); } public DataTable DtSelectTop(int from ,int to , DataTable oDT) { if (oDT.Rows.Count < from) return oDT; DataTable NewTable = oDT.Clone(); DataRow[] rows = oDT.Select("1=1"); for (int i = from; i < to; i++) { ((DataRow)rows[i])["NO"] = "#" + (i+1).ToString() ; NewTable.ImportRow((DataRow)rows[i]); } return NewTable; }
方法二
public static void ExportByEPPlus(DataTable sourceTable) { Stopwatch watch = new Stopwatch(); watch.Start(); var col = sourceTable.Columns.Add("NO", typeof(string)); col.SetOrdinal(0); FileInfo zfile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + Guid.NewGuid().ToString() + ".xlsx"); int dd = 1000000 / 1; int pages = (int)sourceTable.Rows.Count / dd + 1; if (sourceTable.Rows.Count % dd == 0) pages = pages - 1; using (ExcelPackage p = new ExcelPackage(zfile)) { using (sourceTable) { for (int i = 1; i <= pages; i++) { ExcelWorksheet ws = p.Workbook.Worksheets.Add(sourceTable.TableName + i.ToString()); int zstart = (i - 1) * dd; int zend = Math.Min(i * dd, sourceTable.Rows.Count); for (int zrow = zstart; zrow < zend; zrow++) { for (int zcol = 0; zcol < sourceTable.Columns.Count; zcol++) { ws.Cells[zrow + 1 - zstart, zcol + 1].Value = sourceTable.Rows[zrow][zcol]; } //Console.WriteLine($"pageindex:{i} ,rows:{zrow}"); } } } GC.Collect(0, GCCollectionMode.Forced); GC.WaitForPendingFinalizers(); p.Save(); } watch.Stop(); Console.Write("用时:" + watch.Elapsed.ToString()); Console.Read(); }
private static bool MergeExcel(string _stFilePath, List<string> _listFiles, string _stSaveFileName) { ExcelPackage epMergeFile = new ExcelPackage(); bool result = true; try { string stSheetName = string.Empty; foreach (string item in _listFiles) { if (!FileIsHave(_stFilePath + "\" + item)) continue; FileInfo newFile = new FileInfo(_stFilePath + "\" + item); //开启 using (ExcelPackage pck = new ExcelPackage(newFile)) { //设定ExcelWorkBook ExcelWorkbook workBook = pck.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { //设置excel表格的页边距 //workBook.Worksheets[1].PrinterSettings.TopMargin = 10M / 2.54M; stSheetName = item.Substring(0, item.LastIndexOf('.')); epMergeFile.Workbook.Worksheets.Add(stSheetName, workBook.Worksheets[1]); } } } } } catch (Exception ex) { result = false; Debug.WriteLine("合并文件失败:" + ex.Message); throw new Exception("合并文件失败!"); } if (result) { string stFile = _stFilePath + "\" + _stSaveFileName; epMergeFile.SaveAs(new FileInfo(stFile)); epMergeFile.Dispose(); } return result; }