Search code examples
c#exceloledb

Writing into Excel through C#

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.

Solution

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