在WINFORM(C#)如何将数据库中的表中的数据导出,生成EXCEL文件

[lvquanming]

只通过一个BOTTON按纽就从数据库总导出数据，生成EXCEL文件,怎么弄,谢谢!

[ncowboy]

如何在 Microsoft Visual C# .NET 中实现 Microsoft Excel 自动化
http://support.microsoft.com/?scid=kb%3Bzh-cn%3B302084&x=14&y=14

[hhlys]

楼主，这是一个比较复杂的问题。
不是一行或两行代码能说的清的。

[wjhgzx]

给你一个例子参考下。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel;

namespace ExcelReport
{
    public partial class FormExcelNormal : Form
    {
        public FormExcelNormal()
        {
            InitializeComponent();
        }

        private void FormExcelNormal_Load(object sender, EventArgs e)
        {
            DataSet objDataSet = FormUtil.FormGridView("MEMBERINFO");
            dgvMemberInfo.DataSource = objDataSet.Tables["MEMBERINFO"];
        }
        // Excel导出
        private void btnExcelOut_Click(object sender, EventArgs e)
        {
            string strConnect = System.Configuration.ConfigurationManager.AppSettings["connStr"];
            SqlConnection objConnection = new SqlConnection(strConnect);
            SqlCommand objCommand = new SqlCommand("SELECT MEMBERNAME, SEX, AREA, EMAIL FROM MEMBERINFO", objConnection);
            DataSet objDataset = new DataSet();
            SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);
            // 数据填充
            objConnection.Open();
            objAdapter.Fill(objDataset, "Excel");
            objConnection.Close();

            // 创建Excel对象            Excel.Application xlApp = new Excel.Application();
            Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet ws = (Worksheet)wb.Worksheets[1];            

            // 设置标题            xlApp.Cells[1, 1] = "会员姓名";
            xlApp.Cells[1, 2] = "性别";
            xlApp.Cells[1, 3] = "籍贯";
            xlApp.Cells[1, 4] = "电子邮件";

            // 设置格式
            int iMaxRow = objDataset.Tables["Excel"].Rows.Count;
            int iMaxCol = objDataset.Tables["Excel"].Columns.Count;
            ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Name = "黑体";
            ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Bold = true;
            ws.get_Range(ws.Cells[1, 1], ws.Cells[iMaxRow + 1, iMaxCol]).Borders.LineStyle = 1;

            // 填充数据            for (int iRow = 0; iRow < iMaxRow; iRow++)
            {
                for (int iCol = 0; iCol < iMaxCol; iCol++)
                {
                    xlApp.Cells[iRow + 2, iCol + 1] = objDataset.Tables["Excel"].Rows[iRow][iCol].ToString();
                }
            }
            // 保存Excel
            xlApp.Save("sheet1.xls");
            // 打开Excel            xlApp.Visible = true;
        }
    }
}

[wjhgzx]

本来是想在关键位置突出颜色显示，没弄好，再发一遍

using System; 
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel;

namespace ExcelReport
{
    public partial class FormExcelNormal : Form
    {
        public FormExcelNormal()
        {
            InitializeComponent();
        }

        private void FormExcelNormal_Load(object sender, EventArgs e)
        {
            DataSet objDataSet = FormUtil.FormGridView("MEMBERINFO");
            dgvMemberInfo.DataSource = objDataSet.Tables["MEMBERINFO"];
        }
        // Excel导出
        private void btnExcelOut_Click(object sender, EventArgs e)
        {
            string strConnect = System.Configuration.ConfigurationManager.AppSettings["connStr"];
            SqlConnection objConnection = new SqlConnection(strConnect);
            SqlCommand objCommand = new SqlCommand("SELECT MEMBERNAME, SEX, AREA, EMAIL FROM MEMBERINFO", objConnection);
            DataSet objDataset = new DataSet();
            SqlDataAdapter objAdapter = new SqlDataAdapter(objCommand);
            // 数据填充
            objConnection.Open();
            objAdapter.Fill(objDataset, "Excel");
            objConnection.Close();

            // 创建Excel对象
            Excel.Application xlApp = new Excel.Application();
            Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            Worksheet ws = (Worksheet)wb.Worksheets[1];            

            // 设置标题
            xlApp.Cells[1, 1] = "会员姓名";
            xlApp.Cells[1, 2] = "性别";
            xlApp.Cells[1, 3] = "籍贯";
            xlApp.Cells[1, 4] = "电子邮件";

            // 设置格式
            int iMaxRow = objDataset.Tables["Excel"].Rows.Count;
            int iMaxCol = objDataset.Tables["Excel"].Columns.Count;
            ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Name = "黑体";
            ws.get_Range(ws.Cells[1, 1], ws.Cells[1, iMaxCol]).Font.Bold = true;
            ws.get_Range(ws.Cells[1, 1], ws.Cells[iMaxRow + 1, iMaxCol]).Borders.LineStyle = 1;

            // 填充数据
            for (int iRow = 0; iRow < iMaxRow; iRow++)
            {
                for (int iCol = 0; iCol < iMaxCol; iCol++)
                {
                    xlApp.Cells[iRow + 2, iCol + 1] = objDataset.Tables["Excel"].Rows[iRow][iCol].ToString();
                }
            }
            // 保存Excel
            xlApp.Save("sheet1.xls");
            // 打开Excel
            xlApp.Visible = true;
        }
    }
}
 

[lvquanming]

试试看