How to Read Excel Cell Values With C#

-
From this post you will be able to learn How to read row data in Excel worksheet or How to find the used area in an Excel worksheet from C#For reading entire content of an Excel file in C#, we have to know how many cells used in the Excel file. In order to find the used range we use "UsedRange" property of xlWorkSheet . It will return the last cell of used area
Excel.Range range ;

range = xlWorkSheet.UsedRange;

 
Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            Excel.Range range ;

            string str;
            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open("csharp.net-informations.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;

            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2 ;
                    MessageBox.Show(str);
                }
            }

            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

Location: Homagama, Sri Lanka

Comments

Post a Comment

Popular posts from this blog

Dimensional modeling - Star schema vs Fact constellation

-
Image
Dimensional model of Data warehousing Ralph Kimball introduced the dimensional modeling in 1996 with his book, The Data Warehouse Toolkit. Dimensional modeling is implemented by de-normalizing the database for the reporting purposes, because the normalized databases are too hard to query and extract data for business reports. There are two main approaches for implementing dimensional data warehousing models:           Star schema           Snowflake schema They describe how the dimensions relate to the fact tables.  Facts and fact tables hold the information that the business is concerned about,  that measure the performance of the organization. Dimension tables contain descriptive attributes which gives the ability to the qualify the measures (facts). These two tables are related to each other and are connected by a key. Granularity  is the measure of how the dimensions and facts relate to each other. Facts with different granularity should be
Read more

Using Entity Model for connecting the database

-
When using Entity Model to connect the database, database is connected to the orject as a data model and instances of databse tables are used to perform insert,update,delete and search operations. This is a simple example of a database file connected using Entity Model namespace cecbClasses.Data_Adapters {     public class Func_FillMatrix     {         DSSEntities cecbContext; // This is the databse context         LeopoldMatrixValues matrixVal; // This is an variable of a database table         public Func_FillMatrix()             {                 cecbContext = new DSSEntities();             } // Inserting values to a database table public bool InsertValues(String impR, String actR, String proR, String impV, String magV)             {                 bool IsInserted = false;                 try                 {                     matrixVal = new LeopoldMatrixValues(); // Assigning the variable with a databse object                     matrixVal.impt_referenc
Read more

Populating data to the Date Dimension table

-
Date dimension plays an important role in data warehouse designing. It allows you to examine behavior and trends in the data over a period of time. The columns for the DimDate table (date dimension table) should be chosen according to the requirement of the data analysis.  When entering data to the DimDate table, extracting dates from the source database is not a good method because there can be missing days in between as the transactions may not have occurred on each day of the year.  Therefore, the best way is to populate data to the DimDate table separately, as the date is an universal attribute. There are various methods of populating data to the table;     Using an Excel file In this method, an excel spreadsheet which contains the date values is used as the data source and it’s extracted to the data warehouse using SSIS.     Using a SQL script In this method, a SQL script or a stored procedure is used to generate dates between two days and to populate data t
Read more