[nitin.raj]

I have code to export excel data to Sql Server 2000. In the excel, data in some columns is shown as "#####", this creates a runtime error. But when these columns are expanded in width the actual data is shown and the code runs successfully. The columns are expanded using "Autoformat" present in the "Format" menu of the excel file. This autoformat process should be done through the C# code for using in asp.net. Can someone please help with the C# code for autoformating the excel file

Code for exporting excel data to sql server2000

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Odbc;
using System.Text;

protected void btnExport_Click(object sender, EventArgs e)
{
OdbcConnection connection;
SqlBulkCopy bulkCopy;
string ConnectionString = @"server=192.14.45.135;database= NitDB; User ID= sa; Password=gis";
string connstr = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + fyle.Value;
//"fyle" is the html file upload control used in asp.net
//"fyle.value" returns the path of the file selected.
using (connection = new OdbcConnection(connstr))
{
string[] temp;
string fn1;
fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
temp = fn1.Split('.');
OdbcCommand command = new OdbcCommand("Select * FROM [" + temp[0].ToString() + "$]", connection);
connection.Open();
using (OdbcDataReader dr = command.ExecuteReader())
{
using (bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = "MT";
bulkCopy.WriteToServer(dr);
}
dr.Close();
}
}
bulkCopy.Close();
connection.Close();
}
}

This post has been edited by nitin.raj: 21 October 2008 - 01:42 AM