Generating and Exporting an Excel File in ASP.NET -Using Excel’s XML Scheme


I am going to explain how to Generate and Export an Excel File in ASP.NET Using Excel’s XML Scheme. Learn Generating and Exporting an Excel File in ASP.NET -Using Excel’s XML Scheme

About Generating and Exporting an Excel File in ASP.NET -Using Excel’s XML Scheme


An Excel file can be generated and exported using the following alternatives:

Using Excel COM Object.
Using HTMLTextWriter object of ASP.NET.
Using Excel’s XML Scheme (XML Spreadsheet).

I am explaining here Using Excel’s XML Scheme (XML Spreadsheet).


Pros:

1. Supports formulas, styles and formatting.
2. Supports different workbooks.
3. Exported Excel files can be updated and imported in SQL Server and ASP.NET.

Cons:

1. Not supported in previous versions of Excel ( pre- 2000).
2. While saving the file, it gives this message1(See Attachment).


Security Warning in Excel 2007
· When an Excel file is generated using "application/vnd.ms-excel" as content type, it gives this warning1(See Attachment) while opening it in Excel 2007.

· This warning message is a new security feature in Excel 2007 called Extension Hardening, which ensures that the file content being opened matches the extension type specified in the shell command that is attempting to open the file.

· This warning message can be disabled using registry key. It means that whichever desktop wants to get rid of this warning, needs the following registry setting:



Key: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security

Value: (DWORD)"ExtensionHardening" = [0 = Disable check; 1 = Enable check and prompt; 2 = Enable check, no prompt deny open]

Default setting if value not present is 1 (enable and prompt).



public static void ToExcel(DataSet dsInput, string filename, HttpResponse response)

{

string excelXml = GetExcelXml(dsInput, filename);

response.Clear();

response.AppendHeader("Cache-Control", "cache, must-revalidate");

response.AppendHeader("Pragma", "public");

// If we set application/excel as content-type, then while opening the file in excel 2007, it gives security warning.

response.AppendHeader("Content-Type", "text/xml");

response.AppendHeader("Content-disposition", "attachment; filename=" + System.IO.Path.GetFileName(filename));

response.Write(excelXml);

response.Flush();

response.End();

}

const int rowLimit = 65000;

public static string getWorkbookTemplate()

{
StringBuilder sb = new StringBuilder(818);

sb.AppendFormat(@"<?xml version=""1.0""?>{0}", Environment.NewLine);

sb.AppendFormat(@"<?mso-application progid=""Excel.Sheet""?>{0}", Environment.NewLine);

sb.AppendFormat(@"<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);

sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine);

sb.AppendFormat(@" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine);

sb.AppendFormat(@" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);

sb.AppendFormat(@" xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}", Environment.NewLine);

sb.AppendFormat(@" <Styles>{0}", Environment.NewLine);

sb.AppendFormat(@" <Style ss:ID=""Default"" ss:Name=""Normal"">{0}", Environment.NewLine);

sb.AppendFormat(@" <Alignment ss:Vertical=""Bottom""/>{0}", Environment.NewLine);

sb.AppendFormat(@" <Borders/>{0}", Environment.NewLine);

sb.AppendFormat(@" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""/>{0}", Environment.NewLine);

sb.AppendFormat(@" <Interior/>{0}", Environment.NewLine);

sb.AppendFormat(@" <NumberFormat/>{0}", Environment.NewLine);

sb.AppendFormat(@" <Protection/>{0}", Environment.NewLine);

sb.AppendFormat(@" </Style>{0}", Environment.NewLine);

sb.AppendFormat(@" <Style ss:ID=""s62"">{0}", Environment.NewLine);

sb.AppendFormat(@" <Font ss:FontName=""Calibri"" x:Family=""Swiss"" ss:Size=""11"" ss:Color=""#000000""{0}", Environment.NewLine);

sb.AppendFormat(@" ss:Bold=""1""/>{0}", Environment.NewLine);

sb.AppendFormat(@" </Style>{0}", Environment.NewLine);

sb.AppendFormat(@" <Style ss:ID=""s63"">{0}", Environment.NewLine);

sb.AppendFormat(@" <NumberFormat ss:Format=""Short Date""/>{0}", Environment.NewLine);

sb.AppendFormat(@" </Style>{0}", Environment.NewLine);

sb.AppendFormat(@" <Style ss:ID=""s22"">{0}", Environment.NewLine);

sb.AppendFormat(@" <Interior ss:Color=""#FF0000"" ss:Pattern=""Solid""/>{0}", Environment.NewLine);

sb.AppendFormat(@" </Style>{0}", Environment.NewLine);

sb.AppendFormat(@" </Styles>{0}", Environment.NewLine);

sb.Append(@"{0}\r\n</Workbook>");

return sb.ToString();

}

private static string replaceXmlChar(string input)

{
input = input.Replace("&", "&");

input = input.Replace("<", "<");

input = input.Replace(">", ">");

input = input.Replace("\"", """);

input = input.Replace("'", "'");

return input;

}



private static string getCell(Type type, object cellData)

{
object data = (cellData is DBNull) ? "" : cellData;

if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", data);

if (type.Name.Contains("Date") && data.ToString() != string.Empty)

{

return string.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));

}

return string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(data.ToString()));

}

private static string getErrorCel(Type type, object cellData)

{

object data = (cellData is DBNull) ? "" : cellData;

if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", data);

if (type.Name.Contains("Date") && data.ToString() != string.Empty)

{

return string.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));

}

return string.Format("<Cell ss:StyleID=\"s22\"><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(data.ToString()));

}

private static string getWorksheets(DataSet source)

{
StringWriter sw = new StringWriter();

if (source == null || source.Tables.Count == 0)

{

sw.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");

return sw.ToString();

}

foreach (DataTable dt in source.Tables)

{

if (dt.Rows.Count == 0)

sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) + "\">\r\n<Table>\r\n<Row><Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");

else

{
//write each row data

int sheetCount = 0;

for (int i = 0; i < dt.Rows.Count; i++)

{

if ((i % rowLimit) == 0)

{

//add close tags for previous sheet of the same data table

if ((i / rowLimit) > sheetCount)

{

sw.Write("\r\n </Table> \r\n</Worksheet>");

sheetCount = (i / rowLimit);

}

sw.Write("\r\n<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) +(((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\">\r\n<Table>");
foreach (DataColumn dc in dt.Columns)

sw.Write("<Column ss:AutoFitWidth=\"1\" ss:Width=\"90\"/>");



//write column name row

sw.Write("\r\n<Row>");

foreach (DataColumn dc in dt.Columns)

sw.Write(string.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(dc.Caption)));

sw.Write("</Row>");

}



sw.Write("\r\n<Row>");

foreach (DataColumn dc in dt.Columns)

{

if(dt.Rows[i].GetColumnError(dc.ColumnName).Equals("MUPException"))

sw.Write(getErrorCel(dc.DataType, dt.Rows[i][dc.ColumnName]));

else sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));

}

sw.Write("</Row>");



}

sw.Write("\r\n</Table>\r\n</Worksheet>");

}

}



return sw.ToString();

}



public static string GetExcelXml(DataTable dtInput, string filename)

{

string excelTemplate = getWorkbookTemplate();

DataSet ds = new DataSet();

ds.Tables.Add(dtInput.Copy());

string worksheets = getWorksheets(ds);

string excelXml = string.Format(excelTemplate, worksheets);

return excelXml;

}



public static string GetExcelXml(DataSet dsInput, string filename)

{

string excelTemplate = getWorkbookTemplate();

string worksheets = getWorksheets(dsInput);

string excelXml = string.Format(excelTemplate, worksheets);

return excelXml;

}


Attachments

Comments

Author: Muhammad Kamal20 Oct 2011 Member Level: Bronze   Points : 0

how to handle warning popup like picture Message1 ?

Author: nitt08 Nov 2012 Member Level: Bronze   Points : 0

thak

Author: Dharmendra Kumar15 Mar 2013 Member Level: Gold   Points : 3

Hi Muhammad Kamal ,
If you dont want to show the popup message to user and still want to export the file on the client machine , then its not possible for web application. In this case what you cas do is, save the file on server and then redirect user to that file, Then also it will ask for you to open / save and cancel the file. Its for security purpose because files from internet can not be directly saved to clients machine without any warnig/ confirmation purpose.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: