Dear Community,
I am trying to develop an Excel-to-Word converter, which should also work with decimal tab stop functionality. In Excel, I add a Custom Format pattern to each table cell, which sould be decimal tabbed. This pattern is as following:
_(#,##0.00##_);(#,##0.00##);_("-"_)
When I convert the custom formatted Excel range to a Word document, I use the Word-specific Decimal Tab Stop for number alignment in those table cells containing the Custom Format pattern in Excel file.
Now the problem is: How can I calculate the correct position of the tab stop in the word table cell paragraph so that numbers are aligned correctly and are also as close as possible aligned to the right cell border in the Word document? Can you please give me a coding example how to get a Word result that looks nearly 100% like the Excel source? Please find an example Excel source file attached to this post.
Thanks for your support!
Hi Hristo,
Thanks for your inquiry. In your case, I suggest you to use the attached utility to convert Excel document to Word document. You may modify the such cell alignment to right while converting Excel to Word document. I have attached the output document with this post.
ConverterXls2Doc xls = new ConverterXls2Doc();
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(MyDir + "in.xlsx");
Document doc = xls.Convert(wb);
doc.Save(MyDir + "out.docx");
You can also add TabStops to cell’s paragraph as shown below:
// Get the first tab used in this paragraph, this should be the tab used to align the page numbers.
TabStop tab = para.ParagraphFormat.TabStops[0];
// Remove the old tab from the collection.
para.ParagraphFormat.TabStops.RemoveByPosition(tab.Position);
// Insert a new tab using the same properties but at a modified position.
// We could also change the separators used (dots) by passing a different Leader type
para.ParagraphFormat.TabStops.Add(tab.Position + 50, TabAlignment.Decimal, tab.Leader);
Hope this helps you. Please let us know if you have any more queries.
Hello,
Thanks for reply. But unfortunately, this is not a solution for my specific problem. I need to calculate the tab stop position dynamically depending on the decimals of a number value. The number of decimals is pre-defined in the Custom Format pattern in the Excel cell. I can define whole numbers and decimal numbers. Decimal numbers can have several decimals, but it is not static.
During the conversion process from Excel to Word I do not know, how many decimals a number value has, so I have to calculate an appropriate tab stop position depending on the used Custom Format pattern in Excel. This is my problem.
Currently, I am trying to calculate the position by getting font size and font family and the value of the Excel cell. Then I create a substring of the Custom Format pattern and count the number of decimals used. After that, I convert a string value of a number pattern like “.##” or “#” into a bitmap and find out the width of the bitmap. Finally, I calculate the difference between the table cell width minus bitmap width, and the result will be the current tab stop position. But this is not exactly enough, because depending on the font used, there is too much space between number value and right cell border in Word table. The tab stop should be positioned so that the space is as small as possible to align all numbers correctly.
Here is the problem, and I hope you can help me.
Hi Hristo,
Thanks for sharing the details. We are working over your query and will update you soon.
Hi Hristo,
Thanks for your patience. Considering the Excel Workbook ‘Dotted Cells Data Cache.xlsx’ you attached in your first post and to ensure a timely and accurate response, please manually create your expected Word document using Microsoft Word and attach it here for our reference. We will investigate as to how you want your final Word output be generated like. We will then provide you more information on this.
Best regards,
Hello,
I have attached the expected Word result. Hopefully, this will help you to create a solution.
Thanks for your support!
Hi Hristo,
Thanks for sharing your document. Please try using the following code snippet. I hope, this helps:
Document doc = new Document(@"C:\Temp\in.docx");
Table tab = doc.FirstSection.Body.Tables[0];
foreach(Row row in tab.Rows)
{
if (!row.IsFirstRow)
{
double prevWidth = row.Cells[1].CellFormat.PreferredWidth.Value;
double newWidth = PreferredWidth.FromPoints(CalculateWidhtOfCellContent(row.Cells[1])).Value;
double tabStop = 0;
if (prevWidth> newWidth)
{
row.Cells[1].CellFormat.PreferredWidth = PreferredWidth.FromPoints(newWidth);
tabStop = row.Cells[0].CellFormat.PreferredWidth.Value + (prevWidth - newWidth);
}
else
{
tabStop = row.Cells[0].CellFormat.PreferredWidth.Value;
}
row.Cells[0].FirstParagraph.ParagraphFormat.TabStops.Add(new TabStop(tabStop, TabAlignment.Right, TabLeader.Dots));
row.Cells[0].FirstParagraph.Runs[0].Text = row.Cells[0].FirstParagraph.Runs[0].Text\ + ControlChar.Tab;
}
}
doc.Save(@"C:\Temp\out.doc");
private static double CalculateWidhtOfCellContent(Cell cell)
{
double width = 0;
foreach(Paragraph paragraph in cell.Paragraphs)
{
double intermWidth = 0;
foreach(Run run in paragraph.Runs)
{
using(Bitmap bmp = new Bitmap(1, 1))
{
bmp.SetResolution(96, 96);
using(Graphics g = Graphics.FromImage(bmp))
{
using(System.Drawing.Font f = new System.Drawing.Font(run.Font.Name, (float) run.Font.Size))
{
SizeF textSize = g.MeasureString(run.Text.Trim(), f);
intermWidth += textSize.Width;
}
}
}
}
if (intermWidth> width)
width = intermWidth;
}
return width;
}
PS: I have attached the ‘in.doc’ and ‘out.doc’ documents here with this post for your reference.
Best regards,
Hello,
Many Thanks for the provided source code snippet. Unfortunately, this is not a solution for the current problem. In your code example, the source file is Word, but it must be Excel. In the Excel table, we have Custom Format patterns for defining the Decimal Tab Stop. This is a must have so that in the Word table the Decimal Tab Stop must be set as well. The question is, how to calculate the Tab Stop position of the Decimal Tab Stop so that the values are close to the right table cell border and the gap between the number value and the cell border is as small as possible (please see one of my previous posts). In your code example, I guess, you change the width of a table cell. This causes a change of the complete table width in the Word file, which is not allowed so that your code example will not solve the main problem: Where to place the Decimal Tab Stop dynamically in the Word ruler depending on the number value of current table cell?
It would be nice, if you could provide another solution for the problem.
Many thanks for your support!
Hi Hristo,
Thanks for the additional information. I would suggest you please use the following code snippet that inserts TabStops at the expected positions:
ConverterXls2Doc xls = new ConverterXls2Doc();
Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(@"C:\Temp\Dotted+Cells+Data+Cache.xlsx");
Document doc = xls.Convert(wb);
doc.UpdateTableLayout();
foreach (Table tab in doc.GetChildNodes(NodeType.Table, true))
{
foreach (Row row in tab.Rows)
{
if (!row.IsFirstRow)
{
double tabStop = row.Cells[0].CellFormat.PreferredWidth.Value;
row.Cells[0].FirstParagraph.ParagraphFormat.TabStops.Add(new TabStop(tabStop, TabAlignment.Right, TabLeader.Dots));
row.Cells[0].FirstParagraph.Runs[0].Text = row.Cells[0].FirstParagraph.Runs[0].Text + ControlChar.Tab;
}
}
}
doc.Save(@"C:\Temp\out.doc");
Please let me know if I can be of any further assistance.
Best regards,