myflexcel.cpp

Delphi/BCB 各种版本都支持的Excel 读写控件.一成功应用在N个项目中 .

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "myflexcel.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma link "UExcelAdapter"
#pragma link "UFlexCelImport"
#pragma link "XLSAdapter"
#pragma resource "*.dfm"
#pragma alignment

TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::btnModifyClick(TObject *Sender)
{
  // Ask user for the folder containing the files to modify
  AnsiString DirIN = "C:\\";
  if (!SelectDirectory("Select folder with Excel files to read", "C:\\", DirIN)) return;
  DirIN=IncludeTrailingBackslash(DirIN);
  //Ask for destination folder
  AnsiString DirOUT = "C:\\";
  if (!SelectDirectory("Select folder to place modified files", "C:\\", DirOUT)) return;
  DirOUT=IncludeTrailingBackslash(DirOUT);

  //Iterate on all files from input folder
  TSearchRec sr;
  if (FindFirst(DirIN+"*.xls", faArchive, sr) == 0)
  {
    __try
    {
      do
      {
        if ((sr.Attr & faArchive) == faArchive)
        {
          if (!ModifyFile(DirIN, DirOUT, sr.Name)) return;
        }
      } while (FindNext(sr) == 0);
    }
    __finally
    {
      FindClose(sr);
    }
  }
  ShowMessage("Finished modifying files from "+DirIN+" to "+DirOUT);
}
//---------------------------------------------------------------------------


bool __fastcall TForm1::ModifyFile(AnsiString DirIN , AnsiString DirOUT , AnsiString FileName)
{
  Fi->OpenFile(DirIN+FileName);
  __try
  {
    //Always define the sheet we want to work on.
    //If not, the Active sheet will be the last sheet saved.
    Fi->ActiveSheet=1;

    //Create a cell format with blue bkground and red font, courier font and cell rotation = 45 degrees
    //Note that for using TFlxFormat we need to #include "UFlxFormats.hpp"
    TFlxFormat F;
    Fi->GetDefaultFormat(F);  //Initialize the format to the default
    F.Font.Name="Courier New";
    F.Font.ColorIndex=Fi->NearestColorIndex(clRed);
    F.Format="0.00";
    F.Rotation=45;
    F.FillPattern.FgColorIndex=Fi->NearestColorIndex(clBlue);
    F.FillPattern.Pattern= 2;// solid fill
    int Fmt0=Fi->AddFormat(F);

    //Apply format to Cell A1
    Fi->CellFormat[1][1]=Fmt0;

    //Save file to output folder.
    //Note FlexCel will NEVER overwrite an existing file (Even if it is the same file it opened), so we have to delete files by hand

    if (FileExists(DirOUT+FileName))
    {
      int r=Application->MessageBox(("File '"+DirOUT+FileName+"' already exists. Do you want to replace it?").c_str(), "Warning", MB_ICONQUESTION || MB_YESNOCANCEL);
      if (r==IDCANCEL) return false;
      if (r==IDNO) return true;
    }
    DeleteFile(DirOUT+FileName);
    Fi->Save(DirOUT+FileName);
  }
  __finally
  {
    Fi->CloseFile(); //Not really necesary (it only cleans memory and is called automatically when you close the app or load another file), just to save resources.
  }
  return true;
}
void __fastcall TForm1::btnCreateClick(TObject *Sender)
{
//Create a new file
  if (!SaveDialog->Execute()) return;
  if (FileExists(SaveDialog->FileName)) DeleteFile(SaveDialog->FileName);

  //We could also load a predefined file here instead of using newfile
  //Using a predefined file normaly can avoid a lot of formatting work.
  Fi->NewFile(3);

  Fi->CellValue[6][1]=int(Fi->ColorPalette[3]);  //We could use Fi->NearestColorIndex to set a color index based on a known color (as clblue)
  //Change a color on the color palette
  Fi->ColorPalette[3]=RGB(0,200,20);
  Fi->CellValue[6][2]=int(Fi->ColorPalette[3]);

  Fi->CellValue[3][3]=5;
  Fi->CellFormat[2][2]=Fi->CellFormat[1][1]; //Copy a cell format
  Fi->CellValue[2][1]="Test";
  Fi->CellValue[3][1]="T閟t";
  WideString w;
  w=wchar_t(0x266B);
  Fi->CellValue[4][1]=w;

  Fi->RowHeight[3]=1000;
  Fi->ColumnWidth[4]=2000;
  Fi->RowHeight[4]=4000;
  Fi->CellValue[1][3]=Fi->RowHeight[4];
  Fi->AutoRowHeight[4]=true;
  Fi->CellValue[1][4]=Fi->RowHeight[4];

  Fi->ActiveSheet=2;
  Fi->CellValue[1][1]=Fi->RowFormat[6];
  Fi->CellValue[1][2]=Fi->ColumnFormat[2];
  Fi->CellValue[6][1]="Testing";
  //Fi->CellValue[6][2]="Testing";
  Fi->CellValue[6][3]="Testing";
  Fi->RowFormat[6]=Fi->FormatListCount-1;
  Fi->ColumnFormat[2]=Fi->FormatListCount-2;
  Fi->CellValue[2][1]=Fi->RowFormat[6];
  Fi->CellValue[2][2]=Fi->ColumnFormat[2];
  Fi->CellValue[6][4]="Testing";

  //Add some user formats
  TFlxFormat F;
  Fi->GetDefaultFormat(F);
  F.Font.Name="Courier New";
  F.Format="0.00";
  F.Rotation=45;
  F.FillPattern.FgColorIndex=Fi->NearestColorIndex(clNavy);
  int Fmt0=Fi->AddFormat(F);

  Fi->GetDefaultFormat(F);
  F.Font.Name="Times New Roman";
  F.Borders.Left.Style=fbs_Medium_dashed;
  F.Borders.Left.ColorIndex=Fi->NearestColorIndex(clRed);
  F.FillPattern.FgColorIndex=Fi->NearestColorIndex(clBlue);
  F.FillPattern.BgColorIndex=0;
  F.FillPattern.Pattern= 5;
  int Fmt1=Fi->AddFormat(F);

  Fi->CellValue[10][1]=4;
  Fi->CellFormat[10][1]=Fmt1;
  Fi->CellValue[10][2]="Custom Format";
  Fi->CellFormat[10][2]=Fmt1;

  //TestFormulas
  Fi->CellValue[15][20]="This cell should be deleted by a formula";
  Fi->CellValue[16][20]=0;
  Fi->CellFormat[17][20]=Fmt0;
  Fi->CellFormula[15][20]="=Now()";
  Fi->CellFormula[15][20]="=Now()+1";
  Fi->CellFormula[16][20]="=1+(A2)*3^-1% & \"a\"\"b\"";
  Fi->CellFormula[17][20]="=Indirect(b5)+sum(sum(a$1:a$3),7,6,3.2)";
  Fi->CellFormula[18][1]="=-1^2";
  Fi->CellFormula[19][1]="=-if(true, if( false, 5, 2.2), 7)";
  Fi->CellFormula[20][1]="=-$IV65536";

  Fi->CellFormula[22][1]="=Indirect(b5)";
  Fi->CellFormula[23][1]="=sum(sum(a$1:a$3),7,6,3.2)";
  Fi->CellFormula[24][1]="=sum(a1:a2, 7,6,3.2)";
  Fi->CellFormula[25][1]="=sum(b1:b2)";
  Fi->CellFormula[26][1]="=sum($c$1)";
  Fi->CellFormula[27][1]="=sum($c$1, d4, 47, COUNT(A1:A2,A6,3))+count(a1:a5, 5)";
  Fi->CellFormula[28][1]="=Max(A1:A5,A6)+Average(a1:b7)-Min(a3)+Row(b7)-Column(b8)+Abs(a1)-Round(b2,2)";
  Fi->CellFormula[29][1]="=vlookup(\"a\",F3:H18,A22, false)";
  Fi->CellFormula[30][1]="=sin(pi())+Index(a1,1,1)";
  Fi->CellFormula[31][1]="=Match(a1,a1:h1,h2)+Trunc(b3)";
  Fi->CellFormula[32][1]="=hlookup(a1,F3:H18,A22)";
  Fi->CellFormula[33][1]="=choose(a1,a2,a3,a4)";
  Fi->CellFormula[34][1]="=cell(\"width\",a1)";
//  Fi->CellFormula[35][1]="=SUM(Hoja1:Hoja3!E5)";

  Fi->CellValue[5][2]="A1";
  for (int i=15; i<=35;i++) Fi->CellValue[i][2]=Fi->CellFormula[i][1];

  //Test insert sheet
  Fi->InsertEmptySheets(3,2);

  //Test Images
  Fi->ActiveSheet=3;
  TestImages();

  for (int i= Fi->PicturesCount-1; i>=0; i--)
    Fi->DeletePicture(i);    //Kind of silly... but good for testing
  Fi->ActiveSheet=3;
  TestImages();
  Fi->ActiveSheet=2;
  TestImages();
  Fi->ActiveSheet=1;
  TestImages();

  //Test Merged Cells
  Fi->ActiveSheet=3;
  Fi->MergeCells(1,1,3,5);
  Fi->MergeCells(-1,2,2,2); //an invalid one
  Fi->MergeCells(5,5,5,6); //horizontal
  Fi->MergeCells(6,6,7,6); //Vertical

  Fi->MergeCells(10,10,13,12);
  Fi->MergeCells(13,12,15,16); //expand an existing range

  Fi->MergeCells(20,1,20,3);
  Fi->MergeCells(22,1,22,3);
  Fi->MergeCells(19,3,23,3); //Merge multiple existing ranges.

  Fi->MergeCells(35,2,38,2);
  Fi->MergeCells(37,4,42,4);
  Fi->MergeCells(42,2,45,3);
  Fi->MergeCells(45,4,45,11);
  Fi->MergeCells(35,2,35,4);  //Cascading...

  //Test Zoom
  Fi->SheetZoom=75;
  Fi->SheetZoom=79; //this one modifies an existing one

  //Test Comments
  w="\0xFEEB\0xFEEB";
  Fi->SetCellComment(2,7,w);
  Fi->SetCellComment(3,7,"Regards");
  Fi->SetCellComment(1,1,"Test comment");
  Fi->SetCellComment(65536,256,"This is a last-cell comment!");


  Fi->Save(SaveDialog->FileName);
  Fi->CloseFile();
  //Just to test I can read what I wrote...
  Fi->OpenFile(SaveDialog->FileName);

  TFileStream* St= new TFileStream(SaveDialog->FileName, fmCreate);
  __try
  {
    Fi->SaveToStream(St);
  }
  __finally
  {
    St->Free();
  }
  Fi->CloseFile();

  //Just to test I can read what I wrote... again
  Fi->OpenFile(SaveDialog->FileName);
  Fi->CloseFile();
  ShowMessage("File "+SaveDialog->FileName+" created Ok");
}
//---------------------------------------------------------------------------


void __fastcall TForm1::TestImages()
{
  AnsiString ExePath= IncludeTrailingBackslash(ExtractFilePath(Application->ExeName));
  TImageProperties PicProps;
  PicProps.Col1=1;PicProps.Row1=3;PicProps.Col2=3;PicProps.Row2=6;
  PicProps.dx1=0;PicProps.dy1=0;PicProps.dx2=0;PicProps.dy2=0;
  PicProps.FileName="flexcel2.jpg";
  TMemoryStream* Ms = new(TMemoryStream);
  __try
  {
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel.bmp"),xli_Jpeg ,PicProps, at_MoveAndDontResize);
    PicProps.Row1+=3; PicProps.Row2+=3;PicProps.Col1++;PicProps.Col2++;
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel.bmp"),xli_Jpeg ,PicProps, at_MoveAndDontResize);
    PicProps.Row1+=3; PicProps.Row2+=3;PicProps.Col1++;PicProps.Col2++;
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel2.jpg"),xli_Jpeg ,PicProps, at_MoveAndDontResize);
    PicProps.Row1+=3; PicProps.Row2+=3;PicProps.Col1++;PicProps.Col2++;
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel2.jpg"),xli_Jpeg ,PicProps, at_MoveAndDontResize);
    PicProps.Row1+=3; PicProps.Row2+=3;PicProps.Col1++;PicProps.Col2++;
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel.bmp"),xli_Jpeg ,PicProps, at_MoveAndDontResize);
    Fi->DeletePicture(4);
    Fi->ClearPicture(1); //clear second pic
    PicProps.Row1+=3; PicProps.Row2+=3;PicProps.Col1++;PicProps.Col2++;
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel.bmp"),xli_Jpeg ,PicProps, at_MoveAndDontResize);
    PicProps.Row1+=3; PicProps.Row2+=3;PicProps.Col1++;PicProps.Col2++;
    Fi->AddPicture("",xli_Jpeg ,PicProps, at_MoveAndDontResize);

    Fi->AssignPicture(2,"",xli_Jpeg);

    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel.bmp"),xli_Jpeg,20,2, 0, 0, 150,80, at_MoveAndDontResize);
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel.bmp"),xli_Jpeg,20,2, 5, 5, 150,80, at_DontMoveAndDontResize);
    Fi->AddPicture(LoadImageAsJPEG(Ms, ExePath+"flexcel.bmp"),xli_Jpeg,20,2, 45, 45, 150,80, at_MoveAndResize);
  }
  __finally
  {
    Ms->Free();
  }
}

TStream* __fastcall TForm1::LoadImageAsJPEG(TStream* Ms, AnsiString FileName)
{
  if (UpperCase(ExtractFileExt(FileName))!=".JPG")
  { //We have to convert it to a JPEG
    TPicture* Pic = new TPicture();
    __try
    {
      TJPEGImage* Jp = new TJPEGImage();
      __try
      {
        Pic->LoadFromFile(FileName);
        Jp->Assign(Pic->Graphic);

        Ms->Position=0;
        Jp->SaveToStream(Ms);
        Ms->Position=0;
        return Ms;
      }
      __finally
      {
        Jp->Free();
      } //finally
    }
    __finally
    {
      Pic->Free();
    } //finally
  } else //File is already JPEG, we dont need to convert it
  {
    TFileStream* Fs= new TFileStream(FileName, fmOpenRead);
    __try
    {
      Ms->Position=0;
      Ms->CopyFrom(Fs, Fs->Size);
      Ms->Position=0;
      return Ms;
    }
    __finally
    {
      Fs->Free();
    } //finally
  }
  Ms->Size=0;
  return Ms;
}