use std::fs;
use std::path::Path;
use base64::Engine;
use base64::engine::general_purpose::STANDARD;
use json::{JsonValue, object};
use umya_spreadsheet::{FontSize, HorizontalAlignmentValues, Image, new_file, Spreadsheet, VerticalAlignmentValues, writer};
use umya_spreadsheet::drawing::spreadsheet::{ConnectionShape, MarkerType, ShapeProperties, TwoCellAnchor};
use umya_spreadsheet::drawing::{AdjustValueList, PresetGeometry, Transform2D};
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"].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
}
pub fn set_page_array_image(&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 page_info = self.excel.get_sheet_mut(&self.page).unwrap();
let mut merge_cells = vec![];
let mut image_cells = vec![];
let mut row_line = 1;
for row in data.members() {
let mut col_line = 1;
for col in row.members() {
let text = col["value"].as_str().unwrap_or("");
page_info.get_cell_mut((col_line, row_line)).set_value_string(text);
for (key, value) in col.entries() {
match key {
"style" => {
let style = value.clone();
let width = style["width"].to_string().replace("mm", "").as_str().parse::<f64>().unwrap();
page_info.get_column_dimension_by_number_mut(&col_line).set_width(width / 2.54);
let height = style["height"].to_string().replace("mm", "").as_str().parse::<f64>().unwrap();
page_info.get_row_dimension_mut(&row_line).set_height(height / 0.3612);
let size = style["font-size"].to_string().replace("px", "").parse::<f64>().unwrap();
let mut fontsize = FontSize::default();
let tt = fontsize.set_val(size);
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_font_mut().set_font_size(tt.clone());
match style["text-align"].as_str().unwrap_or("center") {
"center" => {
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Center);
}
"left" => {
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Left);
}
"right" => {
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Right);
}
"justify" => {
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Justify);
}
_ => {
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_vertical(VerticalAlignmentValues::Center);
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_alignment_mut()
.set_horizontal(HorizontalAlignmentValues::Center);
}
}
if !style["border-top"].is_empty() {
page_info.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 !style["border-right"].is_empty() {
page_info.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 !style["border-bottom"].is_empty() {
page_info.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 !style["border-left"].is_empty() {
page_info.get_cell_mut((col_line, row_line))
.get_style_mut()
.get_borders_mut()
.get_left_mut()
.set_border_style(umya_spreadsheet::Border::BORDER_THIN);
}
}
"image" => {
if value.is_empty() {
continue;
}
let path = format!("./{}_{}_{}", col_line, row_line, col["image_n"]);
let images = value.as_str().unwrap().split(",").collect::<Vec<&str>>();
let images = images[1];
let images = STANDARD.decode(images).unwrap_or_else(|_| vec![]);
match fs::write(path.clone(), images) {
Ok(_) => {
let mut image = Image::default();
let mut two_cell = TwoCellAnchor::default();
let mut connection_shape = ConnectionShape::default();
let mut shape_properties = ShapeProperties::default();
let mut geometry = PresetGeometry::default();
let adjust_value_list = AdjustValueList::default();
let mut transform_2d = Transform2D::default();
transform_2d.set_height(col["image_h_w"].to_string().parse::<usize>().unwrap());
transform_2d.set_width(col["image_w_w"].to_string().parse::<usize>().unwrap());
transform_2d.set_x(col["image_x_w"].to_string().parse::<usize>().unwrap());
transform_2d.set_y(col["image_y_w"].to_string().parse::<usize>().unwrap());
shape_properties.set_transform2d(transform_2d);
geometry.set_adjust_value_list(adjust_value_list);
shape_properties.set_geometry(geometry);
connection_shape.set_shape_properties(shape_properties.clone());
let mut marker = MarkerType::default();
marker.set_row(col["image_from_row"].to_string().parse::<u32>().unwrap());
marker.set_col(col["image_from_col"].to_string().parse::<u32>().unwrap());
marker.set_col_off(col["image_from_col_off"].to_string().parse::<usize>().unwrap());
marker.set_row_off(col["image_from_row_off"].to_string().parse::<usize>().unwrap());
two_cell.set_from_marker(marker.clone());
let mut to_marker = MarkerType::default();
to_marker.set_row(col["image_to_row"].to_string().parse::<u32>().unwrap());
to_marker.set_col(col["image_to_col"].to_string().parse::<u32>().unwrap());
to_marker.set_col_off(col["image_to_col_off"].to_string().parse::<usize>().unwrap());
to_marker.set_row_off(col["image_to_row_off"].to_string().parse::<usize>().unwrap());
image.new_image(&*path, marker);
two_cell.set_connection_shape(connection_shape);
two_cell.set_to_marker(to_marker);
image.set_two_cell_anchor(two_cell.clone());
image_cells.push(image);
fs::remove_file(path.clone()).expect("TODO: panic message");
}
Err(_) => {}
}
}
_ => {}
}
}
if col["rowspan"] != JsonValue::from(0) && col["colspan"] != JsonValue::from(0) {
let start = Write::int_to_str_new(col["col"].to_string().parse::<u32>().unwrap() - 1, col["row"].to_string().parse::<u32>().unwrap());
let end = Write::int_to_str_new(
col["col"].to_string().parse::<u32>().unwrap() + col["colspan"].as_u32().unwrap() - 2,
col["row"].to_string().parse::<u32>().unwrap() + col["rowspan"].as_u32().unwrap() - 1,
);
merge_cells.push(format!("{}:{}", start, end));
}
col_line += 1;
}
row_line += 1;
}
for image_cell in image_cells.iter() {
page_info.add_image(image_cell.clone());
}
for item in merge_cells.iter() {
page_info.add_merge_cells(item);
}
self
}
fn int_to_str_new(row: u32, col: u32) -> String {
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();
format!("{}{}", row_t, col)
}
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();
format!("{}{}", row_t, col)
}
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());
match writer::xlsx::write(&self.excel, path) {
Ok(_) => true,
Err(e) => {
println!("保存错误: {} {}", e.to_string(), path.to_str().unwrap());
false
}
}
}
}