How do I make the spreadshee​tdatastore​/datastore read, write, and modify the the data?

The attached excel file contains a Template of data that I am trying to use with Matlab. I am trying to build a ssds/ds with an expectation that I can use it as a library. Each of these Excel files essentially contains "1 day" of fabrication Jobs[Pl_ids]. After data is read, rest of the code optimizes, sends it to the machine & So on. Whatever Fabrications get processed today, can be eliminated/discarded from library. Any Fabs left over from the day, will just be left over in excel file(or a new version of it, or maybe written to a empty file).

tic
ssds = spreadsheetDatastore('Data_Files\OptimTemplate.xlsx')
readall(ssds)
ssds.VariableTypes{1,4}= 'categorical'       ;
ssds.VariableTypes{1,5}= 'categorical'       ;
VariablesPopulated = ssds.VariableNames(:)   ;
TypeOfVariable = [ssds.VariableTypes(:)]     ;
Import_Properties_Check_Table = table(VariablesPopulated,TypeOfVariable) ;
Import_Properties_Check_Table.Properties.Description = ['Imported Properties Check Table'] %;

% open(ssds)
toc

s_len = 40; %Specified in feet

The Code should execute easily to see output. First Problem- It Ended Up Combining Data Across Sheets. I need Matlab to only read upto limit. So if fabrication was 10 pieces, so be it. Second Problem: is * ssds/ds* the ideal thing to use for my purpose as explained? [Context: Machines are smart, so the smaller cut lengths of previous day/month etc, need to be remembered by Matlab, so I can bring those pieces back in whenever Optimization requires them. ] I had thought about containers/ Struct/ plain Spreadsheet import but ssds looked most representative of the library. I am sure I can use some guidance and benefit from it.

Lastly, in another part I used multitrace3D command, to build 3D-beams. It seems to be confusing rather than making it easier for end user to make some sense of Stock Lengths. Is their some better command for creating the length, and then showing cuts/patterns on it? It's not of immediate importance, but I need 3D Beams for dumbing it down as much as I can for end user to show Bevel/Miter/Compound cuts etc.

Answer 1

[Vineet Ahirkar]

According to my understanding you want to read data from only within one sheet in the spreadsheet file.

For limiting the data read by ssds you can specify the "ReadSize" property on the "spreadsheetDatastore" object and then use the "read" function instead of the "readall" function. This property takes 3 types of values -

'sheet' - will read data from one sheet at a time.

A positive integer - will read the rows specified by ReadSize, or fewer if it reaches the end of the data.

'file' - (default) will read all the data one file at a time.

Example -

>> ssds = spreadsheetDatastore('OptimTemplate.xlsx') ; 

>> ssds.ReadSize = 'sheet'; 

>> read(ssds)

To answer your other question, "spreadsheetDatastore" is typically used when your data cannot entirely fit in memory and resides in multiple sheets of a spreadsheet file or in a collection of multiple spreadsheet files. If the data is small and is contained in a specific sheet, "readtable" might be a better alternative.

For more information please refer the following links -

spreadsheetDatastore - https://www.mathworks.com/help/matlab/ref/matlab.io.datastore.spreadsheetdatastore.html

readtable - https://www.mathworks.com/help/matlab/ref/readtable.html

[Gaurav]

Thank you so much! I was just getting confused having all the stuff in code.