I have created and excel file using xml code and sending it in email but while i open the open when downloaded from email it shows "The file format and extension of 'Repairorder.xls' don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?"
below is my code
var attachments = new List<KeyValuePair<string, byte[]>>();
private static string ExcelHeader()
{
// Excel header
var sb = new StringBuilder();
sb.Append(" <?xml version=\"1.0\"?>");
sb.Append("<?mso-application progid=\"Excel.Sheet\"?>");
sb.Append("<Workbook ");
sb.Append(" xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
sb.Append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
sb.Append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sb.Append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
return sb.ToString();
}
public static string GetStyles()
{
return @" <Styles>
<Style ss:ID=""Default"" ss:Name=""Normal"">
<Alignment ss:Vertical=""Bottom""/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=""s27"" ss:Name=""Hyperlink"">
<Font ss:Color=""#0000FF"" ss:Underline=""Single""/>
</Style>
<Style ss:ID=""s24"">
<Font x:Family=""Swiss"" ss:Bold=""1""/>
</Style>
<Style ss:ID=""s25"">
<Font x:Family=""Swiss"" ss:Italic=""1""/>
</Style>
<Style ss:ID=""s26"">
<Alignment ss:Horizontal=""Center"" ss:Vertical=""Bottom""/>
</Style>
</Styles>";
}
public static KeyValuePair<string, byte[]> CreateExcelSingleSheet(List<RepairOrderStatusObject> repairOrderStatusObjects, string componentName, int userId)
{
string xlsFileName = string.Format("RepairOrderStatus_dated_{0}_{1}.xls", componentName, DateTime.Now.ToString("MMMM_dd_yyyy")); // Excel file name.
var strExcelXml = new StringBuilder();
strExcelXml.Append(ExcelHeader());
strExcelXml.Append(GetStyles());
strExcelXml.Append(ConvertHTMLToExcelXML(ExcelWorkSheet(repairOrderStatusObjects, componentName, userId)));
strExcelXml.Append("</Workbook>\n");
var ms = new MemoryStream();
var writer = new StreamWriter(ms, Encoding.Unicode);
writer.Write(strExcelXml.ToString());
writer.Flush();
var buffer = new byte[ms.Length];
ms.Position = 0;
ms.Read(buffer, 0, buffer.Length);
writer.Close();
ms.Close();
return new KeyValuePair<string, byte[]>(xlsFileName, buffer);
}
private static string ExcelWorkSheet(List<RepairOrderStatusObject> repairOrderStatusObjects, string name, int userId)
{
var strExcelXml = new StringBuilder();
strExcelXml.Append("<Worksheet ss:Name=\"Repair Order Status List\"><Table><tr>");
List<ExportToExcelSetup> excelSetupList = ExportToExcelSetup.GetRepairOrderStatusUserFields(userId, repairOrderStatusObjects[0].ComponentType);
var onlySelected = excelSetupList.Where(x => x.IsSelected).ToList();
foreach (ExportToExcelSetup excelSetup in onlySelected)
{
strExcelXml.AppendFormat("<td>{0}</td>", excelSetup.Name);
}
strExcelXml.Append("</tr>");
//var sortingColumn = ExportToExcelSetup.GetRepairOrderStatusUserFieldSortingColum(userId, FokkerComponentType.Schiphol);
//var listsorted = repairOrderStatusObjects.OrderBy(q=> q.GetType().GetProperty(sortingColumn.Replace(" ","").Replace(".", "")).GetValue(q, null));
//string dbSortingColumn = ExportToExcelSetup.GetRepairOrderStatusUserFieldSortingColum(MyFokkerFleetPrincipal.CurrentPrincipal.UserID, FokkerComponentType.Schiphol);
//var sortingColumn = FindSortColumn(dbSortingColumn);
//var listsorted = repairOrderStatusObjects.OrderBy(q=> q.GetType().GetProperty(sortingColumn).GetValue(q, null));
foreach (RepairOrderStatusObject repairOrderStatusObject in repairOrderStatusObjects)
{
strExcelXml.Append("<tr>");
foreach (ExportToExcelSetup excelSetup in onlySelected)
{
object value;
try
{
if (((ExportToExcelFieldsEnum) excelSetup.ID) == ExportToExcelFieldsEnum.Messages)
{
value = repairOrderStatusObject.ComponentType == FokkerComponentType.Schiphol
? SchipholRepairOrderStatus.ShopFindings(repairOrderStatusObject.ID)
: repairOrderStatusObject.ShopFindingsAll;
value = Regex.Replace(value.ToString(), "<.*?>", " "); //Remove HTML-tags from the value
}
else
{
value = repairOrderStatusObject.GetType().GetProperty(
((ExportToExcelFieldsEnum)excelSetup.ID).ToString()).GetValue(repairOrderStatusObject, null);
}
}
catch (Exception)
{
value = "Unknown";
}
strExcelXml.AppendFormat("<td>{0}</td>", value == null?"": System.Web.HttpUtility.HtmlEncode(value.ToString()));
}
strExcelXml.Append("</tr>");
}
strExcelXml.Append("</Table></Worksheet>");
return strExcelXml.ToString();
}
public static string ConvertHTMLToExcelXML(string strHtml)
{
// Just to replace TR with Row
strHtml = strHtml.Replace("<tr>", "<Row ss:AutoFitHeight=\"1\">\n");
strHtml = strHtml.Replace("</tr>", "</Row>\n");
//replace the cell tags
strHtml = strHtml.Replace("<td>", "<Cell><Data ss:Type=\"String\">");
strHtml = strHtml.Replace("</td>", "</Data></Cell>\n");
return strHtml;
}
private static Attachment ConvertXlsToAttachment(string fileName, byte[] fileContents)
{
string directory = Settings.TempPath;
if (!Directory.Exists(directory)) Directory.CreateDirectory(directory);
string xlsFilePath = Path.Combine(directory, fileName);
File.WriteAllBytes(xlsFilePath, fileContents);
var fs = new FileStream(xlsFilePath, FileMode.Open, FileAccess.Read, FileShare.Read);
// Make a ContentType indicating that the file data
// that is attached is of a type and is named.
var ct = new ContentType
{
MediaType = "application/vnd.ms-excel",
Name = Path.GetFileName(xlsFilePath)
};
// Attachment.
return new Attachment(fs, ct);
}
private static Attachment ConvertXlsToAttachment(string fileName, byte[] fileContents)
{
string directory = Settings.TempPath;
if (!Directory.Exists(directory)) Directory.CreateDirectory(directory);
string xlsFilePath = Path.Combine(directory, fileName);
File.WriteAllBytes(xlsFilePath, fileContents);
var fs = new FileStream(xlsFilePath, FileMode.Open, FileAccess.Read, FileShare.Read);
// Make a ContentType indicating that the file data
// that is attached is of a type and is named.
var ct = new ContentType
{
MediaType = "application/vnd.ms-excel",
Name = Path.GetFileName(xlsFilePath)
};
// Attachment.
return new Attachment(fs, ct);
}
Any help?
