Excel and .NET Integration

Table-valued functions in finaquant .net libraries

Excel and .NET Integration

Postby tunc » 25 Aug 2014, 19:59

Hello all,

You may find some useful code in C#/.NET below for Excel and .NET integration. You can easily translate the code from C# to VB.NET (visual basic) with a web-based service like this one.

The listed code examples below to address following problems:

  1. How to get the current Application instance of excel
  2. How to check if a certain Workbook corresponding to an application instance is already opened, and get the Workbook object
  3. How to get a Worksheet object of a given Workbook by sheet's name
  4. How to read an ListObject (excel table) into a DataTable (ADO.NET) object
  5. How to write (dump) a DataTable into a given worksheet efficiently

Some useful links for Excel-.NET integration:

Questions and comments are welcome.

Tunc

Excel-related assemby references
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. using System.Runtime.InteropServices;
  2. using Excel = Microsoft.Office.Interop.Excel;
  3. using Office = Microsoft.Office.Core;
  4. using Microsoft.Office.Tools.Excel;
  5. using Microsoft.Office.Tools.Excel.Extensions;
GeSHi ©


1) Get the current application instance of excel
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. public static Excel.Application GetExcelApplicationInstance()
  2. {
  3.     Excel.Application instance = null;
  4.     try
  5.     {
  6.         instance = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
  7.     }
  8.     catch (Exception ex)
  9.     {
  10.         instance = new Excel.Application();
  11.     }
  12.     return instance;
  13. }
GeSHi ©

For the use of Globals class in Office projects (in Visual Sttudio) see also: Global Access to Objects in Office Projects

2) Get the workbook object by file path of the excel file
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. string filename = "CalcsExcelDemo.xlsm";
  2. string filepath = @"C:\Users\Newton\Documents\CalcsExcelDemo.xlsm";
  3. // First check if workbook is opened
  4. public static bool IsWorkbookOpen(Excel.Application xlapp, string WBfilepath)
  5. {
  6.     if (xlapp == null)
  7.         throw new Exception("ExcelFunc.IsExcelWBOpen2: Null-valued application instance");
  8.     var wbs = (Excel.Workbooks) xlapp.Workbooks;
  9.     try
  10.     {
  11.         foreach (var wbook in wbs)
  12.         {
  13.             if (WBfilepath == ((Excel.Workbook)wbook).FullName)
  14.                 return true;
  15.         }
  16.     }
  17.     catch { return false; }
  18.     return false;
  19. }
  20. // Get workbook object:
  21. // Open workbook in editable mode if it is not already opened
  22. Excel.Workbook wb;
  23. if (!ExcelFunc.IsWorkbookOpen(xlapp, filepath))
  24.     wb = (Excel.Workbook)xlapp.Workbooks.Open(filepath, Type.Missing, ReadOnly: false);
  25. else
  26.     wb = xlapp.Workbooks.get_Item(filename);
GeSHi ©


3) Get a worksheet object from the workbook by sheet's name
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. string sheetname = "Sheet1";
  2. Excel.Worksheet ws = wb.Worksheets.get_Item(sheetname);
GeSHi ©

..where wb is the Workbook object.

4) Read a ListObject (excel table) into a DataTable object
Reading a strongly typed DataTable from excel range. MetaData object md contain field definitions.

Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. // Get a ListObject named "ProductTbl" from worksheet
  2. Excel.ListObject xProd = wsheet.ListObjects.get_Item("ProductTbl");
  3.  
  4. // Get range from ListObject
  5. Excel.Range range = xProd.Range
  6.  
  7. // Method for reading a DataTable from a range
  8. public static DataTable ReadDataTableFromRange(Excel.Range range, MetaData md = null)
  9. {
  10.     Excel.Worksheet wsheet = null;
  11.     Excel.Range xrange = null;
  12.  
  13.     // get worksheet from rage
  14.     wsheet = (Excel.Worksheet)range.Worksheet;
  15.     // init variables
  16.     DataTable tbl = new DataTable();
  17.     object CellValue;
  18.     string fieldname;
  19.     FieldType ftype;
  20.  
  21.     // read column names from header line into DataTable
  22.     // assign data types to columns (i.e. other than object) if MetaData is not null
  23.     for (int i = 0; i < range.Columns.Count; i++)
  24.     {
  25.         // all field names are stored in small letters in MetaData
  26.         fieldname = ((string)range.Cells[1, i + 1].Value2).ToLower();
  27.  
  28.         if (md == null)     // return a object-typed DataTable
  29.             tbl.Columns.Add(fieldname.ToString(), typeof(object));
  30.  
  31.         else   // return a strongly typed DataTable
  32.         {
  33.             ftype = MetaData.GetFieldType(md, fieldname);
  34.  
  35.             switch (ftype)
  36.             {
  37.                 case FieldType.TextAttribute:
  38.                     tbl.Columns.Add(fieldname, typeof(string));
  39.                     break;
  40.                 case FieldType.IntegerAttribute:
  41.                     tbl.Columns.Add(fieldname, typeof(int));
  42.                     break;
  43.                 case FieldType.DateAttribute:
  44.                     tbl.Columns.Add(fieldname, typeof(DateTime));
  45.                     break;
  46.                 case FieldType.KeyFigure:
  47.                     tbl.Columns.Add(fieldname, typeof(double));
  48.                     break;
  49.                 default:
  50.                     throw new Exception("Field name '" + fieldname + "' is not defined in meta data!");
  51.             }
  52.         }
  53.     }
  54.     string sval = "EMPTY";
  55.     int ival = 0;
  56.     double dval = 0.0;
  57.    
  58.     // read cell values row by row
  59.     for (int i = 0; i < range.Rows.Count - 1; i++)
  60.     {
  61.         tbl.Rows.Add();
  62.  
  63.         for (int j = 0; j < range.Columns.Count; j++)
  64.         {
  65.             CellValue = (object)range.Cells[2 + i, j + 1].Value2;
  66.  
  67.             if (md == null)     // object-typed DataTable
  68.             {
  69.                 tbl.Rows[i][j] = CellValue;
  70.             }
  71.             else                // strongly-typed DataTable
  72.             {
  73.                 fieldname = tbl.Columns[j].ColumnName;
  74.                 ftype = MetaData.GetFieldType(md, fieldname);
  75.  
  76.                 switch (ftype)
  77.                 {
  78.                     case FieldType.TextAttribute:
  79.                         tbl.Rows[i][fieldname] = CellValue.ToString();
  80.                         break;
  81.  
  82.                     case FieldType.IntegerAttribute:
  83.                     case FieldType.DateAttribute:
  84.                         ival = Convert.ToInt32(CellValue.ToString());
  85.    
  86.                         if (ftype == FieldType.IntegerAttribute)
  87.                             tbl.Rows[i][fieldname] = ival;
  88.                         else
  89.                             tbl.Rows[i][fieldname] = DateFunctions.NumberToDate(ival);
  90.                         break;
  91.  
  92.                     case FieldType.KeyFigure:
  93.                         // throw an error if element value can't be converted to double
  94.                         try
  95.                         {
  96.                             dval = Convert.ToDouble(CellValue.ToString());
  97.                         }
  98.                         catch (Exception ex)
  99.                         {
  100.                             throw new Exception("Cell value " + CellValue + " cannot be converted to double! " +
  101.                                 "Row: " + i + " Column: " + fieldname + "\n" + ex.Message);
  102.                         }
  103.                         tbl.Rows[i][fieldname] = dval;
  104.                         break;
  105.  
  106.                     default:  // should not happen
  107.                         break;
  108.                 }
  109.             }
  110.         }
  111.     }
  112.     // return data table
  113.     return tbl;
  114. }
GeSHi ©

5) Write (dump) a DataTable into a given worksheet efficiently
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. public static void WriteTableToExcelSheet(Excel.Worksheet wsheet, DataTable tbl, string TopLeftCell = "A1")
  2. {
  3.     Excel.Range range = null;
  4.     Excel.Range UpperLeftCell = null;
  5.  
  6.     try
  7.     {
  8.         // get upper left cell in range
  9.         UpperLeftCell = (Excel.Range)wsheet.get_Range(TopLeftCell).Cells[1, 1];
  10.  
  11.         // clear range for table
  12.         range = wsheet.get_Range(Cell1: UpperLeftCell.Address);
  13.         range = range.get_Resize(tbl.Rows.Count + 1, tbl.Columns.Count);
  14.         range.Cells.ClearContents();
  15.  
  16.         // write column names as table header (first line)
  17.         string[] fieldNames = new string[tbl.Columns.Count];
  18.  
  19.         for (int i = 0; i < tbl.Columns.Count; i++)
  20.         {
  21.             UpperLeftCell.Cells[1, i + 1] = tbl.Columns[i].ColumnName;
  22.             fieldNames[i] = tbl.Columns[i].ColumnName;
  23.         }
  24.  
  25.         // for each column, create an array and set the array
  26.         // to the excel range for that column.
  27.         for (int i = 0; i < fieldNames.Length; i++)
  28.         {
  29.             string[,] clnDataString = new string[tbl.Rows.Count, 1];
  30.             int[,] clnDataInt = new int[tbl.Rows.Count, 1];
  31.             double[,] clnDataDouble = new double[tbl.Rows.Count, 1];
  32.  
  33.             // row and column offsets (shifts w.r.t. upper left corner)
  34.             range = wsheet.get_Range(Cell1: UpperLeftCell.Address).get_Offset(1,i);
  35.  
  36.             // define range size
  37.             range = range.get_Resize(tbl.Rows.Count, 1);
  38.  
  39.             string dataTypeName = tbl.Columns[fieldNames[i]].DataType.Name;
  40.  
  41.             for (int j = 0; j < tbl.Rows.Count; j++)
  42.             {
  43.                 switch (dataTypeName.ToLower())
  44.                 {
  45.                     case "int32":
  46.                         clnDataInt[j, 0] = Convert.ToInt32(tbl.Rows[j][fieldNames[i]]);
  47.                         break;
  48.  
  49.                     case "double":
  50.                         clnDataDouble[j, 0] = Convert.ToDouble(tbl.Rows[j][fieldNames[i]]);
  51.                         break;
  52.  
  53.                     case "datetime":
  54.                         clnDataInt[j, 0] = Convert.ToInt32(DateFunctions.DateToNumber((DateTime) tbl.Rows[j][fieldNames[i]]));
  55.                         break;
  56.  
  57.                     case "string":
  58.                         clnDataString[j, 0] = tbl.Rows[j][fieldNames[i]].ToString();
  59.                         break;
  60.  
  61.                     default:
  62.                         throw new Exception("Invalid data type! Any data type other than "
  63.                             + "string, integer, DateTime and double is not compatible with "
  64.                             + " finaquant's table of type MatrixType");
  65.                 }
  66.  
  67.             }
  68.  
  69.             if (dataTypeName == "Int32")
  70.             {
  71.                 range.set_Value(value: clnDataInt);
  72.                 range.NumberFormat = "General";
  73.             }
  74.             else if (dataTypeName == "DateTime")
  75.             {
  76.                 range.set_Value(value: clnDataInt);
  77.                 range.NumberFormat = "dd/mm/yyyy";  //"dd-mmm-yy";
  78.             }
  79.  
  80.             else if (dataTypeName == "Double")
  81.             {
  82.                 range.set_Value(value: clnDataDouble);
  83.                 range.NumberFormat = "General";
  84.             }
  85.             else
  86.             {
  87.                 range.set_Value(value: clnDataString);
  88.                 range.NumberFormat = "General";
  89.             }
  90.         }
  91.  
  92.         // make the header range bold
  93.         range = wsheet.get_Range(Cell1: UpperLeftCell.Address).get_Resize(1, tbl.Columns.Count);
  94.         range.Font.Bold = true;
  95.  
  96.         // autofit for better view
  97.         wsheet.Columns.AutoFit();
  98.     }
  99.     catch (Exception ex)
  100.     {
  101.         throw new Exception("WriteTableToExcelSheet: " + ex.Message);
  102.     }
  103.     finally
  104.     {
  105.         releaseObject(range);
  106.         releaseObject(UpperLeftCell);
  107.     }
  108. }
GeSHi ©
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

Re: Excel and .NET Integration

Postby tunc » 26 Aug 2014, 09:25

Some other useful code in C# for excel VBA integration..

How to find a ListObject in an excel workbook by its name
Check if ListObject (excel table) exists in workbook
Return ListObject handle if found, otherwise return null
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. public static Excel.ListObject FindListObject(Excel.Workbook wbook, string TableName)
  2. {
  3.     if (wbook == null) return null;
  4.     Excel.ListObject xlist = null;
  5.     try
  6.     {
  7.         if (wbook.Worksheets.Count == 0) return xlist;
  8.  
  9.         foreach (var wsheet in wbook.Worksheets)
  10.         {
  11.             if (((Excel.Worksheet)wsheet).ListObjects == null) continue; // don't know if this is necessary
  12.  
  13.             for (int i = 1; i <= ((Excel.Worksheet)wsheet).ListObjects.Count; i++)
  14.             {
  15.                 if (((Excel.Worksheet)wsheet).ListObjects[i].Name == TableName)
  16.                 {
  17.                     xlist = ((Excel.Worksheet)wsheet).ListObjects[i];
  18.                     return xlist;
  19.                 }
  20.             }
  21.         }
  22.     }
  23.     catch
  24.     {
  25.         return xlist;
  26.     }
  27.     return xlist;
  28. }
GeSHi ©

How to check if a worksheet exists in an excel workbook
Check if worksheet exists in excel workbook.
If not, create a new sheet with the given name if AddSheetIfNotFound is set to true, otherwise return null.
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. public static Excel.Worksheet FindWorksheet(Excel.Workbook wbook, string SheetName,
  2.             bool AddSheetIfNotFound = false)
  3. {
  4.     Excel.Worksheet wsheet = null;
  5.     if (wbook == null) return null;
  6.  
  7.     foreach (Excel.Worksheet sheet in wbook.Sheets)
  8.     {
  9.         if (sheet.Name.Equals(SheetName))
  10.         {
  11.             wsheet = wbook.Worksheets.get_Item(SheetName);
  12.             return wsheet;
  13.         }
  14.     }
  15.  
  16.     if (AddSheetIfNotFound)
  17.     {
  18.         wsheet = (Excel.Worksheet)wbook.Sheets.Add();
  19.         wsheet.Name = SheetName;
  20.         return wsheet;
  21.     }
  22.     return wsheet;
  23. }
GeSHi ©
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland

Re: Excel and .NET Integration

Postby tunc » 27 Aug 2014, 16:43

How to add a ListObject (excel table) to a Worksheet:
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. public Excel.ListObject WriteToExcelTable(Excel.Worksheet WSheet, string TableName, string CellStr = "A1", bool ClearSheetContent = false)
  2. {
  3.     Excel.Range range;
  4.  
  5.     if (ClearSheetContent)
  6.         WSheet.Cells.ClearContents();  // clear sheet content
  7.  
  8.     // get upper left corner of range defined by CellStr
  9.     range = (Excel.Range)WSheet.get_Range(CellStr).Cells[1, 1];   //
  10.  
  11.     // Write table to range
  12.     HelperFunc.WriteTableToExcelSheet(WSheet, this._tbl, range.Address);
  13.  
  14.     // derive range for table, +1 row for table header
  15.     range = range.get_Resize(this.RowCount + 1, this.ColumnCount);
  16.  
  17.     // add ListObject to sheet
  18.  
  19.     // ListObjects.AddEx Method
  20.     // http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.listobjects.addex%28v=office.14%29.aspx
  21.  
  22.     Excel.ListObject tbl = (Excel.ListObject)WSheet.ListObjects.AddEx(
  23.         SourceType: Excel.XlListObjectSourceType.xlSrcRange,
  24.         Source: range,
  25.         XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);
  26.  
  27.     // set name of excel table
  28.     tbl.Name = TableName;
  29.  
  30.     // return excel table (ListObject)
  31.     return (Excel.ListObject)tbl;
  32. }
GeSHi ©
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 2 guests

cron