I have an older legacy web application. We've recently moved it from an old Windows 2008 r2 server to a new Windows 2019 server and a few issues have shown up. I've worked through most of them, however there's one that still causing me some problems. This web application is needing to export data in a DataTable to an Excel sheet for the user to view. However when it exports the Excel file they receive this message
The file format and extension of 'Report.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?
If they click "yes" then it gives then another message
The file is corrupt and cannot be opened.
There weren't any problems with the older 2008 r2 server.
I've tried changing the Response.ContentType = "application/vnd.xls"; to Response.ContentType = "application/vnd.ms-excel"; as well as Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; and i still receive the same messages. I need to be able to provide an Excel sheet, and I can't pay for an Excel package.
Here's the code that generates the Excel file:
public void Create_Spreadsheet(string sheetField, string[] arrExcelColumns, DataTable table)
{
using (MemoryStream stream = new MemoryStream())
{
string pathToXsl = Server.MapPath(relToXsl);
string reportFileName = "Report.xls";
string fieldName = "Field";
string worksheetName = "Sheet";
string prevWorksheet = "";
DataView dv = new DataView();
DataTable dt = new DataTable();
dt = table;
// sort data for displaying with worksheets
dv = dt.DefaultView;
if (dv.Table.Columns.Contains(sheetField)) dv.Sort = sheetField;
DataTable dtSorted = dv.ToTable();
// get columns array
ArrayList arrColumns = new ArrayList();
for (int k = 0; k < dtSorted.Columns.Count; ++k)
{
if (Array.IndexOf(arrExcelColumns, dtSorted.Columns[k].ColumnName) >= 0)
arrColumns.Add(dtSorted.Columns[k].ColumnName);
}
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
settings.Encoding = System.Text.Encoding.UTF8;
StringBuilder sb = new StringBuilder();
// Create the XmlWriter object and write some content.
XmlWriter xmlWriter = XmlWriter.Create(sb, settings);
xmlWriter.WriteStartDocument();
// Write our first XML header (root)
xmlWriter.WriteStartElement("Root");
if (dtSorted.Rows.Count > 0)
{
Regex rx = new Regex(@"^[a-zA-z_]+");
foreach (DataRow dr in dtSorted.Rows)
{
worksheetName = arrColumns.Contains(sheetField) && !String.IsNullOrEmpty(dr[sheetField].ToString()) ? dr[sheetField].ToString() : "New";
if (!rx.IsMatch(worksheetName)) worksheetName = "_" + worksheetName;
if (prevWorksheet != worksheetName)
{
// end write worksheet
if (!String.IsNullOrEmpty(prevWorksheet)) xmlWriter.WriteEndElement();
// begin write worksheet
xmlWriter.WriteStartElement(worksheetName);
}
// begin write record
xmlWriter.WriteStartElement("Record");
foreach (Object o in arrColumns)
{
fieldName = o.ToString();
//if (fieldName != sheetField) // don't display worksheet field
xmlWriter.WriteElementString(fieldName, dr[fieldName].ToString());
}
// end write record
xmlWriter.WriteEndElement();
prevWorksheet = worksheetName;
}
// end write last worksheet
xmlWriter.WriteEndElement();
}
else
{
// begin write worksheet
xmlWriter.WriteStartElement(worksheetName);
// begin write record
xmlWriter.WriteStartElement("Record");
foreach (Object o in arrColumns)
{
fieldName = o.ToString();
xmlWriter.WriteElementString(fieldName, "");
}
//xmlWriter.WriteElementString("Result", "No records found.");
// end write record
xmlWriter.WriteEndElement();
// end write worksheet
xmlWriter.WriteEndElement();
}
// end root
xmlWriter.WriteEndElement();
// Finilize the XML document by writing any required closing tag.
xmlWriter.WriteEndDocument();
xmlWriter.Flush();
// Load the style sheet.
XslCompiledTransform xslt = new XslCompiledTransform();
xslt.Load(pathToXsl);
// Create the writer.
XmlWriterSettings settings2 = new XmlWriterSettings();
settings2.Indent = true;
settings2.IndentChars = "\t";
XmlWriter writer = XmlWriter.Create(stream, settings2);
XmlReader xmlReader = XmlReader.Create(new StringReader(sb.ToString()));
xmlReader.MoveToContent();
// Execute the transformation.
xslt.Transform(xmlReader, writer);
writer.Close();
xmlReader.Close();
xmlWriter.Close();
// Convert the memory stream to an array of bytes.
byte[] byteArray = stream.ToArray();
// Send the XML file to the web browser for download.
Response.Clear();
//Response.AppendHeader("Content-Disposition", "filename=Report.xml");
//Response.AppendHeader("Content-Length", byteArray.Length.ToString());
//Response.ContentType = "application/octet-stream";
//Response.ContentType = "application/vnd.xls";
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment; filename=" + reportFileName);
Response.AppendHeader("Content-Length", byteArray.Length.ToString());
Response.BinaryWrite(byteArray);
Response.End();
}
}
I'm not sure where things are going wrong, or what has changed. This is a new Windows 2019 Server (which doesn't have office installed on it), and the Asp.net/C# code does not have a solution (it was created with the old "website" style). Any help would be greatly appreciated! let me know if there's anything more needed.
Edit:
using the EPPlus library and the following code:
public void ExportToExcel(DataTable dt, string FileName)
{
//create a new byte array
byte[] bin;
//create a new excel document
using (ExcelPackage excelPackage = new ExcelPackage())
{
//create a new worksheet
ExcelWorksheet ws = excelPackage.Workbook.Worksheets.Add(FileName);
//add the contents of the datatable to the excel file
ws.Cells["A1"].LoadFromDataTable(dt, true);
//auto fix the columns
ws.Cells[ws.Dimension.Address].AutoFitColumns();
//loop all the columns
for (int col = 1; col <= ws.Dimension.End.Column; col++)
{
//make all columns just a bit wider, it would sometimes not fit
ws.Column(col).Width = ws.Column(col).Width + 1;
var cell = ws.Cells[1, col];
//make the text bold
cell.Style.Font.Bold = true;
//make the background of the cell gray
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#BFBFBF"));
//make the header text upper case
cell.Value = ((string)cell.Value).ToUpper();
}
//convert the excel package to a byte array
bin = excelPackage.GetAsByteArray();
}
//clear the buffer stream
Response.ClearHeaders();
Response.Clear();
Response.Buffer = true;
//set the correct contenttype
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//set the correct length of the data being send
Response.AddHeader("content-length", bin.Length.ToString());
//set the filename for the excel package
Response.AddHeader("content-disposition", "attachment; filename=\"" + FileName + ".xlsx\"");
//send the byte array to the browser
Response.OutputStream.Write(bin, 0, bin.Length);
//cleanup
Response.Flush();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
it now gives me an error:
C:\Users...\AppData\Local\Temp\RoIFPfvQ.xlsx.part could not be saved, because the source file could not be read.
however if I RDP to the web server and go to the web page and download the package and move it to local machine (since excel is not on the web server) the file opens just fine. What am I missing? where is this attempting to create this xlsx.part and could it be that the content is being flushed out/deleted before the file is actually saved/downloaded?
