How to Automatically Change the Image Insertion in Excel Cells with Cell Size？

[songlinshu]

Question

How to Automatically Change the Image Insertion in Excel Cells with Cell Size？

[jmcnamara]

How to Automatically Change the Image Insertion in Excel Cells with Cell Size？

That isn't part of the file format so it isn't directly possible. I don't think it is even possible in Excel without dragging the image to the size of the cell.

However, it is possible to do it with the current API based on the image size and dpi and the cell size. I'll post an example later.

P.S., I had also planned to add an API like image.scale_to_size(height, width, lock_aspect_ratio) in a future release.

[jmcnamara]

Here is an example:

use rust_xlsxwriter::{Image, Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();
    let worksheet = workbook.add_worksheet();

    // Resize some cells.
    worksheet.set_column_width_pixels(0, 200)?;
    worksheet.set_row_height_pixels(0, 120)?;
    worksheet.set_row_height_pixels(1, 120)?;
    worksheet.set_row_height_pixels(2, 120)?;

    let mut image = Image::new("rust_logo.png")?;

    // Insert the image - unscaled.
    worksheet.insert_image(0, 0, &image)?;

    // Insert the image - scaled to cell.
    scale_image_to_size(&mut image, 200.0, 120.0, false);
    worksheet.insert_image(1, 0, &image)?;

    // Insert the image - scaled to cell with fixed aspect ratio.
    scale_image_to_size(&mut image, 200.0, 120.0, true);
    worksheet.insert_image(2, 0, &image)?;

    // Save the file to disk.
    workbook.save("image.xlsx")?;

    Ok(())
}

fn scale_image_to_size(image: &mut Image, width: f64, height: f64, keep_aspect_ratio: bool) {
    if width == 0.0 || height == 0.0 {
        return;
    }

    let mut scale_width = (width / image.width()) * (image.width_dpi() / 96.0);
    let mut scale_height = (height / image.height()) * (image.height_dpi() / 96.0);

    if keep_aspect_ratio {
        if scale_width < scale_height {
            scale_height = scale_width;
        } else {
            scale_width = scale_height;
        }
    }

    image.set_scale_width(scale_width);
    image.set_scale_height(scale_height);
}

Output:

Would something like that work for you?

If so I'll add a similar method to the rust_xlsxwriter::Image struct.

[songlinshu]

Yes ,Thank you !

[jmcnamara]

I've added 2 new features to help with this. The first is image.set_scale_to_size() which is similar to the function above but as a method of the Image struct.

The second is a worksheet.insert_image_fit_to_cell() method that inserts an image and scales it to fit the cell. Here is an example of that:

use rust_xlsxwriter::{Format, FormatAlign, Image, Workbook, XlsxError};

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    let center = Format::new().set_align(FormatAlign::VerticalCenter);

    // Add a worksheet to the workbook.
    let worksheet = workbook.add_worksheet();

    // Widen the first column to make the text clearer.
    worksheet.set_column_width(0, 30)?;

    // Set larger cells to accommodate the images.
    worksheet.set_column_width_pixels(1, 200)?;
    worksheet.set_row_height_pixels(0, 140)?;
    worksheet.set_row_height_pixels(2, 140)?;
    worksheet.set_row_height_pixels(4, 140)?;

    // Create a new image object.
    let image = Image::new("examples/rust_logo.png")?;

    // Insert the image as standard, without scaling.
    worksheet.write_with_format(0, 0, "Unscaled image inserted into cell:", &center)?;
    worksheet.insert_image(0, 1, &image)?;

    // Insert the image and scale it to fit the entire cell.
    worksheet.write_with_format(2, 0, "Image scaled to fit cell:", &center)?;
    worksheet.insert_image_fit_to_cell(2, 1, &image, false)?;

    // Insert the image and scale it to the cell while maintaining the aspect ratio.
    // In this case it is scaled to the smaller of the width or height scales.
    worksheet.write_with_format(4, 0, "Image scaled with a fixed aspect ratio:", &center)?;
    worksheet.insert_image_fit_to_cell(4, 1, &image, true)?;

    // Save the file to disk.
    workbook.save("images_fit_to_cell.xlsx")?;

    Ok(())
}

Output:

This is available on main and you can try it out. However, I need to add docs and some more tests.