Replace text with Image and Fit an image in a cell

[vernon1111]

Hi,

I am currently trying to replace some keyword text with an image and fit an image and centralized the image within the cells

1. Replace the keyword [@image] to an image file (sign.png)
2. Fit the image in the cells without resizing the cells
3. Adjust the image to the middle and center of the cells

May i know how I can do this?

source.PNG (5.01 KiB) Viewed 843 times

output.PNG (5.77 KiB) Viewed 843 times

sample excel file , sign.png is in the zip file attached

sample.zip

(20.48 KiB) Downloaded 437 times

[Annika.Zhou]

Hello,

Thanks for your inquiry.
Please use the following modified code to achieve your needs. If there is any question, just feel free to contact us.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");

Worksheet worksheet = workbook.Worksheets[0];
// Get the default width of the cell
int width = worksheet.GetColumnWidthPixels(1);
// Get the default height of the cell
int height = worksheet.GetRowHeightPixels(1);
// Find the "[@image]" string
CellRange[] ranges = worksheet.FindAllString("[@image]", true, true);

foreach (CellRange range in ranges)
{
    CellRange merge = range.MergeArea;
    int columnCount = merge.ColumnCount;
    int rowCount = merge.RowCount;

    float rangeWidth = width * columnCount;
    float rangeHeight = height * rowCount;

    int row = range.Row;
    int column = range.Column;
    //Open an image.
    Image image = Image.FromFile("sign.png");
    float iWidth = image.Width;
    float iHeight = image.Height;
    //Add a picture to the ramge.
    ExcelPicture pic = worksheet.Pictures.Add(row,column, image);
   
    float w = rangeWidth / iWidth;
    float h = rangeHeight / iHeight;
    //Set the size for the picture.
    if (w > h)
    {
        pic.Width = (int)(iWidth  * h);
        pic.Height = (int)(iHeight * h);
    }
    else
    {
        pic.Width = (int)(iWidth * w);
        pic.Height = (int)(iHeight * w);
    }

    pic.LeftColumnOffset = (int)((rangeWidth- pic.Width) / rangeWidth * 1024);
    pic.TopRowOffset = 20;
   
    range.Text = "";
}

string result = "result.xlsx";
workbook.SaveToFile(result, ExcelVersion.Version2010);

Sincerely,
Annika
E-iceblue support team

[vernon1111]

Hello,

Thanks for your inquiry.
Please use the following modified code to achieve your needs. If there is any question, just feel free to contact us.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");

Worksheet worksheet = workbook.Worksheets[0];
// Get the default width of the cell
int width = worksheet.GetColumnWidthPixels(1);
// Get the default height of the cell
int height = worksheet.GetRowHeightPixels(1);
// Find the "[@image]" string
CellRange[] ranges = worksheet.FindAllString("[@image]", true, true);

foreach (CellRange range in ranges)
{
    CellRange merge = range.MergeArea;
    int columnCount = merge.ColumnCount;
    int rowCount = merge.RowCount;

    float rangeWidth = width * columnCount;
    float rangeHeight = height * rowCount;

    int row = range.Row;
    int column = range.Column;
    //Open an image.
    Image image = Image.FromFile("sign.png");
    float iWidth = image.Width;
    float iHeight = image.Height;
    //Add a picture to the ramge.
    ExcelPicture pic = worksheet.Pictures.Add(row,column, image);
   
    float w = rangeWidth / iWidth;
    float h = rangeHeight / iHeight;
    //Set the size for the picture.
    if (w > h)
    {
        pic.Width = (int)(iWidth  * h);
        pic.Height = (int)(iHeight * h);
    }
    else
    {
        pic.Width = (int)(iWidth * w);
        pic.Height = (int)(iHeight * w);
    }

    pic.LeftColumnOffset = (int)((rangeWidth- pic.Width) / rangeWidth * 1024);
    pic.TopRowOffset = 20;
   
    range.Text = "";
}

string result = "result.xlsx";
workbook.SaveToFile(result, ExcelVersion.Version2010);

Sincerely,
Annika
E-iceblue support team

Hi thanks for the code,

its works but the center alignment still off for my scenario without merging cells + the width and height of the cells for the replacement text range might be different from the default one

Is it possible to to dynamically calculate the width and height of the replacement text range?

do you mind to explain a bit on this formula?

pic.LeftColumnOffset = (int)((rangeWidth- pic.Width) / rangeWidth * 1024);
pic.TopRowOffset = 20;

[Annika.Zhou]

Hello,

Thanks for your feedback.
Please refer to the modified code below.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

// Find the "[@image]" string
CellRange[] ranges = worksheet.FindAllString("[@image]", true, true);

int rangeWidth;
int rangeHeight ;

foreach (CellRange range in ranges)
{
    rangeWidth = 0;
    rangeHeight = 0;

    int row = range.Row;
    int column = range.Column;

    CellRange merge = range.MergeArea;
    if (merge == null)
    {
        rangeWidth = worksheet.GetColumnWidthPixels(column);
        rangeHeight = worksheet.GetRowHeightPixels(row);
    }
    else
    {
        for (int r=range.MergeArea.Row; r <= range.MergeArea.LastRow; r++)
        {
            rangeHeight += worksheet.GetRowHeightPixels(r);
        }
        for (int c = range.MergeArea.Column; c <= range.MergeArea.LastColumn;c++)
        {
            rangeWidth += worksheet.GetColumnWidthPixels(c);
        }
    }
 
    //Open an image.
    Image image = Image.FromFile("sign.png");
    float iWidth = image.Width;
    float iHeight = image.Height;
    //Add a picture to the ramge.
    ExcelPicture pic = worksheet.Pictures.Add(row,column, image);
   
    float w = rangeWidth / iWidth;
    float h = rangeHeight / iHeight;
    //Set the size for the picture.
    if (w > h)
    {
        pic.Width = (int)(iWidth  * h);
        pic.Height = (int)(iHeight * h);
    }
    else
    {
        pic.Width = (int)(iWidth * w);
        pic.Height = (int)(iHeight * w);
    }
    // Set horizontal center
    pic.LeftColumnOffset = (int)((rangeWidth- pic.Width) /2f /(float)worksheet.GetColumnWidthPixels(range.Column) * 1024);
    //Set vertical center
    pic.TopRowOffset = (int)((rangeHeight - pic.Height) /2f / (float)worksheet.GetRowHeightPixels(range.Row)*256);
   
    range.Text = "";
}
string result = "result.xlsx";
workbook.SaveToFile(result, ExcelVersion.Version2010);

Sincerely,
Annika
E-iceblue support team

[vernon1111]

Hello,

Thanks for your feedback.
Please refer to the modified code below.

Code: Select all

Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

// Find the "[@image]" string
CellRange[] ranges = worksheet.FindAllString("[@image]", true, true);

int rangeWidth;
int rangeHeight ;

foreach (CellRange range in ranges)
{
    rangeWidth = 0;
    rangeHeight = 0;

    int row = range.Row;
    int column = range.Column;

    CellRange merge = range.MergeArea;
    if (merge == null)
    {
        rangeWidth = worksheet.GetColumnWidthPixels(column);
        rangeHeight = worksheet.GetRowHeightPixels(row);
    }
    else
    {
        for (int r=range.MergeArea.Row; r <= range.MergeArea.LastRow; r++)
        {
            rangeHeight += worksheet.GetRowHeightPixels(r);
        }
        for (int c = range.MergeArea.Column; c <= range.MergeArea.LastColumn;c++)
        {
            rangeWidth += worksheet.GetColumnWidthPixels(c);
        }
    }
 
    //Open an image.
    Image image = Image.FromFile("sign.png");
    float iWidth = image.Width;
    float iHeight = image.Height;
    //Add a picture to the ramge.
    ExcelPicture pic = worksheet.Pictures.Add(row,column, image);
   
    float w = rangeWidth / iWidth;
    float h = rangeHeight / iHeight;
    //Set the size for the picture.
    if (w > h)
    {
        pic.Width = (int)(iWidth  * h);
        pic.Height = (int)(iHeight * h);
    }
    else
    {
        pic.Width = (int)(iWidth * w);
        pic.Height = (int)(iHeight * w);
    }
    // Set horizontal center
    pic.LeftColumnOffset = (int)((rangeWidth- pic.Width) /2f /(float)worksheet.GetColumnWidthPixels(range.Column) * 1024);
    //Set vertical center
    pic.TopRowOffset = (int)((rangeHeight - pic.Height) /2f / (float)worksheet.GetRowHeightPixels(range.Row)*256);
   
    range.Text = "";
}
string result = "result.xlsx";
workbook.SaveToFile(result, ExcelVersion.Version2010);

Sincerely,
Annika
E-iceblue support team

HI Thanks for the code,

Its working but I have one problem,

The resized image has this problem where some part of the image is in transparent.

I have purposely apply a gray background to the sign.png to show the problem

Please refer to the screenshot attached.

ss1.png (146.49 KiB) Viewed 800 times

ss2.PNG (60.71 KiB) Viewed 798 times

[Annika.Zhou]

Hello,

Thanks for your feedback.
According to your description, I tested it again, but did not reproduce the issue you mentioned. I have attached my test project. Please download and test it on your side. Looking forward to your test feedback.

Sincerely,
Annika
E-iceblue support team

[vernon1111]

Hello,

Thanks for your feedback.
According to your description, I tested it again, but did not reproduce the issue you mentioned. I have attached my test project. Please download and test it on your side. Looking forward to your test feedback.

Sincerely,
Annika
E-iceblue support team

I have tested by running the project file provided having the same issue.

You can find the attached result.xlsx attached.

[Annika.Zhou]

Hello,

Thanks for your feedback.
To help us investigate your issue further, please provide your test environment(such as OS info (E.g. Windows 7, 64-bit) and region setting (E.g. China, Chinese)). Thanks in advance.

Sincerely,
Annika
E-iceblue support team

[vernon1111]

Windows 10 Pro 64 bit
Region : Malaysia
Language English

[Annika.Zhou]

Hello,

Thank you for your sharing.
I did the test in the same environment as you, but still did not reproduce your issue. After further investigation, I found that if I set the computer resolution to 125%, the result is the same as yours. I have submitted this issue to our Dev Team to investigate if it can be fixed. As a temporary solution, you can set your computer's resolution to 100% to avoid this issue, as shown in the screenshot. Sorry for the inconvenience caused.

Sincerely,
Annika
E-iceblue support team

[vernon1111]

HI,

Thanks for the feedback, yes this seems to work after setting the scaling back to 100%.

It will be good if this can be fixed as the windows resolution scaling should not affect this

[Annika.Zhou]

Hello,

You're welcome.
I will keep you informed as soon as there is any update to the issue. Thanks for your understanding.

Sincerely,
Annika
E-iceblue support team

[Annika.Zhou]

Hello,

Thanks for your patience.
Glad to inform you that we just released Spire.XLS Pack(Hotfix) Version:12.4.1, which fixes the issue of SPIREXLS-3732. Please download the new version from the following links for testing.

Website link: https://www.e-iceblue.com/Download/download-excel-for-net-now.html
NuGet link: https://www.nuget.org/packages/Spire.XLS/12.4.1

Sincerely,
Annika
E-iceblue support team