using System.IO;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

?

private void Btn_Click(object sender, EventArgs e)
{

????????//創建一個EXCEL文檔

????????CreateExcel("標題","文檔.XLS", "工作表名");

}

private void CreateExcel(string title,??string fileName, string sheetNames)
{
????????????//待生成的文件名稱
????????????string FileName = fileName;
????????????string FilePath = strCurrentPath + FileName;

????????????FileInfo fi = new FileInfo(FilePath);
????????????if (fi.Exists)?????//判斷文件是否已經存在,如果存在就刪除!
????????????{
????????????????fi.Delete();
????????????}
????????????if (sheetNames != null && sheetNames != "")
????????????{
????????????????Excel.Application?m_Excel = new Excel.Application();//創建一個Excel對象(同時啟動EXCEL.EXE進程)
????????????????m_Excel.SheetsInNewWorkbook = 1;//工作表的個數
????????????????Excel._Workbook m_Book = (Excel._Workbook)(m_Excel.Workbooks.Add(Missing.Value));//添加新工作簿
????????????????Excel._Worksheet m_Sheet;?

????????????????#region 處理

????????????????DataSet ds= ScData.ListData("exec Vote_2008.dbo.P_VoteResult_Update " + int.Parse(fdate));
????????????????if (ds.Tables.Count<= 0)
????????????????{
????????????????????????MessageBox.Show("沒有最新數據!");
????????????????????????return;
?????????????????}
?????????????????DataTableToSheet(title,??ds.Tables[0], m_Sheet, m_Book, 0);
????????????????#endregion

?

????????????????#region 保存Excel,清除進程
????????????????m_Book.SaveAs(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,???Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
????????????????//m_Excel.ActiveWorkbook._SaveAs(FilePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
????????????????m_Book.Close(false, Missing.Value, Missing.Value);
????????????????m_Excel.Quit();
????????????????System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Book);
????????????????System.Runtime.InteropServices.Marshal.ReleaseComObject(m_Excel);

????????????????m_Book = null;
????????????????m_Sheet = null;
????????????????m_Excel = null;
????????????????GC.Collect();
????????????????//this.Close();//關閉窗體

????????????????#endregion
????????????}
???}

????????#region 將DataTable中的數據寫到Excel的指定Sheet中
????????/// <summary>
????????/// 將DataTable中的數據寫到Excel的指定Sheet中
????????/// </summary>
????????/// <param name="dt"></param>
????????/// <param name="m_Sheet"></param>
????????public void DataTableToSheet(string title,?DataTable dt, Excel._Worksheet m_Sheet,
????????????????????????????????????Excel._Workbook m_Book,??int startrow)
????????{

????????????//以下是填寫EXCEL中數據
????????????Excel.Range range = m_Sheet.get_Range(m_Sheet.Cells[1, 1], m_Sheet.Cells[1, 2]);
????????????range.MergeCells = true;??//合并單元格
????????????range.Font.Bold = true;???//加粗單元格內字符
????????????//寫入題目
????????????m_Sheet.Cells[startrow, startrow] = title;
????????????int rownum = dt.Rows.Count;//行數
????????????int columnnum = dt.Columns.Count;//列數
????????????int num = rownum + 2;???//得到數據中的最大行數

????????????//寫入列標題
????????????for (int j = 0; j < columnnum; j++)
????????????{
????????????????????int bt_startrow = startrow + 1;

????????????????????//將字段名寫入文檔
????????????????????m_Sheet.Cells[bt_startrow, 1 + j] = dt.Columns[j].ColumnName;

?????????????????????//單元格內背景色
????????????????????m_Sheet.get_Range(m_Sheet.Cells[bt_startrow, 1 + j], m_Sheet.Cells[bt_startrow, 1 + j]).Interior.ColorIndex = 15;?????????????}
???????????

????????????//逐行寫入數據
????????????for (int i = 0; i < rownum; i++)
????????????{
????????????????for (int j = 0; j < columnnum; j++)
????????????????{
????????????????????????m_Sheet.Cells[startrow + 2 + i, 1 + j] = dt.Rows[i][j].ToString();
????????????????}
????????????}
????????????m_Sheet.Columns.AutoFit();

????????????

??????????//在當前工作表中根據數據生成圖表

???????????CreateChart(m_Book, m_Sheet, num);??
????????}

???????private void CreateChart(Excel._Workbook m_Book,Excel._Worksheet m_Sheet,int num)
????????{
????????????Excel.Range?oResizeRange;
????????????Excel.Series oSeries;

????????????m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value);
????????????m_Book.ActiveChart.ChartType = Excel.XlChartType.xlLine;//設置圖形

????????????//設置數據取值范圍
????????????m_Book.ActiveChart.SetSourceData(m_Sheet.get_Range("A2", "C" + num.ToString()), Excel.XlRowCol.xlColumns);
????????????//m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAutomatic, title);
????????????//以下是給圖表放在指定位置
????????????m_Book.ActiveChart.Location(Excel.XlChartLocation.xlLocationAsObject, m_Sheet.Name);
????????????oResizeRange?= (Excel.Range)m_Sheet.Rows.get_Item(10, Missing.Value);
????????????m_Sheet.Shapes.Item("Chart 1").Top = (float)(double)oResizeRange.Top;??//調圖表的位置上邊距
????????????oResizeRange = (Excel.Range)m_Sheet.Columns.get_Item(6, Missing.Value);??//調圖表的位置左邊距
???????????// m_Sheet.Shapes.Item("Chart 1").Left = (float)(double)oResizeRange.Left;
????????????m_Sheet.Shapes.Item("Chart 1").Width = 400;???//調圖表的寬度
????????????m_Sheet.Shapes.Item("Chart?1").Height = 250;??//調圖表的高度

????????????m_Book.ActiveChart.PlotArea.Interior.ColorIndex = 19;??//設置繪圖區的背景色
????????????m_Book.ActiveChart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//設置繪圖區邊框線條
????????????m_Book.ActiveChart.PlotArea.Width = 400;???//設置繪圖區寬度
????????????//m_Book.ActiveChart.ChartArea.Interior.ColorIndex = 10; //設置整個圖表的背影顏色
????????????//m_Book.ActiveChart.ChartArea.Border.ColorIndex = 8;// 設置整個圖表的邊框顏色
????????????m_Book.ActiveChart.ChartArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//設置邊框線條
????????????m_Book.ActiveChart.HasDataTable = false;

????????????//設置Legend圖例的位置和格式
????????????m_Book.ActiveChart.Legend.Top = 20.00; //具體設置圖例的上邊距
????????????m_Book.ActiveChart.Legend.Left = 60.00;//具體設置圖例的左邊距
????????????m_Book.ActiveChart.Legend.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;
????????????m_Book.ActiveChart.Legend.Width = 150;
????????????m_Book.ActiveChart.Legend.Font.Size = 9.5;
????????????//m_Book.ActiveChart.Legend.Font.Bold = true;
????????????m_Book.ActiveChart.Legend.Font.Name = "宋體";
????????????//m_Book.ActiveChart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;//設置圖例的位置
????????????m_Book.ActiveChart.Legend.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//設置圖例邊框線條

?

????????????//設置X軸的顯示
????????????Excel.Axis xAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
????????????xAxis.MajorGridlines.Border.LineStyle = Excel.XlLineStyle.xlDot;
????????????xAxis.MajorGridlines.Border.ColorIndex = 1;//gridLine橫向線條的顏色
????????????xAxis.HasTitle = false;
????????????xAxis.MinimumScale = 1500;
????????????xAxis.MaximumScale = 6000;
????????????xAxis.TickLabels.Font.Name = "宋體";
????????????xAxis.TickLabels.Font.Size = 9;

?

????????????//設置Y軸的顯示
????????????Excel.Axis yAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
????????????yAxis.TickLabelSpacing = 30;
????????????yAxis.TickLabels.NumberFormat = "M月D日";
????????????yAxis.TickLabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;//Y軸顯示的方向,是水平還是垂直等
????????????yAxis.TickLabels.Font.Size = 8;
????????????yAxis.TickLabels.Font.Name = "宋體";

????????????//m_Book.ActiveChart.Floor.Interior.ColorIndex = 8;?
???????????

????????????oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(1);
????????????oSeries.Border.ColorIndex = 45;
????????????oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;
????????????oSeries = (Excel.Series)m_Book.ActiveChart.SeriesCollection(2);
????????????oSeries.Border.ColorIndex?= 9;
????????????oSeries.Border.Weight = Excel.XlBorderWeight.xlThick;

????????}

轉載于:https://www.cnblogs.com/zpcB/p/5040347.html

總結

以上是生活随笔為你收集整理的Excel Chart的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。