MATLAB: Excel spreadsheet – using xlswrite only writes first value to spreadsheet

columnexcelfirstMATLABonlyrangerowsheetvaluevectorwritexlswrite

I am writing a vector from the workspace to an Excel spreadsheet, and specifying the range. But I only see the first number in the vector being written over and over again.

As an example :

>> x = 1:10;
>> xlswrite('temp.xlsx', x, 'A1:A10');

Now, on opening the "temp.xlsx" file, I can see that only the number "1" is written 10 times from A1 to A10. Why is this?

Best Answer

When specifying the "range" in the "xlswrite" function, ensure that if a "row" range is specified, then the variable is a row vector. Similarly, if a column range is specified, ensure that the input is a column vector.

In this case, a row vector is the input, and the range specified is a column.

To fix this, simply use the transpose operator on the variable x as follows :

>> x = 1:10;
>> xlswrite('temp.xlsx', x', 'A1:A10');

Related Solutions

MATLAB: How to use Excellink functions in a Excel Macro Function instead of a Sub

The above Macro Subroutine can be translated to the following Macro Function:

Function Calculate(A As Range, b As Range) As Variant
    MLOpen
    MLPutMatrix "A", A
    MLPutMatrix "b", b
    MLEvalString "x = myfunc(A, b)"
    MLGetVar "x", Calculate
End Function

The inputs to MLPutMatrix are Ranges. MLGetVar is used to write the "x" from the MATLAB workspace to the "Calculate" variable.

Note Excel Macro Functions only support one output range. For MATLAB functions with multiple outputs, consider writing to multiple ranges directly.

      MLGetMatrix "x", "x"
      MLGetMatrix "y", "y"

MATLAB: Does the datatype automatically change to categorical while importing a spreadsheet

In the Import Tool, a column of data is interpreted as categorical if it:

is detected as text by the detectImportOptions function, and
the uniqueness of the text in that column is below a threshold, in which case, using a categorical value may be a better option for the user.

If the spreadsheet contains dashes or any such characters in cells as a placeholder for empty cell, it causes the data type detection to treat the column as text. You can observe this at the command line by executing the following commands to the attached spreadsheet "Book1.xlsx":

>> opts = detectImportOptions("Book1.xlsx");

>> opts.VariableTypes

ans =

1×10 cell array

{'char'} {'char'} {'char'} {'char'} {'char'} {'char'} {'char'} {'char'} {'char'} {'char'}

A workaround for this is to force these columns to be numeric. For example:

>> opts = detectImportOptions("Book1.xlsx");

>> opts = setvartype(opts, "double");

>> t = readtable("Book1.xlsx", opts);

Best Answer

Related Solutions

MATLAB: How to use Excellink functions in a Excel Macro Function instead of a Sub

MATLAB: Does the datatype automatically change to categorical while importing a spreadsheet

Related Question