导出报表到excel（二）

using System;
using Excel;

namespace RFID.PublicClass.ExcelOut
{
 /// <summary>
 /// ExcelOut 的摘要说明。
 /// </summary>
 public class ExcelOut
 {
  private static Excel.Application excel=null;
  private static Excel._Workbook xBk=null;
  private static Excel._Worksheet xSt=null;
  private static Excel._QueryTable xQt=null;
  private static string Select=null;
        private static string Conn = "ODBC;DRIVER=SQL Server;SERVER=202.200.119.111;UID=sa;PWD=sa;APP=RFID;DATABASE=RFID";
  private static int StartCol=4;//报表标题开始的列位置
  private static int StartRow=2;//报表数据区域开始的行位置,即字段开始的行位置.
  public ExcelOut(string SQL)
  {  
   try
   {    
     Select = SQL;
     excel=new Excel.ApplicationClass();;
     xBk=excel.Workbooks.Add(true);
     xSt=(Excel._Worksheet)xBk.ActiveSheet;
    //定义位置
     xQt = xSt.QueryTables.Add(Conn,xSt.get_Range(excel.Cells[StartRow,StartCol],excel.Cells[StartRow,StartCol]),Select); 
   }
   catch
   {   CloseExcelForm();
   }
    
   

  }

  public static void CreatExcel(string Topic,int TopicIndex, int RowIndex,int ColIndex)
  { 
   try
   {
    CreateForm(Topic,TopicIndex,RowIndex,ColIndex);
    xQt.Name = "导出示例";
    xQt.FieldNames = true;
    xQt.RowNumbers = false;
    xQt.FillAdjacentFormulas = false;
    xQt.PreserveFormatting = false;
    xQt.BackgroundQuery = true;
    xQt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells;
    xQt.AdjustColumnWidth = true;
    xQt.RefreshPeriod = 0;
    xQt.PreserveColumnInfo = true;
    xQt.Refresh(xQt.BackgroundQuery);
    excel.Visible = true;
   }
   catch
   {
   }   

  }

  public static void CreateForm(string Topic,int Index,int RowIndex,int ColIndex)
  { 
    //设置标题格式
     excel.Cells[1,Index+StartCol-1]=Topic;    
     xSt.get_Range(excel.Cells[1,Index+StartCol],excel.Cells[1,Index+StartCol-1]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
     xSt.get_Range(excel.Cells[1,Index+StartCol],excel.Cells[1,Index+StartCol-1]).HorizontalAlignment=Excel.XlHAlign.xlHAlignCenterAcrossSelection;
     xSt.get_Range(excel.Cells[1,Index+StartCol],excel.Cells[1,Index+StartCol-1]).Font.Bold=true;
           xSt.get_Range(excel.Cells[1,Index+StartCol],excel.Cells[1,Index+StartCol-1]).Font.Size=15;
     xSt.get_Range(excel.Cells[1,StartCol],excel.Cells[1,ColIndex+StartCol-1]).Select();
     xSt.get_Range(excel.Cells[1,StartCol],excel.Cells[1,ColIndex+StartCol-1]).Interior.ColorIndex=19;
     
           //字段列居中对齐
      xSt.get_Range(excel.Cells[StartRow,StartCol],excel.Cells[StartRow,ColIndex+StartCol]).HorizontalAlignment=Excel.XlVAlign.xlVAlignCenter;

          

           //加边框
   xSt.get_Range(excel.Cells[StartRow,StartCol],excel.Cells[RowIndex+StartRow,ColIndex+StartCol-1]).Borders.LineStyle = 1;
   
  }

  public static void  CloseExcelForm()
  {
   try
   {
    xBk = null;
    xSt = null;
    xQt = null;
    if(excel!= null)
    {
     excel.Quit();
     excel = null;
    }
   }
   finally
   {
    GC.Collect();
    GC.WaitForPendingFinalizers();
   }
  }
  
  
 }
}