Advertisement

06.10.2008 at 06:20PM PDT, ID: 23474351
[x]
Attachment Details

small excell conversion from vb.net to c#.

Asked by mathieu_cupryk in Microsoft Visual C#.Net, C# Programming Language, Microsoft Visual Basic.Net

I have the following:
  private void ProcessSpreadsheet(string filename)
        {
            Microsoft.Office.Interop.Excel.Application xlApp;
            // Excel application object
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
            // Excel Workbook object
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
            // Excel Worksheet object
            Microsoft.Office.Interop.Excel.Range inputRange;
            // Excel Worksheet Range object
            int rowCount;
            int currRow;
            // row iterator
            string gradeName;
            // character input variables
            double Price;
            // price as a decimal number
            Microsoft.Office.Interop.Excel.Range cellvalue;
            double[,] discounts = new double[5, 4];
            int pricesColumn;

            // Define the input SS and open the 1st worksheet
            xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(filename);
            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.Item(1);

            // Store the rows and columns used in the input SS into inputRange
            inputRange = xlWorkSheet.UsedRange;

            // Read all used rows in the input SS, starting at the 11th row (rows are numbered from 0)
            // Data of interest is in columns O and R (numbered 15 and 18 respectively)

            for (rowCount = 10; rowCount <= inputRange.Rows.Count; rowCount++)
            {
                // Read the grade name and price
                // skip rows where the grade name is empty
                try
                {
                    // Initial prices load file has the prices in column 16
                    // All other load files have their prices in column 18
                    if (initialButton.Checked == true)
                    {
                        pricesColumn = 16;
                    }
                    else
                    {
                        pricesColumn = 18;
                    }
                    cellvalue = (Microsoft.Office.Interop.Excel.Range)inputRange.Cells(rowCount, pricesColumn);
                    if (Strings.Len(cellvalue.Value) > 0)
                    {
                        if (cellvalue.Value.ToString == "Stones" | cellvalue.Value.ToString == "Tough")
                        {
                            // all prices have been read
                            currRow = rowCount + 1;
                            // skip a row to where the discounts are
                            break; // TODO: might not be correct. Was : Exit For
                        }
                        Price = (double)cellvalue.Value;
                        cellvalue = (Microsoft.Office.Interop.Excel.Range)inputRange.Cells(rowCount, 15);
                        gradeName = cellvalue.Value.ToString;
                        Insert_Price_List_Detail(gradeName, Price);
                        // insert the prices
                    }
                }
                catch
                {
                    Console.WriteLine("Exception has occurred.");
                }
            }

            // Process discounts
            for (rowCount = currRow; rowCount <= currRow + 3; rowCount++)
            {
                // Read the discounts
                try
                {
                    cellvalue = (Microsoft.Office.Interop.Excel.Range)inputRange.Cells(rowCount, 16);
                    if (Strings.Len(cellvalue.Value) > 0)
                    {
                        discounts[rowCount - currRow, 0] = (double)cellvalue.Value;
                        cellvalue = (Microsoft.Office.Interop.Excel.Range)inputRange.Cells(rowCount, 17);
                        discounts[rowCount - currRow, 1] = (double)cellvalue.Value;
                        cellvalue = (Microsoft.Office.Interop.Excel.Range)inputRange.Cells(rowCount, 18);
                        if (cellvalue.Value.ToString != "N/A")
                        {
                            // Des Barley is the last discount
                            discounts[rowCount - currRow, 2] = (double)cellvalue.Value;
                            Update_Price_List_Header_with_Discounts(discounts);
                            // save the discounts
                            break; // TODO: might not be correct. Was : Exit For
                        }
                        else
                        {
                            discounts[rowCount - currRow, 2] = 0;
                        }
                    }
                }
                catch
                {
                    Console.WriteLine("Exception has occurred.");
                }
            }

            inputRange = null;
            xlWorkSheet = null;
            if (xlWorkBook != null)
            {
                xlWorkBook.Close(false);
            }
            xlWorkBook = null;
            if (xlApp != null)
            {
                xlApp.Quit();
            }
            xlApp = null;

        }

Error      1      Cannot apply indexing with [] to an expression of type 'method group'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      37      26      InitialPriceReportingSpreadsheet
Error      2      Property, indexer, or event 'Item' is not supported by the language; try directly calling accessor method 'Microsoft.Office.Interop.Excel.Sheets.get_Item(object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      38      91      InitialPriceReportingSpreadsheet
Error      3      The name 'initialButton' does not exist in the current context      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      54      25      InitialPriceReportingSpreadsheet
Error      4      'Microsoft.Office.Interop.Excel.Range.Cells' is a 'property' but is used like a 'method'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      62      82      InitialPriceReportingSpreadsheet
Error      5      The name 'Strings' does not exist in the current context      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      63      25      InitialPriceReportingSpreadsheet
Error      6      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      63      47      InitialPriceReportingSpreadsheet
Error      7      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      65      39      InitialPriceReportingSpreadsheet
Error      8      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      65      78      InitialPriceReportingSpreadsheet
Error      9      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      72      51      InitialPriceReportingSpreadsheet
Error      10      'Microsoft.Office.Interop.Excel.Range.Cells' is a 'property' but is used like a 'method'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      73      86      InitialPriceReportingSpreadsheet
Error      11      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      74      47      InitialPriceReportingSpreadsheet
Error      12      'Microsoft.Office.Interop.Excel.Range.Cells' is a 'property' but is used like a 'method'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      91      82      InitialPriceReportingSpreadsheet
Error      13      The name 'Strings' does not exist in the current context      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      92      25      InitialPriceReportingSpreadsheet
Error      14      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      92      47      InitialPriceReportingSpreadsheet
Error      15      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      94      78      InitialPriceReportingSpreadsheet
Error      16      'Microsoft.Office.Interop.Excel.Range.Cells' is a 'property' but is used like a 'method'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      95      86      InitialPriceReportingSpreadsheet
Error      17      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      96      78      InitialPriceReportingSpreadsheet
Error      18      'Microsoft.Office.Interop.Excel.Range.Cells' is a 'property' but is used like a 'method'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      97      86      InitialPriceReportingSpreadsheet
Error      19      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      98      39      InitialPriceReportingSpreadsheet
Error      20      Property, indexer, or event 'Value' is not supported by the language; try directly calling accessor methods 'Microsoft.Office.Interop.Excel.Range.get_Value(object)' or 'Microsoft.Office.Interop.Excel.Range.set_Value(object, object)'      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      101      82      InitialPriceReportingSpreadsheet
Error      21      No overload for method 'Close' takes '1' arguments      C:\Users\Aministrator\Desktop\InitialPriceReporting\InitialPriceReporting\DotNet\InitialPriceReportingSpreadsheet\Program.cs      122      17      InitialPriceReportingSpreadsheet
Start Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
Private Sub Open_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click
        Dim xlApp As Excel.Application               ' Excel application object
        Dim xlWorkBook As Excel.Workbook             ' Excel Workbook object
        Dim xlWorkSheet As Excel.Worksheet           ' Excel Worksheet object
        Dim inputRange As Excel.Range                ' Excel Worksheet Range object
        Dim rowCount, currRow As Integer             ' row iterator 
        Dim gradeName As String                      ' character input variables
        Dim Price As Double                          ' price as a decimal number
        Dim cellvalue As Excel.Range
        Dim discounts(4, 3) As Double
        Dim pricesColumn As Integer
 
        ' Define the input SS and open the 1st worksheet
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(txtFileInput.Text)
        xlWorkSheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)
 
        ' Store the rows and columns used in the input SS into inputRange
        inputRange = xlWorkSheet.UsedRange
 
        ' Read all used rows in the input SS, starting at the 11th row (rows are numbered from 0)
        ' Data of interest is in columns O and R (numbered 15 and 18 respectively)
 
        For rowCount = 10 To inputRange.Rows.Count
            ' Read the grade name and price
            ' skip rows where the grade name is empty
            Try
                ' Initial prices load file has the prices in column 16
                ' All other load files have their prices in column 18
                If initialButton.Checked = True Then
                    pricesColumn = 16
                Else
                    pricesColumn = 18
                End If
                cellvalue = CType(inputRange.Cells(rowCount, pricesColumn), Excel.Range)
                If Len(cellvalue.Value) > 0 Then
                    If cellvalue.Value.ToString = "Stones" Or cellvalue.Value.ToString = "Tough" Then ' all prices have been read
                        currRow = rowCount + 1 ' skip a row to where the discounts are
                        Exit For
                    End If
                    Price = CDbl(cellvalue.Value)
                    cellvalue = CType(inputRange.Cells(rowCount, 15), Excel.Range)
                    gradeName = cellvalue.Value.ToString
                    Insert_Price_List_Detail(gradeName, Price) ' insert the prices
                End If
            Catch
                Console.WriteLine("Exception has occurred.")
            End Try
        Next
 
        ' Process discounts
        For rowCount = currRow To currRow + 3
            ' Read the discounts
            Try
                cellvalue = CType(inputRange.Cells(rowCount, 16), Excel.Range)
                If Len(cellvalue.Value) > 0 Then
                    discounts(rowCount - currRow, 0) = CDbl(cellvalue.Value)
                    cellvalue = CType(inputRange.Cells(rowCount, 17), Excel.Range)
                    discounts(rowCount - currRow, 1) = CDbl(cellvalue.Value)
                    cellvalue = CType(inputRange.Cells(rowCount, 18), Excel.Range)
                    If cellvalue.Value.ToString <> "N/A" Then ' Des Barley is the last discount
                        discounts(rowCount - currRow, 2) = CDbl(cellvalue.Value)
                        Update_Price_List_Header_with_Discounts(discounts) ' save the discounts
                        Exit For
                    Else
                        discounts(rowCount - currRow, 2) = 0
                    End If
                End If
            Catch
                Console.WriteLine("Exception has occurred.")
            End Try
        Next
 
        inputRange = Nothing
        xlWorkSheet = Nothing
        If xlWorkBook IsNot Nothing Then
            xlWorkBook.Close(False)
        End If
        xlWorkBook = Nothing
        If xlApp IsNot Nothing Then
            xlApp.Quit()
        End If
        xlApp = Nothing
 
        ' Disable the "Open" button, so the user knows the file has been
        ' processed
        btnOpen.Enabled = False
    End Sub
[+][-]06.10.2008 at 09:56PM PDT, ID: 21756891

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: Microsoft Visual C#.Net, C# Programming Language, Microsoft Visual Basic.Net
Sign Up Now!
Solution Provided By: gauthampj
Participating Experts: 1
Solution Grade: A
 
 
[+][-]06.10.2008 at 10:21PM PDT, ID: 21757022

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.10.2008 at 10:22PM PDT, ID: 21757032

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.10.2008 at 10:24PM PDT, ID: 21757036

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.10.2008 at 10:24PM PDT, ID: 21757040

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628