Skip to content

Instantly share code, notes, and snippets.

@karenpayneoregon
Last active March 25, 2021 19:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save karenpayneoregon/c2a40eeba2ea6cd29cd0311caa4948d3 to your computer and use it in GitHub Desktop.
Save karenpayneoregon/c2a40eeba2ea6cd29cd0311caa4948d3 to your computer and use it in GitHub Desktop.
For forum question

OPENROWSET is SQL-Server only.

Now there is another way which if you are up to a huge challenge and know full well you need to take your time as I'm not here to walk you thru this as I see this as a last ditch option which I've only used once or twice back around 2008, see the following.

  1. Create your template file
  2. Do the formatting
  3. Save it
  4. Change the ext from .xlsx to .zip
  5. Rip out the details
  6. Modify it so that resembles what is shown below but without the LINQ and usage of the LINQ and replace with your LINQ read from the DataTable.

Begin code

    '
    ' shows a method which uses an Excel file saved as XML format for the
    ' base to import data from your data source. 
    ' This demo uses two text files, one comma delimited and one tab delimited.
    '
    
    
    Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:o="urn:schemas-microsoft-com:office:office">
    Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">
    Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
    Imports <xmlns:html="http://www.w3.org/TR/REC-html40">
    
    Public Class Form1
        ''' <summary>
        ''' Build Excel worksheet from comma delimited text file
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim Lines =
            (
                From line In IO.File.ReadAllLines("NamesByComma.txt")
                Where line.Length > 0
                Let Items = line.Split(","c)
                Select New With {.Name = Items(0), .State = Items(1)}
            ).ToList
    
            Dim Persons = From P In Lines Select
                          <Row>
                              <Cell><Data ss:Type="String"><%= P.Name %></Data></Cell>
                              <Cell><Data ss:Type="String"><%= P.State %></Data></Cell>
                          </Row>
    
            Dim Document =
            <?xml version="1.0"?>
            <?mso-application progid="Excel.Sheet"?>
            <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">
                <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                    <Author>Karen Payne</Author>
                    <LastAuthor>Karen Payne</LastAuthor>
                    <Created>2011-06-22T02:26:45Z</Created>
                    <Version>12.00</Version>
                </DocumentProperties>
                <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                    <WindowHeight>12300</WindowHeight>
                    <WindowWidth>21075</WindowWidth>
                    <WindowTopX>240</WindowTopX>
                    <WindowTopY>75</WindowTopY>
                    <ProtectStructure>False</ProtectStructure>
                    <ProtectWindows>False</ProtectWindows>
                </ExcelWorkbook>
                <Styles>
                    <Style ss:ID="Default" ss:Name="Normal">
                        <Alignment ss:Vertical="Bottom"/>
                        <Borders/>
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                        <Interior/>
                        <NumberFormat/>
                        <Protection/>
                    </Style>
                    <Style ss:ID="s62">
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                            ss:Bold="1"/>
                    </Style>
                </Styles>
                <Worksheet ss:Name="People">
                    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                        <Row>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">Name</Data></Cell>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">State</Data></Cell>
                        </Row>
                        <%= Persons %>
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <Print>
                            <ValidPrinterInfo/>
                            <HorizontalResolution>600</HorizontalResolution>
                            <VerticalResolution>600</VerticalResolution>
                        </Print>
                        <Selected/>
                        <Panes>
                            <Pane>
                                <Number>3</Number>
                                <ActiveRow>4</ActiveRow>
                                <ActiveCol>1</ActiveCol>
                            </Pane>
                        </Panes>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet2">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet3">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
            </Workbook>
    
            Document.Save("Persons.xml")
    
        End Sub
        ''' <summary>
        ''' Build Excel worksheet from tab delimited text file
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            Dim Lines =
            (
                From line In IO.File.ReadAllLines("NamesByTab.txt")
                Where line.Length > 0
                Let Items = line.Split(CChar(System.Text.RegularExpressions.Regex.Unescape("\t")))
                Select New With
                       {
                           .Name = Items(0),
                           .State = Items(1)
                       }
            ).ToList
    
            Dim Persons = From P In Lines Select
                          <Row>
                              <Cell><Data ss:Type="String"><%= P.Name %></Data></Cell>
                              <Cell><Data ss:Type="String"><%= P.State %></Data></Cell>
                          </Row>
    
            Dim Document = _
            <?xml version="1.0"?>
            <?mso-application progid="Excel.Sheet"?>
            <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">
                <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                    <Author>Karen Payne</Author>
                    <LastAuthor>Karen Payne</LastAuthor>
                    <Created>2011-06-22T02:26:45Z</Created>
                    <Version>12.00</Version>
                </DocumentProperties>
                <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                    <WindowHeight>12300</WindowHeight>
                    <WindowWidth>21075</WindowWidth>
                    <WindowTopX>240</WindowTopX>
                    <WindowTopY>75</WindowTopY>
                    <ProtectStructure>False</ProtectStructure>
                    <ProtectWindows>False</ProtectWindows>
                </ExcelWorkbook>
                <Styles>
                    <Style ss:ID="Default" ss:Name="Normal">
                        <Alignment ss:Vertical="Bottom"/>
                        <Borders/>
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                        <Interior/>
                        <NumberFormat/>
                        <Protection/>
                    </Style>
                    <Style ss:ID="s62">
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                            ss:Bold="1"/>
                    </Style>
                </Styles>
                <Worksheet ss:Name="People">
                    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                        <Row>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">Name</Data></Cell>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">State</Data></Cell>
                        </Row>
                        <%= Persons %>
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <Print>
                            <ValidPrinterInfo/>
                            <HorizontalResolution>600</HorizontalResolution>
                            <VerticalResolution>600</VerticalResolution>
                        </Print>
                        <Selected/>
                        <Panes>
                            <Pane>
                                <Number>3</Number>
                                <ActiveRow>4</ActiveRow>
                                <ActiveCol>1</ActiveCol>
                            </Pane>
                        </Panes>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet2">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet3">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
            </Workbook>
    
            Document.Save("Persons1.xml")
        End Sub
    
        Private Sub cmdDataTableSource_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDataTableSource.Click
    
            Dim Persons = From P In CType(DataGridView1.DataSource, DataTable).AsEnumerable
                          Select
                          <Row>
                              <Cell><Data ss:Type="String"><%= P.Field(Of String)("FirstName") %></Data></Cell>
                              <Cell><Data ss:Type="String"><%= P.Field(Of String)("LastName") %></Data></Cell>
                          </Row>
    
            Dim Document = _
            <?xml version="1.0"?>
            <?mso-application progid="Excel.Sheet"?>
            <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:o="urn:schemas-microsoft-com:office:office"
                xmlns:x="urn:schemas-microsoft-com:office:excel"
                xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
                xmlns:html="http://www.w3.org/TR/REC-html40">
                <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                    <Author>Karen Payne</Author>
                    <LastAuthor>Karen Payne</LastAuthor>
                    <Created>2011-06-22T02:26:45Z</Created>
                    <Version>12.00</Version>
                </DocumentProperties>
                <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                    <WindowHeight>12300</WindowHeight>
                    <WindowWidth>21075</WindowWidth>
                    <WindowTopX>240</WindowTopX>
                    <WindowTopY>75</WindowTopY>
                    <ProtectStructure>False</ProtectStructure>
                    <ProtectWindows>False</ProtectWindows>
                </ExcelWorkbook>
                <Styles>
                    <Style ss:ID="Default" ss:Name="Normal">
                        <Alignment ss:Vertical="Bottom"/>
                        <Borders/>
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
                        <Interior/>
                        <NumberFormat/>
                        <Protection/>
                    </Style>
                    <Style ss:ID="s62">
                        <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
                            ss:Bold="1"/>
                    </Style>
                </Styles>
                <Worksheet ss:Name="People">
                    <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount=<%= Persons.Count + 1 %> x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                        <Row>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">First</Data></Cell>
                            <Cell ss:StyleID="s62"><Data ss:Type="String">Last</Data></Cell>
                        </Row>
                        <%= Persons %>
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <Print>
                            <ValidPrinterInfo/>
                            <HorizontalResolution>600</HorizontalResolution>
                            <VerticalResolution>600</VerticalResolution>
                        </Print>
                        <Selected/>
                        <Panes>
                            <Pane>
                                <Number>3</Number>
                                <ActiveRow>4</ActiveRow>
                                <ActiveCol>1</ActiveCol>
                            </Pane>
                        </Panes>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet2">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
                <Worksheet ss:Name="Sheet3">
                    <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
                        x:FullRows="1" ss:DefaultRowHeight="15">
                    </Table>
                    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                        <PageSetup>
                            <Header x:Margin="0.3"/>
                            <Footer x:Margin="0.3"/>
                            <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
                        </PageSetup>
                        <ProtectObjects>False</ProtectObjects>
                        <ProtectScenarios>False</ProtectScenarios>
                    </WorksheetOptions>
                </Worksheet>
            </Workbook>
    
            Document.Save("Persons_dt.xml")
        End Sub
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=People.accdb"
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT TOP 10 * FROM Person"
                    }
    
                    Dim dt As New DataTable
    
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
    
                    DataGridView1.DataSource = dt
    
                End Using
            End Using
        End Sub
    
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
            Dim FileName As String = IO.Path.Combine(Application.StartupPath, "Persons_dt.xlsx")
            If IO.File.Exists(FileName) Then
                Process.Start(FileName)
            Else
                MessageBox.Show(FileName & " not located")
            End If
        End Sub
    End Class

End result is a structured xml file that when opened Excel nobody would know the difference. Of course you can save e.g. Save As, change to .xlsx and done. Not perfect and surely a good deal of work when using a third party library is a hand full of lines.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment