none
how to copy the non empty cells range in excel sheet using C#?

    General discussion

  • using C# and office excel interop, I need to copy a range of the non-empty cells in a work sheet? 
    I have this code snippet, but I receive an exception in the Copy() method
    the exception is: That command cannot be used on multiple selections
      Excel.Sheets worksheets = excelWorkBook.Sheets;
    foreach (Excel.Worksheet worksheet in worksheets)
    {
    Excel.Range range = worksheet.UsedRange;


    Excel.Range newrange = range.SpecialCells(Excel.XlCellType.xlCellTypeConstants, (object)3);
    newrange.Copy(Type.Missing);
    }

    Amira
    • Changed type Bessie Zhao Thursday, October 08, 2009 1:57 AM
    Wednesday, September 30, 2009 9:10 AM

All replies

  • Hi Amira,

    About this exception in this page, it says as below.
    These error messages may occur when your cell or range selections are in different columns or different rows.

    So, here I also suggest that you could use Range.Cells Property to access each cell[See: Range.Cells Property]. Here is an thread talking about how to use cells of a range: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/0667260c-323a-4293-bd0a-198a245aa23e/.

    Here is the code as below:
                    foreach (Excel.Range cell in newrange.Cells)
                    {
                        Debug.Print(cell.Text.ToString());
                    }

    Best regards,
    Bessie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, October 05, 2009 7:40 AM
  • When you say you need to copy a range.

    Do you mean you need to copy the values, or you need to copy formats, or are you trying to copy all the formats and values and setttings of each cell? etc.

    Then where are you trying to copy them too?

    All this information is useful in trying to help with a suggestion.

    Generally when you are trying to grab constants from a sheet, you are going to get a bunch of strings and numbers all over the shot, and you are better off moving them around by using rng1.value2 = rng2.value2.

    If you want to keep doing what you are doing, then you aren't restricted to iterating through the cells, you can also use the foreach(ExcelRange cell in newRange.Areas){
    }
    syntax, which will give you rectangular ranges. 
    Monday, October 05, 2009 9:14 AM
  • We are changing the issue type to "General Discussion" because you have not followed up with the necessary information. If you have more time to look at the issue and provide more information, please feel free to change the issue type back to "Question" by opening the Options list at the top of the post window, and changing the type. If the issue is resolved, we will appreciate it if you can share the solution so that the answer can be found and used by other community members having similar questions.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, October 08, 2009 1:57 AM