.Net 使用HighCharts 导入图片到Excel
需求:数据统计报表使用到HighCharts显示各种图形:柱状图,饼图,点阵图等等,需要将数据表以及对应的图像导入到Excel中,方便打印。
解决方法: Excel导出采用NPOI,HighChart图像利用svg将图片写入到Excel中。
遇到的问题:图片模糊,图片清晰度与web页面相比差距很大。
明显可以看出,差别很大,无法达到要求,
实现此所采用的代码:
View Code
View Code
View Code
View Code
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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();
优化方案:
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()方法的优化改造,优化图片的格式,
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
3 利用第二步的方法,基本可以满足要求,生成的Exce图片效果如下
![](https://images2018.cnblogs.com/blog/23638/201807/23638-20180726134613618-984469719.png)
4 将以上方法整理封装成类库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
5 调用方法如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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();
参考文章
路曼曼其修远兮,吾将上下而求索