The Microsoft Excel software uses the apostrophe character to indicate that a literal value, or string literal, is entered into the cell. For instance, if you type "1+1" into an Excel cell, the result is displayed as "2." To display the actual text "1+1," you place an apostrophe as the first character. The apostrophe is also used when importing content into an Excel spreadsheet.
Now the problem is how to remove the apostrophe from the cell programmatically?
Hi,
I am unsure if you want to just remove the apostrophe so it doesn't show, or if you want to convert the text to a number or formula.
So just in case, I will explain the concepts first.
The apostrophe comes from lotus 123, but it isn't really saved in the cell, as you might have noticed if you tried to retrieve the text with FlexCel. If you write '1 in a cell in Excel, it will save the string "1" (without apostrophe) and set the format to "123 prefix". But what really matters is that the datatype of the cell is string.
Other way to easily see that the apostrophe is not a part of the data is if you write 'hello in A1 and 'world in B1, and then =A1&B1 in A2. A2 will contain helloworld and not 'hello'world
In FlexCel, what you set depends on the datatype that you pass to the SetCellValue call. For example:
xls.SetCellValue(1, 1, 1) will set the number 1 in cell A1.
but
xls.SetCellValue(1, 1, "1") will set the string "1" in A1.
The same way:
xls.SetCellValue(1, 1, "=1 + 1") will set the string "=1 + 1"
while
xls.SetCellValue(1, 1, TFormula.Create("=1 + 1") will set the formula instead.
If you want to see an apostrophe for strings, you need to set the cell format to "123 prefix". For example, the following code will create a file with the string "1" in A1 and A2, but in A2 it will have a prefix, and not in A1. Both will be strings anyway. For completeness in A3 we will write the number 1.
program Project40;
{$APPTYPE CONSOLE}
{$R *.res}
uses
VCL.FlexCel.Core, FlexCel.XlsAdapter;
var
xls: TXlsFile;
fmt: TFlxFormat;
begin
xls := TXlsFile.Create(1, true);
xls.SetCellValue(1, 1, '1'); //string, without apostrophe.
fmt := xls.GetFormat(xls.GetCellFormat(2, 1));
fmt.Lotus123Prefix := true;
xls.SetCellValue(2, 1, '1', xls.AddFormat(fmt)); //string, with aphostrophe
xls.SetCellValue(3, 1, 1); //number
xls.Save('r:\test.xlsx');
end.
Now, once we know this, what I am not sure is if you want to
1)Remove the apostrophes from the strings, but keep them as strings
or
2)Convert strings to the "best" representation. For example, if you have the string 1 convert it to the number 1.
For 1), you just need to set the cell format for the cells as not having Lotus123Prefix, and the string won't show the apostrophes.
For 2), you might use "SetCellFromString" in FlexCel, which will try to convert the strings to numbers or formulas if possible.
For example:
program Project40;
{$APPTYPE CONSOLE}
{$R *.res}
uses
VCL.FlexCel.Core, FlexCel.XlsAdapter;
var
xls: TXlsFile;
fmt: TFlxFormat;
r, c, cIndex: integer;
v: TCellValue;
begin
xls := TXlsFile.Create('r:\test.xlsx', true);
for r := 1 to xls.RowCount do
begin
for cIndex := 1 to xls.ColCountInRow(r) do
begin
c := xls.ColFromIndex(r, cIndex);
v := xls.GetCellValue(r, c);
if v.IsString then xls.SetCellFromString(r, c, v.AsString);
end;
end;
xls.Save('r:\test2.xlsx');
end.
Will open the file we created in the first example, and try to convert all strings to numbers, formulas or dates if possible.
Note that the code above will try to convert strings no matter if they have apostrophes or not. If you wanted to convert only strings that have an apostrophe, then you should check the format of the cell:
program Project40;
{$APPTYPE CONSOLE}
{$R *.res}
uses
VCL.FlexCel.Core, FlexCel.XlsAdapter;
var
xls: TXlsFile;
fmt: TFlxFormat;
r, c, cIndex: integer;
v: TCellValue;
XF: integer;
begin
xls := TXlsFile.Create('r:\test.xlsx', true);
for r := 1 to xls.RowCount do
begin
for cIndex := 1 to xls.ColCountInRow(r) do
begin
c := xls.ColFromIndex(r, cIndex);
v := xls.GetCellValue(r, c, XF);
fmt := xls.GetFormat(XF);
if v.IsString and fmt.Lotus123Prefix then xls.SetCellFromString(r, c, v.AsString);
end;
end;
xls.Save('r:\test2.xlsx');
end.
But I guess you will want to convert all possible strings, not only those which have a lotus123prefix.
Regards,
Adrian.
Thank You, for the answer!
All I needed was
procedure XlsRemoveApostrofes;
var
c, r: integer;
fmt: TFlxFormat;
XF: integer;
begin
for r := 1 to nRows do begin
for c := 1 to nCols do begin
Vell := Xls.GetCellValue( r,c,XF );
fmt := Xls.GetFormat( XF );
fmt.Lotus123Prefix := FALSE;
Xls.SetCellValue( r,c,Vell,xls.AddFormat(fmt) );
end;{ c }
end;{ r }
end;{ XlsRemoveApostrofes }
But, though it did removed apostrophes from xls file, the Excel still does remember them - look at the picture
Regards,
Rimvydas
OK! The problem was solved by
procedure XlsRemoveApostrofes;
var
c, r: integer;
fmt: TFlxFormat;
XF: integer;
begin
for r := 1 to nRows do begin
for c := 1 to nCols do begin
Vell := Xls.GetCellValue( r,c,XF );
//fmt := Xls.GetFormat( XF );
//fmt.Lotus123Prefix := FALSE;
if Vell.IsString then
xls.SetCellFromString( r, c, Vell.AsString );
//else
//Xls.SetCellValue( r,c,Vell,xls.AddFormat(fmt) );
end;{ c }
end;{ r }
end;{ XlsRemoveApostrofes }
Thank You for help!