I am working on writing HTML formatted text to excel with
bold,
italics, font colour, bullets etc. in c#.
I am using the paste special method of excel for pasting into the specific cell range.
The formatted text is pasted correctly for bold, italics, colours etc. but if html input contains spaces or bullets the contents are pasted into cells beyond the selected range.
I require the bullets and spacing to be pasted into the specifies cell range only. Kindly help me here.
When I tried using
Range("A1").PasteSpecial Paste:=xlPasteType.xlPasteValues, _
Operation:=xlPasteSpecialOperation.xlPasteSpecialOperationAdd, _
SkipBlanks:=False, _
Transpose:=False
on selected range instead of sheet.pastespecial it threw a "Pastespecial method of range class failed" exception.
Kindly help me here . Thanks. I have also attached sample excel file contents here:
Sl. No. Description
1 test for a sentence without spacing
2 spacing present in the sentence sentence 2 sentence 3
3 Test 1
Test 2
Test 3
The input html strings are:
1. test for a <u>sentence</u> without <b>spacing</b>
2. spacing present in sentence<br />
<br /><br />sentence 2<br />
<br /><br /><br /><b>sentence 3</b>
3. <ul>
<li>Test 1</li>
</ul>
<br />
<ul>
<li><b>Test 2</b></li>
</ul>
<br /> <br />
<ul>
<li>Test 3</li>
</ul>
What I have tried:
//method for pasting into excel
public void PasteFormattedTextToExcel(Excel.Worksheet sheet, Excel.Range usedRange)
{
try
{
int rowValues = sheet.UsedRange.Columns[3, Type.Missing].Rows.Count;
if (descriptionValuesForExcel.Count != 0)
{
for (int i = 3; i <= rowValues+1; i++)
{
CopyHtmlToClipBoard(descriptionValuesForExcel[i - 3]);
sheet.Select();
sheet.Cells[i,4].Select();
sheet.Cells[i,4].PasteSpecial();
sheet.Cells[i,4].Select();
}
}
}
catch (Exception ex)
{
}