Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Mar 11, 2022 2:25 am

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
source.PNG (5.01 KiB) Viewed 682 times

output.PNG
output.PNG (5.77 KiB) Viewed 682 times


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

sample.zip
(20.48 KiB) Downloaded 146 times

vernon1111
 
Posts: 44
Joined: Fri Mar 02, 2018 4:34 am

Fri Mar 11, 2022 7:54 am

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
User avatar

Annika.Zhou
 
Posts: 1642
Joined: Wed Apr 07, 2021 2:50 am

Fri Mar 11, 2022 8:45 am

Annika.Zhou wrote: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;

vernon1111
 
Posts: 44
Joined: Fri Mar 02, 2018 4:34 am

Fri Mar 11, 2022 10:21 am

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
User avatar

Annika.Zhou
 
Posts: 1642
Joined: Wed Apr 07, 2021 2:50 am

Mon Mar 14, 2022 1:25 am

Annika.Zhou wrote: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
ss1.png (146.49 KiB) Viewed 639 times


ss2.PNG
ss2.PNG (60.71 KiB) Viewed 637 times

vernon1111
 
Posts: 44
Joined: Fri Mar 02, 2018 4:34 am

Mon Mar 14, 2022 2:58 am

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
User avatar

Annika.Zhou
 
Posts: 1642
Joined: Wed Apr 07, 2021 2:50 am

Mon Mar 14, 2022 4:37 am

Annika.Zhou wrote: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.
Attachments
result.zip
(18.72 KiB) Downloaded 102 times

vernon1111
 
Posts: 44
Joined: Fri Mar 02, 2018 4:34 am

Mon Mar 14, 2022 9:52 am

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
User avatar

Annika.Zhou
 
Posts: 1642
Joined: Wed Apr 07, 2021 2:50 am

Mon Mar 14, 2022 10:49 am

Windows 10 Pro 64 bit
Region : Malaysia
Language English

vernon1111
 
Posts: 44
Joined: Fri Mar 02, 2018 4:34 am

Tue Mar 15, 2022 3:14 am

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
Attachments
resolution.png
resolution.png (8.56 KiB) Viewed 595 times
User avatar

Annika.Zhou
 
Posts: 1642
Joined: Wed Apr 07, 2021 2:50 am

Tue Mar 15, 2022 4:19 am

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

vernon1111
 
Posts: 44
Joined: Fri Mar 02, 2018 4:34 am

Tue Mar 15, 2022 8:11 am

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
User avatar

Annika.Zhou
 
Posts: 1642
Joined: Wed Apr 07, 2021 2:50 am

Fri Apr 15, 2022 9:53 am

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
User avatar

Annika.Zhou
 
Posts: 1642
Joined: Wed Apr 07, 2021 2:50 am

Return to Spire.XLS