parkMonitor_V2.0/PLCLinker/centralController/FormRecordsManager.cs

using DevComponents.DotNetBar.Controls;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace centralController
{
    public partial class FormRecordsManager : Form
    {
        public FormRecordsManager()
        {
            InitializeComponent();
        }

        private void btnx_parkingRecordSearch_Click(object sender, EventArgs e)
        {
            DateTime start, end;
            string license="";
            try
            {
                start = DateTime.Parse(dtp_startDate.Text);
                end = DateTime.Parse(dtp_endDate.Text);
                license = tb_license.Text;
            }
            catch { MessageBox.Show("日期输入异常，请重新输入");return; }

            dgvx_parkingRecords.Rows.Clear();
            List<object[]> list = Monitor.Monitor.GetParkingRecords(license,start.ToString("yyyy-MM-dd"), end.AddDays(1).ToString("yyyy-MM-dd"));
            List<object[]>.Enumerator listEnumer = list.GetEnumerator();
            while (listEnumer.MoveNext())
            {
                dgvx_parkingRecords.Rows.Add(listEnumer.Current);
            }
            //Console.WriteLine(mtb_startDate.Text+","+mtb_endDate.Text);
            //Console.WriteLine(start.ToString("yyyy-MM-dd") + "," + end.ToString("yyyy-MM-dd"));
        }

        private void btnx_parkingRecrodExport_Click(object sender, EventArgs e)
        {
            SaveRecord();
        }

        /// <summary>
        /// 保存记录为xls
        /// </summary>
        private void SaveRecord()
        {
            FolderBrowserDialog dialog = new FolderBrowserDialog();
            dialog.Description = "请选择日志将要导出的目标文件夹";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                if (string.IsNullOrEmpty(dialog.SelectedPath))
                {
                    DialogResult answer = MessageBox.Show("文件夹路径为空\n使用默认位置" + Directory.GetCurrentDirectory() + "?", "提示", MessageBoxButtons.OKCancel);
                    if (answer.Equals(DialogResult.OK))
                    {
                        Export(dgvx_parkingRecords, Directory.GetCurrentDirectory() + "\\", stc_recordManager.SelectedTab.Text);
                    }
                    else
                    {
                        return;
                    }
                }
                else
                {
                    DialogResult answer = MessageBox.Show("将日志保存到" + dialog.SelectedPath + "?", "提示", MessageBoxButtons.OKCancel);
                    if (answer.Equals(DialogResult.OK))
                    {
                        Export(dgvx_parkingRecords, dialog.SelectedPath + "\\", stc_recordManager.SelectedTab.Text);
                    }
                    else
                    {
                        return;
                    }
                }
            }
        }

        /// <summary>
        /// 导出excel文件
        /// </summary>
        /// <param name="dataGridView1"></param>
        /// <param name="path">绝对路径，"D:\\abc\\xxx.xls"</param>
        private void Export(DataGridViewX dataGridView1, string path,string name)
        {
            try
            {
                if (dataGridView1.Rows.Count == 0)
                {
                    return;
                }
                Excel.Application excel = new Excel.Application();
                excel.Visible = false;
                Excel.Workbook workbook = excel.Workbooks.Add(true);
                Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    if (dataGridView1.Columns[i].Visible == true)
                    {
                        worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                    }
                }
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    System.Windows.Forms.Application.DoEvents();
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        if (dataGridView1.Columns[j].Visible == true)
                        {
                            if (dataGridView1[j, i].ValueType == typeof(string))
                            {
                                worksheet.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
                            }
                            else
                            {
                                worksheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
                            }
                        }
                    }
                }
                //设置禁止弹出保存和覆盖的询问提示框  
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;
                worksheet.Cells.Columns.AutoFit();
                //保存写入的数据，这里还没有保存到磁盘
                workbook.Saved = true;
                if (Directory.Exists(path))
                {
                    //设置新建文件路径及名称
                    string savePath = path + DateTime.Now.ToString("yyyy-MM-dd") + name +".xls";

                    //创建文件
                    FileStream file = new FileStream(savePath, FileMode.CreateNew);

                    //关闭释放流，不然没办法写入数据
                    file.Close();
                    file.Dispose();

                    //保存到指定的路径
                    workbook.SaveCopyAs(savePath);
                }

                workbook.Close(false, Type.Missing, Type.Missing);

                //确保Excel进程关闭  
                excel.Quit();
                //释放 COM 对象
                Marshal.ReleaseComObject(worksheet);
                Marshal.ReleaseComObject(workbook);
                Marshal.ReleaseComObject(excel);
                excel = null;
                worksheet = null;
                workbook = null;
                GC.Collect();//如果不使用这条语,excel会无法正常退出
            }
            catch (Exception e) { Console.WriteLine("导出excel文件异常，" + e.Message); }

        }
    }
}