Write Data in EXCEL, CSV and XML File Using C#


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

 

  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