帮助：Excel范围输出到Word表格 [英] Help: Excel Range Output to Word Tables

问题描述

我正在寻找一种方法来在XLS中获取一系列单元格并将它们输出到单词表中。

所以，如果我的范围是：

A，1,2,3

B，1,2,3

C，1,2,3

我想用word创建3（2x3）个表，包含第一个col数据（即A，B，C）的所有表的第一个col和每个表的第2列表格会循环显示范围内的cols。 因此，单词中生成的表格看起来像

A，1

B，1

C ，1

<分页符>

A，2

B，2

C，2

<分页符>

A，3

B，3

C，3

<分页符>

感谢任何帮助！  ; TIA

解决方案

您好，

此结果是否符合您的要求？

代码：

 Imports Excel1 = Microsoft.Office.Interop.Excel 
 Imports word = Microsoft.Office.Interop.Word 
 Imports System.IO 
 Imports Microsoft.Office.Interop.Word 
 Imports System.Data 
 
 
 Public Class Form1 
 Private Sub Form1_Load（sender as Object，e As EventArgs）Handles MyBase.Load 
 Dim docApp As New word.Application 
 Dim doc As word.Document 
 Dim miss As Object = System.Reflection.Missing.Value 
 doc = docApp.Documents.Add（miss ，miss，miss，miss）
 
 Dim table As word.Table = doc.Tables.Add（docApp.Selection.Range，3,1）
 with table 
 
 .Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .B orders.InsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .Columns（1）.Width = 150 
 .Rows（1）.Height = 80 
 .Rows（2）.Height = 80 
 .Rows（3）.Height = 80 
 End with 
 Dim range1 = table.Cell（1,1）.Range 
 Dim table1 as word.Table = doc.Tables.Add（range1 ，3,2,1,1）
使用table1 
 
 .Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .Columns（ 1）.Width = 50 
 .Columns（2）.Width = 50 
 
 End with 
 
 with table1 
 .Rows.Add（）
 .Rows.Add（）
 .Cell（1,1）.Range.Text = GetDataFromExcelByCom（False）.Rows（0）（0）
 .Cell（1,2）.Range .Text = GetDataFromExcelByCom（False）.Rows（0）（1）
 .Cell（2,1）.Range.Text = GetDataFromExcelByCom（False）.Rows（1）（0）
 .Cel l（2,2）.Range.Text = GetDataFromExcelByCom（False）.Rows（1）（1）
 .Cell（3,1）.Range.Text = GetDataFromExcelByCom（False）.Rows（2）（0 ）
 .Cell（3,2）.Range.Text = GetDataFromExcelByCom（False）.Rows（2）（1）
 End with 
 
 
 Dim range2 = table.Cell（2,1）.Range 
 Dim table2 as word.Table = doc.Tables.Add（range2,3,2）
 
 with table2 
 
 .Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .Columns（1）.Width = 50 
 .Columns（2）.Width = 50 
 
结束使用
 
使用table2 
 .Rows.Add（）
 .Rows.Add（）
 .Cell（1,1）.Range .Text = GetDataFromExcelByCom（False）.Rows（0）（0）
 .Cell（2,1）.Range.Text = GetDataFromExcelByCom（False）.Rows（1）（0）
 .Cell（ 3,1）.Range.Text = GetDataFromExcelByCom（False）.Rows（ 2）（0）
 .Cell（1,2）.Range.Text = GetDataFromExcelByCom（False）.Rows（0）（2）
 .Cell（2,2）.Range.Text = GetDataFromExcelByCom （False）.Rows（1）（2）
 .Cell（3,2）.Range.Text = GetDataFromExcelByCom（False）.Rows（2）（2）
 End with 
 
 Dim range3 = table.Cell（3,1）.Range 
 Dim table3 as word.Table = doc.Tables.Add（range3,3,2）
 with table3 
。 Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle 
 .Columns（1）.Width = 50 
 .Columns（2）.Width = 50 
 
以
 
结束使用table3 
 .Rows.Add（）
 .Rows.Add（）
 .Cell（1,1）.Range.Text = GetDataFromExcelByCom（False）.Rows（0）（0）
 .Cell（2,1）.Range.Text = GetDataFromExcelByCom（False）.Rows（1）（0）
 .Cell（3， 1）.Range.Text = GetDataFromExcelByCom（Fal se）.Rows（2）（0）
 .Cell（1,2）.Range.Text = GetDataFromExcelByCom（False）.Rows（0）（3）
 .Cell（2,2）。 Range.Text = GetDataFromExcelByCom（False）.Rows（1）（3）
 .Cell（3,2）.Range.Text = GetDataFromExcelByCom（False）.Rows（2）（3）
 End With 
 doc.SaveAs（" d：/1.doc"）
 doc.Close（）
 docApp.Quit（）
 
 
 End Sub 
私有函数GetDataFromExcelByCom（ByVal可选hasTitle As Boolean = False）As System.Data.DataTable 
 Dim excelFilePath =" C：\Users\alexl2 \Desktop\1.xls" 
 Dim app As Excel1.Application = New Excel1.Application（）
 Dim sheets As Excel1.Sheets 
 Dim oMissiong As Object = System.Reflection.Missing.Value 
 Dim workbook As Excel1.Workbook = Nothing 
 Dim dt As System.Data.DataTable = New System.Data.DataTable（）
 
尝试
如果app Is Nothing则返回Nothing 
 workbook = app.Workbooks.Open（excelFilePath，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong，oMissiong）
 sheets = workbook.Worksheets 
 Dim工作表作为Excel1.Worksheet = sheets（1）
 Dim ji As Integer = CType（12，Integer）
如果工作表是Nothing则返回Nothing 
 Dim iRowCount As Integer = worksheet.UsedRange .Rows.Count 
 Dim iColCount As Integer = worksheet.UsedRange.Columns.Count 
 
对于i As Integer = 0到iColCount  -  1 
 Dim name =" column" &安培; i 
 
如果hasTitle那么
 Dim txt =（CType（worksheet.Cells（1，i + 1），Excel1.Range））。Text.ToString（）
如果不是String.IsNullOrWhiteSpace（txt）然后name = txt 
结束如果
 
而dt.Columns.Contains（name）
 name = name& " _1" 
 End 
 
 dt.Columns.Add（New DataColumn（name，GetType（String）））
 Next 
 
 Dim range As Excel1.Range 
 Dim rowIdx As Integer = If（hasTitle，2,1）
 
 iRow As Integer = rowIdx To iRowCount 
 Dim dr As DataRow = dt.NewRow（）
 
对于iCol As Integer = 1到iColCount 
 range = CType（worksheet.Cells（iRow，iCol），Excel1.Range）
 dr（iCol  -  1）= If（（范围。 Value2 Is Nothing），""，range.Text.ToString（））
下一个
 
 dt.Rows.Add（dr）
下一个
 
返回dt 
 Catch 
返回Nothing 
最后
 workbook.Close（False，oMissiong，oMissiong）
 System.Runtime.InteropServices.Marshal.ReleaseComObject（workbook） 
工作簿=没有
 app.Workbooks.Close（）
 app.Quit（）
 System.Runtime.InteropServices.Marshal.ReleaseComObject（app）
 app = Nothing 
结束尝试
结束函数
结束类
 

最好的问候，

Alex

I am looking for a way to take a range of cells in XLS and output them into word tables.

So, if my range was:

A, 1, 2, 3

B, 1, 2, 3

C, 1, 2, 3

I want to create 3 (2x3) tables in word with the first col for all tables containing the first col data (i.e., A, B, C) and the 2nd column for each of the tables will cycle through the cols in the range.  So, the resulting tables in word will look like

A, 1

B, 1

C, 1

<page break>

A, 2

B, 2

C, 2

<page break>

A, 3

B, 3

C, 3

<page break>

Appreciate any help!  TIA

解决方案

Hi,

Does this result meet your requirements?

code:

Imports Excel1 = Microsoft.Office.Interop.Excel
Imports word = Microsoft.Office.Interop.Word
Imports System.IO
Imports Microsoft.Office.Interop.Word
Imports System.Data


Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim docApp As New word.Application
        Dim doc As word.Document
        Dim miss As Object = System.Reflection.Missing.Value
        doc = docApp.Documents.Add(miss, miss, miss, miss)

        Dim table As word.Table = doc.Tables.Add(docApp.Selection.Range, 3, 1)
        With table

            .Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Columns(1).Width = 150
            .Rows(1).Height = 80
            .Rows(2).Height = 80
            .Rows(3).Height = 80
        End With
        Dim range1 = table.Cell(1, 1).Range
        Dim table1 As word.Table = doc.Tables.Add(range1, 3, 2, 1, 1)
        With table1

            .Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Columns(1).Width = 50
            .Columns(2).Width = 50

        End With

        With table1
            .Rows.Add()
            .Rows.Add()
            .Cell(1, 1).Range.Text = GetDataFromExcelByCom(False).Rows(0)(0)
            .Cell(1, 2).Range.Text = GetDataFromExcelByCom(False).Rows(0)(1)
            .Cell(2, 1).Range.Text = GetDataFromExcelByCom(False).Rows(1)(0)
            .Cell(2, 2).Range.Text = GetDataFromExcelByCom(False).Rows(1)(1)
            .Cell(3, 1).Range.Text = GetDataFromExcelByCom(False).Rows(2)(0)
            .Cell(3, 2).Range.Text = GetDataFromExcelByCom(False).Rows(2)(1)
        End With


        Dim range2 = table.Cell(2, 1).Range
        Dim table2 As word.Table = doc.Tables.Add(range2, 3, 2)

        With table2

            .Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Columns(1).Width = 50
            .Columns(2).Width = 50

        End With

        With table2
            .Rows.Add()
            .Rows.Add()
            .Cell(1, 1).Range.Text = GetDataFromExcelByCom(False).Rows(0)(0)
            .Cell(2, 1).Range.Text = GetDataFromExcelByCom(False).Rows(1)(0)
            .Cell(3, 1).Range.Text = GetDataFromExcelByCom(False).Rows(2)(0)
            .Cell(1, 2).Range.Text = GetDataFromExcelByCom(False).Rows(0)(2)
            .Cell(2, 2).Range.Text = GetDataFromExcelByCom(False).Rows(1)(2)
            .Cell(3, 2).Range.Text = GetDataFromExcelByCom(False).Rows(2)(2)
        End With

        Dim range3 = table.Cell(3, 1).Range
        Dim table3 As word.Table = doc.Tables.Add(range3, 3, 2)
        With table3
            .Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle
            .Columns(1).Width = 50
            .Columns(2).Width = 50

        End With

        With table3
            .Rows.Add()
            .Rows.Add()
            .Cell(1, 1).Range.Text = GetDataFromExcelByCom(False).Rows(0)(0)
            .Cell(2, 1).Range.Text = GetDataFromExcelByCom(False).Rows(1)(0)
            .Cell(3, 1).Range.Text = GetDataFromExcelByCom(False).Rows(2)(0)
            .Cell(1, 2).Range.Text = GetDataFromExcelByCom(False).Rows(0)(3)
            .Cell(2, 2).Range.Text = GetDataFromExcelByCom(False).Rows(1)(3)
            .Cell(3, 2).Range.Text = GetDataFromExcelByCom(False).Rows(2)(3)
        End With
        doc.SaveAs("d:/1.doc")
        doc.Close()
        docApp.Quit()


    End Sub
    Private Function GetDataFromExcelByCom(ByVal Optional hasTitle As Boolean = False) As System.Data.DataTable
        Dim excelFilePath = "C:\Users\alexl2\Desktop\1.xls"
        Dim app As Excel1.Application = New Excel1.Application()
        Dim sheets As Excel1.Sheets
        Dim oMissiong As Object = System.Reflection.Missing.Value
        Dim workbook As Excel1.Workbook = Nothing
        Dim dt As System.Data.DataTable = New System.Data.DataTable()

        Try
            If app Is Nothing Then Return Nothing
            workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong)
            sheets = workbook.Worksheets
            Dim worksheet As Excel1.Worksheet = sheets(1)
            Dim ji As Integer = CType(12, Integer)
            If worksheet Is Nothing Then Return Nothing
            Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count
            Dim iColCount As Integer = worksheet.UsedRange.Columns.Count

            For i As Integer = 0 To iColCount - 1
                Dim name = "column" & i

                If hasTitle Then
                    Dim txt = (CType(worksheet.Cells(1, i + 1), Excel1.Range)).Text.ToString()
                    If Not String.IsNullOrWhiteSpace(txt) Then name = txt
                End If

                While dt.Columns.Contains(name)
                    name = name & "_1"
                End While

                dt.Columns.Add(New DataColumn(name, GetType(String)))
            Next

            Dim range As Excel1.Range
            Dim rowIdx As Integer = If(hasTitle, 2, 1)

            For iRow As Integer = rowIdx To iRowCount
                Dim dr As DataRow = dt.NewRow()

                For iCol As Integer = 1 To iColCount
                    range = CType(worksheet.Cells(iRow, iCol), Excel1.Range)
                    dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString())
                Next

                dt.Rows.Add(dr)
            Next

            Return dt
        Catch
            Return Nothing
        Finally
            workbook.Close(False, oMissiong, oMissiong)
            System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
            workbook = Nothing
            app.Workbooks.Close()
            app.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
            app = Nothing
        End Try
    End Function
End Class

Best Regards,

Alex

这篇关于帮助：Excel范围输出到Word表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！