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