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();

 

  Modified On Sep-18-2014 01:24:00 PM
  1. Great, 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 = new DataSet();
    da.Fill(ds, "products");
    DataTable dt = ds.Tables["products"];

    // Create new ExcelFile.
    var workbook = new ExcelFile();

    // 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, new InsertDataTableOptions("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);

  1. Thanx Chris for nice article.

    But could you please tell me, how to create Excel if Microsoft Office not installed.

Leave Comment