Jiwa

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

DannyC wrote: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).

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.

DannyC wrote: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

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

DannyC wrote: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

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.

DannyC wrote: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

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

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