CaiQie/RfidWeb/Frm/FrmBarCodeGridView.cs

using ClosedXML.Excel;

using DB.Service;

using HZH_Controls.Controls;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

using Tool;

namespace RfidWeb.Frm
{
    public partial class FrmBarCodeGridView : UserControl
    {
        private ReadBufLogService readBufLogService = new ReadBufLogService();

        public FrmBarCodeGridView()
        {
            InitializeComponent();

            this.dataGridView1.Columns["CreateTime"].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss";

            var keyValue = new List<KeyValuePair<string, string>>();
            keyValue.Add(new KeyValuePair<string, string>("A", "收料1"));
            keyValue.Add(new KeyValuePair<string, string>("B", "收料2"));
            keyValue.Add(new KeyValuePair<string, string>("C", "废料"));
            ucHorizontalList1.DataSource = keyValue;
            var dt = DateTime.Now;

            var a = Convert.ToDateTime(dt.ToString("yyyy-MM-dd 00:00:00"));
            var b = Convert.ToDateTime(dt.ToString("yyyy-MM-dd 23:59:00"));

            this.ucDatePickerExt21.CurrentTime = a;
            this.ucDatePickerExt22.CurrentTime = b;

            this.dataGridView1.AutoGenerateColumns = false;

            ucPagerControl21.PageModel = PageModel.PageCount;
            ucPagerControl21.PageIndex = 1;
            ucPagerControl21.PageSize = 20;

            this.ucPagerControl21.ShowSourceChanged +=
                new HZH_Controls.Controls.PageControlEventHandler(this.ucPagerControl21_ShowSourceChanged);

            this.ucHorizontalList1.SelectedItemEvent +=
                new System.EventHandler(this.ucHorizontalList1_SelectedItemEvent);
            ucPagerControl21_ShowSourceChanged(new object());
        }

        private void Replace()
        {
            ucPagerControl21.PageIndex = 1;
            ucPagerControl21_ShowSourceChanged(new object());

        }

        private void ucPagerControl21_ShowSourceChanged(object currentSource)
        {
            this.dataGridView1.AutoGenerateColumns = false;
            var selectedItemDataSource = ucHorizontalList1.SelectedItem.DataSource;
            string key = selectedItemDataSource.Key;

            var beginTime = ucDatePickerExt21.CurrentTime;
            var endTime = ucDatePickerExt22.CurrentTime;

            int index = ucPagerControl21.PageIndex;
            var page = readBufLogService.GetPagedList(ucPagerControl21.PageIndex,
                ucPagerControl21.PageSize, key, beginTime, endTime);
            ucPagerControl21.PageCount = page.TotalPages;
            this.dataGridView1.DataSource = page.Items;
        }

        private void ucBtnSelect_BtnClick(object sender, EventArgs e)
        {
            Replace();
        }

        private void ucHorizontalList1_SelectedItemEvent(object sender, EventArgs e)
        {
            Replace();
        }

        private void ucBtnExt1_BtnClick(object sender, EventArgs e)
        {
            var selectedItemDataSource = ucHorizontalList1.SelectedItem.DataSource;
            string key = selectedItemDataSource.Key;
            string val = selectedItemDataSource.Value;
            var beginTime = ucDatePickerExt21.CurrentTime;
            var endTime = ucDatePickerExt22.CurrentTime;

            string directoryPath = "D:\\excel";
            if (!Directory.Exists(directoryPath))
            {
                Directory.CreateDirectory(directoryPath);
                Console.WriteLine("目录已创建。");
            }

            string suiji = Guid.NewGuid().ToString().Substring(0, 5);

            string file = directoryPath + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + suiji + "_" + val + ".xlsx";
            var dt = readBufLogService.GetDtaTable(key, beginTime, endTime);
            ExportDataTableToExcel(dt, file);

            MessageBox.Show("OK");
        }

        void ExportDataTableToExcel(DataTable table, string filePath)
        {
            using (var workbook = new XLWorkbook())
            {
                table.TableName = "Shell1";
                var worksheet = workbook.Worksheets.Add(table.TableName);

                // 写入列头
                for (int col = 0; col < table.Columns.Count; col++)
                {
                    worksheet.Cell(1, col + 1).Value = table.Columns[col].ColumnName;
                }

                // 写入行数据
                for (int row = 0; row < table.Rows.Count; row++)
                {
                    for (int col = 0; col < table.Columns.Count; col++)
                    {
                        worksheet.Cell(row + 2, col + 1).Value = table.Rows[row][col].ToString();
                    }
                }

               
                // 保存为文件
                workbook.SaveAs(filePath);
            }
        }
    }
}