Click here to Skip to main content
Click here to Skip to main content

ExcelReader

, 12 Feb 2008
Rate this:
Please Sign up or sign in to vote.
A tool to read the data from an Excel sheet into a C# string array

Introduction

This is a tool which acts as a wrapper around the Interop.Excel COM provided by default. The tool reads an Excel worksheet depending upon the range mentioned and stores it into a string array.

Background

In Excel, the data is represented in the form of objects. There is no end of file character or anything that can be used so as to read till the end of an Excel sheet. It always requires the user to specify a region from which data is to be read. So specifying range is important when dealing with Excel sheets.

Using the Code

public void OpenFile(string filename, string password)
        {
            esh_filename = filename;
            if (password.Length > 0)
            {
                esh_password = password;
            }
            try
            {
                this.excelWorkbook = this.excelApplication.Workbooks.Open(filename,
                        esh_update_links, esh_read_only, esh_format, esh_password,
                        esh_write_res_password,esh_ignore_read_only_recommend, 
                        esh_origin, esh_delimiter, esh_editable, esh_notify,
                        esh_converter, esh_add_to_mru, esh_local, esh_corrupt_load);
            }            
            catch (Exception ee)
            {
                if ((ee.Message).Contains("could not be found"))
                {
                    throw (new FileNotFoundException(ee.Message));
                }
                else
                {
                    throw (new Exception("Unknown error while opening the file"));
                }
            }            
        } 

The OpenFile() method is used to open an ExcelFile and this method abstracts the default parameters used to open an Excel workbook for reading.

The worksheets corresponding to the current workbook are then extracted using GetExcelsheets() from which a required Excel sheet is accessed as shown below:

 public bool OpenReqExcelWorksheet(string worksheetName)
        {
            bool sheet_found = false;

            if (this.excelSheets != null)
            {
                for (int i = 1; i <= this.excelSheets.Count; ++i)
                {
                  this.excelWorksheet = (Excel.Worksheet)excelSheets.get_Item((object)i);
                  if (this.excelWorksheet.Name == worksheetName)
                    {
                        ((Excel._Worksheet)excelWorksheet).Activate();
                        sheet_found = true;
                        return sheet_found;
                    }

                }
            }
            return sheet_found;
        }

Once the desired ExcelSheet is found, then comes the part where data is to be read from a desired range.

As mentioned above, specifying range is very important to read data from an Excel sheet. This tool helps the user to mention range in two different ways:

  1. Specify the start range cell and the end range cell.

    In this case the following method is used to extract data from Excel:

     public string[][] GetRange(string startRange, string endRange)
            {
                try
                {
         Excel.Range currentRangeCells = this.excelWorksheet.get_Range
                                         (startRange, endRange);
                    System.Array dataArray = 
                            (System.Array)currentRangeCells.Cells.Value2;
                    string[][] stringArray = this.ToStringArray(dataArray);
                    return stringArray;
                }
                catch (Exception e)
                {
                    if (e.Message.Contains("Exception: Conversion to string array"))
                    {
                        throw (e);
                    }
                    else
                    {
                        throw (new Exception("Exception: Range Extraction"));
                    }
                }
    
            }
  2. Specify that one of the cells in the sheet has information regarding range.

    For example: The range information is given in the Excel sheet itself as "A2:I20" in the cell - 'A1', then the following method is used to extract data:

     public string[][] GetRange(string startRange)
            {
                try
                {
                    Excel.Range currentRangeCells = 
                        this.excelWorksheet.get_Range(startRange,
                        System.Reflection.Missing.Value);
                    string range = currentRangeCells.Cells.Value2 as string;
                    char[] splitter = { ':' };
                    string[] rangeArray = range.Split(splitter, 2);
                    string[][] stringArray = GetRange(rangeArray[0], rangeArray[1]);
                    return stringArray;
                }
                catch (Exception e)
                {
                    if (e.Message.Contains("Exception:"))
                    {
                        throw (e);
                    }
                    else
                    {
                        throw (new Exception("Exception: Range Extraction"));
                    }
                }
            }

Thus the data from Excel is read into a string array.

History

  • 13th February, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
QuestionWith .net 4.5 it's easier now: PinprofessionalDietmar Schoder29-Jul-14 5:19 
SuggestionError in Program.cs of ExcelTool PinmemberMyo Min Lin13-Aug-12 22:28 
QuestionHow to convert it to Visual C# 2008? PinmemberLisa_Ho27-Mar-11 22:00 
AnswerRe: How to convert it to Visual C# 2008? PinmemberBharadwajSonti28-Mar-11 14:50 
GeneralVery helpfull Pinmemberhortonsn34rz23-Jan-11 22:56 
Questionproblem in a reference? Pinmemberredlineborn8-Nov-09 18:17 
AnswerRe: problem in a reference? PinmemberBharadwajSonti10-Nov-09 21:26 
GeneralRe: problem in a reference? PinmemberLisa_Ho27-Mar-11 22:05 
GeneralRe: problem in a reference? PinmemberBharadwajSonti28-Mar-11 14:47 
GeneralNice, Simple, and Straight Forward. PinmemberRajib Ahmed13-Feb-08 5:04 
GeneralRe: Nice, Simple, and Straight Forward. PinmemberBharadwajSonti14-Feb-08 0:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140826.1 | Last Updated 13 Feb 2008
Article Copyright 2008 by BharadwajSonti
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid