xls_rs/excel/
writer.rs

1use anyhow::{Context, Result};
2use calamine::{open_workbook, Reader, Xlsx};
3use std::fs::File;
4use std::io::BufWriter;
5
6use super::reader::ExcelHandler;
7use super::types::WriteOptions;
8use super::xlsx_writer::{CellData, RowData, XlsxWriter};
9use crate::traits::{DataWriteOptions, DataWriter};
10
11/// Write mode for range operations
12#[derive(Debug, Clone, Copy, PartialEq, Eq)]
13pub enum WriteMode {
14    /// Expand sheet bounds as needed
15    Expand,
16    /// Preserve existing cells outside the range
17    Preserve,
18    /// Overwrite all cells with new data
19    Overwrite,
20}
21
22impl Default for WriteMode {
23    fn default() -> Self {
24        Self::Expand
25    }
26}
27
28impl ExcelHandler {
29    pub fn write_from_csv(
30        &self,
31        csv_path: &str,
32        excel_path: &str,
33        sheet_name: Option<&str>,
34    ) -> Result<()> {
35        let mut reader = csv::ReaderBuilder::new()
36            .has_headers(false)
37            .from_path(csv_path)
38            .with_context(|| format!("Failed to open CSV file: {csv_path}"))?;
39
40        let mut writer = XlsxWriter::new();
41        let name = sheet_name.unwrap_or("Sheet1");
42        writer.add_sheet(name)?;
43
44        for result in reader.records() {
45            let record = result?;
46            let mut row = RowData::new();
47            for field in record.iter() {
48                if let Ok(num) = field.parse::<f64>() {
49                    row.add_number(num);
50                } else if !field.is_empty() {
51                    row.add_string(field);
52                } else {
53                    row.add_empty();
54                }
55            }
56            writer.add_row(row);
57        }
58
59        // Auto-fit columns
60        let column_widths: Vec<f64> = if let Some(sheet) = writer.sheets.last() {
61            (0..sheet.column_widths.len())
62                .map(|col_idx| {
63                    let max_width = sheet
64                        .rows
65                        .iter()
66                        .map(|row| {
67                            row.cells
68                                .get(col_idx)
69                                .map(|c| match c {
70                                    CellData::String(s) => s.len(),
71                                    CellData::Number(_) => 10,
72                                    _ => 0,
73                                })
74                                .unwrap_or(0)
75                        })
76                        .max()
77                        .unwrap_or(10);
78                    (max_width + 2) as f64
79                })
80                .collect()
81        } else {
82            Vec::new()
83        };
84
85        for (col_idx, width) in column_widths.iter().enumerate() {
86            writer.set_column_width(col_idx, *width);
87        }
88
89        let file = File::create(excel_path)?;
90        let mut buf_writer = BufWriter::new(file);
91        writer.save(&mut buf_writer)?;
92
93        Ok(())
94    }
95
96    /// Write data to Excel with styling options
97    pub fn write_styled(
98        &self,
99        path: &str,
100        data: &[Vec<String>],
101        options: &WriteOptions,
102    ) -> Result<()> {
103        let mut writer = XlsxWriter::with_options(options.clone());
104        let sheet_name = options.sheet_name.as_deref().unwrap_or("Sheet1");
105        writer.add_sheet(sheet_name)?;
106
107        for (row_idx, row) in data.iter().enumerate() {
108            let is_header = row_idx == 0 && options.style_header;
109
110            let mut row_data = RowData::new();
111            for cell in row {
112                // Determine cell format
113                let _use_header_style = is_header;
114
115                if let Ok(num) = cell.parse::<f64>() {
116                    row_data.add_number(num);
117                } else if !cell.is_empty() {
118                    row_data.add_string(cell);
119                } else {
120                    row_data.add_empty();
121                }
122            }
123            writer.add_row(row_data);
124        }
125
126        let file = File::create(path)?;
127        let mut buf_writer = BufWriter::new(file);
128        writer.save(&mut buf_writer)?;
129
130        Ok(())
131    }
132
133    /// Add a sparkline to an Excel file
134    pub fn add_sparkline_formula(
135        &self,
136        excel_path: &str,
137        data_range: &str,
138        sparkline_cell: &str,
139        sheet_name: Option<&str>,
140    ) -> Result<()> {
141        use super::xlsx_writer::{Sparkline, SparklineGroup, SparklineType};
142
143        let mut writer = XlsxWriter::new();
144        let name = sheet_name.unwrap_or("Sheet1");
145        writer.add_sheet(name)?;
146        writer.add_sparkline_group(SparklineGroup {
147            sparkline_type: SparklineType::Line,
148            sparklines: vec![Sparkline {
149                location: sparkline_cell.to_string(),
150                data_range: data_range.to_string(),
151            }],
152            color: "4472C4".to_string(),
153            show_markers: false,
154        });
155
156        let file = File::create(excel_path)?;
157        writer.save(BufWriter::new(file))?;
158        Ok(())
159    }
160
161    /// Apply conditional formatting to an Excel file
162    pub fn apply_conditional_format_formula(
163        &self,
164        excel_path: &str,
165        range: &str,
166        condition: &str,
167        true_format: &super::types::CellStyle,
168        _false_format: Option<&super::types::CellStyle>,
169        sheet_name: Option<&str>,
170    ) -> Result<()> {
171        use super::xlsx_writer::{ConditionalFormat, ConditionalRule};
172
173        let mut writer = XlsxWriter::new();
174        let name = sheet_name.unwrap_or("Sheet1");
175        writer.add_sheet(name)?;
176        writer.add_conditional_format(ConditionalFormat {
177            range: range.to_string(),
178            rules: vec![ConditionalRule::Formula {
179                formula: condition.to_string(),
180                bg_color: true_format.bg_color.clone(),
181                font_color: true_format.font_color.clone(),
182                bold: true_format.bold,
183            }],
184        });
185
186        let file = File::create(excel_path)?;
187        writer.save(BufWriter::new(file))?;
188        Ok(())
189    }
190
191    /// Write data to a specific range in Excel starting at the given row and column
192    pub fn write_range(
193        &self,
194        path: &str,
195        data: &[Vec<String>],
196        start_row: u32,
197        start_col: u16,
198        sheet_name: Option<&str>,
199    ) -> Result<()> {
200        self.write_range_with_mode(path, data, start_row, start_col, sheet_name, WriteMode::Expand)
201    }
202
203    /// Write data to a specific range with specified write mode
204    pub fn write_range_with_mode(
205        &self,
206        path: &str,
207        data: &[Vec<String>],
208        start_row: u32,
209        start_col: u16,
210        sheet_name: Option<&str>,
211        mode: WriteMode,
212    ) -> Result<()> {
213        match mode {
214            WriteMode::Expand => self.write_range_expand(path, data, start_row, start_col, sheet_name),
215            WriteMode::Preserve => self.write_range_preserve(path, data, start_row, start_col, sheet_name),
216            WriteMode::Overwrite => self.write_range_overwrite(path, data, start_row, start_col, sheet_name),
217        }
218    }
219
220    fn write_range_expand(
221        &self,
222        path: &str,
223        data: &[Vec<String>],
224        start_row: u32,
225        start_col: u16,
226        sheet_name: Option<&str>,
227    ) -> Result<()> {
228        let mut writer = XlsxWriter::new();
229        let name = sheet_name.unwrap_or("Sheet1");
230        writer.add_sheet(name)?;
231
232        // Add empty rows for offset
233        for _ in 0..start_row {
234            writer.add_row(RowData::new());
235        }
236
237        // Add empty cells for column offset
238        for row in data {
239            let mut row_data = RowData::new();
240
241            // Add empty cells for column offset
242            for _ in 0..start_col {
243                row_data.add_empty();
244            }
245
246            for cell in row {
247                if let Ok(num) = cell.parse::<f64>() {
248                    row_data.add_number(num);
249                } else if !cell.is_empty() {
250                    row_data.add_string(cell);
251                } else {
252                    row_data.add_empty();
253                }
254            }
255            writer.add_row(row_data);
256        }
257
258        let file = File::create(path)?;
259        let mut buf_writer = BufWriter::new(file);
260        writer.save(&mut buf_writer)?;
261
262        Ok(())
263    }
264
265    fn write_range_preserve(
266        &self,
267        path: &str,
268        data: &[Vec<String>],
269        start_row: u32,
270        start_col: u16,
271        sheet_name: Option<&str>,
272    ) -> Result<()> {
273        let name = sheet_name.unwrap_or("Sheet1");
274
275        // Read existing data if file exists
276        let mut existing_data: Vec<Vec<String>> = Vec::new();
277
278        if std::path::Path::new(path).exists() {
279            let mut workbook: Xlsx<_> = open_workbook(path)
280                .with_context(|| format!("Failed to open Excel file: {path}"))?;
281
282            let sheet_names = workbook.sheet_names();
283            let sheet_name = sheet_names
284                .first()
285                .map(|s| s.as_str())
286                .unwrap_or("Sheet1");
287
288            if let Ok(range) = workbook.worksheet_range(sheet_name) {
289                for row in range.rows() {
290                    existing_data.push(row.iter().map(|c| c.to_string()).collect());
291                }
292            }
293        }
294
295        // Ensure existing data is large enough
296        let required_rows = (start_row as usize + data.len()).max(existing_data.len());
297        let max_cols = existing_data
298            .iter()
299            .map(|r| r.len())
300            .max()
301            .unwrap_or(0)
302            .max((start_col as usize) + data.iter().map(|r| r.len()).max().unwrap_or(0));
303
304        // Expand existing data if needed
305        while existing_data.len() < required_rows {
306            existing_data.push(vec![String::new(); max_cols]);
307        }
308        for row in &mut existing_data {
309            while row.len() < max_cols {
310                row.push(String::new());
311            }
312        }
313
314        // Write new data, preserving existing cells outside the range
315        for (data_row_idx, data_row) in data.iter().enumerate() {
316            let target_row_idx = start_row as usize + data_row_idx;
317            for (data_col_idx, cell) in data_row.iter().enumerate() {
318                let target_col_idx = start_col as usize + data_col_idx;
319                if target_row_idx < existing_data.len()
320                    && target_col_idx < existing_data[target_row_idx].len()
321                {
322                    existing_data[target_row_idx][target_col_idx] = cell.clone();
323                }
324            }
325        }
326
327        // Write everything back
328        let mut writer = XlsxWriter::new();
329        writer.add_sheet(name)?;
330        writer.add_data(&existing_data);
331
332        let file = File::create(path)?;
333        let mut buf_writer = BufWriter::new(file);
334        writer.save(&mut buf_writer)?;
335
336        Ok(())
337    }
338
339    fn write_range_overwrite(
340        &self,
341        path: &str,
342        data: &[Vec<String>],
343        start_row: u32,
344        start_col: u16,
345        sheet_name: Option<&str>,
346    ) -> Result<()> {
347        let mut writer = XlsxWriter::new();
348        let name = sheet_name.unwrap_or("Sheet1");
349        writer.add_sheet(name)?;
350
351        // Add empty rows for offset
352        for _ in 0..start_row {
353            writer.add_row(RowData::new());
354        }
355
356        for row in data {
357            let mut row_data = RowData::new();
358
359            // Add empty cells for column offset
360            for _ in 0..start_col {
361                row_data.add_empty();
362            }
363
364            for cell in row {
365                if let Ok(num) = cell.parse::<f64>() {
366                    row_data.add_number(num);
367                } else if !cell.is_empty() {
368                    row_data.add_string(cell);
369                } else {
370                    row_data.add_empty();
371                }
372            }
373            writer.add_row(row_data);
374        }
375
376        let file = File::create(path)?;
377        let mut buf_writer = BufWriter::new(file);
378        writer.save(&mut buf_writer)?;
379
380        Ok(())
381    }
382}
383
384impl DataWriter for ExcelHandler {
385    fn write(&self, path: &str, data: &[Vec<String>], options: DataWriteOptions) -> Result<()> {
386        let mut writer = XlsxWriter::new();
387        let sheet_name = options.sheet_name.as_deref().unwrap_or("Sheet1");
388        writer.add_sheet(sheet_name)?;
389
390        writer.add_data(data);
391
392        // Auto-fit columns
393        for col_idx in 0..data.get(0).map(|r| r.len()).unwrap_or(0) {
394            let max_width = data
395                .iter()
396                .map(|row| {
397                    row.get(col_idx)
398                        .map(|s| s.len())
399                        .unwrap_or(0)
400                })
401                .max()
402                .unwrap_or(10);
403            writer.set_column_width(col_idx, (max_width + 2) as f64);
404        }
405
406        let file = File::create(path)?;
407        let mut buf_writer = BufWriter::new(file);
408        writer.save(&mut buf_writer)?;
409
410        Ok(())
411    }
412
413    fn write_range(
414        &self,
415        path: &str,
416        data: &[Vec<String>],
417        start_row: usize,
418        start_col: usize,
419    ) -> Result<()> {
420        self.write_range(path, data, start_row as u32, start_col as u16, None)
421    }
422
423    fn append(&self, path: &str, data: &[Vec<String>]) -> Result<()> {
424        use calamine::{open_workbook, Reader, Xlsx};
425
426        // Check if file exists
427        if !std::path::Path::new(path).exists() {
428            // File doesn't exist, just write the data
429            return self.write(path, data, DataWriteOptions::default());
430        }
431
432        // Read existing data from the file
433        let mut existing_data: Vec<Vec<String>> = Vec::new();
434
435        // Try to open as Excel file first
436        if let Ok(mut workbook) = open_workbook::<Xlsx<_>, _>(path) {
437            let sheet_names = workbook.sheet_names();
438            let sheet_name = sheet_names
439                .first()
440                .map(|s| s.as_str())
441                .unwrap_or("Sheet1");
442
443            if let Ok(range) = workbook.worksheet_range(sheet_name) {
444                for row in range.rows() {
445                    let row_data: Vec<String> = row.iter().map(|cell| cell.to_string()).collect();
446                    existing_data.push(row_data);
447                }
448            }
449        }
450
451        // Append new data
452        existing_data.extend(data.iter().cloned());
453
454        // Write everything back
455        let mut writer = XlsxWriter::new();
456        writer.add_sheet("Sheet1")?;
457        writer.add_data(&existing_data);
458
459        let file = File::create(path)?;
460        let mut buf_writer = BufWriter::new(file);
461        writer.save(&mut buf_writer)?;
462
463        Ok(())
464    }
465
466    fn supports_format(&self, path: &str) -> bool {
467        let path_lower = path.to_lowercase();
468        path_lower.ends_with(".xlsx")
469    }
470}
xls_rs/excel/writer.rs

xls_rs/excel/
writer.rs
