In this blog I am going to explain how to write data in EXCEL, CSV, and XML file by using C#.
· Import the Microsoft.Office.Interop.Excel.dll and inherit it in your application. For e.g.
using Microsoft.Office.Interop.Excel or using Excel=Microsoft.Office.Interop.Excel
Inherit System.IO namespace. For e.g. using System.IO
Following code demonstrate how to write Data in XML file from dataset:
DataSet ds = new DataSet();
da.Fill(ds, "products");
DataTable dt = ds.Tables["products"];
dt.WriteXml(@"d:\rohit\File1.xml");
Following code demonstrate how to write Data in EXCEL file from
dataset:
DataSet ds = new DataSet();
da.Fill(ds, "products");
DataTable dt = ds.Tables["products"];
object missing = Type.Missing;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
ws.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
ws.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
wb.SaveAs(@"d:\rohit\excelData.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue,
misValue, misValue, misValue, misValue);
wb.Close(missing, missing, missing);
excel.Quit();
Following code demonstrate how to write Data in CSV file from dataset:
DataSet ds = new DataSet();
da.Fill(ds, "products");
DataTable dt = ds.Tables["products"];
StreamWriter sw = new StreamWriter(@"d:\rohit\csvData.csv", false);
// First we will write the headers.
int iColCount = dt.Columns.Count;
for (int i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
// Now write all the rows.
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
sw.Write(dr[i].ToString());
if (i < iColCount - 1)
sw.Write(",");
}
sw.Write(sw.NewLine);
}
sw.Close();
Rohit Maheshwari
03-Aug-2015Ely Sanders
21-Aug-2013Great, thank you for the article!
Also you can try this C# Excel component which unifies writing Excel, CSV or XML files in .NET and does not require MS Excel.
Here is a sample code you can try to import DataTable to Excel in C#:
DataSet ds = newDataSet();
da.Fill(ds, "products");
DataTable dt = ds.Tables["products"];
// Create new ExcelFile.
var workbook = newExcelFile();
// Add new worksheet.
var worksheet = workbook.Worksheets.Add(dt.TableName);
// Insert the data from DataTable to the worksheet starting at cell "A1".
worksheet.InsertDataTable(dt, newInsertDataTableOptions("A1") { ColumnHeaders = true });
// Save as XLS, XML and CSV files.
workbook.Save(@"d:\rohit\Data.xls", SaveOptions.XlsDefault);
workbook.Save(@"d:\rohit\File1.xml", SaveOptions.HtmlDefault);
workbook.Save(@"d:\rohit\csvData.csv", SaveOptions.CsvDefault);