如何将当前视图的数据导出到Excel中
各位大大,在lotus的按钮上要如何写lotusScript代码啊,直接写在BS下这个按钮就看不到了!!用@Command([ToolsRunMacro];"(proxy)")也不行,在设定代理的时候有"此代理程序执行时间"和"有哪些文件要执行"这两项要如何设定呢? 我在网上找了个将当前视图数据导出到Excel的代码,看代码应该没有什么问题,但不知如何用... 郁闷...一用总出问题...
亟盼各位指点!!
各位大大都是怎么实现这个功能的啊,能不能讲明白点,小弟一直在线...
----------------
Sub WriteToExcel
'本字程序功能为写入数据到指定的Excel文件里,用户选定的文档将被输出。
'[处理视图]-初始化视图
Dim ws As New NotesUIWorkspace
Dim uiView As NotesUIView
Dim View As NotesView
Dim col As NotesViewColumn
Dim s As New NotesSession
Dim db As NotesDatabase
Dim cl As NotesDocumentCollection
Set uiView = ws.CurrentView
Set View = uiView.View
Set db = s.CurrentDatabase
Set cl = db.UnprocessedDocuments
'初始化Excel对象
Dim xlApp As Variant
Dim xlsheet As Variant
Set xlApp = CreateObject("Excel.application")
xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
xlApp.Visible = True
xlApp.Workbooks.Add
xlApp.ReferenceStyle = 2
Set xlsheet = xlApp.Workbooks(1).Worksheets(1)
'[处理视图]-读取列名
Dim ReadColumnTitle() As String
Redim ReadColumnTitle(0 To Ubound(View.Columns)) As String
For i = 0 To Ubound(View.Columns)
ReadColumnTitle(i)=View.Columns(i).title
Next
Call WriteToExcelRow("A",2,xlsheet,ReadColumnTitle)
'[处理视图]-读取内容
Dim tDoc As NotesDocument
Dim ReadViewResult
Dim j As Integer
Set tDoc = cl.GetFirstDocument
j=3
While Not tDoc Is Nothing
ReadViewResult = ReadRow(View,tDoc)
Call WriteToExcelRow("A",j,xlsheet,ReadViewResult)
Set tDoc = cl.GetNextDocument(tDoc)
j=j+1
Wend
'保存文件
xlApp.Rows("2:1").Select
xlApp.Selection.Font.Bold = True
xlApp.Selection.Font.Underline = True
xlApp.Range(xlsheet.Cells(1,1), xlsheet.Cells(j,Ubound(View.Columns)+1)).Select
xlApp.Selection.Font.Name = "Arial"
xlApp.Selection.Font.Size = 9
xlApp.Selection.Columns.AutoFit
With xlApp.Worksheets(1)
.PageSetup.Orientation = 2
.PageSetup.centerheader = "Report - Confidential"
.Pagesetup.RightFooter = "Page &P" & Chr$(13) & "Date: &D"
.Pagesetup.CenterFooter = ""
End With
xlApp.ReferenceStyle = 1
xlApp.Range("A1").Select
xlApp.StatusBar = "Importing Data from Lotus Notes Application was Completed."
End Sub
Function ReadRow( tView As NotesView , tDoc As NotesDocument ) As Variant
'该函数为把视图中某定位文档的所有视图显示值放入一个ReadRow的数组里，tView为所要操作的视图，tDoc为所定位的文档
Dim tResultArray() As String
tLength% = Ubound(tView.Columns)
Redim tResultArray(0 To tLength%) As String
For i = 0 To Ubound(tView.Columns)
Set col = tView.Columns(i)
tValue$ = col.ItemName
vFormula$ = col.Formula
If col.Formula ="" Then
vResult = Evaluate(tValue$,tDoc)
Else
vResult = Evaluate(vFormula$,tDoc)
End If
tResultArray(i) = vResult(0)
Next
ReadRow = tResultArray
End Function
Sub WriteToExcelRow( tR As String,tC As Integer,xlsheet As Variant,tValue As Variant)
With xlsheet
For i = 0 To Ubound(tValue)
tLoca=Chr(Asc(tR)+i)+Cstr(tC)
.Range(tLoca).Value = tValue(i)
Next
End With
End Sub --------------------编程问答-------------------- 我想要的是在BS结构下的,我测试一个简单的代理程序在CS下可以实现,但BS下显示无法显示网页,这是怎么回事?
在BS下导出数据到excel如何做啊??
--------------------编程问答-------------------- BS下表单里不支持lotusscript的，可以通过直接调用agent的方式实现你要的功能 --------------------编程问答-------------------- 在BS下如果只导出视图上的内容的话，直接Copy到Excel中去就行了。 --------------------编程问答-------------------- function AllAreaExcel()
{
var oXL = new ActiveXObject("Excel.Application");
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var sel=document.body.createTextRange();
sel.moveToElementText(PrintA);
sel.select();
sel.execCommand("Copy");
oSheet.Paste();
oXL.Visible = true;
}
------------
通过JS这样可以将数据导出到excel,但不能控制excel的列宽,都很窄,有哪位知道怎么控制列宽吗? --------------------编程问答-------------------- excel的列宽不能在电子表里设置? --------------------编程问答-------------------- 列宽可以控制的，贴出我早期的一段代码仅供参考
function exportToExcel(oWB,curTbl,sheetName,sheetNum) //读取表格中每个单元到EXCEL中
{
if(sheetNum <= 3 ){
var oSheet = oWB.Worksheets(sheetNum);
}else{
var oSheet= oWB.Worksheets.Add();
}
// 设置sheet1的名称
oSheet.name=sheetName;
//激活当前sheet
var Lenr = curTbl.rows.length;
var domTd;
var flagTitle;
//取得表格行数
for (i = 0; i < Lenr; i++)
{
flagTitle = false;
var Lenc = curTbl.rows(i).cells.length;
//取得每行的列数
for (j = 0; j < Lenc; j++)
{
//hzTitle
domTd = curTbl.rows(i).cells(j);
if(domTd.getAttribute("name") && domTd.getAttribute("name") == "hzTitle"){
oSheet.Cells(i + 1, j + 1).Font.Bold = true;
oSheet.Range(oSheet.Cells(i + 1, j + 1), oSheet.Cells(i + 1, j + 3)).MergeCells = true;
oSheet.Range(oSheet.Cells(i + 1, j + 1), oSheet.Cells(i + 1, j + 3)).Borders.LineStyle = 1;
flagTitle = true;
}else{
//自动换行
oSheet.Cells(i + 1, j + 1).WrapText = true;
}
//设置背景色
if(!domTd.getAttribute("name") | domTd.getAttribute("name") != "hzValue"){
oSheet.Cells(i + 1, j + 1).interior.colorIndex = 15;
}
//定义行高
if( flagTitle ){
oSheet.Rows(parseInt(i+1)+":"+parseInt(i+1)).RowHeight =25;
}else{
oSheet.Rows(parseInt(i+1)+":"+parseInt(i+1)).RowHeight =50;
}
//定义列宽
oSheet.Columns('A:A').ColumnWidth = 10;
oSheet.Columns('B:B').ColumnWidth = 40;
oSheet.Columns('C:C').ColumnWidth = 100;
oSheet.Cells(i + 1, j + 1).Borders.LineStyle = 1;
oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;
}
}
//oSheet.Columns.AutoFit; //自动适应大小
//oSheet.usercontrol = true;
}
补充：企业软件 , Lotus
