How to reduce excel file size during export from Gridview.

Question

How to write code to reduce excel file size during export data from Grid view to excel in aspx page? I have more than 2 Lcs data. Thanks in advance.

Tuhin Paul · Answer

Check the code: using ICSharpCode.SharpZipLib.Zip; using System; using System.IO; using System.Web.UI; using System.Web.UI.WebControls; public partial class ExportToExcel : Page { protected void Page_Load(object sender, EventArgs e) { // Bind data to the GridView GridView1.DataSource = GetData(); GridView1.DataBind(); } protected void btnExport_Click(object sender, EventArgs e) { // Create a MemoryStream to hold the Excel file using (var memoryStream = new MemoryStream()) { // Export data from the GridView to the Excel file using (var streamWriter = new StreamWriter(memoryStream)) { using (var htmlTextWriter = new HtmlTextWriter(streamWriter)) { GridView1.RenderControl(htmlTextWriter); } } // Compress the Excel file memoryStream.Position = 0; var compressedStream = new MemoryStream(); var zipStream = new ZipOutputStream(compressedStream); zipStream.PutNextEntry(new ZipEntry("Export.xlsx")); memoryStream.CopyTo(zipStream); zipStream.CloseEntry(); zipStream.IsStreamOwner = false; zipStream.Close(); compressedStream.Position = 0; // Send the compressed Excel file to the browser Response.Clear(); Response.ContentType = "application/zip"; Response.AppendHeader("Content-Disposition", "attachment; filename=Export.zip"); Response.BinaryWrite(compressedStream.ToArray()); Response.End(); } } public override void VerifyRenderingInServerForm(Control control) { // Required to avoid the "control must be placed inside a form tag" exception } private static object GetData() { // Your code to fetch data from the database or any other source } }

Tuhin Paul · Answer

Remove any unnecessary data such as formatting or styling information, images, or other non-essential data that is not needed in the Excel file. This can be achieved by setting the GridView properties such as AutoFormat, CssClass, HeaderStyle, and RowStyle to false.Also Limit the number of rows and columns exported to Excel by setting the AllowPaging property of the GridView to false and setting the PageSize property to a smaller value. BONUS: Try compress the output using libraries such as SharpZipLib or DotNetZip. These libraries provide classes to compress and decompress files and streams.