How do I add a new sheet?
My code prompts an error:
com.sun.star.lang.IndexOutOfBoundsException:
Here is the codes:
Private Sub SaveAsOds()
If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Dim objServManager As Object
Dim objDesktop As Object
Dim objDocument As Object
Dim objSheet1 As Object
Dim objSheet2 As Object
Dim objFrame As Object
Dim objDispatch As Object
Dim objArguments As Object() = {}
Dim objArgument1(0) As Object
Dim objArgument2(3) As Object
Dim objArgument3(1) As Object
objServManager = CreateObject("com.sun.star.ServiceManager")
objDesktop = objServManager.createInstance("com.sun.star.frame.Desktop")
objDocument = objDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, objArguments)
objDispatch = objServManager.createInstance("com.sun.star.frame.DispatchHelper")
objFrame = objDocument.getCurrentController.getFrame
objArgument3(0) = SetProperties("Name", "sheetx")
objArgument3(1) = SetProperties("Index", 3)
Call objDispatch.executeDispatch(objFrame, ".uno:Insert", "", 0, objArgument3)
objArgument3(0) = SetProperties("Name", "sheetx1")
objArgument3(1) = SetProperties("Index", 3)
Call objDispatch.executeDispatch(objFrame, ".uno:Insert", "", 0, objArgument3)
objArgument3(0) = SetProperties("Name", "sheetx2")
objArgument3(1) = SetProperties("Index", 3)
Call objDispatch.executeDispatch(objFrame, ".uno:Insert", "", 0, objArgument3)
objSheet1 = objDocument.Sheets.getByIndex(0)
objSheet1.Name = "Amortization"
Dim dtAmort As System.Data.DataTable = grdvwAmortization.DataSource
Dim iColumnIndex As Integer = 0
Dim iRowIndex As Integer = 0
For i = 0 To grdvwAmortization.RowCount - 2
For j = 0 To grdvwAmortization.ColumnCount - 1
objSheet1.getCellbyPosition(j, i + 1).setString(grdvwAmortization(j, i).Value.ToString())
Next
Next
objSheet2 = objDocument.Sheets.getByIndex(1)
objSheet2.Name = "SOA"
objSheet2.getCellbyPosition(4, 14) = Date.Now.ToString("MM/dd/yyyy")
objSheet2.getCellbyPosition(6, 4) = lblLastName.Text + ", " + lblFirstName.Text
objSheet2.getCellbyPosition(7, 4) = getLoanType(lblLoanType.Text)
objSheet2.getCellbyPosition(12, 4).NumberFormat = "@"
objSheet2.getCellbyPosition(12, 4) = txtbxPNNum.Text
Dim Row As Integer = 13
For i = 0 To grdvwSOA.RowCount - 2
For j = 0 To grdvwSOA.ColumnCount - 1
objSheet2.getCellbyPosition(Row, j + 1).setString(grdvwSOA(j, i).Value.ToString())
Next
Row = Row + 1
Next
Dim strFilename As String = String.Empty
Dim binFileOpen As Boolean = False
strFilename = SaveFileDialog1.FileName + "_" + Replace(Date.Today.ToShortDateString, "/", "_") + ".ods"
Try
objArgument3(0) = SetProperties("URL", "file:///" & Replace(strFilename, "\", "/"))
Call objDispatch.executeDispatch(objFrame, ".uno:SaveAs", "", 0, objArgument3)
objDocument.Close(True)
MsgBox("Data's are exported to Excel Succesfully in '" & strFilename & "'", MsgBoxStyle.Information)
Catch ex As Exception
binFileOpen = False
MsgBox(ex.Message)
End Try
End If
End Sub