How to get an excel file while button click.

Question

chinni1 0 Newbie Poster

15 Years Ago

Hi,frends

I have a Export product button in product details page.
If i click the Export product button i want to get one pop up,the pop up should be contain the message like this
Your excel has been successfully create and can be viewed by clicking Product_data .
so I Want to get the Export products list in excell sheet using vb.net.

Plz Help me.

asp.net

4 Contributors
6 Replies
294 Views
2 Days Discussion Span
Latest Post 15 Years Ago Latest Post by chinni1

All 6 Replies

Aneesh_Argent 6 Junior Poster

15 Years Ago

Hope this will be helpful
http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp

ruwanthaka 0 Newbie Poster

15 Years Ago

Hi,frends
I have a Export product button in product details page.
If i click the Export product button i want to get one pop up,the pop up should be contain the message like this
Your excel has been successfully create and can be viewed by clicking Product_data .
so I Want to get the Export products list in excell sheet using vb.net.
Plz Help me.

hi
After hitting that button , you just need to show that message only or same time you need to create the excel file(using any data) and save on web server ?

Ruwanthaka

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

chinni1 0 Newbie Poster · Answer 1 · 2009-01-02T11:10:34+00:00

hi
After hitting that button , you just need to show that message only or same time you need to create the excel file(using any data) and save on web server ?
Ruwanthaka

ya i need to show message & create the excel file with details of products at the same time & and it should be saved into a folder.

iDeveloper 21 Light Poster · Answer 2 · 2009-01-02T12:11:32+00:00

create your Excel and simply stream it through the browser as a download.

ruwanthaka 0 Newbie Poster · Answer 3 · 2009-01-02T15:36:21+00:00

create your Excel and simply stream it through the browser as a download.

Hi chinni
Sorry for the delay & wish you a happy new year,

string filePath = location + “sample.xls";//Location>>the path of your virtual folder & sample.xls is the file name which going to create
int lineCount = 1;//Get the line count
System.Text.StringBuilder strExcelXml = new System.Text.StringBuilder();
strExcelXml.Append(this.ExcelHeader());//do not change any thing under ExcelHeader without having much knloage 
#region ExcelStyles & Column Width
                //keep Excel Styles as separate file and save it web server 
                strExcelXml.Append(this.ExcelStyles(ConfigurationSettings.AppSettings["Miscellaneous"] + "Styles.config"));
 
//Column Width settings>> This is only for formating
                strExcelXml.Append(this.SummaryColumnWidth());
                #endregion ExcelStyles & Column Width
//Append your Excel details Ex
this.AddRowCell("Page Number: 10", ref strExcelXml, lineCount++,9,false );
System.IO.File.Delete(filePath);
                System.IO.StreamWriter sw = new System.IO.StreamWriter(filePath, true, System.Text.Encoding.Unicode);
                              sw.Write(ConvertHTMLToExcelXML(strExcelXml.ToString()));
sw.Close()


///Methods
private string ExcelHeader()
        {
            // Excel header
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("<?xml version=\"1.0\"?>\n");
            sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
            sb.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
            sb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
            sb.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");
            sb.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
            sb.Append("<Author>Ruwantaka@yahoo.com</Author>");
            sb.Append("</DocumentProperties>");
            sb.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
            sb.Append("<ProtectStructure>False</ProtectStructure>\n");
            sb.Append("<ProtectWindows>False</ProtectWindows>\n");
            sb.Append("</ExcelWorkbook>\n");

            return sb.ToString();
        }
/////
public static string ConvertHTMLToExcelXML(string strHtml)
        {

            // Just to replace TR with Row
            strHtml = strHtml.Replace("<tr>", "<Row ss:AutoFitHeight=\"1\" >\n");
            strHtml = strHtml.Replace("</tr>", "</Row>\n");

            //replace the cell tags
            strHtml = strHtml.Replace("<td>", "<Cell><Data ss:Type=\"String\">");
            strHtml = strHtml.Replace("</td>", "</Data></Cell>\n");

            return strHtml;
        }
/////
private void AddCell(string data, ref StringBuilder sb, int colId, bool intVal)
        {
            if (intVal)
            {
                sb.Append("<Cell ss:Index=\"" + colId.ToString() + "\"><Data ss:Type=\"Number\">" + data + "</Data></Cell>");
                return;
            }
            sb.Append("<Cell ss:Index=\"" + colId.ToString() + "\"><Data ss:Type=\"String\">" + data + "</Data></Cell>");
        }
/////
private string ExcelStyles(string filename)
        {
            System.IO.StreamReader SR;
            string S;
            string strFileText = string.Empty;
            SR = System.IO.File.OpenText(filename);
            S = SR.ReadLine();
            strFileText = S;
            while (S != null)
            {
                S = SR.ReadLine();
                strFileText += S + "\n";
            }
            SR.Close();
            return strFileText;
        }
private string SummaryColumnWidth()
        {
            //  Column Width settings
            //strExcelXml.Append(ExcelStyles(ConfigurationSettings.AppSettings["Miscellaneous"] + "Styles.config"));
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("<Worksheet ss:Name=\"WorkSheet1\">");
            sb.Append("<Table>");
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"12\"/>");//Blank
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"50\"/>");//CONTRACT NO
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"123\"/>");//SECURITY
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"45\"/>");//QTY
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"33\"/>");//PRICE
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"45\"/>");//GROSS AMOUNT
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"29\"/>");//BROK RATE
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"52\"/>");//BROK AMOUNT
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"35\"/>");//OTHER CHARGES
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"52\"/>");//BROK AMOUNT
            sb.Append("<Column ss:Index=\"18\" ss:Width=\"56.25\"/>");//NET AMOUNT
            return sb.ToString();
            
        }

///// 
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
    <Font ss:FontName="Times New Roman" />
    <Interior/>
    <NumberFormat/>
    <Protection/>
  </Style>

  <Style ss:ID="s27" ss:Name="Hyperlink">
    <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Color="#0000FF" ss:Underline="Single"/>
  </Style>

  <Style ss:ID="s24">
    <Font ss:FontName="Times New Roman" x:Family="Roman" />
  </Style>

   <Style ss:ID="s25">
     <Font ss:FontName="Times New Roman" ss:Size="10"  />
   </Style>

   <Style ss:ID="s20">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="10" />
   </Style>
   <Style ss:ID="s100">
     <NumberFormat ss:Format="Standard"/>
   </Style>

   <Style ss:ID="s101">
     <NumberFormat ss:Format="Standard"/>
     <Borders>
       <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font  ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s102">
     <NumberFormat ss:Format="Standard"/>
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>
   <Style ss:ID="s102Age">
     <NumberFormat ss:Format="Standard"/>
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
   </Style>


   <Style ss:ID="s26">
     <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
     <Font ss:FontName="Times New Roman" x:Family="Roman" />
  </Style>
   <Style ss:ID="s291">
     <Font ss:FontName="Times New Roman" x:Family="Roman" />
   </Style>
   
   <Style ss:ID="s29">
     <Font ss:FontName="Times New Roman" x:Family="Roman" />
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
   </Style>

   <Style ss:ID="s30">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8" />
   </Style>
   
   <Style ss:ID="s90">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s91">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Underline="Double"/>
   </Style>
   
   <Style ss:ID="s60">
     <Borders>
       <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
     <Font  ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>
   
   <Style ss:ID="s61">
     <Borders>
      <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font  ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>
   <Style ss:ID="s61Age">
     <Borders>
       <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
    </Style>
   <Style ss:ID="s62">
     <Borders>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>
   
   <Style ss:ID="s63">
     <Borders>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s64">
     <Borders>
       <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s65">
     <Borders>
     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s66">
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s67">
     <Borders>
     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>
   
 </Styles>

chinni1 0 Newbie Poster · Answer 4 · 2009-01-02T15:41:32+00:00

Hi chinni
Sorry for the delay & wish you a happy new year,

string filePath = location + “sample.xls";//Location>>the path of your virtual folder & sample.xls is the file name which going to create
int lineCount = 1;//Get the line count
System.Text.StringBuilder strExcelXml = new System.Text.StringBuilder();
strExcelXml.Append(this.ExcelHeader());//do not change any thing under ExcelHeader without having much knloage 
#region ExcelStyles & Column Width
            //keep Excel Styles as separate file and save it web server 
            strExcelXml.Append(this.ExcelStyles(ConfigurationSettings.AppSettings["Miscellaneous"] + "Styles.config"));

//Column Width settings>> This is only for formating
            strExcelXml.Append(this.SummaryColumnWidth());
            #endregion ExcelStyles & Column Width
//Append your Excel details Ex
this.AddRowCell("Page Number: 10", ref strExcelXml, lineCount++,9,false );
System.IO.File.Delete(filePath);
System.IO.StreamWriter sw = new System.IO.StreamWriter(filePath, true, System.Text.Encoding.Unicode);
sw.Write(ConvertHTMLToExcelXML(strExcelXml.ToString()));
sw.Close()


///Methods
private string ExcelHeader()
        {
            // Excel header
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("<?xml version=\"1.0\"?>\n");
            sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
            sb.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
            sb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
            sb.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");
            sb.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
            sb.Append("<Author>Ruwantaka@yahoo.com</Author>");
            sb.Append("</DocumentProperties>");
            sb.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
            sb.Append("<ProtectStructure>False</ProtectStructure>\n");
            sb.Append("<ProtectWindows>False</ProtectWindows>\n");
            sb.Append("</ExcelWorkbook>\n");

            return sb.ToString();
        }
/////
public static string ConvertHTMLToExcelXML(string strHtml)
        {

            // Just to replace TR with Row
            strHtml = strHtml.Replace("<tr>", "<Row ss:AutoFitHeight=\"1\" >\n");
            strHtml = strHtml.Replace("</tr>", "</Row>\n");

            //replace the cell tags
            strHtml = strHtml.Replace("<td>", "<Cell><Data ss:Type=\"String\">");
            strHtml = strHtml.Replace("</td>", "</Data></Cell>\n");

            return strHtml;
        }
/////
private void AddCell(string data, ref StringBuilder sb, int colId, bool intVal)
        {
            if (intVal)
            {
                sb.Append("<Cell ss:Index=\"" + colId.ToString() + "\"><Data ss:Type=\"Number\">" + data + "</Data></Cell>");
                return;
            }
            sb.Append("<Cell ss:Index=\"" + colId.ToString() + "\"><Data ss:Type=\"String\">" + data + "</Data></Cell>");
        }
/////
private string ExcelStyles(string filename)
        {
            System.IO.StreamReader SR;
            string S;
            string strFileText = string.Empty;
            SR = System.IO.File.OpenText(filename);
            S = SR.ReadLine();
            strFileText = S;
            while (S != null)
            {
                S = SR.ReadLine();
                strFileText += S + "\n";
            }
            SR.Close();
            return strFileText;
        }
private string SummaryColumnWidth()
        {
            //  Column Width settings
            //strExcelXml.Append(ExcelStyles(ConfigurationSettings.AppSettings["Miscellaneous"] + "Styles.config"));
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("<Worksheet ss:Name=\"WorkSheet1\">");
            sb.Append("<Table>");
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"12\"/>");//Blank
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"50\"/>");//CONTRACT NO
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"123\"/>");//SECURITY
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"45\"/>");//QTY
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"33\"/>");//PRICE
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"45\"/>");//GROSS AMOUNT
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"29\"/>");//BROK RATE
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"52\"/>");//BROK AMOUNT
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"35\"/>");//OTHER CHARGES
            sb.Append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"52\"/>");//BROK AMOUNT
            sb.Append("<Column ss:Index=\"18\" ss:Width=\"56.25\"/>");//NET AMOUNT
            return sb.ToString();

        }

///// 
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
    <Font ss:FontName="Times New Roman" />
    <Interior/>
    <NumberFormat/>
    <Protection/>
  </Style>

  <Style ss:ID="s27" ss:Name="Hyperlink">
    <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Color="#0000FF" ss:Underline="Single"/>
  </Style>

  <Style ss:ID="s24">
    <Font ss:FontName="Times New Roman" x:Family="Roman" />
  </Style>

   <Style ss:ID="s25">
     <Font ss:FontName="Times New Roman" ss:Size="10"  />
   </Style>

   <Style ss:ID="s20">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="10" />
   </Style>
   <Style ss:ID="s100">
     <NumberFormat ss:Format="Standard"/>
   </Style>

   <Style ss:ID="s101">
     <NumberFormat ss:Format="Standard"/>
     <Borders>
       <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font  ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s102">
     <NumberFormat ss:Format="Standard"/>
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>
   <Style ss:ID="s102Age">
     <NumberFormat ss:Format="Standard"/>
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
   </Style>


   <Style ss:ID="s26">
     <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
     <Font ss:FontName="Times New Roman" x:Family="Roman" />
  </Style>
   <Style ss:ID="s291">
     <Font ss:FontName="Times New Roman" x:Family="Roman" />
   </Style>

   <Style ss:ID="s29">
     <Font ss:FontName="Times New Roman" x:Family="Roman" />
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
   </Style>

   <Style ss:ID="s30">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8" />
   </Style>

   <Style ss:ID="s90">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s91">
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Underline="Double"/>
   </Style>

   <Style ss:ID="s60">
     <Borders>
       <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
     <Font  ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s61">
     <Borders>
      <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font  ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>
   <Style ss:ID="s61Age">
     <Borders>
       <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
    </Style>
   <Style ss:ID="s62">
     <Borders>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
       <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" x:Family="Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s63">
     <Borders>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s64">
     <Borders>
       <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s65">
     <Borders>
     <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s66">
     <Borders>
       <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

   <Style ss:ID="s67">
     <Borders>
     <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
     <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
       </Borders>
     <Font ss:FontName="Times New Roman" ss:Size="8"/>
   </Style>

 </Styles>

end quote.

Thanks
Wish u the Same.

How to get an excel file while button click.

Recommended Answers Collapse Answers

All 6 Replies

Recommended Answers