Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have created one windows application using VB.net. i wanna export some result in to excel.
How can i export data in to excel from a datatable in VB.net? please give me the code.
Posted
Updated 11-Jun-13 21:45pm
v4

It's not ideal but the easy way to do it is to create a temporary CSV file and then shell out to Excel using that.

If you want something 'better' - i.e. that creates excel files a third-party library would be the way to go, although there is also this: Using C# to Create an Excel Document[^]
 
Share this answer
 
Comments
Am Gayathri 12-Jun-13 3:15am    
Thanks but i want the code in VB.
 
Share this answer
 
Comments
Am Gayathri 12-Jun-13 3:16am    
Thanks,
But the code looks complex. Do you have any idea how to export data from datatable to excel ?
There are multiple ways to export excel

1. ClosedXML

2. Write a extension method to export excel

CSS
using System;
using System.Collections.Generic;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.OleDb;

DataTable dt;
// fill table data in dt here
...

// export DataTable to excel
// save excel file without ever making it visible if filepath is given
// don't save excel file, just make it visible if no filepath is given
dt.ExportToExcel(ExcelFilePath);


public static class My_DataTable_Extensions
{

// Export DataTable into an excel file with field names in the header line
// - Save excel file without ever making it visible if filepath is given
// - Don't save excel file, just make it visible if no filepath is given
public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
{
try
{
if (Tbl == null || Tbl.Columns.Count == 0)
throw new Exception("ExportToExcel: Null or empty input table!\n");

// load excel, and create a new workbook
Excel.Application excelApp = new Excel.Application();
excelApp.Workbooks.Add();

// single worksheet
Excel._Worksheet workSheet = excelApp.ActiveSheet;

// column headings
for (int i = 0; i < Tbl.Columns.Count; i++)
{
workSheet.Cells[1, (i+1)] = Tbl.Columns[i].ColumnName;
}

// rows
for (int i = 0; i < Tbl.Rows.Count; i++)
{
// to do: format datetime values before printing
for (int j = 0; j < Tbl.Columns.Count; j++)
{
workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
}
}

// check fielpath
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
workSheet.SaveAs(ExcelFilePath);
excelApp.Quit();
MessageBox.Show("Excel file saved!");
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch(Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
}
}
 
Share this answer
 
Comments
Am Gayathri 12-Jun-13 3:57am    
My application is developed in VB. I need VB code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900