Click here to Skip to main content
15,897,518 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
How do I add a new sheet?

My code prompts an error:
com.sun.star.lang.IndexOutOfBoundsException:


Here is the codes:
VB
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

            'Set properties of calc sheets
            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)

            'Set the name of sheet1
            objSheet1.Name = "Amortization"
            Dim dtAmort As System.Data.DataTable = grdvwAmortization.DataSource

            Dim iColumnIndex As Integer = 0
            Dim iRowIndex As Integer = 0

            ''Fill the sheet1 by reading the grdvwAmortization data
            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) ' <== Error
            objSheet2.Name = "SOA"

            'Account Details
            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

            'Copy datagridview to excel
            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, "\", "/"))

            'If RadioButton1.Checked Then
            '    objArgument3(1) = SetProperties("FilterName", "StarCalc 4.2")
            'Else
            '    objArgument3(1) = SetProperties("FilterName", "MS Excel 97")
            'End If

            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
Posted
Comments
DamithSL 12-Aug-14 23:14pm    
in which line you get this error?
Sergey Alexandrovich Kryukov 13-Aug-14 1:02am    
What sheet? add to what?..
—SA

1 solution

If you are on .net then use this component instead to create Excel files : https://epplus.codeplex.com/[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900