表格中的数据

时间:2022-09-15 21:21:13

I was given the assignment of parsing a word document form, and putting it into an Excel sheet. The code below does that with ease.. but I've recently learned I need to put that data into an Excel table instead of just the cells of a sheet.

我被分配解析word文档表单,并将其放入Excel表格中。下面的代码很轻松。但我最近了解到,我需要将这些数据放到Excel表中,而不是只放在表格的单元格中。

For row = 1 To theTable.Rows.Count   `until end of rows
                isHeaderOrNot = theTable.Cell(row, 0).Range.Text `if first field in row is a header
                If isHeaderOrNot.Contains("Section") Or isHeaderOrNot.Contains("Field") Then Continue For
                keyText = theTable.Cell(row, 2).Range.Text    `gets key text
                valueText = theTable.Cell(row, 3).Range.Text  `gets value text
                cleanStringKey = Regex.Replace(keyText, Chr(7), "") `clean strings
                cleanStringValue = Regex.Replace(valueText, Chr(7), "")


                xlWorkSheet.Cells(1, column) = cleanStringKey `puts key in row 1 and column n
                xlWorkSheet.Cells(2, column) = cleanStringValue `puts value in row 2 column n

                column = column + 1 `increment column
            Next

I was wondering if I would have to completely change my code in order to make it a table... In short go from

我想知道我是否需要完全改变我的代码,以使它成为一个表格……总之从

表格中的数据

To

表格中的数据

I am completely new to VB.net so if you could, dumb everything down as much as possible.

我对VB.net是全新的,所以如果你可以的话,尽可能的把所有的事情都说出来。

1 个解决方案

#1


1  

You can use Add method of WorkSheet.ListObject to create a new list (table).

您可以使用工作表的添加方法。ListObject创建一个新的列表(表)。

Example

例子

After adding reference to Microsoft.Office.Interop.Excel Add this import to the form:

在添加了对Microsoft.Office.Interop的引用之后。Excel将此导入添加到表单:

Imports XL = Microsoft.Office.Interop.Excel

Then use such code to create a list:

然后使用这些代码创建一个列表:

Dim Application = New XL.Application()
Application.Visible = True
Dim book = Application.Workbooks.Add()
Dim sheet = DirectCast(book.Worksheets(1), XL.Worksheet)
sheet.Cells(1, 1) = "Product"
sheet.Cells(1, 2) = "Price"
sheet.Cells(2, 1) = "Product 1"
sheet.Cells(2, 2) = "100"
sheet.Cells(3, 1) = "Product 2"
sheet.Cells(3, 2) = "200"
sheet.Cells(4, 1) = "Product 3"
sheet.Cells(4, 2) = "300"
Dim list = sheet.ListObjects.Add( _
                XL.XlListObjectSourceType.xlSrcRange, _
                sheet.Range(sheet.Cells(1, 1), sheet.Cells(4, 2)), _
                Type.Missing, XL.XlYesNoGuess.xlYes)

Then you will see this result:

然后你会看到这个结果:

表格中的数据

#1


1  

You can use Add method of WorkSheet.ListObject to create a new list (table).

您可以使用工作表的添加方法。ListObject创建一个新的列表(表)。

Example

例子

After adding reference to Microsoft.Office.Interop.Excel Add this import to the form:

在添加了对Microsoft.Office.Interop的引用之后。Excel将此导入添加到表单:

Imports XL = Microsoft.Office.Interop.Excel

Then use such code to create a list:

然后使用这些代码创建一个列表:

Dim Application = New XL.Application()
Application.Visible = True
Dim book = Application.Workbooks.Add()
Dim sheet = DirectCast(book.Worksheets(1), XL.Worksheet)
sheet.Cells(1, 1) = "Product"
sheet.Cells(1, 2) = "Price"
sheet.Cells(2, 1) = "Product 1"
sheet.Cells(2, 2) = "100"
sheet.Cells(3, 1) = "Product 2"
sheet.Cells(3, 2) = "200"
sheet.Cells(4, 1) = "Product 3"
sheet.Cells(4, 2) = "300"
Dim list = sheet.ListObjects.Add( _
                XL.XlListObjectSourceType.xlSrcRange, _
                sheet.Range(sheet.Cells(1, 1), sheet.Cells(4, 2)), _
                Type.Missing, XL.XlYesNoGuess.xlYes)

Then you will see this result:

然后你会看到这个结果:

表格中的数据