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