none
C#.net program to find empty cell in excel sheet

    Question

  • Hi all,

    I am working on a project in which i want to find a empty cell in a range say (A1 to A10). So that i can insert a new data in the empty cell. I want my C#.net form Application to do this.

    Thanks,

    Jimmit


    Jimmit Raval


    • Edited by Jimmit_05 Wednesday, April 11, 2012 7:18 AM
    Wednesday, April 11, 2012 7:16 AM

Answers

  • Hi 

    I am adding few comments with the full code, hope it helps, please let me know if u need furthur explanation

    // Few declarations we would need


    private object misValue = System.Reflection.Missing.Value;
    private Excel.Application m_xlApp ;
    private Excel.Workbook m_xlWorkBook ;
    private Excel.Worksheet m_xlWorkSheet ;

    private void CheckandReplace()

    {


    // Create instance of workbook and worksheet
    m_xlWorkBook = m_xlApp.Workbooks.Open(m_CompleteFilePath,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue);

    m_xlWorkSheet = (Excel.Worksheet)m_xlWorkBook.Worksheets.get_Item(1);

    // Lets read the cells now
    for(int i=1;i<=10;i++)
    {


    // Read Each Cell and Compare
    if(ReadCellValue(m_xlWorkSheet,"A"+i.ToString())==string.empty)
    {

    // Cell Empty, Do your processing here.
    }


    }


    }

    // Function to Read the cell value
    private string ReadCellValue(Excel.Worksheet XlsWorkSheet,string rangeAddress)
    {


    object Range = XlsWorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
    null, XlsWorkSheet, new object[] { rangeAddress });
    return Range.GetType().InvokeMember("Value", BindingFlags.GetProperty,
    null, Range, null).ToString();

    }


    Anu Viswan : www.AnuViswan.blogspot.com

    Wednesday, April 11, 2012 8:24 AM

All replies

  • Hi 

    You could find the read the contends of Cell as follows and then compare to check if its empty.

    object Range = XlsWorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
    null, XlsWorkSheet, new object[] { rangeAddress });
    return Range.GetType().InvokeMember("Value", BindingFlags.GetProperty,
    null, Range, null).ToString();

    Thanks

    Anu


    Anu Viswan : www.AnuViswan.blogspot.com

    Wednesday, April 11, 2012 8:08 AM
  • Hi,

    Thaks for solution.

    I am a newbie here, would you give some example to explain it.

    Thanks,

    Jimmit


    Jimmit Raval

    Wednesday, April 11, 2012 8:12 AM
  • Hi 

    I am adding few comments with the full code, hope it helps, please let me know if u need furthur explanation

    // Few declarations we would need


    private object misValue = System.Reflection.Missing.Value;
    private Excel.Application m_xlApp ;
    private Excel.Workbook m_xlWorkBook ;
    private Excel.Worksheet m_xlWorkSheet ;

    private void CheckandReplace()

    {


    // Create instance of workbook and worksheet
    m_xlWorkBook = m_xlApp.Workbooks.Open(m_CompleteFilePath,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue,misValue);

    m_xlWorkSheet = (Excel.Worksheet)m_xlWorkBook.Worksheets.get_Item(1);

    // Lets read the cells now
    for(int i=1;i<=10;i++)
    {


    // Read Each Cell and Compare
    if(ReadCellValue(m_xlWorkSheet,"A"+i.ToString())==string.empty)
    {

    // Cell Empty, Do your processing here.
    }


    }


    }

    // Function to Read the cell value
    private string ReadCellValue(Excel.Worksheet XlsWorkSheet,string rangeAddress)
    {


    object Range = XlsWorkSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty,
    null, XlsWorkSheet, new object[] { rangeAddress });
    return Range.GetType().InvokeMember("Value", BindingFlags.GetProperty,
    null, Range, null).ToString();

    }


    Anu Viswan : www.AnuViswan.blogspot.com

    Wednesday, April 11, 2012 8:24 AM
  • In addition, there is a Excel for developpers forums for your reference. You can post it there to get more responses, where experts live in.

    http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads

    And thanks Anu Viswan's reply.


    Best Regards,
    Rocky Yue[MSFT]
    MSDN Community Support | Feedback to us

    Thursday, April 12, 2012 8:28 AM
    Moderator