Topic: Best way to communicate with Excel

[kaefer]

Thanks Gile for the update.

I should very much coco!

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?

I think it has more do to with you using the first row when there are Column.ColumnName properties for that, accessible through the DataTable.Columns collection.

[vegbruiser]

You are right, but for reasons I'm not entirely clear on, I still end up with the first row looking like this:

 

[gile]

Hi,

Have a look here: the ToAcadTable() method in the Extensions class creates an AutoCAD table with a DataTable content.

[sybold]

is this latebinding excel reading writing possible in vb, was searching for it and only got back c# results.
i now have little reading script with earlybinding, but having it excel version independent is a nice feature.

[gile]

Yes it is.
You can use an automatic converter as this one:
http://www.developerfusion.com/tools/convert/csharp-to-vb/
but convert the code by yourself may be a good exercice.

[vegbruiser]

This:

Code: [Select]

/// <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.

would a check to see if array[rowMin,i].ToString() returns null be a suitable solution?

Thanks,

Alex.

[n.yuan]

                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++;
                }
           

Have you actually run this code?

System.Data.DataTable.RowCollection DOES NOT have a Add() method without argument. That is, one cannot call

table.Rows.Add() to create a DataRow in DataTable.Rows (DataRowCollection object). One cannot treat a DataTable object as a table in AutoCAD or in speadsheet.

To add DataRow to DataTable (assume the DataTable already has its columns defined, usually:

//Create DataRow object
DataRow row=table.NewRow();

//Set Row field value here if needed
....

//Add DaraRow to DataTable
table.Rows.Add(row);

[fixo]

This:

Code: [Select]

/// <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.

would a check to see if array[rowMin,i].ToString() returns null be a suitable solution?

Thanks,

Alex.

Thought you easier do your work with (List of T)
Try this code

Code: [Select]

        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;
        }
~'J'~

[kaefer]

This:

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.

Thought you easier do your work with (List of T)

Sorry, fixo.

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.

And sorry, Alex,

there's little point too in having columns without header, either because they're empty or because they happen to be part of a merged cell, so throwing some kind of exception could be regarded as the right course of action here.

As for the DataRowCollection.Add method, an approximation of no arguments would be to give it an empty item array:

Code: [Select]

dataTable.Rows.Add(new object[0]);

Cheers!

_{Edited: See declaration, public System.Data.DataRow Add(params object[ ] values)
    Member of System.Data.DataRowCollection}

[vegbruiser]

Thanks Kaefer, and thanks fixo/n.yuan - that code was copied verbatim from the ExcelStreamDynamic solution and 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. 

[kaefer]

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. 

See C# Language specification

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, [...]

[TheMaster]

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.

Unless I've missed something, at the other end of the'pipeline' sits:

Code: [Select]


   DataRowCollection.Add( object[] )


which can add the data for an entire row to the table in one call,
rather than requiring one call to row[column-index] = value
for each cell, along with the associated overhead (e.g., it fires events
for each use of the indexer's set method, which involves additional overhead
even if the events have no listeners).

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.

Of course, it will ultimately depend on where the data is coming from, if it can
be gotten as a collection of arrays rather than a 2D array without far greater
cost, how many columns there are, and on whether merged cells are involved.

[kaefer]

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).

Code - F#: [Select]

1. type System.Data.DataTable with
2.     static member OfArray2D o =
3.         let dt = new System.Data.DataTable()
4.         if o <> null then
5.             let a =
6.                 match box o with
7.                 | :? (obj[,]) as a -> a
8.                 | o -> Array2D.create 1 1 o
9.             let jbase = a.GetLowerBound 0
10.             let jrange = {jbase + 1 .. a.GetUpperBound 0}
11.             let irange = {a.GetLowerBound 1 .. a.GetUpperBound 1}
12.             for i in irange do
13.                 dt.Columns.Add(
14.                     string a.[jbase, i],
15.                     typeof<obj> )
16.                 |> ignore
17.             for j in jrange do
18.                 dt.Rows.Add[|
19.                     for i in irange -> a.[j, i] |]
20.                 |> ignore
21.         dt

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[]).

[TheMaster]

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?

[kaefer]

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?