Excel导出报表学习

　　报表特别多的业务逻辑，还是做成一个电子政务、文件管理系统比较合适。比如煤矿的储量管理，就针对报表！和OA系统很类似啊！有些侧重信息管理如地质信息，水文信息，当然也有报表，但是相对来说就少的多了！还是做成信息管理系统，用数据库比较好！对于煤矿机电设备设施管理不好说，感觉各有利弊！

今天重新拾起用Excel输出报表的工作。别人封装的来实在是看不懂！

　　基本思路：任何一个报表都可以划分成若干区域，如图的课程表用红色框划分成了7个区域。

　　（似乎用树来组织会不错，如果能有个配置文件，能否不用编码就能实现任何报表的生成？）

　　水晶报表的思想是报表包含子报表。如果类比这种思想报表->子报表->区域，那么区域就是输出报表的最小单位。区域包含一系列相同的特性：边框、对齐方式、颜色、字体。区域的确定通过左上角点Cell和行列数目。运用Excel自带的互操作集，划分的区域可以再次划分，如7区域可以再进一步划分，Range可以支持反复的写入。这里只是初步实现了基本功能，区域包含的属性包括：起始xlsCell、行数、列数、object[,]数据、对齐方式、边框（是一个16进制的整数值）、字体、颜色、单元宽度、单元格行高等属性。

目前的类关系是区域（父类）<--标题类，如果使用多工厂方法模式或者简单工厂模式，似乎可以，把区域作为抽象，不过似乎有没有那个必要。再考虑吧。

区域定义如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel=Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace NtExcel
{
    
    public class xlsRegion
    {
        public const uint Grid_InsideHorizontal = 0x0001;
        public const uint Grid_InsideVertical = 0x0002;
        public const uint Grid_EdgeTop = 0x0004;
        public const uint Grid_EdgeBottom = 0x0008;
        public const uint Grid_EdgeLeft = 0x0010;
        public const uint Grid_EdgeRight = 0x0020;


        public static Excel._Application xlApp;
        public static Excel.Worksheet sheet;

        protected Excel.Range range;
        //起点单元格
        protected xlsCell startCell;
        //行数
        int nRowsCount = 0;
        public int RowsCount
        {
            get{return nRowsCount;}
            set{ nRowsCount=value;}
        }
        //列数
        int nColumnsCount = 0;
        public int ColumnsCount
        {
            get
            {
                return nColumnsCount;
            }
            set { nColumnsCount = value; }
        }
        //合并单元格
        protected bool IsMergeCells;
        public bool MergeCells
        {
            get { return IsMergeCells; }
            set { IsMergeCells = value;}
        }
        //对齐方式
        Excel.Constants align;

        public Excel.Constants Alignment
        {
            get { return align; }
            set { align = value; }
        }
        //边框
        uint gridLine;

        public uint GridLine
        {
            get { return gridLine; }
            set { gridLine = value; }
        }
        //数据
        object[,] objdata;

        public object[,] Objdata
        {
            get { return objdata; }
            set { objdata = value; }
        }
        //构造函数
        public xlsRegion(xlsCell _startCell, int _RowCount, int _ColumnCount)
        {
            startCell = _startCell;
            int _endRowIndex = _startCell.RowIndex + _RowCount - 1;
            int _endColumnIndex = _startCell.ColumnIndex + _ColumnCount - 1;
            xlsCell endCell = new xlsCell(_endRowIndex, _endColumnIndex);//依赖值
            range = sheet.get_Range(xlApp.Cells[_startCell.RowIndex, startCell.ColumnIndex], xlApp.Cells[endCell.RowIndex, endCell.ColumnIndex]);
        }
        public xlsRegion(string strRange)
        {
            //正则表达式验证
            range = sheet.get_Range(strRange, Missing.Value);
        }
        public virtual void SetStyle()
        {
            //range.HorizontalAlignment = align;
            range.MergeCells = IsMergeCells;
            DrawGridLine();
        }
        protected void DrawGridLine()
        {
            if ((gridLine&Grid_InsideHorizontal)==Grid_InsideHorizontal)
            { range.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).Weight = Excel.XlBorderWeight.xlThin;
            }
           if ((gridLine&Grid_InsideVertical)==Grid_InsideVertical)
           {range.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin;
           }
            if ((gridLine&Grid_EdgeTop)==Grid_EdgeTop)
            {range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin;
            }
            if ((gridLine&Grid_EdgeBottom)==Grid_EdgeBottom)
            {range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin;
            }
            if ((Grid_EdgeLeft&gridLine)==Grid_EdgeLeft)
            {range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin;
            }
            if ((Grid_EdgeRight&gridLine)==Grid_EdgeRight)
            {range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin;
            }
            
        }
        public virtual void FillData()
        {
            range.Value2 = objdata;
        }
        
    }

}

public class xlsCell
    {
        int nRowIndex;

        public int RowIndex
        {
            get { return nRowIndex; }
            set { nRowIndex = value; }
        }
        int nColumnIndex;

        public int ColumnIndex
        {
            get { return nColumnIndex; }
            set { nColumnIndex = value; }
        }
        public xlsCell(int _RowIndex, int _ColumnIndex)
        {
            nRowIndex = _RowIndex;
            nColumnIndex = _ColumnIndex;
        }
       public  void MoveTo(int _RowIndex, int _ColumnIndex)
       {
            nRowIndex = _RowIndex;
            nColumnIndex = _ColumnIndex;
       }
    }

为方便起见：从xlsRegion继承了标题类和列头类，图上1和3区域。

 public class xlsTitle: xlsRegion
    {
        public xlsTitle(xlsCell _startCell, int _cols)
           : base(_startCell, 1, _cols)
        {
            base.IsMergeCells = true;
            base.Alignment = Excel.Constants.xlCenter;
            base.GridLine = Grid_EdgeBottom | Grid_EdgeTop|Grid_EdgeLeft|Grid_EdgeRight;
        }
        string title;

        public string Title
        {
            get { return title; }
            set { title = value; }
        }
        public override void FillData()
        {
            range.Value2=title;
        }
        public override void SetStyle()
        {
            range.MergeCells = base.IsMergeCells;
            range.HorizontalAlignment = base.Alignment;
            base.DrawGridLine();
        }
    }

public class xlsColumnHeader:xlsRegion
    {
        public xlsColumnHeader(xlsCell _startCell,int _ColumnsCount )
           : base(_startCell, 1, _ColumnsCount)
        {
            base.Alignment=Excel.Constants.xlCenter;
            base.GridLine=Grid_EdgeBottom|Grid_EdgeTop|Grid_EdgeLeft|Grid_EdgeRight;
        }
        List<string> headers;

        public List<string> Headers
        {
            get { return headers; }
            set { headers = value; }
        }
        public override void FillData()
        {
            int i=0;
            Excel.Range rg;
            foreach (string str in headers)
            {
                rg = xlApp.get_Range(xlApp.Cells[startCell.RowIndex, startCell.ColumnIndex+i], xlApp.Cells[startCell.RowIndex, startCell.ColumnIndex + i]);
                i++;
                rg.Value2 = str;
            }
           
        }
        public override void SetStyle()
        {
            range.MergeCells = base.IsMergeCells;
            range.HorizontalAlignment = base.Alignment;
            base.DrawGridLine();
        }

Excel导出报表的流程一般为：启动Excel应用程序，初始化工作簿，初始化sheet，写入报表，保存文件，关闭工作簿，退出Excel。

因此封装工作流程类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
namespace NtExcel
{
   public class xlsApp
    {
        public  Excel._Application xlApp;
        public  Excel.Worksheet sheet;
        Excel.Workbook book;
        public void InitReport(string _Sheetname,int _SheetIndex=1)
        {
            xlApp = new Excel.ApplicationClass();
            if (xlApp == null)
            {
                throw new ArgumentException("Excel无法启动，可能是您未安装Excel软件.");
            }
            object missing = System.Reflection.Missing.Value;
            xlApp.Workbooks.Add(missing);

            book = xlApp.Workbooks[1];
            sheet = (Excel.Worksheet)book.Sheets[_SheetIndex];
            sheet.Name = _Sheetname;
        }
        public void KillExcelProcess()
        {
            Process[] myProcesses;
            myProcesses = Process.GetProcessesByName("Excel");

            foreach (Process myProcess in myProcesses)
            {
                myProcess.Kill();
            }
        }
        private void ReleaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            }
            catch { }
            finally { obj = null; }
        }
       public void SaveReport(string filename)
        {
            xlApp.DisplayAlerts = false;
            book.SaveCopyAs(filename);
            xlApp.Workbooks.Close();
            xlApp.Quit();
            sheet = null;
            this.ReleaseObject(book);
            this.ReleaseObject(xlApp);
        }
    }
}

位运算的应用：
借鉴Window窗体样式设置的思想用‘|’按位并操作实现样式的叠加。因为区域的边框有六种样式，用户可以设置不同的样式组合。每一种特性用二进制的0和1表示，每位表示一种特性。如A=0001、B=0010、C=0100、D=1000表示四种特性，通用'|'运算获得style，style1=A|B|C=0111，style2=B|C=0110。用'&'获的是否包含此特性，style&A=0001，style2&A=0000。

        public const uint Grid_InsideHorizontal = 0x0001;
        public const uint Grid_InsideVertical = 0x0002;
        public const uint Grid_EdgeTop = 0x0004;
        public const uint Grid_EdgeBottom = 0x0008;
        public const uint Grid_EdgeLeft = 0x0010;
        public const uint Grid_EdgeRight = 0x0020;

类的调用实例如下：

  private void button2_Click(object sender, EventArgs e)
        {
            OracleHelper.connectionString = "Data Source =demo; User Id =peouser; Password =123; Integrated Security = no";
            csRegions reg = new csRegions();
            reg.Fetch();
            object[,] objs = new object[csRegions.list.Count, 1];
            int i=0;
            foreach (string str in csRegions.list)
            {
                objs[i, 0] = str;
                i++;
            }
            string VirFileName = "D:\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

            xlsApp excel = new xlsApp();
            excel.InitReport("很好啊");

            xlsRegion.xlApp = excel.xlApp;
            xlsRegion.sheet = excel.sheet;

            xlsCell cel1 = new xlsCell(1, 1);
            xlsTitle xls_Title = new xlsTitle(cel1, 10);
            xls_Title.Title = "我是中国人！";
            xls_Title.SetStyle();
            xls_Title.FillData();
            
            cel1.MoveTo(2, 1);
            int rowsData = objs.GetLength(0);
            xlsRegion xls_Region = new xlsRegion(cel1, rowsData, 1);
            xls_Region.Objdata = objs;
            xls_Region.FillData();
            xls_Region.SetStyle();

            cel1.MoveTo(2, 2);
            xlsColumnHeader xls_Column = new xlsColumnHeader(cel1,9);
            List<string> listheader = new List<string>();
            listheader.Add("姓名");
            listheader.Add("工种");
            xls_Column.Headers = listheader;
            xls_Column.FillData();
            xls_Column.SetStyle();

            excel.SaveReport(VirFileName);
            GC.Collect();

            excel.KillExcelProcess();
        }