我用的主要代码如下,怎样修改格式才能让身份证不至于导入到Excel时成为了科学计数法表示?谢谢!!
s:=tstringlist.create;
adoquery3.DisableControls;
Screen.Cursor:=crHourGlass;
try
tmp:='';
for i:=0 to adoquery3.FieldCount -1 do
tmp:=tmp+adoquery3.Fields[i].DisplayLabel+#9;
s.add(tmp);
adoquery3.First ;
while not adoquery3.Eof do
begin
tmp:='';
for i:=0 to adoquery3.FieldCount-1 do
tmp:=tmp+(adoquery3.fields[i].AsString)+#9;
s.Add(tmp);
adoquery3.Next ;
end;
s.savetofile(SaveDialog1.filename);
s:=tstringlist.create;
adoquery3.DisableControls;
Screen.Cursor:=crHourGlass;
try
tmp:='';
for i:=0 to adoquery3.FieldCount -1 do
tmp:=tmp+adoquery3.Fields[i].DisplayLabel+#9;
s.add(tmp);
adoquery3.First ;
while not adoquery3.Eof do
begin
tmp:='';
for i:=0 to adoquery3.FieldCount-1 do
tmp:=tmp+(adoquery3.fields[i].AsString)+#9;
s.Add(tmp);
adoquery3.Next ;
end;
s.savetofile(SaveDialog1.filename);
Selection.NumberFormatLocal = "@"
Range("D1").Select
End Sub
这是把所选单元格设置成文本的代码,你可以用DELPHI实现一下
Columns("F:F").Select
Selection.NumberFormatLocal = "@"
End Sub
这是设置列为文本的VB代码
excel打开你导出的文件时,会认为身份证号码是一个数字,并且很长,所以用科学记数法表示。
如果在前后加上空格或其他的一些控制字符(回车换行Tab),excel可能会认为这是字符串,就不会用数字方式表示了。
真正的excel文件是有特殊格式的,其中包括的每个单元格的表示方式。
另一种方法就是将你的Query中的数据导出成真正的excel文件。但是excel文件的格式是非常复杂的,并且M$还保密,只透露给一些合作伙伴,所以自己解析格式是不太可能。
但是可能通过一些第三方的Grid控件将Grid中的数据导出成excel文件,比如ExpressQuantumGrid
Last revision date : 25/02/2000
Status : FreewareDelphi rewrite from original C-code by Jason Morrill
and the xls-fileformat description from www.wotsit.org}{Tested with Delphi 1 and 5 ; Excel 5.0 and Office 97}
{Should work with other Delphi/Excel versions}{Excel uses a file format called BIFF (Binary File Format).
There are many types of BIFF records. Each has a 4 byte header.
The first two bytes are an opcode that specifies the record type.
The second two bytes specify record length.
The rest of the record is the data itselfWord-values are stored in byte-reversed form (lsbf - least significant byte first).
Double-values (8 byte floats) are stored in IEEE format}{
Usage example :(Calling form Uses : add Xls)Var
FileHandle : Integer;
begin
FileHandle := Xls_Create('c:\test\delphi.xls'); Xls_SetFormat(FileHandle,'yyyy-mm-dd hh:mm:ss'); // user defined additional format 21 // Xls_SetProtection(FileHandle, True); // protect entire worksheet
// Xls_SetAttributeByte1(True, True); // Hidden, Locked -> only works if sheet is protected // some titles in the first column
Xls_SetString(FileHandle, 0, 0, 'A random string :)');
Xls_SetString(FileHandle, 1, 0, 'A lucky integer');
Xls_SetString(FileHandle, 2, 0, 'Pi (in a box) - yuk :)');
Xls_SetString(FileHandle, 3, 0, 'Today :'); Xls_SetString(FileHandle, 0, 1, 'Hello World'); // couldn't resist the temptation
Xls_SetInteger(FileHandle, 1, 1, 13); // integer : lucky 13 Xls_SetAttributeByte3(False, True, True, True, True, 0); // show all borders
Xls_SetDouble(FileHandle, 2, 1, Pi); // pi = double = 3.141572...
Xls_SetAttributeByte3(False, False, False, False, False, 0); // reset Xls_SetAttributeByte2(0, 21); // use user defined format 21
Xls_SetDouble(FileHandle, 3, 1, Now); // datetime
Xls_SetAttributeByte2(0, 0); // reset Xls_SetColWidth(FileHandle, 0, 17); // set column width
Xls_SetColWidth(FileHandle, 1, 25); // set column width Xls_Close (FileHandle); // closing stuff
end;
}interfaceuses
SysUtils;function Xls_Create(FileName: string): Integer;
procedure Xls_Close(FileHandle: Integer);procedure Xls_SetAttributeByte1(Hidden, Locked: Boolean);
procedure Xls_SetAttributeByte2(FontNumber, FormatCode: Byte);
procedure Xls_SetAttributeByte3(Shaded, BottomBorder, TopBorder, RightBorder, LeftBorder: Boolean; AlignMent: Byte);procedure Xls_SetBof(FileHandle: Integer; Version: Word; DocType: Word);
procedure Xls_SetEof(FileHandle: Integer);procedure BiffRecordHeader(FileHandle: Integer; OpCode: Word; Length: Word);
procedure BiffRecordValueWord(FileHandle: Integer; Value: Word);
procedure BiffRecordValueByte(FileHandle: Integer; Value: Byte);
procedure BiffRecordValueString(FileHandle: Integer; Value: string);
procedure BiffRecordValueDouble(FileHandle: Integer; Value: Double);procedure Xls_SetInteger(FileHandle: Integer; Row, Column: Word; Value: Word);
procedure Xls_SetString(FileHandle: Integer; Row, Column: Word; Value: string);
procedure Xls_SetDouble(FileHandle: Integer; Row, Column: Word; Value: Double);
procedure Xls_SetFormatCount(FileHandle: Integer; Count: Integer);
procedure Xls_SetFormat(FileHandle: Integer; FormatString: string);
procedure Xls_SetColWidth(FileHandle: Integer; Column: Integer; Width: Word);
procedure Xls_SetProtection(FileHandle: Integer; Protect: Boolean);implementation
var
AttributeByte1: Byte;
AttributeByte2: Byte;
AttributeByte3: Byte;
{**************************************************}
var
FileHandle: Integer;
begin
FileHandle := FileCreate(FileName); {result = filehandle}
{Create bof record}
Xls_SetBof(FileHandle, 5, 16); {version = 5 (doesn't seem to matter) ; doctype 16 = worksheet} {Set initial attributebyte-values}
Xls_SetAttributeByte1(False, False); {Hidden, Locked -> only works if sheet is protected too}
Xls_SetAttributeByte2(0, 0); {fontnumber 0 = default, formatcode 0 = general}
{Shaded, BottomBorder, TopBorder, RightBorder, LeftBorder, Alignment
Alignment 3 = Right ; Alignment 2 = Center ; Alignment 1 = Left ; Alignment 0 = general}
Xls_SetAttributeByte3(False, False, False, False, False, 0); {set initial standard Excel formats}
Xls_SetFormatCount(FileHandle, 21); {21 standard formats (0-based)}
Xls_SetFormat(FileHandle, 'General');
Xls_SetFormat(FileHandle, '0');
Xls_SetFormat(FileHandle, '0.00');
Xls_SetFormat(FileHandle, '#,##0');
Xls_SetFormat(FileHandle, '#,##0.00');
Xls_SetFormat(FileHandle, '\"$\"#,##0_);\\(\"$\"#,##0\\)');
Xls_SetFormat(FileHandle, '\"$\"#,##0_);[Red]\\(\"$\"#,##0\\)');
Xls_SetFormat(FileHandle, '\"$\"#,##0.00_);\\(\"$\"#,##0.00\\)');
Xls_SetFormat(FileHandle, '\"$\"#,##0.00_);[Red]\\(\"$\"#,##0.00\\)');
Xls_SetFormat(FileHandle, '0%');
Xls_SetFormat(FileHandle, '0.00%');
Xls_SetFormat(FileHandle, '0.00E+00');
Xls_SetFormat(FileHandle, 'm/d/yy');
Xls_SetFormat(FileHandle, 'd\\-mmm\\-yy');
Xls_SetFormat(FileHandle, 'd\\-mmm');
Xls_SetFormat(FileHandle, 'mmm\\-yy');
Xls_SetFormat(FileHandle, 'h:mm\\ AM/PM');
Xls_SetFormat(FileHandle, 'h:mm:ss\\ AM/PM');
Xls_SetFormat(FileHandle, 'h:mm');
Xls_SetFormat(FileHandle, 'h:mm:ss');
Xls_SetFormat(FileHandle, 'm/d/yy\\ h:mm'); Result := FileHandle; {return filehandle}
end;{*******************************************}procedure Xls_Close(FileHandle: Integer);
{*******************************************}
begin
Xls_SetEof(FileHandle); {create eof-record} FileClose(FileHandle); {free filehandle}
end;{*************************************************************}procedure Xls_SetAttributeByte1(Hidden, Locked: Boolean);
{*************************************************************}
begin
AttributeByte1 := 0;
if Hidden = True then
AttributeByte1 := AttributeByte1 + 128; {set bit 7}
if Locked = True then
AttributeByte1 := AttributeByte1 + 64; {set bit 6}
{Bits 5->0 are reserved}
end;{*****************************************************************}procedure Xls_SetAttributeByte2(FontNumber, FormatCode: Byte);
{*****************************************************************}
begin
AttributeByte2 := (FontNumber shl 6) + FormatCode;
end;{**************************************************************************************************************************}procedure Xls_SetAttributeByte3(Shaded, BottomBorder, TopBorder, RightBorder, LeftBorder: Boolean; AlignMent: Byte);
{**************************************************************************************************************************}
begin
AttributeByte3 := Alignment; {set bits 1-0}
if Shaded = True then {set bit 7}
AttributeByte3 := AttributeByte3 + 128;
if BottomBorder = True then {set bit 6}
AttributeByte3 := AttributeByte3 + 64;
if TopBorder = True then {set bit 5}
AttributeByte3 := AttributeByte3 + 32;
if RightBorder = True then {set bit 4}
AttributeByte3 := AttributeByte3 + 16;
if LeftBorder = True then {set bit 3}
AttributeByte3 := AttributeByte3 + 8;
end;{**************************************************************************}procedure BiffRecordHeader(FileHandle: Integer; OpCode: Word; Length: Word);
{**************************************************************************}
begin
{Write the standard recordheader to the file}
BiffRecordValueWord(FileHandle, OpCode);
BiffRecordValueWord(FileHandle, Length);
end;{**************************************************************************}procedure BiffRecordValueWord(FileHandle: Integer; Value: Word);
{**************************************************************************}
begin
{Write a word value to the file}
FileWrite(FileHandle, Value, 2);
end;
{**************************************************************************}procedure BiffRecordValueString(FileHandle: Integer; Value: string);
{**************************************************************************}
begin
try
{Write a string to the file}
FileWrite(FileHandle, Value[1], Length(Value));
finally
end;
end;{**************************************************************************}procedure BiffRecordValueDouble(FileHandle: Integer; Value: Double);
{**************************************************************************}
begin
{Write a double to the file}
FileWrite(FileHandle, Value, 8);
end;{**************************************************************************}procedure BiffRecordValueByte(FileHandle: Integer; Value: Byte);
{**************************************************************************}
begin
{Write a byte to the file}
FileWrite(FileHandle, Value, 1);
end;
{**************************************************************************}
begin
{Write a beginning of file (bof) record}
BiffRecordHeader(FileHandle, 9, 4); {9 = opcode bof, length of data}
BiffRecordValueWord(FileHandle, Version);
BiffRecordValueWord(FileHandle, DocType);
end;
{**************************************************************************}procedure Xls_SetEof(FileHandle: Integer);
{**************************************************************************}
begin
{Write an end of file (eof) record}
BiffRecordHeader(FileHandle, 10, 0); {10 = opcode eof}
end;{************************************************************************************}procedure Xls_SetInteger(FileHandle: Integer; Row, Column: Word; Value: Word);
{************************************************************************************}
begin
{Write an integer value to row, column (0-based)
Caveat : only for unsigned, possitive integers that fit in a word : 0-65535 (or whatever)}
BiffRecordHeader(FileHandle, 2, 9); {2 = Opcode Integer}
BiffRecordValueWord(FileHandle, Row);
BiffRecordValueWord(FileHandle, Column); BiffRecordValueByte(FileHandle, AttributeByte1); {attributes define the look of the cell}
BiffRecordValueByte(FileHandle, AttributeByte2);
BiffRecordValueByte(FileHandle, AttributeByte3);
BiffRecordValueWord(FileHandle, Value);
end;{*************************************************************************************}procedure Xls_SetString(FileHandle: Integer; Row, Column: Word; Value: string);
{*************************************************************************************}
begin
{Write a string to row, column}
BiffRecordHeader(FileHandle, 4, 8 + Length(Value)); {4 = Opcode String/Label}
BiffRecordValueWord(FileHandle, Row);
BiffRecordValueWord(FileHandle, Column); BiffRecordValueByte(FileHandle, AttributeByte1); {attributes define the look of the cell}
BiffRecordValueByte(FileHandle, AttributeByte2);
BiffRecordValueByte(FileHandle, AttributeByte3);
BiffRecordValueByte(FileHandle, Length(Value));
BiffRecordValueString(FileHandle, Value);
end;{************************************************************************************}procedure Xls_SetDouble(FileHandle: Integer; Row, Column: Word; Value: Double);
{************************************************************************************}
begin
{Write a double value to row, column}
{Caveat : Dates are just doubles with formatting applied}
BiffRecordHeader(FileHandle, 3, 15); {3 = Opcode Double}
BiffRecordValueWord(FileHandle, Row);
BiffRecordValueWord(FileHandle, Column); BiffRecordValueByte(FileHandle, AttributeByte1); {attributes define the look of the cell}
BiffRecordValueByte(FileHandle, AttributeByte2);
BiffRecordValueByte(FileHandle, AttributeByte3); BiffRecordValueDouble(FileHandle, Value);
end;
{*************************************************************************}procedure Xls_SetFormatCount(FileHandle: Integer; Count: Integer);
{*************************************************************************}
begin
{Define the number of standard formats : (21)}
BiffRecordHeader(FileHandle, 31, 2); {31 = Opcode Formatcount}
BiffRecordValueWord(FileHandle, Count);
end;{*************************************************************************}procedure Xls_SetFormat(FileHandle: Integer; FormatString: string);
{*************************************************************************}
begin
{Add an additional format-string to the internal formatlist}
BiffRecordHeader(FileHandle, 30, Length(FormatString) + 1); {30 = Opcode Format}
BiffRecordValueByte(FileHandle, Length(FormatString));
BiffRecordValueString(FileHandle, FormatString);
end;{*********************************************************************************}procedure Xls_SetColWidth(FileHandle: Integer; Column: Integer; Width: Word);
{*********************************************************************************}
begin
BiffRecordHeader(FileHandle, 36, 4); {36 = Opcode colwidth}
BiffRecordValueByte(FileHandle, Column); {starting column}
BiffRecordValueByte(FileHandle, Column); {ending column}
BiffRecordValueWord(FileHandle, Width * 256); {width is defined in 1/256 of a character}
end;{*********************************************************************}procedure Xls_SetProtection(FileHandle: Integer; Protect: Boolean);
{*********************************************************************}
begin
{Set general protection setting of worksheet}
BiffRecordHeader(FileHandle, 18, 2); {18 = opcode protected worksheet}
if Protect = True then
BiffRecordValueWord(FileHandle, 1)
else
BiffRecordValueWord(FileHandle, 0);
end;end.
begin
tmp:='';
for i:=0 to adoquery3.FieldCount-1 do
begin
if adoquery3.fields[i].name='身份证字段' then
tmp:=tmp+''''+(adoquery3.fields[i].AsString)+#9
else
tmp:=tmp+(adoquery3.fields[i].AsString)+#9;
end;
s.Add(tmp);
adoquery3.Next ;
end;
直接导出到excel才能控制它的列的格式
2、用代码导出成Excel格式文件,在导出之前,设置Excel单元格的格式为文本。你的代码只不过是存成了一个文本文件而已,不是Excel格式,所以在打开时,Excel自动认为是数字。
方法二:有些Grid控件支持导出Excel,比如ExpressQuantumGrid/InfoPower/EhLib
方法三:自已按Excel文件格式输出,参见上面我贴出的单元文件
方法四:ADO支持Excel文件类型的数据库,用ADODataSet,试一下。