如何从 Excel 工作表生成图表

本文关键字：工作 Excel | 更新日期: 2023-09-27 17:55:30

当我将datatable传递到以下函数中时，我可以生成一个 Excel 文件：

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();
}

此功能运行良好。我需要将 C# 代码中的图形添加到此 Excel 文件中。我尝试了几种方法，但没有找到正确的实现方法。

如何从 Excel 工作表生成图表

看看这里的教程（谷歌的第一批点击之一）。

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

一般思路是这样的：

// 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);
相关文章：