Excel8属性及方法使用.doc

未完成工作错误列表: 1、Style.DiagLineTopRightToBottomLeft Long 整型赋值错误一、 Class GMU 创建的全局成员 公共方法CreateExcelCellStyle创建单元格样式CreateExcelFont创建字体CreateExcelFooter创建打印页脚CreateExcelHeader创建打印页眉CreateExcelFormat创建 格式字符串CreateExcelPrintSetup创建打印设置对象CreateExcelWorkbook创建Excel 表Function CreateExcelCellStyle() As ExcelCellStyle Function CreateExcelFont() As ExcelFont Function CreateExcelFooter() As ExcelFooter Function CreateExcelFormat() As ExcelFormat Function CreateExcelHeader() As ExcelHeader Function CreateExcelPrintSetup() As ExcelPrintSetup Function CreateExcelWorkbook() As ExcelWorkbook 二、类ExcelWorkbook Excel表处理类公共属性password设置Excel保护密码Getsheet(index)返回Excel页对象ExcelSheetOpenExcelInstance打开对应的Excel 进程（如office中Excel.exe）Helper工具类，帮助行列的转换Property password As String 设置Excel保护密码wbook.password = “123456”Property GetSheet(sheet) As ExcelWorksheet 获取页面Dim wsheet As ExcelWorksheetSet wsheet =wbook.GetSheet(“sheet1”)Set wsheet =wbook.GetSheet(0)Property OpenExcelInstance As Booleanwbook.OpenExcelInstance = TrueProperty helper As ExcelHelper 只读工具类，帮助行列的转换wbook.helper.ConvertColRowToAreaRef(2, 2, 5, 5)wbook.helper.ConvertColRowToRef(2, 2, 2)公共方法CreateSheet创建sheet页面Save保存Excel 文件SetActiveSheet(sheet)设置Excel Sheet的激活页面SetDefaultFont设置Excel 中默认字体,字号Function CreateSheet(sSheetName As String, bVisible As Boolean = True) As ExcelWorksheet 创建sheet页面sSheetName: 页签名bVisible : 是否显示本页Call wbook.CreateSheet(firstsheet)Set wsheet = wbook.CreateSheet(Sheet5)Sub Save(fname As String) 保存Excel 文件名 Fname: excel 文件名call Wbook.save(“D:excelfiletest.xls”)Sub SetDefaultFont(Name As String, Size As Long)Name : 字体名称Size ：字号wbook.SetDefaultFont 黑体, 12 Sub SetActiveSheet(sheet) Sheet : 页面名称或者页面序号三、ExcelWorksheet Excel 页公共属性Footer设置页脚GridlineColour表格线的颜色Gridlines是否显示表格线Header设置页眉PrintSetup打印设置ScaleFactor打印比率Selected设置选中页面sheetHeaders是否显示页顶部的列标签（如：A B C D E）wsheet.SheetHeaders = TrueshowFormalas是否显示公式wsheet.ShowFormulas = TrueShowZeroValues是否显示零值Visible是否可见Wsheet.visible = trueProperty Footer As ExcelFooter Dim oFooter As ExcelFooter Set oFooter = CreateExcelFooter() Call oFooter.StartLeftSection oFooter.AddText 左边Property GridlineColour As enumColours 页表格线的颜色wsheet.GridlineColour = cBlueProperty GridLines As Boolean 是否显示表格线wsheet.GridLines = FalseProperty Header As ExcelHeader 页眉 Dim oheader as excelheader Set oheader = create excelheader() Call oheader.startleftsection oheader.addtext ”左边”Property PrintSetup As ExcelPrintSetup 打印设置 Dim pSetup As ExcelPrintSetup Set pSetup = CreateExcelPrintSetup() pSetup.CenterHorizontally = True Set wsheet.PrintSetup = pSetupWith wsheet.PrintSetup .CenterHorizontally = True .HeaderMargin = 0.5 .TopMargin = 1.5 .FitPagesWide = 1 .FitPagesHigh = 1 .BottomMargin = 1.5 .FooterMargin = 0.5 .Orientation = oLandscape End WithProperty ScaleFactor As Integer 打印缩放比率wsheet.ScaleFactor = 95 打印原稿的 95%Property Selected As Boolean 设置选中页面 wsheet.Selected = False公共方法AddBorder加边框线AddCell单个单元格插入数据AddCells增加单元格AddTable插入表格AverageRows求平均值SumRows同上CountRows行数DeleteCells删除单元格GetCell单元格返回GetRow单元格一行返回MergeCells合并单元格SetColumnStyle设置列格式(样式)SetStyle设置区域样式SumRows设置求和Public Sub AddBorder(columnFrom As Integer, rowFrom As Integer, columnTo As Integer, rowTo As Integer, borderStyle As enumLineStyle, Optional BorderColour As enumColours = cSystemBorder)Add a border around a block of cellswsheet.AddBorder 1, 1, 3, 3, xfbtDoubleFunction AddCell(columnNumber As Integer, rowNumber As Integer, Optional vValue As Variant, Optional oCellStyle As ExcelCellStyle) As ExcelCellwsheet.AddCell 1, 1, aa, style Public Sub AddCells(columnFrom , columnTo, rowNumber, oCellStyle As ExcelCellStyle, ParamArray vValues() As Variant) Add a row of cells between the given columnswsheet.AddCells 1, 10, 10, style, aaaaaaPublic Sub AddTable(columnNumber, rowNumber, Optional oCellStyle As ExcelCellStyle, Optional vArray As Variant) Add a table of values from a 2 dimensional arrayDim v(1, 1) As String v(0, 0) = 第一行，第一列 v(0, 1) = 第一行，第二列 v(1, 0) = 第二行，第一列 v(1, 1) = 第二行，第二列Dim ww As Variant ww = vwsheet.AddTable 3, 3, style, wwPublic Sub SumRows(columnFrom, columnTo, sumFromRow, sumToRow, rowNumber, Optional oCellStyle As ExcelCellStyle) Creates a row of Sum formulas, 1 for each column in the range示例: wsheet.AverageRows 3, 4, 3, 4, 5效果等同于：C5=AVERAGE(C3:C4) D5=AVERAGE(D3:D4)Public Sub DeleteCells(firstColumn, firstRow, lastColumn, lastRow) wsheet.DeleteCells 3, 3, 4, 4说明：3，3，4，4表示单元格的区域（C3：D4）Public Function GetCell(columnNumber, rowNumber) As ExcelCell returns a cell object at the specified column/row, creates one if it does not existDim cell As ExcelCellSet cell = wsheet.GetCell(3, 3)cell.Value = 100cell.style.Font.Colour = cRedPublic Function GetRow(ByVal rowNumber As Integer) As ExcelRowGets a row object for the specified row create one if it doesnt existDim row As ExcelRowSet row = wsheet.GetRow(4)row.Height = 100row.style.Font.Colour = cBlueMergeCells 合并单元格Sub MergeCells(firstColumn As Integer, firstRow As Integer, lastColumn As Integer, lastRow As Integer)firstColumn: 合并单元格的起始单元格的列firRow : 起始单元格的行lastColumn : 合并单元格的结束单元格的列lastRow : 结束单元格行示例： 合并区域 C3:E5 为一个单元格wsheet.MergeCells 3, 3, 5, 5SetColumnStyle 设置列格式:Sub SetColumnStyle (iFromColumn As Integer, iToColumn As Integer, iWidth As Integer = 64, oStyle As ExcelCellStyle, Hidden As Boolean) iFromColumn: 设置起始列 iToColumn : 设置结束列 iWidth : 列宽度 oStyle : 单元样式(ExcelCellStyle) Hidden : 是否隐藏列 示例：style.Font.Colour = cBluewsheet.SetColumnStyle 2, 5, 100, styleSetStyle 区域设置样式Sub SetStyle(columnFrom As Integer, rowFrom As Integer, columnTo As Integer, rowTo As Integer, oCellStyle As ExcelCellStyle) columnFrom 需设置样式的起始列 rowFrom 起始行 columnTo 需设置样式的结束列 rowTo 结束行 oCellStyle : 单元样式(ExcelCellStyle)示例： 设置B4， D10 的颜色 Style.Font.Colour = cRed Wsheet.SetStyle 2,4,4,10,StyleSumRows 区域列求和Sub SumRows(columnFrom As Integer, columnTo As Integer, sumFrom As Integer, sumTo As Integer, rowNumber As Integer, oCellStyle As ExcelCellStyle)columnFrom 需求和的起始列columnTo 需求和的结束列sumFrom 求和的起始行sumTo 求和的结束行rowNumber 所求和做放的位置行四、ExcelPrintSetupBottomMargin下边距CenterHorizontally居中方式 水平居中CenterVertically居中方式 垂直居中Copies打印页数 FitPagesHigh页面合适高度FitPagesWide页面合适宽度FooterMargin页脚设置HeaderMargin页眉设置LeftMargin左边距Orientation打印方向PaperSize纸张大小PrintBlackAndWhite是否黑白打印PrintDraftQuality打印草稿PrintGridLines是否显示表格线 PrintHeaders是否打印页头RightMargin右边距ScaleFactor缩放比例StartPage开始打印页TopMargin上边距BottomMargin 下边距 Property BottomMargin As DoubleDim psetup As ExcelPrintSetuppsetup.BottomMargin = 4 LeftMargin Property LeftMargin As Doublepsetup.leftMargin = 4 RightMargin Property RinghtMargin As Doublepsetup.LightMargin = 4TopMargin Property TopMargin As Doublepsetup.TopMargin = 4CenterHorizontally 水平居中 Property CenterHorizontally As Boolean psetup.CenterHorizontally = True CenterVertically 垂直居中 PropertyCenterVertically As Boolean Psetup.CenterVertically = True Copies 打印数量 Property Copies As Integerpsetup.Copies = 3FitPagesHigh Property FitPagesHigh As Integerpsetup.FitPagesHigh = 3FitPagesWide Property FitPagesWide As Integerpsetup.FitPagesWide= 3PrintGridLines PropertyCenterVertically As Booleanpsetup.PrintGridLines = TrueFooterMargin Property FooterMargin As Double psetup.FooterMargin = 2HeaderMargin Property HeaderMargin As Double psetup.HeaderMargin = 2Orientation 打印方向 Property Orientation As enumPaperOrientationpsetup.Orientation = oLandscape / oPortrait 横向/纵向PaperSize 纸张大小 Property PaperSize As enumPaperSizepsetup.PaperSize = psA3 PrintBlackAndWhite 是否黑白打印 Property PrintBlackAndWhite As Booleanpsetup.PrintBlackAndWhite = TruePrintDraftQuality 打印草稿 Property PrintDraftQuality As BooleanPsetup.PrintDraftQuality=truePrintGridLines 是否打印表格线 Property PrintGridLines As Booleanpsetup.PrintGridLines = TruePrintHeaders是否打印页 Property PrintHeaders As Booleanpsetup.PrintHeaders = TrueScaleFactor 缩放比例 Property ScaleFactor As Integer psetup.ScaleFactor = 95StartPage 打印启始页 Property StartPage As BooleanPsetup.StartPage = true五、ExcelHeader 公共方法AddCurrentDate日期AddCurrentTime时间AddFileNameNoPath页眉名AddPageCount页数AddPageNumber页号AddPathNoName文件目录AddSheetName页面名AddText写入字符SetBoldOnOff输入字符SetDoubleUnderlineOnOff 为字体之一SetFont设置字体SetFontSize设置字体大小SetItalicOnOff斜体SetStrikeOutOnOff划线字体SetSubScriptOutOnOff为字体之一SetSuperScriptOutOnOff为字体之一SetUnderlineOnOff为字体之一StartCenterSection写到中间StartLeftSection写到左边StartRightSection写到右边AddCurrentDate 当前日期Dim header As ExcelHeader Set header = CreateExcelHeader() Call header.AddCurrentDate header.AddText Set wsheet.header = headerAddCurrentTime Call header.AddCurrentTimeAddFileNameNoPath 文件名,不带路径 Call header.AddCurrentTimeAddPageCount 总页数 Call header.AddPageCountAddPageNumber 当前页码号 Call header.AddPageNumberAddPathNoName 文件目录 Call header.AddPathNoNameAddSheetName 页面名称(SheetNane) Call header.AddSheetNameAddText 写入文本 Sub AddText(sText As String) Call header.Addtext (“text”)SetBoldOnOff 插入字符 header.SetBoldOnOff header.AddText ddddSet wsheet.header = headerSetDoubleUnderlineOnOff 为字体之一，双划线Call header.SetDoubleUnderlineOnOff header.AddText 力量SetFont 设置字体Sub SetFont(fontName As String, fontStyle As enumFontStyle)Call header.SetFont fnt header.AddText 力量SetFontSize 设置字体大小Sub SetFontSize(fontSize As Integer)Call header.SetFontSize (10) header.AddText 力量SetItalicOnOff 斜体Dim header As ExcelHeader call header.SetItalicOnOff header.AddText 力量SetStrikeOutOnOff 同上格式， 为字体之一SetSubScriptOutOnOff 同上格式 为字体之一SetSuperScriptOutOnOff 同上格式 为字体之一SetUnderlineOnOff 同上格式 为字体之一StartCenterSection 开始中间部分 Set header = CreateExcelHeader()Call header.StartCenterSection header.AddText 力量StartLeftSection 同上格式，字体显示在左边StartRightSection 同上格式，字体显示在右边六、ExcelFooter 的公共方法与 ExcelHeader 全部相同七、ExcelFontBold粗体CharacterSet字符集Colour字体颜色Escapement扁字体FamilyItalic斜体Name字体名称Size字体大小StruckoutUnderline下划线Bold 粗体 Dim fnt As ExcelfontSet fnt = CreateExcelfont Fnt.Bold = true CharacterSet 字符集 Property CharacterSet As enumFontCharacterSetSet fnt = CreateExcelfontfnt = csANSI_Chinese_TraditionalEscapement 扁字体 Property Escapement As enumFontEscapementSet fnt = CreateExcelFontSet cell = wsheet.GetCell(4, 4)cell.Value = 500cell.style.font.Escapement = escSuperScriptStruckout 是否有边框Property Bold As BooleanSet fnt = CreateExcelFontSet cell = wsheet.GetCell(4, 4)cell.Value = 500cell.style.font.Underline = uDoubleAccountingcell.style.font.Struckout = TrueItalic 斜体Property Italic As Booleanset font = CreateExcelFontSet cell = wsheet.GetCell(4, 4)cell.Value = 500cell.style.font.Italic = trueName 字体名称Property Name As StringSet font = CreateExcelFontSet cell = wsheet.GetCell(4, 4)cell.Value = 500cell.style.font.Name = ddSize 字体大小Property Size As IntegerSet font = CreateExcelFontSet cell = wsheet.GetCell(4, 4)cell.Value = 500cell.style.font.Size = 30Underline 下划线Property Underline As enumFontUnderlineTypeSet font = CreateExcelFontSet cell = wsheet.GetCell(4, 4)cell.Value = 500cell.style.font.Underline = uDoubleAccounting八、ExcelCellStyleBorderColour边线颜色BorderLineStyle边线类型DiagLineColour线的颜色DiagLineStyle线条风格DiagLineTopLeftToBottomRight左上到右下DiagLineTopRightToBottomLeft右上到左下Font字体，见上页Format格式化FormulaHidden隐藏公式HorizontalAlignment左右显示位置LeftLineColour左边线颜色LeftLineStyle左边线风格Pattern图案PatternBackColour图案背景色PatternForeColour图案主色RightLineColour右边线颜色RightLineStyle右边线风格StackText是否堆叠StyleType风格类型TextRotationAngle旋转角度TopLineColour上边线颜色TopLineStyle上边线风格VerticalAlignment上下显示位置WrapText文本换行BorderColour 边线框颜色Property BorderColour As enumColoursBorderLineStyle 边线框类型Property BorderLineStyle As enumLineStyleSet cell = wsheet.GetCell(4, 4) cell.style.BorderLineStyle = xfbtDoublecell.style.BorderColour = cRedDiaglineStyle/ DiagLineColour/ DiagLineTopLeftToBottonRight 设定的时候要一组一起设Set cell = wsheet.GetCell(4, 4)cell.style.DiagLineStyle = xfbtThincell.style.DiagLineColour = cRedcell.style.DiagLineTopLeftToBottomRight = TrueHorizontalAlignment 左右显示位置Property HorizontalAlignment As enum_H_AlignmentSet cell = wsheet.GetCell(4, 4)cell.style.HorizontalAlignment = xfhaLeftVerticalAlignment 上下显示位置 格式同上LeftLineStyle/ LeftLineColour 左边线设置Property LeftLineColour As enumColoursSet cell = w