正文
.Net 使用HighCharts 导入图片到Excel
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
需求：数据统计报表使用到HighCharts显示各种图形：柱状图，饼图，点阵图等等，需要将数据表以及对应的图像导入到Excel中，方便打印。
解决方法： Excel导出采用NPOI，HighChart图像利用svg将图片写入到Excel中。
遇到的问题：图片模糊，图片清晰度与web页面相比差距很大。
明显可以看出，差别很大，无法达到要求，
实现此所采用的代码： 
var doc = new SvgDocument();
XmlDocument xml = new XmlDocument();
xml.LoadXml(this.chart_Hidden.Value);
doc = SvgDocument.Open(xml);
Bitmap mapImage = doc.Draw();
byte[] buffer = NPOIExportExcelHelp.BitmapToBytes(mapImage);
int pictureIdx = workBook.AddPicture(buffer, PictureType.JPEG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(, , , , , , , );
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
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()方法的优化改造，优化图片的格式，
/// <summary>
/// Creates an SvgDocument from the SVG text string.
/// </summary>
/// <returns>An SvgDocument object.</returns>
private SvgDocument CreateSvgDocument()
{
SvgDocument svgDoc;
XmlDocument xml = new XmlDocument();
xml.LoadXml(this.Svg);
XmlNodeList nodeListAllg = xml.GetElementsByTagName("g");
Dictionary<int, XmlNode[,]> dic = new Dictionary<int, XmlNode[,]>();
int i = ;
foreach (XmlNode xNod in nodeListAllg)
{
i++;
XmlNode xmlvisibility = xNod.Attributes.GetNamedItem("class");
if (xmlvisibility != null && xmlvisibility.Value == "highcharts-series-group")
{
foreach (XmlNode xNod2 in xNod.ChildNodes)
{
i++;
XmlNode xmlvisibility1 = xNod2.Attributes.GetNamedItem("visibility");
if (xmlvisibility1 != null && xmlvisibility1.Value == "hidden")
{
XmlNode[,] xmln = new XmlNode[, ];
xmln[, ] = xNod;
xmln[, ] = xNod2;
dic.Add(i, xmln);
}
}
}
else if (xmlvisibility != null && xmlvisibility.Value == "highcharts-tooltip")
{
XmlNode[,] xmln = new XmlNode[, ];
xmln[, ] = xml.FirstChild;
xmln[, ] = xNod;
dic.Add(i, xmln);
}
}
foreach (KeyValuePair<int, XmlNode[,]> a in dic)
{
a.Value[, ].RemoveChild(a.Value[, ]);
}
this.Svg = xml.OuterXml;
// Create a MemoryStream from SVG string.
using (MemoryStream streamSvg = new MemoryStream(
Encoding.UTF8.GetBytes(this.Svg)))
{
// Create and return SvgDocument from stream.
svgDoc = SvgDocument.Open(streamSvg);
}
// Scale SVG document to requested width.
svgDoc.Transforms = new SvgTransformCollection();
float scalar = (float)this.Width / (float)svgDoc.Width;
svgDoc.Transforms.Add(new SvgScale(scalar, scalar));
svgDoc.Width = new SvgUnit(svgDoc.Width.Type, svgDoc.Width * scalar);
svgDoc.Height = new SvgUnit(svgDoc.Height.Type, svgDoc.Height * scalar);
return svgDoc;
}
3 利用第二步的方法，基本可以满足要求,生成的Exce图片效果如下
4 将以上方法整理封装成类库
public class HightChartExport
{
/// <summary>
/// 获取svg图片buffer，用于excel
/// </summary>
/// <param name="svgHtml"></param>
/// <param name="width"></param>
/// <param name="height"></param>
/// <returns></returns>
public static byte[] GetSvgDocumentByte(string svgHtml, int width, int height)
{
try
{
if (string.IsNullOrEmpty(svgHtml))
{
return new byte[];
}
if (width == || height == )
{
return new byte[];
}
SvgDocument svgDocument = CreateSvgDocument(svgHtml, width, height);
return BitmapToBytes(svgDocument.Draw());
}
catch
{
throw new Exception();
}
}
private static SvgDocument CreateSvgDocument(string svgHtml, int width, int height)
{
SvgDocument svgDoc;
XmlDocument xml = new XmlDocument();
xml.LoadXml(svgHtml);
XmlNodeList nodeListAllg = xml.GetElementsByTagName("g");
Dictionary<int, XmlNode[,]> dic = new Dictionary<int, XmlNode[,]>();
int i = ;
foreach (XmlNode xNod in nodeListAllg)
{
i++;
XmlNode xmlvisibility = xNod.Attributes.GetNamedItem("class");
if (xmlvisibility != null && xmlvisibility.Value == "highcharts-series-group")
{
foreach (XmlNode xNod2 in xNod.ChildNodes)
{
i++;
XmlNode xmlvisibility1 = xNod2.Attributes.GetNamedItem("visibility");
if (xmlvisibility1 != null && xmlvisibility1.Value == "hidden")
{
XmlNode[,] xmln = new XmlNode[, ];
xmln[, ] = xNod;
xmln[, ] = xNod2;
dic.Add(i, xmln);
}
}
}
else if (xmlvisibility != null && xmlvisibility.Value == "highcharts-tooltip")
{
XmlNode[,] xmln = new XmlNode[, ];
xmln[, ] = xml.FirstChild;
xmln[, ] = xNod;
dic.Add(i, xmln);
}
}
foreach (KeyValuePair<int, XmlNode[,]> a in dic)
{
a.Value[, ].RemoveChild(a.Value[, ]);
}
svgHtml = xml.OuterXml;
// Create a MemoryStream from SVG string.
using (MemoryStream streamSvg = new MemoryStream(
Encoding.UTF8.GetBytes(svgHtml)))
{
// Create and return SvgDocument from stream.
svgDoc = SvgDocument.Open<SvgDocument>(streamSvg);
}
// Scale SVG document to requested width.
svgDoc.Transforms = new SvgTransformCollection();
float scalar = (float)width / (float)svgDoc.Width;
svgDoc.Transforms.Add(new SvgScale(scalar, scalar));
svgDoc.Width = new SvgUnit(svgDoc.Width.Type, svgDoc.Width * scalar);
svgDoc.Height = new SvgUnit(svgDoc.Height.Type, svgDoc.Height * scalar);
return svgDoc;
}
/// <summary>
/// 读取图片
/// </summary>
/// <param name="Bitmap"></param>
/// <returns></returns>
private static byte[] BitmapToBytes(Bitmap Bitmap)
{
MemoryStream ms = null;
try
{
ms = new MemoryStream();
Bitmap.Save(ms, ImageFormat.Png);
byte[] byteImage = new Byte[ms.Length];
byteImage = ms.ToArray();
return byteImage;
}
catch (ArgumentNullException ex)
{
throw ex;
}
finally
{
ms.Close();
}
}
}
5 调用方法如下：
byte[] buffer = HightChartExport.GetSvgDocumentByte(this.chart_Pie.Value, Convert.ToInt32(chart_width.Value), Convert.ToInt32(chart_height.Value));
int pictureIdx = workBook.AddPicture(buffer, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(, , , , , rowIndex + , , );
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize();
参考文章
https://github.com/imclem/Highcharts-export-module-asp.net
https://jucelin.com/highcharts_esport_net.html
