//生成统计图方法
DataSet ds=OracleDAL.DbHelperOra.Query("select year||month A1, FESJSSL B1 from feysfxt where depid='"+departmentName.SelectedValue+"' and year='"+ddl4.SelectedValue+"' order by year,month");
foreach (DataRow dr in ds.Tables[0].Rows)
{
x+=dr["A1"].ToString()+'/t';
y+=dr["B1"].ToString()+'/t';
}
OWC10.ChartSpace cs = new OWC10.ChartSpace();
OWC10.ChChart c = cs.Charts.Add(0);
//OWC10.ChSeries s = c.SeriesCollection.Add(0);
//折线图
//折线图的种类还有:chChartTypeLine3D(3D折线统计图)、chChartTypeLineStacked(折线统计图)
c.Type = OWC10.ChartChartTypeEnum.chChartTypeLineMarkers;//折线带点统计图
c.HasLegend=true;//是否需要图例
c.HasTitle=true;
c.Title.Caption="折线图例";
string strSeriesName="铁元素损失率";
c.SeriesCollection.Add(0);
c.Rotation = 360;//表示指定三维图表的旋转角度
c.Inclination = 45;//表示指定三维图表的视图斜率。有效范围为 -90 到 90
添加一个series
c.SeriesCollection[0].SetData(OWC10.ChartDimensionsEnum.chDimSeriesNames,(int)OWC10.ChartSpecialDataSourcesEnum.chDataLiteral.GetHashCode(), strSeriesName);
//给定分类
c.SeriesCollection[0].SetData(OWC10.ChartDimensionsEnum.chDimCategories,(int)OWC10.ChartSpecialDataSourcesEnum.chDataLiteral.GetHashCode(), x);
//给定值
c.SeriesCollection[0].SetData(OWC10.ChartDimensionsEnum.chDimValues,(int)OWC10.ChartSpecialDataSourcesEnum.chDataLiteral.GetHashCode(), y);
//表示系列或趋势线上的单个数据标志
OWC10.ChDataLabels dl=c.SeriesCollection[0].DataLabelsCollection.Add();
dl.HasValue=true;
dl.Font .Name="宋体";
dl.Font.Bold=true;
dl.Font.Color="black";
dl.Font.Size=5;
//格式是年月日时分
string Times=departmentName.SelectedItem+"铁元素平衡分析图表"+ddl4.SelectedValue+ddl5.SelectedValue;
ViewState["name"]=Times;
cs.ExportPicture(Server.MapPath(".") + "//excelFiles//"+ViewState["name"].ToString()+".gif", "GIF", 580, 400);
//生成Excel的一个sheet 里面有报表和统计用的图片的方法
set_imbg();
Application exc = new Application();
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
exc.ScreenUpdating=false;
Excel.Worksheet thisSheet;
thisSheet = (Excel.Worksheet) workbook.Sheets[1] ;
Excel.Range rH = thisSheet.get_Range("A1","D1");
rH.get_Range(thisSheet.Cells[1,1],thisSheet.Cells[1,1]).HorizontalAlignment=3 ;
rH.Merge(0);
rH.Font.Bold=true;
rH.Font.Size=20;
thisSheet.Cells[1,1]=ddl4.SelectedValue+"年"+ddl5.SelectedValue+"月"+"铁元素平衡分析报告";
//第二行
thisSheet.Cells[2,1]="车间名称:"+departmentName.SelectedItem.ToString();;
thisSheet.Cells[2,2]="报告人:";
thisSheet.Cells[2,3]="报告日期:";
thisSheet.Cells[2,4]=DateTime.Now.ToShortDateString();
//第三行
Excel.Range rH1 = thisSheet.get_Range("A3","D3");
rH1.Merge(0);
rH1.Borders.LineStyle=1;
thisSheet.Cells[3,1]="本月铁元素损失情况";
//截止月(本月)的铁损失率情况
string currsql="select * from feysfxt where depid='"+departmentName.SelectedValue+"' and year='"+ddl4.SelectedValue+"' and month='"+ddl5.SelectedValue+"'";
OracleDataReader odr1=OracleDAL.DbHelperOra.ExecuteReader(currsql);
if(odr1.Read())
{
thisSheet.Cells[4,1]="铁元素输入量(吨)";
thisSheet.Cells[4,2]=odr1["FEYLHT"].ToString();
thisSheet.Cells[4,3]="铁元素输出量(吨)";
thisSheet.Cells[4,4]=odr1["FECCHT"].ToString();
thisSheet.Cells[5,1]="铁元素损失量(吨)";
thisSheet.Cells[5,2]=odr1["FESSL"].ToString();
thisSheet.Cells[5,3]="铁元素损失率(%)";
thisSheet.Cells[5,4]=odr1["FESJSSL"].ToString();
thisSheet.Cells[6,1]="铁元素平衡阀值(%)";
thisSheet.Cells[6,2]=odr1["FEJHSSL"].ToString();
thisSheet.Cells[6,3]="铁元素平衡判别结论";
thisSheet.Cells[6,4]=odr1["REASON"].ToString();
}
odr1.Dispose();
//第七行
Excel.Range rH2 = thisSheet.get_Range("A7","D7");
rH2.Merge(0);
rH2.Borders.LineStyle=1;
thisSheet.Cells[7,1]="本年度铁元素损失率最低月份的铁元素损失情况";
//铁损失率情况
string yearsql="select * from feysfxt where depid='"+departmentName.SelectedValue+"' and year='"+ddl4.SelectedValue+"' order by (FESJSSL)";
OracleDataReader odr2=OracleDAL.DbHelperOra.ExecuteReader(yearsql);
if(odr2.Read())
{
thisSheet.Cells[8,1]="铁元素输入量(吨)";
thisSheet.Cells[8,2]=odr2["FEYLHT"].ToString();
thisSheet.Cells[8,3]="铁元素输出量(吨)";
thisSheet.Cells[8,4]=odr2["FECCHT"].ToString();
thisSheet.Cells[9,1]="铁元素损失量(吨)";
thisSheet.Cells[9,2]=odr2["FESSL"].ToString();
thisSheet.Cells[9,3]="铁元素损失率(%)";
thisSheet.Cells[9,4]=odr2["FESJSSL"].ToString();
thisSheet.Cells[10,1]="铁元素平衡阀值(%)";
thisSheet.Cells[10,2]=odr2["FEJHSSL"].ToString();
thisSheet.Cells[10,3]="铁元素平衡判别结论";
thisSheet.Cells[10,4]=odr2["REASON"].ToString();
}
odr2.Dispose();
//第11行
Excel.Range rH3 = thisSheet.get_Range("A11","D11");
rH3.Merge(0);
rH3.Borders.LineStyle=1;
thisSheet.Cells[11,1]="以往铁元素损失率最低月份的铁元素损失情况";
//铁损失率情况
string quansql="select * from feysfxt where depid='"+departmentName.SelectedValue+"' order by (FESJSSL)";
OracleDataReader odr3=OracleDAL.DbHelperOra.ExecuteReader(quansql);
if(odr3.Read())
{
thisSheet.Cells[12,1]="铁元素输入量(吨)";
thisSheet.Cells[12,2]=odr3["FEYLHT"].ToString();
thisSheet.Cells[12,3]="铁元素输出量(吨)";
thisSheet.Cells[12,4]=odr3["FECCHT"].ToString();
thisSheet.Cells[13,1]="铁元素损失量(吨)";
thisSheet.Cells[13,2]=odr3["FESSL"].ToString();
thisSheet.Cells[13,3]="铁元素损失率(%)";
thisSheet.Cells[13,4]=odr3["FESJSSL"].ToString();
thisSheet.Cells[14,1]="铁元素平衡阀值(%)";
thisSheet.Cells[14,2]=odr3["FEJHSSL"].ToString();
thisSheet.Cells[14,3]="铁元素平衡判别结论";
thisSheet.Cells[14,4]=odr3["REASON"].ToString();
}
odr3.Dispose();
Excel.Range allrng;
allrng = thisSheet.get_Range("$A$2:$D$14",Type.Missing);
allrng.ColumnWidth=18;
Excel.Range rH4 = thisSheet.get_Range("A15","D15");
rH4.Merge(0);
rH4.Borders.LineStyle=1;
thisSheet.Cells[15,1]="本年度各月份铁元素损失情况图";
Excel.Pictures v_Pictures = (Excel.Pictures)thisSheet.Pictures(Type.Missing);
//设定显示图片的单元格
Excel.Range rng;
rng = thisSheet.get_Range("$A$16:$D$36",Type.Missing);
rng.Merge(0);
rng.Borders.LineStyle=1;
rng.Activate();
//开始插入图片
v_Pictures.Insert(Server.MapPath(".")+"//excelFiles//"+ViewState["name"].ToString()+".gif",Type.Missing).Select(rng);
//v_Pictures.Height = 250;
v_Pictures.Left = 5;
//v_Pictures.Width = 400;
v_Pictures.Locked = false;
Excel.Range rH5 = thisSheet.get_Range("A37","D37");
rH5.Merge(0);
thisSheet.Cells[37,1]="本月铁元素损失情况综合评价";
Excel.Range rH7 = thisSheet.get_Range(thisSheet.Cells[38,1],thisSheet.Cells[45,4]);
rH7.Merge(0);//表示合并
rH7.Borders.LineStyle=1;
Excel.Range rH6 = thisSheet.get_Range(thisSheet.Cells[1,1],thisSheet.Cells[45,4]);
//rH6.Merge(0);//表示合并
rH6.Borders.LineStyle=1;
rH6.Font.Name="宋体";
exc.ScreenUpdating=true;//ScreenUpdating 属性,设置屏幕是否刷新;
string Times=departmentName.SelectedItem+"铁元素平衡分析月报表"+ddl4.SelectedValue+ddl5.SelectedValue;
thisSheet.SaveAs(Server.MapPath(".")+"//excelFiles//"+Times+".xls",Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
exc.Visible=true;
//页面要引用 using Excel; using System.Reflection;在bin里要把Execl.DLL引入