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
create your Excel and simply stream it through the browser as a download.
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" ssize="10" />
</Style>
<Style ss:ID="s20">
<Font ss:FontName="Times New Roman" x:Family="Roman" ssize="10" />
</Style>
<Style ss:ID="s100">
<NumberFormat ss:Format="Standard"/>
</Style>
<Style ss:ID="s101">
<NumberFormat ss:Format="Standard"/>
<Borders>
<Border ssosition="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" x:Family="Roman" ssize="8"/>
</Style>
<Style ss:ID="s102">
<NumberFormat ss:Format="Standard"/>
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" ssize="8"/>
</Style>
<Style ss:ID="s102Age">
<NumberFormat ss:Format="Standard"/>
<Borders>
<Border ssosition="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 ssosition="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s30">
<Font ss:FontName="Times New Roman" x:Family="Roman" ssize="8" />
</Style>
<Style ss:ID="s90">
<Font ss:FontName="Times New Roman" x:Family="Roman" ssize="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 ssosition="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ssosition="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" x:Family="Roman" ssize="8"/>
</Style>
<Style ss:ID="s61">
<Borders>
<Border ssosition="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" x:Family="Roman" ssize="8"/>
</Style>
<Style ss:ID="s61Age">
<Borders>
<Border ssosition="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s62">
<Borders>
<Border ssosition="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ssosition="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" x:Family="Roman" ssize="8"/>
</Style>
<Style ss:ID="s63">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" ssize="8"/>
</Style>
<Style ss:ID="s64">
<Borders>
<Border ssosition="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" ssize="8"/>
</Style>
<Style ss:ID="s65">
<Borders>
<Border ssosition="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ssosition="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" ssize="8"/>
</Style>
<Style ss:ID="s66">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" ssize="8"/>
</Style>
<Style ss:ID="s67">
<Borders>
<Border ssosition="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ssosition="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Times New Roman" ssize="8"/>
</Style>
</Styles>
| DaniWeb Message | |
| Cancel Changes | |