[游戏开发]C# Excel绘制组合图

绘制组合图表，记录一下，中间遇到的问题点，
1.获取行数

//这个会把曾经使用过的元素所在的范围都包含进去，并不是实际数据的行数
int rowNum = xSheet.UsedRange.CurrentRegion.Rows.Count;
//这个就很好了，获取的是实际的行数
int rowNum = xSheet.Range["A1".ToUpper()].CurrentRegion.Rows.Count; 

2.重新绘制图表

//根据名字获取已有的图表，修改，如果没有就新增。
Excel.Chart xChart = null;
if (xSheet.ChartObjects().Count > 0)
   xChart = xSheet.ChartObjects("GroupChart").Chart;
else
   xChart = xBook.Charts.Add();
                
//这样会出现问题，一开始的组合图有一条折线，一个柱状图，现在想加一条折线，一个柱状图，会加不上去，只会更改原有的数据源，不知道为什么。我的解决方法是删除原有的图表重新绘制
//删除原有的图表 
xSheet.ChartObjects().Delete();
//重新生成图表
Excel.Chart xChart = xBook.Charts.Add();

3.清除单元格区域中的内容

//清除单元格区域中的内容
xSheet.UsedRange.ClearContents();

完整的代码
引用Microsoft.Office.Interop.Excel，ExcelDna.Integration;

 public static void OpenFOFSheets()
{
 Excel.Application xApp = (Excel.Application)ExcelDnaUtil.Application;
Excel.Workbook xBook = xApp.ActiveWorkbook;
Excel.Worksheet xSheet = xBook.ActiveSheet;
//获取sheet行数,获取的是实际行数
int rowNum = xSheet.Range["A1".ToUpper()].CurrentRegion.Rows.Count;
//删除原有的图表 
xSheet.ChartObjects().Delete();
//绘制优化后组合表
MoreCharts(xBook, xSheet, rowNum, fofName);
 xBook.Save();
}
        /// <summary>
        /// 绘制优化后chart组合图表
        /// </summary>
        /// <param name="xBook">工作薄</param>
        /// <param name="xSheet">工作表</param>
        /// <param name="rowNum">行数</param>
        /// <param name="fofName">fof名称</param>
        private static void MoreCharts(Excel.Workbook xBook, Excel.Worksheet xSheet, int rowNum, string fofName)
        {
            Excel.Chart xChart = xBook.Charts.Add();

            //数据源
            string str = string.Format("A1:E{0}", rowNum);
            Excel.Range range = xSheet.Range[str];
            xChart.SetSourceData(range);
            //折线图
            var chart1 = xChart.FullSeriesCollection(1);
            chart1.ChartType = Excel.XlChartType.xlLine;
            chart1.Format.Fill.ForeColor.RGB = Excel.XlRgbColor.rgbBlue;//蓝色
            var chart2 = xChart.FullSeriesCollection(2);
            chart2.ChartType = Excel.XlChartType.xlLine;
            chart2.Format.Fill.ForeColor.RGB = Excel.XlRgbColor.rgbDarkRed;//红色
            //柱状图
            var chart3 = xChart.FullSeriesCollection(3);
            chart3.ChartType = Excel.XlChartType.xlColumnClustered;
            chart3.Format.Fill.ForeColor.RGB = Excel.XlRgbColor.rgbGray;//灰色
            var chart4 = xChart.FullSeriesCollection(4);
            chart4.ChartType = Excel.XlChartType.xlColumnClustered;
            chart4.Format.Fill.ForeColor.RGB = Excel.XlRgbColor.rgbOrange;//橙色
            //坐标轴为2组
            chart3.AxisGroup = 2;
            chart4.AxisGroup = 2;
            //获取次坐标轴的最小值
            double min = GetMinimumScale(xSheet, rowNum);
            //设置chart2坐标轴最大值最小值
            xChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary).MaximumScale = Math.Abs(min);
            xChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary).MinimumScale = min;

            //将图表放到指定位置
            xChart.Location(Excel.XlChartLocation.xlLocationAsObject, xSheet.Name);

            xSheet.Shapes.Item("图表 1").Name = "GroupChart";
            //指定图表位置
            xSheet.Shapes.Item("GroupChart").Top = (float)(double)xSheet.get_Range("H6").Top;
            xSheet.Shapes.Item("GroupChart").Left = (float)(double)xSheet.get_Range("H6").Left;

        }
        /// <summary>
        /// 得到次坐标的最小值
        /// </summary>
        /// <param name="xSheet"></param>
        /// <param name="rowNum"></param>
        /// <returns></returns>
        private static double GetMinimumScale(Excel.Worksheet xSheet, int rowNum)
        {
            string str = string.Format("D2:D{0}", rowNum);
            Excel.Range range = xSheet.Range[str];
            var arr = range.Value2;
            List<double> arrDou = new List<double>();
            for (int i = 0; i < range.Count; i++)
            {
                arrDou.Add(arr[i + 1, 1]);
            }
            double min = arrDou.Min();
            //向下取整，保留两位小数
            min = Math.Floor(min * 100) / 100;
            return min;
        }