Excel报输表出.讲稿.ppt

林渊钟;Excel对象模型 对象基本操作 Application对象 Workbooks对象 Worksheets对象 Range对象 Excel操作方法扩展(VBA宏) 性能优化 FastExport Singleton 套用模板输出Excel 动态输出设计 ;Application;创建 释放 ;打开 关闭;设置当前操作的sheet;获取Range : 单个: (Range)_workSheet.Cells[top, left]; 区域: _workSheet.get_Range(“B3:D5”, Type.Missing); _workSheet.get_Range(_workSheet.Cells[3, 2],_workSheet.Cells[5, 4]); 合并单元格: if(range.Count 1) range.Merge(Missing.Value); 设置单元格值: range.NumberFormatLocal = “@”; //文本格式,“.0_”一位小数 range.Value2 = value; //区别range.Value ;格式化目标单元格: styleRange.Copy(Missing.Value); objectRange.PasteSpecial(XlPasteType.xlPasteFormats, XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false); 序列填充: startRange.Value2 = “1”; //填充第一个单元格序号1 startRange.AutoFill(destination, XlAutoFillType.xlFillSeries); 设置公式: targetRange.Formula = “=SUM(B3:B10)”; targetRange.FormulaR1C1=“=SUM(R[-3]C[-2]:R[-1]C[-2],R[-3]C:R[-1]C)”; ;工具-宏-录制新宏;Cell by Cell;;; public class ExcelOperator { private static ExcelOperator instance; private ApplicationClass _appClass = null; private _Workbook _workBook = null; private string _filePath = ; public string FileName { get { return _filePath; } } private ExcelOperator(string filePath) { _appClass = new ApplicationClass(); _appClass.Visible = false; Open(filePath); //打开workbook 赋值_filePath = filePath } public static ExcelOperator GetInstance(string filePath) { if (instance == null) { instance = new ExcelOperator(filePath); //保证仅实例化一次 } return instance; } } ;;1）固定单元格填充（CellInfo类） 2）动态生成表头（仅动态Excel表） 3）从数据库获取DataTable，快速填充;CelleInfo类构造函数： CellInfo(string content, int top, int left) CellInfo(string content, int top, int left, int finalTop, int finalLeft) 例：如果要合并