How to write data into an excel file but keep the original formatting?

How can I write values into my excel file without changing the formatting that already exists. specifically I want to keep the cell formulas and cell colours unchanged. This is what I have so far but it wipes out all formatting:

writetable(ResultMtx,Result_File);

[Mathieu NOE]

hello

have you tried to protect your excel file before your write into it from matlab ?

How to protect cell formatting but only allow data entry in Excel? (extendoffice.com)

Answer 1

[Image Analyst]

What release are you using? You forgot to enter it for some reason when you filled out the submission form. Yes that was a problem but I believe they fixed it now. Try release r2021a or later. If I recall correctly, it's no longer blowing away my formatting present in an already-existing workbook.

[Leo]

I'm using 2023a, and I still get the same issue with writematrix and writetimetable. Haven't tried latest release or other functions. It's an annoying problem so, even against MatLab warnings, I continue to use xlswrite, which doesn't change the formatting. Would appreciate a fix, or letting me know whether the latest version has fixed it.

[Leo]

And I just tried the PreserveFormat option, as below, without success:

writematrix(Data_TT.Variables,ExcelWriteFileName,'Sheet','TEMP','Range','G10','PreserveFormat',true)

[Walter Roberson]

@Leo

Which operating system are you using?

[Leo]

I'm using Windows 11

[Image Analyst]

@Leo please attach the original workbook, and code where you are writing new data to it, and the new workbook where you demonstrate that the original formatting was destroyed. Then we can test it ourselves.

[Leo]

I've prepared an example showing the format changes using the code below, now in 2025a. But I can't see how to attach the Excel file. Please advise on how to do the latter.

%% Read data.

Test=readcell('ExcelFormatTest.xlsx','Sheet','ReadSheet','Range','E10:K40');

disp('Finished reading.')

%% Write data.

writecell(Test,'ExcelFormatTest.xlsx','Sheet','WriteSheet','Range','E10:K40','PreserveFormat',true);

disp('Finished writing.')

[Image Analyst]

When you reply, there should be an icon that looks like a paperclip. Use that. If you see an INSERT icon with a downward pointing arrow, then click on that.

[Leo]

Hello. Sorry for the delayed response - I'll check that email account regularly in future.

So, I've attached two Excel sheets. The first shows the original formatting in each of the sheets - just a copy of "ReadSheet". The second, "..._After", shows the formatting after I use the code below to test writecell, writetimetable, writematrix, and xlswrite . Only the latter leaves the original formatting unchanged.

cd('C:\MATLAB\MatLabTesting')

clc

clear

%% Read data as cells, timetable, and Excel data.

Test1=readcell('ExcelFormatTest.xlsx','Sheet','ReadSheet','Range','E10:K40');

Test2=readtimetable('ExcelFormatTest.xlsx','Sheet','ReadSheet','Range','E10:K40');

Test3=readmatrix('ExcelFormatTest.xlsx','Sheet','ReadSheet','Range','F11:K40');

disp('Finished reading.')

%% Write cells.

writecell(Test1,'ExcelFormatTest.xlsx','Sheet','writecell','Range','E10','PreserveFormat',true);

disp('Finished writing cells.')

%% Write timetable.

writetimetable(Test2,'ExcelFormatTest.xlsx','Sheet','writetimetable','Range','E10','PreserveFormat',true);

disp('Finished writing timetable.')

%% Write matrix. Note: only writes data, leaving dates and headings.

Matrix=Test2.Variables;

writematrix(Test3,'ExcelFormatTest.xlsx','Sheet','writematrix','Range','F11','PreserveFormat',true);

disp('Finished writing matrix.')

%% Write Excel data. Note: only writes data, leaving dates and headings.

xlswrite('ExcelFormatTest.xlsx',Matrix,'xlswrite','F11:K40');

disp('Finished writing Excel data.')