Borrar filtros
Borrar filtros

excel cell value

4 visualizaciones (últimos 30 días)
Samer Husam
Samer Husam el 11 de Jun. de 2012
hi all, how can I check the value of a cell in excel ? something like:
a= str2double(get(handles.edit1,'string'));
if xlsread('C:\Data sheet.xlsx','Sheet1',A1,'value')==1
xlswrite('C:\Data sheet.xlsx',a,'Sheet1',A1);
else
xlswrite('C:\Data sheet.xlsx',a,'Sheet1',A2);
end
  2 comentarios
Mark Whirdy
Mark Whirdy el 9 de Jul. de 2012
Hi Samer In general, xlsread(xlswrite) is a good idea ONLY if you are doing a once-off read (write) from a file. If you are doing multiple actions (reads/writes) I strongly suggest not using these function as there is massive amounts of overhead associcated with the re-instantiation of the excel application object with each function call. Instead use the Activex object directly and capture the data by invoking the VBA-type properties and methods of the excel COM Object. You can adapt the code below to get started (and simply google "Matlab activex COM" for more sample code).
To answer your question specifically, use the "Value2" property of the Range object (you'll find this below)
xlApp = actxserver('Excel.Application');
xlApp.Visible = 1;
xlWorkbook = xlApp.workbooks.Open(fullfile(xlFilePath,xlFileName),0,true);
xlSheets = xlWorkbook.Sheets;
xlSheetNamesArray = cell(xlSheets.Count,1);
for i = 1:xlSheets.Count
xlSheetNamesArray{i} = xlSheets.Item(i).Name; % sheet-order is not guaranteed so must build array
end
[~,idx] = ismember('Price',xlSheetNamesArray);
xlSheets.Item(idx).Activate
xlActiveSheet = xlWorkbook.ActiveSheet;
xlCurrRange = xlActiveSheet.Range('C4:C33');
priceVector = xlCurrRange.Value2;
priceVector = cell2mat(priceVector);
[~,idx] = ismember('Portfolios',xlSheetNamesArray);
xlSheets.Item(idx).Activate
xlActiveSheet = xlWorkbook.ActiveSheet;
xlCurrRange = xlActiveSheet.Range('B4:B22');
isinVector = xlCurrRange.Value2;
xlCurrRange = xlActiveSheet.Range('C3:G3');
ptfNumVector = cell2mat(xlCurrRange.Value2)';
xlCurrRange = xlActiveSheet.Range('C4:G22');
dataMatrix = xlCurrRange.Value2;
isnanMatrixMask = strcmp(dataMatrix,'ActiveX VT_ERROR: '); % handle missing data - assume as no position
dataMatrix(isnanMatrixMask) = {0};
dataMatrix = cell2mat(dataMatrix);
Samer Husam
Samer Husam el 10 de Jul. de 2012
Hi Mark, thanks for your suggestion but so far I have a question about the code you post, I couldn't find where the part that you write in the excel after using the Activex, can you please mentions it in your code ?? thanks a lot for your answer..

Iniciar sesión para comentar.

Respuestas (1)

Walter Roberson
Walter Roberson el 8 de Jul. de 2012
Editada: Walter Roberson el 8 de Jul. de 2012
I am not certain of what you are asking for, but perhaps
a= str2double(get(handles.edit1,'string'));
if xlsread('C:\Data sheet.xlsx', 'Sheet1', 'A1:A1') == 1
xlswrite('C:\Data sheet.xlsx' ,a, 'Sheet1', 'A1:A1');
else
xlswrite('C:\Data sheet.xlsx', a, 'Sheet1', 'A2:A2');
end
This sequence relies upon the fact that the first output argument from xlsread() will be a numeric array containing the data; and of course that a numeric array of size 1x1 is a scalar.
  1 comentario
Samer Husam
Samer Husam el 9 de Jul. de 2012
it suppose to check the cells values for column A starting from A1 and write in Empty cell only..

Iniciar sesión para comentar.

Etiquetas

Community Treasure Hunt

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

Start Hunting!

Translated by