MATLAB Central Newsreader - Using the Microsoft Office Spreadsheet object

Using the Microsoft Office Spreadsheet object

Conrad — Tue, 06 Oct 2009 22:07:03 -0400

Hi,

I'm trying to format an MS-Excel table from within Malab and I'm a bit stuck at the moment. I've managed to create the table using 'OWC11.Spreadsheet.11' and I have populated it. I don't know how to format the table from within Matlab as one would from an excel macro using the following commands:

  Range("A1:V26").Select
  Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

I also want to draw some lines between different columns as shown in the following excel macro example:

   Range("B1:B26").Select
   Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

Any help on this would be appreciated.Thanks

Re: Using the Microsoft Office Spreadsheet object

Cynthia — Tue, 06 Oct 2009 23:57:02 -0400

Conrad,

There's an example of how to draw borders around cells in this post:
http://www.mathworks.com/matlabcentral/fileexchange/6889

Cindy

"Conrad " <conrad7@gmx.net> wrote in message <hagf27$fhi$1@fred.mathworks.com>...
> Hi,
>
> I'm trying to format an MS-Excel table from within Malab and I'm a bit stuck at the moment. I've managed to create the table using 'OWC11.Spreadsheet.11' and I have populated it. I don't know how to format the table from within Matlab as one would from an excel macro using the following commands:
>
> Range("A1:V26").Select
> Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
> True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
>
> I also want to draw some lines between different columns as shown in the following excel macro example:
>
> Range("B1:B26").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> Selection.Borders(xlEdgeLeft).LineStyle = xlNone
> With Selection.Borders(xlEdgeRight)
> .LineStyle = xlContinuous
> .Weight = xlThin
> .ColorIndex = xlAutomatic
> End With
>
> Any help on this would be appreciated.Thanks

Re: Using the Microsoft Office Spreadsheet object

Conrad — Wed, 07 Oct 2009 14:39:02 -0400

Thanks Cynthia. I actually used this file to get a lot of what I wanted to do but I can't find any instance where the autoformat features of excel are used...
Does anyone know a link, maybe Microsoft where I can get the info that i need?

"Cynthia " <remove.this.cynthia.bell@asu.edu> wrote in message <haglge$5m$1@fred.mathworks.com>...
> Conrad,
>
> There's an example of how to draw borders around cells in this post:
> http://www.mathworks.com/matlabcentral/fileexchange/6889
>
> Cindy
>
>
>
> "Conrad " <conrad7@gmx.net> wrote in message <hagf27$fhi$1@fred.mathworks.com>...
> > Hi,
> >
> > I'm trying to format an MS-Excel table from within Malab and I'm a bit stuck at the moment. I've managed to create the table using 'OWC11.Spreadsheet.11' and I have populated it. I don't know how to format the table from within Matlab as one would from an excel macro using the following commands:
> >
> > Range("A1:V26").Select
> > Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
> > True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
> >
> > I also want to draw some lines between different columns as shown in the following excel macro example:
> >
> > Range("B1:B26").Select
> > Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> > Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> > Selection.Borders(xlEdgeLeft).LineStyle = xlNone
> > With Selection.Borders(xlEdgeRight)
> > .LineStyle = xlContinuous
> > .Weight = xlThin
> > .ColorIndex = xlAutomatic
> > End With
> >
> > Any help on this would be appreciated.Thanks

Re: Using the Microsoft Office Spreadsheet object

ImageAnalyst — Wed, 07 Oct 2009 14:54:36 -0400

Cynthia:
I had my contractor do this for me. He wasn't very familiar with
MATLAB at the time so he did it in VIsual Basic. Below is the main
part of the code. I'm sure you can use the same methods but just part
of your Excel object in MATLAB rather than VB. The Excel mathod
apparently expects a filename so if you have an array in MATLAB, you
may have to save it out to a disk file first.
Good luck,
ImageAnalyst

Imports System.Runtime.InteropServices

Module modAddPicture

    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="in_strImageFilepath"></param>
    ''' <param name="in_objWksheet"></param>
    ''' <param name="in_objCell"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertPicture(ByRef in_strImageFilepath As String,
ByVal in_objWksheet As Excel.Worksheet, ByVal in_objCell As
Excel.Range) As Integer

        Dim objPicture As Object
        objPicture = in_objWksheet.Pictures.Insert
(in_strImageFilepath)

        objPicture.Top = in_objCell.Top + 1
        objPicture.Left = in_objCell.Left + 1

        objPicture.ShapeRange.LockAspectRatio =
mso.MsoTriState.msoTrue
        objPicture.ShapeRange.Width = in_objCell.Width - 2
        ' objPicture.ShapeRange.Height = in_objCell.RowHeight

        Return objPicture.ShapeRange.Height
    End Function

    ''' <summary>
    '''
    ''' </summary>
    ''' <param name="in_strImageFilepath"></param>
    ''' <param name="objWksheet"></param>
    ''' <param name="imgNumber"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertPictureLarge(ByRef in_strImageFilepath As
String, ByVal objWksheet As Excel.Worksheet, ByVal imgNumber As
Integer) As Object
        Dim rowHeight As Integer = 19
        Dim row As Integer = 5 + (imgNumber * (rowHeight + 1))
        Dim cellString As String
        cellString = String.Format("E{0}", row)
        Dim objCell As Excel.Range = objWksheet.Range(cellString)

        Dim objPicture As Object
        objPicture = objWksheet.Pictures.Insert(in_strImageFilepath)

        objPicture.Top = objCell.Top
        objPicture.Left = objCell.Left

        objPicture.ShapeRange.LockAspectRatio =
mso.MsoTriState.msoTrue
        objPicture.ShapeRange.Height = objCell.RowHeight * rowHeight

        Return objPicture
    End Function

End Module