使用EPPlus从excel获取所有单元格值 [英] Get all the cell values from excel using EPPlus

问题描述

我想使用EPPlus库中的ExcelWorksheet对象并检索所有具有值及其位置的单元格。

I want to use the ExcelWorksheet object from the EPPlus library and retrieve all the cells with values as well as their positions.

最后，我

Dictionary<KeyValuePair<int, int>, object> values.

我正在寻找一种干净的方法将单元格值提取到此字典中以供使用。

I am looking for a nice and clean way to extract the cell values into this dictionary for use.

我很清楚您可以按如下所示直接引用单元格值，但是有没有一种很好的干净方法来提取所有数据？

I am quite aware that you can reference the cell values directly as below, but is there a nice clean way of extracting all the data ?

worksheet.Cells[row, column];

推荐答案

EPPlus中没有本机函数可以将将单元格集合直接转换为另一个集合，但它确实实现了 IEnumerable ，因此您可以使用工作表上的 Linq 轻松地完成此操作。这很好，因为 cells 仅包含包含值的单元格引用，因此您甚至不需要指定行/列限制：

There is no native function in EPPlus to convert a the cells collection to a another collection directly but it does implement IEnumerable so you can do it fairly easily with Linq on the worksheet. This works out nicely because cellsonly contains cell references that contain values so you do not even need to specify a row/column limit:

[TestMethod]
public void Cells_To_Dictionary_Test()
{
    //http://stackoverflow.com/questions/32066500/get-all-the-cell-values-from-excel-using-epplus

    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[] { new DataColumn("Col1", typeof(int)), new DataColumn("Col2", typeof(int)), new DataColumn("Col3", typeof(object)) });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = i; 
        row[1] = i * 10;
        row[2] = Path.GetRandomFileName();
        datatable.Rows.Add(row);
    }

    //Create a test file
    var existingFile = new FileInfo(@"c:\temp\Grouped.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var pck = new ExcelPackage(existingFile))
    {
        var worksheet = pck.Workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromDataTable(datatable, true);
        pck.Save();
    }

    //Load a dictionary from a file
    using (var pck = new ExcelPackage(existingFile))
    {
        var worksheet = pck.Workbook.Worksheets["Sheet1"];

        //Cells only contains references to cells with actual data
        var cells = worksheet.Cells;
        var dictionary = cells
            .GroupBy(c => new {c.Start.Row, c.Start.Column})
            .ToDictionary(
                rcg => new KeyValuePair<int, int>(rcg.Key.Row, rcg.Key.Column),
                rcg => cells[rcg.Key.Row, rcg.Key.Column].Value);

        foreach (var kvp in dictionary)
            Console.WriteLine("{{ Row: {0}, Column: {1}, Value: \"{2}\" }}", kvp.Key.Key, kvp.Key.Value, kvp.Value);
    }
}

在输出中生成以下内容：

Which produces this in the output:

{ Row: 1, Column: 1, Value: "Col1" }
{ Row: 1, Column: 2, Value: "Col2" }
{ Row: 1, Column: 3, Value: "Col3" }
{ Row: 2, Column: 1, Value: "0" }
{ Row: 2, Column: 2, Value: "0" }
{ Row: 2, Column: 3, Value: "o103ujwu.hmq" }
{ Row: 3, Column: 1, Value: "1" }
{ Row: 3, Column: 2, Value: "10" }
{ Row: 3, Column: 3, Value: "awyajw2t.lwa" }
{ Row: 4, Column: 1, Value: "2" }
{ Row: 4, Column: 2, Value: "20" }
{ Row: 4, Column: 3, Value: "mjhlre2d.nh2" }
{ Row: 5, Column: 1, Value: "3" }
{ Row: 5, Column: 2, Value: "30" }
{ Row: 5, Column: 3, Value: "dypjvona.m3w" }
{ Row: 6, Column: 1, Value: "4" }
{ Row: 6, Column: 2, Value: "40" }
{ Row: 6, Column: 3, Value: "s5oushbr.sax" }
{ Row: 7, Column: 1, Value: "5" }
{ Row: 7, Column: 2, Value: "50" }
{ Row: 7, Column: 3, Value: "1vqjxxky.2gn" }
{ Row: 8, Column: 1, Value: "6" }
{ Row: 8, Column: 2, Value: "60" }
{ Row: 8, Column: 3, Value: "2dw00hi0.pjp" }
{ Row: 9, Column: 1, Value: "7" }
{ Row: 9, Column: 2, Value: "70" }
{ Row: 9, Column: 3, Value: "qgottyza.vnu" }
{ Row: 10, Column: 1, Value: "8" }
{ Row: 10, Column: 2, Value: "80" }
{ Row: 10, Column: 3, Value: "yx4tmvdp.2pq" }
{ Row: 11, Column: 1, Value: "9" }
{ Row: 11, Column: 2, Value: "90" }
{ Row: 11, Column: 3, Value: "jutk2r0v.1yx" }

这篇关于使用EPPlus从excel获取所有单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！