using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
using System.Data.OleDb;
using System.Text;
namespace WebApplicationConVertExcelToHtml
{
public partial class ExcelToHtml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnconvert_Click(object sender, EventArgs e)
{
string ConStr = "";
if (FileUpload.HasFile)
{
string filename = Path.GetExtension(FileUpload.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload.PostedFile.FileName);
string path = Server.MapPath("~/Files/" + FileUpload.FileName);
//saving the file inside the MyFolder of the server
FileUpload.SaveAs(path);
lblmsg.Text = FileUpload.FileName + "\'s Data showing into the Html";
FileUpload.SaveAs(path);
//checking that extantion is .xls or .xlsx
if (Extension.Trim() == ".xls")
{
//connection string for that file which extantion is .xls
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (Extension.Trim() == ".xlsx")
{
//connection string for that file which extantion is .xlsx
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//making query
string query = "SELECT * FROM [Sheet1$]";
//Providing connection
OleDbConnection conn = new OleDbConnection(ConStr);
//checking that connection state is closed or not if closed the
//open the connection
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//create command object
OleDbCommand cmd = new OleDbCommand(query, conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
//ExportDatatableToHtml(dt);
string HtmlBody = ExportDatatableToHtml(dt);
System.IO.File.WriteAllText(@"C:\Users\Administrator\Desktop\GetExcelData.htm", HtmlBody);
//Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
else
{
}
}
protected string ExportDatatableToHtml(DataTable dt)
{
StringBuilder strHTMLBuilder = new StringBuilder();
strHTMLBuilder.Append("<html >");
strHTMLBuilder.Append("<head>");
strHTMLBuilder.Append("</head>");
strHTMLBuilder.Append("<body>");
strHTMLBuilder.Append("<table border='1px' cellpadding='1' cellspacing='1' bgcolor='lightyellow' style='font- family:Garamond; font-size:smaller'>");
strHTMLBuilder.Append("<tr >");
foreach (DataColumn myColumn in dt.Columns)
{
strHTMLBuilder.Append("<td >");
strHTMLBuilder.Append(myColumn.ColumnName);
strHTMLBuilder.Append("</td>");
}
strHTMLBuilder.Append("</tr>");
foreach (DataRow myRow in dt.Rows)
{
strHTMLBuilder.Append("<tr >");
foreach (DataColumn myColumn in dt.Columns)
{
strHTMLBuilder.Append("<td >");
strHTMLBuilder.Append(myRow[myColumn.ColumnName].ToString());
strHTMLBuilder.Append("</td>");
}
strHTMLBuilder.Append("</tr>");
}
//Close tags.
strHTMLBuilder.Append("</table>");
strHTMLBuilder.Append("</body>");
strHTMLBuilder.Append("</html>");
string Htmltext = strHTMLBuilder.ToString();
return Htmltext;
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Configuration;
using System.Data.OleDb;
using System.Text;
namespace WebApplicationConVertExcelToHtml
{
public partial class ExcelToHtml : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnconvert_Click(object sender, EventArgs e)
{
string ConStr = "";
if (FileUpload.HasFile)
{
string filename = Path.GetExtension(FileUpload.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload.PostedFile.FileName);
string path = Server.MapPath("~/Files/" + FileUpload.FileName);
//saving the file inside the MyFolder of the server
FileUpload.SaveAs(path);
lblmsg.Text = FileUpload.FileName + "\'s Data showing into the Html";
FileUpload.SaveAs(path);
//checking that extantion is .xls or .xlsx
if (Extension.Trim() == ".xls")
{
//connection string for that file which extantion is .xls
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (Extension.Trim() == ".xlsx")
{
//connection string for that file which extantion is .xlsx
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//making query
string query = "SELECT * FROM [Sheet1$]";
//Providing connection
OleDbConnection conn = new OleDbConnection(ConStr);
//checking that connection state is closed or not if closed the
//open the connection
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//create command object
OleDbCommand cmd = new OleDbCommand(query, conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
//ExportDatatableToHtml(dt);
string HtmlBody = ExportDatatableToHtml(dt);
System.IO.File.WriteAllText(@"C:\Users\Administrator\Desktop\GetExcelData.htm", HtmlBody);
//Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
else
{
}
}
protected string ExportDatatableToHtml(DataTable dt)
{
StringBuilder strHTMLBuilder = new StringBuilder();
strHTMLBuilder.Append("<html >");
strHTMLBuilder.Append("<head>");
strHTMLBuilder.Append("</head>");
strHTMLBuilder.Append("<body>");
strHTMLBuilder.Append("<table border='1px' cellpadding='1' cellspacing='1' bgcolor='lightyellow' style='font- family:Garamond; font-size:smaller'>");
strHTMLBuilder.Append("<tr >");
foreach (DataColumn myColumn in dt.Columns)
{
strHTMLBuilder.Append("<td >");
strHTMLBuilder.Append(myColumn.ColumnName);
strHTMLBuilder.Append("</td>");
}
strHTMLBuilder.Append("</tr>");
foreach (DataRow myRow in dt.Rows)
{
strHTMLBuilder.Append("<tr >");
foreach (DataColumn myColumn in dt.Columns)
{
strHTMLBuilder.Append("<td >");
strHTMLBuilder.Append(myRow[myColumn.ColumnName].ToString());
strHTMLBuilder.Append("</td>");
}
strHTMLBuilder.Append("</tr>");
}
//Close tags.
strHTMLBuilder.Append("</table>");
strHTMLBuilder.Append("</body>");
strHTMLBuilder.Append("</html>");
string Htmltext = strHTMLBuilder.ToString();
return Htmltext;
}
}
}
Posts
No comments:
New comments are not allowed.