How to assign rownames and field names (variable names) to tables in table after loading several listobjects from a excel file by using actixserver?

5 次查看（过去 30 天）
monkeyquant
monkeyquant 2022-10-28
编辑： monkeyquant 2022-10-31
I read several ListObjects (ranges) into a cell array (because I am using actxserver), convert it to a table, cell2table, assign variable names to each table in the table. Each table (from Excel ListObjects) contains column names and row names and I want to assign them row names with first colum and fieldnames with the first row of each ListObjects by using actixserver, that is, make the first column to be row names and first row to be fieldname/vairable names in each table in the table. First, how to access elements in tables in table? Second, how to assign row names ad column names to each table in a table? Thanks for your time.
clear all;
clc
[tFile, tPath] = uigetfile('Program*.xlsx', 'Grab the Parameter files you want to process', 'MultiSelect', 'off');
tFullName = fullfile(tPath, tFile);
% % Using Com Server - results range data as cell arrays
xlApp = actxserver('Excel.Application'); % Initiate ActiveX Com Server
xlWb = xlApp.Workbooks; % Open Excel Workbook
xlSh = xlWb.Open(tFullName); % Sheets contained in Excel Workbook - One Drive is not working
% Sheet Names
mm = xlSh.Sheets.Count;
xlShNames = cell(mm, 1);
for ii = 1:mm
iSh = get(xlSh.Sheets, 'item', ii);
xlShNames {ii, 1} = iSh.Name;
end
% % Sheet Names, List Object Names, and Reference Ranges contained in tblPivot
% Pull these out from the Excel by VBA... each sheet has a single
% ListObject. The numbers of list objects, sheets and variable could grow.
%
tListObjs = {'tbl1'; 'tbl2'; 'tbl3'; 'tbl3'; 'tbl4'; ...
'tbl5'; 'tbl6'; 'tbl7'; 'tbl8'; 'tbl9'; 'tbl10'; 'tbl11'};
% The same as xlShNames above
tSheetNames = {'Sh1'; 'Sh2'; 'Sh3'; 'Sh4'; 'Sh5'; ...
'Sh6'; 'Sh7'; 'Sh8'; 'Sh9'; 'Sh10'; 'Sh11'};
% Assign tListObjects or Ranges to these
tVariables = {'lq1'; 'lq2'; 'lq3'; 'lq3'; 'lq4'; 'lq5'; 'lq6'; ...
'lq7'; 'lq8'; 'lq9'; 'lq10'; 'lq11'};
% Range String if using range with lq1
tDelimiter = ':';
% Note: MATLAB doesn't recognize ListObjects at least with ActivXserver
% Get first cell and last cell information from the Excel Table,
% tblPivot
% Note that xlSh.Sheets(SheetName).Range().Value is not working. It should
% be xlSh.Sheets.Items(SheetName).Range().Value.
% lqVersion = xlSh.Sheets('Version').ListObjects('tblVersion').Item(1).Value; % not
% working
% I want to obtain these values automatically if ListObject works in
% xlsread, actxserver or readtable
lq0 = xlSh.Sheets.Item('Version').Range("C9").Value;
lq1 = xlSh.Sheets.Item('Version').Range("C16:L18").Value;
tData{mm} = []; % either with struc or cell array (actxserver create)
for i = 2:mm % numel(xlSh) % or Use xlSh.Count instead
% tData{i} = xlSh.Sheets(tSheetNames{i}).ListObjects(tListObjs{i}).Value;
tData{i} = xlSh.Sheets.Item(tSheetNames{i}).Range(string(lqPivot{1, i-1}) + tDelimiter + string(lqPivot{2, i-1})).Value;
end
% Convert to Table from array, array2table, or from cell, cell2table
% now tData is table of tables which contains field names in the first row
% and want first column as row name for quick reference
tData = cell2table(tData,"VariableNames",tVariables);
% no idea here
table(tData.lq1, 'RowNames', tData.lq1{1, 1}{2:end, 1}, 'VariableNames', tData.lq1{1, 1}{1, 2:end});
% the tables or table of tables will be save as a sequential name (version
% number) which will be loaded at the valuation step but using the same
% table names to refer.

请先登录，再回答此问题。

回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Use COM Objects in MATLAB 的更多信息

标签

产品


版本

R2014b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by