Creating Excel without MS-Office Installation

Create excel without ms office installation

In this article we will be discussing about 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.

Scenario:

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

Solution:

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

Code snippet as follows:

Read some SharePoint Online tutorial:

// 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;
}

Hope this helps !!!!

Similar SharePoint 2013 Tutorials


About Krishna Vandanapu

I am Krishna.Vandanapu a SharePoint architect working in IT from last 12 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 →

Leave a Reply