.net导出Excel几种方式比较

数据原共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());
                }
                //删除多余Sheet
                for (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 directory
            MemoryStream 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 DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation 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