C# Tutorials and offshore development in India

Prizes & Awards

My Profile

AdSense Revenue

Active Members
Today

Last 7 Days

more...

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !

To create a Excel sheet with Formating from CSV File wihtout Excel object model.

Posted Date: 27 Aug 2008 Resource Type: Articles Category: .NET Framework

Posted By: D.Jeya kumar(JK) Member Level: Diamond
Rating:

Points: 10

To create a Excel sheet with Formating from CSV File wihtout Excel object model.

Read the CSV file and then use the header for the Excel sheet to create the Excel file with formating and we can create any number of sheets which can be read from the Comma seperated file(CSV).



Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        lblImportFileName.Text = ""

End Sub



 
    '''Header for the Excel file with Style to format the excel file
    ''' 

    ''' 
    Private sHeader As String = "" + _
    "    "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"">" + _
    "  False" + _
    "False  " + _
    "" + _
    "" + _
    "" + _
    "" + _
    ""

'Path of the Excel file
    Dim Strpath As String = "C:\Users\" & Environment.UserName & "\Desktop\test.xls"



Private sFooter As String = "

"

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Windows.Forms.Cursor.Current = Cursors.WaitCursor
Dim SWorksheet As String

Try
Dim rnd As Random = New Random()
Dim i As Integer = rnd.Next()
'For each worksheet we need to add the sheetname and other properties
SWorksheet = "" + _
"" + _
"" + _
"" + _
"" + _
"" + _
""

If lblImportFileName.Text = "" Or Strpath = "" Then
MsgBox("Please select a file to import in CSV format and also a destination file location and name.")
End If

'To read the CSV file
Dim MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(lblImportFileName.Text)
MyReader.TextFieldType = FileIO.FieldType.Delimited
MyReader.SetDelimiters("\\\")

Dim filewriter As System.IO.StreamWriter
Dim fileReader As System.IO.StreamReader

'To check the file is already exists or not
If System.IO.File.Exists(Strpath) Then
'to read the Previous Worksbook and get all the perviously work sheet
fileReader = My.Computer.FileSystem.OpenTextFileReader(Strpath)
Dim strWorkbook As String = fileReader.ReadToEnd()

'to remove the Workbook and add the workbook after the new work sheet is added.
strWorkbook = strWorkbook.Replace("", "")
fileReader.Close()
fileReader.Dispose()
'Open the file to write
filewriter = My.Computer.FileSystem.OpenTextFileWriter(Strpath, False)
'Write the Previous values in the new excel file
filewriter.Write(strWorkbook)
'New work sheet creation
filewriter.Write(SWorksheet)
Else
'If the file is not found then create a new excel file.
filewriter = My.Computer.FileSystem.OpenTextFileWriter(Strpath, False)
filewriter.Write(sHeader)
filewriter.Write(SWorksheet)
End If

'Read the file and add the Values from CSV.

Dim currentRow As String()
While Not MyReader.EndOfData
Try
currentRow = MyReader.ReadFields()
Dim currentField As String

filewriter.Write("")
For Each currentField In currentRow
filewriter.Write("" + currentField + "")
Next
filewriter.Write("")
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
End Try
End While
filewriter.Write(sFooter)
filewriter.Close()
lblexcelfile.Text = Strpath
Catch ex As Exception
MsgBox(ex.Message)
End Try
Windows.Forms.Cursor.Current = Cursors.WaitCursor
MsgBox("Done")
End Sub




''' 
    ''' to get the CSV file path
    ''' 

    ''' 
    ''' 
    ''' 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            lblImportFileName.Text = OpenFileDialog1.FileName
        End If

    End Sub

Attachments

Sample CSV file (20522-2745-test.csv)

Download the Complete code for the CSV to excel convertion (20522-2741-To create a Excel sheet with Formating from CSV File wihtout Excel object model.doc)

Responses
No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks

Popular Tags

What are tags ?

Search Tags

To create a Excel sheet with Formating from CSV File wihtout Excel object model. .

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: Full Code to create a Excel report wihtout using Excel object model -Example
	Previous Resource: C# Language
	Return to Discussion Resource Index
	Post New Resource
	Category: .NET Framework

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

conference call

To create a Excel sheet with Formating from CSV File wihtout Excel object model.

Attachments

Responses

Post Feedback