none
Specifying Column Range via Excel Interop RRS feed

  • Question

  • I'm writing the contents of an array into an .xls file and trying to specify output cells based on a length I have chosen; in this case 15 columns across then go to the next row.  In Excel 15 columns should be A to O.  Currently I'm trying to specify a column range in which my app can populate the Excel cells.  Since I take that range and turn it into an int it only seems to take the total number of rows in that range and put my contents in that column.  I need to return a single int because myWorksheet.Cells takes an int for [row, column] and I can't specify my range 1-15 there.

    How do I specific a column range to be used as an int?

    statsCollect = excelInstance.Workbooks.Open(path, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true,
                                false, 0, true, false, false);
    cellCollection = statsCollect.Worksheets;
    
    cellData = (Microsoft.Office.Interop.Excel.Worksheet)cellCollection.get_Item("Sheet1");
    
    Microsoft.Office.Interop.Excel.Range colRange = cellData.Range[cellData.Cells[Missing.Value, 1], cellData.Cells[Missing.Value, 15]];
    Microsoft.Office.Interop.Excel.Range rowRange = cellData.UsedRange;
    
    int colCount = colRange.Columns.Count;
    int rowCount = rowRange.Rows.Count;
    
    for (int index = 0; index < contents.Length; index++)
    {
        cellData.Cells[rowCount + index, colCount] = contents[index]; 
    }
    statsCollect.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
        Missing.Value, Missing.Value, Missing.Value,
        Missing.Value, Missing.Value);
    
    statsCollect.Close(Missing.Value, Missing.Value, Missing.Value);
    Marshal.ReleaseComObject(cellCollection);
    Marshal.ReleaseComObject(statsCollect);
    excelInstance.Quit();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();

    I initially posted this question on Stack Overflow but haven't gotten any responses so I'm reposting here in hopes someone can help me.


    Monday, January 9, 2017 8:21 PM

All replies

  • Hi BlueBarren,

    Thank you for posting here.

    According to your question is more related to Excel, I will move it to Excel for Developers forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, January 10, 2017 6:54 AM
  • What?  I'm not Unit Testing, I'm creating a C# application that utilizing Microsoft.Office.Interop.Excel to manipulate Excel for the purposes of my C# application.

    Tuesday, January 10, 2017 1:20 PM
  • Hi BlueBarren,

    Sorry for that but I move it to right place Excel for Developers forum for suitable support.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 11, 2017 12:51 AM
  • Hi,

    Do you want to get column index from column name? Like convert A into 1, B into 2?

    You could find different methods from the link below:

    http://stackoverflow.com/questions/848147/how-to-convert-excel-sheet-column-names-into-numbers

    If not, could you please give detail explanation for your case?

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 11, 2017 9:00 AM
    Moderator