mapping datagrid cells to excel template

[exile07]

hi
I'm trying to figure out how i can export contents of a datagrid to a template in excel. I have the code to export to excel which works but all it does is open a new spreadsheet and dump the contents here. I need to take the datagrid contents, open a excel template and map the cells to specific cells on the template all behind a button click event on my application.

many thanks

here is what i have to export to excel

 Private Sub btnTrackingExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTrackingExcel.Click

        If (Me.dgTrackingCurrentInvoice Is Nothing) Then
            Throw New ArgumentNullException("No DataGridView was provided for export")
        End If
        Using saveFileDialog As SaveFileDialog = Me.GetExcelSaveFileDialog
            If (saveFileDialog.ShowDialog(Me) = DialogResult.OK) Then
                filename = saveFileDialog.FileName

                Dim fs As New IO.StreamWriter(filename, False)
                fs.WriteLine("<?xml version=""1.0""?>")
                fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
                fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"">")
                fs.WriteLine("    <ss:Styles>")
                fs.WriteLine("        <ss:Style ss:ID=""1"">")
                fs.WriteLine("           <ss:Font ss:Bold=""1""/>")
                fs.WriteLine("        </ss:Style>")
                fs.WriteLine("    </ss:Styles>")
                fs.WriteLine("    <ss:Worksheet ss:Name=""Sheet1"">")
                fs.WriteLine("        <ss:Table>")
                For x As Integer = 0 To dgTrackingCurrentInvoice.Columns.Count - 1
                    fs.WriteLine("            <ss:Column ss:Width=""{0}""/>", dgTrackingCurrentInvoice.Columns.Item(x).Width)
                Next
                fs.WriteLine("            <ss:Row ss:StyleID=""1"">")
                For i As Integer = 0 To dgTrackingCurrentInvoice.Columns.Count - 1
                    fs.WriteLine("                <ss:Cell>")
                    fs.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", dgTrackingCurrentInvoice.Columns.Item(i).HeaderText))

                    fs.WriteLine("                </ss:Cell>")
                Next
                fs.WriteLine("            </ss:Row>")
                For intRow As Integer = 0 To dgTrackingCurrentInvoice.RowCount - 2
                    fs.WriteLine(String.Format("            <ss:Row ss:Height =""{0}"">", dgTrackingCurrentInvoice.Rows(intRow).Height))

                    For intCol As Integer = 0 To dgTrackingCurrentInvoice.Columns.Count - 1
                        fs.WriteLine("                <ss:Cell>")
                        fs.WriteLine(String.Format("                   <ss:Data ss:Type=""String"">{0}</ss:Data>", dgTrackingCurrentInvoice.Item(intCol, intRow).Value.ToString))

                        fs.WriteLine("                </ss:Cell>")
                    Next
                    fs.WriteLine("            </ss:Row>")
                Next
                fs.WriteLine("        </ss:Table>")
                fs.WriteLine("    </ss:Worksheet>")
                fs.WriteLine("</ss:Workbook>")
                fs.Close()

                Dim excel As Microsoft.Office.Interop.Excel.Application

                Dim wb As Microsoft.Office.Interop.Excel.Workbook

                excel = New Microsoft.Office.Interop.Excel.Application
                wb = excel.Workbooks.Open(filename)
                excel.Visible = True
                wb.Activate()

            Else
            End If
        End Using
    End Sub

    Private Function GetExcelSaveFileDialog() As SaveFileDialog
        Dim saveFileDialog As New SaveFileDialog
        saveFileDialog.CheckPathExists = True
        saveFileDialog.AddExtension = True
        saveFileDialog.ValidateNames = True
        saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        saveFileDialog.DefaultExt = ".xls"
        saveFileDialog.Filter = "Microsoft Excel Workbook (*.xls)|*.xls"
        Return saveFileDialog
    End Function

 

[cjard]

You want to put column number 3's data from the dataset, into column number 1 (excel column A) in excel, for example?

Right, well youre gonna have to set up that mapping using a dictionary(of int, int)

Dim mapping as new Dictionary(Of Int32, Int32)
mapping(2) = 0
mapping(1) = 2
mapping(0) = 1


and where you say:

dgTrackingCurrentInvoice.Item(intCol, intRow).Value.ToString


youre gonna have to transform the col value:

dgTrackingCurrentInvoice.Item(mapping(intCol), intRow).Value.ToString


Now, whatever appears in the third col in the dataset will appear in the first col in excel


-

Just a thought.. are you allowing the user to drag the columns round in your app and then export to excel, or will they always have a certain output mapping?

 

[exile07]

thanks for your reply. well thing is, the contents of the datagrid represents an invoice. the datatable is made up of an TaskID, Description and price column. i edit the prices and then want to export to the template. since its an invoice, there isnt a fixed amount of rows as the amount of items differ for each one so i think i need some sort of loop to cycle through each row?

in response to your question, no the user doesn't drag columns.

thanks

 

[cjard]

I dont see that being a problem in your current code? It has loops?