Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
C#
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 12-Jan-20 23:19pm
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900