Click here to Skip to main content
14,423,178 members
Rate this:
Please Sign up or sign in to vote.
See more:
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 />&nbsp;<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
            {
                //fetching the usedrange row count for the column where data needs to be pasted
                int rowValues = sheet.UsedRange.Columns[3, Type.Missing].Rows.Count;
                if (descriptionValuesForExcel.Count != 0)  
                {
                    for (int i = 3; i <= rowValues+1; i++)
                    {
                       //writing the data to clipboard
                        CopyHtmlToClipBoard(descriptionValuesForExcel[i - 3]);
                       //selecting the sheets
                        sheet.Select();
                       //collecting the cell range to paste
                        sheet.Cells[i,4].Select();
                        sheet.Cells[i,4].PasteSpecial();
                        sheet.Cells[i,4].Select();
                    }
                } 
            }
            catch (Exception ex)
            {
               
            }
Posted
Updated 13-Jan-20 0:19am
v3
Comments
Jochen Arndt 24-Jul-18 5:08am
   
It would help much better to see the HTML source of the text to be copied.

I don't know for sure how Excel handles inserting HTML but I guess it is inserting specific formats like tables and lists into multiple cells.
Member 13922719 24-Jul-18 5:21am
   
I have updated html strings. Kindly check it. Exactly, its inserting into the adjacent cells based on the spacing's yet I require the same formatting to be maintained within selected cell only.
Jochen Arndt 24-Jul-18 5:43am
   
As I expected:
You are pasting lists which is Excel spanning into multiple cells. You are also using br (line break; that is not "spacing") tags which Excel is also treating as "insert into next line".

BTW:
Why do you have lists with only one element?
It also not really valid HTML because br tags should be inside other block elements (usually p blocks).

Excel organises data row and column based. When inserting data which are organised similar, they are inserted into multiple cells. If you don't want this behaviour, you have to prepare the data accordingly.

A quick web research reveals the solution when doing it manually:
Paste into the formula bar input field instead of the cell.
But I don't know if this can be also done programmatically (inserting formulas can be done but I don't know how to "paste" HTML there).
Member 13922719 24-Jul-18 6:03am
   
Thank you..The list descriptionValuesForExcel has multiple elements each being html texts which gets written to clipboard. Okay now I understand that presence of and tags is taking the contents to adjacent cells. Sorry I dint understand the idea of pasting contents into formula bar. This list would have say 50 html texts will it still be a good idea to paste it as suggested.
Jochen Arndt 24-Jul-18 6:19am
   
When doing it manually with an active Excel instance:
Instead of selecting the cell and pasting there, select the cell and paste into the formula input field shown top of the Excel application window below the menu bar.

I think the corresponding automation method would be using Range.PasteSpecial with Paste:=xlPasteType.xlPasteFormulas

You might give it a try.
Member 13922719 24-Jul-18 7:42am
   
Sure thank you..I'll check it and get back.
Member 13922719 24-Jul-18 9:05am
   
I tried by making the following change to code:
sheet.Cells[i,4].PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteFormulas);

It throws the "Unable to get the PasteSpecial property of the Range class" exception.
Jochen Arndt 24-Jul-18 9:35am
   
Then I have no more idea besides the already mentioned one:
Ensure that your HTML does not contain multi line content.
Member 13922719 25-Jul-18 2:30am
   
Thank you..sorry but that is no possible because users can have formatted multi line contents.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100