【转】C# Excel 图表操作(二)--实例

地址：http://www.6ideas.cn/ArtF/58110871425.html

C# Excel 图表操作实例,生成Excel统计报表

System.DateTime StartTime=System.DateTime.Now;
   string filename=Server.MapPath("tmp/sales.xls");
   m_objExcel=new Excel.Application();
   m_objExcel.UserControl=false;
   m_objExcel.DisplayAlerts=false;
   m_objExcel.Visible = false;
  
   m_objExcel.Workbooks.Open( filename,miss,miss,miss,miss,miss,miss,miss,
    miss,miss,miss,miss,miss,miss,miss);

   m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

   m_objBook = m_objExcel.ActiveWorkbook;
   sheet = (Excel.Worksheet)m_objBook.ActiveSheet;

   //Weeks
            System.Data.DataTable  dts=BLL.OrderHeaderBLL.SalesReportByWeek();
  
   int custcount=1;
   DataRow[] drs=dts.Select("CustID='Allumette'");
   for(int i=0;i<drs.Length;i++){
    sheet.Cells[3,2+i]="week "+drs[i]["Weeks"].ToString();
   }

   for(int i=0;i<dts.Rows.Count;i++)
   {
    if(Convert.ToInt32(dts.Rows[i]["weeks"])!=1)
     break;
    sheet.Cells[4+i,1]=dts.Rows[i]["CustID"].ToString();
    sheet.Cells[19+i,24]=dts.Rows[i]["CustID"].ToString();
    custcount+=1;
   }
   int week=1;
   int startrow=3;
   double total=0.0;
   
   for(int i=0;i<dts.Rows.Count;i++)
   {
    if(Convert.ToInt32(dts.Rows[i]["weeks"])!=week)
    {
     sheet.Cells[startrow+1,1+week]=total;
     total=0;
     week=week+1;
     startrow=4;
    }
    else
     startrow+=1;

    total+=Convert.ToDouble(dts.Rows[i]["Price"]);
    sheet.Cells[startrow,1+week]=dts.Rows[i]["Price"];
    
   }
   //Last
   sheet.Cells[startrow+1,1+week]=total;
   sheet.Cells[4+custcount,1]="Total";
   week+=1;
  // B4:AI4
   //Stat.
 
        
   sheet.Cells[3,week+1]="Total";
   for(int i=0;i<custcount;i++)
   {
    Range rge=(Range)sheet.Cells[4+i,week+1];
    //新加的单元格的颜色采用，之前的单元格的颜色
    sheet.get_Range("B"+(4+i),GetColflg(week+1)+(4+i)).Interior.Color=((Range)sheet.Cells[4+i,2]).Interior.Color;
    
    rge.Formula="=SUM(B"+(4+i)+":"+GetColflg(week)+(4+i)+")";
   }

   //获取图表对象
   ChartObject chartObj=(ChartObject)sheet.ChartObjects(1);
   Chart xlChart = chartObj.Chart;
   xlChart.ChartType=Excel.XlChartType.xlLine;
   //设置数据源
   Range chartRange = sheet.get_Range("A4", GetColflg(week)+startrow.ToString());
   

   xlChart.SetSourceData(chartRange,miss);

 

 

   //Months
    System.Data.DataTable  dtMon=BLL.OrderHeaderBLL.SalesReportByMonth();
   int month=1;
    startrow=18;
    total=0.0;
   for(int i=0;i<dtMon.Rows.Count;i++)
   {
    if(Convert.ToInt32(dtMon.Rows[i]["Months"])!=month)
    {
     sheet.Cells[startrow+1,24+month]=total;
     total=0;
     month=month+1;
     startrow=19;
    }
    else
     startrow+=1;
       total+=Convert.ToDouble(dtMon.Rows[i]["Price"]);
    sheet.Cells[startrow,24+month]=dtMon.Rows[i]["Price"];
   }

            sheet.Cells[startrow+1,1+week]=total;
   sheet.Cells[19+custcount,24]="Total";
   sheet.Cells[18,37]="Total";//AK
   for(int i=0;i<custcount;i++)
   {
    Range rge1=(Range)sheet.Cells[19+i,37];

    
    rge1.Formula="=SUM(Y"+(19+i)+":AJ"+(19+i)+")";
   }

 

   
   ChartObject chartMon=(ChartObject)sheet.ChartObjects(2);
   Chart MonChart = chartMon.Chart;
    MonChart.ChartType=Excel.XlChartType.xlLine;
   Range MonchartRange = sheet.get_Range("X19", "AJ"+startrow.ToString());
   Excel.ChartGroup grp= (ChartGroup)MonChart.ChartGroups(1);
   
   grp.VaryByCategories=true;
   MonChart.SetSourceData(MonchartRange,miss);
  
  

           string filepath=Server.MapPath("tmp/"+System.DateTime.Now.ToFileTime()+".xls");
   m_objBook.SaveAs(filepath, miss, miss,
    miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange,miss,
    miss, miss, miss, miss);

   m_objBook.Close(false, miss, miss);
   m_objExcel.Quit();
   System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel);
   System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks);
   System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook);
   System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);

   foreach(System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
   {
    if( theProc.CloseMainWindow() == false )
    {
    // if(theProc.StartTime>StartTime)
     theProc.Kill();
    }
   }