Writing into Excel through C#

Question

This can be a very naive question. I want to write into into an excel file and each time the insert of data should happen on a new line. Here is what i have to do in detail:

1. Create an Excel Dynamically and name it on the basis of the current date.
2. Add the headers like "Actual", "Expected " and Outcome.
3. insert date in the above columns .

I have a small code which verifies certain fields , so I wan to write into excel the fields which deviate from the expected behavior. So each run whenever my code finds an error it should write into that excel.

Answer 1

It is possible to write to an excel file without using any third-party library by using the Excel Spreadsheet XML format. All you need is using a XmlTextWriter. Here is an example (the stream where to write the excel is assumed to be provided):

XmlTextWriter w = new XmlTextWriter(stream, null); // Creates the XML writer from pre-declared stream.

//First Write the Excel Header
w.WriteStartDocument();
w.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'");

w.WriteStartElement("Workbook");
w.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:spreadsheet");
w.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
w.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
w.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
w.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

w.WriteStartElement("DocumentProperties");
w.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:office");
w.WriteEndElement();

// Creates the workbook
w.WriteStartElement("ExcelWorkbook");
w.WriteAttributeString("xmlns", "urn:schemas-microsoft-com:office:excel");
w.WriteEndElement();

// Creates the worksheet
w.WriteStartElement("Worksheet");
w.WriteAttributeString("ss", "Name", null, "Sheet1");

// Creates the table
w.WriteStartElement("Table");

// Creates a row.
w.WriteStartElement("Row");

// Creates a cell with "SomeData" written in it.
w.WriteStartElement("Cell");
w.WriteStartElement("Data");
w.WriteAttributeString("ss", "Type", null, "String");
w.WriteString("SomeData");
w.WriteEndElement();
w.WriteEndElement();

w.WriteEndElement(); // Closes the row.

w.WriteEndElement();
w.WriteEndElement();
w.WriteEndElement();
w.WriteEndDocument();
w.Flush();
w.Close();

Answer 2

You would want to use Closed XML which a wrapper around Open XML SDK. Check out their examples.

If you do not want to rely on a 3rd party library you can use Open XML SDK directly.

Answer 3

You can use the following ways to write into excel file:

1. Use COM instance of Excel application
2. Use a third party component to write excel

I use syncfusion xslIo component to read and write excel files