Clear Filters
Clear Filters

Importing (and exporting) from excel with formatting?

13 views (last 30 days)
Is there a method to load from excel sheet with formatting and later save the table back to excel INCLUDING previously loaded formatting? I mean fonts, cell background color. I have tables with extensive color coding and I would like to load-modify text-write them automatically.
  5 Comments
Indrek Morell
Indrek Morell on 30 Nov 2023
No, I got ~100 of different xlsx files and they are all different. What I CAN do is to make a script that will color the table according to content but I was hoping for some easier way like importing the formatting and then re-exporting it.
Indrek Morell
Indrek Morell on 1 Dec 2023
OK, I got to a solution that I do not need to extract the color data at all. I open the excel sheet first with XLSREAD and data goes to a cell variable. Then I start the ActiveX server and insert the data into the sheet (I don't need to delete / add any columns / rows) and save. Thus color coding is preserved but strings in cell can be changed with STRREP etc.
Thanks for everyone for thinking along!
Excel=actxserver('Excel.Application');
% start ActiveX
WB = Excel.Workbooks.Open(fullfile([Path File]),0,false);
% get workbook object
Sheets=Excel.ActiveWorkbook.Sheets;
% sheet handles
Sheet = Sheets.get('Item', SNr);
% acquires sheet handle
% #####################
% ### Modifications ###
% #####################
NetWork(:,2)=strrep(NetWork(:,2),'C0','X0');
% replacement
% #####################
% ### Modifications ###
% #####################
ActiveRange = get(Sheet,'Range',['A1:G' num2str(size(NetWork,1))]);
% inserts data range
set(ActiveRange, 'Value', NetWork);
% inserts cell to sheet
WB.Save();
% save Workbook
WB.Close();
% close Workbook
Excel.Quit();
% quit Excel

Sign in to comment.

Answers (0)

Products


Release

R2022b

Community Treasure Hunt

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

Start Hunting!