如何把一个Excel文件放到ASP页面中去?

%set xlApp = Server.CreateObject(“Excel.Application”)xlApp.Visible =
false

原文章地址:

 

‘ 使服务端不出现Excel窗口.set myWorkbook = xlApp.Workbooks.Add

    参考网上的方法修改,1000条记录导出只要3S(1GRDM,C2.8CPU).
1.项目添加excel对象类库的引用,Microsoft Excel 11.0 object
library(不同版本的Excel,类库不同,这是2003的) 
2.代码

‘ 建立一个worksheet.set myWorksheet =
myWorkbook.Worksheets(1)myWorksheet.Range(“A1”).Value =
‘精彩春风通讯录’myWorksheet.Range(“A1”).Font.Size =
12myWorksheet.Range(“A1”).Font.Bold = truemyWorksheet.Range(“A2”).Value
= ‘姓名’myWorksheet.Range(“B2”).Value =
‘地址’myWorksheet.Range(“C2”).Value =
‘电话’myWorksheet.Range(“D2”).Value = ‘手机’

设计思路如下:

图片 1
  1图片 2//-***************获取要写入excel的数据源***************
  2图片 3            Dao model=new Dao();
  3图片 4            DataTable dt=model.GetFileNameList(0,intPageSize,Convert.ToDateTime(dtPFrom.Value), Convert.ToDateTime(dtPTo.Value));//取得dataGrid绑定的DataSet
  4图片 5            if(dt==null) return;
  5图片 6            DataGridTableStyle ts = dataGrid1.TableStyles[0];
  6图片 7
  7图片 8
  8图片 9            //-***************获取excel对象***************
  9图片 10            string saveFileName=””;
 10图片 11            bool fileSaved=false;
 11图片 12            SaveFileDialog saveDialog=new SaveFileDialog();
 12图片 13            saveDialog.DefaultExt =”xls”;
 13图片 14            saveDialog.Filter=”Excel文件|*.xls”;
 14图片 15            saveDialog.FileName =”导入记录查询结果 “+DateTime.Today.ToString(“yyyy-MM-dd”);
 15图片 16            saveDialog.ShowDialog();
 16图片 17            saveFileName=saveDialog.FileName;
 17图片 18            if(saveFileName.IndexOf(“:”)<0) return; //被点了取消
 18图片 19            Excel.Application xlApp=new Excel.Application();
 19图片 20            if(xlApp==null)
 20图片 21            {
 21图片 22                MessageBox.Show(“无法启动Excel,可能您的机子未安装Excel”);
 22图片 23                return;
 23图片 24            }
 24图片 25            Excel.Workbook workbook = xlApp.Workbooks.Add(true);
 25图片 26            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];   
 26图片 27            Excel.Range range;
 27图片 28
 28图片 29       
 29图片 30            string oldCaption=dataGrid1.CaptionText;
 30图片 31            // 列索引,行索引,总列数,总行数                   
 31图片 32            int colIndex = 0;
 32图片 33            int RowIndex = 0;
 33图片 34            int colCount = ts.GridColumnStyles.Count;
 34图片 35            int RowCount=dt.Rows.Count;
 35图片 36           
 36图片 37
 37图片 38            // *****************获取数据*********************
 38图片 39            dataGrid1.CaptionVisible = true;
 39图片 40            dataGrid1.CaptionText = “正在导出数据图片 41图片 42“;
 40图片 43            // 创建缓存数据
 41图片 44            object[,] objData = new object[RowCount + 1, colCount];
 42图片 45            // 获取列标题
 43图片 46            foreach(DataGridColumnStyle cs in ts.GridColumnStyles)
 44图片 47            {
 45图片 48                objData[RowIndex,colIndex++] = cs.HeaderText;   
 46图片 49            }
 47图片 50       
 48图片 51            // 获取具体数据
 49图片 52            for(RowIndex =1;RowIndex< RowCount;RowIndex++)
 50图片 53            {
 51图片 54                for(colIndex=0;colIndex < colCount;colIndex++)
 52图片 55                {               
 53图片 56                    objData[RowIndex,colIndex] =dt.Rows[RowIndex-1][colIndex+1];
 54图片 57                }
 55图片 58               
 56图片 59            }   
 57图片 60
 58图片 61           //********************* 写入Excel*******************
 59图片 62               
 60图片 63            range = worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[RowCount,colCount]);           
 61图片 64            range.Value2= objData;            
 62图片 65            Application.DoEvents();   
 63图片 66           
 64图片 67            //*******************设置输出格式******************************
 65图片 68             
 66图片 69            //设置顶部説明   
 67图片 70            range = worksheet.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,colCount]);
 68图片 71            range.MergeCells = true;
 69图片 72            range.RowHeight=38;
 70图片 73            range.Font.Bold=true;
 71图片 74            range.Font.Size=14;
 72图片 75            range.Font.ColorIndex=10;//字体颜色
 73图片 76            xlApp.ActiveCell.FormulaR1C1 = “导入记录查询结果”;
 74图片 77
 75图片 78            //特殊数字格式
 76图片 79            range = worksheet.get_Range(xlApp.Cells[2,colCount],xlApp.Cells[RowCount,colCount]);
 77图片 80            range.NumberFormat=”yyyy-MM-dd hh:mm:ss”;
 78图片 81
 79图片 82            xlApp.Cells.HorizontalAlignment=Excel.Constants.xlCenter;   
 80图片 83            range = worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[2,colCount]);
 81图片 84            range.Interior.ColorIndex = 10;//背景色
 82图片 85            range.Font.Bold = true;
 83图片 86            range.RowHeight=20;
 84图片 87            ((Excel.Range)worksheet.Cells[2,1]).ColumnWidth=25;
 85图片 88            ((Excel.Range)worksheet.Cells[2,2]).ColumnWidth=13;
 86图片 89            ((Excel.Range)worksheet.Cells[2,3]).ColumnWidth=18;
 87图片 90            ((Excel.Range)worksheet.Cells[2,4]).ColumnWidth=15;
 88图片 91            ((Excel.Range)worksheet.Cells[2,5]).ColumnWidth=22;
 89图片 92
 90图片 93            //***************************保存**********************
 91图片 94            dataGrid1.CaptionVisible = false;
 92图片 95            dataGrid1.CaptionText = oldCaption; 
 93图片 96            if(saveFileName!=””)
 94图片 97            {
 95图片 98                try
 96图片 99                {
 97图片 100                    workbook.Saved =true;
 98图片 101                    workbook.SaveCopyAs(saveFileName);
 99图片 102                    fileSaved=true;
100图片 103                }
101图片 104                catch(Exception ex)
102图片 105                {
103图片 106                    fileSaved=false;
104图片 107                    MessageBox.Show(“导出文件时出错,文件可能正被打开!n”+ex.Message);
105图片 108                }
106图片 109            }
107图片 110            else
108图片 111            {
109图片 112                fileSaved=false;
110图片 113            }
111图片 114            xlApp.Quit();
112图片 115            GC.Collect();//强行销毁   
113图片 116            TimeSpan dateEnd=new TimeSpan(DateTime.Now.Ticks);           
114图片 117            TimeSpan tspan=dateBegin.Subtract(dateEnd).Duration();
115图片 118            MessageBox.Show(tspan.ToString());
116图片 119            if(fileSaved && File.Exists(saveFileName))
117图片 120                System.Diagnostics.Process.Start(saveFileName);

‘ 以上7行建立表头.myWorksheet.Range(“A2:D2”).Font.Bold = truestrFileName
= Session.SessionID & “.xls”

here comes the excel report, I made the modification from report manager
and wrapped into 2 main functions to be called in QTP.
Function initReport()
runtime = Date & “-“& Hour(Now) & Minute(Now)& Second(Now)
folderPath = “D:QTP_Frameworkreport”&runtime
call CreatFolderIfNotExist(folderPath)
call
WriteFile_Append(“D:QTP_Frameworkreportconfig.mse”,folderPath)
End Function
Function ReportQTP(testCaseName, result, Comment)
fullPath = ReadLastLine(“D:QTP_Frameworkreportconfig.mse”)
ReportExcelFile = fullPath &”report.xls”
CaptureFilePath = fullPath
Environment(“TCase”) = testCaseName
Call Report(result, comment, ReportExcelFile, CaptureFilePath)
End Function
‘GetIP is the IP add of the execution machine
Public Function GetIP
ComputerName=”.”
Dim objWMIService,colItems,objItem,objAddress
Set objWMIService = GetObject(“winmgmts:\” & ComputerName &
“rootcimv2”)
Set colItems = objWMIService.ExecQuery(“Select * From
Win32_NetworkAdapterConfiguration Where IPEnabled = True”)
For Each objItem in colItems
For Each objAddress in objItem.IPAddress
If objAddress <> “” then
GetIP = objAddress
Exit Function
End If
Next
Next
End Function
‘Report is the function for reporting
‘sStatus is the status of execution, valid input are :FAIL?PASS,
WARNING
‘sDetails is the comment/notes for this execution
Public Function Report(sStatus,sDetails, ReportExcelFile,
CaptureFilePath)
Dim fso
Dim oExcel
Dim ExcelFile
Dim TestcaseName
Dim objWorkBook
Dim objSheet
Dim NewTC
Dim Status
Dim temp
Dim PngPath
Set fso = CreateObject(“scripting.FileSystemObject”)
Set oExcel = CreateObject(“Excel.Application”)
Status=UCase(sStatus)
oExcel.Visible = false ‘True
‘Setting excel forms
If Not fso.FileExists(ReportExcelFile)Then
oExcel.Workbooks.Add
‘Get the first sheet of excel
Set objSheet = oExcel.Sheets.Item(1)
oExcel.Sheets.Item(1).Select
With objSheet
.Name = “Testing Result”
‘testing results
.Columns(“A:A”).ColumnWidth = 5
.Columns(“B:B”).ColumnWidth = 35
.Columns(“C:C”).ColumnWidth = 12.5
.Columns(“D:D”).ColumnWidth = 60
.Columns(“A:D”).HorizontalAlignment = -4131
.Columns(“A:D”).WrapText = True
‘set the font’s type and size
.Range(“A:D”).Font.Name = “Arial”
.Range(“A:D”).Font.Size = 10

.Range(“B1”).Value = “Testing Results”
.Range(“B1:C1”).Merge
‘set the style of the head of the file
.Range(“B1:C1”).Interior.ColorIndex = 53
.Range(“B1:C1”).Font.ColorIndex = 19
.Range(“B1:C1”).Font.Bold = True
‘set the execution date
.Range(“B3”).Value = “Test Date:”
.Range(“B4”).Value = “Test Start Time:”
.Range(“B5”).Value = “Test End Time:”
.Range(“B6”).Value = “Test Duration: “
.Range(“C3”).Value = Date
.Range(“C4”).Value = Time
.Range(“C5”).Value = Time
.Range(“C6”).Value = “=R[-1]C-R[-2]C”
.Range(“C6”).NumberFormat = “[h]:mm:ss;@”
‘Set the Borders for the Date & Time Cells
.Range(“C3:C8”).HorizontalAlignment = 4 ‘right align
.Range(“C3:C8”).Font.Bold = True
.Range(“C3:C8”).Font.ColorIndex = 7
.Range(“B3:C8”).Borders(1).LineStyle = 1
.Range(“B3:C8”).Borders(2).LineStyle = 1
.Range(“B3:C8”).Borders(3).LineStyle = 1
.Range(“B3:C8”).Borders(4).LineStyle = 1
‘Format the Date and Time Cells
.Range(“B3:C8”).Interior.ColorIndex = 40
.Range(“B3:C8”).Font.ColorIndex = 12
.Range(“C3:C8”).Font.ColorIndex = 7
.Range(“B3:A8”).Font.Bold = True
.Range(“B7”).Value = “No Of Testcases:”
.Range(“C7”).Value = “0”
.Range(“B8”).Value = “Testing Machine:”
.Range(“C8”).Value =GetIP()
.Range(“B10”).Value = “Test Case name”
.Range(“C10”).Value = “Testing results”
.Range(“D10”).Value = “Notes”
‘ set style for Result Summery
.Range(“B10:D10”).Interior.ColorIndex = 53
.Range(“B10:D10”).Font.ColorIndex = 19
.Range(“B10:D10”).Font.Bold = True
‘set style for Result Summery
.Range(“B10:D10”).Borders(1).LineStyle = 1
.Range(“B10:D10”).Borders(2).LineStyle = 1
.Range(“B10:D10”).Borders(3).LineStyle = 1
.Range(“B10:D10”).Borders(4).LineStyle = 1
.Range(“B10:D10”).HorizontalAlignment = -4131
.Range(“C11:C1000”).HorizontalAlignment = -4131
.Columns(“B:D”).Select
‘ .Columns(“B:D”).Autofit
.Range(“B11”).Select
End With
oExcel.ActiveWindow.FreezePanes = True
oExcel.ActiveWorkbook.SaveAs ReportExcelFile
oExcel.Quit
Set objSheet = Nothing
End If
TestcaseName = Environment(“TCase”)
Set objWorkBook = oExcel.Workbooks.Open(ReportExcelFile)
Set objSheet = oExcel.Sheets(“Testing Result”)
With objSheet
Environment.Value(“Row”) = .Range(“C7”).Value + 11
NewTC = False
If TestcaseName <> objSheet.Cells(Environment(“Row”)-1,2).value
Then
.Cells(Environment(“Row”),2).value = TestcaseName
.Cells(Environment(“Row”), 3).Value = Status
.Cells(Environment(“Row”), 4).value = sDetails
.Cells(Environment(“Row”), 5).value = “click this link to see
ScreenShot”
Select Case Status
Case “FAIL”
.Range(“C” & Environment(“Row”)).Font.ColorIndex = 3
temp = Date & “-“& Hour(Now) & Minute(Now)& Second(Now)
PngPath = CaptureFilePath & “” & temp & “.png”
.Hyperlinks.Add objSheet.Cells(Environment(“Row”), 5), PngPath, “”,””
Call Capture(temp, CaptureFilePath)
Case “PASS”
.Range(“C” & Environment(“Row”)).Font.ColorIndex = 50
temp = Date & “-“& Hour(Now) & Minute(Now)& Second(Now)
PngPath = CaptureFilePath & “” & temp & “.png”
.Hyperlinks.Add objSheet.Cells(Environment(“Row”), 5), PngPath, “”,””
Call Capture(temp, CaptureFilePath)
Case “WARNING”
.Range(“C” & Environment(“Row”)).Font.ColorIndex = 5
temp = Date & “-“& Hour(Now) & Minute(Now)& Second(Now)
PngPath = CaptureFilePath & “” & temp & “.png”
.Hyperlinks.Add objSheet.Cells(Environment(“Row”), 5), PngPath, “”,””
Call Capture(temp, CaptureFilePath)
End Select
NewTC = True
.Range(“C7”).Value = .Range(“C7”).Value + 1
‘set board
.Range(“B” & Environment(“Row”) & “:D” &
Environment(“Row”)).Borders(1).LineStyle = 1
.Range(“B” & Environment(“Row”) & “:D” &
Environment(“Row”)).Borders(2).LineStyle = 1
.Range(“B” & Environment(“Row”) & “:D” &
Environment(“Row”)).Borders(3).LineStyle = 1
.Range(“B” & Environment(“Row”) & “:D” &
Environment(“Row”)).Borders(4).LineStyle = 1
‘set font type and color
.Range(“B” & Environment(“Row”) & “:D” &
Environment(“Row”)).Interior.ColorIndex = 19
.Range(“B” & Environment(“Row”)).Font.ColorIndex = 53
.Range(“D” & Environment(“Row”)).Font.ColorIndex = 41
.Range(“B” & Environment(“Row”) & “:D” & Environment(“Row”)).Font.Bold =
True
End If
If (Not NewTC) And (Status = “FAIL”) Then
.Cells(Environment(“Row”), 3).Value = “Fail”
.Range(“C” & Environment(“Row”)).Font.ColorIndex = 3
end If
‘update end time
.Range(“C5”).Value = Time
.Columns(“B:D”).Select
‘.Columns(“B:D”).Autofit
End With
oExcel.ActiveWindow.FreezePanes = True
‘save result
objWorkBook.Save
oExcel.Quit
Set objSheet = Nothing
Set objWorkBook = Nothing
Set oExcel = Nothing
Set fso = Nothing
End Function
Public Function Capture(fileNo, CaptureFilePath)
Dim datestamp
Dim filename
datestamp = Now()
filename = fileNo & “.png”
‘filename = Replace(filename,”/”,””)
‘filename = Replace(filename,”:”,””)
filename = CaptureFilePath + “” + “”&filename
Desktop.CaptureBitmap filename
‘Reporter.ReportEvent micFail,”image”,”<img src='” & filename &
“‘>”
End Function
‘Append to txt file
Public Function WriteFile_Append(pathway,words)
Dim fileSystemObj,fileSpec,logFile,way
Set fileSystemObj = CreateObject(“Scripting.FileSystemObject”)
fileSpec = pathway
Set logFile = fileSystemObj.OpenTextFile(fileSpec, 8, true)
logFile.WriteLine (CStr(words))
logFile.Close
Set logFile = Nothing
End Function
‘Read last line of txt file
Function ReadLastLine(pathway)
Dim fso,myfile
Set fso=CreateObject(“scripting.FileSystemObject”)
Set myfile = fso.openTextFile(pathway,1,false)
While Not myfile.AtEndOfLine
temp = myfile.ReadLine
Wend
ReadLastLine = temp
End Function
Function CreatFolderIfNotExist(fldr)
Dim fso, msg
Set fso = CreateObject(“Scripting.FileSystemObject”)
If Not (fso.FolderExists(fldr)) Then
Set f = fso.CreateFolder(fldr)
End If
End Function

‘ 确保文件名唯一.strAppPath =
Request.ServerVariables(“PATH_trANSLATED”)strAppPath = Left(strAppPath,
InstrRev(strAppPath, “/”))strFullPath = strAppPath &
strFileNamemyWorkbook.SaveAs(strFullPath)

‘ 保存文件.myWorkbook.Close

‘ 关闭Excel.xlApp.Quitset myWorksheet = Nothingset myWorkbook =
Nothingset myxlApp = NothingResponse.Redirect strFileName

‘ 写到ASP页面,即浏览器中.%

[1]

发表评论

电子邮件地址不会被公开。 必填项已用*标注