I am a bit confused on how to change the color of a cell, for Font.ColorIndex, FillPattern.FgColorIndex, and FillPattern.BgColorIndex.
I am assuming that I somehow need to add the color to the ColorPalette and then select that index, but do not see how that is done.
Using FlexCel v3 on D2009.
Hi,
Short answer: FillPattern.FgColorIndex := FlexCelImport.NearestColorIndex(clBlue);
You can also change the color palette with FlexCelImport.ColorPalette
Long answer: The rabbit hole goes deep, so it will be a long answer indeed. But let's start by the beginning:
1)Before going to colors themselves, let me note a thing that can be confusing. As you mention in the question, there are 3 properties for the color of a cell: Font.ColorIndex, FgColorIndex and BgColorIndex.
This can be confusing because you might assume that FgColorIndex is the "foreground" color of the cell, but the "foreground" color is Font.ColorIndex. Both Fg and BgColorIndex apply to the FillPattern, so they both apply to the background of the cell. This is used because you might have a "hatch" fill, like say diagonal lines, and then FgColor would be the color of the lines and BgColor the color of the background. But for a solid fill (which is what everybody uses anyway), FgColor completely covers BgColor, so it doesn't matter the color you set to BgColor. (which is what most people try to modify).
So, in short, for solid fill change Font.ColorIndx and Pattern.FGColorIndex
2)Now, going to the colors themselves. Excel 2003 and FlexCel 3 only see indexed colors. You have a palette of 56 colors. That's the only colors you can show in a spreadsheet. Excel 2007 (and FlexCel 5/6) support true colors: you are not restricted anymore to a palette.
This is one of the reasons why really, if possible, it is best to use FlexCelGrid with XlsxAdapter: It won't only recalculate, but also show the true colors. But you need XE or newer for that.
As an example, here is how a file created with Excel 2010 looks in Excel 2010:
And here is the same file when opened in Excel 2003:
As you can see, the colors were modified so they are one of the 56 colors available in the Excel 2003 palette.
But here is the thing: This is the file opened in FlexCelGrid with XlsxAdapter:
And here with XlsAdapter:
As you can see, colors in XlsAdapter are similar to Excel 2003, because FlexCel 3 doesn't know about anything but indexed colors. XlsxAdapter in the other hand uses FlexCel 6, so it can show the same colors as Excel 2010.
So for FlexCel 3 the best you can do is to set the colors as said at the beginning in the short answer. If you want, you can first modify the palette so the colors available are the ones you need. Then, you need to set the colorindexes, and you can use NearestColorIndex to convert an rgb color into the nearest colorindex.
If someday you can migrate from Delphi 2009 to a newer version and use XlsxAdapter, then the best solution is to set the true colors in the file. You can't do that with FlexCelImport, but you can access the FlexCel 6 "TXlsFile" object the same way as you did for recalculation. So you would write something like:
(FlexCelImport.GetWorkbook as TXlsxFile).GetTWorkbook.SetCellFormat(v6FlxFormat);
where TFlxFormat in version 6 has colors instead of colorindexes.
I've written more about how Excel 2007 changed the way colors worked in here:
You know more about Excel than Microsoft does!! :)
This works perfectly for what I need. I also figured out that changing the fill color to "None" requires a TFlxPatternStyle_None (changing from Solid).
Also, your FlexCel_XlsAdapter.TXLSFile is working perfectly without any hiccups. I have controls for changing fonts, point sizes, bold, italics, underline, text alignment, font color and cell color. It copies cells, cell formulas, and cell formats.
I do have a question about copying formulas. (I think you already know what I am going to ask!) If I copy a cell that has the formula =$b5+c5 and paste it one cell to the right, it would be great if the formula changed to =$b5+d5. Do you have any special copy and paste procedures for formulas?
(This also opens a huge can of worms for inserting and deleting columns and rows.)
Sorry to be such a pain!
Rick
Hi,
For deleting rows and columns, you can use FlexCelImport.DeleteRows and DeleteCols. They will adapt all the formulas, charts, whatever so say A5 becomes A4 but A$5 stays A$5
For copying and inserting at the same time, you have InsertAndCopyRows and InsertAndCopyCols. Those methods also are fully aware of formulas,so C5 will become D5. FlexCel is actually built around the InsertAndCopy functionality, because I originally created as an Excel report tool, and to do reports I mainly needed a way to insert and copy rows. So it isn't an afterthought, FlexCel is designed so copying and inserting can be very fast and correct. (By correct I mean work as Excel does). In fact, for some border cases, we behave even better than Excel, but that's for another post :)
Now, in v3 you can only copy or delete full rows and columns. In FlexCel 6, we have a new overcharged "InsertAndCopyRange" method that can insert, copy or insertandcopy at the same time full rows, full columns or arbitrary ranges of cells, from a sheet to the same sheet, to a different sheet or to a different file. It manages to keep all the formulas correct, those copied and those inserted. FlexCel 6 also has a DeleteRange and MoveRange methods that can do all the rest of cell manipulation you can do in Excel.
So, in case you want to copy arbitrary ranges, you might need to use InsertAndCopyRange from v6. Sadly this wasn't implemented in the dll I sent you (it was just an oversight), but I have implemented it now and I will send you a link for the new dll by private mail.
The steps to use it would be similar to the Recalc() method: You save the FlexCelImport into a MemoryStream, then open the memoryStream with the FlexCel 6 dll, then do the InsertAndCopy, then save back again to the memory stream and open it again with the FlexCelImport. Yes, convoluted, but at least it works. (as said, this is much easier if you are in Delphi XE+).
Code would be something like:
procedure CopyRange(const stream: TMemoryStream;
const range: VCL_FlexCel_Core.TXlsCellRange;
const destRow, destCol: integer);
var
xls: FlexCel_XlsAdapter.TXlsFile;
begin
xls := FlexCel_XlsAdapter.TXlsFile.Create();
try
stream.Position := 0;
xls.Open(stream);
xls.InsertAndCopyRange(range, destRow, destCol, 1, TFlxInsertMode_ShiftRangeDown, TRangeCopyMode_All);
stream.Clear;
xls.Save(stream);
stream.Position := 0;
finally
xls.Free;
end;
end;
And you would call it like:
procedure TForm1.Button1Click(Sender: TObject);
var
ms: TMemoryStream;
range: VCL_FlexCel_Core.TXlsCellRange;
begin
FlexCelImport1.OpenFile('r:\test.xls');
ms := TMemoryStream.Create;
try
FlexCelImport1.SaveToStream(ms);
range.Left := 1;
range.Top := 1;
range.Bottom := 19;
range.Right := 15;
CopyRange(ms, range, 20, 20);
FlexCelImport1.LoadFromStream(ms);
finally
ms.Free;
end;
FlexCelImport1.Save('r:\newtest.xls');
end;
Note that in thie CopyRange method I am calling:
xls.InsertAndCopyRange(range, destRow, destCol, 1, TFlxInsertMode_ShiftRangeDown, TRangeCopyMode_All);
As said, InsertAndCopyRange is a very overloaded method, and it allows many other scenarios. You could for example change TFlxInsertMode_ShiftRangeDown to TFlxInsertMode_NoneDown and the existing cells will be overwritten by the new cells, instead of moved down. Or you could change it to copy the full row. Or to only insert, by using TRangeCopyMode_None. Or copy only formats with TRangeCopyMode_Formats. The possiblities are a lot.
Again, I appreciate the DLL and help. You have excellent customer service! I have already compiled the 6617.zip file and will be testing it later today or tomorrow. I'll let you know how it goes.
Where are TFlxInsertMode_ShiftRangeDown and TRangeCopyMode_All declared?
Sorry, forgot to mention it `(and I was going to). Just add FlexCelDynEnums to the uses clauses. In older delphi versions enum aren't scoped, so you can't declare them in FlexCel_Core as FlexCel 6 does. You need to use the unit with the enums directly.
I am struggling with the InsertAndCopyRange. Nothing is happening.
I copied your CopyRange procedure and changed the TFlxInsertMode_ShiftRangeDown to TFlxInsertMode_NoneDown so that it would overwrite cells.
I am assuming that if I wanted to copy cell A3, that:
Top = 3
Bottom = 3
Left = 1
Right = 1
I then save the stream, issue the CopyRange(ms, range, 4, 1) to copy A3 to A4, and LoadFromStream. To get it back on the FlexCelGrid, I issue a recalculate (which does a savetostream, recalc, loadfromstream, and finally a LoadSheet). -- Note: I have also tried simply to do a grid.loadsheet right after the loadfromstream and that did nothing as well.
I thought that maybe I
was not linking in the right DCU files, but my Delphi library is
pointing to the v6617 folder. Then I remembered that the FlexCelDyn.dll
needed to be copied to the folder I am running from. However, the v6617
FlexCelDyn.dll has the same date as the v6611 FlexCelDyn.dll. Could that
be the problem?
Hi,
Note that you can dom omre than one action at the time. That is: instead of save->copy->load->Save->recalc->Load, you can do: save->copy->recalc->load
This seems like a problem in the dll indeed. In the first one I sent InsertAndCopyRange did nothing.
But it is strange: I just downloaded the file from our webserver, and FlexCelDyn.dll is dated feb-28-2015, the day I sent the link. Which dates are the dlls you have?
I just downloaded again and on one PC, it sees the DLL dated 2/19 and on another it sees 2/28. WEIRD. I re-extracted from the PC that sees it correctly and then compiled it.
What is the size of the FlexCelDyn.dll file? The one I have is 5523k dated 2/28/2015 3:47pm.
I am still getting nothing after all of that. Perhaps my code is wrong....but it looks like yours. I have separate copy and paste buttons.
I will copy in the routines. Before you go through them, please understand that I am an old-school programmer and code things differently than others. My variables are prefixed as:
W_ : Function or routine variable only
L_ : Private (local) variable for the unit
X_ : Function or routine passed-in variable
EF : These are fields from the Form. Most likely, TEdit fields. EFCellValue gets filled by the SelectCell method. It contains the value or formula of the current selected cell.
My copy routine is:
procedure TFrameXPayoff2.SBXCopyCellClick(Sender: TObject);
{* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Copy}
var
W_Value : TXlsCellValue;
begin
L_CopyFromRow := L_XLSRec.Row; // L_XLSRec is a record that contains info for the selected cell
L_CopyFromCol := L_XLSRec.Col;
W_Value := FlexCelImportPO.Cell[L_XLSRec.Row,L_XLSRec.Col];
If W_Value.IsFormula
then begin
L_Fmla := FlexCelImportPO.CellFormula[L_XLSRec.Row,L_XLSRec.Col];
EFCellValue.SelectAll; // If I can get the InsertAndCopyRange to work, some of this code will be deleted
EFCellValue.CopyToClipboard;
end
else begin
L_Fmla := '';
FlexCelGridPO.CopyToClipboard;
end;
SBXCopyCellFmtClick(Sender); // SBXCopyCellFmt is a speedbutton that simply copies the format
If Sender = SBXCutCell then begin // If I am cutting the cell, this clears the cell
FlexCelImportPO.CellValue[L_XLSRec.Row,L_XLSRec.Col] := '';
FlexCelImportPO.CellFormat[L_XLSRec.Row,L_XLSRec.Col] := 0;
SBXRecalcGridClick(Sender); // This does a recalc to freshen the grid after the cut
end;
end;
Essentially, what I am looking for in the copy are the row and col of the cell that is to be copied. Those are in the L_CopyFrom... variables. (I am waiting to see what exactly gets copied from the new InsertAndCopyRange before I simplify the Copy procedure.)
My paste routine looks like this:
procedure TFrameXPayoff2.SBXPasteCellClick(Sender: TObject);
{ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Paste}
var
W_MS : TMemoryStream;
W_Range : VCL_FlexCel_Core.TXlsCellRange;
begin
If L_Fmla = ''
then FlexCelGridPO.PasteFromClipboard
else begin
W_MS := TMemoryStream.Create;
try
FlexCelImportPO.SaveToStream(W_MS);
W_Range.Left := L_CopyFromCol;
W_Range.Right := L_CopyFromCol;
W_Range.Top := L_CopyFromRow;
W_Range.Bottom := L_CopyFromRow;
CopyRange(W_MS,W_Range,L_XLSRec.Row,L_XLSRec.Col);
FlexCelImportPO.LoadFromStream(W_MS);
finally
W_MS.Free;
end;
end;
{ Eventually, this will be uncommented in order to copy the format as well (if needed)
SBXPasteCellFmtClick(Sender);
}
SBXRecalcGridClick(Sender);
end;
Here is CopyRange:
procedure TFrameXPayoff2.CopyRange(const stream: TMemoryStream;
const range: VCL_FlexCel_Core.TXlsCellRange;
const destRow, destCol: integer);
{ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Copies a cell range}
var
xls: FlexCel_XlsAdapter.TXlsFile;
begin
xls := FlexCel_XlsAdapter.TXlsFile.Create();
try
stream.Position := 0;
xls.Open(stream);
xls.InsertAndCopyRange(range, destRow, destCol, 1, TFlxInsertMode_NoneDown, TRangeCopyMode_All);
stream.Clear;
xls.Save(stream);
stream.Position := 0;
finally
xls.Free;
end;
end;
As you can see, this is your code verbatim. Here is the grid recalc:
procedure TFrameXPayoff2.SBXRecalcGridClick(Sender: TObject);
{ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Recalculate the grid}
var
W_Col : Integer;
W_Row : Integer;
W_MS : TMemoryStream;
begin
L_ProcessCellChange := False; // This temporarily disables the setting of the row and col in my SelectCell routine
W_Row := L_XLSRec.Row;
W_Col := L_XLSRec.Col;
W_MS := TMemoryStream.Create;
Try
FlexCelImportPO.SaveToStream(W_MS);
RecalcXLS(W_MS);
FlexCelImportPO.LoadFromStream(W_MS);
FlexCelGridPO.LoadSheet;
FlexCelGridPO.Row := W_Row;
FlexCelGridPO.Col := W_Col;
Finally
W_MS.Free;
End;
L_ProcessCellChange := True;
end;
And finally. RecalcXLS:
procedure TFrameXPayoff2.RecalcXLS(Const X_Stream: TMemoryStream);
{ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
Recalculate the grid}
var
W_XLS : FlexCel_XlsAdapter.TXLSFile;
begin
W_XLS := FlexCel_XlsAdapter.TXlsFile.Create();
Try
X_Stream.Position := 0;
W_XLS.Open(X_Stream);
W_XLS.Recalc(True);
X_Stream.Clear;
W_XLS.Save(X_Stream);
X_Stream.Position := 0;
Finally
W_XLS.Free;
End;
end;
Hi,
Yes, that seems correct. Make sure this dll is the one in the same folder as your exe.
I don't see a priori anything wrong with the code. But before continuing, let me explain a little how copy-paste works.
There are 2 different ways to "copy paste": Through the clipboard and directly. this applies to FlexCel, to Excel, and to Open/LibreOffice
But to get a better idea, the simplest way is to see it with Excel itself.Let's do an experiment:
1)Open Excel and create an empty file.
2)In A2, write the formula =A1
3)Ctr-copy in A2, then select A3:A5 and paste.
You will see A3 has =A2, A4 = A3, etc, as expected. But this copy-paste didn't went trough the clipboard, Excel copied the values directly. So now let's try to make it go thourgh the clipboard. let's do:
1)Open Excel
2)Write =A1 in A2
3)Ctrl-Copy
4)Now, close Excel completely. Don't save the file. If you have other documents open, close them too. The data is still in the clipboard, but we don't want Excel to be able to "cheat" and copy directly. We want it to go though the clipboard.
5)After completely closing Excel, Open it again.
6)Select A3:A5 as before and paste.
If you did things right, now Excel will drop a warning saying that the areas aren't the same. Press ok, and it will paste a "'0" in A3. and nothing in A4:A5. And no formula either.
If you do it in LibreOffice, you will see the same behavior: Copy-paste through the clipboard loses a lot of stuff.
In FlexCel, we have a similar situation. FlexCel is a little smarter than Excel or libre office, so it won't paste a "0" from the clipboard, it will paste the formula "=A1". But, it won't modify the formulas to be =A2, = A3, etc. This would be very hard to do, because you need to guess what is what you have in the clipboard.
So FlexCel also has a "direct copy paste" which doesn't go through the Clipboard: This is InsertAndCopyRange. But same as Excel, this is direct, it doesn't use the clipboard at all. All InsertAndCopyRange does is to copy cells from one place to another (and it doesn't use the clipboard).
Ok, now with the basics covered, let's try to simplify the code.
1)Create a new empty app
2)Add a FlexCelImport and an XlsAdapter, and set the adapter of the FlexCelImport to be the XlsAdapter.
3)Add a button, double click it, and in the event handler write:
procedure TForm1Button1Click(Sender: TObject);
var
ms: TMemoryStream;
range: VCL_FlexCel_Core.TXlsCellRange;
begin
FlexCelImport1.OpenFile('r:\test.xls');
ms := TMemoryStream.Create;
try
FlexCelImport1.SaveToStream(ms);
range.Left := 1;
range.Top := 1;
range.Bottom := 19;
range.Right := 15;
CopyRange(ms, range, 20, 1);
FlexCelImport1.LoadFromStream(ms);
finally
ms.Free;
end;
FlexCelImport1.Save('r:\newtest.xls');
end;
4)Also write this code before the event:
procedure CopyRange(const stream: TMemoryStream;
const range: VCL_FlexCel_Core.TXlsCellRange;
const destRow, destCol: integer);
var
xls: FlexCel_XlsAdapter.TXlsFile;
begin
xls := FlexCel_XlsAdapter.TXlsFile.Create();
try
stream.Position := 0;
xls.Open(stream);
xls.InsertAndCopyRange(range, destRow, destCol, 1, TFlxInsertMode_ShiftRangeDown, TRangeCopyMode_All);
stream.Clear;
xls.Save(stream);
stream.Position := 0;
finally
xls.Free;
end;
end;
5)Make sure to copy the last dll I sent in the exe folder.
6)Run the app. I've done it here, and the range gets copied. So if you aren't getting the range copied, then we need to look more. But let's start by seeing if this code works the same for you as it does for me.
Nope. Nothing got copied. The resulting newtest.xls is identical to the test.xls.
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, VCL_FlexCel_Core, FlexCel_XLSAdapter, FlexCelDynEnums,
UExcelAdapter, XLSAdapter, UFlexCelImport;
type
TForm1 = class(TForm)
FlexCelImport1: TFlexCelImport;
XLSAdapter1: TXLSAdapter;
Button1: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure CopyRange(const stream: TMemoryStream;
const range: VCL_FlexCel_Core.TXlsCellRange;
const destRow, destCol: integer);
var
xls: FlexCel_XlsAdapter.TXlsFile;
begin
xls := FlexCel_XlsAdapter.TXlsFile.Create();
try
stream.Position := 0;
xls.Open(stream);
xls.InsertAndCopyRange(range, destRow, destCol, 1, TFlxInsertMode_ShiftRangeDown, TRangeCopyMode_All);
stream.Clear;
xls.Save(stream);
stream.Position := 0;
finally
xls.Free;
end;
end;
procedure TForm1.Button1Click(Sender: TObject);
var
ms: TMemoryStream;
range: VCL_FlexCel_Core.TXlsCellRange;
begin
FlexCelImport1.OpenFile('N:\D2009Projects\Test\FlexCel\test.xls');
ms := TMemoryStream.Create;
try
FlexCelImport1.SaveToStream(ms);
range.Left := 1;
range.Top := 1;
range.Bottom := 19;
range.Right := 15;
CopyRange(ms, range, 20, 1);
FlexCelImport1.LoadFromStream(ms);
finally
ms.Free;
end;
FlexCelImport1.Save('N:\D2009Projects\Test\FlexCel\newtest.xls');
end;
end.
The only thing I added were the items in the Uses at the top. I also revised the XLS test and newtest files. Other than that, it should have worked.
So, that tells me there is something wrong on my PC. I am suspicious that the DLL is not the right one. If you want, you can email it directly to me as an attachment.
I've sent you en email with the test, let me know how it goes.
All is well! I thought I had altered my Library list to the updated routines and I had not. Thank you again for your excellent support!