如果日期在EPPlus中更改其月份，我如何创建新工作表并将其保存在那里

I have a column named datein which has dates in it. The question is how can i create a new worksheet and save it there whenever the date changes its month. A new month new worksheet.

我有一个名为datein的列，其中包含日期。问题是如何创建新工作表并在日期更改月份时将其保存在那里。新月新工作表。

all I have right now is my code for saving into excel using epplus.

我现在所拥有的是使用epplus保存到excel的代码。

SaveFileDialog saveFileDialog1 = new SaveFileDialog();

        using (MySqlConnection con = new MySqlConnection(connectionString))
        {
            using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM statusrouted.routed", con))
            {
                cmd.CommandType = CommandType.Text;
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {

                        using (ExcelPackage pck = new ExcelPackage())
                        {
                            sda.Fill(dt);

                            ExcelWorksheet ws = pck.Workbook.Worksheets.Add(DateTime.Today.ToString("MMMM-yyyy"));

                            ws.Cells.Style.Font.Size = 11;
                            ws.Cells["B1:K1"].Merge = true;
                            ws.Cells["B1"].Value = "INCOMING AND OUTGOING ROUTED COMMUNICATIONS";
                            ws.Cells["B1"].Style.Font.Size = 12;
                            ws.Cells["B1"].Style.Font.Bold = true;
                            ws.Cells["B1"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                            ws.Cells["B1"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                            ws.Cells["A2"].LoadFromDataTable((this.maindgv.DataSource as DataTable).DefaultView.ToTable(), true);


                            ws.DeleteColumn(1);

                            saveFileDialog1.Title = "Save as Excel";
                            saveFileDialog1.FileName = "";

                            saveFileDialog1.Filter = "Excel files(2007)|*.xlsx";

                            if (saveFileDialog1.ShowDialog() != DialogResult.Cancel)
                            {
                                try
                                {
                                    pck.SaveAs(new FileInfo(@""+ saveFileDialog1.FileName));
                                    recentsToolStripMenuItem1.AddRecentItem(@"" + saveFileDialog1.FileName);
                                }
                                catch (Exception)
                                {
                                    DialogResult reminder = MessageBox.Show("Cannot save file, file opened in another program.\nClose it first! ", "Save Failed", MessageBoxButtons.OK);
                                }
                            }
                        }
                    }
                }
            }
        }

1 个解决方案

#1

How the logic will work is as given below

逻辑如何工作如下所示

Follow these steps

按着这些次序

var dateGroups  = dt.AsEnumerable().GroupBy(row => row["ColumnName of Date Time"]);
foreach (var group in dateGroups)
{
 // the group.Key will give the Date on the basis of which you can create sheets. 
// and then your logic of excel
  foreach (var rows in group)
  {
   // excel filling logic
  }
}

#1