Empty rows with OpenXml Excel

Question

Empty rows with OpenXml Excel

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I'm going through an excel with the library DocumentFormat.OpenXml , and when I get the rows, I get rows in which the cells are empty; that is, I have an excel where you only have information in the first 10 rows, but you are extracting information from the first 140 rows.

using (SpreadsheetDocument spreadDoc = SpreadsheetDocument.Open(stream, true))
 {
        IEnumerable<Sheet> sheets = spreadDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
        string relationshipId = sheets.First().Id.Value;
        WorksheetPart worksheetPart = (WorksheetPart)spreadDoc.WorkbookPart.GetPartById(relationshipId);
        Worksheet workSheet = worksheetPart.Worksheet;
        SheetData underscoreSheetData = workSheet.GetFirstChild<SheetData>();
        IEnumerable<Row> underscoreRows = underscoreSheetData.Elements<Row>();
        var numRows=underscoreRows.Count();
 }

How can I get only the rows in which there is information, in this case the first 10 rows.

Thanks

c# linq excel

asked by Jairo1010 05.02.2016 в 20:28

source

2 answers

Get the value of a dropdown with Bootstrap optgroup in ASP.NET Specify Bundler versions in the Gemfile

score 1 · Answer 1

You may have a foreach (Row r in sheetData.Elements<Row>()) missing.

Try this example and if you find it you adapt it to your code:

 using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
    string text;
    foreach (Row r in sheetData.Elements<Row>())
    {
        foreach (Cell c in r.Elements<Cell>())
        {
            text = c.CellValue.Text;
            Console.Write(text + " ");
        }
    }
    Console.WriteLine();
    Console.ReadKey();
}

I hope it serves you.

score 1 · Answer 2

You can try filtering them using Linq, this is an example of how you could do

 sheetData.Elements<Row>().Where(row=> 
           row.Elements<Cell>.Any(cell=>   
                !String.IsEmptyOrNull(cell.CellValue.Text)
                                  )).ToList();