Read Excel & run stored proc

[DannyC]

I am trying to read an Excel sheet with Debtor Class across the top, and inventory items down the left col (see sample).
I want to read the sheet and run a stored proc passing the parameters of PartNo, DBClass and price.

There will be a variable number of debtor classes and a variable number of inventory items.

It would be run from a menu item, possibly under Inventory -> Utilities.

I have been trying to work it out but is getting beyond me.

Thanks

Danny

[Mike.Sheen]

I am trying to read an Excel sheet with Debtor Class across the top, and inventory items down the left col (see sample).
I want to read the sheet and run a stored proc passing the parameters of PartNo, DBClass and price.

There will be a variable number of debtor classes and a variable number of inventory items.

It would be run from a menu item, possibly under Inventory -> Utilities.

I have been trying to work it out but is getting beyond me.

Thanks

Danny

Hi Danny,

Which part are you having trouble with ? What have you got so far in terms of code ? (your attachment contained no plugin).

[DannyC]

Mike,
Using the Launch App plugin as a base (the one which executes notepad) I have found sample code on the internet which runs the Windows File Open dialog but I couldn't even get that to compile without error.

I actually don't want the Excel sheet to open, but I need the user to be able to select their own file, hence why I have tried to run the File Open dialog.
Without even getting that far, I am stuck with advancing to the point of reading the Excel file.

[Mike.Sheen]

Mike,
Using the Launch App plugin as a base (the one which executes notepad) I have found sample code on the internet which runs the Windows File Open dialog but I couldn't even get that to compile without error.

I actually don't want the Excel sheet to open, but I need the user to be able to select their own file, hence why I have tried to run the File Open dialog.
Without even getting that far, I am stuck with advancing to the point of reading the Excel file.

Hi Danny,

Attached is a sample which works for me. I added a reference to the Microsoft.Office.Interop.Excel assembly - but if that causes issues then just revert to using CreateObject("Excel.Application") - as explained in the code:

Code: Select all

Partial Class ImportExcel
   Inherits JiwaFinancials.Jiwa.JiwaApplication.ProcessAction.UserInterface
      
   Private fileOpenDialog As System.Windows.Forms.OpenFileDialog = New System.Windows.Forms.OpenFileDialog()
      
   Public Sub New()
       fileOpenDialog.Filter = "Excel Spreadsheets (*.xls;*.xlsx)|*.xls;*.xlsx"
         
      If fileOpenDialog.ShowDialog(JiwaApplication.Manager.Instance.MDIParentForm) = System.Windows.Forms.DialogResult.OK Then
         ' We have added a reference to Microsoft.Office.Interop.Excel.Application - if that causes problems you can always
         ' use the version independent programmatic id instead - but then you lose intellisense / compile time checks
         ' e.g.:            
         ' Dim excel As Object = CreateObject("Excel.Application")
                  
         Dim excel As New Microsoft.Office.Interop.Excel.Application
         Dim book As Microsoft.Office.Interop.Excel.Workbook = excel.Workbooks.Open(fileOpenDialog.FileName)
            
         Dim sheet As Microsoft.Office.Interop.Excel.Worksheet = book.Sheets(1) ' Just assume we're only interested in the first sheet - if not maybe do something like: For Each sheet As Microsoft.Office.Interop.Excel.Sheet In book.Sheets
                     
         For Row As Integer = 2 To 10000
            JiwaApplication.Manager.DisplayMessage(JiwaApplication.Manager.Instance.MDIParentForm, String.Format("Row {0}, Column 1 Has a value of '{1}'", row, If(sheet.Cells(Row, 1).Value Is Nothing, "", sheet.Cells(Row, 1).Value)), System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information, System.Windows.Forms.MessageBoxButtons.OK)
            If sheet.Cells(Row, 1).Value Is Nothing OrElse sheet.Cells(Row, 1).Value.ToString.Trim.Length = 0 Then
               ' Jump out on the first blank row we encounter.
               Exit For
            End If
         Next
         
      End If
   End Sub
      
End Class

Mike

[DannyC]

That is awesome!! I'd never had sussed that one out.

It took me some time to locate Microsoft.Office.Interop.Excel.dll but found it (for the benefit of others reading this)
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14

I reckon I should be able to work out the rest of the code I need & call a stored proc.

Thanks again

[Mike.Sheen]

It took me some time to locate Microsoft.Office.Interop.Excel.dll but found it (for the benefit of others reading this)
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14

The location will vary from machine to machine, depending on the version of Office installed. The version independent programmatic ID I mentioned is a way of insulating your code from these various versions installed on different machines. Note this only works with Assemblies which have a COM interop layer - which Microsoft Office does have.

The location on my machine was C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15\. The location doesn't really matter - but the Assembly Full Name does - when I chose the assembly in that location it loads the Assembly and obtains the Full Name "Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c". It's this Full Name that we use to find assemblies when we load a plugin - not the location. On a system with Office version 14 installed, that assembly reference may fail to resolve - and users logging into Jiwa with that plugin enabled will receive errors.

If you needed to find the reference after importing my plugin, then that demonstrates and proves the problem.

I strongly recommend once you have your plugin finished, remove the reference and use the version independent technique for creating the Excel object before deploying it live - or you will encounter issues.

i.e.:

Code: Select all

Try
    Dim excel As Object = CreateObject("Excel.Application")
Catch ex As System.Exception
   Throw New System.Exception("Microsoft Office is not installed!", ex)
End Try


Mike

[DannyC]

Understood. For deployment to client I'll do your suggestion.

Next question may be worthy of a new thread.
I am now having trouble passing multiple parameters to the stored proc. I'm confident I am getting values in the parameters as I've checked with a messagebox.
When I run the plugin, I am getting an error
"Procedure of function 'usp_UpdateSpecificPrice expects parameter @Classification which was not supplied"

Thing is, as far as I can tell, I am supplying all 3 required parameters. Current version of plugin attached.
And as an FYI, in this scenario I don't need to read any values back into the plugin from the SP resultset, I just need it to fire.

[Mike.Sheen]

I am now having trouble passing multiple parameters to the stored proc. I'm confident I am getting values in the parameters as I've checked with a messagebox.
When I run the plugin, I am getting an error
"Procedure of function 'usp_UpdateSpecificPrice expects parameter @Classification which was not supplied"

Can you provide the SQL for creating the stored proc? The problem parameter is "@Classification" - so either that is missing or spelt different in the actual stored proc, or the value you are passing in the code is Nothing (ie: null) - try replacing this code:

Code: Select all

SQLParam2 = New SqlParameter("@Classification", System.Data.SqlDbType.Char)   
SQLParam2.Value = DBClass(j)
SQLParam2.Direction = System.Data.ParameterDirection.Input

With this:

Code: Select all

SQLParam2 = New SqlParameter("@Classification", System.Data.SqlDbType.Char)   
SQLParam2.Value = If(DBClass(j) Is Nothing, System.DBNull.Value, DBClass(j))
SQLParam2.Direction = System.Data.ParameterDirection.Input

Also, you really shouldn't be using Float for numeric values - use Decimal(19,6) instead - and where you are using Char datatypes, I think VarChar would be more appropriate. It might not seem to make a difference, but there are edge cases where this will cause unexpected behaviour.

Mike

[DannyC]

Mike,

i can't get it to compile using the Excel version independent technique.
'excel is not declared. It may be inaccessible due to its protection level'

Code: Select all

         Try
             Dim excel As Object = CreateObject("Excel.Application")
            Catch ex As System.Exception
               Throw New System.Exception("Microsoft Office is not installed!", ex)
         End Try      
         Dim book As Object = excel.Workbooks.Open(fileOpenDialog.FileName)
            
         Dim sheet As Object = book.Sheets(1) ' Just assume we're only interested in the first sheet - if not maybe do something like: For Each sheet As Microsoft.Office.Interop.Excel.Sheet In book.Sheets


EDIT: I got it to compile by removing the Try

[Scott.Pearce]

You have declared your excel variable within a try-catch block, so it's scope is limited to that try-catch block, but then you are referring to it after the try-catch block is closed. Use this instead:

Code: Select all

Dim excel As Object = nothing
Try
  excel = CreateObject("Excel.Application")
Catch ex As System.Exception
  Throw New System.Exception("Microsoft Office is not installed!", ex)
End Try     

If Not excel is nothing Then
  Dim book As Object = excel.Workbooks.Open(fileOpenDialog.FileName)
  Dim sheet As Object = book.Sheets(1) ' Just assume we're only interested in the first sheet - if not maybe do something like: For Each sheet As Microsoft.Office.Interop.Excel.Sheet In book.Sheets
End If