Frequently Asked Component Specific Questions
Options |
|
Display all FAQ items |
Displaying items 1 to 1 of 1, page 1 of 1
<< previous next >>
TMS FlexCel for VCL & FMXHow to create an Excel file with a gradient in a cell, a protected sheet and an autofilter
Excel files can be very complex, and guessing how to for example add a gradient to a cell, or protect a sheet, or add an autofilter can get difficult.
To make it easier, FlexCel provides a very useful tool named APIMate that can "convert" an Excel file into FlexCel code, for either Delphi or C++ Builder.
It works like this: You create a file in Excel, and open it in APIMate (without closing Excel). It will tell you the Delphi or C++ code needed to create the file. Then you can make changes in Excel, save the file, and press “Refresh” in APIMate (all without closing Excel). It will tell you the code you need to modify the file from the old state to the new one.
We are now in position to answer the question at the top of this section: How to create a file with a gradient in a cell, a protected sheet and an autofilter? I created a simple file with those things in Excel, and APIMate gives back this code:
procedure CreateFile(const xls: TExcelFile);
var
fmt: TFlxFormat;
GradientStops: TArray<TGradientStop>;
SheetProtectionOptions: TSheetProtectionOptions;
begin
xls.NewFile(1, TExcelFileFormat.v2010); //Create a new Excel file with 3 sheets.
//Set the names of the sheets
xls.ActiveSheet := 1;
xls.ActiveSheet := 1; //Set the sheet we are working in.
//Global Workbook Options
xls.OptionsCheckCompatibility := false;
//Printer Settings
xls.PrintXResolution := 600;
xls.PrintYResolution := 600;
xls.PrintOptions := [TPrintOptions.Orientation];
fmt := xls.GetCellVisibleFormatDef(3, 1);
fmt.FillPattern.Pattern := TFlxPatternStyle.Gradient;
SetLength(GradientStops, 2);
GradientStops[0].Position := 0;
GradientStops[0].Color := TExcelColor.FromTheme(TThemeColor.Background1);
GradientStops[1].Position := 1;
GradientStops[1].Color := TExcelColor.FromTheme(TThemeColor.Accent1);
fmt.FillPattern.Gradient := TExcelLinearGradient_Create(GradientStops, 90);
xls.SetCellFormat(3, 1, xls.AddFormat(fmt));
//AutoFilter
xls.SetAutoFilter(1, 3, 5);
//Protection
SheetProtectionOptions := TSheetProtectionOptions.Create(false);
SheetProtectionOptions.Contents := true;
SheetProtectionOptions.Objects := true;
SheetProtectionOptions.Scenarios := true;
SheetProtectionOptions.SelectLockedCells := true;
SheetProtectionOptions.SelectUnlockedCells := true;
xls.Protection.SetSheetProtection(''******'', SheetProtectionOptions);
end;
APIMate is not the panacea for all the questions, but it helps a lot answering many of those "how do I?" doubts. Remember to use it.