1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
|
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include <utilcls.h>
// #include <ComObj.hpp> // a ajouter dans certains cas
#include "Unit1.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
// les Variants doivent etre declares dans le .h
// ils ne sont place la que par commodite pour le test
Variant vMSExcel, vXLWorkbook, vXLWorkbooks, vWorksheet;
Variant vDestination, vConnection;
Variant vActiveWorkbook, vActiveSheet;
//Variant vFileName;
String filename, NomFichier;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
// acquisition de chemin de la base
if(OpenDialog1->Execute())
{
filename = OpenDialog1->FileName; // on ouvre la base
}
vMSExcel = Variant::CreateObject("Excel.Application");
vMSExcel.OlePropertySet("Visible", true);
vXLWorkbooks = vMSExcel.OlePropertyGet("Workbooks");
vXLWorkbook = vXLWorkbooks.OleFunction("Add");
vWorksheet = vXLWorkbook.OlePropertyGet("Worksheets", 1);
// on selectionne la Feuil1
vWorksheet.OleProcedure("Select");
vActiveWorkbook = vMSExcel.OlePropertyGet("ActiveWorkbook");
vActiveSheet = vActiveWorkbook.OlePropertyGet("ActiveSheet");
// chaine de connection a la base Access
String provider = "ODBC;DSN=MS Access Database;"
// "DBQ=C:\maBddessai.mdb;"
"DBQ=" + WideString(filename) + ";" // " + WideString(filename) + "
"DefaultDir=C:;DriverId=25;FIL=MS Access;"
"MaxBufferSize=2048;PageTimeout=5;";
vDestination = vActiveSheet.OlePropertyGet("Range", "A1"); // on selectioone la premiere cellule en haut a gauche
// la table est triee par prenom, nom, id, option
String requette = "SELECT Etudiant.id, Etudiant.nom, Etudiant.prenom, Etudiant.option "
"FROM " + WideString(filename) + ".Etudiant Etudiant "
"ORDER BY "
"Etudiant.prenom, Etudiant.nom, Etudiant.id, Etudiant.option";
// on affiche le contenu de la table
vConnection = vActiveSheet.OlePropertyGet("QueryTables").OleFunction("Add", WideString(provider), vDestination, WideString(requette));
vConnection.OlePropertySet("Name", "Lancer la requête à partir de MS Access Database");
vConnection.OlePropertySet("FieldNames", true);
vConnection.OlePropertySet("RowNumbers", false);
vConnection.OlePropertySet("FillAdjacentFormulas", false);
vConnection.OlePropertySet("PreserveFormatting", true);
vConnection.OlePropertySet("RefreshOnFileOpen", false);
vConnection.OlePropertySet("BackgroundQuery", true);
vConnection.OlePropertySet("RefreshStyle", 1); // xlInsertDeleteCells = 1 constante Excel
vConnection.OlePropertySet("SavePassword", false);
vConnection.OlePropertySet("SaveData", true);
vConnection.OlePropertySet("AdjustColumnWidth", true);
vConnection.OlePropertySet("RefreshPeriod", 0);
vConnection.OlePropertySet("PreserveColumnInfo", true);
vConnection.OleFunction("Refresh");
}
//--------------------------------------------------------------------------- |
Partager