如何添加新表？ [英] How do I add a new sheet?

问题描述

如何添加新工作表？



我的代码提示错误：

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

            '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

推荐答案

如果您使用的是.net，请使用此组件来创建Excel文件：< a href =https://epplus.codeplex.com/> https://epplus.codeplex.com/ [ ^ ]

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

这篇关于如何添加新表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！