Winform数据导出Execl小工具

前台界面.cs文件

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using Unis.Realtime.Data;
using Unis.Realtime.Drivers;

namespace ExportRTData
{
    public partial class ExportData : Form
    {
        #region 声明

        /// <summary>
        /// 定义单线程文件
        /// </summary>
        BackgroundWorker worker = new BackgroundWorker();
        /// <summary>
        /// 定义终止操作
        /// </summary>
        private ManualResetEvent manualReset = new ManualResetEvent(true);
        /// <summary>
        /// 计算一共要执行的总次数
        /// </summary>
        public int Count = 0;
        /// <summary>
        /// 计算执行了多少
        /// </summary>
        public int ExcuteCount = 0;
        /// <summary>
        /// 错误
        /// </summary>
        private bool Error = true;
        /// <summary>
        /// 实时库连接
        /// </summary>
        private RealtimeClient rt = RealtimeClient.Create("RTDB");

        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        public ExportData()
        {
            InitializeComponent();
            worker.WorkerSupportsCancellation = true;
        }

        #endregion

        /// <summary>
        /// 页面加载事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void ExportData_Load(object sender, EventArgs e)
        {
            string strStartTime = ConfigurationManager.AppSettings["StartTime"];
            string strEndTime = ConfigurationManager.AppSettings["EndTime"];
            string strInterval = ConfigurationManager.AppSettings["Interval"];

            this.tbInterval.Text = strInterval;
            this.dtStartTime.Text = strStartTime;
            this.dtEndTime.Text = strEndTime;
        }

        /// <summary>
        /// 开始导出数据事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnStart_Click(object sender, EventArgs e)
        {
            if (DateTime.Parse(this.dtStartTime.Text) > DateTime.Parse(this.dtEndTime.Text))
            {
                MessageBox.Show("开始时间不能大于结束时间");
                return;
            }

            #region 保存配置信息

            string strStartTime = this.dtStartTime.Text;
            string strEndTime = this.dtEndTime.Text;
            string strInterval = this.tbInterval.Text;

            Configuration cf = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            cf.AppSettings.Settings["StartTime"].Value = strStartTime;
            cf.AppSettings.Settings["EndTime"].Value = strEndTime;
            cf.AppSettings.Settings["Interval"].Value = strInterval;
            cf.Save();
            ConfigurationManager.RefreshSection("appSettings");

            #endregion

            #region 控制控件

            this.dtStartTime.Enabled = false;
            this.dtEndTime.Enabled = false;
            this.btnStart.Enabled = false;
            this.btnStop.Enabled = true;
            this.tbInterval.ReadOnly = true;

            #endregion

            SetWorkerMethod();
            this.lbRate.Items.Clear();
            worker.RunWorkerAsync();
        }

        /// <summary>
        /// 设定线程事件
        /// </summary>
        protected void SetWorkerMethod()
        {
            worker = new BackgroundWorker();
            worker.WorkerReportsProgress = true;
            worker.WorkerSupportsCancellation = true;
            worker.DoWork += new DoWorkEventHandler(worker_DoWork);
            worker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(worker_RunWorkerCompleted);
            worker.ProgressChanged += new ProgressChangedEventHandler(worker_ProgressChanged);
        }

        /// <summary>
        /// 线程工作事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void worker_DoWork(object sender, DoWorkEventArgs e)
        {
            workExportData(worker);
        }

        /// <summary>
        /// 历史数据导出
        /// </summary>
        /// <param name="bk"></param>
        private void workExportData(BackgroundWorker bk)
        {
            try
            {
                // 连接字符串            
                string xlsPath = Application.StartupPath + "\\FanStencil\\导数据清单.xlsx";
                string strCon = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + xlsPath + ";Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'"; 
                //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
                //备注： "HDR=yes;"是说Excel文件的第一行是列名而不是数据，"HDR=No;"正好与前面的相反。
                //      "IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。 

                OleDbConnection conn = new OleDbConnection(strCon);
                conn.Open();
                DataTable dtFanPoint = new DataTable();
                string strExcel = "select * from [Sheet1$]";
                OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strCon);
                myCommand.Fill(dtFanPoint);

                Count = dtFanPoint.Rows.Count;
                bk.ReportProgress(ExcuteCount, "读到的风总数为：" + dtFanPoint.Rows.Count.ToString() + ".");

                int columnCount = dtFanPoint.Columns.Count;
                for (int i = 0; i < dtFanPoint.Rows.Count; i++)
                {
                    //判断是否取消线程执行
                    while (bk.CancellationPending)
                    {
                        System.Threading.Thread.Sleep(2000);
                        //将信息显示到前台UI
                        bk.ReportProgress(0, "导出已经停止，停止时间为：" + DateTime.Now.ToString("HH:mm:ss") + "，若要再导出请点击开始！");
                        return;
                    }

                    bk.ReportProgress(ExcuteCount, "风数据：" + dtFanPoint.Rows[i][1].ToString());

                    DataRow drFanPoint = dtFanPoint.Rows[i];
                    DataTable dtValueData = new DataTable();

                    dtValueData = dtFanPoint.Copy();
                    dtValueData.Clear();
                    dtValueData.Rows.Add(dtFanPoint.Rows[i].ItemArray);  //添加数据行
                    dtValueData.Columns.RemoveAt(0);
                    dtValueData.Columns[0].ColumnName = "时间";

                    for (int j = 2; j < columnCount; j++)
                    {
                        string strPoint = dtFanPoint.Rows[i][j].ToString();
                        PointModel pointModel = new PointModel() { Name = strPoint };
                        List<PointData> listPointData = rt.Hist(pointModel, DateTime.Parse(this.dtStartTime.Text), DateTime.Parse(this.dtEndTime.Text), TimeSpan.Parse("00:" + this.tbInterval.Text.Trim() + ":00"));

                        for (int k = 0; k < listPointData.Count; k++)
                        {
                            if (j == 2)
                            {
                                dtValueData.Rows.Add(new object[] { listPointData[k].Time });
                            }
                            dtValueData.Rows[k + 1][j - 1] = listPointData[k].Value;
                        }
                    }

                    string filePath = Application.StartupPath + "\\HistoryData\\";
                    ExeclHelper.ExportDataToExcel(dtValueData, filePath, drFanPoint[0].ToString());

                    ExcuteCount += 1;
                    bk.ReportProgress(ExcuteCount, "风数据已导出完毕：" + dtFanPoint.Rows[i][1].ToString() + ".");
                }
                bk.ReportProgress(ExcuteCount, "数据已全部成功导出!");
            }
            catch (Exception ex)
            {
                Error = false;
                MessageBox.Show("导出异常：" + ex.Message);
                bk.ReportProgress(ExcuteCount, "异常:" + ex.Message + ",");

            }
        }

        private void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (Error == true)
                MessageBox.Show("数据成功", "提示");
            else
                MessageBox.Show("数据导出异常，见文本提示", "提示");
        }

        /// <summary>
        /// 报告异步操作的进度
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            string strRan = e.UserState.ToString();

            if (strRan.LastOrDefault() == '.')
            {
                //走滚动条添加进度
                this.pgbExport.Maximum = Count;
                this.pgbExport.Value = e.ProgressPercentage;
                this.lbRate.Items.Add(e.UserState.ToString());
            }
            else if (strRan.LastOrDefault() == ',')
            {
                Error = true;
                //添加错误状态
                //errorlist.Add(e.UserState.ToString());
                this.lbRate.Items.Add(e.UserState.ToString());
                //errorInformation(e.UserState.ToString(), false);
            }
            else if (strRan.LastOrDefault() == '!')
            {
                //数据处理完毕
                this.dtStartTime.Enabled = true;
                this.dtEndTime.Enabled = true;
                this.btnStart.Enabled = true;
                this.btnStop.Enabled = false;
                this.tbInterval.ReadOnly = false;
            }
            else
            {
                this.lbRate.Items.Add(e.UserState.ToString() + "正在处理！");
            }
        }

        /// <summary>
        /// 停止导出数据事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnStop_Click(object sender, EventArgs e)
        {
            if (worker.IsBusy)
            {
                worker.ReportProgress(1, "数据导出已经停止.");
                worker.CancelAsync();
            }

            this.dtStartTime.Enabled = true;
            this.dtEndTime.Enabled = true;
            this.btnStart.Enabled = true;
            this.btnStop.Enabled = false;
            this.tbInterval.ReadOnly = false;
        }

    }
}

 

ExeclHelper.cs，将DataTable输出到Execl中

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExportRTData
{
    public class ExeclHelper
    {
        /// <summary>
        /// 将DataTable的数据导出到Excel中。
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="xlsFileDir">导出的Excel文件存放目录</param>
        /// <param name="strTitle">Excel表的标题</param>
        /// <returns>Excel文件名</returns>
        public static string ExportDataToExcel(System.Data.DataTable dt, string xlsFileDir, string strTitle)
        {
            if (dt == null) return "";
            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks workBooks = excel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workBook = workBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            //Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[1];
            int titleRowsCount = 0;

            int rowCount = dt.Rows.Count + 2;
            int colCount = dt.Columns.Count;
            object[,] dataArray = new object[rowCount, colCount];

            if (strTitle != null && strTitle.Trim() != "")
            {
                titleRowsCount = 1;
                excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Bold = true;
                excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.Size = 16;
                excel.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).MergeCells = true;
                dataArray[0, 0] = strTitle;
            }

            if (!System.IO.Directory.Exists(xlsFileDir))
            {
                System.IO.Directory.CreateDirectory(xlsFileDir);
            }

            strTitle = strTitle.Replace(":", "：");
            string strFileName = strTitle + ".xlsx";
            string tempColumnName = "";

            for (int i = 0; i < rowCount - 2; i++)
            {
                for (int j = 0; j < colCount; j++)
                {
                    if (i == 0)
                    {
                        tempColumnName = dt.Columns[j].ColumnName.Trim();
                        dataArray[titleRowsCount, j] = tempColumnName;
                    }
                    dataArray[i + titleRowsCount + 1, j] = dt.Rows[i][j];
                }
            }
            excel.get_Range("A1", excel.Cells[rowCount, colCount]).Value2 = dataArray;

            excel.get_Range(excel.Cells[titleRowsCount + 1, 1], excel.Cells[titleRowsCount + 1, dt.Columns.Count]).Font.Bold = true;
            excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
            excel.get_Range(excel.Cells[1, 1], excel.Cells[titleRowsCount + 1 + dt.Rows.Count, dt.Columns.Count]).EntireColumn.AutoFit();

            workBook.Saved = true;
            workBook.SaveCopyAs(xlsFileDir + strFileName);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
            //workSheet = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
            workBook = null;
            workBooks.Close();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workBooks);
            workBooks = null;
            excel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;
            return strFileName;
        }
    }
}