首页 > > 网络编程 > Asp.Net编程 >

.Net 使用HighCharts 导入图片到Excel

2018-07-27 06:32:39来源：博客园阅读 ()

需求：数据统计报表使用到HighCharts显示各种图形：柱状图，饼图，点阵图等等，需要将数据表以及对应的图像导入到Excel中，方便打印。

解决方法： Excel导出采用NPOI，HighChart图像利用svg将图片写入到Excel中。

遇到的问题：图片模糊，图片清晰度与web页面相比差距很大。

导出Excel显示图片 web显示图片

明显可以看出，差别很大，无法达到要求，

实现此所采用的代码：

 1 var doc = new SvgDocument();
 2             XmlDocument xml = new XmlDocument();
 3             xml.LoadXml(this.chart_Hidden.Value);
 4             doc = SvgDocument.Open(xml);
 5             Bitmap mapImage = doc.Draw();
 6             byte[] buffer = NPOIExportExcelHelp.BitmapToBytes(mapImage);
 7             int pictureIdx = workBook.AddPicture(buffer, PictureType.JPEG);
 8             HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
 9             HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 7, 0, 7,  15);
10             HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
11             pict.Resize();

View Code

优化方案：

1 下载开源的.net导出文件： https://github.com/imclem/Highcharts-export-module-asp.net

利用此项目中的dll文件：itextsharp.dll,sharpPDF.dll,Svg.dll

2 借鉴文章：https://jucelin.com/highcharts_esport_net.html 中针对于CreateSvgDocument()方法的优化改造，优化图片的格式，

 1 /// <summary>
 2         /// Creates an SvgDocument from the SVG text string.
 3         /// </summary>
 4         /// <returns>An SvgDocument object.</returns>
 5         private SvgDocument CreateSvgDocument()
 6         {
 7             SvgDocument svgDoc;
 8             XmlDocument xml = new XmlDocument();
 9             xml.LoadXml(this.Svg);
10             XmlNodeList nodeListAllg = xml.GetElementsByTagName("g");
11             Dictionary<int, XmlNode[,]> dic = new Dictionary<int, XmlNode[,]>();
12             int i = 0;
13             foreach (XmlNode xNod in nodeListAllg)
14             {
15                 i++;
16                 XmlNode xmlvisibility = xNod.Attributes.GetNamedItem("class");
17                 if (xmlvisibility != null && xmlvisibility.Value == "highcharts-series-group")
18                 {
19                     foreach (XmlNode xNod2 in xNod.ChildNodes)
20                     {
21                         i++;
22                         XmlNode xmlvisibility1 = xNod2.Attributes.GetNamedItem("visibility");
23                         if (xmlvisibility1 != null && xmlvisibility1.Value == "hidden")
24                         {
25                             XmlNode[,] xmln = new XmlNode[1, 2];
26                             xmln[0, 0] = xNod;
27                             xmln[0, 1] = xNod2;
28                             dic.Add(i, xmln);
29                         }
30                     }
31                 }
32                 else if (xmlvisibility != null && xmlvisibility.Value == "highcharts-tooltip")
33                 {
34                     XmlNode[,] xmln = new XmlNode[1, 2];
35                     xmln[0, 0] = xml.FirstChild;
36                     xmln[0, 1] = xNod;
37                     dic.Add(i, xmln);
38                 }
39             }
40             foreach (KeyValuePair<int, XmlNode[,]> a in dic)
41             {
42                 a.Value[0, 0].RemoveChild(a.Value[0, 1]);
43             }
44             this.Svg = xml.OuterXml;
45             // Create a MemoryStream from SVG string.
46             using (MemoryStream streamSvg = new MemoryStream(
47               Encoding.UTF8.GetBytes(this.Svg)))
48             {
49                 // Create and return SvgDocument from stream.
50                 svgDoc = SvgDocument.Open(streamSvg);
51             }
52             // Scale SVG document to requested width.
53             svgDoc.Transforms = new SvgTransformCollection();
54             float scalar = (float)this.Width / (float)svgDoc.Width;
55             svgDoc.Transforms.Add(new SvgScale(scalar, scalar));
56             svgDoc.Width = new SvgUnit(svgDoc.Width.Type, svgDoc.Width * scalar);
57             svgDoc.Height = new SvgUnit(svgDoc.Height.Type, svgDoc.Height * scalar);
58             return svgDoc;
59         }

View Code

3 利用第二步的方法，基本可以满足要求,生成的Exce图片效果如下

4 将以上方法整理封装成类库

  1 public class HightChartExport
  2     {
  3         /// <summary>
  4         /// 获取svg图片buffer，用于excel
  5         /// </summary>
  6         /// <param name="svgHtml"></param>
  7         /// <param name="width"></param>
  8         /// <param name="height"></param>
  9         /// <returns></returns>
 10         public static byte[] GetSvgDocumentByte(string svgHtml, int width, int height)
 11         {
 12             try
 13             {
 14                 if (string.IsNullOrEmpty(svgHtml))
 15                 {
 16                     return new byte[0];
 17                 }
 18                 if (width == 0 || height == 0)
 19                 {
 20                     return new byte[0];
 21                 }
 22                 SvgDocument svgDocument = CreateSvgDocument(svgHtml, width, height);
 23                 return BitmapToBytes(svgDocument.Draw());
 24             }
 25             catch
 26             {
 27                 throw new Exception();
 28             }
 29         }
 30         private static SvgDocument CreateSvgDocument(string svgHtml, int width, int height)
 31         {
 32             SvgDocument svgDoc;
 33             XmlDocument xml = new XmlDocument();
 34             xml.LoadXml(svgHtml);
 35             XmlNodeList nodeListAllg = xml.GetElementsByTagName("g");
 36             Dictionary<int, XmlNode[,]> dic = new Dictionary<int, XmlNode[,]>();
 37             int i = 0;
 38             foreach (XmlNode xNod in nodeListAllg)
 39             {
 40                 i++;
 41                 XmlNode xmlvisibility = xNod.Attributes.GetNamedItem("class");
 42                 if (xmlvisibility != null && xmlvisibility.Value == "highcharts-series-group")
 43                 {
 44                     foreach (XmlNode xNod2 in xNod.ChildNodes)
 45                     {
 46                         i++;
 47                         XmlNode xmlvisibility1 = xNod2.Attributes.GetNamedItem("visibility");
 48                         if (xmlvisibility1 != null && xmlvisibility1.Value == "hidden")
 49                         {
 50                             XmlNode[,] xmln = new XmlNode[1, 2];
 51                             xmln[0, 0] = xNod;
 52                             xmln[0, 1] = xNod2;
 53                             dic.Add(i, xmln);
 54                         }
 55                     }
 56                 }
 57                 else if (xmlvisibility != null && xmlvisibility.Value == "highcharts-tooltip")
 58                 {
 59                     XmlNode[,] xmln = new XmlNode[1, 2];
 60                     xmln[0, 0] = xml.FirstChild;
 61                     xmln[0, 1] = xNod;
 62                     dic.Add(i, xmln);
 63                 }
 64             }
 65             foreach (KeyValuePair<int, XmlNode[,]> a in dic)
 66             {
 67                 a.Value[0, 0].RemoveChild(a.Value[0, 1]);
 68             }
 69             svgHtml = xml.OuterXml;
 70             // Create a MemoryStream from SVG string.
 71             using (MemoryStream streamSvg = new MemoryStream(
 72               Encoding.UTF8.GetBytes(svgHtml)))
 73             {
 74                 // Create and return SvgDocument from stream.
 75                 svgDoc = SvgDocument.Open<SvgDocument>(streamSvg);
 76             }
 77             // Scale SVG document to requested width.
 78             svgDoc.Transforms = new SvgTransformCollection();
 79             float scalar = (float)width / (float)svgDoc.Width;
 80             svgDoc.Transforms.Add(new SvgScale(scalar, scalar));
 81             svgDoc.Width = new SvgUnit(svgDoc.Width.Type, svgDoc.Width * scalar);
 82             svgDoc.Height = new SvgUnit(svgDoc.Height.Type, svgDoc.Height * scalar);
 83             return svgDoc;
 84         }
 85         /// <summary>
 86         /// 读取图片
 87         /// </summary>
 88         /// <param name="Bitmap"></param>
 89         /// <returns></returns>
 90         private static byte[] BitmapToBytes(Bitmap Bitmap)
 91         {
 92             MemoryStream ms = null;
 93             try
 94             {
 95                 ms = new MemoryStream();
 96                 Bitmap.Save(ms, ImageFormat.Png);
 97                 byte[] byteImage = new Byte[ms.Length];
 98                 byteImage = ms.ToArray();
 99                 return byteImage;
100             }
101             catch (ArgumentNullException ex)
102             {
103                 throw ex;
104             }
105             finally
106             {
107                 ms.Close();
108             }
109         }
110     }

View Code

5 调用方法如下：

1 byte[] buffer = HightChartExport.GetSvgDocumentByte(this.chart_Pie.Value, Convert.ToInt32(chart_width.Value), Convert.ToInt32(chart_height.Value));
2  int pictureIdx = workBook.AddPicture(buffer, PictureType.PNG);
3  HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
4  HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, rowIndex + 1, 0, 15);
5  HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
6   pict.Resize();