rust-导出导入Excel优化更改

导出导入Excel部分接口之前我们使用的是calamine和umya-spreadsheet结合的方式，为了减少我们的依赖，无奈我们只能去掉calamine，将对应的接口更改为umya-spreadsheet的方式

calamine = "0.21" # 解析Excel（.xlsx/.xls/.ods）
umya-spreadsheet = "1.1"

利用umya-spreadsheet的方式重写导入接口

查看以后我们可以发现，之前我们的导入用户数据采取的方式是采取calamine的方式进行实现的，接下来我们把数据实现方式换成umya-spreadsheet的方式

旧的写法

// 流式导入用户数据
use actix_web::{post};
use actix_multipart::Multipart;
use csv;
use csv::ReaderBuilder;
use uuid::Uuid;
use std::fs::File;
use std::fs;
use std::io::Write;
use calamine::{open_workbook_auto, Reader, RangeDeserializerBuilder};
use serde::{Serialize, Deserialize};
#[derive(Serialize)]
pub struct BasicImportResponse {
    pub code: i32,
    pub msg: String,
}


impl BasicImportResponse {
    pub fn new(code: i32, msg: impl Into<String>) -> Self {
        Self {
            code,
            msg: msg.into(),
        }
    }
}


#[derive(Debug, Deserialize)]
struct UserRow {
    #[serde(rename = "用户名")]
    username: String,
    #[serde(rename = "姓名")]
    name: String,
    #[serde(rename = "年龄")]
    age: i32,
    #[serde(rename = "电话")]
    phone: String,
}


pub async fn import_users_data(
    mut payload: Multipart,
    db_pool: web::Data<Pool<MySql>>,
) -> impl Responder {
    fs::create_dir_all("uploads/tmp").unwrap();
    println!("收到上传请求");


    while let Some(item) = payload.next().await {
        let mut field = match item {
            Ok(f) => f,
            Err(_) => {
                return web::Json(BasicImportResponse::new(400, "文件读取失败"));
            }
        };


        let file_id = Uuid::new_v4().to_string();
        let file_path = format!("uploads/tmp/{}.xlsx", file_id);
        let mut f = File::create(&file_path).unwrap();


        while let Some(chunk) = field.next().await {
            let data = match chunk {
                Ok(d) => d,
                Err(_) => {
                    return web::Json(BasicImportResponse::new(400, "文件写入失败"));
                }
            };
            f.write_all(&data).unwrap();
        }


        match open_workbook_auto(&file_path) {
            Ok(mut workbook) => {
                if let Some(Ok(range)) = workbook.worksheet_range_at(0) {
                    let mut iter = RangeDeserializerBuilder::new()
                        .has_headers(true)
                        .from_range::<_, UserRow>(&range)
                        .unwrap();


                    let mut count = 0;
                    let mut duplicated = 0;


                    while let Some(Ok(row)) = iter.next() {
                        let hashed_pwd = match hash("123456", DEFAULT_COST) {
                            Ok(p) => p,
                            Err(_) => continue,
                        };


                        match sqlx::query!(
                            r#"
                            INSERT INTO sys_user (username, name, age, phone, password, del_flag)
                            VALUES (?, ?, ?, ?, ?, '0')
                            "#,
                            row.username,
                            row.name,
                            row.age,
                            row.phone,
                            hashed_pwd
                        )
                        .execute(db_pool.get_ref())
                        .await
                        {
                            Ok(result) => {
                                if result.rows_affected() > 0 {
                                    count += 1;
                                }
                            }
                            Err(_) => {
                                duplicated += 1;
                            }
                        }
                    }


                    let msg = if duplicated > 0 {
                        format!("成功导入 {} 条用户数据，{} 条已存在", count, duplicated)
                    } else {
                        format!("成功导入 {} 条用户数据", count)
                    };


                    return web::Json(BasicImportResponse::new(200, msg));
                } else {
                    return web::Json(BasicImportResponse::new(400, "未找到工作表或解析失败"));
                }
            }
            Err(e) => {
                return web::Json(BasicImportResponse::new(400, format!("Excel 解析失败: {}", e)));
            }
        }
    }


    web::Json(BasicImportResponse::new(400, "未收到文件"))
}

新的写法

use actix_multipart::Multipart;
use actix_web::post;
use serde::{Deserialize, Serialize};
use std::fs;
use std::fs::File;
use std::io::Write;
use umya_spreadsheet::*;
use uuid::Uuid;


#[derive(Serialize)]
pub struct BasicImportResponse {
    pub code: i32,
    pub msg: String,
}


impl BasicImportResponse {
    pub fn new(code: i32, msg: impl Into<String>) -> Self {
        Self {
            code,
            msg: msg.into(),
        }
    }
}


#[derive(Debug, Deserialize)]
struct UserRow {
    #[serde(rename = "用户名")]
    username: String,
    #[serde(rename = "姓名")]
    name: String,
    #[serde(rename = "年龄")]
    age: i32,
    #[serde(rename = "电话")]
    phone: String,
}


pub async fn import_users_data(
    mut payload: Multipart,
    db_pool: web::Data<MySqlPool>,
) -> impl Responder {
    // 创建临时目录
    if let Err(e) = fs::create_dir_all("uploads/tmp") {
        return web::Json(BasicImportResponse::new(500, format!("创建临时目录失败: {}", e)));
    }


    while let Some(item) = payload.next().await {
        let mut field = match item {
            Ok(f) => f,
            Err(e) => {
                return web::Json(BasicImportResponse::new(400, format!("文件读取失败: {}", e)));
            }
        };


        let file_id = Uuid::new_v4().to_string();
        let file_path = format!("uploads/tmp/{}.xlsx", file_id);
       
        // 创建临时文件
        let mut f = match File::create(&file_path) {
            Ok(file) => file,
            Err(e) => {
                return web::Json(BasicImportResponse::new(500, format!("创建临时文件失败: {}", e)));
            }
        };


        // 写入文件内容
        while let Some(chunk) = field.next().await {
            let data = match chunk {
                Ok(d) => d,
                Err(e) => {
                    let _ = fs::remove_file(&file_path); // 清理临时文件
                    return web::Json(BasicImportResponse::new(400, format!("文件写入失败: {}", e)));
                }
            };
            if let Err(e) = f.write_all(&data) {
                let _ = fs::remove_file(&file_path); // 清理临时文件
                return web::Json(BasicImportResponse::new(500, format!("文件写入失败: {}", e)));
            }
        }


        // 处理 Excel 文件
        let result = match reader::xlsx::read(&file_path) {
            Ok(book) => {
                let worksheet = match book.get_sheet(&0) {
                    Some(sheet) => sheet,
                    None => {
                        let _ = fs::remove_file(&file_path);
                        return web::Json(BasicImportResponse::new(400, "未找到工作表"));
                    }
                };



                let mut count = 0;
                let mut duplicated = 0;


               
                // 验证表头（从第1行第1列开始）
                let expected_headers = ["用户名", "姓名", "年龄", "电话"];
                for (i, expected) in expected_headers.iter().enumerate() {
                    let col = (i + 1) as u32;
                    match worksheet.get_cell((col, 1)) {
                        Some(cell) => {
                            let value = cell.get_value().to_string();
                            if value != *expected {
                                let _ = fs::remove_file(&file_path);
                                return web::Json(BasicImportResponse::new(
                                    400,
                                    format!("表头格式不正确，第{}列应为'{}'，实际为'{}'", col, expected, value)
                                ));
                            }
                        }
                        None => {
                            let _ = fs::remove_file(&file_path);
                            return web::Json(BasicImportResponse::new(
                                400,
                                format!("表头第{}列缺失", col)
                            ));
                        }
                    }
                }


                 // 从第2行开始读取数据
                for row_index in 2.. {
                    let mut has_data = false;
                    let mut user_row = UserRow {
                        username: String::new(),
                        name: String::new(),
                        age: 0,
                        phone: String::new(),
                    };


                    // 读取4列数据
                    for col in 1..=4 {
                        if let Some(cell) = worksheet.get_cell((col, row_index)) {
                            let value = cell.get_value().to_string();
                            if !value.is_empty() {
                                has_data = true;
                            }


                            match col {
                                1 => user_row.username = value,
                                2 => user_row.name = value,
                                3 => {
                                    user_row.age = value.parse::<i32>()
                                        .unwrap_or_else(|_| {
                                            eprintln!("警告: 第{}行年龄值'{}'解析失败，使用默认值0", row_index, value);
                                            0
                                        });
                                }
                                4 => user_row.phone = value,
                                _ => {}
                            }
                        }
                    }


                    // 如果这一行没有数据，结束处理
                    if !has_data {
                        break;
                    }


                    // 验证必填字段
                    if user_row.username.is_empty() {
                        eprintln!("警告: 第{}行用户名为空，跳过此行", row_index);
                        continue;
                    }


                    // 插入数据库
                    let hashed_pwd = match hash("123456", DEFAULT_COST) {
                        Ok(p) => p,
                        Err(e) => {
                            eprintln!("密码加密失败: {}", e);
                            continue;
                        }
                    };


                    match sqlx::query!(
                        r#"
                        INSERT INTO sys_user (username, name, age, phone, password, del_flag)
                        VALUES (?, ?, ?, ?, ?, '0')
                        "#,
                        user_row.username,
                        user_row.name,
                        user_row.age,
                        user_row.phone,
                        hashed_pwd
                    )
                    .execute(db_pool.get_ref())
                    .await
                    {
                        Ok(result) => {
                            if result.rows_affected() > 0 {
                                count += 1;
                            }
                        }
                        Err(e) => {
                            eprintln!("数据库插入失败: {}", e);
                            duplicated += 1;
                        }
                    }
                }


                let msg = if duplicated > 0 {
                    format!("成功导入 {} 条用户数据，{} 条已存在", count, duplicated)
                } else {
                    format!("成功导入 {} 条用户数据", count)
                };


                // 清理临时文件
                let _ = fs::remove_file(&file_path);
                web::Json(BasicImportResponse::new(200, msg))
            }
            Err(e) => {
                let _ = fs::remove_file(&file_path);
                web::Json(BasicImportResponse::new(400, format!("Excel 解析失败: {}", e)))
            }
        };


        return result;
    }


    web::Json(BasicImportResponse::new(400, "未收到文件"))
}

重写以后，这个时候我们就可以在项目之中去掉我们的依赖了，也就是我们这部分数据

calamine = "0.21" # 解析Excel（.xlsx/.xls/.ods）

测试我们的接口。数据ok

导出模板抽离优化

之前我们的导出部分模板如下,接下来我们将它抽空出来，写成公共的模板方法，抽离公共模块

// 导出用户模板
use std::io::Cursor;
use umya_spreadsheet::*;
pub async fn export_template() -> HttpResponse {
    // 创建一个带默认 Sheet1 的工作簿
    let mut book = new_file();
    let sheet = book.get_sheet_by_name_mut("Sheet1").unwrap();


    // 表头
    sheet.get_cell_mut((1, 1)).set_value("用户名");
    sheet.get_cell_mut((2, 1)).set_value("姓名");
    sheet.get_cell_mut((3, 1)).set_value("年龄");
    sheet.get_cell_mut((4, 1)).set_value("电话");


    // 写到内存
    let mut buffer = Cursor::new(Vec::new());
    writer::xlsx::write_writer(&book, &mut buffer).unwrap();
    let bytes = buffer.into_inner();


    HttpResponse::Ok()
        .append_header((
            "Content-Type",
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        ))
        .append_header((
            "Content-Disposition",
            "attachment; filename=\"users_template.xlsx\"",
        ))
        .append_header((
            "Access-Control-Expose-Headers",
            "Content-Disposition, Content-Type",
        ))
        .body(bytes)
}

抽离逻辑

use actix_web::{HttpResponse, http::header::ContentDisposition};
use std::io::Cursor;
use umya_spreadsheet::{new_file, writer};


// 通用模板导出函数
pub async fn export_excel_template(headers: Vec<&str>, filename: &str) -> HttpResponse {
    // 创建一个带默认 Sheet1 的工作簿
    let mut book = new_file();
    let sheet = book.get_sheet_by_name_mut("Sheet1").unwrap();


    // 设置表头
    for (i, header) in headers.iter().enumerate() {
        sheet.get_cell_mut(((i + 1) as u32, 1)).set_value(*header);
    }


    // 写到内存
    let mut buffer = Cursor::new(Vec::new());
    unsafe {
    writer::xlsx::write_writer(&book, &mut buffer).unwrap();
    let bytes = buffer.into_inner();
    }
    HttpResponse::Ok()
        .append_header((
            "Content-Type",
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        ))
        .append_header(ContentDisposition {
            disposition: actix_web::http::header::DispositionType::Attachment,
            parameters: vec![actix_web::http::header::DispositionParam::Filename(
                filename.to_string(),
            )],
        })
        .append_header((
            "Access-Control-Expose-Headers",
            "Content-Disposition, Content-Type",
        ))
        .body(bytes)
}

优化安全区

因为 umya_spreadsheet 库的 write_writer 函数实际上已经被安全地封装了，我们不需要显式使用 unsafe 块

unsafe {
    writer::xlsx::write_writer(&book, &mut buffer).unwrap();
    let bytes = buffer.into_inner();
}


=>
writer::xlsx::write_writer(&book, &mut buffer).unwrap();
    let bytes = buffer.into_inner();

使用

我们导出用户的模板使用就可以更改为

// 导出用户模板-通用方法
pub async fn export_template() -> HttpResponse {
    let headers = vec!["用户名", "姓名", "年龄", "电话"];
    crate::common::excelmethods::export_excel_template(headers, "users_template.xlsx").await
}

其他部分使用测试

在通告部分我们进行使用并且测试，这里我们就以通告模板部分为例

  // 导出模板
  cfg.route("/system/notices/exporttemplate",
            web::post().to(crate::modules::notice::handlers::export_template));

导出通告模板

// 导出通告模板-通用导出
pub async fn export_template() -> HttpResponse {
    let headers = vec!["标题", "类型", "内容"];
    crate::common::excelmethods::export_excel_template(headers, "notice_template.xlsx").await
}

测试接口，ok，这里我们导出模板已经抽离好了