如何匹配日期到行，然后使用EPPlus获取最终的列值？ [英] How to match date to row then get the final column value using EPPlus?

问题描述

到目前为止，我可以从电子表格中轻松获得数据抓取，只需获取引号号码，但是我现在不知道如何将该行与数据部分匹配，然后才能获取正确的数据。

So far I can do an easy data grab from the spreadsheet, just getting the ref number row but I currently don't know how to match the row to the data section before getting the correct data out.

我目前必须从以下excel电子表格示例中提取一些数据：

I currently have to extract some data from the excel spreadsheet example below:

Start date  Ref number
29/07/2015  2342326
01/07/2016  5697455
02/08/2016  3453787
02/08/2016  5345355
02/08/2015  8364456
03/08/2016  1479789
04/07/2015  9334578

主要问题是可以从设定的日期读取数据，从行中获取参考号，例如：开始日期。

The main question is would it be possible to read in the data from a set date, from the row and get the ref number form the set date e.g. Start date.

例如，如果我只是希望将数据从上个月的上一个月的上升到上一个。

For example if i just wanted the data from the date set to the 1st of last month and upwards.

如何最好地实现。

使用基本的OleDb获取列的当前代码示例：

Example of current code used to get the column, using basic OleDb:

using System;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace Number_Cleaner
{
    public class NumberCleanerReport
    {
        public void runExcel_Report()
        {
            Console.ForegroundColor = ConsoleColor.Green;
            Console.WriteLine("[*][START OF: NumberExt.xls, Number Extraction]");
            Console.ResetColor();
            string con =
            @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NumberExt.xls;" +
            @"Extended Properties='Excel 8.0;HDR=Yes;'";

        string connectionString = ExcelWriter.GetConnectionString();

        using (OleDbConnection conn = new OleDbConnection(connectionString))
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = conn;

            using (OleDbConnection connection = new OleDbConnection(con))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                System.IO.StreamWriter files = new System.IO.StreamWriter(Controller.fpath + "NumberExtOutput.txt");
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        var row1Col0 = dr[0];
                        string ExcelData = row1Col0.ToString();
                        string subStr = "null";

                        try
                        {
                            subStr = ExcelData.Substring(0, 6);
                        }
                        catch
                        {
                            //Console.WriteLine("Found Nulls.");
                        }

                        if (subStr == "00")
                        {
                            string result = Regex.Replace(ExcelData, "^00", "0");
                            Console.WriteLine(result);
                            files.WriteLine(result);
                            cmd.CommandText = "INSERT INTO [table1]('MainNmbers') VALUES(" + result + ");";
                            cmd.ExecuteNonQuery();
                        }
                    }
                    files.Close();
                    conn.Close();
                    Console.ForegroundColor = ConsoleColor.Green;
                    Console.WriteLine("[*][END OF: NumberExt.xls, RefNumber Extraction]");
                    Console.ResetColor();
                }
            }
        }
    }
}
}

推荐答案

您可以将其加载到 DataTable 中，然后使用 LINQ到数据表。 这是一种方法，它将全部作为字符串读取。您可以修改它，以便将第一列解析为 DateTime ，而将第二列解析为 int 与 DateTime.Parse 或 DateTime.ParseExact 和 int.Parse ：

You could load it into a DataTable and then filter it with Linq-To-DataTable. Here's a method that reads all as strings. You could modify it so that it parse the first column to DateTime and the second to int with DateTime.Parse or DateTime.ParseExact and int.Parse:

public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true)
{
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        using (var stream = File.OpenRead(path))
        {
            pck.Load(stream);
        }
        var ws = pck.Workbook.Worksheets.First();  
        DataTable tbl = new DataTable();
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        {
            tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
        }
        var startRow = hasHeader ? 2 : 1;
        for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            DataRow row = tbl.Rows.Add();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Text;
            }
        }
        return tbl;
    }
}

一旦你在表中有正确的类型查询很简单：

Once you have the correct types in the table the query is simple:

var rowsOfInterest = table.AsEnumerable()
    .Where(row => row.Field<DateTime>("Start date") >= new DateTime(2016, 7, 1))
    .ToList();

如果您需要它作为 DataTable

DataTable resultTable = table.Clone();  // empty table with correct columns
if(rowsOfInterest.Count > 0)
    resultTable = rowsOfInterest.CopyToDataTable();

这篇关于如何匹配日期到行，然后使用EPPlus获取最终的列值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！