C# EPPlus 阅读列标题

EPPlus Reading Column Headers

提问by fr3dr1k8009

Is there an easy way to tell EPPlus that a row is a header? Or should I create the headers by specifying a range using SelectedRange, remove it from the sheet and iterate the cells that remain?

有没有一种简单的方法可以告诉 EPPlus 一行是标题？或者我应该通过使用 SelectedRange 指定范围来创建标题，将其从工作表中删除并迭代剩余的单元格？

I ended up doing this:

我最终这样做了：

class Program
{
    static void Main(string[] args)
    {
        DirectoryInfo outputDir = new DirectoryInfo(@"C:\testdump\excelimports");
        FileInfo existingFile = new FileInfo(outputDir.FullName + @"\Stormers.xlsx");
        Dictionary<string, string> arrColumnNames = new Dictionary<string,string>() { { "First Name", "" }, { "Last Name", "" }, { "Email Address", "" } };
        using (ExcelPackage package = new ExcelPackage(existingFile))
        {
            ExcelWorksheet sheet = package.Workbook.Worksheets[1];
            var q = from cell in sheet.Cells
                    where arrColumnNames.ContainsKey(cell.Value.ToString())
                    select cell;

            foreach (var c in q)
            {
                arrColumnNames[c.Value.ToString()] = c.Address;
            }
            foreach (var ck in arrColumnNames)
            {
                Console.WriteLine("{0} - {1}", ck.Key, ck.Value);
            }

            var qValues = from r in sheet.Cells
                          where !arrColumnNames.ContainsValue(r.Address.ToString())
                          select r;

            foreach (var r in qValues)
            {
                Console.WriteLine("{0} - {1}", r.Address, r.Value);
            }
        }
    }
}

回答by Muhammad Mubashir

var pck = new OfficeOpenXml.ExcelPackage();
pck.Load(new System.IO.FileInfo(path).OpenRead());
var ws = pck.Workbook.Worksheets["Worksheet1"];
DataTable tbl = new DataTable();
var hasHeader = true;
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 (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++){
     var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
     var row = tbl.NewRow();
     foreach (var cell in wsRow){
           row[cell.Start.Column - 1] = cell.Text;
     }
     tbl.Rows.Add(row);
}

回答by Noah Heldman

I had a similar issue. Here's some code that may help:

我有一个类似的问题。下面是一些可能有帮助的代码：

using (var package = new ExcelPackage(fileStream))
{
    // Get the workbook in the file
    var workbook = package.Workbook;
    if (workbook != null && workbook.Worksheets.Any())
    {
        // Get the first worksheet
        var sheet = workbook.Worksheets.First();

        // Get header values
        var column1Header = sheet.Cells["A1"].GetValue<string>();
        var column2Header = sheet.Cells["B1"].GetValue<string>();

        // "A2:A" means "starting from A2 (1st col, 2nd row),
        // get me all populated cells in Column A" (yes, unusual range syntax)
        var firstColumnRows = sheet.Cells["A2:A"];

        // Loop through rows in the first column, get values based on offset
        foreach (var cell in firstColumnRows)
        {
            var column1CellValue = cell.GetValue<string>();
            var column2CellValue = cell.Offset(0, 1).GetValue<string>();
        }
    }
}

If anyone knows of a more elegant way than cell.Offset, let me know.

如果有人知道比 更优雅的方式cell.Offset，请告诉我。

回答by ndd

I needed to enumerate through header and display all the columns headers to my end user. I took Muhammad Mubashircode as base and changed/converted it to extension method and removed hard-coded numbers from it.

我需要通过标题枚举并向我的最终用户显示所有列标题。我将Muhammad Mubashir代码作为基础并将其更改/转换为扩展方法并从中删除硬编码数字。

public static class ExcelWorksheetExtension
{
    public static string[] GetHeaderColumns(this ExcelWorksheet sheet)
    {
        List<string> columnNames = new List<string>();
        foreach (var firstRowCell in sheet.Cells[sheet.Dimension.Start.Row, sheet.Dimension.Start.Column, 1, sheet.Dimension.End.Column]) 
            columnNames.Add(firstRowCell.Text);
        return columnNames.ToArray();
    }
}

回答by J.Hpour

I just took nddcode and convert it with using of System Linq.

我只是使用了ndd代码并使用 System Linq 进行了转换。

using System.Linq;
using OfficeOpenXml;

namespace Project.Extensions.Excel
{
    public class ExcelWorksheetExtension
    {
        /// <summary>
        ///     Get Header row with EPPlus. 
        ///     <a href="https://stackoverflow.com/questions/10278101/epplus-reading-column-headers">
        ///         EPPlus Reading Column Headers
        ///     </a>
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns>Array of headers</returns>
        public static string[] GetHeaderColumns(this ExcelWorksheet sheet)
        {
            return sheet.Cells[sheet.Dimension.Start.Row, sheet.Dimension.Start.Column, 1, sheet.Dimension.End.Column]
                .Select(firstRowCell => firstRowCell.Text).ToArray();
        }
    }
}