[John-Ellis]

Hi All,

I have created an application that can store employees details, that part is not a problem and frankly is sorted, I have used a sql database to save my data, all information is displayed in TextBox controls on my main form.

The part I am struggling with is creating 2 buttons.

The first button I want to be able to import data from an excel file into my data set

The second button I want to create is a back-up button, which exports all the data from my dataset into an excel file

I will start looking round google of course but any help would be much appreciated, I have no idea at all where to start with this.

Thanks again

John

This post has been edited by John-Ellis: 17 January 2012 - 12:55 PM

[modi123_1]

Well you have options. What sort of excel file is this? If it's an "CSV" or comma delimited file you could use a streamreader to get every line, break each line apart by the comma, and know that each piece is a data cell... and then a stream writer to write each row to the file with a comma between them...

[umesh.patil]

modi123_1, on 17 January 2012 - 01:37 PM, said:

Well you have options. What sort of excel file is this? If it's an "CSV" or comma delimited file you could use a streamreader to get every line, break each line apart by the comma, and know that each piece is a data cell... and then a stream writer to write each row to the file with a comma between them...

may this help you

Imports System.Data.SqlClient
Imports System.IO

Public Class Export
    Public Sub ExportToExcel(ByVal FileName As String, ByVal SavePath As String, ByVal objDataReader As DataTable)
        Dim i As Integer
        Dim sb As New System.Text.StringBuilder
        Try
            Dim intColumn, intColumnValue As Integer
            Dim row As DataRow
            For intColumn = 0 To objDataReader.Columns.Count - 1
                sb.Append(objDataReader.Columns(intColumn).ColumnName)
                If intColumnValue <> objDataReader.Columns.Count - 1 Then
                    sb.Append(vbTab)
                End If
            Next
            sb.Append(vbCrLf)
            For Each row In objDataReader.Rows
                For intColumnValue = 0 To objDataReader.Columns.Count - 1
                    sb.Append(StrConv(IIf(IsDBNull(row.Item(intColumnValue)), "", row.Item(intColumnValue)), VbStrConv.ProperCase))
                    If intColumnValue <> objDataReader.Columns.Count - 1 Then
                        sb.Append(vbTab)
                    End If
                Next
                sb.Append(vbCrLf)
            Next
            SaveExcel(SavePath & "\" & FileName & ".xls", sb)
        Catch ex As Exception
            Throw
        Finally
            objDataReader = Nothing
            sb = Nothing
        End Try
    End Sub

    Private Sub SaveExcel(ByVal fpath As String, ByVal sb As System.Text.StringBuilder)
        Dim fsFile As New FileStream(fpath, FileMode.Create, FileAccess.Write)
        Dim strWriter As New StreamWriter(fsFile)
        Try
            With strWriter
                .BaseStream.Seek(0, SeekOrigin.End)
                .WriteLine(sb)
                .Close()
            End With
        Catch e As Exception
            Throw
        Finally
            sb = Nothing
            strWriter = Nothing
            fsFile = Nothing
        End Try
    End Sub
End Class

[hemantwithu]

The trick really is getting the format of the Excel spreadsheet as XML and using that sheet as a template to populate your data. First open Excel (for this example I'll use the Excel 2003 format so you can use Excel 2007 or 2003 to follow along). Next let's enter some column headers, for my example I have a list of customers I want to import/export so the fields I'll want are Abbrev, Name, Phone, Country. It's also helpful to enter one line of dummy data (you'll see why in a second). So here' what we've got:

Link 1

[John-Ellis]

Thanks for the help everyone, I was a bit ill last night so hopefully will get chance to code something up tonight.

Thanks again everyone

John