cell auto format when inserting date

[Dmitry]

Hi all!

In VBA macro I can do 

Cells(1, 1).Value = Date

then in cell A1 will be date, formatted according to you locale.

But if you will use  exceldna like so (c#):

ExcelReference insertRange = new ExcelReference(0, 0);

insertRange.SetValue(DateTime.Now);

then in cell A1 will be number (date in "General" format).

How to insert data with exceldna with auto-format, like in VBA example?

Thx!

[Govert van Drimmelen]

Hi Dmitry,

You could use COM Automation interfaces to push your data into Excel - then it will work exactly like the VBA. 

With the C API (which Excel-DNA uses) there is no direct interpretation of DateTimes - these just get set as the corresponding double.

The C API call to set the formatting would look like this:

    // Select a cell on the active sheet

    XlCall.Excel(XlCall.xlcFormulaGoto, refToSelect);

    

    // Set the selected cell's format

    XlCall.Excel(XlCall.xlcFormatNumber, desiredFormat)

It's a bit inconvenient, and if the reference is not on the active sheet, you first have to activate the corresponding sheet. There's a helper class called ExcelSelectionHelper in the sample Distribution\Samples\Async\AsyncMacros.dna which allow you to say:

                        // Set the formatting of the function caller

                        using (new ExcelSelectionHelper(myReference)

                        {

                            XlCall.Excel(XlCall.xlcFormatNumber, desiredFormat);

                        }

this also restores the selection after the call.

But for most cases, for a macro that writes out a report to Excel based on a ribbon click or something, I'd suggest using the COM Automation interfaces.

The everything is pretty much the same as in VBA. (Just remember to get the root Application object via ExcelDnaUtil.Application.)

Regards,

Govert

[Dmitry]

Thx, Govert!

Let me share working solution after all:   

...

using Excel = Microsoft.Office.Interop.Excel; // also you need to add reference to Microsoft Excel Object Library (in com tab)

...   

 private static void InsertArrayToExcel_COM(string sheet_name, string book_name, int row, int col,   object[,] array_to_insert)

        {

            ExcelAsyncUtil.QueueAsMacro(delegate

            {

                int row_count = array_to_insert.GetLength(0);

                int col_count = array_to_insert.GetLength(1);

                Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;

                Excel.Workbook wb = (Excel.Workbook)app.Workbooks[book_name];

                Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[sheet_name];                

                Excel.Range r = (Excel.Range)ws.get_Range(ws.Cells[row, col], ws.Cells[row + row_count - 1, col + col_count-1]);

                r.set_Value(System.Type.Missing, array_to_insert);                

            });

        }

[Gautam Bhatnagar]

Hi Dmitry, I'm trying to do the same thing.

how do you call this from within Excel-DNA?  I can't seem to get access to the sheet and workbook name, only the sheet id?