NAV 2013 Excel integration

Rudolf

Hi,

I have the standard Excel-reports running withour any problems (ex. report 82)
I then try to use the new DonNet-functions in ExcelBuffer-table (370).
I want to loop through an Items BOM, write it to Excel and then save the Excel-file to the client
without opening it.

I have build the following code: (only partly shown)
(where EnterCell fills in a temporary variable build on table 370 and inserting the record)

IF Item."Production BOM No." <> '' THEN BEGIN
BOM_Line.SETRANGE("Production BOM No.",Item."Production BOM No.");
IF BOM_Line.FINDSET THEN
REPEAT
EnterCell(RowNo,1,FORMAT(BOM_Line.Type),FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
EnterCell(RowNo,2,BOM_Line."No.",FALSE,TRUE,'@',ExcelBuf."Cell Type"::Text);
EnterCell(RowNo,3,BOM_Line.Description,FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
EnterCell(RowNo,4,FORMAT(BOM_Line."Quantity per"),FALSE,TRUE,'',ExcelBuf."Cell Type"::Number);
EnterCell(RowNo,5,BOM_Line."Unit of Measure Code",FALSE,TRUE,'',ExcelBuf."Cell Type"::Text);
RowNo := RowNo + 1;
UNTIL BOM_Line.NEXT = 0;
END;

ExcelBuf.CreateBook(Text007);
ExcelBuf.WriteSheet(STRSUBSTNO(Text006,Item."No."),COMPANYNAME,USERID);
ExcelBuf.UTgetGlobalValue('ExcelFile',FromFileName);
ExcelBuf.CloseBook;

ExcelBuf.UTgetGlobalValue('ExcelFile',FromFileName);
MESSAGE(FromFileName);
ToFileName := STRSUBSTNO(Text050,Item."No.");
MESSAGE(ToFileName);
FileManagement.DownloadToFile(FromFileName,ToFileName);

(The 2 message lines are only for testing)

I get an error message stating:
"You must specify a source file name"
(which my message of FromFileName also show is empty)

Apparently I am missing something here.....(?)

Rudolf

forgot to mention that
removing either of the two lines of
UTgetGlobalValue
doesn't make any difference.

agentz

if you install the excel engine on the server, you can point to the library from .net references.
then you can use the direct oop excel functionality to access the workbook/sheets, cells, using data objects i believe...

use data ranges in excel to define things like 'customer', 'shipping', 'items'.

what this basically does is create an instance to the access lib, passing the data ranges to data adapter, data table... you can enumerate, loop and go absolutely ape crazy with it now! (For Each r As DataRow In orderData.Rows)

enjoy

Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=""Excel 12.0;HDR=YES;"""

'Return "connectionstring: " & connectionString & "<br>"

Dim factory As DbProviderFactory = DbProviderFactories.GetFactory("System.Data.OleDb")
Dim adapter As DbDataAdapter = factory.CreateDataAdapter()
Dim selectCommand As DbCommand = factory.CreateCommand()
Dim connection As DbConnection = factory.CreateConnection()
Dim orderData As New DataTable
Dim customerInfo As New DataTable
Dim purchaseOrder As New DataTable
Dim freightInfo1 As New DataTable
'Dim freightInfo2 As New DataTable

Dim navInt As New paeEDI.International

Dim custNo As String = ""

connection.ConnectionString = connectionString
selectCommand.Connection = connection

'get order items and quantity
selectCommand.CommandText = "SELECT * FROM [order] where [UNITS] > 0 and [itemnumber] <>'' "
adapter.SelectCommand = selectCommand
adapter.Fill(orderData)

'get purchase order number
selectCommand.CommandText = "select * from [purchaseordernumber]"
adapter.SelectCommand = selectCommand
adapter.Fill(purchaseOrder)

'get customer information
Try

selectCommand.CommandText = "select * from [customerinfo]"
adapter.SelectCommand = selectCommand
adapter.Fill(customerInfo)

Catch ex As Exception

End Try

'get freight 1 information
selectCommand.CommandText = "select * from [freightinfo1]"
adapter.SelectCommand = selectCommand
adapter.Fill(freightInfo1)

'get freight 2 information
'selectCommand.CommandText = "select * from [freightinfo2]"
'adapter.SelectCommand = selectCommand
'adapter.Fill(freightInfo2)

connection.Close()

gvOrders.DataSource = orderData
gvOrders.DataBind()

gvPurchaseOrder.DataSource = purchaseOrder
gvPurchaseOrder.DataBind()

If Not IsNothing(customerInfo) And customerInfo.Rows.Count > 0 Then

gvCustomerInfo.DataSource = customerInfo
gvCustomerInfo.DataBind()

custNo = customerInfo.Columns(1).ColumnName
lblDistributorName.Text = custNo

Else

'cboxOverrideDistName_CheckedChanged(Nothing, Nothing)
lblDistributorName.Text = "Cust. not found"

End If

gvFreightInfo1.DataSource = freightInfo1
gvFreightInfo1.DataBind()

'gvFreightInfo2.DataSource = freightInfo2
'gvFreightInfo2.DataBind()

'release memory
orderData.Dispose()
purchaseOrder.Dispose()
customerInfo.Dispose()
freightInfo1.Dispose()
'freightInfo2.Dispose()