File Conversion Multiple .DAT files to one xlsx workbook with different sheets

Question

I have a directory containing with 50 DAT files one base xlsx file in each. I need to make one workbook with those 50 DAT files as an independent worksheet in each folder within directory. The worksheet shall use the columns 3,4,5 from DAT files which is tab separated. The worksheet shall have 6 columns. The first three will be taken from the base xlsx file in the directory. Column 4-6 shall have values from Column 3-5 from .DAT files. I wrote a code in MATLAB to do the needful. Everytime I run it, program crashes. The error I received is

Error using xlswrite (line 219) Invoke Error, Dispatch Exception: Source: Microsoft Excel Description: Document not saved. Help File: xlmain11.chm Help Context ID: 0.

The code is as follows: In the code, files_larswg is base xlsx file. Is there a procesto rectify the error and make the process a batch process in case I have 10 similar directories.

date=xlsread('files_larswg.xlsx',1,'A2:C18251');

 header_tree={ 'da' , 'mo', 'year', 'tminC', 'tmaxC','prcpmm'};

for k=1:50

k

fileID =fopen(sprintf('FortWayneWG%d.dat',k+516));

data = textscan(fileID,'%*d %*d %f %f %f');

fclose(fileID);

data=cell2mat(data);

sheet_no=sprintf('sheet%d',k);

xlswrite('FWYLARSWG_50set.xlsx', data, sheet_no,'D2:F18251');
xlswrite('FWYLARSWG_50set.xlsx', header_tree, sheet_no,'A1:F1');
xlswrite('FWYLARSWG_50set.xlsx', date, sheet_no,'A2:C18251');

end

zhqiat zhqiat · Accepted Answer · 2016-09-06T21:19:35

Each time you use xlswrite, what you are doing is opening an excel instance, trying to write everything to a file, closing the file, and then reopening the file to do this again. Your OS is erroring (presuming you are using windows) out because the file is locked in windows.

What you should do is use the underlying ActiveXServer in excel, write all of your tabs into a spreadsheet once, and then save this.

Your code should broadly read something like this.

Create an excel object and setup a fresh new workbook.

e = actxserver('Excel.Application');
e.Visible = 1;
w=Add(e.Workbooks)

You should subsequently loop over your data range and write your data in. This code will add a sheet for each item in a loop, change the sheetname, and then create a the following text in each cell.

datasheet=Add(e.Sheets)
datasheet.Name=sheetname(n)
datasheet.Range('A1:B1').Value='test data or your range'

Once you've completed the loop, save the file, and cleanup your excel object.

w.SaveAs('myfile.xls')
w.Saved = 1;
w.Close;
w.delete

You should follow the pattern of this example on the Matlab help page as well.

File Conversion Multiple .DAT files to one xlsx workbook with different sheets

1 Answers