Hello,
I encounter a problem. Right now I am required to add pictures into cells, and these cells have values already. So I have to put the picture right behind the cell value, or behind specific character in the cell.
How can I do that? Thank you in advance!
@msdos41
You can use CellsHelper.GetTextWidth method to measure the width of the text and then change the position of the added image. Please refer to the attachment (22.4 KB).
The sample code as follows:
Workbook wb = new Workbook(filePath + "sample.xlsx");
Worksheet sheet = wb.Worksheets[0];
Cell d7 = sheet.Cells["D7"];
// get text width in d7 cell
double textWidthPt = CellsHelper.GetTextWidth(d7.StringValue, d7.GetStyle().Font, 1.0d);
int textWidthPx = (int)(textWidthPt * CellsHelper.DPI / 72 + 0.5);
//Add an image after the text in cell d7 and change the position
int pictIndex = sheet.Pictures.Add(d7.Row, d7.Column, filePath + "sample.png");
Picture pict = sheet.Pictures[pictIndex];
pict.X += textWidthPx;
wb.Save(filePath + "out_net.xlsx");
Hope helps a bit.
Thanks. It helps a lot!
Furthermore, your sample’s value is “AAABBBCCC” and you put the png behind the last character of the value. But if I want to put the png behind the fifth character in this situation is the second “B”.
How to update the code to achieve this?
@msdos41
Please try the following code:
Workbook wb = new Workbook(filePath + "sample.xlsx");
Worksheet sheet = wb.Worksheets[0];
Cell d7 = sheet.Cells["D7"];
//Place after the fifth character position
string measureText = d7.StringValue.Substring(0, 5);
// get text width in measureText string
double textWidthPt = CellsHelper.GetTextWidth(measureText, d7.GetStyle().Font, 1.0d);
int textWidthPx = (int)(textWidthPt * CellsHelper.DPI / 72 + 0.5);
//Add an image after the text in cell d7 and change the position
int pictIndex = sheet.Pictures.Add(d7.Row, d7.Column, filePath + "sample.png");
Picture pict = sheet.Pictures[pictIndex];
pict.X += textWidthPx;
wb.Save(filePath + "out_net.xlsx");
Hope helps a bit.
Thank you! It helps a lot!
By the way, I use Find method to locate specific cell by containing key words. But I found that this method only get cell object not a list of cell. So if there are several finding results, how to do it?
Here is my current code.
Aspose.Cells.Cells cells = worksheet.Cells;
Aspose.Cells.FindOptions opts = new Aspose.Cells.FindOptions();
opts.LookInType = Aspose.Cells.LookInType.Values;
opts.LookAtType = Aspose.Cells.LookAtType.Contains;
Aspose.Cells.Cell cell = cells.Find(approver.KeyWord, null, opts);
Another question is I want to insert a textbox with specific font, font-size into a specific place in the worksheet. Could you show me some example codes?
Thank you so much!
@msdos41
When you use the Cells.Find function to find cells, you can only get one cell at a time. You can search for all cells one by one by specifying the previous cell. The steps for adding a text box are the same as adding an image. Please refer to the following code and attachments. (15.6 KB).
The following code as follows:
Workbook wb = new Workbook(filePath + "sample.xlsx");
Worksheet sheet = wb.Worksheets[0];
Cell d7 = sheet.Cells["D7"];
//Place after the fifth character position
string measureText = d7.StringValue.Substring(0, 5);
// get text width in d7 cell
double textWidthPt = CellsHelper.GetTextWidth(measureText, d7.GetStyle().Font, 1.0d);
int textWidthPx = (int)(textWidthPt * CellsHelper.DPI / 72 + 0.5);
//Add a textbox after the text in cell d7 and change the position
TextBox textbox = sheet.Shapes.AddTextBox(d7.Row, 0, d7.Column, 0, 100, 200);
textbox.Text = "set text";
textbox.Font.Name = "Calibri";
textbox.Font.Size = 18;
textbox.X += textWidthPx;
// You can search for all cells one by one by specifying the previous cell
Aspose.Cells.Cells cells = sheet.Cells;
Aspose.Cells.FindOptions opts = new Aspose.Cells.FindOptions();
opts.LookInType = Aspose.Cells.LookInType.Values;
opts.LookAtType = Aspose.Cells.LookAtType.Contains;
Cell temp = null;
do
{
temp = cells.Find("BBB", temp, opts);
if (temp != null)
{
Console.WriteLine(temp.Name + ": " + temp.StringValue);
}
}
while (temp != null);
wb.Save(filePath + "out_net.xlsx");
Hope helps a bit.
@John.He Thanks a lot.
Till now everything works fine.
Question1: if the found cell’s value is in the center of the cell, the method you gave me is not correct. CellsHelper.GetTextWidth only get the pure text width, not consider the position in the cell. how to deal with it?
Question2: Please check my uploaded zip file. And there are several merged cells. And I want to put image into the target cell position which is also merged. The input is find cell containing keyword “BBBB” which determine the image y position and find cell containing keyword “Name” which determine the image x position. Hopefully image could be put in the center of the column “Name”.
Please give me some sample code. Thanks.
question.zip (6.7 KB)
@msdos41
If you want to place the text box after the found cell, there are two ways to achieve your goal. One is to locate the cell, directly locate the adjacent cell, and then obtain the corresponding row and column data and add a text box. The second is to calculate the width of the cell after finding it, and offset the x position when adding a text box.
The sample code as follows:
Workbook wb = new Workbook(filePath + "question.xlsx");
Worksheet sheet = wb.Worksheets[0];
// You can search for all cells one by one by specifying the previous cell
Aspose.Cells.Cells cells = sheet.Cells;
Aspose.Cells.FindOptions opts = new Aspose.Cells.FindOptions();
opts.LookInType = Aspose.Cells.LookInType.Values;
opts.LookAtType = Aspose.Cells.LookAtType.Contains;
Cell temp = null;
do
{
temp = cells.Find("BBBB", temp, opts);
if (temp != null)
{
Console.WriteLine(temp.Name + ": " + temp.StringValue);
//The first method
int newColumn = 0;
if (temp.IsMerged)
{
Range mergeRange = temp.GetMergedRange();
newColumn = mergeRange.FirstColumn + mergeRange.ColumnCount;
}
else
{
newColumn = temp.Column + 1;
}
//Add an textbox after the text in cell and change the position
TextBox textbox1 = sheet.Shapes.AddTextBox(temp.Row, 0, newColumn, 0, 100, 200);
textbox1.Text = "the first method";
textbox1.Font.Name = "Calibri";
textbox1.Font.Size = 18;
//The second method
//get total width
int totalWidth = 0;
if (temp.IsMerged)
{
Range mergeRange = temp.GetMergedRange();
for (int i = 0; i < mergeRange.ColumnCount; i++)
{
totalWidth += cells.GetColumnWidthPixel(mergeRange.FirstColumn + i);
}
}
else
{
totalWidth = cells.GetColumnWidthPixel(temp.Column);
}
//Add an textbox after the text in cell and change the position
TextBox textbox2 = sheet.Shapes.AddTextBox(temp.Row, 0, temp.Column, 0, 100, 200);
textbox2.Text = "the second method";
textbox2.Font.Name = "Calibri";
textbox2.Font.Size = 18;
textbox2.X += totalWidth;
//Add a little cheaper for easy observation
textbox2.Y += 10;
}
}
while (temp != null);
wb.Save(filePath + "out_net.xlsx");
Hope helps a bit.
@John.He Thanks.
If value “AAAA” is in a merged range and in the center position, I still want to put the image next to the “AAAA”. How to do it? CellsHelper.GetTextWidth only get the pure text width, not consider the position in the cell.
@msdos41
You can calculate the offset value of x by the relationship between range width and text width. Please refer to the following code.
Workbook wb = new Workbook(filePath + "question.xlsx");
Worksheet sheet = wb.Worksheets[0];
// You can search for all cells one by one by specifying the previous cell
Aspose.Cells.Cells cells = sheet.Cells;
Aspose.Cells.FindOptions opts = new Aspose.Cells.FindOptions();
opts.LookInType = Aspose.Cells.LookInType.Values;
opts.LookAtType = Aspose.Cells.LookAtType.Contains;
Cell temp = null;
do
{
temp = cells.Find("BBBB", temp, opts);
if (temp != null)
{
Console.WriteLine(temp.Name + ": " + temp.StringValue);
//get total width
int totalWidth = 0;
if (temp.IsMerged)
{
Range mergeRange = temp.GetMergedRange();
for (int i = 0; i < mergeRange.ColumnCount; i++)
{
totalWidth += cells.GetColumnWidthPixel(mergeRange.FirstColumn + i);
}
}
else
{
totalWidth = cells.GetColumnWidthPixel(temp.Column);
}
// get text width in cell
double textWidthPt = CellsHelper.GetTextWidth(temp.StringValue, temp.GetStyle().Font, 1.0d);
int textWidthPx = (int)(textWidthPt * CellsHelper.DPI / 72 + 0.5);
// When aligned horizontally in the center
int offsetX = (totalWidth - textWidthPx) / 2 + textWidthPx;
//Add an textbox after the text in cell and change the position
TextBox textbox2 = sheet.Shapes.AddTextBox(temp.Row, 0, temp.Column, 0, 100, 200);
textbox2.Text = "set text";
textbox2.Font.Name = "Calibri";
textbox2.Font.Size = 18;
textbox2.X += offsetX;
}
}
while (temp != null);
wb.Save(filePath + "out_net.xlsx");
Hope helps a bit.
If the worksheet is protected and need password to unprotect. I dont know the password.
So if i use the regular way to new the workbook not passing password, will it be error?
I cannot insert any image or textbox in this protected sheet?
@msdos41
When the worksheet or workbook is protected, you will be restricted from performing certain operations in Excel. But when you use code to do some operations, you can still operate normally. Aspose.Cells will not manage whether certain operations are restricted, and users can make judgments and operations based on their actual situation.