【C#】15. ExcelDriver
来源:互联网 发布:农村淘宝推广招聘信息 编辑:程序博客网 时间:2024/06/16 04:55
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
- 【C#】15. ExcelDriver
- 【c#】15. ExcelMechanisms
- <C++>15.this指针
- c
- c
- c
- c
- C
- c
- c
- c
- C+
- c
- C
- c
- c
- c
- C
- 四种常见的基于比较的排序算法C++
- HTMl基础
- 【C#】14. printOneExcel在Excel里作图 & 利率插值计算(线性)
- Java进程与线程
- [Leetcode]Merge Intervals
- 【C#】15. ExcelDriver
- 【c#】15. ExcelMechanisms
- javascript读书笔记3----实现公有和私有成员
- FDM之二维静态热传导--含有不同传导系数K
- #(Linux)Ubuntu使用# bash shell 无法执行cd
- LeetCode 136 Single Number
- LeetCode 104 Maximum Depth of Binary Tree
- LeetCode 100 Same Tree
- LeetCode 191 Number of 1 Bits