C# 导出Excel速度慢的问题

导出excel 是比较慢的，如果你有几千万条记录的excel，你打开都非常慢了，当然excel不可能存储这么多记录。

这么多记录怎么导入到EXCEL中呢？
难道你导入EXCEL中还有人去看？
记录不多可以开个线程去做导出动作。可能快点。

怎么会要导这么多数据到EXCEL中，导EXCEL速度本来就不快

为什么一定要excel格式呢？lz可以试试导出xml或csv格式，会快很多

private void SaveAs() //另存新档按钮   导出成Excel
        {
            DataTable dt = dc.getTable("select * from table");
            if (dt.Rows.Count == 0)
            {
                MessageBox.Show("没有要输出为excel的数据！");
                return;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";            saveFileDialog.FilterIndex = 0;            saveFileDialog.RestoreDirectory = true;            saveFileDialog.CreatePrompt = true;            saveFileDialog.Title = "生成Excel";
            saveFileDialog.ShowDialog();            Stream myStream;
            try
            {
                myStream = saveFileDialog.OpenFile();
                //StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
                try
                {
                    string str = "字段1\t字段2\t字段3\t";//表头
                    sw.WriteLine(str);

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        string tempStr = string.Empty;
                        tempStr += dt.Rows[i]["a"].ToString() + "\t";
                        tempStr += dt.Rows[i]["b"].ToString() + "\t";
                        tempStr += dt.Rows[i]["c"].ToString() + "\t";
                        sw.WriteLine(tempStr);
                    }
                    //MessageBox.Show("导出成功！");
                }
                catch (Exception e)
                {
                    sw.Close();
                    MessageBox.Show(e.ToString());
                }                finally
                {
                    sw.Close();
                    myStream.Close();
                }
                sw.Close();                myStream.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return;
            }
        }这个速度快，但是缺点是数字的可能会编程科学计数法

或者你看看这个文章：http://ximi82878.blog.hexun.com/27907174_d.html
我转载的，不过忘了从哪转的了

使用二维数组方式很快直接把二维数组的值赋给一个Rang千万不要一个Cell一个Cell的设置值，那会慢死的

你可以试试导出xml或csv格式，会快很多

使用二维数组方式很快直接把二维数组的值赋给一个Rang千万不要一个Cell一个Cell的设置值，那会慢死的

二维数组不错不过要这么多条数据的话。可以考虑xml或csv

不好意思，没瞧见你要插入图片，如果你用的是b/s的话，可以用MyXLS，很好用，如果是C/S的，那我就不知道了，而且我也很想解决这个问题，如果LZ有了答案，请分享下，谢谢了

把dataset数据保存到excel
public void CreateExcel(DataSet ds, string FileName)
    {
        HttpResponse resp;
        resp = Page.Response;
        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
        string colHeaders = "", ls_item = "";
        //定义表对象与行对象，同时用DataSet对其值进行初始化
        DataTable dt = ds.Tables[0];
        DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
        int i = 0;
        int cl = dt.Columns.Count;
        //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
        for (i = 0; i < cl; i++)
        {
            if (i == (cl - 1))//最后一列，加n
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "\n";
            }
            else
            {
                colHeaders += dt.Columns[i].Caption.ToString() + "\t";
            }
        }
        resp.Write(colHeaders);
        //向HTTP输出流中写入取得的数据信息
        //逐行处理数据　
        foreach (DataRow row in myRow)
        {
            //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据　　
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {
                    ls_item += row[i].ToString() + "\n";
                }
                else
                {
                    ls_item += row[i].ToString() + "\t";
                }
            }
            resp.Write(ls_item);
            ls_item = "";
        }
        resp.End();
    }

其实呢,我要做的是类似于一张数据名片,即每一个事物把他的属性信息(名称面积等)做成一个详细表格,不要问我为什么,因为别人给我钱,需要我这么做~我数了一下,一共也就三十个cell,另外,需要往这个表中插入照片(可能是拍出来的照片,也可能是画出来的平面图形)我看了一下,里面最重要的操作就是关对单元格的合并以及边框样式的设置,我用了c#模拟vba来定制,但是感觉比较慢,所以想提高一下速度和效率.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using System.IO;
using System.Text;
/// <summary>
/// Author:匆匆  Blog:http://www.cnblogs.com/huangjianhuakarl/
/// 将Gridview中的数据导出Excel表格
/// </summary>
public partial class GridviewExcel : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bind();
        }
    }
    /// <summary>
    /// 数据绑定
    /// </summary>
    public void bind()
    {
        string sqlStr = "select * from Employee";
        DataSet myds = Common.dataSet(sqlStr);
        GridView1.DataSource = myds;
        GridView1.DataKeyNames = new string[] { "ID" };
        GridView1.DataBind();
    }
    /// <summary>
    /// 在 GridView 控件中的某个行被绑定到一个数据记录时发生。此事件通常用于在某个行被绑定到数据时修改该行的内容。
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        foreach (TableCell tc in e.Row.Cells)
        {
            tc.Attributes["style"] = "border-color:Black";
        }
        if (e.Row.RowIndex != -1)
        {
            int id = GridView1.PageIndex * GridView1.PageSize + e.Row.RowIndex + 1;
            e.Row.Cells[0].Text = id.ToString();
        }
    }
    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        Export("application/ms-excel", "Employee information.xls");
    }
    /// <summary>
    /// 定义导出Excel的函数
    /// </summary>
    /// <param name="FileType"></param>
    /// <param name="FileName"></param>
    private void Export(string FileType, string FileName)
    {
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        GridView1.RenderControl(hw);
        Response.Write(tw.ToString());
        Response.End();
    }
    /// <summary>
    /// 此方法必重写，否则会出错
    /// </summary>
    /// <param name="control"></param>
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //Export("application/ms-excel", "Employee.doc");
        Export("application/ms-word", "员工信息.doc");//都可以
    }
}<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewExportExcel.aspx.cs" Inherits="GridviewExcel" EnableEventValidation="false"  %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Gridview Excel</title>
    <link href="~/CSS/Gridview.css" type="text/css" rel="Stylesheet" />
</head>
<body>
    <form id="form1" runat="server">
    <div id="container">
        <asp:GridView ID="GridView1" BorderColor="Black" OnRowDataBound="GridView1_RowDataBound"   runat="server" AutoGenerateColumns="False"  Font-Size="12px" Width="530px" AllowSorting="True">
          <Columns>
            <asp:BoundField DataField="EmpID" HeaderText="编号"  />
            <asp:BoundField DataField="EmpRealName" HeaderText="姓名"  />
            <asp:BoundField DataField="EmpSex" HeaderText="性别"  />
            <asp:BoundField DataField="EmpAddress" HeaderText="住址"  />
          </Columns>
          <HeaderStyle BackColor="Azure" Font-Size="12px" HorizontalAlign="Center" />
            <RowStyle HorizontalAlign="Center" />
            <PagerStyle HorizontalAlign="Center" />
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" Text="导出 EXCEL 表格" Height="34px" OnClick="Button1_Click" Width="263px" />
        <asp:Button ID="Button2" runat="server" Text="导出 Word 文档" Height="34px" OnClick="Button2_Click" Width="263px" /></div>
    </form>
</body>
</html>
导出文本，图片就

用模版试试。在程序中调用宏，这样的话对Excel有复杂操作的时候相对快点。我导过几千条数据，大约是3-5秒钟吧。生成图什么的都算在内

public bool ExportDataGridview(DataGridView dgv, bool isShowExcle)
        {
            if (dgv.Rows.Count == 0)
                return false;
            //建立Excel对象
            Excel.Application excel = new Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;
            //生成字段名称
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < dgv.RowCount - 1; i++)
            {
                for (int j = 0; j < dgv.ColumnCount; j++)
                {
                    if (dgv[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }

你要设置的表格如果是固定的话，建议你用word画出格式，让后在程序中作为背景插入好了，然后直接检索数据，就不用考虑格式了

我公司的系统也是这样，向导出每年的订单记录到Excel时，都要等好久。
如果楼主有了好的解决办法，请分享。

一个excel最多也就装几万条记录，多了不行建议导出csv格式的

        public void FilltoExcel(System.Data.DataTable deTemp, string strTitle)
        {            GC.Collect();
            Random rand = new Random();
            Microsoft.Office.Interop.Excel.ApplicationClass oexcel = new ApplicationClass();
            if (oexcel == null)
            {                // 无法创建Excel对象，可能您的计算机上未安装Excel软件。                MessageBox.Show("无法创建Excel对象，可能您的计算机上未安装Excel软件。");                return;            }            object omissing = System.Reflection.Missing.Value;
            oexcel.Workbooks.Add(omissing);
            Microsoft.Office.Interop.Excel.Workbook obook = oexcel.Workbooks[1];
            Microsoft.Office.Interop.Excel.Worksheet osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets[1];
            //osheet.Name = "TEST1";
            //osheet.Name = strTitle;
            Microsoft.Office.Interop.Excel.Range rg;
            int i = deTemp.Columns.Count;
            long j = deTemp.Rows.Count;
            if (j < 65534)
            {
                #region
                char c = 'A';
                string[,] datas = new string[j + 1, i];
                for (int it = 0; it < i - 1; it++)
                {
                    c++;
                }
                for (int ig = 0; ig < i; ig++) //写入标题
                {                    datas[0, ig] = deTemp.Columns[ig].ColumnName;                }
                for (int p = 0; p < i; p++)//写入内容
                {
                    for (int q = 0; q < j; q++)
                    {
                        datas[q + 1, p] = deTemp.Rows[q][p].ToString();
                    }
                }
                string rc = Convert.ToString(c) + Convert.ToString(j + 1);
                rg = (Microsoft.Office.Interop.Excel.Range)osheet.get_Range("A1", rc);
                rg.Value2 = datas;
                #endregion
            }
            else
            {
                long pageRows = 60000;//定义每页显示的行数,行数必须小于                int scount = (int)(j / pageRows);                if (scount * pageRows < j)//当总行数不被pageRows整除时，经过四舍五入可能页数不准
                {                    scount = scount + 1;                }                for (int sc = 1; sc <= scount; sc++)
                {                    if (sc > 3)
                    {                        object missing = System.Reflection.Missing.Value;                        osheet = (Microsoft.Office.Interop.Excel.Worksheet)obook.Sheets.Add(                       missing, missing, missing, missing);//添加一个sheet                    }                    else
                    {                        osheet = (Worksheet)obook.Sheets[sc];//取得sheet1
                        //osheet.Name = "Test" + (sc+1).ToString();//测试                    }                    string[,] datas = new string[pageRows + 1, i];                    for (int ig = 0; ig < i; ig++) //写入标题
                    {                        datas[0, ig] = deTemp.Columns[ig].ColumnName;                    }                    int init = int.Parse(((sc - 1) * pageRows).ToString());                    int r = 0;                    int index = 0;                    long result;                    if (pageRows * sc >= j)
                    {                        result = j;                    }                    else
                    {                        result = long.Parse((pageRows * sc).ToString());                    }                    for (r = init; r < result; r++)
                    {                        index = index + 1;                        for (int it = 0; it < i; it++)
                        {                            deTemp.Rows[r][it].ToString();                            object obj = deTemp.Rows[r][it];                            datas[index, it] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
                        }                        System.Windows.Forms.Application.DoEvents();                    }                    Range fchR = osheet.get_Range(osheet.Cells[1, 1], osheet.Cells[index + 1, i]);                    fchR.Value2 = datas;                    //osheet.Columns.EntireColumn.AutoFit();//列宽自适应。
                }
            }

            obook.SaveAs("C:/" + strTitle + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + rand.Next(9999) + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795, omissing, omissing, omissing, omissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, omissing, omissing, omissing, omissing, omissing);
            oexcel.Workbooks.Close();
            oexcel.Quit();
            osheet = null;
            obook = null;
            oexcel = null;
            GC.Collect();

调试易

C# 导出Excel速度慢的问题

解决方案 »