C# 数据导出Excel

最新推荐文章于 2024-09-12 15:39:41 发布

xieyongbao

最新推荐文章于 2024-09-12 15:39:41 发布

阅读量712

点赞数

分类专栏： C# 文章标签： c# string excel file exception user

C# 专栏收录该内容

40 篇文章 0 订阅

订阅专栏

上次贴一段Excel导入数据到数据库的函数，忘了把数据库导出数据到Excel的贴上来了，现在补上！！！

导出到Excel我用了一个进度条，显示导出的进度，所以函数我也一起写在窗体代码里了！

以下是窗体截图：

以下是代码：

using System;
using Excel;
using Microsoft.Office.Core;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;

namespace MedicineSaleSystem
{
    public partial class XForm_JDT : DevExpress.XtraEditors.XtraForm
    {
        private System.Data.DataTable MyExcel_D = new System.Data.DataTable();
        private System.Data.DataTable MyExcel_LM = new System.Data.DataTable();
        private string MyExcel_Name = "";
        private string MyExCEL_User = "";
        private string MyExcel_File = "";
        private string MyExcel_Cap = "";

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="Cap_s">标题名称</param>
        /// <param name="MyExcel_Data">数据源</param>
        /// <param name="MyExcel_LieMing">列名表</param>
        /// <param name="MyExcel_N">Excel名称</param>
        /// <param name="MyExcel_Un">操作员名称</param>
        /// <param name="MyExcel_F">保存路径</param>
        public XForm_JDT(string Cap_s, System.Data.DataTable MyExcel_Data,System.Data.DataTable MyExcel_LieMing, string MyExcel_N, string MyExcel_Un, string MyExcel_F)
        {
            InitializeComponent();

this.groupControl1.Text = Cap_s + "——请稍后胡……";
MyExcel_Cap = this.groupControl1.Text;

            MyExcel_D = MyExcel_Data;
            MyExcel_LM = MyExcel_LieMing;
            MyExcel_Name = MyExcel_N;
            MyExCEL_User = MyExcel_Un;
            MyExcel_File = MyExcel_F;

this.pBarControl.Properties.Maximum = MyExcel_D.Rows.Count;
this.pBarControl.Properties.Minimum = 0;

this.groupControl1.Text += " (0/"+MyExcel_D.Rows.Count.ToString()+")";

this.ShowDialog();

if (this.DaoChuExcel())
DevExpress.XtraEditors.XtraMessageBox.Show("成功导出Excel文档！", "提示");

this.Close();
}

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="MyExcel_Data">数据源</param>
        /// <param name="MyExcel_LieMing">列名表</param>
        /// <param name="MyExcel_N">Excel名称</param>
        /// <param name="MyExcel_Un">操作员名称</param>
        /// <param name="MyExcel_F">保存路径</param>
        ///
        public XForm_JDT(System.Data.DataTable MyExcel_Data,System.Data.DataTable MyExcel_LieMing, string MyExcel_N, string MyExcel_Un, string MyExcel_F)
        {//这几个参数，自己可以看着来改吧！我是为了方便自己的程序随便设的！
            InitializeComponent();

MyExcel_Cap = this.groupControl1.Text;

            MyExcel_D = MyExcel_Data;
            MyExcel_LM = MyExcel_LieMing;
            MyExcel_Name = MyExcel_N;
            MyExCEL_User = MyExcel_Un;
            MyExcel_File = MyExcel_F;

this.pBarControl.Properties.Maximum = MyExcel_D.Rows.Count;
this.pBarControl.Properties.Minimum = 0;//这个是进度条的控件

this.groupControl1.Text += " (0/" + MyExcel_D.Rows.Count.ToString() + ")";//这个是那个框框控件，不知道怎么叫，看名字应该知道了吧！
}

        /// <summary>
        /// 导出到Excel文档
        /// </summary>
        /// <returns>True-导出成功；False-导出失败</returns>
        public bool DaoChuExcel()
        {
            ApplicationClass MyExcel_App = new ApplicationClass();

            if (MyExcel_App == null)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("没能启动Excel程序！请确保你已经Office的相关组件！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

            Workbooks MyExcel_Ws = MyExcel_App.Workbooks;
            Workbook MyExcel_W = MyExcel_Ws.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Worksheet MyExcel_S = (Worksheet)MyExcel_W.Worksheets[1];
            MyExcel_S.Name = MyExcel_Name;

            Range MyExcel_R = MyExcel_S.get_Range(MyExcel_S.Cells[1, 1], MyExcel_S.Cells[2, MyExcel_D.Columns.Count]);//这里设置表头标题，位置可以自己改！
            MyExcel_R.Merge(0);
            MyExcel_R.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            MyExcel_R.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            MyExcel_R.Value2 = MyExcel_Name;

MyExcel_S.Cells[3, 1] = "操作员：" + MyExCEL_User;
MyExcel_S.Cells[3, MyExcel_D.Columns.Count] = "导出时间："+DateTime.Now;

            for (int a = 0; a < MyExcel_D.Columns.Count; a++)
            {
                DataRow[] MyRow_LM = this.MyExcel_LM.Select(this.MyExcel_LM.Columns[0].ColumnName + "='" + MyExcel_D.Columns[a].ColumnName+"'");
                MyExcel_S.Cells[4, a + 1] = MyRow_LM[0][1].ToString();
            }

            for (int b = 0; b < MyExcel_D.Rows.Count; b++)
            {
                for (int c = 0; c < MyExcel_D.Columns.Count; c++)
                {
                    MyExcel_S.Cells[b + 5, c + 1] = "'" + MyExcel_D.Rows[b][c];

}

                if (int.Parse(this.pBarControl.Text) < this.pBarControl.Properties.Maximum)
                {
                    int LS = int.Parse(this.pBarControl.Text) + 1;
                    this.pBarControl.Text = LS.ToString();
                }

                int Rows_i = b + 1;
                this.groupControl1.Text = MyExcel_Cap + " (" + Rows_i.ToString()+ "/" + MyExcel_D.Rows.Count.ToString() + ")";
                System.Windows.Forms.Application.DoEvents();
            }

MyExcel_S.Cells.EntireColumn.AutoFit();

            try
            {
                MyExcel_W.Saved = true;
                MyExcel_W.SaveCopyAs(MyExcel_File);
            }
            catch (Exception Err_E)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("导出错误！/n" + Err_E.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }

            MyExcel_App.Quit();
            return true;
          }

}
}

这个设计其实不是很好！因为我觉得进度条的显示总是不太理想！刚显示的时候，有一小段黑！另外显示这个窗体的时候也不能用Show()，下面我也把调用的部分代码贴一下，我也是尝试了好多次，才试到的！

XForm_JDT MyExcel_C = new XForm_JDT(MyExcel_T, MyTab_LM, "采购单据", QuanJuHanShu.UersN, MyExcel_F.FileName);//参数就参照上面的代码吧！

MyExcel_C.Visible = true;//这里不能用show()

if (MyExcel_C.DaoChuExcel())
DevExpress.XtraEditors.XtraMessageBox.Show("成功导出Excel文档！", "提示");

MyExcel_C.Close();//在进度条窗体上，我是没有做关闭窗体的，只是隐藏了，所以记得这里要关闭释放！
MyExcel_C.Dispose();

好啦！这样一个导出Excel就可以了！还是那句话，这里的设计不是很好！看看还有那位有更好的显示方法！