CODEWITHSUNDEEP
c#excelexcel-interopcells
PineCone
PineCone

Reputation: 2343

Blank cell is ignored and overwritten in Excel.Range using Interop

have data in an excel file as shown below, where row 1 & 2 are considered as header row which will be copied when this particular file is split according to the first column.

enter image description here

After split the files look like as below:

enter image description here enter image description here

The Problem is that Column D's value is copied to the wrong column that is C in this case.

I presume I need to check if a cell is empty or null and put blank/null value to the new file during write. Question is how can incorporate that into my following code? 

    private FileEntity GetFileObject(Excel.Range range)
    {
        FileEntity fileEntity = new FileEntity();
        fileEntity.RowValues = new List<RowEntity>();

        for (int rowCount = 1; rowCount <= range.Rows.Count; rowCount++)
        {
            RowEntity rowEntity = new RowEntity();
            rowEntity.ColumnValues = new List<string>();

            for (int columnCount = 1; columnCount <= range.Columns.Count; columnCount++)
            {
                if ((range.Cells[rowCount, columnCount] as Excel.Range).Value != null)
                {
                    rowEntity.ColumnValues.Add((range.Cells[rowCount, columnCount] as Excel.Range).Value.ToString());
                }
            }
            fileEntity.RowValues.Add(rowEntity);
        }
        return fileEntity;
    }

Upvotes: 0

Views: 1012

Answers (2)

PineCone
PineCone

Reputation: 2343

Found the solution: 

                if ((range.Cells[rowCount, columnCount] as Excel.Range).Value != null)
                {
                    rowEntity.ColumnValues.Add((range.Cells[rowCount, columnCount] as Excel.Range).Value.ToString());
                }
                else
                    rowEntity.ColumnValues.Add(""); //just add this line. keeps the blank cell as created with empty string

Upvotes: 0

Crowcoder
Crowcoder

Reputation: 11514

That is because there are built-in efficiencies to the document structure that avoid wasting space. You have to inspect the cells for their reference (A1, B1, etc.) and then you can know where it came from and what to do with it.

      int rowIdx = 0;

      foreach (Row r in sheetData.Elements<Row>().Skip(1))
      {
        rowIdx++;
        IEnumerable<Cell> thisRow = r.Elements<Cell>();

        foreach (var c in thisRow)
        {
          //This will tell you what cell you are looking at (A2, D14, whatever..)
          string cellRef = c.CellReference.ToString().Substring(0, 1);

          switch (cellRef)
          {
            case "A": 
              //do something...
              break;
            case "B": 
              //do something...
              break;
            case "C": 
              //do something...
              break;
        ....ETC.......

Upvotes: 0

Related Questions