stanhope/
read_xlsx.rs

1//! *Interpret a well-formatted Excel (.xlsx) to initialize Process structs, one for each row*
2//! 
3//! When identifying the need for new processes, a well-formatted list can be constructed, with a specified first set of columns, then all remaining columns assumed to be additional flags for templates:
4//! 
5//! | "Document Number" | "Title" | "Subject" | "Product" | "Author" | "Reviewer" | *TFlag* | *TFlag* | ... |
6//! | --- | --- | --- | --- | --- | --- | :---: | :---: | :---: |
7//! | ... | ... | ... | ... | ... | ... | x |   | ... |
8//! | ... | ... | ... | ... | ... | ... |   | x | ... |
9//! | ... | ... | ... | ... | ... | ... |   |   | ... |
10//! | ... | ... | ... | ... | ... | ... | x | x | ... |
11//! 
12//! The ***TFlag*** columns are user-defined. For example, if a ***TFlag*** column is "WarpSpeed" then this module assumes there is a Cascading Stylesheet file called "WarpSpeed.css" available to these processes, and any process with an "x" in the "WarpSpeed" column will have this Template (CSS file) applied.
13//! 
14//! All other columns are strictly enforced as shown above. Empty values in the data rows are OK, but in the first row of the CSV file, the first 6 columns *must* be those six, in that order.
15use crate::read_ebml::Process;
16use crate::read_csv;
17use calamine::{HeaderRow, Reader, Xlsx, open_workbook, DataType};
18
19/// Pull header and content data from a specifically-formatted Excel (.xlsx) file, and return a list of requested processes (one per spreadsheet data row).
20pub fn read_xlsx(file_name:&String,verbose:bool) -> Vec<Process> {
21	
22	// Assumption: first row is the header row. I mean, that assumption is in the description above, so...
23	let header_row_number:usize = 0;
24
25	// TODO: better handling with blank call (no Excel file?) perhaps with an error thrown
26	let read_file = if file_name==&String::from("") { "./DocumentList.xlsx" } else { file_name };
27	
28	// Lean *way* into the "calamine" crate for the actual heavy lifting...
29	let mut excel: Xlsx<_> = open_workbook(read_file).unwrap();
30	let sheet1 = excel
31	    .with_header_row(HeaderRow::Row(header_row_number as u32))
32	    .worksheet_range("Sheet1")
33	    .unwrap();
34
35	let total_cells = sheet1.get_size().0 * sheet1.get_size().1;
36    let non_empty_cells: usize = sheet1.used_cells().count();
37    println!("Found {} cells in 'Sheet1', including {} non empty cells", total_cells, non_empty_cells);
38
39    let mut processes:Vec<Process> = vec!();
40
41    let head = sheet1.headers().unwrap();
42
43    // To loop through rows...
44    for (row_num,row) in sheet1.rows().enumerate() {
45
46    	if row_num > header_row_number {
47
48	    	let mut new_proc = Process::new();
49
50	    	// TODO: make this more robust, like a lookup table. Pull Document Number from ANY column called Document Number, etc. (not tied to specific column number)
51	    	if &head[0].to_uppercase()=="DOCUMENT NUMBER" && !&row.get(0).unwrap().is_empty() { new_proc.set_number(&row.get(0).unwrap().as_string().expect("NO DATA??")); }
52	    	if &head[1].to_uppercase()=="TITLE" && !&row.get(1).unwrap().is_empty()  { new_proc.set_title(&row.get(1).unwrap().as_string().expect("NO DATA??")); }
53	    	if &head[2].to_uppercase()=="SUBJECT" && !&row.get(2).unwrap().is_empty() { new_proc.set_subject(&row.get(2).unwrap().as_string().expect("NO DATA??")); }
54	    	if &head[3].to_uppercase()=="PRODUCT" && !&row.get(3).unwrap().is_empty() { new_proc.set_product(&row.get(3).unwrap().as_string().expect("NO DATA??")); }
55	    	if &head[4].to_uppercase()=="AUTHOR" && !&row.get(4).unwrap().is_empty() { new_proc.set_author(&row.get(4).unwrap().as_string().expect("NO DATA??")); }
56	    	if &head[5].to_uppercase()=="REVIEWER" && !&row.get(5).unwrap().is_empty() { new_proc.set_reviewer(&row.get(5).unwrap().as_string().expect("NO DATA??")); }
57	    	for (col_num,data) in row.iter().enumerate() {
58	    		if col_num > 5 && !data.is_empty() && !head[col_num].is_empty() {
59	    			let template_name = head[col_num].clone()+".css";
60	    			new_proc.add_template(&template_name);
61	    			read_csv::new_css_file_if_none_exists(&template_name);
62	    		}
63	    	};
64	    	   	
65	    	if verbose { new_proc.display_process_to_stdout(); }
66
67	    	processes.push(new_proc);
68    	}
69    }
70
71	return processes;
72
73}
74
75//  ▄▄▄▄▄▄▄▄▄▄▄  ▄▄▄▄▄▄▄▄▄▄▄  ▄▄▄▄▄▄▄▄▄▄▄  ▄▄▄▄▄▄▄▄▄▄▄ 
76// ▐░░░░░░░░░░░▌▐░░░░░░░░░░░▌▐░░░░░░░░░░░▌▐░░░░░░░░░░░▌
77//  ▀▀▀▀█░█▀▀▀▀ ▐░█▀▀▀▀▀▀▀▀▀ ▐░█▀▀▀▀▀▀▀▀▀  ▀▀▀▀█░█▀▀▀▀ 
78//      ▐░▌     ▐░▌          ▐░▌               ▐░▌     
79//      ▐░▌     ▐░█▄▄▄▄▄▄▄▄▄ ▐░█▄▄▄▄▄▄▄▄▄      ▐░▌     
80//      ▐░▌     ▐░░░░░░░░░░░▌▐░░░░░░░░░░░▌     ▐░▌     
81//      ▐░▌     ▐░█▀▀▀▀▀▀▀▀▀  ▀▀▀▀▀▀▀▀▀█░▌     ▐░▌     
82//      ▐░▌     ▐░▌                    ▐░▌     ▐░▌     
83//      ▐░▌     ▐░█▄▄▄▄▄▄▄▄▄  ▄▄▄▄▄▄▄▄▄█░▌     ▐░▌     
84//      ▐░▌     ▐░░░░░░░░░░░▌▐░░░░░░░░░░░▌     ▐░▌     
85//       ▀       ▀▀▀▀▀▀▀▀▀▀▀  ▀▀▀▀▀▀▀▀▀▀▀       ▀  
86
87#[cfg(test)]
88mod tests {
89    // Note this useful idiom: importing names from outer (for mod tests) scope.
90	use super::*;
91
92	fn open_sample_excel_file(f:&str) -> Vec<Process> {
93		
94	    // CARGO_MANIFEST_DIR points to the directory containing your Cargo.toml
95	    let manifest_dir = env!("CARGO_MANIFEST_DIR");
96	    
97	    // Build the path to your static file
98	    let file_path = manifest_dir.to_owned()+"/tests/excel_files/"+f;
99
100	    read_xlsx(&file_path,false)
101	}
102
103	#[test]
104	fn test_read_excel_1000_processes() {
105		// This Excel file has 1000 process rows (only the first two columns filled)
106		assert_eq!(open_sample_excel_file("DocumentList_1000_processes.xlsx").len(),1000);
107		// This should register the 1000 processes, even if default values for most fields are used
108	}
109
110	#[test]
111	fn test_read_excel_header() {
112		// The following assertions can only be valid if the header row is read correctly
113		let default_three_processes = open_sample_excel_file("DocumentList_0.111.2.xlsx");
114		
115		assert_eq!(default_three_processes[0].get_number(),"DOC-001");
116		assert_eq!(default_three_processes[0].get_title(),"Inspect Incoming Parts");
117		assert_eq!(default_three_processes[0].get_subject(),"Incoming Parts");
118		assert_eq!(default_three_processes[0].get_product(),"Inspected Parts");
119		assert_eq!(default_three_processes[0].get_author(),"Emma Ployee");
120		assert_eq!(default_three_processes[0].get_reviewer(),"R. Stamp");
121
122		let all_templates = default_three_processes[0].get_all_templates();
123
124		assert_eq!(all_templates.len(),4);
125		assert_eq!(all_templates[0],"NonConformances.css");
126		assert_eq!(all_templates[1],"ESD.css");
127		assert_eq!(all_templates[2],"CleanRoom.css");
128		assert_eq!(all_templates[3],"NoAI.css");
129	}
130
131	#[test]
132	fn test_read_correct_number_of_processes() {
133		// DOC-001, DOC-002, DOC-003
134		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx").len(),3);
135	}
136
137	#[test]
138	fn test_read_document_numbers_from_excel() {
139		// DOC-001, DOC-002, DOC-003
140		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[0].get_number(),"DOC-001");
141		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[1].get_number(),"DOC-002");
142		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[2].get_number(),"DOC-003");
143	}
144
145	#[test]
146	fn test_read_titles_from_excel() {
147		// DOC-001, DOC-002, DOC-003
148		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[0].get_title(),"Inspect Incoming Parts");
149		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[1].get_title(),"Produce Product from Parts");
150		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[2].get_title(),"Inspect Outgoing Product");
151	}
152
153	#[test]
154	fn test_read_subject_from_excel() {
155		// DOC-001, DOC-002, DOC-003
156		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[0].get_subject(),"Incoming Parts");
157		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[1].get_subject(),"Inspected Parts");
158		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[2].get_subject(),"Product");
159	}
160
161	#[test]
162	fn test_read_product_from_excel() {
163		// DOC-001, DOC-002, DOC-003
164		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[0].get_product(),"Inspected Parts");
165		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[1].get_product(),"Product");
166		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[2].get_product(),"Inspected Product");
167	}
168
169	#[test]
170	fn test_read_author_from_excel() {
171		// DOC-001, DOC-002, DOC-003
172		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[0].get_author(),"Emma Ployee");
173		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[1].get_author(),"Emma Ployee");
174		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[2].get_author(),"Emma Ployee");
175	}
176
177	#[test]
178	fn test_read_reviewer_from_excel() {
179		// DOC-001, DOC-002, DOC-003
180		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[0].get_reviewer(),"R. Stamp");
181		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[1].get_reviewer(),"R. Stamp");
182		assert_eq!(open_sample_excel_file("DocumentList_0.111.2.xlsx")[2].get_reviewer(),"R. Stamp");
183	}
184
185	#[test]
186	fn test_read_templates_from_excel() {
187		assert_eq!(open_sample_excel_file("DocumentList_1000_templates.xlsx")[0].get_all_templates().len(),1000);
188	}
189
190	#[test]
191	fn test_bad_excel_typo_document_number_column() {
192		// DOC-001, DOC-002, DOC-003
193		assert_ne!(open_sample_excel_file("bad_header_document_number.xlsx")[0].get_number(),"DOC-001");
194		assert_ne!(open_sample_excel_file("bad_header_document_number.xlsx")[1].get_number(),"DOC-002");
195		assert_ne!(open_sample_excel_file("bad_header_document_number.xlsx")[2].get_number(),"DOC-003");
196	}
197
198	#[test]
199	fn test_bad_excel_missing_subject_column() {
200		// Current implementation is fragile, so a missing column should throw everything off.
201		// Process rows will still generate entries in the resultant Vec<Process> but the fixed indexing will be off
202		// For example, the TFLAG columns will start one row to the right on accident, so DOC-002 should NOT have TechCompany.css in its list of templates...
203		// Also, any core column to the right of the missing column will not be recorded, because of fixed indexing...
204
205		// First thing: the process lines should still be generated...
206		assert_eq!(open_sample_excel_file("bad_header_missing_column.xlsx").len(),3);
207
208		// Doc number and title should still be good...
209		assert_eq!(open_sample_excel_file("bad_header_missing_column.xlsx")[0].get_number(),"DOC-001");
210		assert_eq!(open_sample_excel_file("bad_header_missing_column.xlsx")[0].get_title(),"Inspect Incoming Parts");
211
212		// The file is the same as the baseline file, but with the "Subject" column deleted.
213		assert_ne!(open_sample_excel_file("bad_header_missing_column.xlsx")[0].get_subject(),"Incoming Parts");
214		assert_ne!(open_sample_excel_file("bad_header_missing_column.xlsx")[0].get_product(),"Inspected Parts");
215		assert_ne!(open_sample_excel_file("bad_header_missing_column.xlsx")[0].get_author(),"Emma Ployee");
216		assert_ne!(open_sample_excel_file("bad_header_missing_column.xlsx")[0].get_reviewer(),"R. Stamp");
217
218		// For templates (TFLAG columns) let's look at the second process, because it has an "X" in the first TFLAG column ("TechCompany")
219		// The reason to look at this one is because the fixed-column numbering baked into this code means that first TFLAG column will not be picked up...
220		assert_eq!(open_sample_excel_file("bad_header_missing_column.xlsx")[1].get_all_templates().len(),5);
221		// actually 6, but only 5 will register...
222		// check that the first one is "ESD.css"
223		assert_eq!(open_sample_excel_file("bad_header_missing_column.xlsx")[1].get_all_templates()[0],"ESD.css");
224	}
225}
stanhope/read_xlsx.rs

stanhope/
read_xlsx.rs
