/// <summary> /// 柱形图,语文,数学 多个颜色 /// </summary> /// <param name="sheet"></param> private void ChartColumn(Worksheet sheet) { Spire.Xls.Chart chartColumn = sheet.Charts.Add(ExcelChartType.ColumnClustered); chartColumn.ChartTitle = "分数"; chartColumn.ChartTitleArea.IsBold = true; chartColumn.ChartTitleArea.Size = 15; chartColumn.DataRange = sheet.Range[2, 2, 3, 3]; chartColumn.SeriesDataFromRange = false; chartColumn.LeftColumn = 1; chartColumn.TopRow = 5; chartColumn.RightColumn = 6; chartColumn.BottomRow = 16; chartColumn.PrimaryCategoryAxis.Title = "姓名"; //X轴显示名 chartColumn.PrimaryCategoryAxis.CategoryLabels = sheet.Range[2, 1, 3, 1]; chartColumn.PrimaryValueAxis.Title = "分数"; //Y轴显示名 chartColumn.Legend.Position = LegendPositionType.Right; chartColumn.PlotArea.ForeGroundColor = Color.Transparent; //透明,去掉默认的灰色 #region 初始化图例 Spire.Xls.Charts.ChartSerie csColumn1 = chartColumn.Series[0]; csColumn1.Name = "语文分数"; csColumn1.Values = sheet.Range[2, 2, 3, 2]; //csColumn.Values.NumberFormat = "0.0#%"; csColumn1.DataPoints.DefaultDataPoint.DataLabels.HasValue = true; csColumn1.Format.Fill.ForeColor = ColorTranslator.FromHtml("#5B9BD5"); Spire.Xls.Charts.ChartSerie csColumn2 = chartColumn.Series[1]; csColumn2.Name = "数学分数"; csColumn2.Values = sheet.Range[2, 3, 3, 3]; csColumn2.DataPoints.DefaultDataPoint.DataLabels.HasValue = true; csColumn2.Format.Fill.ForeColor = ColorTranslator.FromHtml("#ED7D31"); #endregion }
/// <summary> /// 用姓名+语文成绩 生成饼图 /// </summary> /// <param name="sheet"></param> private void ChartPie(Worksheet sheet) { Spire.Xls.Chart chartPie = sheet.Charts.Add(ExcelChartType.Pie); chartPie.DataRange = sheet.Range[2, 2, 3, 2]; chartPie.SeriesDataFromRange = false; //设置图表的位置 chartPie.LeftColumn = 1; chartPie.TopRow = 17; chartPie.RightColumn = 6; chartPie.BottomRow = 27; //图表标题 chartPie.ChartTitle = "语文成绩"; //设置字体 chartPie.ChartTitleArea.IsBold = true; chartPie.ChartTitleArea.Size = 12; #region 初始化图例 Spire.Xls.Charts.ChartSerie csPie = chartPie.Series[0]; csPie.CategoryLabels = sheet.Range[2, 1, 3, 1]; csPie.Values = sheet.Range[2, 2, 3, 2]; csPie.DataPoints[0].DataFormat.Fill.ForeColor = Color.FromArgb(91, 155, 213); csPie.DataPoints[1].DataFormat.Fill.ForeColor = ColorTranslator.FromHtml("#ED7D31"); #endregion }
public void GetChart() { wb.LoadFromFile(path); ws = wb.Worksheets[0]; int row = 5; int column = ws.Range.ColumnCount - 20; int lastRow = 8; int lastColumn = ws.Range.ColumnCount; for (int i = 0; i < chart.Length; i++) { int k = 6; int r = row; chart[i] = ws.Charts.Add(); chart[i].ChartType = ExcelChartType.Line; //chart[i].DataRange = ws.Range[row-1, column, lastRow-1, lastColumn]; //chart[i].SeriesDataFromRange = false; //chart 位置 A20-D30 chart[i].LeftColumn = 1; chart[i].TopRow = 20; chart[i].RightColumn = 10; chart[i].BottomRow = 40; chart[i].ChartTitle = pic_name[i]; chart[i].ChartTitleArea.IsBold = true; chart[i].ChartTitleArea.Size = 10; //设置横坐标的标题 chart[i].PrimaryCategoryAxis.Title = "时间"; chart[i].PrimaryCategoryAxis.Font.IsBold = true; chart[i].PrimaryCategoryAxis.HasMajorGridLines = true; chart[i].PrimaryCategoryAxis.TitleArea.IsBold = true; chart[i].PrimaryValueAxis.Title = "压力值(max)"; chart[i].PrimaryValueAxis.HasMajorGridLines = true; chart[i].PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart[i].PrimaryValueAxis.MinValue = 5; chart[i].PrimaryValueAxis.TitleArea.IsBold = true; //循环绘制 for (int m = 0; m < 3; m++) { Spire.Xls.Charts.ChartSerie cs = chart[i].Series.Add(); //每个series的名字 cs.Name = ws.Range[string.Format(@"B{0}", k++)].Value;//B6~B8 //设置横坐标 cs.CategoryLabels = ws.Range[row, column, row, lastColumn]; r++; cs.Values = ws.Range[r, column, r, lastColumn]; cs.SerieType = ExcelChartType.LineMarkers; cs.Format.Options.IsVaryColor = true; cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = false; } row += 5; lastRow += 5; } Image[] images = wb.SaveChartAsImage(ws); if (images != null) { int i = 0; while (i < images.Length) { images[i].Save(pic_path[i], ImageFormat.Png); i++; } } //wb.SaveToFile(path); }
public ChartFuck() { wb.LoadFromFile(path); ws = wb.Worksheets[0]; int row = 5; int column = ws.Range.ColumnCount - 20; int lastRow = 8; int lastColumn = ws.Range.ColumnCount; chart = ws.Charts.Add(); chart.ChartType = ExcelChartType.Line; //chart.DataRange = ws.Range[row, column, lastRow, lastColumn]; chart.SeriesDataFromRange = false; chart.LeftColumn = 1; chart.TopRow = 20; chart.RightColumn = 10; chart.BottomRow = 40; chart.ChartTitle = pic_name[0]; chart.ChartTitleArea.IsBold = true; chart.ChartTitleArea.Size = 10; Spire.Xls.Charts.ChartSerie cs = chart.Series.Add(); cs.Name = ws.Range["B6"].Value;//B6~B8 cs.CategoryLabels = ws.Range[5, 10, 5, 11]; cs.Values = ws.Range[6, 10, 6, 11]; cs.DataFormat.ShowActiveValue = true; cs.Format.Options.IsVaryColor = true; cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = false; Spire.Xls.Charts.ChartSerie cs2 = chart.Series.Add(); cs2.Name = ws.Range["B7"].Value;//B6~B8 cs2.CategoryLabels = ws.Range[5, 10, 5, 11]; cs2.Values = ws.Range[7, 10, 7, 11]; cs2.DataFormat.ShowActiveValue = true; cs2.Format.Options.IsVaryColor = true; cs2.DataPoints.DefaultDataPoint.DataLabels.HasValue = false; //int r = row; //int k = 6; //for (int m = 0; m < 3; m++) //{ // Spire.Xls.Charts.ChartSerie cs = chart.Series.Add(); // //每个series的名字 // cs.Name = ws.Range[string.Format(@"B{0}", k++)].Value;//B6~B8 // //设置横坐标 // cs.CategoryLabels = ws.Range[row, column, row, lastColumn]; // r++; // //cs.Values = ws.Range[r, column, r, lastColumn]; // cs.DataFormat.ShowActiveValue = true; // cs.Format.Options.IsVaryColor = true; // cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = false; //} Image[] images = wb.SaveChartAsImage(ws); if (images != null) { int i = 0; while (i < images.Length) { images[i].Save(pic_path[i], ImageFormat.Png); i++; } } wb.SaveToFile(path); }
private void bt_baocao_Click(object sender, EventArgs e) { //Create a new workbook Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); //Initialize worksheet workbook.CreateEmptySheets(1); Spire.Xls.Worksheet sheet = workbook.Worksheets[0]; string conString = @"Data Source=DESKTOP-RGHAECC;Initial Catalog=SucKhoeNhanVien_PNT;Integrated Security=True"; SqlConnection sqlCon = new SqlConnection(conString); sqlCon.Open(); SqlDataAdapter da = new SqlDataAdapter("SELECT LichSuBenh.Id_LichSuBenh,NhanVien.MaNV,NhanVien.TenNV,Khoa.TenKhoa,ChucVu.TenChucVu,LichSuBenh.NgayKham,LichSuBenh.PhanLoaiSK,LichSuBenh.BenhKhac from NhanVien, LichSuBenh, Khoa , ChucVu where NhanVien.IdNhanVien = LichSuBenh.IdNhanVien And NhanVien.Khoa = Khoa.IdKhoa And NhanVien.IdChucVu = ChucVu.IdChucVu", sqlCon); System.Data.DataTable dtMainSQLData = new System.Data.DataTable(); da.Fill(dtMainSQLData); DataColumnCollection dcCollection = dtMainSQLData.Columns; // Export Data into EXCEL Sheet Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 20; //Create a new workbook // Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); //Initialize worksheet // workbook.CreateEmptySheets(1); //Spire.Xls.Worksheet sheet = workbook.Worksheets[0]; //Set sheet name sheet.Name = "Chart data"; //Set the grid lines invisible sheet.GridLinesVisible = false; //Create a chart Spire.Xls.Chart chart = sheet.Charts.Add(ExcelChartType.Pie3D); //Set region of chart data chart.DataRange = sheet.Range["B2:B5"]; chart.SeriesDataFromRange = false; //Set position of chart chart.LeftColumn = 1; chart.TopRow = 6; chart.RightColumn = 9; chart.BottomRow = 25; //Chart title chart.ChartTitle = "Sales by year"; chart.ChartTitleArea.IsBold = true; chart.ChartTitleArea.Size = 12; //Initialize the chart series Spire.Xls.Charts.ChartSerie cs = chart.Series[0]; //Chart Labels resource cs.CategoryLabels = sheet.Range["A2:A5"]; //Chart value resource cs.Values = sheet.Range["B2:B5"]; //Set the value visible in the chart cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true; //Year sheet.Range["A1"].Value = "Year"; sheet.Range["A2"].Value = "2002"; sheet.Range["A3"].Value = "2003"; sheet.Range["A4"].Value = "2004"; sheet.Range["A5"].Value = "2005"; //Sales sheet.Range["B1"].Value = "Sales"; sheet.Range["B2"].NumberValue = 4000; sheet.Range["B3"].NumberValue = 6000; sheet.Range["B4"].NumberValue = 7000; sheet.Range["B5"].NumberValue = 8500; //Style sheet.Range["A1:B1"].Style.Font.IsBold = true; sheet.Range["A2:B2"].Style.KnownColor = ExcelColors.LightYellow; sheet.Range["A3:B3"].Style.KnownColor = ExcelColors.LightGreen1; sheet.Range["A4:B4"].Style.KnownColor = ExcelColors.LightOrange; sheet.Range["A5:B5"].Style.KnownColor = ExcelColors.LightTurquoise; //Border sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin; sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin; sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin; sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin; //Number format sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0"; chart.PlotArea.Fill.Visible = false; //Save the file // workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003); //Launch the file //System.Diagnostics.Process.Start("Sample.xls"); // ExcelApp.Cells.CopyFromRecordset(objRS); for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++) { for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++) { if (i == 1) { ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString(); ExcelApp.Cells[i, j].Font.Bold = true; ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; Microsoft.Office.Interop.Excel.Range formatRange; formatRange = ExcelApp.Cells[i, j]; formatRange.Interior.Color = System.Drawing. ColorTranslator.ToOle(System.Drawing.Color.CornflowerBlue); } else { ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString(); } ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; } } //Save the file workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003); //Launch the file System.Diagnostics.Process.Start("Sample.xls"); ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Oanh\\Desktop\\BaoCaoSK.xls"); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); }