/// <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 }
private void ChartDraw2(string title_name) { chart = ws2.Charts.Add(); chart.ChartType = ExcelChartType.Line; chart.DataRange = ws2.Range["A1:I7"]; chart.LeftColumn = 12; chart.TopRow = 7; chart.RightColumn = 20; chart.BottomRow = 22; //标题名称 chart.ChartTitle = title_name; chart.ChartTitleArea.IsBold = true; chart.ChartTitleArea.Size = 12; //设置横坐标的标题 chart.PrimaryCategoryAxis.Title = "时间"; chart.PrimaryCategoryAxis.Font.IsBold = true; chart.PrimaryCategoryAxis.TitleArea.IsBold = true; //y chart.PrimaryValueAxis.Title = "数值"; chart.PrimaryValueAxis.HasMajorGridLines = false; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MinValue = 0; chart.PrimaryValueAxis.TitleArea.IsBold = true; //循环绘制 foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series) { cs.Format.Options.IsVaryColor = true; cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true; } Image[] image = wb2.SaveChartAsImage(ws2); image[0].Save(pic_fuyun_path, ImageFormat.Png); }
void save_Excel() { print_chart.save_image(chart1, panel1, table_name.Text); string c = path + "\\" + table_name.Text + "\\" + table_name.Text + ".xls"; try { SqlCommand command = new SqlCommand(); command.CommandText = "select * from " + table_name.Text + ""; using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command.CommandText, Program.cnx)) { DataTable t = new DataTable(); dataAdapter.Fill(t); Workbook book = new Workbook(); Worksheet sheet = book.Worksheets[0]; sheet.InsertDataTable(t, true, 1, 1); Spire.Xls.Chart chart = sheet.Charts.Add(ExcelChartType.Line); //chart.DataRange = sheet.Range["B1:B700"].AddCombinedRange(sheet.Range["C1:C700"]); //.AddCombinedRange(sheet.Range["D2:D200"]).AddCombinedRange(sheet.Range["E2:E200"]).AddCombinedRange(sheet.Range["I2:I200"]).AddCombinedRange(sheet.Range["J2:J200"]); chart.SeriesDataFromRange = false; chart.TopRow = 7; chart.LeftColumn = 3; chart.Width = 43200 / int.Parse(tempo_amostra.Text) * 4000 / 200 + 4000; chart.Height = 500; chart.PlotArea.ForeGroundColor = System.Drawing.Color.White; chart.ChartTitle = "Chart with Data Table"; chart.ChartTitleArea.IsBold = true; chart.ChartTitleArea.Size = 12; var cs1 = (ChartSerie)chart.Series.Add(); cs1.Name = sheet.Range["B1"].Value; cs1.CategoryLabels = sheet.Range["A2:A700"]; cs1.Values = sheet.Range["B2:B700"]; cs1.SerieType = ExcelChartType.Line; var cs2 = (ChartSerie)chart.Series.Add(); cs2.Name = sheet.Range["C1"].Value; cs2.CategoryLabels = sheet.Range["A2:A700"]; cs2.Values = sheet.Range["C2:C700"]; cs2.SerieType = ExcelChartType.Line; var cs3 = (ChartSerie)chart.Series.Add(); cs3.Name = sheet.Range["D1"].Value; cs3.CategoryLabels = sheet.Range["A2:A700"]; cs3.Values = sheet.Range["D2:D700"]; cs3.SerieType = ExcelChartType.Line; var cs4 = (ChartSerie)chart.Series.Add(); cs4.Name = sheet.Range["E1"].Value; cs4.CategoryLabels = sheet.Range["A2:A700"]; cs4.Values = sheet.Range["E2:E700"]; cs4.SerieType = ExcelChartType.Line; var cs5 = (ChartSerie)chart.Series.Add(); cs5.Name = sheet.Range["I1"].Value; cs5.CategoryLabels = sheet.Range["A2:A700"]; cs5.Values = sheet.Range["I2:I700"]; cs5.SerieType = ExcelChartType.Line; var cs6 = (ChartSerie)chart.Series.Add(); cs6.Name = sheet.Range["J1"].Value; cs6.CategoryLabels = sheet.Range["A2:A700"]; cs6.Values = sheet.Range["J2:J700"]; cs6.SerieType = ExcelChartType.Line; book.SaveToFile(c, ExcelVersion.Version97to2003); MessageBox.Show("guardado com sucesso"); //System.Diagnostics.Process.Start(c); } } catch (IOException ex) { LogError(ex); MessageBox.Show("não foi possivel guardar no excel"); } }
public FileResult GetPDF() { #region "GetSessionVariables" string logon_user = (string)Session["logon_user"]; long teamId, repId; int month, year; teamId = (long)Session["IS_Team"]; repId = (long)Session["IS_Rep"]; month = (int)Session["IS_Month"]; year = (int)Session["IS_Year"]; OSC_Team oSC_Team = db.Teams.Find(teamId); OSC_Representative oSC_Representative = db.Representatives.Find(repId); #endregion "GetSessionVariables" #region "WorkbookUsingClosedXML" string templateFileName = Server.MapPath("~/Templates/Template.xlsx"); XLWorkbook workbook = new XLWorkbook(templateFileName); string exportPath = Server.MapPath("~/Export"); string excelFileName = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".xlsx"; string pdfFileName = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".pdf"; string tempFileName = exportPath + "/" + excelFileName; string outputFileName = exportPath + "/" + pdfFileName; if (System.IO.File.Exists(tempFileName)) { System.IO.File.Delete(tempFileName); } workbook.SaveAs(tempFileName); IXLWorksheet worksheet = workbook.Worksheet("Scorecard"); IXLWorksheet worksheet2 = workbook.Worksheet("Workitems"); IXLWorksheet worksheet3 = workbook.Worksheet("NPT"); #region "Table" List <IndividualScorecard> list = new List <IndividualScorecard>(); list = af.GetIndividualScorecardFull(teamId, repId, month, year); int x = 2; int y = 29; IXLCell cell = worksheet.Cell(y, x); cell.SetValue("Attendance"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Overtime"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("NPT Hours"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Processing Time"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Total Transactions"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Rate of Production"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Processing Quality"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Total Utilization"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Efficiency"); for (int i = 1; i <= list.Count; i++) { y += 1; x = 2; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualActivities.Attendance_Days.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualActivities.Overtime_Hours.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualNonProcessing.NPTHours.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualBIProd.ProcessingHours.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualBIProd.Count.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].ProductivityRating.ToString() + "%"); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualBIQual.QualityRating.ToString() + "%"); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].TotalUtilization.ToString() + "%"); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].Efficiency.ToString() + "%"); } do { x += 1; worksheet.Column(x).Clear(); }while (x <= 23); #endregion "Table" #region "Highlights" IndividualScorecard ind = af.GetIndividualScorecard(teamId, repId, month, year); cell = worksheet.Cell("A47"); cell.SetValue(ind.Highlights); #endregion "Highlights" #region "Worktypes" List <IndividualWorkTypes> worktypes = af.GetIndividualWorkTypes(teamId, repId, month, year); y = 1; x = 1; IXLCell cell2 = worksheet2.Cell(y, x); for (int i = 1; i <= worktypes.Count; i++) { y += 1; x = 1; cell2 = worksheet2.Cell(y, x); cell2.SetValue(worktypes[i - 1].WorkType.ToString()); x += 1; cell2 = worksheet2.Cell(y, x); cell2.SetValue(((int)worktypes[i - 1].Count)); } do { y += 1; worksheet2.Row(y).Clear(); }while(y <= 200); #endregion "Worktypes" #region "NPT" List <PieList> npts = af.GetIndividualNPT(teamId, repId, month, year); y = 1; x = 1; IXLCell cell3 = worksheet3.Cell(y, x); for (int i = 1; i <= npts.Count; i++) { y += 1; x = 1; cell3 = worksheet3.Cell(y, x); cell3.SetValue(npts[i - 1].Category.ToString()); x += 1; cell3 = worksheet3.Cell(y, x); cell3.SetValue((double)npts[i - 1].TimeSpent); } do { y += 1; worksheet3.Row(y).Clear(); }while (y <= 200); #endregion "NPT" #region "SaveWorkBook"" workbook.SaveAs(tempFileName); #endregion "SaveWorkBook" #endregion "WorkbookUsingClosedXML" #region "UsingSpireXLS" sxl.Workbook swb = new sxl.Workbook(); swb.LoadFromFile(tempFileName); sxl.Worksheet sws1 = swb.Worksheets[0]; sxl.Worksheet sws2 = swb.Worksheets[1]; sxl.Worksheet sws3 = swb.Worksheets[2]; sws1.TextBoxes[0].Text = oSC_Team.TeamName + " Monthly Scorecard"; sws1.TextBoxes[1].Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName; sws1.TextBoxes[2].Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString(); sxl.Chart chart1 = sws1.Charts[0]; chart1.DataRange = sws2.Range[1, 1, sws2.Rows.Count(), 2]; sxl.Chart chart2 = sws1.Charts[1]; chart2.DataRange = sws3.Range[1, 1, sws3.Rows.Count(), 2]; swb.Save(); #endregion "UsingSpireXLS" #region "WorkbookUsingInterop" //remove this codes when 403 - 454 when closedXML can generate this to PDF xl.Application app = new xl.Application(); xl.Workbook wb = app.Workbooks.Open(tempFileName); #region "Cover" xl.Sheets xs = wb.Worksheets; xl.Worksheet ws = (xl.Worksheet)xs.get_Item("Scorecard"); xl.Worksheet ws2 = (xl.Worksheet)xs.get_Item("Workitems"); xl.Worksheet ws3 = (xl.Worksheet)xs.get_Item("NPT"); int txt_ctr = 1; foreach (xl.Shape shp in ws.Shapes) { if (shp.Type == Microsoft.Office.Core.MsoShapeType.msoTextBox) { if (txt_ctr == 1) { shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Team.TeamName + " Monthly Scorecard"; } else if (txt_ctr == 2) { shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName; shp.ZOrder(MsoZOrderCmd.msoBringToFront); } else { shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString(); shp.ZOrder(MsoZOrderCmd.msoBringToFront); } } txt_ctr += 1; } int cht_ctr = 1; foreach (xl.ChartObject cht in ws.ChartObjects()) { if (cht_ctr == 1) { xl.Range last = ws2.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing); xl.Range chartRange = ws2.get_Range("A1", last); cht.Chart.SetSourceData(chartRange); } else { xl.Range last = ws3.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing); xl.Range chartRange = ws3.get_Range("A1", last); cht.Chart.SetSourceData(chartRange); } cht_ctr += 1; } #endregion "Cover" wb.Save(); wb.ExportAsFixedFormat(xl.XlFixedFormatType.xlTypePDF, outputFileName, xl.XlFixedFormatQuality.xlQualityStandard); wb.Close(); app.Quit(); #endregion "WorkbookUsingInterop" #region "Return" byte[] fileBytes = System.IO.File.ReadAllBytes(outputFileName); string fileName = pdfFileName; return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName)); #endregion "Return" }
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(); }