use std::path::Path;
use json::{JsonValue, object};
use umya_spreadsheet::{FontSize, HorizontalAlignmentValues, new_file, Spreadsheet, VerticalAlignmentValues, writer};
use crate::Head;
pub struct Write {
path: String,
page: usize,
excel: Spreadsheet,
}
impl Write {
pub fn new(filepath: &str) -> Self {
let book = new_file();
Self {
path: filepath.to_string(),
page: 0,
excel: book,
}
}
pub fn set_page(&mut self, page: usize, name: &str, headers: Vec<Head>, data: JsonValue) -> &mut Self {
self.page = page - 1;
if self.page > 0 {
self.excel.new_sheet(name).unwrap();
}
self.excel.get_sheet_mut(&self.page).unwrap().set_name(name);
let mut row_line = 1;
let mut col_line = 1;
let mut header_key_value = object! {};
for header in headers.iter() {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line)).set_value_string(header.clone().title);
header_key_value[header.clone().field] = JsonValue::from(col_line);
col_line += 1;
}
for row in data.members() {
row_line += 1;
for (key, value) in row.entries() {
if header_key_value[key].is_empty() {
continue;
}
col_line = header_key_value[key].as_u32().unwrap();
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line)).set_value_string(value.to_string().clone());
}
}
self
}
pub fn set_page_array(&mut self, page: usize, name: &str, data: JsonValue) -> &mut Self {
self.page = page - 1;
if self.page > 0 {
self.excel.new_sheet(name).unwrap();
}
self.excel.get_sheet_mut(&self.page).unwrap().set_name(name);
let mut merge_cells = vec![];
let mut row_line = 1;
for row in data.members() {
let mut col_line = 1;
for col in row.members() {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line)).set_value_string(col["value"].as_str().unwrap_or("").to_string());
if !col["style"].is_empty() {
let style = col["style"].clone();
if !style["height"].is_empty() {
let height = style["height"].to_string().replace("m", "").as_str().parse::<f64>().unwrap();
self.excel.get_sheet_mut(&self.page).unwrap().get_row_dimension_mut(&row_line).set_height(height / 0.3612);
}
if !style["width"].is_empty() {
let width = style["width"].to_string().replace("m", "").as_str().parse::<f64>().unwrap();
self.excel.get_sheet_mut(&self.page).unwrap().get_column_dimension_by_number_mut(&col_line).set_width(width / 2.54);
}
if !style["font-size"].is_empty() {
let size = style["font-size"].to_string().parse::<f64>().unwrap();
let mut fontsize = FontSize::default();
let tt = fontsize.set_val(size);
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_font_mut().set_font_size(tt.clone());
}
if !style["text-align"].is_empty() {
match style["text-align"].as_str().unwrap() {
"center" => {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Center);
}
"left" => {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Left);
}
"right" => {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Right);
}
"bottom" => {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Bottom);
}
"justify" => {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Justify);
}
"fill" => {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Fill);
}
_ => {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Center);
}
}
}
}
if !col["border"]["top"].is_empty() {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_borders_mut()
.get_top_mut()
.set_border_style(umya_spreadsheet::Border::BORDER_THIN);
}
if !col["border"]["right"].is_empty() {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_borders_mut()
.get_right_mut()
.set_border_style(umya_spreadsheet::Border::BORDER_THIN);
}
if !col["border"]["bottom"].is_empty() {
self.excel.get_sheet_mut(&self.page).unwrap().get_cell_mut((col_line, row_line))
.get_style_mut()
.get_borders_mut()
.get_bottom_mut()
.set_border_style(umya_spreadsheet::Border::BORDER_THIN);
}
if !col["border"]["left"].is_empty() {
self.excel.get_sheet_mut(&self.page).unwrap()
.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_borders_mut()
.get_left_mut()
.set_border_style(umya_spreadsheet::Border::BORDER_THIN);
}
if col["rowspan"] != JsonValue::from(0) && col["colspan"] != JsonValue::from(0) {
let start = self.int_to_str(col["col"].to_string().parse::<u32>().unwrap(), col["row"].to_string().parse::<u32>().unwrap());
let end = self.int_to_str_js(col["col"].to_string().parse::<u32>().unwrap() + col["colspan"].as_u32().unwrap(), col["row"].to_string().parse::<u32>().unwrap() + col["rowspan"].as_u32().unwrap());
merge_cells.push(format!("{}:{}", start, end));
}
col_line += 1;
}
row_line += 1;
}
for item in merge_cells.iter() {
self.excel.get_sheet_mut(&self.page).unwrap().add_merge_cells(item);
}
self
}
fn int_to_str(&mut self, row: u32, col: u32) -> String {
let row = row - 1;
let count = row / 26 + 1;
let row = row % 26;
let ascii = row as u8 + 65;
let mut r = vec![];
for _ in 0..count - 1 {
r.push(65);
}
r.push(ascii);
let row_t = r.escape_ascii().to_string();
let col_t = col;
format!("{}{}", row_t, col_t)
}
fn int_to_str_js(&mut self, row: u32, col: u32) -> String {
let row = row - 2;
let col = col - 1;
let count = row / 26 + 1;
let row = row % 26;
let ascii = row as u8 + 65;
let mut r = vec![];
for _ in 0..count - 1 {
r.push(65);
}
r.push(ascii);
let row_t = r.escape_ascii().to_string();
let col_t = col;
format!("{}{}", row_t, col_t)
}
pub fn save(&mut self) -> bool {
let path = Path::new(self.path.as_str().clone());
match writer::xlsx::write(&self.excel, path) {
Ok(_) => true,
Err(e) => {
println!("保存错误: {} {}", e.to_string(), path.to_str().unwrap());
false
}
}
}
}