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 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114
|
//---------------------------------------------------------------------------
#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
#include "utilcls.h" // <-----------
#include "StrUtils.hpp" // <------------
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
Variant vMSExcel, vXLWorkbooks, vXLWorkbook, vWorksheet;
Variant vFileName;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
try
{
vMSExcel = Variant::GetActiveObject("Excel.Application");
}
catch(...)
{
vMSExcel = Variant::CreateObject("Excel.Application");
}
vMSExcel.OlePropertySet("Visible", true);
vFileName = ("C:\\Users\\home\\Documents\\PharmaExcel\\pharma\\pharma.xls");
vXLWorkbooks = vMSExcel.OlePropertyGet("Workbooks");
vXLWorkbook = vXLWorkbooks.OleFunction("Open", WideString(vFileName));
// ici le fichier pharma.xls est charge dans la feuille 1
vWorksheet = vXLWorkbook.OlePropertyGet("Worksheets", 1);
// on arrete le rafrechissement ecran
vMSExcel.OlePropertySet("ScreenUpdating",false);
// on efface les colonnes
// de C a L, N, P, R, T, V, X, Z, AB, AD, AF, AH
vWorksheet.OlePropertyGet("Range", "C:L").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "N:N").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "P:P").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "R:R").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "T:T").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "V:V").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "X:X").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "Z:Z").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "AB:AB").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "AD:AD").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "AF:AF").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
vWorksheet.OlePropertyGet("Range", "AH:AH").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
// addition des colonnes
// M - O - Q - S - U - W - Y - AA - AC - AE - AG - AI
// on ecrit les formules d'addition pour chaque colonne
// derniere ligne
int finCol = vWorksheet.OlePropertyGet("Range", "A1").OlePropertyGet("SpecialCells", 11).OlePropertyGet("Row");
// ecriture de la formule en C2
// le resultat de la somme de la colonne M sera en C1
vWorksheet.OlePropertyGet("Range", "L2").OlePropertySet("Formula", WideString("=SOMME(M2:AI2"));
// recopie la formule jusque en bas de la colonne C
Variant fillRange = vWorksheet.OlePropertyGet("Range", WideString("L2:L" + IntToStr(finCol)));
vWorksheet.OlePropertyGet("Range", "L2:L2").OleProcedure("AutoFill", fillRange);
vWorksheet.OlePropertyGet("Range", WideString("L2:L" + IntToStr(finCol))).OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("Copy");
vWorksheet.OlePropertyGet("Range", WideString("C2:C" + IntToStr(finCol))).OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("PasteSpecial", -4163, -4142, false, false);
vMSExcel.OlePropertySet("CutCopyMode", false);
// on efface les cellules de M a AI
vWorksheet.OlePropertyGet("Range", "L:AI").OleProcedure("Select");
vMSExcel.OlePropertyGet("Selection").OleProcedure("ClearContents");
// trie de C inutile
int depart = 2;
for(finCol; finCol >= depart; finCol--)
{
// on recupere le contenu de la cellule
// Cells ligne, colonne
int vCell = vWorksheet.OlePropertyGet("Cells", finCol, 3).OlePropertyGet("Value");
String vCell1 = vWorksheet.OlePropertyGet("Cells", finCol, 1).OlePropertyGet("Value");
// Supression en C supérieur à 7
// Supression en A supérieur à 3400949999999
// Supression en A inférieure a 3400920000000
if(vCell <= 6 || vCell1 > 3400949999999 || vCell1 < 3400920000000)
{
vWorksheet.OlePropertyGet("Rows", finCol).OleFunction("Select");
vMSExcel.OlePropertyGet("Selection").OleFunction("Delete", -4162);
}
else
{
int lng = vCell1.Length();
String vCellMid = MidStr(vCell1, 6, lng - 6);
vWorksheet.OlePropertyGet("Cells", finCol, 1).OlePropertySet("Value", WideString(vCellMid));
}
}
int fin = vWorksheet.OlePropertyGet("Range", "A2").OlePropertyGet("SpecialCells", 11).OlePropertyGet("Column");
Variant vCol, vRange;
vCol = vWorksheet.OlePropertyGet("Columns", 1);
// Cells ligne, colonne
vRange = vWorksheet.OlePropertyGet("Range", vWorksheet.OlePropertyGet("Cells", 1, 1), vWorksheet.OlePropertyGet("Cells", finCol, fin));
vRange.OleProcedure("Sort", vCol, 1); // 1 xlAscending , 2 xlDescending
// on retabli le rafraichissement ecran
vMSExcel.OlePropertySet("ScreenUpdating",true);
} |