How to autoformat in excel in c# asp.net

Autoformat the columns to increase in width using C# in Asp.net

Page 1 of 1

0 Replies - 2676 Views - Last Post: 21 October 2008 - 01:23 AM Rate Topic: -----

#1 nitin.raj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 21-October 08

How to autoformat in excel in c# asp.net

Post icon  Posted 21 October 2008 - 01:23 AM

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


Is This A Good Question/Topic? 0
  • +

Page 1 of 1