求C#导出EXCEL高效算法

就是DS中的数据保存到excel中的意思吧？各位大大，都有什么算法？

可以直接用oledb
代码如下：
public static void InsertDataToExcel2003(string DateSourcePath, string insertCommand)
        {
            DataSet ds = new DataSet();
            string execelConnectionStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + DateSourcePath + ";Extended Properties='Excel 8.0;'";
            using (OleDbConnection OleDbconn = new OleDbConnection(execelConnectionStr))
            {
                OleDbconn.Open();
                //DataTable dt = OleDbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //string FileName = dt.Rows[0][2].ToString().Trim();
                OleDbCommand command = new OleDbCommand(insertCommand, OleDbconn);
                command.ExecuteNonQuery();

                OleDbconn.Close();
            }        }

我用的时候，是这样调用的
DataRow row=dtPrePopulate.Rows[i];
                string insertCommand = "Insert into [Data$] ([GUID],[FirstName],[LastName],[Company],[Email],[Country]) Values('" + row["GUID"].ToString() + "','" +
                    row["FirstName"].ToString() + "','" + row["LastName"].ToString() + "','" + row["Company"].ToString() + "','"  + row["Email"].ToString() +
                    "','" + row["Country"].ToString() + "')";                Common.InsertDataToExcel2003(fileNameWithPath, insertCommand);fileNameWithPath 传入一个路径就可以，比如 "D:\aa.xls"

http://sun77.blog.163.com/blog/static/979865732009329102721706/

  导出datagridview中的数据到Excel
private void ExportExcel(DataGridView dgv, bool IsOnlyVisible)
{   int n, i;
    this.Cursor = Cursors.WaitCursor;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//新的excel文档
            excel.Application.Workbooks.Add(true);  //新的工作表
            excel.Visible = true;
            n = 1;
            //列头
            excel.Cells[1, n++] = dgv.Columns[1].HeaderText;//行号
            excel.Cells[1, n++] = dgv.Columns[2].HeaderText;   //内容
            excel.Cells[1, n++] = dgv.Columns[3].HeaderText;
            excel.Cells[1, n++] = dgv.Columns[4].HeaderText;
            excel.Cells[1, n++] = dgv.Columns[5].HeaderText;
            excel.Cells[1, n++] = dgv.Columns[6].HeaderText;
            excel.Cells[1, n++] = dgv.Columns[7].HeaderText;
            excel.Cells[1, n++] = dgv.Columns[8].HeaderText;
            excel.Cells[1, n++] = dgv.Columns[9].HeaderText;
            //记录内容
            for (i = 0; i <= dgv.RowCount - 1; i++)
            {                n = 1;
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[1].Value;//行号
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[2].Value;//内容
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[3].Value;
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[4].Value;
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[5].Value;
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[6].Value;
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[7].Value;
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[8].Value;
                excel.Cells[i + 2, n++] = dgv.Rows[i].Cells[9].Value;
            }            this.Cursor = Cursors.Default;        }

如果只是导入数据的话，不建议使用Microsoft.Office.Interop.Excel.Application这种方法，效率明显不行……
还是俺的Oledb好，而且只用sql就行

高效的话，不太好说，尽量写好点
我这里有一个，挺不错的，看代码吧，很简单
前台代码就不贴了啊
  先绑定数据
        public void bind()
        {
            string sqlStr = "select id,userid,state,type from m_user";//sql语句自己改啊
            mysqlcon = new MySqlConnection(connStr);
            MySqlDataAdapter myda = new MySqlDataAdapter(sqlStr,mysqlcon);
            DataSet myds = new DataSet();
            mysqlcon.Open();
            myda.Fill(myds, "m_user");//表名字
            GridView1.DataSource = myds;
            GridView1.DataKeyNames = new string[] { "type" };
            GridView1.DataBind();
            mysqlcon.Close();

        }
        //导出这是方法  下面在按钮调用这个方法
        public void Export(string FileType, string FileName)
        {
        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        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();
        }点击导出按钮
        protected void Button1_Click(object sender, EventArgs e)
        {
            Export("application/ms-excel", "user.xls");
        }
下面再给个  导入        //导入
        private DataSet CreateDataSource()
        {
            string strCon;
            strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("excel.xls") + "; Extended Properties=Excel 8.0;";
            OleDbConnection olecon = new OleDbConnection(strCon);
            OleDbDataAdapter myda = new OleDbDataAdapter("select * from [user]",strCon);
            DataSet myds = new DataSet();
            myda.Fill(myds);
            //myda.Fill(myds);
            return myds;
        }按钮这样调用导入        protected void Button2_Click(object sender, EventArgs e)
        {
            GridView1.DataSource = CreateDataSource();
            GridView1.DataBind();
        }
代码很清晰，简单
希望有用

保存在那里，不是这么问的吧点击导出按钮会要你选择保存到哪里的
难道还要写成死路径吗楼上几位没思考就回到，.net不熟啊

/// <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)
    {
    }

楼上的方法不错。我一般就用这个导出gridview到excel.

推荐使用NPOI，或是FpSpread(FarPoint.Win.Spread.FpSpread控件，属于FarPoint产品)
再不行就只能用Excel的DLL了。

string fileName,filePath,strLine;
FileStream objFileStream;
StreamWriter objStreamWriter;
fileName = "NewFile.xls";
filePath = "c:\\"+fileName; objFileStream = new FileStream(filePath,FileMode.Create,FileAccess.ReadWrite);
objStreamWriter = new StreamWriter(objFileStream,System.Text.Encoding.Default);
objStreamWriter.AutoFlush=true; strLine = "第一列\t第二列\t第三列";
objStreamWriter.WriteLine(strLine); strLine = "";
DataSet ds = new DataSet();
ds=....(查询出来的数据)
for(int j=0;j<=ds.Tables[0].Rows.Count-1;j++)
{
strLine += "\r";
for (int i = 0; i < ds.Tables[0].Columns.Count-1; i++)
{
strLine = strLine + ds.Tables[0].Rows[j][i].ToString()+"\t";
}
strLine += ds.Tables[0].Rows[j][ds.Tables[0].Columns.Count-1].ToString();
strLine = strLine.Trim();
objStreamWriter.WriteLine(strLine);
strLine="";
}
objStreamWriter.Flush();
objFileStream.Flush();
objStreamWriter.Close();
objFileStream.Close();用这种方式写是最快的。用oledb的方式也比较快。用Excel API的话，如果一行一行写会很慢，但是可以将多行数据划分为一个块，将数据"一块一块"写进去，效率会高很多。

/// <summary>
        /// 将二维数组数据写入Excel文件（自动分页，并指定要合并的列索引）
        /// </summary>
        /// <param name="arr">二维数组</param>
        /// <param name="rows">每个WorkSheet写入多少行数据</param>
        /// <param name="top">行索引</param>
        /// <param name="left">列索引</param>
        /// <param name="mergeColumnIndex">数组的二维索引，相当于DataTable的列索引，索引从0开始</param>
        public void ArrayToExcel(string[,] arr,int rows,int top,int left,int mergeColumnIndex)
        {
            int rowCount = arr.GetLength(0);        //二维数组行数（一维长度）
            int colCount = arr.GetLength(1);    //二维数据列数（二维长度）
            sheetCount = this.GetSheetCount(rowCount,rows); //WorkSheet个数

            //复制sheetCount-1个WorkSheet对象
            for(int i=1;i<sheetCount;i++)
            {
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                workSheet.Copy(missing,workBook.Worksheets[i]);
            }

            //将二维数组数据写入Excel
            for(int i=sheetCount;i>=1;i--)
            {
                int startRow = (i - 1) * rows;      //记录起始行索引
                int endRow = i * rows;          //记录结束行索引

                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
                if(i == sheetCount)
                    endRow = rowCount;

                //获取要写入数据的WorkSheet对象，并重命名
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                workSheet.Name = sheetPrefixName + "-" + i.ToString();

                //将二维数组中的数据写入WorkSheet
                for(int j=0;j<endRow-startRow;j++)
                {
                    for(int k=0;k<colCount;k++)
                    {
                        workSheet.Cells[top + j,left + k] = arr[startRow + j,k];
                    }
                }

                //利用二维数组批量写入
                int row = endRow-startRow;
                string[,] ss = new string[row,colCount];

                for(int j=0;j<row;j++)
                {
                    for(int k=0;k<colCount;k++)
                    {
                        ss[j,k] = arr[startRow + j,k];
                    }
                }

                range = (Excel.Range)workSheet.Cells[top,left];
                range = range.get_Resize(row,colCount);
                range.Value = ss;

                //合并相同行
                this.MergeRows(workSheet,left+mergeColumnIndex,top,rows);
            }

        }

说个各位没说的方法：            string filename = "";
            if (DialogResult.OK == saveFileDlg.ShowDialog())
            {
                filename = saveFileDlg.FileName;
                //execl 导出
                String source = null;
                OdbcConnection conn = null;                try
                {
                    source = "Driver={Microsoft Excel Driver (*.xls)};FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\""+filename +"\";DBQ=" + filename ;
                    conn = new OdbcConnection(source);
                    conn.Open();
                }
                catch
                {
                    try
                    {
                        source = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\""+filename +"\";DBQ=" + filename ;
                        conn = new OdbcConnection(source);
                        conn.Open();
                    }
                    catch
                    {
                        MessageBox.Show("请确认此文件没有被其它程序打开！");
                        return;
                    }
                }                if (dataSet1.Tables.Count <= 0) return;
                try
                {
                    string s = "";
                    string f = "";
                    string[] numlx = new string[] { "System.Int32", "System.Int16", "System.Int64", "System.Decimal", "System.Single", "System.Double" };                    for (int i = 0; i < dataSet1.Tables[0].Columns.Count; i++)
                    {
                        if (numlx.Contains(dataSet1.Tables[0].Columns[i].DataType.ToString()))
                        {
                            s = s + ",[" + dataSet1.Tables[0].Columns[i].ColumnName + "] NUMBER";
                        }
                        else
                        {
                            s = s +",[" +dataSet1.Tables[0].Columns[i].ColumnName + "] TEXT";
                        }
                        f = f + ",[" + dataSet1.Tables[0].Columns[i].ColumnName+"]";
                    }
                    s = "CREATE TABLE " + dataSet1.Tables[0].TableName + "(" + s.Substring(1) + ")";
                    f = "insert into " + dataSet1.Tables[0].TableName + "(" + f.Substring(1) + ") values(";
                    OdbcCommand cmd1 = new OdbcCommand(s, conn);
                    cmd1.ExecuteNonQuery();
                    foreach (DataRow dr in dataSet1.Tables[0].Rows)
                    {
                        string sz = "";
                        for (int i = 0; i < dataSet1.Tables[0].Columns.Count; i++)
                        {
                            if (dr[i] != DBNull.Value)
                            {
                                sz = sz + ",'" + dr[i].ToString() + "'";
                            }
                            else
                            {
                                sz = sz + ",null";
                            }
                        }
                        sz = sz.Substring(1) + ")";
                        cmd1.CommandText = f + sz;
                        cmd1.ExecuteNonQuery();
                    }
                    MessageBox.Show("导出完毕！   ", "导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch(Exception e1)
                {
                    MessageBox.Show("发生错误：  " + e1.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
http://www.mybuffet.cn

当然速度是很快的！http://www.mybuffet.cn

参考C# WinForm开发系列 - Excel

那如果是html中的表格呢？如何导入word中去，我用的是javascript代码，导出来的文件后缀名为什么是mht。而且还不能顺利的一次就关闭word文件，必须要关两次。
这个是用来导出的js代码
function tableToWord() {
    var oWD = new ActiveXObject("Word.Application");
    var oDC = oWD.Documents.Add("", 0, 1);
    var oRange = oDC.Range(0, 1);
    var sel = document.body.createTextRange();
    sel.moveToElementText(theObjTable);
    sel.select();
    sel.execCommand("Copy");
    oRange.Paste();
    oWD.Application.Visible = true;
}

用ADO.NET的方法是最简单的
string strCon;
            strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 路径 + "; Extended Properties=Excel 8.0;";
            OleDbConnection olecon = new OleDbConnection(strCon);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [sheet$1]",strCon);
            DataSet ds = new DataSet();
            da.Fill(ds);

            datatable dt = new datatable()
            dt = ds.tables[0];
            //下边提取dt里边单元格的值我就不详细写了
              for(i=0,i<dt.cloums.count -1;i++)
              {
                 for (j=0,j<dt.rows.cout -1;j++)
                    {
                       string str = dt.rows[j][i].tostring.trim();//这里得到每个单元格的值
                     }
               }

最简单的方法：使用第三方控件。Developer.Express 它的报表系统就带导出excel doc 等功能！要是下载不到发信到我邮箱要哦:[email protected]朋友们关注一下我的小游戏网哦。http://www.51wanyouxi.com

因为程序员的工资不高，所以要用最简短的代码来完成将DataTable中的数据导入EXCEL public static void ExportToExcel(System.Data.DataTable eDataTable, string[] headers)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp;
            ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbook ExcelBook = ExcelApp.Workbooks.Add(1);
            Microsoft.Office.Interop.Excel.Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];
            ExcelApp.Visible = true;
            //写列标题
            for (int i = 0; i < eDataTable.Columns.Count; i++)
            {
                ExcelSheet.Cells[1, i + 1] = eDataTable.Columns[i].ColumnName;
            }
            //写值
            for (int r = 0; r < eDataTable.Rows.Count; r++)
            {
                for (int i = 0; i < eDataTable.Columns.Count; i++)
                {
                    ExcelSheet.Cells[r + 2, i + 1] = eDataTable.Rows[r][i].ToString();
                }
            }
            GC.Collect();
        }

如果牵扯到存储过程分页的gridview的话，这种方法该怎么解决？

             /// <summary>
             /// 导出到excel文件
             /// </summary>
             /// <param name="dgv"></param>
        public  void dataGridViewToExcel(DataGridView dgv)
        {
#region   验证可操作性
            //判断是否安装excel
            bool ExcelYOrN=isExcelInstalled();
            if(ExcelYOrN==false)
            {
                MessageBox.Show("您的系统未安装Excel2003,请安装Excel后再执行本操作", "温馨提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
             }
            //申明保存对话框
            SaveFileDialog dlg = new SaveFileDialog();
            //默然文件后缀
            dlg.DefaultExt = "xls ";
            //文件后缀列表
            dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
            //默然路径是系统当前路径
            dlg.InitialDirectory = Directory.GetCurrentDirectory();
            //打开保存对话框
            if (dlg.ShowDialog() == DialogResult.Cancel) return;
            //返回文件路径
            string fileNameString = dlg.FileName;
            //验证strFileName是否为空或值无效
            if (fileNameString.Trim() == " ")
            { return; }
            //定义表格内数据的行数和列数
            int rowscount = dgv.Rows.Count;
            int colscount = dgv.Columns.Count;
            //行数必须大于0
            if (rowscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }            //列数必须大于0
            if (colscount <= 0)
            {
                MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }            //行数不可以大于65536
            if (rowscount > 65536)
            {
                MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }            //列数不可以大于255
            if (colscount > 255)
            {
                MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }            //验证以fileNameString命名的文件是否存在，如果存在删除它
            FileInfo file = new FileInfo(fileNameString);
            if (file.Exists)
            {
                try
                {
                    file.Delete();
                }
                catch (Exception error)
                {
                    MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }
#endregion

            {

#region excel中添加datagridview内容
                Excel.Application objExcel = null;
                Excel.Workbook objWorkbook = null;
                Excel.Worksheet objsheet = null;
                try
                {
                    //申明对象
                    objExcel = new Excel.Application();
                    objWorkbook = objExcel.Workbooks.Add(Missing.Value);
                    objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
                    //设置EXCEL不可见
                    objExcel.Visible = false;                    //向Excel中写入表格的表头
                    int displayColumnsCount = 1;
                    for (int i = 0; i <= dgv.ColumnCount - 1; i++)
                    {
                        if (dgv.Columns[i].Visible == true)
                        {
                            objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
                            displayColumnsCount++;
                        }
                    }
                    //向Excel中逐行逐列写入表格中的数据
                    for (int row = 0; row <= dgv.RowCount - 1; row++)
                    {
                        //tempProgressBar.PerformStep();                           displayColumnsCount = 1;
                            for (int col = 0; col < colscount; col++)
                            {
                                    if (dgv.Columns[col].Visible == true)
                                    {
                                            try
                                            {
                                                objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
                                                displayColumnsCount++;
                                            }
                                            catch (Exception)
                                            {                                            }                                    }
                            }
                        }
                        //保存文件
                        objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                                Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
                                Missing.Value, Missing.Value);
                    }
                    catch (Exception error)
                    {
                        MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }
                    finally
                    {
                        //关闭Excel应用
                        if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
                        if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
                        if (objExcel != null) objExcel.Quit();                        objsheet = null;
                        objWorkbook = null;
                        objExcel = null;
                    }
                    MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);                 }#endregion
             }

用數據流導出EXCEL吧！我已在CSDN下載區中放了一個范例，你可去下載參考吧！

调试易

求C#导出EXCEL高效算法

解决方案 »