A Sample Data Table Is Created for the Demo Purpose

To create Excel or Word document file without Excel object model or word object model in vb.net

To call the function with first parameter as filename and second argument is path of the file and the third argument is Data table. Append all the values in a String builder with Tab for the new cell and VBCrlf for the new line and write the file using stream writer.

A sample Data table is created for the Demo purpose

Add the below code in form load for testing the Sample code

[CODE]

'Added a new table with column and rows for demo purpose

Dim Dt AsNew System.Data.DataTable

Dt.Columns.Add("Eno")

Dt.Columns.Add("EmpName")

Dt.Columns.Add("Salary")

Dim Dr As DataRow

Dr = Dt.NewRow()

Dr("Eno") = "1000000"

Dr("EmpName") = "JK"

Dr("Salary") = "190809"

Dt.Rows.Add(Dr)

Dr = Dt.NewRow()

Dr("Eno") = "1239834"

Dr("EmpName") = "Kumar"

Dr("Salary") = "234234444"

Dt.Rows.Add(Dr)

Dr = Dt.NewRow()

Dr("Eno") = "23476234"

Dr("EmpName") = "Rajes"

Dr("Salary") = "12312333"

Dt.Rows.Add(Dr)

[/CODE]

[CODE]

'To call the function to create the excel report with filename , path and A datatable to create the excel file

ExportToExcelfile("text", "C:\", Dt)

[/CODE]

To append the values to a string builder with Vbtab for new cell and vbcrlf for new line pass the values to create excel sheet.

[CODE]

'To create the values with VBTab for New cell and VBcrlf for New line and append it in a string builder

'Pass the values to the Excel report to generate it

PublicSubExportToExcelfile(ByVal FileName AsString, ByVal SavePath AsString, ByVal objDataReader As System.Data.DataTable)

Dim sb AsNew System.Text.StringBuilder

Try

Dim intColumn, intColumnValue AsInteger

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)

EndIf

Next

sb.Append(vbCrLf)

ForEach 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)

EndIf

Next

sb.Append(vbCrLf)

Next

SaveExcelfile(SavePath & "\" & FileName & ".xls", sb)

Catch ex As Exception

Throw

Finally

objDataReader = Nothing

sb = Nothing

EndTry

EndSub

[/CODE]

To create the Excel file using Stream writer

[CODE]

'Using the stream writer we can write the file with xls as extension or doc etc.,

'and finally disposing the objects

PrivateSub SaveExcelfile(ByVal fpath AsString, ByVal sb As System.Text.StringBuilder)

Dim fsFile AsNew FileStream(fpath, FileMode.Create, FileAccess.Write)

Dim strWriter AsNew StreamWriter(fsFile)

Try

With strWriter

.BaseStream.Seek(0, SeekOrigin.End)

.WriteLine(sb)

.Close()

EndWith

Catch e As Exception

Throw

Finally

sb = Nothing

strWriter = Nothing

fsFile = Nothing

EndTry

EndSub

[/CODE]