time write1000Lines2DArray // 00:00:00.7738569
time write1000LinesStream // 00:00:02.3321666
open ExcelLateBinding
let time f =
let sw = System.Diagnostics.Stopwatch.StartNew()
try f() finally sw.Stop()
printfn "%A" sw.Elapsed
let write1000LinesStream() =
use xlw = new ExcelWriter(@"C:\TEMP\1000")
for i in 'A' .. 'F' do
xlw.Write(string i)
xlw.NewLine()
for j in 1 .. 1000 do
xlw.WriteLine[| box j |]
xlw.Save()
let write1000Lines2DArray() =
use xlw = new ExcelWriter(@"C:\TEMP\1001")
let a : obj[,] = Array2D.create 1001 6 null
for (i, c) in {'A' .. 'F'} |> Seq.mapi(fun i v -> i, v) do
a.[0, i] <- box(string c)
for j in 1 .. 1000 do
a.[j, 0] <- box j
let r =
xlw.Worksheet.Get(
"Range",
xlw.Worksheet.Get("Cells", 1, 1),
xlw.Worksheet.Get("Cells", a.GetLength 0, a.GetLength 1))
r.Set("Value2", a)
xlw.Save()
Having looked at the ExcelReader Class would a ReadRange() method be possible (is it even worth it?) and do you think it would give (seemingly) the same efficiencies as WriteRange()?
Isn't it a time to digg into OpenXML API? | :) |
object range = Worksheet.Get("Range",
Worksheet.Get("Cells", _rowIndex, _columnIndex),
Worksheet.Get("Cells", _rowIndex + rows, _columnIndex + cols));
dynamic range = Worksheet.Get("Range",
Worksheet.Get("Cells", _rowIndex, _columnIndex),
Worksheet.Get("Cells", _rowIndex + rows, _columnIndex + cols));
'object' does not contain a definition for 'Set' and no extension method 'Set' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)
I'm no expert but shouldn't this:Code: [Select]object range = Worksheet.Get("Range",
Worksheet.Get("Cells", _rowIndex, _columnIndex),
Worksheet.Get("Cells", _rowIndex + rows, _columnIndex + cols));
be like this:Code: [Select]dynamic range = Worksheet.Get("Range",
Worksheet.Get("Cells", _rowIndex, _columnIndex),
Worksheet.Get("Cells", _rowIndex + rows, _columnIndex + cols));
If I use object instead of dynamic I get an error thus:Quote'object' does not contain a definition for 'Set' and no extension method 'Set' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)
But using dynamic crashes AutoCAD?
:?
dynamic range = Worksheet.Range[
Worksheet.Cells[_rowIndex, _columnIndex],
Worksheet.Cells[_rowIndex + rows, _columnIndex + cols]];
range.Value2 = values;
That explains why it didn't work for me then - I only added it to the Dynamic version, not the late-binding one.I'm no expert but shouldn't this:Code: [Select]object range = Worksheet.Get("Range",
Worksheet.Get("Cells", _rowIndex, _columnIndex),
Worksheet.Get("Cells", _rowIndex + rows, _columnIndex + cols));
be like this:Code: [Select]dynamic range = Worksheet.Get("Range",
Worksheet.Get("Cells", _rowIndex, _columnIndex),
Worksheet.Get("Cells", _rowIndex + rows, _columnIndex + cols));
If I use object instead of dynamic I get an error thus:Quote'object' does not contain a definition for 'Set' and no extension method 'Set' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)
But using dynamic crashes AutoCAD?
:?
The WriteRange() method have to be added to the ExcelWriter class (in ExcelStream_LateBinding (http://www.theswamp.org/index.php?topic=31867.msg461433#msg461433)) which uses the LateBinding class where the Set() extension method is defined.
If you use ExcelStream_Dynamic (http://www.theswamp.org/index.php?topic=31867.msg461447#msg461447) which uses the dynamic type instead of late binding, you have to write:Code: [Select]dynamic range = Worksheet.Range[
Worksheet.Cells[_rowIndex, _columnIndex],
Worksheet.Cells[_rowIndex + rows, _columnIndex + cols]];
range.Value2 = values;
During this week-end I'll try to update the codes.
I added some overloaded ExcelReader.ReadRange() and ExcelWriter.WriteRange() methods and modify the ExcelReader.ReadToEnd() method
As shown in some examples other there I think using an intermediate DataTable instance is interesting when dealing from (or to) block attributes and Excel (or AutoCAD) table.
DataTable has other interesting options:For sure !
Relations via DataSet
Filtering/Viewing via DataView
Support for Linq queries, e.g. Intersect; most easily by LINQ to DataSet Extensions
/// <summary>
/// Opens an Excel file using the ExcelReader class
/// reads the values (from a specific sheet if necessary)
/// creates an AutoCAD Table with the values
/// Originally copied from here: http://bit.ly/wtBLVZ
/// </summary>
[CommandMethod("test4")]
public void test4()
{
Document doc = Application.DocumentManager.MdiActiveDocument;
Database db = doc.Database;
Editor ed = doc.Editor;
Stopwatch sw = Stopwatch.StartNew();
//object[][] values;
System.Data.DataTable table;
using (ExcelReader xlr = new ExcelReader(@"C:\temp\test1.xls"))
{
table= xlr.RangeToDataTable(1,1);
}
if (table == null)
return;
using (Transaction tr = db.TransactionManager.StartTransaction())
{
BlockTable bt = db.BlockTableId.GetObject(OpenMode.ForRead) as BlockTable;
BlockTableRecord btrMs = (BlockTableRecord)bt[BlockTableRecord.ModelSpace].GetObject(OpenMode.ForRead);
Table bomTable = new Table();
bomTable.TableStyle = db.Tablestyle;
bomTable.Position = Point3d.Origin;
TableStyle ts = (TableStyle)tr.GetObject(bomTable.TableStyle, OpenMode.ForRead);
double txtHeight = ts.TextHeight(RowType.DataRow);
//add the correct number of rows & columns
bomTable.InsertColumns(1,1,table.Columns.Count);
bomTable.InsertRows(1,1,table.Rows.Count);
//write the header row.
DataRow row = table.Rows[1];
for (int i = 0; i < table.Columns.Count; i++)
{
bomTable.Cells[0, i].TextString = table.Rows[0][i].ToString();
}
// then ALL the data rows
for (int i = 1; i < table.Rows.Count; i++)
{
for (int j = 1; j < table.Columns.Count; j++)
{
bomTable.Cells[i, j].TextString = table.Rows[i][j].ToString();
}
}
bomTable.GenerateLayout();
btrMs.UpgradeOpen();
btrMs.AppendEntity(bomTable);
tr.AddNewlyCreatedDBObject(bomTable, true);
tr.Commit();
}
sw.Stop();
AcadApp.DocumentManager.MdiActiveDocument.Editor.WriteMessage(
"\nPasteFromClipboard: {0}", sw.Elapsed.TotalMilliseconds);
}
Thanks Gile for the update.
It has an issue to do with the column heads and rows within the AutoCAD table itself, but I think that's due to my not having a style setup in the .dwg file already?
/// <summary>
/// Converts an array to a DataTable
/// </summary>
/// <param name="array"></param>
/// <returns></returns>
public static System.Data.DataTable ToDataTable(this object[,] array)
{
System.Data.DataTable table = new System.Data.DataTable();
if (array != null)
{
int rowMin = array.GetLowerBound(0), rowMax = array.GetUpperBound(0);
int colMin = array.GetLowerBound(1), colMax = array.GetUpperBound(1);
int row = 0;
for (int i = colMin; i <= colMax; i++)
{
table.Columns.Add(array[rowMin,i].ToString(), typeof(object));
}
for (int i = rowMin + 1; i <= rowMax; i++)
{
table.Rows.Add();
for (int j = colMin; j <= colMax; j++)
{
table.Rows[row][j - colMin] = array[i,j];
}
row++;
}
}
return table;
}
errors when there are merged cells as it can't do anything with the null value of array[rowMin,i].ToString() which is returned from the merged "empty" cells.
for (int i = rowMin + 1; i <= rowMax; i++)
{
table.Rows.Add();
for (int j = colMin; j <= colMax; j++)
{
table.Rows[row][j - colMin] = array[i,j];
}
row++;
}
This:Thought you easier do your work with (List of T)Code: [Select]/// <summary>
errors when there are merged cells as it can't do anything with the null value of array[rowMin,i].ToString() which is returned from the merged "empty" cells.
/// Converts an array to a DataTable
/// </summary>
/// <param name="array"></param>
/// <returns></returns>
public static System.Data.DataTable ToDataTable(this object[,] array)
{
System.Data.DataTable table = new System.Data.DataTable();
if (array != null)
{
int rowMin = array.GetLowerBound(0), rowMax = array.GetUpperBound(0);
int colMin = array.GetLowerBound(1), colMax = array.GetUpperBound(1);
int row = 0;
for (int i = colMin; i <= colMax; i++)
{
table.Columns.Add(array[rowMin,i].ToString(), typeof(object));
}
for (int i = rowMin + 1; i <= rowMax; i++)
{
table.Rows.Add();
for (int j = colMin; j <= colMax; j++)
{
table.Rows[row][j - colMin] = array[i,j];
}
row++;
}
}
return table;
}
would a check to see if array[rowMin,i].ToString() returns null be a suitable solution?
Thanks,
Alex.
public static System.Data.DataTable PopupTable(List<object[]> tabledata)
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.BeginLoadData();
object[] headers = tabledata[0];
/* - add columns - */
foreach (object obj in tabledata[0])
{
dt.Columns.Add(obj.ToString(), typeof(string));//change on the type you need
}
List<object[]> restdata = tabledata.Skip(1).ToList<object[]>();
/* - fill the table - */
foreach (object[] arr in tabledata)
{
DataRow dr = dt.NewRow();
dr.BeginEdit();
int i = 0;
foreach (object obj in arr)
{
dr[i] = obj;
i += 1;
}
dr.EndEdit();
dt.Rows.Add(dr);
}
dt.EndLoadData();
dt.AcceptChanges();
return dt;
}
This:Thought you easier do your work with (List of T)Code: [Select]table.Columns.Add(array[rowMin,i].ToString(), typeof(object));
errors when there are merged cells as it can't do anything with the null value of array[rowMin,i].ToString() which is returned from the merged "empty" cells.
dataTable.Rows.Add(new object[0]);
it works fine for me with no argument - I'm not sure why though as the overloads clearly state that it requires an input of some description. :?
10.6.1.4 Parameter arrays
[...]
A parameter array permits arguments to be specified in one of two ways in a method invocation:
The argument given for a parameter array can be a single expression that is implicitly convertible (§6.1) to the parameter array type. In this case, the parameter array acts precisely like a value parameter.
Alternatively, the invocation can specify zero or more arguments for the parameter array, [...]
It would've been easier, but that's not the idea here. When doing interop with big chunks of data to transfer, you'll need to handle them as a two-dimensional array. There's little point in intermediate conversion, either to jagged array or to generic list, when at the other end of the pipeline sits System.Data.DataTable.
DataRowCollection.Add( object[] )
So if given the choice, I would prefer to have the data in the form
of a list of arrays, with each array holding one row's data, and then
pass each of those to Add(), requiring only one call for each row.
So if given the choice, I would prefer to have the data in the form
of a list of arrays, with each array holding one row's data, and then
pass each of those to Add(), requiring only one call for each row.
It got somehow lost in translation (cf. lines 18-19).
The starting point was the relative slowness of iterating linewise/recordwise over an Excel spreadsheet, as opposed to access by object[,]. I doubt very much that another substantial speed increase can be achieved by this particular optimization (i.e. temporary object[]).
Sorry, what temporary object[] is that?
Sorry, what temporary object[] is that?
That would be this then:Code - C#: [Select]
public static System.Data.DataTable ToDataTable(this object[,] array) { if (array != null) { int rowMin = array.GetLowerBound(0), rowMax = array.GetUpperBound(0); int colMin = array.GetLowerBound(1), colMax = array.GetUpperBound(1); for (int i = colMin; i <= colMax; i++) { } for (int i = rowMin + 1; i <= rowMax; i++) { for (int j = colMin; j <= colMax; j++) { items[j - colMin] = array[i, j]; } table.Rows.Add(items); } } return table; }
You could argue that it is an intermediate data structure, which appears to have a point. Would anyone care to time the difference?
.....Would anyone care to time the difference?
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