【C#】15. ExcelDriver

来源：互联网发布：农村淘宝推广招聘信息编辑：程序博客网时间：2024/06/16 01:07
using System;using System.Collections.Generic;using System.Linq;using System.Text;using Excel=Microsoft.Office.Interop.Excel;namespace UserDefinedDataEXP{    class ExcelDriver    {        private Excel.Application pExcel;        private long curDataColumn;        //构造器：pExcel创建，当前列为1        public ExcelDriver()        {            if (pExcel == null)            {                pExcel = new Excel.Application();            }            curDataColumn = 1;        }        //是否显示Excel？        public void MakeVisible(bool visible)         {            this.pExcel.Visible=visible;        }        // Reflection        static object SetPropertyInternational(object target, string name,  params object[] args)        {            return target.GetType().InvokeMember(name,                System.Reflection.BindingFlags.SetProperty |                System.Reflection.BindingFlags.Public |                System.Reflection.BindingFlags.Instance,                null, target, args, new System.Globalization.CultureInfo(1033));        }        static object GetPropertyInternational(object target, string name, params object[] args)        {            return target.GetType().InvokeMember(name,                System.Reflection.BindingFlags.GetProperty |                System.Reflection.BindingFlags.Public |                System.Reflection.BindingFlags.Instance,                null, target, args, new System.Globalization.CultureInfo(1033));        }        static object InvokeMethodInternational(object target, string name, params object[] args)        {            return target.GetType().InvokeMember(name,                System.Reflection.BindingFlags.InvokeMethod |                System.Reflection.BindingFlags.Public |                System.Reflection.BindingFlags.Instance,                null, target, args, new System.Globalization.CultureInfo(1033));        }        //自定义打印AssocMatrix函数        void ToSheetAssocMatrix<R, C, T>(Excel.Worksheet pWorksheet, long sheetRow, long sheetColumn, AssocMatrix<R, C, T> mat)        {            //a.    单元格引用            Excel.Range item = (Excel.Range)pWorksheet.Cells[sheetRow, sheetColumn];            //b.    打印行标题            for (int i = mat.numMatMinRowIndex; i <=mat.numMatMaxRowIndex; i++)            {                item = (Excel.Range)pWorksheet.Cells[sheetRow+i, sheetColumn];                SetPropertyInternational(item, "Value2",mat.getRowKey(i).ToString());            }            //c.    打印列标题            for (int i = mat.numMatMinColumnIndex; i <=mat.numMatMaxColumnIndex; i++)            {                item = (Excel.Range)pWorksheet.Cells[sheetRow, sheetColumn+i];                SetPropertyInternational(item, "Value2",mat.getColumnKey(i).ToString());            }            //d.    打印numMatrix值            for (int i = mat.numMatMinRowIndex; i <=mat.numMatMaxRowIndex; i++){  for (int j = mat.numMatMinColumnIndex; j <=mat.numMatMaxColumnIndex; j++)                {                    item = (Excel.Range)pWorksheet.Cells[sheetRow+i, sheetColumn+j];                    SetPropertyInternational(item, "Value2", mat[i,j]);                }}        }        //  R是RowsKeys的类型，C是ColumnsKeys的类型，T是数据类型        public void AddAssocMatrix<R, C, T>(AssocMatrix<R, C, T> matrix, string SheetName)        {            try            {            // Add sheet.            Excel.Workbook pWorkbook;            Excel.Worksheet pSheet;            if (pExcel.ActiveWorkbook == null)            {            pWorkbook = (Excel.Workbook)InvokeMethodInternational(pExcel.Workbooks, "Add", Excel.XlWBATemplate.xlWBATWorksheet);            pSheet = (Excel.Worksheet)pWorkbook.ActiveSheet;            }            else            {            pWorkbook = pExcel.ActiveWorkbook;            pSheet = (Excel.Worksheet)InvokeMethodInternational(pWorkbook.Worksheets,"Add", Type.Missing, Type.Missing, 1, Type.Missing);            }            pSheet.Name = SheetName;            // Add row labels + values.            int sheetColumn = 1;            int sheetRow = 1;            //  打印数据            ToSheetAssocMatrix<R, C, T>(pSheet, sheetRow, sheetColumn, matrix);            }            catch (IndexOutOfRangeException e)            {            Console.WriteLine("Exception: " + e);            }                               }        //  AddLattice  【更新】2015/3/7          public void AddLattice(string name, Lattice<double> lattice, List<string> rowLabels)        {            try                {                    // Add sheet.                    Excel.Workbook pWorkbook;                    Excel.Worksheet pSheet;                    if (pExcel.ActiveWorkbook == null)                    {                        pWorkbook =(Excel.Workbook)InvokeMethodInternational(pExcel.Workbooks,"Add", Excel.XlWBATemplate.xlWBATWorksheet);                        pSheet = (Excel.Worksheet)pWorkbook.ActiveSheet;                    }                    else                    {                         pWorkbook = pExcel.ActiveWorkbook;                         pSheet =(Excel.Worksheet)InvokeMethodInternational(pWorkbook.Worksheets, "Add", Type.Missing,Type.Missing, 1, Type.Missing);                    }                    pSheet.Name = name;                    // Add row labels + values.                    int sheetColumn = 1;                    int sheetRow = 1;                    for (int i = lattice.MinIndex; i <= lattice.MaxIndex; i++)                    {                        Vector<double> row = lattice.PyramidVector(i);                        ToSheetHorizontal<double>(pSheet, sheetRow, sheetColumn,rowLabels[i], row);                        sheetRow++;                    }                }            catch (IndexOutOfRangeException e)            {                Console.WriteLine("Exception: " + e);            }        }        // ToSheetHorizontal打印函数  【更新】2015/3/7           public void ToSheetHorizontal<T>(Excel.Worksheet pWorksheet, long sheetRow, long sheetColumn, string label, Vector<T> values)        {            // First cell contains the label.            Excel.Range item = (Excel.Range)pWorksheet.Cells[sheetRow, sheetColumn];            SetPropertyInternational(item, "Value2", label);            sheetColumn++;            // Next cells contain the values.            for (int i = values.MinIndex; i <= values.MaxIndex; i++)            {                item = (Excel.Range)pWorksheet.Cells[sheetRow, sheetColumn];                SetPropertyInternational(item, "Value2", values[i]);                sheetColumn++;            }        }        // ToSheetVertical打印函数  【更新】2015/3/10        public void ToSheetVertical<T>(Excel.Worksheet pWorksheet, long sheetRow, long sheetColumn, string label, Vector<T> values)        {            // First cell contains the label.            Excel.Range item = (Excel.Range)pWorksheet.Cells[sheetRow, sheetColumn];            SetPropertyInternational(item, "Value2", label);            sheetRow++;            // Next cells contain the values.            for (int i = values.MinIndex; i <= values.MaxIndex; i++)            {                item = (Excel.Range)pWorksheet.Cells[sheetRow, sheetColumn];                SetPropertyInternational(item, "Value2", values[i]);                sheetRow++;            }        }        // Excel作图  【更新】2015/3/10         public void CreateChart<T>(Vector<T> x, Vector<T> y, string title, string horizontal, string vertical, string legend)        {            try            {                // Add sheet.                Excel.Workbook pWorkbook;                Excel.Worksheet pSheet;                if (pExcel.ActiveWorkbook == null)                {                    pWorkbook = (Excel.Workbook)InvokeMethodInternational(pExcel.Workbooks, "Add", Excel.XlWBATemplate.xlWBATWorksheet);                    pSheet = (Excel.Worksheet)pWorkbook.ActiveSheet;                }                else                {                    pWorkbook = pExcel.ActiveWorkbook;                    pSheet = (Excel.Worksheet)InvokeMethodInternational(pWorkbook.Worksheets, "Add", Type.Missing, Type.Missing, 1, Type.Missing);                }                // Add row labels + values.                int sheetColumn = 1;                int sheetRow = 1;                //打印X列                ToSheetVertical(pSheet, sheetRow, sheetColumn, horizontal, x);                //打印Y列                ToSheetVertical(pSheet, sheetRow, sheetColumn + 1, vertical, y);                // 在工作簿中增加图表Chart（XY点图）                Excel.Chart chart = pWorkbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Excel.Chart;                Excel.Range range = (Excel.Range)pSheet.get_Range("A2","B" + (x.Length+1).ToString() );                chart.ChartWizard(range, Excel.XlChartType.xlXYScatterLinesNoMarkers, 1, Excel.XlRowCol.xlColumns, 1, 0, true, title, horizontal, vertical, Type.Missing);                chart.SeriesCollection(1).Name = legend;            }            catch (Exception e)            {                Console.WriteLine("Exception: " + e);            }                }    }}
0 0