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::{BlipFill, MarkerType, NonVisualDrawingProperties, NonVisualPictureDrawingProperties, NonVisualPictureProperties, Picture, ShapeProperties, TwoCellAnchor};
use umya_spreadsheet::drawing::{AdjustValueList, Blip, FillRectangle, Miter, NoFill, Outline, PictureLocks, Point2DType, PositiveSize2DType, PresetGeometry, Stretch, 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"] != 0 && col["colspan"] != 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![]);
if fs::write(path.clone(), images).is_ok() {
let mut image = Image::default();
let mut two_cell = TwoCellAnchor::default();
let mut transform_2d = Transform2D::default();
let mut point2dtype = Point2DType::default();
let x = col["image_x"].to_string().parse::<f64>().unwrap() * 70823.0;
let y = col["image_y"].to_string().parse::<f64>().unwrap() * 32743.37;
point2dtype.set_x(x as i64);
point2dtype.set_y(y as i64);
let mut positive_size2dtype = PositiveSize2DType::default();
let cx = col["image_w"].to_string().parse::<f64>().unwrap() * 70823.0;
let cy = col["image_h"].to_string().parse::<f64>().unwrap() * 32743.37;
positive_size2dtype.set_cx(cx as i64);
positive_size2dtype.set_cy(cy as i64);
transform_2d.set_offset(point2dtype);
transform_2d.set_extents(positive_size2dtype);
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::<i32>().unwrap());
marker.set_row_off(col["image_from_row_off"].to_string().parse::<i32>().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::<i32>().unwrap());
to_marker.set_row_off(col["image_to_row_off"].to_string().parse::<i32>().unwrap());
two_cell.set_to_marker(to_marker);
let mut picture = Picture::default();
let mut non_visual_picture_properties = NonVisualPictureProperties::default();
let mut non_visual_drawing_properties = NonVisualDrawingProperties::default();
let mut picture_locks = PictureLocks::default();
picture_locks.set_no_change_aspect(true);
non_visual_drawing_properties.set_name(col["image_n"].to_string());
non_visual_drawing_properties.set_id(col["image_id"].to_string().parse::<u32>().unwrap_or(0));
non_visual_picture_properties.set_non_visual_drawing_properties(non_visual_drawing_properties);
let mut non_visual_picture_drawing_properties = NonVisualPictureDrawingProperties::default();
non_visual_picture_drawing_properties.set_picture_locks(picture_locks);
non_visual_picture_properties.set_non_visual_picture_drawing_properties(non_visual_picture_drawing_properties);
picture.set_non_visual_picture_properties(non_visual_picture_properties);
let mut shape_properties = ShapeProperties::default();
shape_properties.set_transform2d(transform_2d);
let mut geometry = PresetGeometry::default();
let adjust_value_list = AdjustValueList::default();
geometry.set_adjust_value_list(adjust_value_list);
geometry.set_geometry("rect");
shape_properties.set_geometry(geometry);
let mut outline = Outline::default();
let image_w_w = col["image_w_w"].to_string().parse::<u32>().unwrap();
let image_h_w = col["image_h_w"].to_string().parse::<u32>().unwrap();
outline.set_width(image_w_w);
outline.set_cap_type("flat");
let tt = NoFill::default();
outline.set_no_fill(tt);
let mut miter = Miter::default();
miter.set_limit(image_h_w as i32);
outline.set_miter(miter);
shape_properties.set_outline(outline);
picture.set_shape_properties(shape_properties);
let mut blip_fill = BlipFill::default();
let mut blip = Blip::default();
let r = blip.get_image_mut();
let rrr = fs::read(path.clone()).unwrap();
r.set_image_data(rrr);
r.set_image_name(col["image_n"].to_string());
let ttt = r.clone().clone();
blip.set_image(ttt);
blip_fill.set_blip(blip);
let mut stretch = Stretch::default();
stretch.set_fill_rectangle(FillRectangle::default());
blip_fill.set_stretch(stretch);
picture.set_blip_fill(blip_fill);
two_cell.set_picture(picture);
image.set_two_cell_anchor(two_cell.clone());
image_cells.push(image);
fs::remove_file(path.clone()).expect("TODO: panic message");
}
}
_ => {}
}
}
if col["rowspan"] != 0 && col["colspan"] != 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![65; (count - 1) as usize];
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![65; (count - 1) as usize];
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![65; (count - 1) as usize];
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, path.to_str().unwrap());
false
}
}
}
}