Home > DeveloperSection > Blogs > Write Data in EXCEL, CSV And XML File Using C#

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


C# C# 
Ratings:
2 Comment(s)
 14863  View(s)
Rate this:

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

 


Nice

By Ely Sanders on   3 years ago

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


Nice Article

By Rohit Maheshwari on   one year ago
Thanx Chris for nice article.

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

Don't want to miss updates? Please click the below button!

Follow MindStick