如何使用c＃interop从Excel表生成图表？ [英] How generate chart from Excel sheet using c# interop?

问题描述

  public static void ExportDataTableToExcel（DataTable dt，string filepath ）
 {
 
 object missing = Type.Missing; 
 
 object misValue = System.Reflection.Missing.Value; 
 
 //创建excel 
 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application（）; 
 
 //添加excel工作簿
 Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add（）; 
 
 //将工作表添加到工作簿
 Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets [1]作为Microsoft.Office.Interop.Excel.Worksheet; 
 
 //将第二个工作表添加到工作簿
 Microsoft.Office.Interop.Excel.Worksheet ws2 = wb.Sheets [2]作为Microsoft.Office.Interop.Excel.Worksheet; 
 
 //设置标题行粗体
 ws.Range [A1，A1]。EntireRow.Font.Bold = true; 
 
 //调整所有列
 ws.Columns.AutoFit（）; 
 
 // spit top row 
 ws.Application.ActiveWindow.SplitRow = 1; 
 
 //将图像插入到worsheet 2 
 ws2.Shapes.AddPicture（C：\\\Koala.JPG，Microsoft.Office.Core.MsoTriState.msoFalse，Microsoft.Office 50C，50，300，300）; 
 
 //冻结顶行
 ws.Application.ActiveWindow.FreezePanes = true; 
 
 
 int rowCount = 1; 
 
 foreach（dt.Rows中的DataRow dr）
 {
 
 rowCount + = 1; 
 
 for（int i = 1; i< dt.Columns.Count + 1; i ++）
 {
 
 //首次添加标题
 
 if（rowCount == 2）
 {
 
 ws.Cells [1，i] = dt.Columns [i  -  1] .ColumnName; 
 ws.Cells [1，i] .Interior.ColorIndex = 40; 
 
 //添加单元格边框
 ws.Cells [1，i] .Borders.LineStyle = Excel.XlLineStyle.xlContinuous; 
 
} 
 
 ws.Cells [rowCount，i] = dr [i  -  1] .ToString（）; 
 
 //添加单元格边框
 ws.Cells [rowCount，i] .Borders.LineStyle = Excel.XlLineStyle.xlContinuous; 
 
} 
 
} 
 
 Microsoft.Office.Interop.Excel.Range范围=（Microsoft.Office.Interop.Excel.Range）ws。 UsedRange; 
 
 Console.Write（range.ToString（））; 
 
 
 wb.SaveAs（@C：\Test.xls，Excel.XlFileFormat.xlWorkbookNormal，misValue，
 misValue，misValue，misValue，
 Excel .XlSaveAsAccessMode.xlExclusive，misValue，
 misValue，misValue，misValue，misValue）; 
 
 wb.Close（缺少，缺少，缺少）; 
 
 excel.Quit（）; 
} 
  

此功能很好。我需要从C＃代码添加一个图到这个excel文件。我尝试了几种方法。但没有找到正确的方法来实现。你可以帮助我吗？

解决方案

看看教程这里（第一个Google命中之一）。

它很清楚地描述了如何制作一个简单的图表在Excel中从C＃代码。

一般的想法是这样的：

  //添加图表。 
 var charts = worksheet.ChartObjects（）as 
 Microsoft.Office.Interop.Excel.ChartObjects; 
 var chartObject = charts.Add（60，10，300，300）as 
 Microsoft.Office.Interop.Excel.ChartObject; 
 var chart = chartObject.Chart; 
 
 //设置图表范围。 
 var range = worksheet.get_Range（topLeft，bottomRight）; 
 chart.SetSourceData（range）; 
 
 //设置图表属性。 
 chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine; 
 chart.ChartWizard（来源：range，
标题：graphTitle，
 CategoryTitle：xAxis，
 ValueTitle：yAxis）; 
  

I can generate excel file when i pass datatable into below function.

public static void ExportDataTableToExcel(DataTable dt, string filepath)
{

    object missing = Type.Missing;

    object misValue = System.Reflection.Missing.Value;

    //create excel
    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

    //add excel workbook
    Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add();

    //add worksheet to workbook
    Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

    //add 2nd worksheet to workbook
    Microsoft.Office.Interop.Excel.Worksheet ws2 = wb.Sheets[2] as Microsoft.Office.Interop.Excel.Worksheet;

    //Set the header-row bold
    ws.Range["A1", "A1"].EntireRow.Font.Bold = true;

    //Adjust all columns
    ws.Columns.AutoFit();

   //spit top row
    ws.Application.ActiveWindow.SplitRow = 1;

    //insert image into worsheet 2
    ws2.Shapes.AddPicture("C:\\Koala.JPG", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 300, 300); 

    //freeze top row
    ws.Application.ActiveWindow.FreezePanes = true;


    int rowCount = 1;

    foreach (DataRow dr in dt.Rows)
    {

        rowCount += 1;

        for (int i = 1; i < dt.Columns.Count + 1; i++)
        {

            // Add the header the first time through

            if (rowCount == 2)
            {

                ws.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                ws.Cells[1, i].Interior.ColorIndex = 40;

                // add cell border
                ws.Cells[1, i].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

            }

            ws.Cells[rowCount, i] = dr[i - 1].ToString();

            // add cell border
            ws.Cells[rowCount, i].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

        }

    }

    Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)ws.UsedRange;

    Console.Write(range.ToString());


    wb.SaveAs(@"C:\Test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue,
                                         misValue, misValue, misValue,
                                         Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                                         misValue, misValue, misValue, misValue);

    wb.Close(missing, missing, missing);

    excel.Quit();
}

This function works well . I need to add a graph from C# code to this excel file. I tried few methods.But did not find proper way to implement. Guys can you help me ?

解决方案

Take a look at the tutorial here (one of the first Google hits).

It very clearly describe how to make a simple chart in Excel from C# code.

The general idea is like this:

// Add chart.
var charts = worksheet.ChartObjects() as
    Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(60, 10, 300, 300) as
    Microsoft.Office.Interop.Excel.ChartObject;
var chart = chartObject.Chart;

// Set chart range.
var range = worksheet.get_Range(topLeft, bottomRight);
chart.SetSourceData(range);

// Set chart properties.
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine;
chart.ChartWizard(Source: range,
    Title: graphTitle,
    CategoryTitle: xAxis,
    ValueTitle: yAxis);

这篇关于如何使用c＃interop从Excel表生成图表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！