Write data to Excel

by

 

19 Apr 2011 (Updated )

Write matrix to Excel-file, and leave the files open. File written to is not saved.

w2excel.m
function [] = w2excel(filename,sheetname,range,matrix,filepath)
%% DESCRIPTION FUNCTION 'w2excel'
% Function to write to an Excel file. Checks first if the file is open and active. Is it open but not active, so it is enabled.
% Is the file does not open it is opened and activated. Is not the file, it will be created. The user is then notified that the file did not exist
% And that it is made. It also checks if the correct sheet is activated and activate this if necessary. Is not the sheet, then made
% Spreadsheet and the user is notified that it is made one sheet in this file.
%% ERROR CONTROL
% It is not implemented any error checking on input beyond those in MatLab or other functions called by this
% Function.
%% STORAGE OF EXCEL FILE
% File writes to an Excel file but does not save the file. The reason that it is not stored is that the user should be able to check data visually,
% And be able to choose whether data is written to be retained. This eliminates the data in Excel will be overwritten by MatLab.
%% DATA RANGE
% The user may choose to provide in a range that meet the data size and that is a valid Excel range.Gis it into a range that does not
% Matches the size of the array is the given error message. Is there an array that is greater than [1 x 1] and range is that
% Is given as a single cell of the "A2", are considered valid range within this function.
%% DESCRIPTION INPUT FOR 'w2excel'
% Filename      : Variable type string: The name of the Excel file including the file extension, such as xls, xlsx, xlsm, etc.
% Range         : Variable type string: String that provides range or start cell that is to be written to the Excel file is open.
% Sheetname     : Variable type string: String that gives the sheet name as it is written to the open Excel file, range notation A1: B2
% Matrix        : Variable type string: array to be written to Excel.
% filepaht      : Variable type string: NB! OPTIONAL: File path where the Excel file is or will be, if not in the current directory.
%% CHECKS IF THE NUMBER OF INPUT ARGUMENTS
    if nargin < 4 || nargin > 5
        error('Give in at least four strings, see function infor for w2excel for input type info')
    elseif nargin == 4
% If the file is  on current path
        fullfilename = fullfile(pwd,filename);
    else
% If another path is given to the file
        fullfilename = fullfile(filepath,filename);
    end
% Extract file type to create if necessary.     
    [~,~, filtype] = fileparts(fullfilename);
%% CHECK OF EXCEL STATUS
% Create at Active-X object in order to communicate with Excel.
    try
        try 
% Use this if Excel allready is started.            
            excel = actxGetRunningServer('Excel.Application');    
        catch exception %#ok<NASGU>
% Create Excel instance if not running.            
            excel = actxserver('Excel.Application');
            excel.visible = 1;
        end
        wbs                 = excel.Workbooks;
        throwerrormessage   = false;
        for i = 1:wbs.Count
% Looping through all Excel files, check if the file is open.
            if strcmp(filename,wbs.Item(i).Name) 
                wbs.Item(i).Activate;
                throwerrormessage   = true;
                break
            end
        end
% Need this in order to enter into the second part of the try-catch        
        assert(throwerrormessage,strcat('Excelfile',char(1),char(34),filnavn,char(34),char(1),'does not excist!'))
% Do this if the file not is among the group which is open.
    catch exception   %#ok<NASGU>
        if exist(fullfilename,'file') ~= 2
            if nargin == 4
                warning('File does not %s excist and will be created on the paht %s',filename,pwd)
            else
                warning('File does not %s excist and will be created on the paht %s',filename,filepath)
            end
% Call the function that make a new excel-file            
                lagexcelfil(fullfilename,filtype)            
        else
% File does excist, open it.
            excel.Workbooks.Open(fullfilename)
        end 
    end
%% SELECT CORRECT SHEET
    sheets = excel.Sheets;
    antallark = sheets.count;
    for i = 1:antallark
        aktivtark = get(sheets,'Item',i);
        if strcmp(aktivtark.name,sheetname)
            invoke(aktivtark, 'Activate');
            break
        elseif i == antallark
            warning('Sheet "%s" does not excist and will be created within the Excel-file"%s"',sheetname,filename)
            newsheet = sheets.Add([],sheets.Item(antallark));
            set(newsheet,'Name',sheetname);
        end
    end
%% CREATE THE RANGE TO WRITE TO
    temprange = regexp(range, ':', 'split');
    if size(temprange,1) > 1 || size(temprange,2) > 2
        error('Excel-range given is not correct')
    else
% Use a function to decide the range to write to.        
        range = ExcelRange(char(temprange{1,1}),size(matrix));
    end
%% WRITE VALUES TO EXCEL
    AktivteSheet = excel.Activesheet;
    AktiveSheetRange = get(AktivteSheet,'Range',range);
    set(AktiveSheetRange, 'Value', matrix);
%% COPY THE FUNCTION FROM XLSWRITE THAT CREATE EXCEL FILE
    function [] = lagexcelfil(fulltnavn,filtype)
        ExcelObject     = actxGetRunningServer('Excel.Application');
        ExcelWorkbook   = ExcelObject.workbooks.Add;
            switch filtype
                case '.xls' %xlExcel8 or xlWorkbookNormal
                   xlFormat = -4143;
                case '.xlsb' %xlExcel12
                   xlFormat = 50;
                case '.xlsx' %xlOpenXMLWorkbook
                   xlFormat = 51;
                case '.xlsm' %xlOpenXMLWorkbookMacroEnabled 
                   xlFormat = 52;
                otherwise
                   xlFormat = -4143;
            end
            ExcelWorkbook.SaveAs(fulltnavn, xlFormat);
    end
function [range] =  ExcelRange(startcell,matrixinfo)
%% FUNCTION THAT RETURN AN EXCEL-RANGE
% This function return a valid Excel-range based on starting cell and size of an matrix.
%% INNDATA
% startcell     : variabel type string  : String type giving range on the form = "A1" 
% matrixinfo    : variabel type mat     : Matrix of the type returned by size-function in MatLab.

%% GIVE VALID MAXIMUM VALUE FOR ROWS AND COLUMNS
% Values applies for Excel-2007 and later
    rowmaks = 1048576;
    colmaks =   16384;
%% INPUT CHECK
    if nargin < 2 
       error('Give in range as string and a valid MatLab matrix as returned by MatLab size-function!') 
    end
%% INDATA CHEKC VARIABEL #1
    temp = isletter(startcell);
    if ~ischar(startcell)
        error('First variable should be a string, giving a Excel-range on the format "A1"!!') 
    elseif size(startcell,1) > 1
        error('Given range description not valid, only one row in the string!') 
    elseif sum(temp) == 0
        error('Range name not valid, now letters??') 
    elseif temp(1,end) == 1        
        error('Range name not valid, last element in string must be a number!') 
    end
%% CHECK THAT THE STRING IS GIVEN IN CORRECT FORMAT
    startverdi = temp(1,1);
    counter = 1;
    for k = 2:size(temp,2)
        if temp(1,k) ~= startverdi
            startverdi = temp(1,k);
            counter = counter +1;
            if counter > 2
                error('Range not valid!') 
            end 
        end
    end
%% INDATA CHEKC VARIABEL #2
    if ~ismatrix(matrixinfo)
        error('Matrix given inn should be as returned by MatLab size-function!!') 
    elseif  size((size(matrixinfo)),2) > 2
        error('Use a for-loop to write matrix of higher dimensions to Excel!') 
    end
%% EXTRACT ROW NUMBER
    startcell = upper(startcell);
    [mat ,~] = regexp(startcell, '\d', 'match', 'start');
    startrad = str2double(cell2mat(mat));
    if startrad > rowmaks
       error('Start rad er strre enn antall rader i Excel!') 
    elseif ~(startrad + matrixinfo(1,1)-1) < rowmaks
        sluttrad = startrad + matrixinfo(1,1)-1;
    end
%% MAKE RANGE STRING
    startkol = startcell(temp);
    if ExcelKolNo(startkol)+matrixinfo(1,2)-1 <= colmaks ;
        sluttkol = ExcelKolIndeks(ExcelKolNo(startkol)+matrixinfo(1,2)-1);
    else
        error('Range cover columns not allowed in Excel!') 
    end
    range = strcat(startkol,num2str(startrad),':',sluttkol,num2str(sluttrad));
end
%% FUNCTION THAT GIVE OUT KOL
function utdata = ExcelKolNo(invalues)
% Column index is running from the letter A to XFD in Excel 2007. In Excel 2003, it is smaller, and the program will error if it is used
% Indices that are not valid in Excel 2003. It is not done any check on whether the range is valid for the version of Excel being worked
% against.
    counter = size(invalues,2);
    switch counter
        case 1
            utdata = double(char(invalues(1)))-64;
        case 2
            utdata = 26*(double(char(invalues(1)))-64) + double(char(invalues(2)))-64;
        case 3
            utdata = 26*26*(double(char(invalues(1)))-64) + 26*(double(char(invalues(2)))-64) + double(char(invalues(3)))-64;
    end
end
%% FUNKSJON SOM RETURNERER KOLONNE INDEKS I EXCEL
function outvalues = ExcelKolIndeks(invalues)
% Funksjon som returner kolonne indeks som funkjon av kolonnenummer. Kolonne nummeret som gis inn m vre mindre enn 16384.
    if invalues > 16384
        error('Excel-2007 have maximum 16348 columns, values given in is above this!')
    elseif invalues < 1
        error('Column index must be an integer larger than 0!')
    end 
%% MAKE THE COLUMN INDEX   
    x3 = mod(invalues,26);
    if x3 == 0
        x3 = 26;
    end
    x2 = mod((invalues-x3),26^2)/26;
    if x2 == 0 && invalues > 26
        x2 = 26;
    end
    x1 = mod((invalues-x3-(x2-1)*26 -26)/26^2,26);    
    if x1 > 0 
        outvalues= strcat(char(x1+64),char(x2+64),char(x3+64));    
    elseif x2 > 0
        outvalues= strcat(char(x2+64),char(x3+64));    
    else
        outvalues= char(x3+64);    
    end
end
end

Contact us