use crate::tina::data::binary::StoredFileData;
use crate::tina::data::excel::ExcelFileData;
use crate::tina::data::json::JsonToString;
use crate::tina::data::AppResult;
use crate::tina::excel::{IExcelRow, IExcelSheet};
use crate::tina::file::FileData;
use crate::tina::i18n::message::system_message::SystemMessage;
use crate::tina::server::session::Session;
use crate::tina::util::json::JsonUtil;
use crate::tina::util::not_empty::INotEmpty;
use crate::tina::util::string::AsStr;
use crate::tina::util::string_joiner::StringJoiner;
use crate::tina::util::Utility;
use crate::{app_system_error, app_system_error_with_msg, i18n_string};
use calamine::{DataType, Range, Reader, Xls, Xlsx};
use futures::Future;
use indexmap::IndexMap;
use serde::de::DeserializeOwned;
use serde_json::Value;
use std::io::BufReader;
use uuid::Uuid;
pub struct ExcelUtil;
impl ExcelUtil {
pub async fn write_excel<Data, F, R>(
session: &Session,
sheet_name: &str,
delete_on_drop: bool,
mut data_fn: F,
) -> AppResult<StoredFileData>
where
Data: IExcelSheet + IExcelRow + Default + Send + Sync + 'static,
F: FnMut() -> R + Send + 'static,
R: Future<Output = AppResult<Option<Data>>> + Send,
{
let (first_data, is_default_data) = match data_fn().await? {
Some(v) => (v, false),
None => (Data::default(), true),
};
let sheet_name = match sheet_name.not_empty() {
true => sheet_name.to_string(),
false => {
let s = first_data.get_sheet_name(session).await?;
match s.not_empty() {
true => s,
false => "Sheet".to_string(),
}
}
};
let field_column_mapping = first_data.get_field_column_mapping(session).await?;
let headers = field_column_mapping.iter().map(|v| v.1.as_str()).collect::<Vec<&str>>();
let field_dict = first_data.get_field_dict(session).await?;
let dict_service = session.get_application().get_dict_service()?;
let db_dict_map = match field_dict.is_empty() {
true => IndexMap::default(),
false => {
let dict_types = field_dict.iter().map(|v| v.1.as_str()).collect::<Vec<&str>>();
dict_service.get_dict_data_mapping_by_types(session, dict_types.as_slice()).await?
}
};
let dict_map = db_dict_map.iter().fold(IndexMap::new(), |mut m, (k, v)| {
for (fk, fv) in field_dict.iter() {
if k.as_str() == fv.as_str() {
m.insert(fk.to_string(), v);
}
}
m
});
let enum_map = first_data.get_field_enum_mapping(session).await?;
let mut excel = ExcelFileData::new(
format!("{}/{}_{}.xlsx", Utility::get_tmp_dir(true)?, sheet_name.as_str(), Uuid::new_v4().as_simple()),
delete_on_drop,
);
let handle = excel.create_write_handle()?;
let header_format = excel.generate_header_format(&handle)?;
let bool_format = excel.generate_bool_format(&handle)?;
let number_format = excel.generate_number_format(&handle)?;
let string_format = excel.generate_simple_string_format(&handle)?;
excel.add_header(&handle, &sheet_name, &headers, Some(&header_format), Some(&header_format), Some(&header_format))?;
let value_func = |mut data: IndexMap<String, Value>| {
field_column_mapping
.iter()
.map(|(field, _header)| match data.remove(field) {
None => (field, Value::Null),
Some(v) => (field, v),
})
.map(|(field, v)| {
if let Some(map) = dict_map.get(field.as_str()) {
return match map.get(&v.to_string_value()) {
Some(v) => Value::String(v.to_string()),
None => Value::Null,
};
}
if let Some(map) = enum_map.get(field) {
return match map.get(&v.to_string_value()) {
Some(v) => Value::String(v.to_string()),
None => Value::Null,
};
}
v
})
.collect::<Vec<Value>>()
};
if !is_default_data {
let data = first_data.get_field_datas(session).await?;
let value = value_func(data);
excel.add_value(
&handle,
&sheet_name,
serde_json::Value::Array(value),
Some(&bool_format),
Some(&number_format),
Some(&string_format),
)?;
}
while let Some(v) = data_fn().await? {
let data = v.get_field_datas(session).await?;
let value = value_func(data);
excel.add_value(
&handle,
&sheet_name,
serde_json::Value::Array(value),
Some(&bool_format),
Some(&number_format),
Some(&string_format),
)?;
}
excel.flush_and_close(handle)?;
Ok(excel.into_stored_file_data())
}
#[cfg(feature = "server-http")]
pub async fn export<Data, F, R>(
session: &Session,
sheet_name: &str,
data_fn: F,
) -> AppResult<crate::tina::data::http::response_stream::ResStream>
where
Data: IExcelSheet + IExcelRow + Default + Send + Sync + 'static,
F: FnMut() -> R + Send + 'static,
R: Future<Output = AppResult<Option<Data>>> + Send,
{
use std::str::FromStr;
let file_data = Self::write_excel(session, sheet_name, true, data_fn).await?;
let content_type = mime::Mime::from_str("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
.map_err(|err| app_system_error!("create mime type failed! {:?}", err))?;
Ok(crate::tina::data::http::response_stream::ResStream::success(
session,
file_data,
format!("{}.xlsx", sheet_name).as_str(),
content_type,
true,
))
}
#[cfg(feature = "server-http")]
pub async fn generate_import_template<Data: IExcelSheet + IExcelRow + Default + Send + Sync + 'static>(
session: &Session,
sheet_name: &str,
) -> AppResult<crate::tina::data::http::response_stream::ResStream> {
use std::str::FromStr;
let file_data = Self::write_excel(session, sheet_name, true, || async { Ok(None) as AppResult<Option<Data>> }).await?;
let content_type = mime::Mime::from_str("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
.map_err(|err| app_system_error!("create mime type failed! {:?}", err))?;
Ok(crate::tina::data::http::response_stream::ResStream::success(
session,
file_data,
format!("{}.xlsx", sheet_name).as_str(),
content_type,
true,
))
}
pub async fn import<Data>(session: &Session, file: FileData) -> AppResult<Vec<Data>>
where
Data: IExcelSheet + IExcelRow + DeserializeOwned + Default + Send + Sync + 'static,
{
Self::import_with_option(session, file, Some(vec![0]), 0, 1).await
}
pub async fn import_with_option<Data>(
session: &Session,
file: FileData,
sheet_indexes: Option<Vec<usize>>,
header_row_index: usize,
data_start_row_index: usize,
) -> AppResult<Vec<Data>>
where
Data: IExcelSheet + IExcelRow + DeserializeOwned + Default + Send + Sync + 'static,
{
let data = Data::default();
let sheet_indexes = sheet_indexes.unwrap_or_default();
let field_column_mapping = data.get_field_column_mapping(session).await?;
let column_field_mapping = field_column_mapping.into_iter().map(|(k, v)| (v, k)).fold(IndexMap::new(), |mut m, (k, v)| {
m.insert(k, v);
m
});
let enum_map = data
.get_field_enum_mapping(session)
.await?
.into_iter()
.map(|(k, v)| {
(
k,
v.into_iter().map(|(k1, v1)| (v1, k1)).fold(IndexMap::new(), |mut m, (k2, v2)| {
m.insert(k2, v2);
m
}),
)
})
.collect::<IndexMap<String, IndexMap<String, String>>>();
let field_dict = data.get_field_dict(session).await?;
let dict_service = session.get_application().get_dict_service()?;
let dict_map = match field_dict.is_empty() {
true => IndexMap::default(),
false => {
let dict_types = field_dict.iter().map(|v| v.1.as_str()).collect::<Vec<&str>>();
dict_service
.get_dict_data_mapping_by_types(session, dict_types.as_slice())
.await?
.into_iter()
.map(|(k, v)| {
(
k,
v.into_iter().map(|(k1, v1)| (v1, k1)).fold(IndexMap::new(), |mut m, (k2, v2)| {
m.insert(k2, v2);
m
}),
)
})
.fold(IndexMap::new(), |mut m, (k, v)| {
for (fk, fv) in field_dict.iter() {
if k.as_str() == fv.as_str() {
m.insert(fk.to_string(), v.clone());
}
}
m
})
}
};
let mut sheets = read_excel(file)?;
let mut datas = Vec::new();
let mut idx: isize = -1;
for (_, data) in sheets.worksheets().into_iter() {
idx += 1;
if (!sheet_indexes.is_empty()) && (!sheet_indexes.contains(&(idx as usize))) {
continue;
}
let mut col_field_map = IndexMap::new();
let mut col_field_enum_map = IndexMap::new();
let mut col_field_dict_map = IndexMap::new();
let mut row_datas = IndexMap::<usize, StringJoiner>::new();
for (row_no, col_no, cell) in data.used_cells() {
if row_no == header_row_index {
let cell_value = get_string_value(cell)?;
if let Some(field) = column_field_mapping.get(&cell_value) {
col_field_map.insert(col_no, field);
if let Some(map) = enum_map.get(field) {
col_field_enum_map.insert(col_no, map);
}
if let Some(map) = dict_map.get(field) {
col_field_dict_map.insert(col_no, map);
}
}
} else if row_no >= data_start_row_index {
let mut cell_value = get_string_value(cell)?;
let row_data = match row_datas.get_mut(&row_no) {
None => {
row_datas.insert(row_no, StringJoiner::new(None, None, Some("&")));
row_datas.get_mut(&row_no).expect("get row data failed")
}
Some(v) => v,
};
if let Some(field_name) = col_field_map.get(&col_no) {
let mut replaced = false;
if let Some(enum_map) = col_field_enum_map.get(&col_no) {
match enum_map.get(&cell_value) {
None => {
return Err(app_system_error_with_msg!(i18n_string!(
SystemMessage::ERROR_UNSUPPORTED_EXCEL_ENUM_VALUE,
("row", format!("{}", row_no + 1).as_str()),
("col", format!("{}", col_no + 1).as_str()),
("value", cell_value.as_str()),
("available", JsonUtil::to_json_string(enum_map).as_str())
)));
}
Some(enum_value) => {
cell_value = enum_value.to_string();
replaced = true;
}
}
}
if !replaced {
if let Some(dict_map) = col_field_dict_map.get(&col_no) {
match dict_map.get(&cell_value) {
None => {
return Err(app_system_error_with_msg!(i18n_string!(
SystemMessage::ERROR_UNSUPPORTED_EXCEL_DICT_VALUE,
("row", format!("{}", row_no + 1).as_str()),
("col", format!("{}", col_no + 1).as_str()),
("value", cell_value.as_str()),
("available", JsonUtil::to_json_string(dict_map).as_str())
)));
}
Some(enum_value) => {
cell_value = enum_value.to_string();
}
}
}
}
row_data.push_string(format!("{}={}", field_name, encode_value(cell_value.as_str())));
}
}
}
for (_, data) in row_datas.into_iter() {
let data_string = data.into_string();
if data_string.not_empty() {
let row_data: Data = serde_qs::from_str(data_string.as_str())
.map_err(|v| app_system_error!("解析excel数据失败: data = {}, reason: {:?}", data_string, v))?;
datas.push(row_data);
}
}
}
Ok(datas)
}
}
fn get_string_value(cell: &DataType) -> AppResult<String> {
match cell {
DataType::Int(v) => Ok(v.to_string()),
DataType::Float(v) => Ok(v.to_string()),
DataType::String(v) => Ok(v.to_string()),
DataType::Bool(v) => Ok(v.to_string()),
DataType::DateTime(v) => Ok(v.to_string()),
DataType::Error(v) => Ok(v.to_string()),
DataType::Empty => Ok("".to_string()),
}
}
fn encode_value(v: &str) -> String {
percent_encoding::utf8_percent_encode(v, percent_encoding::NON_ALPHANUMERIC).to_string()
}
fn read_excel(file: FileData) -> AppResult<FileDataSheets> {
let file_name = file.get_original_filename().as_str().to_string();
if let Ok(v) = Xlsx::new(BufReader::new(file.clone())) {
return Ok(FileDataSheets::Xlsx(Box::new(v)));
}
if let Ok(v) = Xls::new(BufReader::new(file)) {
return Ok(FileDataSheets::Xls(Box::new(v)));
}
Err(app_system_error!("非法Excel格式: {}", file_name))
}
enum FileDataSheets {
Xls(Box<Xls<BufReader<FileData>>>),
Xlsx(Box<Xlsx<BufReader<FileData>>>),
}
impl FileDataSheets {
fn worksheets(&mut self) -> Vec<(String, Range<DataType>)> {
match self {
FileDataSheets::Xls(v) => v.worksheets(),
FileDataSheets::Xlsx(v) => v.worksheets(),
}
}
}