none
How to copy blank cell (entire column) in Excel using Range in C# RRS feed

  • Question

  • I have an excel file as hown below. Where any cell can be empty at given point of time, except Column A(cell values)

    Office

    A | B | C | D

    1 | a |  | 12

    1 | b |  | 22

    2 | c |  | 21

    2 | d |  | 44

     The tool I am working on splits this excel dependign on the Column A value, for istance:

    Office

    A | B | C | D

    1 | a | 12 |

    1 | b | 22 |

    Office

    A | B | C | D

    2 | c | 21 |

    2 | d |44 |

    The problem is Colum C has no value in the main file., which in that case should have been empty on the new files. However, the split function ignores the empty/blank cell and over write with the nex cell's value.

    Expected :Column A, B and D should have value. Column C should be empty in this case.

    Office

    A | B | C | D

    2 | c |     | 21

    2 | d |     | 44


    How can I do that?

    Here is my 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;
            }
    
    
     private void WriteToExcel(String fileName, List<RowEntity> headerRowEntities, List<RowEntity> dataRowEntities)
            {
                object misValue = System.Reflection.Missing.Value;
    
                Excel.Application xlApplication = new Excel.ApplicationClass();
                Excel.Workbook xlWorkBook = xlApplication.Workbooks.Add(misValue);
                Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                int headerRowTotal = 0;
    
                for (int i = 0; i < headerRowEntities.Count; i++)
                {
                    for (int j = 0; j < headerRowEntities[i].ColumnValues.Count; j++)
                    {
                        xlWorkSheet.Cells[i + 1, j + 1] = headerRowEntities[i].ColumnValues[j].ToString();
                    }
                    headerRowTotal++;
                }
    
                for (int i = 0; i < dataRowEntities.Count; i++)
                {
                    for (int j = 0; j < dataRowEntities[i].ColumnValues.Count; j++)
                    {
                        xlWorkSheet.Cells[headerRowTotal + i + 1, j + 1] = dataRowEntities[i].ColumnValues[j].ToString();
                    }
                }
    
                xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    
                xlWorkBook.Close(true, misValue, misValue);
                xlApplication.Quit();
    
                ReleaseObject(xlWorkSheet);
                ReleaseObject(xlWorkBook);
                ReleaseObject(xlApplication);
            }



       



    • Edited by sharlene108 Monday, January 12, 2015 4:21 PM
    Monday, January 12, 2015 4:21 PM

Answers

  • Solved:

    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());
                        }
                        else
                            rowEntity.ColumnValues.Add(""); // just add this line
                    }

    • Marked as answer by sharlene108 Tuesday, January 13, 2015 10:07 AM
    Tuesday, January 13, 2015 9:47 AM

All replies

  • Hi Sharlene,

    According to the description, you want to split the data based on the column A and you want to keep the empty cells.

    However, based on the code it didn't add the value to the collection. Since there is custom classes in the code, I am not able to test it, you can try the code below to see whether it works:

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

    Also you can consider to loop the source data and get the previous and current row index like figure below then copy the data directly use Range.Copy to the target workbook.

    And here is the code wiring in VBA for your reference:

    Sub splitData()
    Dim previousRowIndex As Integer
    Dim currentRowIndex As Integer
    Dim sourceSheet As Worksheet
    Dim copySheet As Worksheet
    previousRowIndex = 1
    Set sourceSheet = Application.ActiveSheet
    For i = 1 To sourceSheet.UsedRange.Rows.Count
         If sourceSheet.Cells(i, 1).Value <> sourceSheet.Cells(previousRowIndex, 1).Value Then
            currentRowIndex = i
            sourceSheet.Range(sourceSheet.Cells(previousRowIndex, 1), sourceSheet.Cells(currentRowIndex - 1, 4)).Copy
            Set copySheet = ActiveWorkbook.Sheets.Add
            copySheet.Paste
            previousRowIndex = currentRowIndex
         End If
         If i = sourceSheet.UsedRange.Rows.Count Then
            'copy last section of data
            sourceSheet.Range(sourceSheet.Cells(previousRowIndex, 1), sourceSheet.Cells(i, 4)).Copy
            Set copySheet = ActiveWorkbook.Sheets.Add
            copySheet.Paste
         End If
    Next i
    End Sub
    Regards & Fei

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Tuesday, January 13, 2015 7:23 AM
    Moderator
  • I know the problem is in the following lines of code:

    if ((range.Cells[rowCount, columnCount] as Excel.Range).Value != null)
    {
         rowEntity.ColumnValues.Add((range.Cells[rowCount, columnCount] as Excel.Range).Value.ToString());
    }

    Where the cells are empty the Value returns COM Obj = null. Since I am omitting all the nulls I would have to change them to empty string perhaps to keep the empty cell as empty or write empty string for blank cells in the new Excel files. However, since I have custom object (rowEntity), question would be how to have the empty string in the rowEntity object in my private FileEntity GetFileObject(Excel.Range range) method.

    I am afraid I didn't understand your approach.

    Tuesday, January 13, 2015 8:49 AM
  • Solved:

    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());
                        }
                        else
                            rowEntity.ColumnValues.Add(""); // just add this line
                    }

    • Marked as answer by sharlene108 Tuesday, January 13, 2015 10:07 AM
    Tuesday, January 13, 2015 9:47 AM