Author Topic: Best way to communicate with Excel  (Read 14742 times)

0 Members and 1 Guest are viewing this topic.

TheMaster

  • Guest
Re: Best way to communicate with Excel
« Reply #30 on: March 21, 2012, 08:26:31 AM »
Sorry, what temporary object[] is that?

That would be this then:
Code - C#: [Select]
  1.         public static System.Data.DataTable ToDataTable(this object[,] array)
  2.         {
  3.             System.Data.DataTable table = new System.Data.DataTable();
  4.             if (array != null)
  5.             {
  6.                 int rowMin = array.GetLowerBound(0), rowMax = array.GetUpperBound(0);
  7.                 int colMin = array.GetLowerBound(1), colMax = array.GetUpperBound(1);
  8.                 object[] items = new object[colMax - colMin + 1];
  9.  
  10.                 for (int i = colMin; i <= colMax; i++)
  11.                 {
  12.                     table.Columns.Add(array[rowMin, i].ToString(), typeof(object));
  13.                 }
  14.                 for (int i = rowMin + 1; i <= rowMax; i++)
  15.                 {
  16.                     for (int j = colMin; j <= colMax; j++)
  17.                     {
  18.                         items[j - colMin] = array[i, j];
  19.                     }
  20.                     table.Rows.Add(items);
  21.                 }
  22.             }
  23.             return table;
  24.         }
  25.  

You could argue that it is an intermediate data structure, which appears to have a point. Would anyone care to time the difference?

As I mentioned previously, it depends on where the data comes from
and what form it has initially. If the data comes from excel and it has
to be gotten one cell at a time, there's not much difference between
putting it in a 2D array or a List/array of single-dimensional arrays, but
if the data is coming over as a 2D array then it could be a wash.

« Last Edit: March 21, 2012, 08:33:04 AM by TheMaster »

TheMaster

  • Guest
Re: Best way to communicate with Excel
« Reply #31 on: March 21, 2012, 11:53:27 AM »

  .....Would anyone care to time the difference?


In case anyone would like to try it, here's my take:

Code: [Select]

using System;
using System.Linq;
using System.Data;
using System.Diagnostics;
using System.Threading;

namespace DataTableTesting
{

public static class Tests
{

  public static void Run()
  {

    // These numbers were used on a very
    // slow machine to get timings > 100 ms.
    // You should adjust them as needed to
    // get results that are at least 100 ms.
    //
    // The dimensions ware adjusted uniformly,
    // maintaining a row-to-column ratio of
    // 100:1.
   
    int rows = 5000;
    int cols = 50;

    // The time required to generate the
    // source data is not measured:
   
    List<object[]> list = GetRowArrayList( rows, cols );
   
    object[,] array = GetCellDataArray( rows, cols );

    // Times loading the DataTable cell by cell,
    // from a 2D array:
   
    Chrono.Run( "LoadDataTableByCell()", 5,
      () => LoadDataTableByCell( rows, cols, array ) );

    // Times loading the DataTable row by row,
    // from a list of one-dimensional arrays:
   
    Chrono.Run( "LoadDataTableByRow()", 5,
      () => LoadDataTableByRow( rows, cols, list ) );

    // Times loading the DataTable row by row,
    // from a 2D array:
   
    Chrono.Run( "LoadDataTableByRowFromArray()", 5,
      () => LoadDataTableByRowFromArray( rows, cols, array ) );
  }
 

  public static DataTable LoadDataTableByCell( int rows, int cols, object[,] array )
  {
    DataTable table = new DataTable();
    DataColumnCollection columns = table.Columns;
    table.BeginLoadData();
    for( int i = 0; i < cols; i++ )
    {
      columns.Add( string.Format("Column{0}", i), typeof(object) );
    }
    for( int i = 0; i < rows; i++ )
    {
      DataRow row = table.NewRow();
      for( int j = 0; j < cols; j++ )
      {
        row[j] = array[j, i];   
      }
      table.Rows.Add( row );
    }
    table.EndLoadData();
    return table;
  }

  public static DataTable LoadDataTableByRow( int rows, int cols, List<object[]> rowList )
  {
    DataTable table = new DataTable();
    DataColumnCollection columns = table.Columns;
    table.BeginLoadData();
    for(int i = 0; i < cols; i++ )
    {
      columns.Add( string.Format("Column{0}", i), typeof(object) );
    }

    for( int i = 0; i < rows; i++ )
    {
      table.Rows.Add( rowList[i] );   
    }
    table.EndLoadData();
    return table;
  }

  public static DataTable LoadDataTableByRowFromArray( int rows, int cols, object[,] array )
  {
    DataTable table = new DataTable();
    DataColumnCollection columns = table.Columns;
    table.BeginLoadData();
    for( int i = 0; i < cols; i++ )
    {
      columns.Add( string.Format("Column{0}", i), typeof(object) );
    }
    for( int i = 0; i < rows; i++ )
    {
      object[] rowArray = new object[cols];
      for( int j = 0; j < cols; j++ )
      {
        rowArray[j] = array[j, i];   
      }
      table.Rows.Add( rowArray );
    }
    table.EndLoadData();
    return table;
  }
 
  // Returns a List<object[column-count]>( row-count ):
  public static List<object[]> GetRowArrayList( int rows, int cols )
  {
    List<object[]> list = new List<object[]>( rows );
    for( int j = 0; j < rows; j++ )
    {
      object[] array = new object[cols];
      for( int i = 0; i < cols ; i++ )
      {
        array[i] = string.Format("Cell[{0},{1}]", i, j );
      }
      list.Add( array );
    }
    return list;
  }

  // Returns an array[column-count,row-count]:
  public static object[,] GetCellDataArray( int rows, int cols )
  {
    object[,] array = new object[cols, rows];
    for(int i = 0; i < cols ; i++ )
    {
      for(int j = 0; j < rows ; j++ )
      {
        array[i, j] = string.Format("Cell[{0},{1}]", i, j );
      }
    }
    return array;
  }
 
}


//////////// Benchmarking code /////////////////////////////////////


public static class Chrono
{
  public static List<double> Run( int iterations, Action action )
  {
    return Run( iterations, true, null, GCBehavior.Default, action );
  }
 
  public static List<double> Run( string desc, int iterations, Action action )
  {
    return Run( iterations, true, desc, GCBehavior.Default, action );
  }
 
  public static List<double> Run( int iterations, bool dump, string desc, GCBehavior collect, Action action )
  {
    Stopwatch stopWatch = new Stopwatch();
    IntPtr affinity = Process.GetCurrentProcess().ProcessorAffinity;
    ThreadPriority priority = Thread.CurrentThread.Priority;
    List<double> results = new List<double>( iterations );
    try
    {
      // Use the second Core or Processor for the Test.
      // Not applicable on single-core computers, so we
      // catch and ignore the exception that happens in
      // that case:
     
      Process.GetCurrentProcess().ProcessorAffinity = new IntPtr(2);
    }
    catch{}
   
    // Minimize interruptions from "Normal" processes
    // (don't set this to "Highest" on single-core machines!!!):
   
    Thread.CurrentThread.Priority = ThreadPriority.AboveNormal;   
   
    try
    {
      // A Warmup of 1000-1500 ms stabilizes the CPU cache and pipeline,
      // and ensures that the test code is fully-JIT'ed before actual
      // measurement starts (we don't want to measure how long it takes
      // to JIT the code. which happens the first time it executes, on
      // a per-method basis)
     
      stopWatch.Reset();
      stopWatch.Start();

      while( stopWatch.ElapsedMilliseconds < 1200 ) 
      {
        action(); // Warmup
      }
      stopWatch.Stop();
      stopWatch.Reset();
     
      Collect();
     
      // If GCBehavior is Inclusive, it means
      // a full GC is integral and the collection
      // time is measured.
      // The Inclusive option is not recommended for
      // most routine timings, and is provided only
      // to observe/experiment with GC behavior.
      //
      // If GCBehavior.Exclusive is specified, then a
      // full collection is done on each iteration, but
      // the collection time is not incorporated into
      // execution timing.
     
      Action expr = null;
      if( collect == GCBehavior.Inclusive )
        expr = () => {action(); Collect();};
      else
        expr = action;
      for( int i = 0; i < iterations; i++ )
      {
        stopWatch.Reset();
        if( collect == GCBehavior.Exclusive )
          Collect();
        stopWatch.Start();
        expr();
        stopWatch.Stop();
        results.Add( stopWatch.Elapsed.TotalMilliseconds);
      }
    }
    finally
    {
      Thread.CurrentThread.Priority = priority;
      Process.GetCurrentProcess().ProcessorAffinity = affinity;
    }   
    if( dump )
    {
      Dump( results, desc );
    }
    return results;
  }
 
  static void Collect()
  {
    GC.Collect( GC.MaxGeneration );
    GC.WaitForPendingFinalizers();
  }
 
  // returns true if this is a DEBUG build
  // (e.g., no optimizations, or a release
  // build with a debugger attached):
 
  static bool IsDebug()
  {
#if DEBUG
    return true;
#endif
    return Debugger.IsAttached;
  }
 
  static void ShowBuild()
  {
    if( IsDebug() )
    {
      Console.WriteLine( "(Testing in {0})", IsDebug() ?
        "DEBUG build or with debugger attached" :
        "release build"
      );
    }   
  }
 
  public static void Dump( List<double> results )
  {
    Dump( results, null );
  }
 
  public static void Dump( List<double> results, string label )
  {
    double[] array = results.ToArray();
    double min = array.Min();
    double max = array.Max();
    double tot = array.Sum();
    double avg = tot / results.Count;
    double dev = Math.Abs( max - min );
   
    Console.WriteLine("==================================");
    ShowBuild(); 
    if( ! string.IsNullOrEmpty( label ) )
      Console.WriteLine("Description: {0}", label );
    Console.WriteLine("Iterations: {0}", results.Count );
    Console.WriteLine("Avg: {0:F2} ms", avg );
    Console.WriteLine("Tot: {0:F2} ms", tot );
    Console.WriteLine("Max: {0:F2} ms", max );
    Console.WriteLine("Min: {0:F2} ms", min );
    Console.WriteLine("Dev: {0:F2} ms ({1:P})", dev, dev / avg );
    Console.WriteLine("------------------------------------");
  }
}

public enum GCBehavior
{
  Default = 0,
  Exclusive = 1,
  Inclusive = 2
}


} // namespace
« Last Edit: March 21, 2012, 12:19:07 PM by TheMaster »