Matlab tip -- finding the indices of the elements of one array in another

Suppose all elements in array1 are contained in array2, now we want to get the indices of array1's elements in array2.

Here is a simple and neat solution, using Matlab's arrayfun function, which applies a function to each element of an array.

array1=[1,6,2,3,7];
array2=[3,6];
arrayfun(@(x)find(array1==x,1),array2)

The output is:
ans =
      4    2

Read More

Control Excel chart position with row and column numbers in Matlab

Sometimes we want to align, in Excel spreadsheet, a chart with a cell which, for example, may contain a label for the chart. We can easily drag (with mouse) the chart to make it aligned with the cell, but it may not be so easy to do this by program. The challenge here is that in Excel spreadsheet a cell position is expressed in row and columns, while a chart position is expressed in points. Depending on the screen resolution, the number of points corresponding to one row or column may vary on different computers. When we want to insert a large amount of charts into a sheet by program, exact positioning of these charts will be a headache. This tutorial teaches you how to programmingly obtain and position the chart in terms of row/column numbers.

1. Determine the chart position in terms of rows
A VBA chart Object has properties TopLeftCell and BottomRightCell which return the cells in the upper left and lower right corners of the chart, respectively. For example, following VBA code is used to retrieve the row property of cells pertaining to the chart position:

Dim TopRow As Long
Dim BottomRow As Long
With ActiveSheet.ChartObjects(1)
    TopRow = .TopLeftCell.Row
    BottomRow = .BottomRightCell.Row
End With



2. Determine the cell position in terms of points
We can use the Left and Top properties to determine the distances (of a cell, row or column) to the left and top boundaries of the spreadsheet, in the unit of points. Here is an example using VBA code

Worksheets("Sheet1").Range("A5").Top
Worksheets("Sheet1").Range("B7").Left
Worksheets("Sheet1").Columns(6).Left
Worksheets("Sheet1").Rows(11).Top


Following Matlab code shows how to insert 10 charts into an excel spreadsheet, each having a figure number printed in a cell beside it:

ObjExcel = actxserver('Excel.Application');
TheWorkbook = ObjExcel.Workbooks.Open('example.xls');
DataSheet = TheWorkbook.Sheets.Item('Data');
RowLastChartBottom = 0;
for i = 1 : 10
    RowChartTitle = RowLastChartBottom + 2;
    DataSheet.Range('A', num2str(RowChartTitle)).Value = ['Chart ', num2str(i)];
    %---
    RowChartTop = RowChartTitle + 1;
    PointsChartTop = ChartSheet.Range(['B', num2str(RowChartTop)]).Top;
    ChartObject = ChartSheet.ChartObjects.Add(10, PointsChartTop, 1000, 250);
    ChartObject.Chart.HasTitle = 1;
    ChartObject.Chart.ChartTitle.Text = '......';
    ChartObject.Chart.Axes.Item(1).TickLabelPosition = 'xlTickLabelPositionLow';
    %---
    CurveObject = ChartObject.Chart.SeriesCollection.NewSeries;
    XRange = DataSheet.Range(......);
    YRange = DataSheet.Range(......);
    CurveObject.XValues = XRange;
    CurveObject.Values = YRange;
    CurveObject.ChartType = 'xlLineMarkers';
    CurveObject.MarkerSize = 2;
    CurveObject.Format.Line.Weight = 2;
    %---
    RowLastChartBottom = ChartObject.BottomRightCell.Row;
end

Read More

Import data from Microsoft SQL server to Matlab using JDBC driver

Since Matlab Visual Query Builder is a simple tool mainly for testing purpose, it is not recommended for setting up database connection within Matlab. This article talks about how to use Matlab Database toolbox functions to set up a connection to Microsoft SQL server database.

Environment

• Windows 7, 64-bit
• Matlab R2012a
• Microsoft SQL Server 2008

Installation

1. Download JDBC driver from Microsoft and install it so that Matlab is able to interact with SQL Server database via the Java interface.
• You can install the driver to a local folder, such as

  C:\Program Files\Microsoft SQL Server JDBC Driver 3.0

2. Insert the path of the database driver JAR file in the classpath.txt file
• The path file is located in the Matlab installation folder, such as

  C:\Program Files\MATLAB\R2012a\toolbox\local\classpath.txt

• A quick way to edit the path file is to run Matlab command:

  open('classpath.txt')

• Add the following line to the end of the file:
  

  C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\sqljdbc4.jar

3. Insert the path to the folder containing sqljdbc_auth.dll in the librarypath.txt file
• The path file is located in the Matlab installation folder, such as

  C:\Program Files\MATLAB\R2012a\toolbox\local\librarypath.txt

• A quick way to edit the path file is to run Matlab command:

  open('librarypath.txt')

• Add the following line to the end of the file:
  

  C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\auth\x64

  Note: The path entry should not include the file name sqljdbc_auth.dll.
• The sqljdbc_auth.dll file is installed in the following location:
  

  [installation]\sqljdbc_[version]\[language]\auth\[arch]

  where [installation] is the installation directory of the SQL server driver.
• If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version.
• If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder.
• If you are running a 64-bit JVM on a IA-64 processor, use the sqljdbc_auth.dll file in the IA64 folder.
4. Edit startup.m and add the following line to the end of the file:
   setdbprefs('DataReturnFormat','structure')
   • If you don't have a startup.m file, create one in your default Matlab directory:

     C:\users\user_name\Documents\MATLAB\

5. Notes:
   • If Matlab refuses to save these files, you may have to restart Matlab using Run as administrator and try again
   • Use path command to check whether following directories has been added to Matlab

     C:\Program Files\MATLAB\R2011a\toolbox\database\database

     C:\Program Files\MATLAB\R2011a\toolbox\database\dbdemos

     C:\Program Files\MATLAB\R2011a\toolbox\database\vqb

     If they aren't, type following commands to add them:

     addpath('C:\Program Files\MATLAB\R2011a\toolbox\database\database','-end');

     addpath('C:\Program Files\MATLAB\R2011a\toolbox\database\dbdemos','-end');

     addpath('C:\Program Files\MATLAB\R2011a\toolbox\database\vqb','-end');

Usage

• Use database function to connect to a MS SQL Server database, using following syntax
  conn = database(instance, username, password, driver, databaseurl)
  where the arguments, for connecting to MS SQL server with JDBC, are:
• Instance --- the name of the database, specified as a string
• username --- User name required to access database, specified as a string. If no user name is required, specify empty strings, ''.
• password --- Password required to access database, specified as a string. If no password is required, specify empty strings, ''.
• driver --- JDBC driver name, specified as a string. This is the name of the Java driver that implements the java.sql.Driver interface.
  For MS SQL server: JDBC Driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
• databaseurl --- Database connection URL, specified as a string. This is a vendor-specific URL that is typically constructed using connection properties like server name, port number, database name, and so on.
  For MS SQL server, database URL = jdbc:sqlserver://localhost:port;database=databasename
  Note: 1433 is a commonly used default port number.
• One example of Matlab code

  % Make connection to database using JDBC driver.
  conn = database('MARKET_DATA_NYMEX','username','password', …
  'com.microsoft.sqlserver.jdbc.SQLServerDriver', …
  'jdbc:sqlserver://111.22.333.44:1433;database=MARKET_DATA_NYMEX');
   
  % Read data from database.
  e = exec(conn,'SELECT * FROM TableName');
  setdbprefs('DataReturnFormat','cellarray')
  e = fetch(e);
  close(e)
   
  % Assign data to output variable.
  Exciting_Data = e.Data;
   
  % Close database connection.
  close(conn)

Read More