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