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.
Hope this will be helpful
http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp
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
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.
create your Excel and simply stream it through the browser as a download.
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>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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.