how to insert the color full data from excel sheet to database?

Posted by Tejamanimala under ASP.NET on 10/29/2013 | Points: 10 | Views : 727 | Status : [Member] | Replies : 1
hi ihave one application,which is used to upload the excel sheet to database.its working very fine.but my problem is my excel sheet is having color data,means data is with color red and underline in some places like... 8000 with red color and in some place it is more than 8000 with red color and underline.how to exactly store the data with color and underline in database,my normal uploading code is....

private string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
PopulateDatabaseTables();
}
}

private void PopulateDatabaseTables()
{
string tableName = string.Empty;
string sql = "SELECT *, name AS table_name " +
" FROM sys.tables WHERE Type = 'U' ORDER BY table_name";
using (SqlConnection conn = new SqlConnection(connStr))
{
using (DataTable table = new DataTable())
{
conn.Open();
using (SqlDataAdapter dAd = new SqlDataAdapter(sql, conn))
{
dAd.Fill(table);
}
ListBox1.DataSource = table;
ListBox1.DataBind();
}
}
}

protected void ImportNow_Click(object sender, EventArgs e)
{
if (ListBox1.SelectedValue == "")
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please select table in which you want to import data from excel sheet";
}
else if ((fileuploadExcel.FileName != ""))
{
string extension = Path.GetExtension(fileuploadExcel.FileName); // fileuploadExcel.PostedFile.FileName

string excelConnectionString;
SqlConnection conn = new SqlConnection(connStr);
string tableName = ListBox1.SelectedValue;
string path = Server.MapPath("~/fileuploadExcel/" + fileuploadExcel.FileName);
fileuploadExcel.SaveAs(path);

//Create connection string to Excel work book
if (extension == ".xls")
{
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Excel 8.0;HDR=Yes;IMEX=1";
}
else
{
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;sData Source=" + path + ";Excel 12.0;HDR=Yes;IMEX=1";
}
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
conn.Open();
SqlCommand comm = new SqlCommand("truncate table " + tableName, conn);
SqlCommand identityChange = conn.CreateCommand();
identityChange.CommandText = "SET IDENTITY_INSERT " + tableName + " ON";
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
// identityChange.ExecuteNonQuery();
SqlBulkCopy sqlBulk = new SqlBulkCopy(connStr);
//Give your Destination table name
sqlBulk.DestinationTableName = tableName;
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
conn.Close();
lblMessage.ForeColor = Color.Green;
lblMessage.Text = "Import into table <b>" + tableName + "</b> successful!<br />";
}
else
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please first upload (Select) excel file.";
}
}

protected void viewdata_Click(object sender, EventArgs e)
{
BindData();
}

private void BindData()
{
try
{
if (ListBox1.SelectedValue == "")
{
lblMessage.ForeColor = Color.Red;
lblMessage.Text = "Please select table for which you want to view data in Gridview";
}
else
{
string tableName = ListBox1.SelectedValue;
SqlConnection conn = new SqlConnection(connStr);
SqlDataAdapter sda = new SqlDataAdapter("select * from " + tableName, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
gvdetails.DataSource = ds;
gvdetails.DataBind();
}
}
catch (DataException de)
{
lblMessage.Text = de.Message;
lblMessage.ForeColor = System.Drawing.Color.Red;
}
}

protected void gvdetails_PageIndexChan

manimala


Responses

Posted by: Bandi on: 10/29/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
We can not color the data in database... Rather we can maintain the flag for colors by specifying same conditions of excel which were used for coloring data....



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Tejamanimala, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response