asp操作Excel类_.docx
《asp操作Excel类_.docx》由会员分享,可在线阅读,更多相关《asp操作Excel类_.docx(28页珍藏版)》请在三一文库上搜索。
1、asp操作Excel类_ asp操作Excel类: % * 用法说明 Dim a Set a=new CreateExcel a.SavePath=x 保存路径 a.SheetName=工作簿名称 多个工作表 a.SheetName=array(工作簿名称一,工作簿名称二) a.SheetTitle=表名称 可以为空 多个工作表 a.SheetName=array(表名称一,表名称二) a.Data =d 二维数组 多个工作表 array(b,c) b与c为二维数组 Dim rs Set rs=server.CreateObject(Adodb.RecordSet) rs.open Selec
2、t id, classid, className from class ,conn, 1, 1 a.AddDBData rs, 字段名一,字段名二, 工作簿名称, 表名称, true true自动猎取表字段名 a.AddData c, true , 工作簿名称, 表名称 c二维数组 true 第一行是否为标题行 a.AddtData e, Sheet1 按模板生成 c=array(array(AA1, 内容), array(AA2, 内容2) a.Create() a.UsedTime 生成时间,毫秒数 a.SavePath 保存路径 Set a=nothing 设置COM组件的操作权限。在指
3、令行键入“DCOMCNFG”,则进入COM组件配置界面,选择MicrosoftExcel后点击属性按钮,将三个单选项一律选择自定义,编辑中将Everyone加入全部权限 * Class CreateExcel Private CreateType_ Private savePath_ Private readPath_ Private AuthorStr Rem 设置 Private VersionStr Rem 设置版本 Private SystemStr Rem 设置系统名称 Private SheetName_ Rem 设置表名 Private SheetTitle_ Rem 设置标题
4、Private ExcelData Rem 设置表数据 Private ExcelApp Rem Excel.Application Private ExcelBook Private ExcelSheets Private UsedTime_ Rem 用法的时间 Public TitleFirstLine Rem 首行是否标题 Private Sub Class_Initialize() Server.ScriptTimeOut = 99999 UsedTime_ = Timer SystemStr = Lc00_CreateExcelServer AuthorStr = Surnfu 3
5、1333716 VersionStr = 1.0 if not IsObjInstalled(Excel.Application) then InErr(服务器未安装Excel.Application控件) end if set ExcelApp = createObject(Excel.Application) ExcelApp.DisplayAlerts = false ExcelApp.Application.Visible = false CreateType_ = 1 readPath_ = null End Sub Private Sub Class_Terminate() Exc
6、elApp.Quit If Isobject(ExcelSheets) Then Set ExcelSheets = Nothing If Isobject(ExcelBook) Then Set ExcelBook = Nothing If Isobject(ExcelApp) Then Set ExcelApp = Nothing End Sub Public Property Let ReadPath(ByVal Val) If Instr(Val, :)0 Then readPath_ = Trim(Val) else readPath_=Server.MapPath(Trim(Val
7、) end if End Property Public Property Let SavePath(ByVal Val) If Instr(Val, :)0 Then savePath_ = Trim(Val) else savePath_=Server.MapPath(Trim(Val) end if End Property Public Property Let CreateType(ByVal Val) if Val 1 and Val 2 then CreateType_ = 1 else CreateType_ = Val end if End Property Public P
8、roperty Let Data(ByVal Val) if not isArray(Val) then InErr(表数据设置有误) end if ExcelData = Val End Property Public Property Get SavePath() SavePath = savePath_ End Property Public Property Get UsedTime() UsedTime = UsedTime_ End Property Public Property Let SheetName(ByVal Val) if not isArray(Val) then
9、if Val = then InErr(表名设置有误) end if TitleFirstLine = true else ReDim TitleFirstLine(Ubound(Val) Dim ik_ For ik_ = 0 to Ubound(Val) TitleFirstLine(ik_) = true Next end if SheetName_ = Val End Property Public Property Let SheetTitle(ByVal Val) if not isArray(Val) then if Val = then InErr(表标题设置有误) end i
10、f end if SheetTitle_ = Val End Property Rem 检查数据 Private Sub CheckData() if savePath_ = then InErr(保存路径不能为空) if not isArray(SheetName_) then if SheetName_ = then InErr(表名不能为空) end if if CreateType_ = 2 then if not isArray(ExcelData) then InErr(数据载入错误,或者未载入) end if Exit Sub end if if isArray(SheetNam
11、e_) then if not isArray(SheetTitle_) then if SheetTitle_ then InErr(表标题设置有误,与表名不对应) end if end if if not IsArray(ExcelData) then InErr(表数据载入有误) end if if isArray(SheetName_) then if GetArrayDim(ExcelData) 1 then InErr(表数据载入有误,数据格式错误,维度应当为一) else if GetArrayDim(ExcelData) 2 then InErr(表数据载入有误,数据格式错误,
12、维度应当为二) end if End Sub Rem 生成Excel Public Function Create() Call CheckData() if not isnull(readPath_) then ExcelApp.WorkBooks.Open(readPath_) else ExcelApp.WorkBooks.add end if set ExcelBook = ExcelApp.ActiveWorkBook set ExcelSheets = ExcelBook.Worksheets if CreateType_ = 2 then Dim ih_ For ih_ = 0
13、to Ubound(ExcelData) Call SetSheets(ExcelData(ih_), ih_) Next ExcelBook.SaveAs savePath_ UsedTime_ = FormatNumber(Timer - UsedTime_)*1000, 3) Exit Function end if if IsArray(SheetName_) then Dim ik_ For ik_ = 0 to Ubound(ExcelData) Call CreateSheets(ExcelData(ik_), ik_) Next else Call CreateSheets(E
14、xcelData, -1) end if ExcelBook.SaveAs savePath_ UsedTime_ = FormatNumber(Timer - UsedTime_)*1000, 3) End Function Private Sub CreateSheets(ByVal Data_, DataId_) Dim Spreadsheet Dim tempSheetTitle Dim tempTitleFirstLine if DataId_-1 then if DataId_ ExcelSheets.Count - 1 then ExcelSheets.Add() set Spr
15、eadsheet = ExcelBook.Sheets(1) else set Spreadsheet = ExcelBook.Sheets(DataId_ + 1) end if if isArray(SheetTitle_) then tempSheetTitle = SheetTitle_(DataId_) else tempSheetTitle = end if tempTitleFirstLine = TitleFirstLine(DataId_) Spreadsheet.Name = SheetName_(DataId_) else set Spreadsheet = ExcelB
16、ook.Sheets(1) Spreadsheet.Name = SheetName_ tempSheetTitle = SheetTitle_ tempTitleFirstLine = TitleFirstLine end if Dim Line_ : Line_ = 1 Dim RowNum_ : RowNum_ = Ubound(Data_, 1) + 1 Dim LastCols_ if tempSheetTitle then Spreadsheet.Columns(1).ShrinkToFit=true 设定是否自动适应表格单元大小(单元格宽不变) LastCols_ = getCo
17、lName(Ubound(Data_, 2) + 1) with Spreadsheet.Cells(1, 1) .value = tempSheetTitle 设置Excel表里的字体 .Font.Bold = True 单元格字体加粗 .Font.Italic = False 单元格字体倾斜 .Font.Size = 20 设置单元格字号 .font.name=宋体 设置单元格字体 .font.ColorIndex=2 设置单元格文字的颜色,颜色可以查询,2为白色 End with with Spreadsheet.Range(A1: LastCols_ 1) .merge 合并单元格(单
18、元区域) .Interior.ColorIndex = 1 设计单元络背景色 .HorizontalAlignment = 3 居中 End with Line_ = 2 RowNum_ = RowNum_ + 1 end if Dim iRow_, iCol_ Dim dRow_, dCol_ Dim tempLastRange : tempLastRange = getColName(Ubound(Data_, 2)+1) (RowNum_) Dim BeginRow : BeginRow = 1 if tempSheetTitle then BeginRow = BeginRow + 1
19、 if tempTitleFirstLine = true then BeginRow = BeginRow + 1 if BeginRow=1 then with Spreadsheet.Range(A1: tempLastRange) .Borders.LineStyle = 1 .BorderAround -4119, -4138 设置外框 .NumberFormatLocal = 文本格式 .Font.Bold = False .Font.Italic = False .Font.Size = 10 .ShrinkToFit=true end with else with Spread
20、sheet.Range(A1: tempLastRange) .Borders.LineStyle = 1 .BorderAround -4119, -4138 .ShrinkToFit=true end with with Spreadsheet.Range(A BeginRow : tempLastRange) .NumberFormatLocal = .Font.Bold = False .Font.Italic = False .Font.Size = 10 end with end if if tempTitleFirstLine = true then BeginRow = 1 i
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- asp 操作 Excel
链接地址:https://www.31doc.com/p-11625641.html