數據原共400條數據,21列,我是雙核cpu,4G內存
1. Excel com組件
要3秒左右,上千條30秒+這種方法比較慢，要引用Microsoft.Office.Interop.Excel

#region DataSet導入到Excel里（最原始樣式）/// <summary>/// DataSet導入到Excel里，多個DataTable分成多個Sheet,Sheet名以TableName命名/// </summary>/// <param name="DS">要導入的Excel</param>/// <param name="FilePathAndName">要保存的路徑和文件名（絕對路徑)</param>public static void DataSetToExcel(DataSet DS, string FilePathAndName){string strName = FilePathAndName.Replace(@"\\", @"\").Replace(@"\\", @"\").Replace(@"\\", @"\");strName = FilePathAndName;Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();try{excel.Visible = false;//設置禁止彈出保存和覆蓋的詢問提示框excel.DisplayAlerts = false;excel.AlertBeforeOverwriting = false;//增加一個工作簿Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Add(true);System.Reflection.Missing miss = System.Reflection.Missing.Value;//添加工作表Microsoft.Office.Interop.Excel.Worksheet sheets = (Microsoft.Office.Interop.Excel.Worksheet)book.Worksheets.Add(miss, miss, 19, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);for (int i = 0; i < DS.Tables.Count; i++){System.Data.DataTable table = DS.Tables[i];//獲取一個工作表//Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[i + 1] as Microsoft.Office.Interop.Excel.Worksheet; Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets.Add(book.Worksheets[i + 1], Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;int rowIndex = 1;int colIndex = 0;foreach (DataColumn col in table.Columns){colIndex++;sheet.Cells[1, colIndex] = col.ColumnName;Microsoft.Office.Interop.Excel.Range Range1 = (Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, colIndex];Range1.Font.Bold = true;//表頭字體加粗Range1.Borders.Value = 1;}foreach (DataRow row in table.Rows){rowIndex++;colIndex = 0;foreach (DataColumn col in table.Columns){colIndex++;sheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();}}//sheet.Name = tableNames[i];sheet.Name = GetLegalFileName(DS.Tables[i].TableName.ToString());}//刪除多余Sheetfor (int g = 1; g <= book.Worksheets.Count; g++){Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[g] as Microsoft.Office.Interop.Excel.Worksheet;if (sheet.Name.Length > 5 && sheet.Name.Substring(0, 5) == "Sheet"){sheet.Delete();g--;}}//book.Save(); book.SaveAs(strName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);book.Close(false, miss, miss);book = null;}catch (Exception){throw;}finally{KillExcelProcess(excel);//結束Excel進程 GC.WaitForPendingFinalizers();GC.Collect();}}#endregion

?

2. OLEDB

這個需要3秒左右，要using System.Data.OleDb;

#region oledb方式public static void ExcelExport(DataTable dt, string filepath, string tablename){//excel 2003格式string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";//Excel 2007格式//string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0 Xml;";try{using (OleDbConnection con = new OleDbConnection(connString)){con.Open();StringBuilder strSQL = new StringBuilder();strSQL.Append("CREATE TABLE ").Append("[" + tablename + "]");strSQL.Append("(");for (int i = 0; i < dt.Columns.Count; i++){strSQL.Append("[" + dt.Columns[i].ColumnName + "] text,");}strSQL = strSQL.Remove(strSQL.Length - 1, 1);strSQL.Append(")");OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);cmd.ExecuteNonQuery();for (int i = 0; i < dt.Rows.Count; i++){strSQL.Clear();StringBuilder strfield = new StringBuilder();StringBuilder strvalue = new StringBuilder();for (int j = 0; j < dt.Columns.Count; j++){strfield.Append("[" + dt.Columns[j].ColumnName + "]");strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");if (j != dt.Columns.Count - 1){strfield.Append(",");strvalue.Append(",");}else{}}cmd.CommandText = strSQL.Append(" insert into [" + tablename + "]( ").Append(strfield.ToString()).Append(") values (").Append(strvalue).Append(")").ToString();cmd.ExecuteNonQuery();}con.Close();}Console.WriteLine("OK");}catch (Exception ex){Console.WriteLine(ex.Message);}

?

3.NPOI
?這個在有二種方式，第一種在web頁面下，很快，秒的速度，但是下下來的方式沒有保存在某一目錄下

aspx頁只有一個按鈕就可以了，.cs文件代碼如下

using System; using System.Web; using System.Web.UI; using System.Web.UI.WebControls;using System.IO; using NPOI.HSSF.UserModel; using NPOI.HPSF; using NPOI.POIFS.FileSystem; using NPOI.SS.UserModel;namespace ExportXlsToDownload {public partial class _Default : System.Web.UI.Page{protected void Page_Load(object sender, EventArgs e){}protected void Button1_Click(object sender, EventArgs e){string filename="test.xls";Response.ContentType = "application/vnd.ms-excel";Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}",filename));Response.Clear();InitializeWorkbook();GenerateData(); GetExcelStream().WriteTo(Response.OutputStream);Response.End();}HSSFWorkbook hssfworkbook;MemoryStream GetExcelStream(){ //Write the stream data of workbook to the root directoryMemoryStream file = new MemoryStream();hssfworkbook.Write(file);return file;}void GenerateData(){ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");int x = 1;for (int i = 1; i <= 400; i++){IRow row = sheet1.CreateRow(i);for (int j = 0; j < 20; j++){row.CreateCell(j).SetCellValue(x++);}}}void InitializeWorkbook(){hssfworkbook = new HSSFWorkbook();////create a entry of DocumentSummaryInformationDocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();dsi.Company = "NPOI Team";hssfworkbook.DocumentSummaryInformation = dsi;////create a entry of SummaryInformationSummaryInformation si = PropertySetFactory.CreateSummaryInformation();si.Subject = "NPOI SDK Example";hssfworkbook.SummaryInformation = si;}} }

第二種方式,可以保存在目錄下,也是秒存，代碼如下

public class NPOIExcel{//最大數據條數readonly int EXCEL03_MaxRow = 65535;/// <summary>/// 將DataTable轉換為excel2003格式。/// </summary>/// <param name="dt"></param>/// <returns></returns>public byte[] DataTable2Excel(DataTable dt, string sheetName){IWorkbook book = new HSSFWorkbook();if (dt.Rows.Count < EXCEL03_MaxRow)DataWrite2Sheet(dt, 0, dt.Rows.Count - 1, book, sheetName);else{int page = dt.Rows.Count / EXCEL03_MaxRow;for (int i = 0; i < page; i++){int start = i * EXCEL03_MaxRow;int end = (i * EXCEL03_MaxRow) + EXCEL03_MaxRow - 1;DataWrite2Sheet(dt, start, end, book, sheetName + i.ToString());}int lastPageItemCount = dt.Rows.Count % EXCEL03_MaxRow;DataWrite2Sheet(dt, dt.Rows.Count - lastPageItemCount, lastPageItemCount, book, sheetName + page.ToString());}MemoryStream ms = new MemoryStream();book.Write(ms);return ms.ToArray();}private void DataWrite2Sheet(DataTable dt, int startRow, int endRow, IWorkbook book, string sheetName){ISheet sheet = book.CreateSheet(sheetName);IRow header = sheet.CreateRow(0);for (int i = 0; i < dt.Columns.Count; i++){ICell cell = header.CreateCell(i);string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;cell.SetCellValue(val);}int rowIndex = 1;for (int i = startRow; i <= endRow; i++){DataRow dtRow = dt.Rows[i];IRow excelRow = sheet.CreateRow(rowIndex++);for (int j = 0; j < dtRow.ItemArray.Length; j++){excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());}}}}

第二種方式調用如下:

DataTable dt=GetData();
NPOIExcel myhelper = new NPOIExcel();byte[] data = myhelper.DataTable2Excel(dt,"sheet");string path = "d:\\temp" + DateTime.Now.Ticks.ToString() + ".xls";if (!File.Exists(path)){FileStream fs = new FileStream(path, FileMode.CreateNew);fs.Write(data, 0, data.Length);fs.Close();}

?

?

轉載于:https://www.cnblogs.com/q149072205/p/3364144.html

總結

以上是生活随笔為你收集整理的.net导出Excel几种方式比较的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。