我怎样才能得到实际的使用范围，使用Epplus修改过人之处？ [英] How can i get actual used range for modified excels using Epplus?

问题描述

我从Excel中读取数据使用EPPlus来确定时代。

I am reading data from excel to datable using EPPlus.

10行记录读取Excel表后，我通过删除现有数据修改的Excel工作表和保持数据为只有一行。
但是，当我读了修改的Excel仍看10行（1值，其余为空字段），以数据表。

After reading an excel sheet with 10 rows of record, I modified the excel sheet by removing existing data and kept data for only one row. But when I am reading the modified excel it still reading 10 rows (1 with value and remaining as null fields) to data table.

如何限制这？我使用Excel的阅读下面的代码

How can limit this? I am using following code for reading Excel.

using (var pck = new OfficeOpenXml.ExcelPackage())
{
    using (var stream = File.OpenRead(FilePath))
    {
        pck.Load(stream);
    }
    var ws = pck.Workbook.Worksheets.First();                   
    bool hasHeader = true; // adjust it accordingly(this is a simple approach)
    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
    {
        DSClientTransmittal.Tables[0].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 wsRow = ws.Cells[rowNum, 1, rowNum, DSClientTransmittal.Tables[0].Columns.Count];
        var row = DSClientTransmittal.Tables[0].NewRow();
        foreach (var cell in wsRow)
        {
            try
            {
                object cellValue = cell.Value;
                //row[cell.Start.Column - 1] = cell.Text;
                row[cell.Start.Column - 1] = cellValue.ToString().Trim();
                //cell.Style.Numberformat.Format = "@";
                //row[cell.Start.Column - 1] = cell.Text;
            }
            catch (Exception ex) { }
        }
        DSClientTransmittal.Tables[0].Rows.Add(row);
    }
    pck.Dispose();
}   

当我使用互操作的Excel来读取Excel，同样的问题是由$克服b $ b clearformat（）法像

When I was using Interop excel to read excel, same issue was overcame by clearformat() method like

ws.Columns.ClearFormats();
xlColCount = ws.UsedRange.Columns.Count;

有没有Epplus的Open XML任何同等此？
我怎样才能得到实际使用的范围修改过人之处？

Is there any equivalent for this in Epplus open xml? How can I get actual used range for modified excels?

推荐答案

有是表示没有内置的方式行不应该被占时，只在某些细胞中删除数据。

There is no built-in way of indicating that a row shouldn't be accounted for when only deleting data in some cells.

尺寸是接近你可以搞定，但行包含在尺寸如果的任何的列中包含的数据，或者如果的任何的行的上方或下方包含数据。

Dimension is as close as you can get, but rows are included in the Dimension if any column contains data or if any row above or below contains data.

不过你可以试试，看看是否应该在跳过一排循环。
为例，如果你总是在第4列删除数据而已，那么你可以尝试：

You could however try to find out if you should skip a row in the for loop. For example if you always delete data in the first 4 columns only, then you could try:

if(!ws.Cells[rowNum, 1, rowNum, 4].All(c => c.Value == null))
{
    //Continue adding the row to the table
}

的描述不指示跳过一排标准，但你的想法。

The description isn't indicating the criteria for skipping a row, but you get the idea.

这篇关于我怎样才能得到实际的使用范围，使用Epplus修改过人之处？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！