C# 操作Excel生成折线图,导出折线图图片
记录c#学习过程
用到的命名空间
using Excel = Microsoft.Office.Interop.Excel;
using _Workbook = Microsoft.Office.Interop.Excel.Workbook;
using _Worksheet = Microsoft.Office.Interop.Excel.Worksheet;
using Spire.Xls;
using System.Drawing;
using System.Drawing.Imaging;
-
创建excel
private void CreateEexcel(){ excelPath = Path.Combine(workspace, "table.xlsx"); Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = xlWorkBook.Sheets["Sheet1"]; //在当前工作表中根据数据生成图表 CreateChart(xlWorkBook, xlWorkSheet); xlWorkBook.SaveAs(excelPath); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); }`
-
创建折线图
private void CreateChart(Excel._Workbook m_Book, Excel._Worksheet m_Sheet){ Excel.Range oResizeRange; m_Book.Charts.Add(Missing.Value, Missing.Value, 1, Missing.Value); m_Book.ActiveChart.ChartType = Excel.XlChartType.xlLine;//设置图形 //设置数据取值范围 m_Book.ActiveChart.SetSourceData(m_Sheet.Range["A1", "F6"], Excel.XlRowCol.xlColumns); //以下是给图表放在指定位置 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; //设置X轴的显示 Excel.Axis xAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis.HasTitle = false; xAxis.TickLabels.Font.Name = "宋体"; xAxis.TickLabels.Font.Size = 9; //设置Y轴的显示 Excel.Axis yAxis = (Excel.Axis)m_Book.ActiveChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); yAxis.TickLabels.Orientation = Excel.XlTickLabelOrientation.xlTickLabelOrientationHorizontal;//Y轴显示的方向,是水平还是垂直等 yAxis.TickLabels.Font.Size = 8; yAxis.TickLabels.Font.Name = "宋体"; yAxis.LogBase = 10; }`
-
数据写入excel
public bool WriteInXls(string filename, Excel.Application xls){ //启动Excel应用程序 xls.Visible = false; //如果表已经存在,可以用下面的命令打开 _Workbook book = xls.Workbooks.Open(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); _Worksheet sheet;//定义sheet变量 xls.Visible = false;//设置Excel后台运行 xls.DisplayAlerts = false;//设置不显示确认修改提示 try { sheet = book.Worksheets.Item[1];//(_Worksheet)book.Worksheets.get_Item[1];//获得第i个sheet,准备写入 } catch (Exception ex)//不存在就增加一个sheet { sheet = (_Worksheet)book.Worksheets.Add(Missing.Value, book.Worksheets[book.Sheets.Count], 1, Missing.Value); } //单元格填入数据 SetSheet(ref sheet); //将表另存为 //book.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //如果表已经存在,直接用下面的命令保存即可 book.Save(); book.Close(false, Missing.Value, Missing.Value);//关闭打开的表 xls.Quit();//Excel程序退出 //sheet,book,xls设置为null,防止内存泄露 sheet = null; book = null; xls = null; GC.Collect();//系统回收资源 return true; }`
-
单元格填入数据
private void SetSheet(ref _Worksheet sheet){ //判断当前树节点是第几个 int treeNum = GetSelectedNodeProfile(); sheet.Cells[1, 2] = Convert.ToString("A谱"); sheet.Cells[1, 3] = Convert.ToString("B谱"); sheet.Cells[1, 4] = Convert.ToString("C谱"); sheet.Cells[1, 5] = Convert.ToString("D谱"); sheet.Cells[1, 6] = Convert.ToString("E谱"); //X坐标: for (int row = 0; row < 5; row++)//循环设置每个单元格的值 { sheet.Cells[row + 2, 1] = Convert.ToString(row); } //Y坐标: //A List<double> Arow = new List<double>(); Arow.Add(Convert.ToDouble(textBox_Datar1c1.Text)); Arow.Add(Convert.ToDouble(textBox_Datar1c2.Text)); Arow.Add(Convert.ToDouble(textBox_Datar1c3.Text)); Arow.Add(Convert.ToDouble(textBox_Datar1c4.Text)); Arow.Add(Convert.ToDouble(textBox_Datar1c5.Text)); for (int row = 0; row < Arow.Count; row++)//循环设置每个单元格的值 sheet.Cells[row+2, 2] = Convert.ToString(Arow[row]) ; //B List<double> Brow = new List<double>(); Brow.Add(Convert.ToDouble(textBox_Datar2c2.Text)); Brow.Add(Convert.ToDouble(textBox_Datar2c3.Text)); Brow.Add(Convert.ToDouble(textBox_Datar2c4.Text)); Brow.Add(Convert.ToDouble(textBox_Datar2c5.Text)); for (int row = 0; row < Brow.Count; row++)//循环设置每个单元格的值 sheet.Cells[row + 2,3] = Convert.ToString(Brow[row]); //C List<double> Crow = new List<double>(); Crow.Add(Convert.ToDouble(textBox_Datar3c3.Text)); Crow.Add(Convert.ToDouble(textBox_Datar3c4.Text)); Crow.Add(Convert.ToDouble(textBox_Datar3c5.Text)); for (int row = 0; row < Crow.Count; row++)//循环设置每个单元格的值 sheet.Cells[row + 2, 4] = Convert.ToString(Crow[row]); //D List<double> Drow = new List<double>(); Drow.Add(Convert.ToDouble(textBox_Datar4c4.Text)); Drow.Add(Convert.ToDouble(textBox_Datar4c5.Text)); for (int row = 0; row < Drow.Count; row++)//循环设置每个单元格的值 sheet.Cells[row + 2, 5] = Convert.ToString(Drow[row]); double Erow = Convert.ToDouble(textBox_Datar5c5.Text); sheet.Cells[ 2, 6] = Convert.ToString(Erow); }`
-
chart导出图片
private void ChartToPicture(){ Workbook workbook = new Workbook(); workbook.LoadFromFile(excelPath); Worksheet sheet = workbook.Worksheets[0]; Image[] images = workbook.SaveChartAsImage(sheet); for (int i = 0; i < images.Length; i++) { //将图表保存为图片 string chartName = "chart.png"; string chartPath = Path.Combine(workspace, chartName); DeleteFile(chartPath); //images[i].Save(string.Format("img-{0}.png", i), ImageFormat.Png); images[i].Save(chartPath); } }`
-
显示图片
private void ShowChartPicture(string picturePath){ pictureBox1.Image = new Bitmap(picturePath); }`