br_excel/read.rs
1use std::collections::HashMap;
2use std::path::Path;
3use json::{array, JsonValue, object};
4use log::{error};
5use umya_spreadsheet::{EnumTrait, reader};
6use umya_spreadsheet::helper::coordinate::CellCoordinates;
7use crate::{Cell, Head};
8
9
10pub struct Read {}
11
12impl Read {
13 /// 导出指定头内容的对象组
14 /// * filename 文件路径
15 /// * page 页码 0开始
16 /// * header_line 第几行开始
17 /// * headers 头部信息
18 pub fn export(filename: &str, page: usize, header_line: u32, headers: Vec<Head>) -> JsonValue {
19 let path = Path::new(filename);
20 let book = match reader::xlsx::read(path) {
21 Ok(e) => e,
22 Err(e) => {
23 error!("加载xlsx错误: {}", e);
24 return array![];
25 }
26 };
27
28 let total_row = match book.get_sheet(&page) {
29 None => {
30 error!("加载xlsx行数错误: {}", page);
31 return array![];
32 }
33 Some(e) => e.get_highest_row()
34 };
35
36 let total_col = book.get_sheet(&page).unwrap().get_highest_column();
37
38 let mut head = HashMap::new();
39 for row in header_line..=header_line {
40 let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
41 if row_info {
42 continue;
43 }
44 for col in 1..=total_col {
45 let value = book.get_sheet(&page).unwrap().get_value((col, row));
46 for header in headers.iter() {
47 if header.title == value {
48 head.insert(col, header.clone());
49 }
50 }
51 }
52 }
53 let mut list = array![];
54 for row in header_line + 1..=total_row {
55 let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
56 if row_info {
57 continue;
58 }
59 let mut data = object! {};
60 for col in 1..=total_col {
61 let value = book.get_sheet(&page).unwrap().get_value((col, row));
62 if head.contains_key(&col) {
63 let info = head.get(&col).unwrap().clone();
64 data[info.field] = JsonValue::from(value);
65 }
66 }
67 list.push(data).unwrap();
68 }
69 list
70 }
71
72 ///// 导出表结构
73 //pub fn export_array(filename: &str, page: usize) -> JsonValue {
74 // let path = Path::new(filename);
75 // let book = match reader::xlsx::read(path) {
76 // Ok(e) => e,
77 // Err(e) => {
78 // if filename.ends_with(".xls") {
79 // let mut workbook: Xls<_> = match open_workbook(path) {
80 // Ok(e) => e,
81 // Err(e) => {
82 // error!("加载xlsx错误: {}", e);
83 // return array![];
84 // }
85 // };
86 //
87 //
88 // let mut data = array![];
89 // for name in workbook.sheet_names() {
90 // if let Ok(range) = workbook.worksheet_range(&name) {
91 // let mut rows = vec![];
92 // for row in range.rows() {
93 // let mut cols = vec![];
94 // for col in row {
95 // cols.push(col.to_string().trim().to_string());
96 // }
97 // rows.push(cols);
98 // }
99 // data.push(rows).unwrap();
100 // }
101 // }
102 // return data;
103 // }
104 // error!("加载xlsx错误: {}", e);
105 // return array![];
106 // }
107 // };
108 //
109 // let sheet = match book.get_sheet(&page) {
110 // None => {
111 // error!("读取页面错误");
112 // return array![];
113 // }
114 // Some(e) => e
115 // };
116 // let total_row = sheet.get_highest_row();
117 // let total_col = sheet.get_highest_column();
118 //
119 // let mut data = array![];
120 //
121 // for row in 1..=total_row {
122 // if sheet.get_row_dimension(&row).is_none() {
123 // continue;
124 // }
125 // // 获取行信息
126 // let row_info = sheet.get_row_dimension(&row).unwrap();
127 //
128 // // 获取行高
129 // let row_height = *row_info.clone().get_height();
130 //
131 // let mut row_data = array![];
132 // for col in 1..=total_col {
133 // let value = sheet.get_value((col, row));
134 //
135 // let mut col_width = 20.0;
136 //
137 // let tt = sheet.get_column_dimension_by_number(&col).is_none();
138 // if !tt {
139 // let col_info = sheet.get_column_dimension_by_number(&col).unwrap();
140 // col_width = *col_info.clone().get_width();
141 // }
142 //
143 //
144 // let background_color = "";
145 //
146 // let style = sheet.get_style((col, row)).clone();
147 //
148 // // // font
149 // // let font_color = style.get_font().clone().unwrap().get_color().clone().clone().get_argb().to_string();
150 // // let font_color = style.clone();
151 // // println!("{:#?}", font_color);
152 //
153 //
154 // let mut style_size = 0;
155 // if style.get_font().is_some() {
156 // style_size = *style.get_font().unwrap().get_size() as i32;
157 // }
158 //
159 // let ttt = Alignment::default();
160 // let alignment = style.get_alignment().unwrap_or(&ttt);
161 // let alignment = alignment.get_horizontal().clone();
162 // let alignment = alignment.get_value_string();
163 // let horizontal = if alignment == "general" { "center" } else { alignment };
164 //
165 //
166 // let alignment = style.get_alignment().unwrap_or(&ttt);
167 // let alignment = alignment.get_vertical().clone();
168 // let vertical = alignment.get_value_string();
169 //
170 // let alignment = style.get_alignment().unwrap_or(&ttt);
171 // let alignment = *alignment.get_wrap_text();
172 // let wrap_text = if alignment { "normal" } else { "none" };
173 //
174 // // let get_fill = book.get_sheet(&page).unwrap().get_style((col, row)).get_fill().clone();
175 // // let get_fill = get_fill.unwrap().get_pattern_fill().clone();
176 // // let get_fill = get_fill.unwrap().get_background_color().clone();
177 // // let get_fill = get_fill.unwrap_or(Color::default());
178 // // get_fill = get_fill.get_indexed().clone().to_string().as_str().clone();
179 //
180 // let mut style_left = false;
181 // let mut style_right = false;
182 // let mut style_top = false;
183 // let mut style_bottom = false;
184 //
185 // let is_borders = style.get_borders().is_none();
186 // if !is_borders {
187 // let borders = style.get_borders().unwrap();
188 // style_left = *borders.get_left().get_color().get_indexed() == 8_u32;
189 // if !style_left {
190 // style_left = *borders.get_left().get_color().get_indexed() == 64_u32;
191 // }
192 // style_right = *borders.get_right().get_color().get_indexed() == 8_u32;
193 // if !style_right {
194 // style_right = *borders.get_right().get_color().get_indexed() == 64_u32;
195 // }
196 // style_top = *borders.get_top().get_color().get_indexed() == 8_u32;
197 // if !style_top {
198 // style_top = *borders.get_top().get_color().get_indexed() == 64_u32;
199 // }
200 // style_bottom = *borders.get_bottom().get_color().get_indexed() == 8_u32;
201 // if !style_bottom {
202 // style_bottom = *borders.get_bottom().get_color().get_indexed() == 64_u32;
203 // }
204 // }
205 //
206 //
207 // let col_data = object! {
208 // value:value,
209 // row:row,
210 // col:col,
211 // style:object!{
212 // height:format!("{}mm",row_height*0.3612),
213 // width:format!("{}mm",col_width*2.54),
214 // "font-size":style_size,
215 // "background-color":background_color,
216 // "text-align":horizontal,
217 // "vertical-align":vertical,
218 // "text-wrap":wrap_text
219 // },
220 // rowspan:1,
221 // colspan:1,
222 // border:object! {
223 // left:style_left,
224 // top:style_top,
225 // bottom:style_bottom,
226 // right:style_right
227 // },
228 // state:1
229 // };
230 // row_data.push(col_data).unwrap();
231 // }
232 // data.push(row_data).unwrap();
233 // }
234 //
235 //
236 // // 获取合并信息
237 // let merge = book.get_sheet(&page).unwrap().get_merge_cells();
238 // // println!("{:?}",book.get_sheet(&page).unwrap());
239 // for item in merge.iter() {
240 // let start_row = match item.get_coordinate_start_row() {
241 // None => {
242 // continue;
243 // }
244 // Some(e) => { *e.get_num() }
245 // };
246 // let start_col = match item.get_coordinate_start_col() {
247 // None => {
248 // continue;
249 // }
250 // Some(e) => { *e.get_num() }
251 // };
252 // let end_row = match item.get_coordinate_end_row() {
253 // None => {
254 // continue;
255 // }
256 // Some(e) => { *e.get_num() }
257 // };
258 // let end_col = match item.get_coordinate_end_col() {
259 // None => {
260 // continue;
261 // }
262 // Some(e) => { *e.get_num() }
263 // };
264 // for row in start_row..=end_row {
265 // for col in start_col..=end_col {
266 // data[row as usize - 1][col as usize - 1]["state"] = 0.into();
267 // }
268 // }
269 // data[start_row as usize - 1][start_col as usize - 1]["rowspan"] = JsonValue::from(end_row - start_row + 1);
270 // data[start_row as usize - 1][start_col as usize - 1]["colspan"] = JsonValue::from(if end_col == start_col { 1 } else { end_col - start_col + 1 });
271 // data[start_row as usize - 1][start_col as usize - 1]["state"] = 1.into();
272 // }
273 //
274 // data
275 //}
276 ///// 导出全部表结构
277 //pub fn export_array_all(filename: &str) -> JsonValue {
278 // let path = Path::new(filename);
279 // let book = match reader::xlsx::read(path) {
280 // Ok(e) => e,
281 // Err(e) => {
282 // if filename.ends_with(".xls") {
283 // let mut workbook: Xls<_> = match open_workbook(path) {
284 // Ok(e) => e,
285 // Err(e) => {
286 // error!("加载xlsx错误: {}", e);
287 // return array![];
288 // }
289 // };
290 // let mut data = array![];
291 // for name in workbook.sheet_names() {
292 // if let Ok(range) = workbook.worksheet_range(&name) {
293 // let mut rows = array![];
294 // for row in range.rows() {
295 // let mut cols = array![];
296 // for col in row {
297 // let t = object! {
298 // value:col.to_string().trim().to_string(),
299 // image:""
300 // };
301 // let _ = cols.push(t);
302 // }
303 // let _ = rows.push(cols);
304 // }
305 // data.push(rows).unwrap();
306 // }
307 // }
308 // return data;
309 // }
310 // error!("加载xlsx错误: {}", e);
311 // return array![];
312 // }
313 // };
314 //
315 // let mut data = array![];
316 // for sheet in book.get_sheet_collection() {
317 // let total_row = sheet.get_highest_row();
318 // let total_col = sheet.get_highest_column();
319 // let mut rows = array![];
320 // for row in 1..=total_row {
321 // let mut cols = array![];
322 // for col in 1..=total_col {
323 // let value = sheet.get_value((col, row));
324 // let mut t = object! {
325 // value:value,
326 // image:""
327 // };
328 // let row_col = CellCoordinates {
329 // row,
330 // col,
331 // };
332 // match sheet.get_image(row_col) {
333 // None => {}
334 // Some(e) => {
335 // let extension = Path::new(e.get_image_name())
336 // .extension()
337 // .and_then(|ext| ext.to_str());
338 // t["image"] = match extension {
339 // Some(ext) => format!("data:image/{};base64,{}", ext, e.get_image_data_base64()),
340 // None => format!("data:image/png;base64,{}", e.get_image_data_base64())
341 // }.into();
342 // }
343 // };
344 //
345 // let _ = cols.push(t);
346 // }
347 // let _ = rows.push(cols);
348 // }
349 // data.push(rows).unwrap();
350 // }
351 //
352 // data
353 //}
354
355 /// 导出表结构带图片
356 pub fn export_array_image(filename: &str, page: usize) -> JsonValue {
357 let path = Path::new(filename);
358 let book = match umya_spreadsheet::reader::xlsx::read(path) {
359 Ok(e) => e,
360 Err(e) => {
361 error!("加载xlsx错误: {}", e);
362 return array![];
363 }
364 };
365
366 let page_data = match book.get_sheet(&page) {
367 None => {
368 error!("页面不存在");
369 return array![];
370 }
371 Some(e) => e
372 };
373
374 let total_row = page_data.get_highest_row();
375 let total_col = page_data.get_highest_column();
376
377 let mut data = vec![];
378
379 for row in 1..=total_row {
380
381 // 获取行信息
382 let row_info = match page_data.get_row_dimension(&row) {
383 None => continue,
384 Some(e) => e
385 };
386 // 获取行高
387 let row_height = *row_info.clone().get_height();
388
389
390 let mut row_data = vec![];
391 // 列数据
392 for col in 1..=total_col {
393 let mut cell = Cell::new();
394 cell.row = row as usize;
395 cell.col = col as usize;
396 cell.value = page_data.get_value((col, row));
397 cell.style.height = row_height;
398
399 cell.style.width = match page_data.get_column_dimension_by_number(&col) {
400 None => 20.0,
401 Some(e) => *e.get_width()
402 };
403
404 let style = page_data.get_style((col, row)).clone();
405
406 // font
407 match style.get_font() {
408 None => {}
409 Some(font) => {
410 cell.style.font_family = font.get_font_name().get_val().to_string();
411 cell.style.font_size = *font.get_size();
412 cell.style.font_weight = if font.get_font_bold().get_val().to_string() == "true" { "bold".to_string() } else { "normal".to_string() };
413 cell.style.font_style = if font.get_font_italic().get_val().to_string() == "true" { "italic".to_string() } else { "".to_string() };
414 cell.style.font_strike = if font.get_font_strike().get_val().to_string() == "true" { "strike".to_string() } else { "".to_string() };
415 cell.style.text_decoration = font.get_font_underline().get_val().get_value_string().to_string();
416 if cell.style.text_decoration == "none" {
417 cell.style.text_decoration = "".to_string();
418 }
419 cell.style.vertical_align = font.get_vertical_text_alignment().get_val().get_value_string().to_string();
420 cell.style.color = style.get_font().unwrap().get_color().clone().clone().get_argb().to_string();
421 }
422 };
423
424 match style.get_alignment() {
425 None => {}
426 Some(alignment) => {
427 cell.style.text_align = alignment.get_horizontal().get_value_string().to_string();
428 cell.style.vertical_align = alignment.get_vertical().get_value_string().to_string();
429 cell.style.text_wrap = if alignment.get_wrap_text().to_string() == "true" { "normal".to_string() } else { "nowrap".to_string() };
430 }
431 }
432
433 //
434 // match style.get_fill() {
435 // None => {}
436 // Some(fill) => {
437 // // fill.get_pattern_fill().unwrap().get_background_color().clone();
438 // }
439 // }
440 let _ = style.get_background_color().is_none();
441
442 match style.get_borders() {
443 None => {}
444 Some(borders) => {
445 if borders.get_top().get_style().get_value_string() != "none" {
446 cell.style.border_top = "1px solid #000000".to_string();
447 }
448 if borders.get_right().get_style().get_value_string() != "none" {
449 cell.style.border_right = "1px solid #000000".to_string();
450 }
451
452 if borders.get_bottom().get_style().get_value_string() != "none" {
453 cell.style.border_bottom = "1px solid #000000".to_string();
454 }
455
456 if borders.get_left().get_style().get_value_string() != "none" {
457 cell.style.border_left = "1px solid #000000".to_string();
458 }
459 }
460 }
461
462 let row_col = CellCoordinates {
463 row,
464 col,
465 };
466
467 match page_data.get_image(row_col) {
468 None => {}
469 Some(e) => {
470 cell.is_image = true;
471 let extension = Path::new(e.get_image_name())
472 .extension()
473 .and_then(|ext| ext.to_str());
474 cell.image = match extension {
475 Some(ext) => format!("data:image/{};base64,{}", ext, e.get_image_data_base64()),
476 None => format!("data:image/png;base64,{}", e.get_image_data_base64())
477 };
478
479 cell.image_n = e.get_image_name().parse().unwrap();
480 match e.get_two_cell_anchor() {
481 None => {}
482 Some(e) => {
483 cell.image_from_col_off = *e.get_from_marker().get_col_off() as usize;
484 cell.image_from_row_off = *e.get_from_marker().get_row_off() as usize;
485 cell.image_from_col = *e.get_from_marker().get_col();
486 cell.image_from_row = *e.get_from_marker().get_row();
487
488 cell.image_to_col_off = *e.get_to_marker().get_col_off() as usize;
489 cell.image_to_row_off = *e.get_to_marker().get_row_off() as usize;
490 cell.image_to_col = *e.get_to_marker().get_col();
491
492 match e.get_picture() {
493 None => {}
494 Some(e) => {
495 cell.image_id = e.get_non_visual_picture_properties().get_non_visual_drawing_properties().get_id().to_string();
496 if let Some(outline) = e.get_shape_properties().get_outline() {
497 cell.image_w_w = outline.get_width().to_string().parse::<usize>().unwrap();
498 cell.image_h_w = match outline.get_miter() {
499 Some(miter) => miter.get_limit().to_string().parse::<usize>().unwrap_or_else(|e| {
500 error!("{e}");
501 0
502 }),
503 None => 0,
504 };
505 } else {
506 cell.image_w_w = 0;
507 cell.image_h_w = 0;
508 }
509
510
511 match e.get_shape_properties().get_transform2d() {
512 None => {}
513 Some(e) => {
514 cell.image_w = e.get_extents().get_cx().to_string().parse::<f64>().unwrap();
515 cell.image_h = e.get_extents().get_cy().to_string().parse::<f64>().unwrap();
516
517 cell.image_x = e.get_offset().get_x().to_string().parse::<f64>().unwrap();
518 cell.image_y = e.get_offset().get_y().to_string().parse::<f64>().unwrap();
519 // cell.image_x_w = e.get_x().to_string().parse::<usize>().unwrap();
520 // cell.image_y_w = e.get_y().to_string().parse::<usize>().unwrap();
521 }
522 }
523 }
524 }
525 }
526 }
527 }
528 };
529 row_data.push(cell.json().clone());
530 }
531 data.push(row_data.clone());
532 }
533
534 // 获取合并信息
535 let merge = page_data.get_merge_cells();
536 for item in merge.iter() {
537 let start_row = match item.get_coordinate_start_row() {
538 None => {
539 continue;
540 }
541 Some(e) => { *e.get_num() }
542 };
543 let start_col = match item.get_coordinate_start_col() {
544 None => {
545 continue;
546 }
547 Some(e) => { *e.get_num() }
548 };
549 let end_row = match item.get_coordinate_end_row() {
550 None => {
551 continue;
552 }
553 Some(e) => { *e.get_num() }
554 };
555 let end_col = match item.get_coordinate_end_col() {
556 None => {
557 continue;
558 }
559 Some(e) => { *e.get_num() }
560 };
561
562 for row in start_row..=end_row {
563 for col in start_col..=end_col {
564 data[row as usize - 1][col as usize - 1]["state"] = 0.into();
565 }
566 }
567 data[start_row as usize - 1][start_col as usize - 1]["state"] = 1.into();
568 data[start_row as usize - 1][start_col as usize - 1]["rowspan"] = JsonValue::from(end_row - start_row + 1);
569 data[start_row as usize - 1][start_col as usize - 1]["colspan"] = JsonValue::from(if end_col == start_col { 1 } else { end_col - start_col + 1 });
570 }
571 JsonValue::from(data)
572 }
573
574 /// 导出指定行的自定义数据
575 /// * filename 文件路径
576 /// * page 页码 1开始
577 /// * start_line 开始行
578 /// * end_line 结束行
579 /// * partition 隔断
580 /// * headers 头部信息
581 pub fn export_custom_range(filename: &str, page: usize, start_line: u32, end_line: u32, partition: &str, headers: Vec<Head>) -> JsonValue {
582 let path = Path::new(filename);
583 let book = match reader::xlsx::read(path) {
584 Ok(e) => e,
585 Err(e) => {
586 error!("加载xlsx错误: {}", e);
587 return array![];
588 }
589 };
590 let page = page - 1;
591 let total_col = book.get_sheet(&page).unwrap().get_highest_column();
592 let mut data = object! {};
593
594 for row in start_line..=end_line {
595 let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
596 if row_info {
597 continue;
598 }
599 for col in 1..=total_col {
600 let value = book.get_sheet(&page).unwrap().get_value((col, row));
601 if value.is_empty() {
602 continue;
603 }
604 for header in headers.iter() {
605 if value.contains(header.title.as_str()) {
606 let res = value.split(partition).collect::<Vec<&str>>();
607 data[res[0].trim().to_string()] = res[1].trim().trim_start_matches('"').trim_end_matches('"').into();
608 }
609 }
610 }
611 }
612 data
613 }
614
615
616 /// 导出指定头内容的对象组
617 /// * filename 文件路径
618 /// * page 页码 1开始
619 /// * header_line 第几行开始
620 /// * headers 头部信息
621 pub fn export_new(filename: &str, page: usize, start_line: u32, headers: Vec<Head>) -> JsonValue {
622 let path = Path::new(filename);
623 let book = match reader::xlsx::read(path) {
624 Ok(e) => e,
625 Err(e) => {
626 error!("加载xlsx错误: {}", e);
627 return array![];
628 }
629 };
630 let page = page - 1;
631
632 let total_row = match book.get_sheet(&page) {
633 None => {
634 error!("加载xlsx行数错误: {}", page);
635 return array![];
636 }
637 Some(e) => e.get_highest_row()
638 };
639
640 let total_col = book.get_sheet(&page).unwrap().get_highest_column();
641
642 let mut head = HashMap::new();
643 for row in start_line..=start_line {
644 let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
645 if row_info {
646 continue;
647 }
648 for col in 1..=total_col {
649 let value = book.get_sheet(&page).unwrap().get_value((col, row)).trim().to_string();
650 for header in headers.iter() {
651 if header.title == value {
652 head.insert(col, header.clone());
653 }
654 }
655 }
656 }
657 let mut list = array![];
658 for row in start_line + 1..=total_row {
659 let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
660 if row_info {
661 continue;
662 }
663 let mut data = object! {};
664 for col in 1..=total_col {
665 let value = book.get_sheet(&page).unwrap().get_value((col, row)).trim().to_string();
666 if head.contains_key(&col) {
667 let info = head.get(&col).unwrap().clone();
668 data[info.field] = JsonValue::from(value);
669 }
670 }
671 list.push(data).unwrap();
672 }
673 list
674 }
675}
676
677
678