In my current project I am migrating an excel application into .net 3.5 project.
my client is using an excel sheet as their project.
After seeing that excel application I learned so much about Excel. we can do so many things in excel.
 
Formulas tab: Name Manager (For defining the constants) Trace precendents, trace dependents, remove arrows.
 
 
Excel problem
If u importing excel into a datatable if some columns are having formulas then they wont go to dataset.
For getting them imported use IMEX=1;
Soul: IMEX=1;
string strConnectionToSheet = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathToSpreadSheet + "; Extended Properties=’Excel 8.0; IMEX=1; HDR=YES’";
———
Find no of columns in a table in sqlserver
CREATE TABLE #temp (
                table_name sysname ,
                row_count INT,
                reserved_size VARCHAR(50),
                data_size VARCHAR(50),
                index_size VARCHAR(50),
                unused_size VARCHAR(50))
    SET NOCOUNT ON
INSERT     #temp
    EXEC       sp_msforeachtable ‘sp_spaceused ”?”’
SELECT     a.table_name,
            a.row_count,
            COUNT(*) AS col_count,
            a.data_size
    FROM       #temp a
            INNER JOIN information_schema.columns b
            ON a.table_name collate database_default
    = b.table_name collate database_default
    GROUP BY   a.table_name, a.row_count, a.data_size
    ORDER BY   CAST(REPLACE(a.data_size, ‘ KB’, ”) AS integer) DESC
DROP TABLE #temp
Advertisements

About ambatisreedhar

Developer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s