Creating Excel without MS-Office Installation

Create excel without ms office installation
SharePoint deveopment training course

This tutorial explains, how to create an excel file with multiple sheets without installing MS-Office. We all know that as per best practice from Microsoft we should not install MS-Office nor any development tools in SharePoint Application Server.

User wants to export the list data into an excel file as part of a WebPart. Since we don’t have office installed on server we can’t create excel through the server-side object model.

Create an Excel without MS-Office Installation

We use “XmlTextWriter” class to achieve this, with this class we can create a raw file and save it as .xml file.

// Create XMLWriter

XmlTextWriter xtwWriter = new XmlTextWriter(strFileName, Encoding.UTF8);
//Format the output file for reading easier
xtwWriter.Formatting = Formatting.Indented;
// <?xml version="1.0"?>
xtwWriter.WriteStartDocument();
// <?mso-application progid="Excel.Sheet"?>
xtwWriter.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
// <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >"
xtwWriter.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");
//Write definition of namespace
xtwWriter.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
xtwWriter.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
xtwWriter.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
xtwWriter.WriteAttributeString("xmlns", "html", null, "http://www.w3.org/TR/REC-html40");

// <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
xtwWriter.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");

// Write document properties
xtwWriter.WriteElementString("Author", Environment.UserName);
xtwWriter.WriteElementString("LastAuthor", Environment.UserName);
xtwWriter.WriteElementString("Created", DateTime.Now.ToString("u") + "Z");
xtwWriter.WriteElementString("Company", "Unknown");
xtwWriter.WriteElementString("Version", "11.8122");

// </DocumentProperties>
xtwWriter.WriteEndElement();

// <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
xtwWriter.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

// Write settings of workbook
xtwWriter.WriteElementString("WindowHeight", "13170");
xtwWriter.WriteElementString("WindowWidth", "17580");
xtwWriter.WriteElementString("WindowTopX", "120");
xtwWriter.WriteElementString("WindowTopY", "60");
xtwWriter.WriteElementString("ProtectStructure", "False");
xtwWriter.WriteElementString("ProtectWindows", "False");
// </ExcelWorkbook>
xtwWriter.WriteEndElement();

// <Styles>
xtwWriter.WriteStartElement("Styles");

// <Style ss:ID="Default" ss:Name="Normal">
xtwWriter.WriteStartElement("Style");
xtwWriter.WriteAttributeString("ss", "ID", null, "Default");
xtwWriter.WriteAttributeString("ss", "Name", null, "Normal");

// <Alignment ss:Vertical="Bottom"/>
xtwWriter.WriteStartElement("Alignment");
xtwWriter.WriteAttributeString("ss", "Vertical", null, "Bottom");
xtwWriter.WriteEndElement();

// Write null on the other properties
xtwWriter.WriteElementString("Borders", null);
xtwWriter.WriteElementString("Font", null);
xtwWriter.WriteElementString("Interior", null);
xtwWriter.WriteElementString("NumberFormat", null);
xtwWriter.WriteElementString("Protection", null);

// </Style>
xtwWriter.WriteEndElement();
// </Styles>
xtwWriter.WriteEndElement();

#region Creating Sheet1 Data
DataTable dtSourceSheet1 = new DataTable("Application Details");
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 1"));
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 2"));
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 3"));
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 4"));
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 5"));
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 6"));
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 7"));
dtSourceSheet1.Columns.Add(new DataColumn("Sheet 1 – Column 8"));
DataRow myDataRow;
for (int i = 1; i < 10; i++)
{
myDataRow = dtSourceSheet1.NewRow();
myDataRow["Sheet 1 – Column 1"] = (i > 1 ? ("Column-1 :: Value " + "_" + (i-1).ToString()) : "Column 1");
myDataRow["Sheet 1 – Column 2"] = (i > 1 ? ("Column-2 :: Value " + "_" + (i-1).ToString()) : "Column 2");
myDataRow["Sheet 1 – Column 3"] = (i > 1 ? ("Column-3 :: Value " + "_" + (i-1).ToString()) : "Column 3");
myDataRow["Sheet 1 – Column 4"] = (i > 1 ? ("Column-4 :: Value " + "_" + (i-1).ToString()) : "Column 4");
myDataRow["Sheet 1 – Column 5"] = (i > 1 ? ("Column-5 :: Value " + "_" + (i-1).ToString()) : "Column 5");
myDataRow["Sheet 1 – Column 6"] = (i > 1 ? ("Column-6 :: Value " + "_" + (i-1).ToString()) : "Column 6");
myDataRow["Sheet 1 – Column 7"] = (i > 1 ? ("Column-7 :: Value " + "_" + (i-1).ToString()) : "Column 7");
myDataRow["Sheet 1 – Column 8"] = (i > 1 ? ("Column-8 :: Value " + "_" + (i-1).ToString()) : "Column 8");
dtSourceSheet1.Rows.Add(myDataRow);
}
// With this line of code we create the excel sheet
xtwWriter = Createworksheet(xtwWriter, dtSourceSheet1, "Application Details");
#endregion

#region Creating Sheet2 Data
DataTable dtSourceSheet2 = new DataTable("Loan Details");
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 1"));
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 2"));
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 3"));
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 4"));
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 5"));
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 6"));
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 7"));
dtSourceSheet2.Columns.Add(new DataColumn("Sheet 2 – Column 8"));
for (int i = 1; i < 10; i++)
{
myDataRow = dtSourceSheet2.NewRow();
myDataRow["Sheet 2 – Column 1"] = (i > 1 ? ("Column-1 :: Value " + "_" + (i – 1).ToString()) : "Column 1");
myDataRow["Sheet 2 – Column 2"] = (i > 1 ? ("Column-2 :: Value " + "_" + (i – 1).ToString()) : "Column 2");
myDataRow["Sheet 2 – Column 3"] = (i > 1 ? ("Column-3 :: Value " + "_" + (i – 1).ToString()) : "Column 3");
myDataRow["Sheet 2 – Column 4"] = (i > 1 ? ("Column-4 :: Value " + "_" + (i – 1).ToString()) : "Column 4");
myDataRow["Sheet 2 – Column 5"] = (i > 1 ? ("Column-5 :: Value " + "_" + (i – 1).ToString()) : "Column 5");
myDataRow["Sheet 2 – Column 6"] = (i > 1 ? ("Column-6 :: Value " + "_" + (i – 1).ToString()) : "Column 6");
myDataRow["Sheet 2 – Column 7"] = (i > 1 ? ("Column-7 :: Value " + "_" + (i – 1).ToString()) : "Column 7");
myDataRow["Sheet 2 – Column 8"] = (i > 1 ? ("Column-8 :: Value " + "_" + (i – 1).ToString()) : "Column 8");
dtSourceSheet2.Rows.Add(myDataRow);
}
xtwWriter = Createworksheet(xtwWriter, dtSourceSheet2, "Loan Details");
#endregion

// </Workbook>
xtwWriter.WriteEndElement();

// Write file on hard disk
xtwWriter.Flush();
xtwWriter.Close();

// This method is to create excel sheet

private static XmlTextWriter Createworksheet(XmlTextWriter xtwWriter, DataTable dtSource,string strSheetName)
{
try
{
// <Worksheet ss:Name="xxx">
xtwWriter.WriteStartElement("Worksheet");
xtwWriter.WriteAttributeString("ss", "Name", null, strSheetName);
// <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">
xtwWriter.WriteStartElement("Table");
xtwWriter.WriteAttributeString("ss", "ExpandedColumnCount", null, dtSource.Columns.Count.ToString());
xtwWriter.WriteAttributeString("ss", "ExpandedRowCount", null, dtSource.Rows.Count.ToString());
xtwWriter.WriteAttributeString("x", "FullColumns", null, "1");
xtwWriter.WriteAttributeString("x", "FullRows", null, "1");
xtwWriter.WriteAttributeString("ss", "DefaultColumnWidth", null, "60");

// Run through all rows of data source
foreach (DataRow row in dtSource.Rows)
{
// <Row>
xtwWriter.WriteStartElement("Row");
// Run through all cell of current rows
foreach (object cellValue in row.ItemArray)

{
// <Cell>
xtwWriter.WriteStartElement("Cell");
// <Data ss:Type="String">xxx</Data>
xtwWriter.WriteStartElement("Data");
xtwWriter.WriteAttributeString("ss", "Type", null, "String");
try
{
// Write content of cell
xtwWriter.WriteValue(cellValue);
}
catch { }
// </Data>
xtwWriter.WriteEndElement();
// </Cell>
xtwWriter.WriteEndElement();
}
// </Row>
xtwWriter.WriteEndElement();
}
// </Table>
xtwWriter.WriteEndElement();

// <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
xtwWriter.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel");

// Write settings of page
xtwWriter.WriteStartElement("PageSetup");
xtwWriter.WriteStartElement("Header");
xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845");
xtwWriter.WriteEndElement();
xtwWriter.WriteStartElement("Footer");
xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845");
xtwWriter.WriteEndElement();
xtwWriter.WriteStartElement("PageMargins");
xtwWriter.WriteAttributeString("x", "Bottom", null, "0.984251969");
xtwWriter.WriteAttributeString("x", "Left", null, "0.78740157499999996");
xtwWriter.WriteAttributeString("x", "Right", null, "0.78740157499999996");
xtwWriter.WriteAttributeString("x", "Top", null, "0.984251969");
xtwWriter.WriteEndElement();
xtwWriter.WriteEndElement();

// <Selected/>
xtwWriter.WriteElementString("Selected", null);

// <Panes>
xtwWriter.WriteStartElement("Panes");

// <Pane>
xtwWriter.WriteStartElement("Pane");

// Write settings of active field
xtwWriter.WriteElementString("Number", "1");
xtwWriter.WriteElementString("ActiveRow", "1");
xtwWriter.WriteElementString("ActiveCol", "1");

// </Pane>
xtwWriter.WriteEndElement();

// </Panes>
xtwWriter.WriteEndElement();

// <ProtectObjects>False</ProtectObjects>
xtwWriter.WriteElementString("ProtectObjects", "False");

// <ProtectScenarios>False</ProtectScenarios>
xtwWriter.WriteElementString("ProtectScenarios", "False");

// </WorksheetOptions>
xtwWriter.WriteEndElement();

// </Worksheet>
xtwWriter.WriteEndElement();
}
catch(Exception ex)
{
Console.WriteLine("Error::" + ex.Message);
}
return xtwWriter;
}

You may like following tutorials:

This tutorial we learned how to create an excel file with multiple sheets without installing MS-Office.

Check out Best Alternative to InfoPath -> Try Now

free sharepoint training

SharePoint Online FREE Training

JOIN a FREE SharePoint Video Course (3 Part Video Series)

envelope
envelope

About Krishna Vandanapu

I am Krishna.Vandanapu a SharePoint architect working in IT from last 13+ years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019

View all posts by Krishna Vandanapu →