locked
Help: Excel Range Output to Word Tables RRS feed

  • Question

  • 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

    Wednesday, September 5, 2018 5:27 AM

All replies

  • 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


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 5, 2018 11:30 AM
  • Thank you, Alex!  This more or less solves the problem.  If I had a range of values in the worksheet and I had to compose tables based on the number of cols (and rows as well) and loop thru them like this rather than a fixed 3 table shown in my example what do you do? How can one modify this?  Thanks!
    • Edited by Simonnara Wednesday, September 5, 2018 1:00 PM
    Wednesday, September 5, 2018 12:16 PM
  • Hi,

    GetDataFromExcelBy converts the values in the Excel  into a DataTable.

    I think you can iterate GetDataFromExcelBy(DataTable) and then generate n table based on your requirements.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 6, 2018 6:20 AM