ExcelReader

• Download source - 347.71 KB

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:

   C#

   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:

   C#

   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

• 13^th February, 2008: Initial post