ASP实例代码 asp操作Excel类

ASP实例代码asp操作Excel类asp操作Excel类：<%'*******************************************************************'使用说明'Dima'Seta=newCreateExcel'a.SavePath="x"'保存路径'a.SheetName="工作簿名称"'多个工作表a.SheetName=array("工作簿名称一","工作簿名称二")'a.SheetTitle="表名称"'可以为空多个工作表a.SheetName=array("表名称一","表名称二")'a.Data=d'二维数组'多个工作表array(b,c)b与c为二维数组'Dimrs'Setrs=server.CreateObject("Adodb.RecordSet")'rs.open"Selectid,classid,classNamefrom[class]",conn,1,1'a.AddDBDatars,"字段名一，字段名二","工作簿名称","表名称",true'true自动获取表字段名'a.AddDatac,true,"工作簿名称","表名称"'c二维数组true第一行是否为标题行'a.AddtDatae,"Sheet1"'按模板生成c=array(array("AA1","内容"),array("AA2","内容2"))'a.Create()'a.UsedTime生成时间，毫秒数'a.SavePath保存路径'Seta=nothing'设置COM组件的操作权限。在命令行键入“DCOMCNFG”，则进入COM组件配置界面，选择MicrosoftExcel后点击属性按钮，将三个单选项一律选择自定义，编辑中将Everyone加入所有权限'*******************************************************************ClassCreateExcelPrivateCreateType_PrivatesavePath_PrivatereadPath_PrivateAuthorStrRem设置作者PrivateVersionStrRem设置版本PrivateSystemStrRem设置系统名称PrivateSheetName_Rem设置表名PrivateSheetTitle_Rem设置标题PrivateExcelDataRem设置表数据PrivateExcelAppRemExcel.ApplicationPrivateExcelBookPrivateExcelSheetsPrivateUsedTime_Rem使用的时间PublicTitleFirstLineRem首行是否标题PrivateSubClass_Initialize()Server.ScriptTimeOut=99999UsedTime_=TimerSystemStr="Lc00_CreateExcelServer"AuthorStr="Surnfusurnfu@126.com31333716"VersionStr="1.0"ifnotIsObjInstalled("Excel.Application")thenInErr("服务器未安装Excel.Application控件")endifsetExcelApp=createObject("Excel.Application")ExcelApp.DisplayAlerts=falseExcelApp.Application.Visible=falseCreateType_=1readPath_=nullEndSubPrivateSubClass_Terminate()ExcelApp.QuitIfIsobject(ExcelSheets)ThenSetExcelSheets=NothingIfIsobject(ExcelBook)ThenSetExcelBook=NothingIfIsobject(ExcelApp)ThenSetExcelApp=NothingEndSubPublicPropertyLetReadPath(ByValVal)IfInstr(Val,":\")<>0ThenreadPath_=Trim(Val)elsereadPath_=Server.MapPath(Trim(Val))endifEndPropertyPublicPropertyLetSavePath(ByValVal)IfInstr(Val,":\")<>0ThensavePath_=Trim(Val)elsesavePath_=Server.MapPath(Trim(Val))endifEndPropertyPublicPropertyLetCreateType(ByValVal)ifVal<>1andVal<>2thenCreateType_=1elseCreateType_=ValendifEndPropertyPublicPropertyLetData(ByValVal)ifnotisArray(Val)thenInErr("表数据设置有误")endifExcelData=ValEndPropertyPublicPropertyGetSavePath()SavePath=savePath_EndPropertyPublicPropertyGetUsedTime()UsedTime=UsedTime_EndPropertyPublicPropertyLetSheetName(ByValVal)ifnotisArray(Val)thenifVal=""thenInErr("表名设置有误")endifTitleFirstLine=trueelseReDimTitleFirstLine(Ubound(Val))Dimik_Forik_=0toUbound(Val)TitleFirstLine(ik_)=trueNextendifSheetName_=ValEndPropertyPublicPropertyLetSheetTitle(ByValVal)ifnotisArray(Val)thenifVal=""thenInErr("表标题设置有误")endifendifSheetTitle_=ValEndPropertyRem检查数据PrivateSubCheckData()ifsavePath_=""thenInErr("保存路径不能为空")ifnotisArray(SheetName_)thenifSheetName_=""thenInErr("表名不能为空")endififCreateType_=2thenifnotisArray(ExcelData)thenInErr("数据载入错误，或者未载入")endifExitSubendififisArray(SheetName_)thenifnotisArray(SheetTitle_)thenifSheetTitle_<>""thenInErr("表标题设置有误，与表名不对应")endifendififnotIsArray(ExcelData)thenInErr("表数据载入有误")endififisArray(SheetName_)thenifGetArrayDim(ExcelData)<>1thenInErr("表数据载入有误，数据格式错误，维度应该为一")elseifGetArrayDim(ExcelData)<>2thenInErr("表数据载入有误，数据格式错误，维度应该为二")endifEndSubRem生成ExcelPublicFunctionCreate()CallCheckData()ifnotisnull(readPath_)thenExcelApp.WorkBooks.Open(readPath_)elseExcelApp.WorkBooks.addendifsetExcelBook=ExcelApp.ActiveWorkBooksetExcelSheets=ExcelBook.WorksheetsifCreateType_=2thenDimih_Forih_=0toUbound(ExcelData)CallSetSheets(ExcelData(ih_),ih_)NextExcelBook.SaveAssavePath_UsedTime_=FormatNumber((Timer-UsedTime_)*1000,3)ExitFunctionendififIsArray(SheetName_)thenDimik_Forik_=0toUbound(ExcelData)CallCreateSheets(ExcelData(ik_),ik_)NextelseCallCreateSheets(ExcelData,-1)endifExcelBook.SaveAssavePath_UsedTime_=FormatNumber((Timer-UsedTime_)*1000,3)EndFunctionPrivateSubCreateSheets(ByValData_,DataId_)DimSpreadsheetDimtempSheetTitleDimtempTitleFirstLineifDataId_<>-1thenifDataId_>ExcelSheets.Count-1thenExcelSheets.Add()setSpreadsheet=ExcelBook.Sheets(1)elsesetSpreadsheet=ExcelBook.Sheets(DataId_+1)endififisArray(SheetTitle_)thentempSheetTitle=SheetTitle_(DataId_)elsetempSheetTitle=""endiftempTitleFirstLine=TitleFirstLine(DataId_)Spreadsheet.Name=SheetName_(DataId_)elsesetSpreadsheet=ExcelBook.Sheets(1)Spreadsheet.Name=SheetName_tempSheetTitle=SheetTitle_tempTitleFirstLine=TitleFirstLineendifDimLine_:Line_=1DimRowNum_:RowNum_=Ubound(Data_,1)+1DimLastCols_iftempSheetTitle<>""then'Spreadsheet.Columns(1).ShrinkToFit=true'设定是否自动适应表格单元大小(单元格宽不变）LastCols_=getColName(Ubound(Data_,2)+1)withSpreadsheet.Cells(1,1).value=tempSheetTitle'设置Excel表里的字体.Font.Bold=True'单元格字体加粗.Font.Italic=False'单元格字体倾斜.Font.Size=20'设置单元格字号.="宋体"'设置单元格字体'.font.ColorIndex=2'设置单元格文字的颜色，颜色可以查询，2为白色EndwithwithSpreadsheet.Range("A1:"&LastCols_&"1").merge'合并单元格(单元区域)'.Interior.ColorIndex=1'设计单元络背景色.HorizontalAlignment=3'居中EndwithLine_=2RowNum_=RowNum_+1endifDimiRow_,iCol_DimdRow_,dCol_DimtempLastRange:tempLastRange=getColName(Ubound(Data_,2)+1)&(RowNum_)DimBeginRow:BeginRow=1iftempSheetTitle<>""thenBeginRow=BeginRow+1iftempTitleFirstLine=truethenBeginRow=BeginRow+1ifBeginRow=1thenwithSpreadsheet.Range("A1:"&tempLastRange).Borders.LineStyle=1.BorderAround-4119,-4138'设置外框.NumberFormatLocal="@"'文本格式.Font.Bold=False.Font.Italic=False.Font.Size=10.ShrinkToFit=trueendwithelsewithSpreadsheet.Range("A1:"&tempLastRange).Borders.LineStyle=1.BorderAround-4119,-4138.ShrinkToFit=trueendwithwithSpreadsheet.Range("A"&BeginRow&":"&tempLastRange).NumberFormatLocal="@".Font.Bold=False.Font.Italic=False.Font.Size=10endwithendififtempTitleFirstLine=truethenBeginRow=1iftempSheetTitle<>""thenBeginRow=BeginRow+1withSpreadsheet.Range("A"&BeginRow&":"&getColName(Ubound(Data_,2)+1)&(BeginRow)).NumberFormatLocal="@".Font.Bold=True.Font.Italic=False.Font.Size=12.Interior.ColorIndex=37.HorizontalAlignment=3'居中.font.ColorIndex=2endwithendifForiRow_=Line_ToRowNum_ForiCol_=1To(Ubound(Data_,2)+1)dCol_=iCol_-1iftempSheetTitle<>""thendRow_=iRow_-2elsedRow_=iRow_-1IfnotIsNull(Data_(dRow_,dCol_))thenwithSpreadsheet.Cells(iRow_,iCol_).Value=Data_(dRow_,dCol_)EndwithEndIfNextNextsetSpreadsheet=NothingEndSubRem测试组件是否已经安装PrivateFunctionIsObjInstalled(strClassString)OnErrorResumeNextIsObjInstalled=FalseErr=0DimxTestObjSetxTestObj=Server.CreateObject(strClassString)If0=ErrThenIsObjItalled=TrueSetxTestObj=NothingErr=0EndFunctionRem取得数组维数PrivateFunctionGetArrayDim(ByValarr)GetArrayDim=NullDimi_,tempIfIsArray(arr)ThenFori_=1To60OnErrorResumeNexttemp=UBound(arr,i_)IfErr.Number<>0ThenGetArrayDim=i_-1Err.ClearExitFunctionEndIfNextGetArrayDim=i_EndIfEndFunctionPrivateFunctionGetNumFormatLocal(DataType)SelectCaseDataTypeCase"Currency":GetNumFormatLocal="￥#,##0.00_);(￥#,##0.00)"Case"Time":GetNumFormatLocal="[$-F800]dddd,mmmmdd,yyyy"Case"Char":GetNumFormatLocal="@"Case"Common":GetNumFormatLocal="G/通用格式"Case"Number":GetNumFormatLocal="#,##0.00_"Caseelse:GetNumFormatLocal="@"EndSelectEndFunctionPublicSubAddDBData(ByValRsFlied,ByValFliedTitle,ByValtempSheetName_,ByValtempSheetTitle_,DBTitle)ifRsFlied.EofthenExitSubDimcolNum_:colNum_=RsFlied.fields.countDimRownum_:Rownum_=RsFlied.RecordCountDimArrFliedTitleifDBTitle=truethenFliedTitle=""Dimig_Forig_=0tocolNum_-1FliedTitle=FliedTitle&RsFlied.fields.item(ig_).nameifig_<>colNum_-1thenFliedTitle=FliedTitle&","NextendififFliedTitle<>""thenRownum_=Rownum_+1ArrFliedTitle=Split(FliedTitle,",")ifUbound(ArrFliedTitle)<>colNum_-1thenInErr("获取数据库表有误，列数不符")endifendifDimtempData:ReDimtempData(Rownum_-1,colNum_-1)Dimix_,iy_DimizifFliedTitle<>""theniz=Rownum_-2elseiz=Rownum_-1Forix_=0ToizForiy_=0TocolNum_-1ifFliedTitle<>""thenifix_=0thentempData(ix_,iy_)=ArrFliedTitle(iy_)tempData(ix_+1,iy_)=RsFlied(iy_)elsetempData(ix_+1,iy_)=RsFlied(iy_)endifelsetempData(ix_,iy_)=RsFlied(iy_)endifNextRsFlied.MoveNextNextDimtempFirstLineifFliedTitle<>""thentempFirstLine=trueelsetempFirstLine=falseCallAddData(tempData,tempFirstLine,tempSheetName_,tempSheetTitle_)EndSubPublicSubAddData(ByValtempDate_,ByValtempFirstLine_,ByValtempSheetName_,ByValtempSheetTitle_)ifnotisArray(ExcelData)thenExcelData=tempDate_TitleFirstLine=tempFirstLine_SheetName_=tempSheetName_SheetTitle_=tempSheetTitle_elseifGetArrayDim(ExcelData)=1thenDimtempArrLen:tempArrLen=Ubound(ExcelData)+1ReDimPreserveExcelData(tempArrLen)ExcelData(tempArrLen)=tempDate_ReDimPreserveTitleFirstLine(tempArrLen)TitleFirstLine(tempArrLen)=tempFirstLine_ReDimPreserveSheetName_(tempArrLen)SheetName_(tempArrLen)=tempSheetName_ReDimPreserveSheetTitle_(tempArrLen)SheetTitle_(tempArrLen)=tempSheetTitle_elseDimtempOldData:tempOldData=ExcelDataExcelData=Array(tempOldData,tempDate_)TitleFirstLine=Array(TitleFirstLine,tempFirstLine_)SheetName_=Array(SheetName_,tempSheetName_)SheetTitle_=Array(SheetTitle_,tempSheetTitle_)endifendifEndSubRem模板增加数据方法PublicSubAddtData(ByValtempDate_,ByValtempSheetName_)CreateType_=2ifnotisArray(ExcelData)thenExcelData=Array(tempDate_)SheetName_=Array(tempSheetName_)elseDimtempArrLen:tempArrLen=Ubound(ExcelData)+1ReDimPreserveExcelData(tempArrLen)ExcelData(tempArrLen)=tempDate_ReDimPreserveSheetName_(tempArrLen)SheetName_(tempArrLen)=tempSheetName_EndifEndSubPrivateSubSetSheets(ByValData_,DataId_)DimSpreadsheetsetSpreadsheet=ExcelBook.Sheets(SheetName_(DataId_))Spreadsheet.ActivateDimix_Forix_=0ToUbound(Data_)ifnotisArray(Data_(ix_))thenInErr("表数据载入有误，数据格式错误")ifUbound(Data_(ix_))<>1thenInErr("表数据载入有误，数据格式错误")Spreadsheet.Range(Data_(ix_)(0)).value=Data_(ix_)(1)NextsetSpreadsheet=NothingEndSubPublicFunctionGetTime(msec_)DimReTime_:ReTime_=""ifmsec_<1000thenReTime_=msec_&"MS"elseDimsecond_second_=(msec_\1000)if(msec_mod1000)<>0thenmsec_=(msec_mod1000)&"毫秒"elsemsec_=""endifDimn_,aryTime(2),aryTimeunit(2)aryTimeunit(0)="秒"aryTimeunit(1)="分"aryTimeunit(2)="小时"n_=0DimtempSecond_:tempSecond_=second_While(tempSecond_/60>=1)tempSecond_=Fix(tempSecond_/60*100)/100n_=n_+1WEndDimm_Form_=n_To0Step-1aryTime(m_)=second_\(60^m_)second_=second_mod(60^m_)ReTime_=ReTime_&aryTime(m_)&aryTimeunit(m_)Nextifmsec_<>""thenReTime_=ReTime_&msec_endifGetTime=ReTime_endFunctionRem取得列名PrivateFunctiongetColName(ByValColNum)DimArrlitter:Arrlitter=split("ABCDEFGHIJKLMNOPQRSTUVWXYZ","")DimReValue_ifColNum<=Ubound(Arrlitter)+1thenReValue_=Arrlitter(ColNum-1)elseReValue_=Arrlitter(((ColNum-1)\26))&Arrlitter(((ColNum-1)mod26)