Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1242
  • Last Modified:

c# datagridview - keep formatting on copy/paste into excel

hi all

I have a c# 2010 winforms  application which displays data in a datagridview. The table is formatted using colours and bold font etc etc.

When it is copied and pasted into excel the formatting is lost.

is there any way to keep the formatting on paste into excel?

cheers
0
cycledude
Asked:
cycledude
  • 4
  • 2
1 Solution
 
Bob LearnedCommented:
You should use HTML, and set the clipboard format as HTML, and then it should keep the style from the DataGridView.
0
 
cycledudeAuthor Commented:
Sounds interesting

how do you use html in the datagridview?
0
 
Bob LearnedCommented:
Take a look here:

Copying an HTML-fragment to the Clipboard
http://blog.tcx.be/2005/08/copying-html-fragment-to-clipboard.html

using System;
using System.Text;
using System.Windows.Forms;

class Test {
public static void CopyHtmlToClipBoard(string html) {
   Encoding enc = Encoding.UTF8;

   string begin = "Version:0.9\r\nStartHTML:{0:000000}\r\nEndHTML:{1:000000}"
      + "\r\nStartFragment:{2:000000}\r\nEndFragment:{3:000000}\r\n";

   string html_begin = "<html>\r\n<head>\r\n"
      + "<meta http-equiv=\"Content-Type\""
      + " content=\"text/html; charset=" + enc.WebName + "\">\r\n"
      + "<title>HTML clipboard</title>\r\n</head>\r\n<body>\r\n"
      + "<!--StartFragment-->";

   string html_end = "<!--EndFragment-->\r\n</body>\r\n</html>\r\n";

   string begin_sample = String.Format(begin, 0, 0, 0, 0);

   int count_begin = enc.GetByteCount(begin_sample);
   int count_html_begin = enc.GetByteCount(html_begin);
   int count_html = enc.GetByteCount(html);
   int count_html_end = enc.GetByteCount(html_end);

   string html_total = String.Format(
      begin
      , count_begin
      , count_begin + count_html_begin + count_html + count_html_end
      , count_begin + count_html_begin
      , count_begin + count_html_begin + count_html
      ) + html_begin + html + html_end;

   DataObject obj = new DataObject();
   obj.SetData(DataFormats.Html, new System.IO.MemoryStream(
      enc.GetBytes(html_total)));
   Clipboard.SetDataObject(obj, true);
}
}

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cycledudeAuthor Commented:
Hi

How do you enter the html into the datagrid?
0
 
cycledudeAuthor Commented:
The way I got around this, was to paste special into excel, this kept the formatting of the existing spreadsheet, thus allowing the colours etc to remain intact in the spreadsheet...

bit of a cop out but it was the only way I could get the colour information
0
 
cycledudeAuthor Commented:
could not find another solution
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now