I am having a difficult time finding where I can specify that a cell's
overall format is Text, Date, Currency, Number, etc. (The "Number"
category on Excel's Format Cells window.)
Is there such a setting?
I am trying to ensure that a cell that is text, stays text, even if I enter a string like 00012345.
Thanks!!
Hi,
For this kind of stuff the simplest way is to use APIMate (it is on the start menu if you have FlexCel installed, just search for it)
So for example, if you want to format cell A1 as text, go and format it as text in an empty file in Excel, save the file, and open it in APIMate
This is the code I get by doing the above procedure:
//Set the cell values
fmt := xls.GetCellVisibleFormatDef(1, 1);
fmt.Format := '@';
xls.SetCellFormat(1, 1, xls.AddFormat(fmt));
The format will change depending in what you want to format, but "@" is for text. (And text is used in Excel so if you type 000012345 it will be saved as a string, not a number). You might also format full columns or rows, and again, APIMate should tell you the commands.
PS: The '@' format doesn't affect FlexCel itself. FlexCel will always enter the data you pass to it, no matter the format. So for example:
xls.SetCellValue(1, 1, 000012345) will enter the number 12345.
xls.SetCellValue(1,1, '000012345') will enter the string '000012345'.
I have been using TFlexCelImport with a TXLSAdapter. I set the cell values using FlexCelImport1.CellValue[row,col] = '''Test'''. But that enters a value of 'Test', including the quotes.
I opened a test worksheet using APIMate like you said and I see everything is using TXlsFile. Should I not be using TFlexCellImport any longer?
Hi,
TXlsAdapter is really old, the best would be not to use it. You can still use TXlsxAdapter with TFlexCelImport, but again, for new code I would indeed move to TXlsFile. TFlexCelImport is maintained for compatibility of old code, but it is just a wrapper which calls TXlsFile.
TXlsFile will be faster, and you will also gain the ability to use APIMate to figure out stuff like this. And TXlsFile has a lot of methods not available in TFlexCelImport, like methods to add conditional formats or tables (and you can find out about all those methods with APIMate)
Now, in any case, what you would need to do is to call:
FlexCelImport1.CellValue[row,col] = 'Test';
or
FlexCelImport1.CellValue[row,col] = '01234'; to set the string 01234'
or
FlexCelImport1.CellValue[row,col] = 1234; to set the number 1234.
With XlsFile it would be respectively:
xls.SetCellValue(row, col, 'Test');
xls.SetCellValue(row, col, '1234');
xls.SetCellValue(row, col, 1234);
You will find that most methods in TFlexCelImport match directly with a method in TXlsFile. But indexed properties have been eliminated (because they caused all kind of issues with C++ builder).
So FlexCelImport.CellValue gets replaced by Xls.GetCellValue and xls.SetCellValue. Wherever you have Property[some index] it will now be GetProperty(index) and SetProperty(index, value).
Remember also that you can read more about the differences between FlexCel 3 and 6 here:
http://www.tmssoftware.biz/flexcel/doc/vcl/guides/migrating-from-flexcel-3.html
I deleted the adapter and import components, defined a TXLSFile component, made the necessary code modifications and it works great. The only thing that does not work is setting the font name.
I set the newfile to v2013 when I created the sheet. Then:
var fmt : TFlxFormat;
begin
fmt := xls.GetCellVisibleFormatDef(row,col);
fmt.Font.Name := 'Arial';
xls.SetCellFormat(row,col,xls.AddFormat(fmt));
But the cell stays on the default Excel font of Calibri. What am I doing wrong?
Hi,
The problem is likely that you didn't change the Font Scheme (which was introduced in Excel 2007, and has more priority than the font name). If a file has a "major" scheme, then it will be whatever the font defined in the major scheme is.
Try setting fmt.Font.Scheme := TFontScheme.None;
For more complete information, please read:
Thank you for all of your help! That worked just fine. :)