If you are using ASP.NET then you can try to use 'HTTP Compression'.
protected void Page_Load(object sender, EventArgs e)
{
// Decide what format the data is requested in by examining the Querystring; e.g. thispage.aspx?ExportedDataFormat=XML&DisplaySchema=true
if (Request.QueryString["ExportedDataFormat"] != string.Empty)
exportedDataFormat = (ExportedDataFormatValue)Enum.Parse((typeof(ExportedDataFormatValue)), Request.QueryString["ExportedDataFormat"]);
else exportedDataFormat = ExportedDataFormatValue.XML;
// Does the caller want the Schema for the XML displayed (if it's XML)?
if (Request.QueryString["DisplaySchema"] != string.Empty)
exportedDataSchemaDisplay = (ExportedDataSchemaDisplayValue)Enum.Parse((typeof(ExportedDataSchemaDisplayValue)), Request.QueryString["DisplaySchema"]);
else exportedDataSchemaDisplay = ExportedDataSchemaDisplayValue.False;
// Get a full DataSet from code elsewhere (not shown)
DataSet report = myDataLayer.GetADataSet();
// Get a MemoryStream with the processed report in
MemoryStream memoryStream = ProcessReport(report);
// Send the stream of data to the Client (Optionally Compressed)
SendDataStream(memoryStream);
}
/// <summary>
/// If the user desires an XML report, then just spit out the passed in dataset
/// (with or without the Schema as appropriate). Otherwise pass off to
/// another modthod to spit out the HTML or Excel report.
/// </summary>
/// <param name="dataSet">DataSet containing the data to export.</param>
private MemoryStream ProcessReport(DataSet dataSet)
{
MemoryStream memoryStream = new MemoryStream();
if (exportedDataFormat == ExportedDataFormatValue.XML)
{
if (exportedDataSchemaDisplay == ExportedDataSchemaDisplayValue.False)
dataSet.WriteXml(memoryStream, XmlWriteMode.IgnoreSchema);
else dataSet.WriteXml(memoryStream, XmlWriteMode.WriteSchema);
}
else memoryStream = WriteOut(dataSet.Tables[0], memoryStream); // It's Excel or HTML
return memoryStream;
}
/// <summary>
/// Common Method to write out a Datatable as either HTML or as CSV (Excel)
/// </summary>
/// <param name="oDataTable">The Table to write out, complete with data.</param>
private MemoryStream WriteOut(DataTable oDataTable, MemoryStream memoryStream)
{
MemoryStream excelStream = new MemoryStream();
bool IsInCsvFormat = (exportedDataFormat == ExportedDataFormatValue.Excel);
using (StreamWriter streamWriter = new StreamWriter(excelStream))
{
StringBuilder oStringBuilder = new StringBuilder();
if (!IsInCsvFormat)
{
oStringBuilder.Append("<table class=\"report\"><tr>");
}
// Create Column Headers
foreach (DataColumn oDataColumn in oDataTable.Columns)
{
// Skip Byte columns altogether; unlikely to be valuable in a report (YMMV)
if (oDataColumn.GetType().ToString() == "System.Byte[]") continue;
if (IsInCsvFormat)
{
oStringBuilder.Append(oDataColumn.ColumnName);
if (oDataColumn != oDataTable.Columns[oDataTable.Columns.Count - 1]) oStringBuilder.Append(",");
}
else
{
oStringBuilder.Append("<th>");
oStringBuilder.Append(oDataColumn.ColumnName);
oStringBuilder.Append("</th>");
}
}
if (!IsInCsvFormat) oStringBuilder.Append("</tr>");
// Write the Header to our local excelStream
streamWriter.WriteLine(oStringBuilder.ToString());
oStringBuilder.Length = 0;
// Now Create the Rows
foreach (DataRow oDataRow in oDataTable.Rows)
{
if (!IsInCsvFormat) oStringBuilder.Append("<tr>");
foreach (DataColumn oDataColumn in oDataTable.Columns)
{
switch (oDataRow[oDataColumn.ColumnName].GetType().ToString())
{
case "System.DBNull":
if (!IsInCsvFormat) oStringBuilder.Append("<td>");
break;
case "System.String":
if (IsInCsvFormat)
{
oStringBuilder.Append("\"");
oStringBuilder.Append(oDataRow[oDataColumn.ColumnName].ToString().Replace("\"", string.Empty).Trim());
oStringBuilder.Append("\"");
}
else
{
oStringBuilder.Append("<td class=\"string\">");
oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]);
}
break;
case "System.Byte[]":
// Skip this column
continue;
case "System.Int32":
if (!IsInCsvFormat) oStringBuilder.Append("<td class=\"int\">");
oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]);
break;
case "System.DateTime":
if (!IsInCsvFormat) oStringBuilder.Append("<td class=\"datetime\">");
oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]);
break;
case "System.Guid":
if (IsInCsvFormat) oStringBuilder.Append("\"");
else oStringBuilder.Append("<td class=\"guid\">");
oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]);
if (IsInCsvFormat) oStringBuilder.Append("\"");
break;
default:
if (!IsInCsvFormat) oStringBuilder.Append("<td>");
oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]);
break;
}
if (IsInCsvFormat)
{
if (oDataColumn != oDataTable.Columns[oDataTable.Columns.Count - 1]) oStringBuilder.Append(",");
}
else oStringBuilder.Append("</td>");
}
if (!IsInCsvFormat) oStringBuilder.Append("</tr>");
// Write the row to our local excelStream
streamWriter.WriteLine(oStringBuilder.ToString());
oStringBuilder.Length = 0;
}
if (!IsInCsvFormat)
{
streamWriter.WriteLine("</table>");
streamWriter.WriteLine(footerText);
}
streamWriter.Flush();
// Copy local excelStream to our MemoryStream before disposing of it
memoryStream.Write(excelStream.ToArray(), 0, Convert.ToInt32(excelStream.Length));
}
return memoryStream;
}
private void SendDataStream(MemoryStream memoryStream)
{
Response.Clear();
switch (exportedDataFormat)
{
case ExportedDataFormatValue.XML:
Response.AddHeader("Content-Type", "text/xml");
break;
case ExportedDataFormatValue.Excel:
Response.AddHeader("Content-Type", "application/vnd.ms-excel");
Response.AddHeader("Content-disposition", ": csv; filename=document_" + Guid.NewGuid().ToString("N") + ".csv");
break;
default:
break;
}
/* Uncomment the three lines below if you want a physical file written to the server for debugging purposes.
if (exportedDataFormat == ExportedDataFormatValue.XML) File.WriteAllBytes(Request.PhysicalApplicationPath + "Output.xml", memoryStream.ToArray());
else if (exportedDataFormat == ExportedDataFormatValue.Excel) File.WriteAllBytes(Request.PhysicalApplicationPath + "Output.csv", memoryStream.ToArray());
else File.WriteAllBytes(Request.PhysicalApplicationPath + "Output.html", memoryStream.ToArray());*/
// Use GZip compression if the Client supports it (all modern browsers do)
memoryStream = OptionallyCompressOutput(memoryStream);
// Send the reponse to the Client and quit out
Response.OutputStream.Write(memoryStream.ToArray(), 0, Convert.ToInt32(memoryStream.Length));
memoryStream.Close();
Response.End();
}
/// <summary>
/// Examine HTTP Headers, looking for gzip support from requesting Client.
/// If found, compress the stream using GZip compression. This works very well
/// as our stream is XML and so full of air. We also set the Content-Encoding header to
/// inform the client it needs to uncompress the data.
/// If no support found, return uncompressed stream and don't set any Content-Encoding header.
/// </summary>
/// <param name="streamToSend">The stream full of data to squash.</param>
private MemoryStream OptionallyCompressOutput(MemoryStream memoryStream)
{
// Examine the headers sent by the client to see if it accepts gzip compression
string acceptedTypes = Request.Headers["Accept-Encoding"];
string[] types = acceptedTypes.Split(',');
for (int i = 0; i < types.Length; i++)
{
string acceptEncodingValue = types[i].Trim().ToLower();
if (acceptEncodingValue.StartsWith("gzip") || acceptEncodingValue.StartsWith("x-gzip"))
{
Response.AppendHeader("Content-Encoding", "gzip");
using (MemoryStream compressedData = new MemoryStream())
{
using (System.IO.Compression.GZipStream compressedStream = new System.IO.Compression.GZipStream(compressedData, System.IO.Compression.CompressionMode.Compress))
{
byte[] data = memoryStream.ToArray();
compressedStream.Write(data, 0, data.Length);
}
memoryStream = new MemoryStream(compressedData.ToArray());
// We now have our original data all squashed up in our local memoryStream.
}
}
}
return memoryStream;
}
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Member
504 Points
1776 Posts
Compress
Apr 29, 2018 02:53 PM|JagjitSingh|LINK
Hi
Is it possible to compress data before export to excel
Thanks
Contributor
2990 Points
1210 Posts
Re: Compress
Apr 30, 2018 06:19 AM|Deepak Panchal|LINK
Hi JagjitSingh,
If you are using ASP.NET then you can try to use 'HTTP Compression'.
protected void Page_Load(object sender, EventArgs e) { // Decide what format the data is requested in by examining the Querystring; e.g. thispage.aspx?ExportedDataFormat=XML&DisplaySchema=true if (Request.QueryString["ExportedDataFormat"] != string.Empty) exportedDataFormat = (ExportedDataFormatValue)Enum.Parse((typeof(ExportedDataFormatValue)), Request.QueryString["ExportedDataFormat"]); else exportedDataFormat = ExportedDataFormatValue.XML; // Does the caller want the Schema for the XML displayed (if it's XML)? if (Request.QueryString["DisplaySchema"] != string.Empty) exportedDataSchemaDisplay = (ExportedDataSchemaDisplayValue)Enum.Parse((typeof(ExportedDataSchemaDisplayValue)), Request.QueryString["DisplaySchema"]); else exportedDataSchemaDisplay = ExportedDataSchemaDisplayValue.False; // Get a full DataSet from code elsewhere (not shown) DataSet report = myDataLayer.GetADataSet(); // Get a MemoryStream with the processed report in MemoryStream memoryStream = ProcessReport(report); // Send the stream of data to the Client (Optionally Compressed) SendDataStream(memoryStream); } /// <summary> /// If the user desires an XML report, then just spit out the passed in dataset /// (with or without the Schema as appropriate). Otherwise pass off to /// another modthod to spit out the HTML or Excel report. /// </summary> /// <param name="dataSet">DataSet containing the data to export.</param> private MemoryStream ProcessReport(DataSet dataSet) { MemoryStream memoryStream = new MemoryStream(); if (exportedDataFormat == ExportedDataFormatValue.XML) { if (exportedDataSchemaDisplay == ExportedDataSchemaDisplayValue.False) dataSet.WriteXml(memoryStream, XmlWriteMode.IgnoreSchema); else dataSet.WriteXml(memoryStream, XmlWriteMode.WriteSchema); } else memoryStream = WriteOut(dataSet.Tables[0], memoryStream); // It's Excel or HTML return memoryStream; } /// <summary> /// Common Method to write out a Datatable as either HTML or as CSV (Excel) /// </summary> /// <param name="oDataTable">The Table to write out, complete with data.</param> private MemoryStream WriteOut(DataTable oDataTable, MemoryStream memoryStream) { MemoryStream excelStream = new MemoryStream(); bool IsInCsvFormat = (exportedDataFormat == ExportedDataFormatValue.Excel); using (StreamWriter streamWriter = new StreamWriter(excelStream)) { StringBuilder oStringBuilder = new StringBuilder(); if (!IsInCsvFormat) { oStringBuilder.Append("<table class=\"report\"><tr>"); } // Create Column Headers foreach (DataColumn oDataColumn in oDataTable.Columns) { // Skip Byte columns altogether; unlikely to be valuable in a report (YMMV) if (oDataColumn.GetType().ToString() == "System.Byte[]") continue; if (IsInCsvFormat) { oStringBuilder.Append(oDataColumn.ColumnName); if (oDataColumn != oDataTable.Columns[oDataTable.Columns.Count - 1]) oStringBuilder.Append(","); } else { oStringBuilder.Append("<th>"); oStringBuilder.Append(oDataColumn.ColumnName); oStringBuilder.Append("</th>"); } } if (!IsInCsvFormat) oStringBuilder.Append("</tr>"); // Write the Header to our local excelStream streamWriter.WriteLine(oStringBuilder.ToString()); oStringBuilder.Length = 0; // Now Create the Rows foreach (DataRow oDataRow in oDataTable.Rows) { if (!IsInCsvFormat) oStringBuilder.Append("<tr>"); foreach (DataColumn oDataColumn in oDataTable.Columns) { switch (oDataRow[oDataColumn.ColumnName].GetType().ToString()) { case "System.DBNull": if (!IsInCsvFormat) oStringBuilder.Append("<td>"); break; case "System.String": if (IsInCsvFormat) { oStringBuilder.Append("\""); oStringBuilder.Append(oDataRow[oDataColumn.ColumnName].ToString().Replace("\"", string.Empty).Trim()); oStringBuilder.Append("\""); } else { oStringBuilder.Append("<td class=\"string\">"); oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]); } break; case "System.Byte[]": // Skip this column continue; case "System.Int32": if (!IsInCsvFormat) oStringBuilder.Append("<td class=\"int\">"); oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]); break; case "System.DateTime": if (!IsInCsvFormat) oStringBuilder.Append("<td class=\"datetime\">"); oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]); break; case "System.Guid": if (IsInCsvFormat) oStringBuilder.Append("\""); else oStringBuilder.Append("<td class=\"guid\">"); oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]); if (IsInCsvFormat) oStringBuilder.Append("\""); break; default: if (!IsInCsvFormat) oStringBuilder.Append("<td>"); oStringBuilder.Append(oDataRow[oDataColumn.ColumnName]); break; } if (IsInCsvFormat) { if (oDataColumn != oDataTable.Columns[oDataTable.Columns.Count - 1]) oStringBuilder.Append(","); } else oStringBuilder.Append("</td>"); } if (!IsInCsvFormat) oStringBuilder.Append("</tr>"); // Write the row to our local excelStream streamWriter.WriteLine(oStringBuilder.ToString()); oStringBuilder.Length = 0; } if (!IsInCsvFormat) { streamWriter.WriteLine("</table>"); streamWriter.WriteLine(footerText); } streamWriter.Flush(); // Copy local excelStream to our MemoryStream before disposing of it memoryStream.Write(excelStream.ToArray(), 0, Convert.ToInt32(excelStream.Length)); } return memoryStream; } private void SendDataStream(MemoryStream memoryStream) { Response.Clear(); switch (exportedDataFormat) { case ExportedDataFormatValue.XML: Response.AddHeader("Content-Type", "text/xml"); break; case ExportedDataFormatValue.Excel: Response.AddHeader("Content-Type", "application/vnd.ms-excel"); Response.AddHeader("Content-disposition", ": csv; filename=document_" + Guid.NewGuid().ToString("N") + ".csv"); break; default: break; } /* Uncomment the three lines below if you want a physical file written to the server for debugging purposes. if (exportedDataFormat == ExportedDataFormatValue.XML) File.WriteAllBytes(Request.PhysicalApplicationPath + "Output.xml", memoryStream.ToArray()); else if (exportedDataFormat == ExportedDataFormatValue.Excel) File.WriteAllBytes(Request.PhysicalApplicationPath + "Output.csv", memoryStream.ToArray()); else File.WriteAllBytes(Request.PhysicalApplicationPath + "Output.html", memoryStream.ToArray());*/ // Use GZip compression if the Client supports it (all modern browsers do) memoryStream = OptionallyCompressOutput(memoryStream); // Send the reponse to the Client and quit out Response.OutputStream.Write(memoryStream.ToArray(), 0, Convert.ToInt32(memoryStream.Length)); memoryStream.Close(); Response.End(); } /// <summary> /// Examine HTTP Headers, looking for gzip support from requesting Client. /// If found, compress the stream using GZip compression. This works very well /// as our stream is XML and so full of air. We also set the Content-Encoding header to /// inform the client it needs to uncompress the data. /// If no support found, return uncompressed stream and don't set any Content-Encoding header. /// </summary> /// <param name="streamToSend">The stream full of data to squash.</param> private MemoryStream OptionallyCompressOutput(MemoryStream memoryStream) { // Examine the headers sent by the client to see if it accepts gzip compression string acceptedTypes = Request.Headers["Accept-Encoding"]; string[] types = acceptedTypes.Split(','); for (int i = 0; i < types.Length; i++) { string acceptEncodingValue = types[i].Trim().ToLower(); if (acceptEncodingValue.StartsWith("gzip") || acceptEncodingValue.StartsWith("x-gzip")) { Response.AppendHeader("Content-Encoding", "gzip"); using (MemoryStream compressedData = new MemoryStream()) { using (System.IO.Compression.GZipStream compressedStream = new System.IO.Compression.GZipStream(compressedData, System.IO.Compression.CompressionMode.Compress)) { byte[] data = memoryStream.ToArray(); compressedStream.Write(data, 0, data.Length); } memoryStream = new MemoryStream(compressedData.ToArray()); // We now have our original data all squashed up in our local memoryStream. } } } return memoryStream; }Reference:
Regards
Deepak
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.