Monday 3 February 2020

Create excel and zip without save file and images using asp.net

/////////////////////


public void ExportToExcel(DataTable dt, string FIleName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("");
            string Fullfilename = "" + FIleName + DateTime.Now.ToString("dd_MM_yyyy_hh_mm_ss") + ".xls";
            String HeaderStyle = "border:.3pt solid windowtext; font-weight:700;   word-wrap: normal; word-break: break-all; ";
            sb.Append("<table width='70%'>");
            sb.Append("<tr style='"+ HeaderStyle + "'>");
            string[] Heads = { "ID", "Name", "Address" };
            for (int j = 0; j < 2; j++)
            {
                sb.Append("<th>"+ Heads [j]+ "</th>");
            }
            sb.Append("</tr>");

            String RowStyle = "border:.1pt solid windowtext; font-weight:100; font-size:9pt;rowspan=2;";
         
            for (int j = 0; j < 5; j++)
            {
                sb.Append("<tr style='" + RowStyle + "'>");
                for (int i = 0; i < 2; i++)
                {
                    sb.Append("<td>" + "Name_"+j+"_"+i + "</td>");
                }              

                sb.Append("</tr>");
            }
          
            sb.Append("</table>");

            MemoryStream stream = new MemoryStream();
            byte[] data = Encoding.ASCII.GetBytes(sb.ToString());
            stream.Write(data, 0, data.Length);
            stream.Seek(0, SeekOrigin.Begin);

            HttpResponse Response = HttpContext.Current.Response;
            Response.ClearHeaders();
            Response.Charset = "UTF-8";

            string filePath = string.Empty;
            using (Ionic.Zip.ZipFile zip = new Ionic.Zip.ZipFile())
            {
                zip.AddEntry(Fullfilename, stream);
                zip.AlternateEncodingUsage = Ionic.Zip.ZipOption.AsNecessary;
                zip.AddDirectoryByName("UserDocs");
                string pth = Server.MapPath("~/FolderName");

                DirectoryInfo dir = new DirectoryInfo(pth);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    foreach (var d in dir.GetFiles())
                    {
                        if (d.Name == Convert.ToString(dt.Rows[i]["ImageName"]))
                        {
                            string pt = pth + "\\" + d.Name;
                            zip.AddFile(d.FullName, "UserDocs");
                        }
                    }
                }


                Response.Clear();
                Response.BufferOutput = false;
                string fName =DateTime.Now.ToString("yyyy-MMM-dd-HHmmss");
                Response.ContentType = "application/zip";
                Response.AddHeader("content-disposition", "attachment; filename="fName +".zip";
                zip.Save(Response.OutputStream);
                Response.End();
            }
        }

No comments:

Post a Comment

Excel Sort values in ascending order using function TEXTJOIN

 Excel ::  Text ::  1,3,5,2,9,5,11 Result :: 1,2,3,5,5,9,11 Formula ::     TEXTJOIN ( ",",1,SORT(MID(SUBSTITUTE( A1 ,","...