MS Excel usage examples
Excel database in the PmaAdo object
The
PmaAdo object is created, containing the
DtiOper method on the "
Methods" tab. This method represents the basic inteface for working with database tables and can be used, for example, by the
Preconfiguration "Panel with editable table viewer of data source" viewer.
The interface uses the
ADO technology that can be extended by additional functionality:
Example:
Creates new table data with columns nTime (time), 'flags' (integer) and 'value1' (real number).
JavaScriptVBScriptSelect and copy to clipboard
pMe.DbExecute("", "CREATE TABLE data (nTime date, flags integer, value1 double)", "");
pMe.DbExecute "", "CREATE TABLE data (nTime date, flags integer, value1 double)", ""Creating a new table (
CREATE TABLE) in the
Excel database means creating a new
Sheet.
Removing a table (DROP TABLE) however does not cause sheet removal, but only empties its content.
- The
GetObject method can be used for opening and getting reference to
MsExcel application. Then can be used the internal implementation of the
VBA language (
Visual Basic For Aplications) that is a part of the
Microsoft Office for managing Excel sheets and data content.
- One possible disadvantage could be the fact that reading or writing large ammounts of data can be very time consuming.
Example1:
Open the Test.xls file, with included 'Sheet1'. Read the value from the "B3" cell and assign it into the a variable. Write the value of 3.14 into the "D7" cell. Display the MsExcel application. Save and close the application.
JavaScriptVBScriptSelect and copy to clipboard
var oWbs, oExcelApp, oSheet, a;
oWbs = Pm.AxGetObject("new", "Excel.Application");
oExcelApp = oWbs.WorkBooks.Open("c:\\Data\\Test.xls", 3, false, 4);
oSheet = oExcelApp.Sheets("Sheet1");
a = oSheet.Cells(3, 2).Value;
oSheet.Cells(7, 4).Value = 3.14;
oExcelApp.Parent.Windows(1).Visible = true;
oExcelApp.Save();
oExcelApp.Application.Quit();
Dim oWbs, oExcelApp, oSheet, a
Set oWbs = Pm.AxGetObject("new", "Excel.Application")
Set oExcelApp = oWbs.WorkBooks.Open("c:\Data\Test.xls", 3, false, 4)
Set oSheet = oExcelApp.Sheets("Sheet1")
a = oSheet.Cells(3, 2).Value
oSheet.Cells(7, 4).Value = 3.14
oExcelApp.Parent.Windows(1).Visible = true
oExcelApp.Save
oExcelApp.Application.QuitExample2:
Creates new EXCEL file.
Display the MsExcel application.
Creates sheets (according to default Excel settings).
Creates a reference to first sheet.
Write values into the first sheet, save and close the application.
It is possible to export the content of the file into pdf format (since version Microsoft Office 2007).
JavaScriptVBScriptSelect and copy to clipboard
var oExcelApp, oWorkbook, oSheet;
oExcelApp = Pm.AxGetObject("new", "Excel.Application");
oExcelApp.Visible = true;
oWorkbook = oExcelApp.WorkBooks.Add;
oSheet = oExcelApp.Sheets(1);
oSheet.Cells(1, 1).Value = oWorkbook.Sheets.Count;
oSheet.Cells(1, 2).Value = 34;
oSheet.Cells(2, 1).Value = Pm.Time;
oSheet.Cells(2, 1).NumberFormat = "d/m/yyyy h:mm:ss";
oExcelApp.ExportAsFixedFormat(0, "C:\\Data\\Test\\Report.pdf", , , , , true);
// Export to pdf format file
oSheet.SaveAs("C:\\Data\\Test.xls");
oExcelApp.Quit();
Dim oExcelApp, oWorkbook, oSheet
Set oExcelApp = Pm.AxGetObject("new", "Excel.Application")
oExcelApp.Visible = true
Set oWorkbook = oExcelApp.WorkBooks.Add
Set oSheet = oExcelApp.Sheets(1)
oSheet.Cells(1, 1).Value = oWorkbook.Sheets.Count
oSheet.Cells(1, 2).Value = 34
oSheet.Cells(2, 1).Value = Pm.Time
oSheet.Cells(2, 1).NumberFormat = "d/m/yyyy h:mm:ss"
oExcelApp.ExportAsFixedFormat 0, "C:\Data\Test\Report.pdf", , , , , true
' Export to pdf format file
oSheet.SaveAs "C:\Data\Test.xls"
oExcelApp.Quit