Problem in reading Excel data with OPENXML

Problem in reading Excel cells with OPENXML:

If the users are not using the correct template excel with the data then we will not be able to read the excel cells data. Here the issue will be, if we are trying to retrieve the 3^rd cell value then it may through exception. Exception will throw because of cell not used.

The solution for this issue is, first we need to check whether particular is used or not. Then we need to try to read the data from that cell.

Normal syntax to read Excel Data:

using (MemoryStream mem = new MemoryStream())

            {

                mem.Write(byteArray, 0, (int)byteArray.Length);

                string Path = filePath;

                CultureInfo pt = CultureInfo.GetCultureInfo("fr-FR");

                dtMain.Locale = pt;

                using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(mem, true))

                {

                    WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;

                    Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();

                     //Where(s => s.Name == SheetName).FirstOrDefault();

                    if (theSheet == null)

                    {

                        ErrorMessage = "Invalid Sheet";

                        return ErrorMessage;

                    }

                    WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(theSheet.Id));

                    Worksheet workSheet = worksheetPart.Worksheet;

                    SheetData sheetData = workSheet.GetFirstChild<SheetData>();

                    IEnumerable<Row> rows = sheetData.Descendants<Row>();

Boolean IsEndofFIle = false;

foreach (Row row in rows) //this will also include your header row...        

{

  for (int i = 0; i < 10; i++)

   {

    string ColName= GetColumnName(row.Descendants<Cell>().ElementAt(i).CellReference);

      if (!ColName.Equals("A") && !ColName.Equals("B") && !ColName.Equals("C")&& !ColName.Equals("D")&& !ColName.Equals("E"))

      {

         IsEndofFIle = true;

         break;

      }

     

      if (ColName.Contains("E"))

      {

       IVal = i;

       IMaxVal = i + 6;

   ColName = GetColumnName(row.Descendants<Cell>().ElementAt(i+1).CellReference);

    if (!ColName.Equals("F"))

    {

     IsDataValid = false;

     ErrorMessage += "Sheet contain invalid data</br>";

     break;

    }

break;

}

}

Above Syntax will get the column names like A,B,C,D,E,F….. So based on the column name we can retrieve the particular cell data.

Above code don’t have complete logic. It just have the syntax to read column name.