/// <summary>
/// Create an Excel file, and write it out to a MemoryStream (rather than directly to a file)
/// </summary>
/// <param name="ds">DataSet containing the data to be written to the Excel.</param>
/// <param name="filename">The filename (without a path) to call the new Excel file.</param>
/// <param name="Response">HttpResponse of the current page.</param>
/// <returns>Either a MemoryStream, or NULL if something goes wrong.</returns>
public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponse Response)
{
try
{
System.IO.MemoryStream stream = new System.IO.MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
{
WriteExcelFile(ds, document);
}
stream.Flush();
stream.Position = 0;
Response.ClearContent();
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
// NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have
// manually added System.Web to this project's References.
Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
Response.AddHeader("content-disposition", "attachment; filename=" + filename);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("content-length", stream.Length.ToString());
byte[] data1 = new byte[stream.Length];
stream.Read(data1, 0, data1.Length);
stream.Close();
Response.BinaryWrite(data1);
Response.Flush();
// Feb2015: Needed to replace "Response.End();" with the following 3 lines, to make sure the Excel was fully written to the Response
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.SuppressContent = true;
System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();
return(true);
}
catch (Exception ex)
{
// Display an error on the webpage.
System.Web.UI.Page page = System.Web.HttpContext.Current.CurrentHandler as System.Web.UI.Page;
page.ClientScript.RegisterStartupScript(page.GetType(), "log", "console.log('Failed, exception thrown: " + ex.Message + "')", true);
return(false);
}
}
internal static void CreatePDFDocument(DataTable dt, string excelFilename, System.Web.HttpResponse Response)
{
//
// For PDF export we are using the free open-source iTextSharp library.
//
Document pdfDoc = new Document();
MemoryStream pdfStream = new MemoryStream();
PdfWriter pdfWriter = PdfWriter.GetInstance(pdfDoc, pdfStream);
pdfDoc.Open();//Open Document to write
pdfDoc.NewPage();
iTextSharp.text.Font font8 = FontFactory.GetFont("ARIAL", 7);
PdfPTable PdfTable = new PdfPTable(dt.Columns.Count);
PdfPCell PdfPCell = null;
//Add Header of the pdf table
for (int column = 0; column < dt.Columns.Count; column++)
{
PdfPCell = new PdfPCell(new Phrase(new Chunk(dt.Columns[column].Caption, font8)));
PdfTable.AddCell(PdfPCell);
}
//How add the data from datatable to pdf table
for (int rows = 0; rows < dt.Rows.Count; rows++)
{
for (int column = 0; column < dt.Columns.Count; column++)
{
PdfPCell = new PdfPCell(new Phrase(new Chunk(dt.Rows[rows][column].ToString(), font8)));
PdfTable.AddCell(PdfPCell);
}
}
PdfTable.SpacingBefore = 15f; // Give some space after the text or it may overlap the table
pdfDoc.Add(PdfTable); // add pdf table to the document
pdfDoc.Close();
pdfWriter.Close();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/pdf";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + excelFilename);
Response.BinaryWrite(pdfStream.ToArray());
Response.End();
}
public void Save(System.IO.Stream OutputStream, System.Web.HttpResponse response)
{
string filename = "Export_" + Guid.NewGuid().ToString();
string completePathOne = GenerateUniquePath(filename, "xlsx");
string completePathTwo = GenerateUniquePath(filename, "xls");
string completePath = null;
string tableName = null;
ISDWorksheet ws = (ISDWorksheet)this.Worksheets[0];
ISDTable ta = ws.Table;
tableName = ws.Name;
ArrayList rows = ta.Rows;
ISDWorksheetRow row0 = null;
if (rows.Count > 0)
{
row0 = (ISDWorksheetRow)rows[0];
}
ISDWorksheetRow row1 = row0;
if (rows.Count > 1)
{
row1 = (ISDWorksheetRow)rows[1];
}
ArrayList cols = ta.Columns;
string colDefs = GetColumnDefinitions(cols, row0.Cells, row1.Cells, true);
string colNames = GetColumnDefinitions(cols, row0.Cells, row1.Cells, false);
completePath = completePathTwo;
HSSFWorkbook hssfwb = new HSSFWorkbook();
IDataFormat format = hssfwb.CreateDataFormat();
ISheet sh = hssfwb.CreateSheet("Sheet1");
int rIndex = 0;
IRow r = sh.CreateRow(rIndex);
int c = 0;
HSSFCellStyle[] styles = new HSSFCellStyle[row0.Cells.Count + 1];
foreach (ISDWorksheetCell hCell in row0.Cells)
{
HSSFCellStyle style = (HSSFCellStyle)hssfwb.CreateCellStyle();
ICell ce = r.CreateCell(c);
ce.SetCellValue(hCell.Text);
style.WrapText = true;
styles[c] = (HSSFCellStyle)hssfwb.CreateCellStyle();
ce.CellStyle = style;
c += 1;
}
for (rIndex = 1; rIndex <= rows.Count - 1; rIndex++)
{
ISDWorksheetRow currentRow = (ISDWorksheetRow)rows[rIndex];
r = sh.CreateRow(rIndex);
c = 0;
for (int i = 0; i <= currentRow.Cells.Count - 1; i++)
{
//myValue = myValue.Replace("$", "").Replace(",", "")
ICell ce = r.CreateCell(c);
HSSFCellStyle style = styles[i];
ISDWorksheetCell dCell = (ISDWorksheetCell)currentRow.Cells[i];
string formatStr = dCell.Format;
if (dCell.Type == ISDDataType.ISDInteger || dCell.Type == ISDDataType.ISDNumber)
{
ce.SetCellType(CellType.NUMERIC);
if (dCell.Value != null)
{
ce.SetCellValue(Convert.ToDouble(dCell.Value));
}
if (GetBuildInFormat(dCell.Format) > 0)
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
}
else
{
System.Globalization.NumberFormatInfo info = System.Globalization.CultureInfo.CurrentCulture.NumberFormat;
if (string.IsNullOrEmpty(dCell.Format) || dCell.Format == null)
{
formatStr = "##0.00";
}
else if (dCell.Format.Contains("C") || dCell.Format.Contains("c"))
{
formatStr = info.CurrencySymbol + "##0.00";
}
else if (dCell.Format.Contains("P") || dCell.Format.Contains("p"))
{
formatStr = "##0.00" + info.PercentSymbol;
}
else if (dCell.Format.Contains(info.CurrencySymbol) || dCell.Format.Contains(info.PercentSymbol))
{
// use the user given display format
}
else
{
formatStr = "##0.00";
}
style.DataFormat = format.GetFormat(formatStr);
}
}
else if (dCell.Type == ISDDataType.ISDDateTime)
{
if (dCell.Value != null)
{
ce.SetCellType(CellType.NUMERIC);
ce.SetCellValue(Convert.ToDateTime(dCell.Value));
}
if (GetBuildInFormat(dCell.Format) > 0)
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
}
else
{
System.Globalization.DateTimeFormatInfo info = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat;
// convert the date format understood by Excel
// see http://msdn.microsoft.com/en-us/library/az4se3k1(v=vs.71).aspx
switch (dCell.Format)
{
case "d":
formatStr = info.ShortDatePattern;
break;
case "D":
formatStr = info.LongDatePattern;
break;
case "t":
formatStr = info.ShortTimePattern;
break;
case "T":
formatStr = info.LongTimePattern;
break;
case "f":
formatStr = info.LongDatePattern + " " + info.ShortTimePattern;
break;
case "F":
formatStr = info.FullDateTimePattern;
break;
case "g":
formatStr = info.ShortDatePattern + " " + info.ShortTimePattern;
break;
case "G":
formatStr = info.ShortDatePattern + " " + info.LongTimePattern;
break;
case "M":
case "m":
formatStr = info.MonthDayPattern;
break;
case "R":
case "r":
formatStr = info.RFC1123Pattern;
break;
case "s":
formatStr = info.SortableDateTimePattern;
break;
case "u":
formatStr = info.UniversalSortableDateTimePattern;
break;
case "U":
formatStr = info.FullDateTimePattern;
break;
case "Y":
case "y":
formatStr = info.YearMonthPattern;
break;
default:
formatStr = info.ShortDatePattern;
break;
}
// some pattern above might return t but this is not recognized by Excel, so remove it
formatStr = formatStr.Replace("t", "");
style.DataFormat = format.GetFormat(formatStr);
}
}
else
{
ce.SetCellType(CellType.STRING);
if (dCell.Value != null)
{
string myValue = dCell.Text;
if (myValue.Length > 255)
{
myValue = myValue.Substring(0, 255);
}
ce.SetCellValue(myValue);
}
if (GetBuildInFormat(dCell.Format) > 0)
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat(dCell.Format);
}
else
{
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("TEXT");
style.WrapText = true;
}
}
ce.CellStyle = style;
c += 1;
}
}
MemoryStream ms = new MemoryStream();
hssfwb.Write(ms);
string NPOIDownloadFileName = this.Properties.Title;
if (completePath.EndsWith(".xlsx"))
{
NPOIDownloadFileName += ".xlsx";
}
else
{
NPOIDownloadFileName += ".xls";
}
response.ClearHeaders();
response.Clear();
response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
response.Cache.SetMaxAge(new TimeSpan(0));
response.Cache.SetExpires(new DateTime(0));
response.Cache.SetNoServerCaching();
response.AppendHeader("Content-Disposition", ("attachment; filename=\"" + (NPOIDownloadFileName + "\"")));
response.ContentType = "application/vnd.ms-excel";
OutputStream.Write(ms.ToArray(), 0, ms.ToArray().Length);
return;
}
void context_EndRequest(object sender, System.EventArgs e)
{
if (System.Web.HttpContext.Current != null && System.Web.HttpContext.Current.Response != null)
{
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
// This does not work with IIS-6...
//try
//{
// if (response.Cookies.Count > 0)
// {
// System.Text.StringBuilder stringBuilder = new System.Text.StringBuilder();
// for (int i = 0; i < response.Cookies.Count; i++)
// {
// ModifyCookie(stringBuilder, response.Cookies[i]);
// }
// string cookieText = stringBuilder.ToString();
// response.Headers.Set("Set-Cookie", cookieText);
// } // End if (response.Cookies.Count > 0)
//}
//catch (System.Exception ex)
//{
// System.Console.WriteLine(ex.Message); // Suppress warning
//}
try
{
// response.Headers["P3P"] = "CP=\\\"IDC DSP COR ADM DEVi TAIi PSA PSD IVAi IVDi CONi HIS OUR IND CNT\\\"":
// response.Headers.Set("P3P", "CP=\\\"IDC DSP COR ADM DEVi TAIi PSA PSD IVAi IVDi CONi HIS OUR IND CNT\\\"");
// response.AddHeader("P3P", "CP=\\\"IDC DSP COR ADM DEVi TAIi PSA PSD IVAi IVDi CONi HIS OUR IND CNT\\\"");
response.AppendHeader("P3P", "CP=\\\"IDC DSP COR ADM DEVi TAIi PSA PSD IVAi IVDi CONi HIS OUR IND CNT\\\"");
// response.AppendHeader("X-Frame-Options", "DENY");
// response.AppendHeader("X-Frame-Options", "SAMEORIGIN");
// response.AppendHeader("X-Frame-Options", "AllowAll");
// Log("Url: " + System.Web.HttpContext.Current.Request.Url.OriginalString);
if (System.Web.HttpContext.Current.Request.UrlReferrer != null)
{
// Log("UrlReferrer: " + System.Web.HttpContext.Current.Request.UrlReferrer.OriginalString);
// "X-Frame-Options": "ALLOW-FROM " Not recognized in Chrome
string host = System.Web.HttpContext.Current.Request.UrlReferrer.Scheme + System.Uri.SchemeDelimiter
+ System.Web.HttpContext.Current.Request.UrlReferrer.Authority
;
string origHost = System.Web.HttpContext.Current.Request.Url.Scheme + System.Uri.SchemeDelimiter
+ System.Web.HttpContext.Current.Request.Url.Authority
;
string selfAuth = System.Web.HttpContext.Current.Request.Url.Authority;
string refAuth = System.Web.HttpContext.Current.Request.UrlReferrer.Authority;
// SQL.Log(System.Web.HttpContext.Current.Request.RawUrl, System.Web.HttpContext.Current.Request.UrlReferrer.OriginalString, refAuth);
// Log("selfAuth: " + selfAuth + System.Environment.NewLine + "refAuth: " + refAuth);
if (IsHostAllowed(refAuth))
{
// Log("IsHostAllowed: true");
BrowserInfo bi = new BrowserInfo(System.Web.HttpContext.Current.Request);
// bi.Name = Firefox
// bi.Name = InternetExplorer
// bi.Name = Chrome
// Chrome wants entire path...
if (!System.StringComparer.OrdinalIgnoreCase.Equals(bi.Name, "Chrome"))
{
// response.AppendHeader("X-Frame-Options", "ALLOW-FROM " + host); // response.AppendHeader("X-Frame-Options", "ALLOW-FROM " + origHost);
response.AppendHeader("X-Frame-Options", "ALLOWALL"); // only working way to allow multiple
// response.AppendHeader("X-Frame-Options", "ALLOW-FROM " + "https://cafm-int.intra.stzh.ch/ https://cafm.intra.stzh.ch/ https://cafm.rs.intra.stzh.ch/ https://cafm-int.rs.intra.stzh.ch/");
// string allowFromRef = GetFromRef(System.Web.HttpContext.Current);
// if(!string.IsNullOrEmpty(allowFromRef)) response.AppendHeader("X-Frame-Options", "ALLOW-FROM " + allowFromRef);
// https://www.example.fr/ https://example.fr/ http://www.example.fr/ http://example.fr/"
// It gives a "A" when you check the result with https://security
}
// unsafe-eval: invalid JSON https://github.com/keen/keen-js/issues/394
// unsafe-inline: styles
// data: url(data:image/png:...)
// https://www.owasp.org/index.php/Clickjacking_Defense_Cheat_Sheet
// https://www.ietf.org/rfc/rfc7034.txt
// https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/X-Frame-Options
// https://developer.mozilla.org/en-US/docs/Web/HTTP/CSP
// https://stackoverflow.com/questions/10205192/x-frame-options-allow-from-multiple-domains
// https://content-security-policy.com/
// http://rehansaeed.com/content-security-policy-for-asp-net-mvc/
// This is for Chrome:
// response.AppendHeader("Content-Security-Policy", "default-src 'self' 'unsafe-inline' 'unsafe-eval' data: *.msecnd.net vortex.data.microsoft.com " + selfAuth + " " + refAuth);
System.Collections.Generic.List <string> ls = new System.Collections.Generic.List <string>();
ls.Add("default-src");
ls.Add("'self'");
ls.Add("'unsafe-inline'");
ls.Add("'unsafe-eval'");
ls.Add("data:");
// http://az416426.vo.msecnd.net/scripts/a/ai.0.js
// ls.Add("*.msecnd.net");
// ls.Add("vortex.data.microsoft.com");
ls.Add(selfAuth);
ls.Add(refAuth);
string contentSecurityPolicy = string.Join(" ", ls.ToArray());
response.AppendHeader("Content-Security-Policy", contentSecurityPolicy);
}
else
{
// Log("IsHostAllowed: false");
response.AppendHeader("X-Frame-Options", "SAMEORIGIN");
// response.AppendHeader("X-Frame-Options", "ALLOWALL");
}
}
else
{
// Log("no referrer.");
response.AppendHeader("X-Frame-Options", "SAMEORIGIN");
// response.AppendHeader("X-Frame-Options", "ALLOWALL");
}
}
catch (System.Exception ex)
{
// WTF ?
// Log(ex.Message);
// Log(ex.StackTrace);
System.Console.WriteLine(ex.Message); // Suppress warning
}
} // End if (System.Web.HttpContext.Current != null && System.Web.HttpContext.Current.Response != null)
} // End Using context_EndRequest
/// <summary>
/// Download File จาก File Server
/// </summary>
/// <param name="httpResponse">Current Response from Page</param>
/// <param name="folderContrainner">Folder ที่เก็บไฟล์</param>
/// <param name="fileName">ชื่อไฟล์</param>
public void DownloadFile(System.Web.HttpResponse httpResponse, string folderContrainner, string fileName)
{
//ระบุตำแหน่งไฟล์และที่เก็บบน File Server
//FileService.DownloadRequest requestData = new FileService.DownloadRequest();
//FileService.RemoteFileInfo fileInfo = new FileService.RemoteFileInfo();
//string resMsg = svc.DownloadFile(ref fileName, folderContrainner, out fileInfo.Length, out fileInfo.FileByteStream);
DownloadFileResponse response = new DownloadFileResponse();
response = svc.DownloadFile(new DownloadFileRequest()
{
TargetContainer = folderContrainner,
TargetFileName = fileName
});
Stream fileStream = response.FileByteStream;
httpResponse.Clear();
httpResponse.BufferOutput = true;
// Set Response.ContentType
httpResponse.ContentType = response.ContentType; //GetContentType(fileExtension);
// Append header
httpResponse.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
// Write the file to the Response
const int bufferLength = 10000;
byte[] buffer = new Byte[bufferLength];
int length = 0;
Stream download = null;
try
{
download = response.FileByteStream; // GetFile(fileName);
do
{
if (httpResponse.IsClientConnected)
{
length = download.Read(buffer, 0, bufferLength);
httpResponse.OutputStream.Write(buffer, 0, length);
buffer = new Byte[bufferLength];
}
else
{
length = -1;
}
}while (length > 0);
httpResponse.Flush();
httpResponse.End();
}
finally
{
if (download != null)
{
download.Close();
}
}
}
} // End Sub CreateExe
// http://blogs.msdn.com/b/dotnetinterop/archive/2008/06/04/dotnetzip-now-can-save-directly-to-asp-net-response-outputstream.aspx
// This will accumulate each of the files named in the fileList into a zip file,
// and stream it to the browser.
// This approach writes directly to the Response OutputStream.
// The browser starts to receive data immediately which should avoid timeout problems.
// This also avoids an intermediate memorystream, saving memory on large files.
//
public static void DownloadZipToBrowser(System.Collections.Generic.List <string> zipFileList)
{
System.Web.HttpResponse Response = System.Web.HttpContext.Current.Response;
Response.ClearContent();
Response.ClearHeaders();
Response.Clear();
Response.Buffer = false;
Response.ContentType = "application/zip";
// If the browser is receiving a mangled zipfile, IIS Compression may cause this problem. Some members have found that
// Response.ContentType = "application/octet-stream" has solved this. May be specific to Internet Explorer.
Response.AppendHeader("content-disposition", "attachment; filename=\"Download.zip\"");
// Response.CacheControl = "Private";
// Response.Cache.SetExpires(System.DateTime.Now.AddMinutes(3)); // or put a timestamp in the filename in the content-disposition
// http://stackoverflow.com/questions/9303919/pack-empty-directory-with-sharpziplib
byte[] buffer = new byte[4096];
using (ZipOutputStream zipOutputStream = new ZipOutputStream(Response.OutputStream))
{
zipOutputStream.SetLevel(3); //0-9, 9 being the highest level of compression
// zipOutputStream.Dispose
// Empty folder...
foreach (string directoryName in zipFileList)
{
string dname = "myfolder/";
ZipEntry entry = new ZipEntry(dname);
// ZipEntry entry = new ZipEntry(ZipEntry.CleanName(dname));
// entry.Size = fs.Length;
zipOutputStream.PutNextEntry(entry);
} // Next directoryName
foreach (string fileName in zipFileList)
{
// or any suitable inputstream
using (System.IO.Stream fs = System.IO.File.OpenRead(fileName))
{
ZipEntry entry = new ZipEntry(ZipEntry.CleanName(fileName));
entry.Size = fs.Length;
// Setting the Size provides WinXP built-in extractor compatibility,
// but if not available, you can set zipOutputStream.UseZip64 = UseZip64.Off instead.
zipOutputStream.PutNextEntry(entry);
int count = fs.Read(buffer, 0, buffer.Length);
while (count > 0)
{
zipOutputStream.Write(buffer, 0, count);
count = fs.Read(buffer, 0, buffer.Length);
if (!Response.IsClientConnected)
{
break;
}
Response.Flush();
} // Whend
fs.Close();
} // End Using fs
} // Next fileName
zipOutputStream.Close();
} // End Using zipOutputStream
Response.Flush();
Response.End();
} // End Function DownloadZipToBrowser