/// <summary>
/// 给工作表添加一个图表
/// </summary>
/// <param name="firstRange">数据工作表</param>
/// <param name="xData">x轴数据区域：格式A1:A14</param>
/// <param name="ydata">y轴数据区域：格式B1:E14</param>
/// <param name="chartName">图标名称</param>
/// <param name="xname">y轴名称</param>
/// <param name="yname">y轴名称</param>
/// <param name="chartType">图表类型</param>
public static void createInnerChart(Excel.Worksheet dataSheet, List <ChartStuct> charts, string chartName, string categoryName)
{
Excel.ChartObjects ChartObjects = (Excel.ChartObjects)dataSheet.ChartObjects(Missing.Value);
int cols = 0;
foreach (ChartStuct chartStuct in charts)
{
cols += chartStuct.dataRange.Columns.Count;
}
Excel.ChartObject chartObject = ChartObjects.Add(cols * 60, 30, 800, 350);
if (charts.Count < 1)
{
return; //无数不处理
}
//绘制第一维图表
Excel.Range firstRange = charts[0].dataRange;
Excel.XlChartType chartType = charts[0].chartType;
string yname = charts[0].yname;
chartObject.Chart.ChartWizard(firstRange, chartType, Missing.Value, Excel.XlRowCol.xlColumns, 1, 1, true, chartName, categoryName, yname, Missing.Value);
chartObject.Chart.ChartArea.ClearFormats();
chartObject.Chart.ChartType = chartType;
chartObject.Chart.ChartArea.Fill.BackColor.SchemeColor = 2;//整个图表区背景
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor);
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2;
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
//Console.WriteLine(chartObject.Chart.PlotArea.Interior.ColorIndex);
//chartObject.Chart.PlotArea.Interior.PatternColorIndex = 19;
chartObject.Chart.PlotArea.Interior.ColorIndex = 2;//图例区域背景
Excel.Axis firstAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
firstAxis.HasMajorGridlines = true;//不显示横向网格线
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection(Type.Missing);
int sc = seriesCollection.Count;
if (seriesCollection.Count > 0)
{
Excel.Series seires1 = (Excel.Series)seriesCollection.Item(1);//具体数据图表区颜色
//seires1.Fill.BackColor.SchemeColor = 18;
seires1.Fill.ForeColor.SchemeColor = 46;
seires1.MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexNone;
seires1.HasLeaderLines = false;
}
//绘制第二维图表
//修改第一维图表的各个序列的图例,这里有个bug，会用最后的标题覆盖前面的
// for (int i = 0; i < charts[0].seriesTitles.Length; i++)
//{
//string stitle = charts[0].seriesTitles[i];
//Excel.Series tmpSeires = (Excel.Series)seriesCollection.Item(1);
//tmpSeires.Name = stitle;
//}
//绘制第二个图表,多维这里设置 当有图表类型为XlChartType.xl3DColumn或其他3d效果时，抛 xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;异常
for (int i = 1; i < charts.Count; i++)
{
ChartStuct cs = charts[i];
string stitle = cs.seriesTitles[0];
Excel.Series xlSeries = (Excel.Series)seriesCollection.NewSeries();
xlSeries.Name = stitle;
xlSeries.HasLeaderLines = true;
xlSeries.Fill.ForeColor.SchemeColor = 46;
try
{
xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;
}
catch (Exception eee) {
Console.WriteLine(eee.Message);
}
xlSeries.ChartType = cs.chartType;
xlSeries.Values = cs.dataRange;
Excel.Axis valueAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary);
valueAxis.HasTitle = true;
valueAxis.AxisTitle.Text = cs.yname;
}
}
/// <summary>
/// 保持excel
/// </summary>
/// <param name="isChart">是否生成图表</param>
/// <returns></returns>
public bool Save(Boolean isChart)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlBooks = xlApp.Workbooks;
Excel.Workbook xlBook = xlBooks.Add(Missing.Value);
Excel.Sheets xlsheets = xlBook.Worksheets;
Excel.Worksheet xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
int rowIndex = 0;
try
{
List<ChartStuct> csList = new List<ChartStuct>();
if (excelDatas != null)
{
ChartStuct cs = new ChartStuct();
int innerRows = 0;
foreach (ExcelData data in excelDatas)
{
if (isChart)
{
//是多维图表
if (isMulti)
{
cs = new ChartStuct();
//解决如果是柱状图和线图比较，不能只是辅助y轴的问题，将图表类型改为线图
if (data.chartType.Equals(ChartType.column)) data.chartType = ChartType.line;
}
cs.yname = data.Title;
cs.chartType = convertType(data.chartType);
cs.seriesTitles = data.series;
}
if (data.Rows != null && data.Rows.Count > 0)
{
int rowLength = 0;
int iRows = 0;
foreach (IList<string> column in data.Rows)
{
innerRows++;
iRows++;
rowIndex++;
for (int i = 0; i < column.Count; i++)
{
if (align)
xlSheet.Cells[rowIndex, i + 1] = column[i];// string.Format("'{0}", column[i]);
else
xlSheet.Cells[i + 1, rowIndex] = column[i];// string.Format("'{0}", column[i]);
}
rowLength = column.Count;
}
if (isChart)
{
//是多维图表
if (isMulti)
{
cs.dataRange = xlSheet.get_Range(xlApp.Cells[1, rowIndex - iRows + 1], xlApp.Cells[rowLength, rowIndex]);
}
else
{
cs.dataRange = xlSheet.get_Range(xlApp.Cells[1, rowIndex - innerRows + 1], xlApp.Cells[rowLength, rowIndex]);
}
}
}
if (cs.dataRange != null)
{
cs.dataRange.NumberFormat = "@";
cs.dataRange.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
cs.dataRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//xlSheet.get_Range(xlApp.Cells[rowIndex, 1], xlApp.Cells[rowIndex, column.Count + 1]).NumberFormat = "@";
//xlSheet.get_Range(xlApp.Cells[rowIndex, 1], xlApp.Cells[rowIndex, column.Count + 1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
csList.Add(cs);
}
}
}
//生成图表
if (isChart)
{
try
{
ExcelUtil.createInnerChart(xlSheet, csList, this.chartName, this.xName);
}
catch (Exception ee)
{
Console.WriteLine(" ExcelUtil.createInnerChart" + ee.Message);
}
}
xlSheet = null;
string fullPath = string.Format("{0}\\{1}.xls", this.excelPath, this.fileName);
if (File.Exists(fullPath))
File.Delete(fullPath);
xlBook.SaveAs(fullPath, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
xlBook.Close(false, Missing.Value, Missing.Value);
return true;
}
catch (Exception e)
{
Console.WriteLine("Save" + e.Message);
return false;
}
finally
{
xlApp.Quit();
xlSheet = null;
xlBook = null;
xlApp = null;
GC.Collect();
}
}
public static void createChart()
{
Excel.Application xlApp = null;
Excel.Workbook xlBook;
Excel.Workbooks xlBooks;
Excel.Sheets xlsheets;
Excel.Worksheet xlSheet;
try
{
xlApp = new Excel.Application();
xlBooks = xlApp.Workbooks;
xlBook = xlBooks.Add(Missing.Value);
xlsheets = xlBook.Worksheets;
xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
// xlApp.DisplayAlerts = false;
// create cell header
object[] objHeaders = { "Count", "num1", "num2", "num3", "num4" };
// set header text
//xlRange = xlSheet.get_Range("A1", "E1");
//xlRange.set_Value(Missing.Value, objHeaders);
//xlRange.Font.Bold = true;
//xlRange.Font.Name = "Verdana";
//xlRange.Font.Size = 10;
//xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int i = 0; i < 1; i++)
{
xlSheet.Cells[1, 1 + i] = "";
xlSheet.Cells[2, 1 + i] = i + 1 + "k";
xlSheet.Cells[3, 1 + i] = i + 2 + "k";
xlSheet.Cells[4, 1 + i] = i + 3 + "k";
xlSheet.Cells[5, 1 + i] = i + 4 + "k";
}
for (int i = 1; i < 3; i++)
{
xlSheet.Cells[1, 1 + i] = "dd" + i;
xlSheet.Cells[2, 1 + i] = i + 1;
xlSheet.Cells[3, 1 + i] = i + 2;
xlSheet.Cells[4, 1 + i] = i + 3;
xlSheet.Cells[5, 1 + i] = i + 4;
}
List <ChartStuct> css = new List <ChartStuct>();
ChartStuct cs = new ChartStuct();
cs.dataRange = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[5, 2]);
// cs.dataRange = xlSheet.get_Range("A2:E2", "A3:E11");
cs.seriesTitles = new string[] { "s1", "s2", "s3", "s4", "s5" };
cs.yname = "功率";
cs.chartType = Excel.XlChartType.xlLine;
css.Add(cs);
cs = new ChartStuct();
cs.dataRange = xlSheet.get_Range(xlApp.Cells[2, 3], xlApp.Cells[5, 3]);
// cs.dataRange = xlSheet.get_Range("A2:E2", "A3:E11");
cs.seriesTitles = new string[] { "s1", "s2", "s3", "s4", "s5" };
cs.yname = "功率2";
cs.chartType = Excel.XlChartType.xlLine;
css.Add(cs);
// xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[2, 5]).NumberFormat = "@";
//xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[2, 5]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
createInnerChart(xlSheet, css, "设备功率图", "dd");
xlBook.SaveAs("d:\\Analysis.xls", Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
xlBook.Close(false, Missing.Value, Missing.Value);
}
catch (Exception ec)
{
Console.WriteLine(ec.Message);
}
finally
{
xlSheet = null;
xlBook = null;
xlApp = null;
}
}
public static void createChart()
{
Excel.Application xlApp = null;
Excel.Workbook xlBook;
Excel.Workbooks xlBooks;
Excel.Sheets xlsheets;
Excel.Worksheet xlSheet;
try
{
xlApp = new Excel.Application();
xlBooks = xlApp.Workbooks;
xlBook = xlBooks.Add(Missing.Value);
xlsheets = xlBook.Worksheets;
xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
// xlApp.DisplayAlerts = false;
// create cell header
object[] objHeaders = { "Count", "num1", "num2", "num3", "num4" };
// set header text
//xlRange = xlSheet.get_Range("A1", "E1");
//xlRange.set_Value(Missing.Value, objHeaders);
//xlRange.Font.Bold = true;
//xlRange.Font.Name = "Verdana";
//xlRange.Font.Size = 10;
//xlRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
for (int i = 0; i < 1; i++)
{
xlSheet.Cells[1, 1 + i] = "";
xlSheet.Cells[2, 1 + i] = i + 1 + "k";
xlSheet.Cells[3, 1 + i] = i + 2 + "k";
xlSheet.Cells[4, 1 + i] = i + 3 + "k";
xlSheet.Cells[5, 1 + i] = i + 4 + "k";
}
for (int i = 1; i < 3; i++)
{
xlSheet.Cells[1, 1 + i] = "dd" + i;
xlSheet.Cells[2, 1 + i] = i + 1;
xlSheet.Cells[3, 1 + i] = i + 2;
xlSheet.Cells[4, 1 + i] = i + 3;
xlSheet.Cells[5, 1 + i] = i + 4;
}
List<ChartStuct> css = new List<ChartStuct>();
ChartStuct cs = new ChartStuct();
cs.dataRange = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[5, 2]);
// cs.dataRange = xlSheet.get_Range("A2:E2", "A3:E11");
cs.seriesTitles = new string[]{"s1","s2","s3","s4","s5"};
cs.yname="功率";
cs.chartType = Excel.XlChartType.xlLine;
css.Add(cs);
cs = new ChartStuct();
cs.dataRange = xlSheet.get_Range(xlApp.Cells[2, 3], xlApp.Cells[5, 3]);
// cs.dataRange = xlSheet.get_Range("A2:E2", "A3:E11");
cs.seriesTitles = new string[] { "s1", "s2", "s3", "s4", "s5" };
cs.yname = "功率2";
cs.chartType = Excel.XlChartType.xlLine;
css.Add(cs);
// xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[2, 5]).NumberFormat = "@";
//xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[2, 5]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
createInnerChart(xlSheet, css, "设备功率图", "dd");
xlBook.SaveAs("d:\\Analysis.xls", Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value);
xlBook.Close(false, Missing.Value, Missing.Value);
}
catch (Exception ec)
{
Console.WriteLine(ec.Message);
}
finally
{
xlSheet = null;
xlBook = null;
xlApp = null;
}
}