use std::collections::HashMap;
use std::path::Path;
use json::{array, JsonValue, object};
use log::{error, info};
use umya_spreadsheet::{Alignment, EnumTrait, reader};
use umya_spreadsheet::helper::coordinate::CellCoordinates;
use crate::{Cell, Head};
pub struct Read {}
impl Read {
pub fn export(filename: &str, page: usize, header_line: u32, headers: Vec<Head>) -> JsonValue {
let path = Path::new(filename);
let book = match reader::xlsx::read(path) {
Ok(e) => e,
Err(e) => {
error!("加载xlsx错误: {}", e);
return array![];
}
};
let total_row = match book.get_sheet(&page) {
None => {
error!("加载xlsx行数错误: {}", page);
return array![];
}
Some(e) => e.get_highest_row()
};
let total_col = book.get_sheet(&page).unwrap().get_highest_column();
let mut head = HashMap::new();
for row in header_line..=header_line {
let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
if row_info {
continue;
}
for col in 1..=total_col {
let value = book.get_sheet(&page).unwrap().get_value((col, row));
for header in headers.iter() {
if header.title == value {
head.insert(col, header.clone());
}
}
}
}
let mut list = array![];
for row in header_line + 1..=total_row {
let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
if row_info {
continue;
}
let mut data = object! {};
for col in 1..=total_col {
let value = book.get_sheet(&page).unwrap().get_value((col, row));
if !head.get(&col).is_none() {
let info = head.get(&col).unwrap().clone();
data[info.field] = JsonValue::from(value);
}
}
list.push(data).unwrap();
}
list
}
pub fn export_array(filename: &str, page: usize) -> JsonValue {
let path = Path::new(filename);
let book = match umya_spreadsheet::reader::xlsx::read(path) {
Ok(e) => e,
Err(e) => {
error!("加载xlsx错误: {}", e);
return array![];
}
};
let sheet = match book.get_sheet(&page) {
None => {
error!("读取页面错误");
return array![];
}
Some(e) => e
};
let total_row = sheet.get_highest_row();
let total_col = sheet.get_highest_column();
let mut data = array![];
for row in 1..=total_row {
if sheet.get_row_dimension(&row).is_none() {
continue;
}
let row_info = sheet.get_row_dimension(&row).unwrap();
let row_height = row_info.clone().get_height().clone();
let mut row_data = array![];
for col in 1..=total_col {
let value = sheet.get_value((col, row));
let mut col_width = 20.0;
let tt = sheet.get_column_dimension_by_number(&col).is_none();
if !tt {
let col_info = sheet.get_column_dimension_by_number(&col).unwrap();
col_width = col_info.clone().get_width().clone();
}
let background_color = "";
let style = sheet.get_style((col, row)).clone();
let mut style_size = 0;
if !style.get_font().is_none() {
style_size = style.get_font().clone().unwrap().get_size().clone() as i32;
}
let ttt = Alignment::default();
let alignment = style.get_alignment().clone().unwrap_or(&ttt);
let alignment = alignment.get_horizontal().clone();
let alignment = alignment.get_value_string();
let horizontal = if alignment == "general" { "center" } else { alignment };
let alignment = style.get_alignment().clone().unwrap_or(&ttt);
let alignment = alignment.get_vertical().clone();
let vertical = alignment.get_value_string();
let alignment = style.get_alignment().clone().unwrap_or(&ttt);
let alignment = alignment.get_wrap_text().clone();
let wrap_text = if alignment { "normal" } else { "none" };
let mut style_left = false;
let mut style_right = false;
let mut style_top = false;
let mut style_bottom = false;
let is_borders = style.get_borders().is_none();
if !is_borders {
let borders = style.get_borders().clone().unwrap();
style_left = if borders.get_left().get_color().get_indexed().clone() == 8 as u32 { true } else { false };
if !style_left {
style_left = if borders.get_left().get_color().get_indexed().clone() == 64 as u32 { true } else { false };
}
style_right = if borders.get_right().get_color().get_indexed().clone() == 8 as u32 { true } else { false };
if !style_right {
style_right = if borders.get_right().get_color().get_indexed().clone() == 64 as u32 { true } else { false };
}
style_top = if borders.get_top().get_color().get_indexed().clone() == 8 as u32 { true } else { false };
if !style_top {
style_top = if borders.get_top().get_color().get_indexed().clone() == 64 as u32 { true } else { false };
}
style_bottom = if borders.get_bottom().get_color().get_indexed().clone() == 8 as u32 { true } else { false };
if !style_bottom {
style_bottom = if borders.get_bottom().get_color().get_indexed().clone() == 64 as u32 { true } else { false };
}
}
let col_data = object! {
value:value,
row:row,
col:col,
style:object!{
height:format!("{}mm",row_height*0.3612),
width:format!("{}mm",col_width*2.54),
"font-size":style_size,
"background-color":background_color,
"text-align":horizontal,
"vertical-align":vertical,
"text-wrap":wrap_text
},
rowspan:1,
colspan:1,
border:object! {
left:style_left.clone(),
top:style_top.clone(),
bottom:style_bottom.clone(),
right:style_right.clone()
},
state:1
};
row_data.push(col_data).unwrap();
}
data.push(row_data).unwrap();
}
let merge = book.get_sheet(&page).unwrap().get_merge_cells().clone().clone();
for item in merge.iter() {
let start_row = match item.get_coordinate_start_row().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
let start_col = match item.get_coordinate_start_col().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
let end_row = match item.get_coordinate_end_row().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
let end_col = match item.get_coordinate_end_col().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
for row in start_row..=end_row {
for col in start_col..=end_col {
data[row as usize - 1][col as usize - 1]["state"] = 0.into();
}
}
data[start_row as usize - 1][start_col as usize - 1]["rowspan"] = JsonValue::from(end_row - start_row + 1);
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 });
data[start_row as usize - 1][start_col as usize - 1]["state"] = 1.into();
}
return data;
}
pub fn export_array_image(filename: &str, page: usize) -> JsonValue {
let path = Path::new(filename);
let book = match umya_spreadsheet::reader::xlsx::read(path) {
Ok(e) => e,
Err(e) => {
error!("加载xlsx错误: {}", e);
return array![];
}
};
let page_data = match book.get_sheet(&page) {
None => {
error!("页面不存在");
return array![];
}
Some(e) => e
};
let total_row = page_data.get_highest_row();
let total_col = page_data.get_highest_column();
let mut data = vec![];
for row in 1..=total_row {
let row_info = match page_data.get_row_dimension(&row) {
None => continue,
Some(e) => e
};
let row_height = row_info.clone().get_height().clone();
let mut row_data = vec![];
for col in 1..=total_col {
let mut cell = Cell::default();
cell.row = row as usize;
cell.col = col as usize;
cell.value = page_data.get_value((col, row));
cell.style.height = row_height;
cell.style.width = match page_data.get_column_dimension_by_number(&col) {
None => 20.0,
Some(e) => e.get_width().clone()
};
let style = page_data.get_style((col, row)).clone();
match style.get_font() {
None => {}
Some(font) => {
cell.style.font_family = font.get_font_name().get_val().to_string();
cell.style.font_size = font.get_size().clone();
cell.style.font_weight = if font.get_font_bold().get_val().to_string() == "true" { "bold".to_string() } else { "normal".to_string() };
cell.style.font_style = if font.get_font_italic().get_val().to_string() == "true" { "italic".to_string() } else { "".to_string() };
cell.style.font_strike = if font.get_font_strike().get_val().to_string() == "true" { "strike".to_string() } else { "".to_string() };
cell.style.text_decoration = font.get_font_underline().get_val().get_value_string().to_string();
if cell.style.text_decoration == "none" {
cell.style.text_decoration = "".to_string();
}
cell.style.vertical_align = font.get_vertical_text_alignment().get_val().get_value_string().to_string();
cell.style.color = style.get_font().clone().unwrap().get_color().clone().clone().get_argb().to_string();
}
};
match style.get_alignment() {
None => {}
Some(alignment) => {
cell.style.text_align = alignment.get_horizontal().get_value_string().to_string();
cell.style.vertical_align = alignment.get_vertical().get_value_string().to_string();
cell.style.text_wrap = if alignment.get_wrap_text().to_string() == "true" { "normal".to_string() } else { "nowrap".to_string() };
}
}
match style.get_background_color() {
None => {}
Some(_) => {}
}
match style.get_borders() {
None => {}
Some(borders) => {
if borders.get_top().get_style().get_value_string() != "none" {
cell.style.border_top = format!("1px solid #000000");
}
if borders.get_right().get_style().get_value_string() != "none" {
cell.style.border_right = format!("1px solid #000000");
}
if borders.get_bottom().get_style().get_value_string() != "none" {
cell.style.border_bottom = format!("1px solid #000000");
}
if borders.get_left().get_style().get_value_string() != "none" {
cell.style.border_left = format!("1px solid #000000");
}
}
}
let row_col = CellCoordinates {
row,
col,
};
match page_data.get_image(row_col) {
None => {}
Some(e) => {
cell.is_image = true;
let extension = Path::new(e.get_image_name())
.extension()
.and_then(|ext| ext.to_str());
cell.image = match extension {
Some(ext) => format!("data:image/{};base64,{}", ext, e.get_image_data_base64()),
None => format!("data:image/png;base64,{}", e.get_image_data_base64())
};
cell.image_n = e.get_image_name().parse().unwrap();
match e.get_two_cell_anchor() {
None => {}
Some(e) => {
let col_off = e.get_from_marker().get_col_off();
let row_off = e.get_from_marker().get_row_off();
cell.image_from_col_off = *col_off;
cell.image_from_row_off = *row_off;
cell.image_from_col = *e.get_from_marker().get_col();
cell.image_from_row = *e.get_from_marker().get_row();
let col_off = e.get_to_marker().get_col_off();
let row_off = e.get_to_marker().get_row_off();
cell.image_to_col_off = *col_off;
cell.image_to_row_off = *row_off;
cell.image_to_col = *e.get_to_marker().get_col();
match e.get_picture() {
None => {}
Some(e) => {
match e.get_shape_properties().get_transform2d() {
None => {}
Some(e) => {
cell.image_h = e.get_height().to_string().parse::<f64>().unwrap();
cell.image_w = e.get_width().to_string().parse::<f64>().unwrap();
cell.image_h_w = e.get_height().to_string().parse::<usize>().unwrap();
cell.image_w_w = e.get_width().to_string().parse::<usize>().unwrap();
cell.image_x = e.get_x().to_string().parse::<f64>().unwrap();
cell.image_y = e.get_y().to_string().parse::<f64>().unwrap();
cell.image_x_w = e.get_x().to_string().parse::<usize>().unwrap();
cell.image_y_w = e.get_y().to_string().parse::<usize>().unwrap();
}
}
}
}
}
}
}
};
row_data.push(cell.json().clone());
}
data.push(row_data.clone());
}
let merge = page_data.get_merge_cells().clone();
for item in merge.iter() {
let start_row = match item.get_coordinate_start_row().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
let start_col = match item.get_coordinate_start_col().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
let end_row = match item.get_coordinate_end_row().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
let end_col = match item.get_coordinate_end_col().clone() {
None => {
continue;
}
Some(e) => { e.get_num().clone() }
};
for row in start_row..=end_row {
for col in start_col..=end_col {
data[row as usize - 1][col as usize - 1]["state"] = 0.into();
}
}
data[start_row as usize - 1][start_col as usize - 1]["state"] = 1.into();
data[start_row as usize - 1][start_col as usize - 1]["rowspan"] = JsonValue::from(end_row - start_row + 1);
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 });
}
return JsonValue::from(data);
}
pub fn export_custom_range(filename: &str, page: usize, start_line: u32, end_line: u32, partition: &str, headers: Vec<Head>) -> JsonValue {
let path = Path::new(filename);
let book = match reader::xlsx::read(path) {
Ok(e) => e,
Err(e) => {
error!("加载xlsx错误: {}", e);
return array![];
}
};
let page = page - 1;
let total_col = book.get_sheet(&page).unwrap().get_highest_column();
let mut data = object! {};
for row in start_line..=end_line {
let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
if row_info {
continue;
}
for col in 1..=total_col {
let value = book.get_sheet(&page).unwrap().get_value((col, row));
if value.is_empty() {
continue;
}
for header in headers.iter() {
if value.contains(header.title.as_str()) {
let res = value.split(partition).collect::<Vec<&str>>();
data[res[0].trim().to_string()] = res[1].trim().trim_start_matches('"').trim_end_matches('"').into();
}
}
}
}
data
}
pub fn export_new(filename: &str, page: usize, start_line: u32, headers: Vec<Head>) -> JsonValue {
let path = Path::new(filename);
let book = match reader::xlsx::read(path) {
Ok(e) => e,
Err(e) => {
error!("加载xlsx错误: {}", e);
return array![];
}
};
let page = page - 1;
let total_row = match book.get_sheet(&page) {
None => {
error!("加载xlsx行数错误: {}", page);
return array![];
}
Some(e) => e.get_highest_row()
};
let total_col = book.get_sheet(&page).unwrap().get_highest_column();
let mut head = HashMap::new();
for row in start_line..=start_line {
let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
if row_info {
continue;
}
for col in 1..=total_col {
let value = book.get_sheet(&page).unwrap().get_value((col, row)).trim().to_string();
for header in headers.iter() {
if header.title == value {
head.insert(col, header.clone());
}
}
}
}
let mut list = array![];
for row in start_line + 1..=total_row {
let row_info = book.get_sheet(&page).unwrap().get_row_dimension(&row).is_none();
if row_info {
continue;
}
let mut data = object! {};
for col in 1..=total_col {
let value = book.get_sheet(&page).unwrap().get_value((col, row)).trim().to_string();
if !head.get(&col).is_none() {
let info = head.get(&col).unwrap().clone();
data[info.field] = JsonValue::from(value);
}
}
list.push(data).unwrap();
}
list
}
}