C#将Datatable数据导入到Excel中

Question

C#怎样将不同的Datatable数据导入到同一个Excel的不同工作簿中
怎么多次将数据追加进Execl中求大神们来点代码

Answer 1

自己写入excel，还可以直接把excel关联数据集
http://blog.csdn.net/bdmh/article/details/5857470

Answer 2

我自己写了个将DataGridView里的数据导出到Excel表中using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft;
using Microsoft.Office;
using Microsoft.Office.Interop.Excel;namespace ExportToExcel
{
    public partial class ExportToExcel : Form
    {
        public ExportToExcel()
        {
            InitializeComponent();
        }        private void ExportToExcel_Load(object sender, EventArgs e)
        {
            string sql = @"select top 10 [au_id]
                                  ,au_fname+' '+au_lname name
                                  ,phone
                                  ,address
                                  ,city
                                  FROM [pubs].[dbo].[authors]";            SqlConnection con = new SqlConnection("server=.;database=pubs;uid=sa;pwd=123");            SqlDataAdapter sdr = new SqlDataAdapter(sql, con);            System.Data.DataTable dt = new System.Data.DataTable();            sdr.Fill(dt);            gdvList.DataSource = dt;
        }        private void btnExport_Click(object sender, EventArgs e)
        {
            Export export = new Export();
            export.print(gdvList);
            this.Close();            //bool b = ExportDataGridView(gdvList);
            //if (b == true)
            //{
            //    MessageBox.Show("导出成功！！！");
            //    this.Close();
            //}
            //else
            //{
            //    MessageBox.Show("导出失败！！！");
            //}
        }        #region//导出到Excel表
        /// <summary>
        /// 导出Excel表
        /// </summary>
        /// <param name="dataGridView1">DataGridView控件名</param>
        /// <returns>true为成功，false为失败</returns>
        public static bool ExportDataGridView(DataGridView dataGridView1)
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            
            try
            {
                #region//没有数据的话就不往下执行
                if (dataGridView1.Rows.Count == 0)
                    return false;
                #endregion                #region//获取保存的路径和文件名
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Filter = "导出Excel (*.xls)|*.xls";
                sfd.ShowDialog();
                string fileName = sfd.FileName;
                #endregion                excel.Application.Workbooks.Add(true).Save();
                //要不要显示Excel表
                excel.Visible = false;                #region//判断电脑是否有Excel
                if (excel == null)
                {
                    //MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                #endregion                #region//生成字段名称
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                }
                #endregion                #region//填充数据
                for (int i = 0; i < dataGridView1.ColumnCount - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.ColumnCount; j++)
                    {
                        if (dataGridView1[j, i].ValueType == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "" + dataGridView1[j, i].Value.ToString();
                        }
                        else
                        {
                            excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
                        }
                    }
                }
                #endregion                //保存Excel表
                excel.Save(fileName);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
            finally
            {
                //设置禁止弹出保存和覆盖的询问提示框  
                excel.DisplayAlerts = true;
                excel.AlertBeforeOverwriting = true;                //确保Excel进程关闭  
                excel.Quit();
                excel = null;                //垃圾回收
                GC.Collect();
            }            return true;
        }
        #endregion
    }
}

Answer 3

DataGrid导出Excel
http://dev.mjxy.cn/a-Excel-Export-DataGrid.aspx

Answer 4

NPOI
http://www.cnblogs.com/tonyqus/archive/2009/03/16/1409966.html

Answer 5

#Region "导出excel"
    Protected Sub btOutputExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btOutputExcel.Click
        helpDetail.Style("Visibility") = "hidden"
        DivThirdCor.Style("Visibility") = "hidden"
        DivNY.Style("Visibility") = "hidden"
        DataGridToExcel(dtTempTable)
    End Sub
    Public Sub DataGridToExcel(ByVal DS As DataTable)
        Dim S, TT As String
        Dim I, J As Integer        Dim sw As New System.IO.StringWriter
        S = ""
        For J = 0 To DS.Columns.Count - 1            TT = DS.Columns(J).ColumnName.ToString.ToUpper.Replace("ID", "'ID")
            If TT = "" Then
                TT = "无标题"
            End If
            S += TT + Chr(9)
        Next
        sw.WriteLine(S)
        For I = 0 To DS.Rows.Count - 1            S = ""
            For J = 0 To DS.Columns.Count - 1                S += CStr(DS.Rows(I)(J).ToString).ToString + Chr(9)
            Next
            sw.WriteLine(S)
        Next
        'S = Chr(9) + "系统生成表:〖" + Title + "〗" + " 生成时间:" + Now.ToString + Chr(9)
        'sw.WriteLine(S)
        sw.Close()
        Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(Encoding.UTF8.GetBytes(Session("ModelName").Substring(0, Session("ModelName").LastIndexOf("_")))) + ".xls")
        Response.ContentType = "application/ms-excel"        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
        'EnableViewState = False
        Response.Write(sw)
        Response.End()
    End Sub#End Region
其中dtTempTable，是你操作完数据库之后，查询出来的数据集 放在datatable 里

Answer 6

上面：
  helpDetail.Style("Visibility") = "hidden"
  DivThirdCor.Style("Visibility") = "hidden"
  DivNY.Style("Visibility") = "hidden"
这个没删了。忘了，呵呵~~