13,871
社区成员
发帖
与我相关
我的任务
分享怎样利用C++builder创建excel文件
因为需要生成报表，我在网上查了查，但是功能是将数据写入已创建的excel表格。但是如何创建excel文件呢
...全文
请发表友善的回复…
发表回复
aranjuze 2011-09-10
- 打赏
- 举报
我用JAVA的JXL来生成Excel，用JNI在BCB里调用，确定是要装JVM。
fbmsyu 2011-09-09
- 打赏
- 举报
// 程式來源 http://delphi.ktop.com.tw
// 原作者:Yudi Wibisono XLSFILE元件
// CCCHEN:改為Function版
// 領航天使:除錯
// dllee: 加入 StringGridToXLS(), 修正一些小 BUG, 指定此格式最大 Rows 數,以免爆了產生出的檔案不能用
unit XLSFile;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Grids, Forms, Dialogs,db,dbctrls,comctrls;
const
{BOF}
CBOF = $0009;
BIT_BIFF5 = $0800;
BOF_BIFF5 = CBOF or BIT_BIFF5;
{EOF}
BIFF_EOF = $000a;
{Document types}
DOCTYPE_XLS = $0010;
{Dimensions}
DIMENSIONS = $0000;
type
TAtributCell = (acHidden,acLocked,acShaded,acBottomBorder,acTopBorder,
acRightBorder,acLeftBorder,acLeft,acCenter,acRight,acFill);
TSetOfAtribut = set of TatributCell;
TXLSWriter = class(Tobject)
private
fstream:TFileStream;
procedure WriteWord(w:word);
protected
public
maxCols,maxRows:Word;
procedure WriteDimension;
procedure WriteBOF;
procedure WriteEOF;
procedure CellWord(vCol,vRow:word;aValue:word;vAtribut:TSetOfAtribut=[]);
procedure CellDouble(vCol,vRow:word;aValue:double;vAtribut:TSetOfAtribut=[]);
procedure CellStr(vCol,vRow:word;aValue:String;vAtribut:TSetOfAtribut=[]);
procedure WriteField(vCol,vRow:word;Field:TField);
constructor create(vFileName:string);
destructor destroy;override;
end;
procedure SetCellAtribut(value:TSetOfAtribut;var FAtribut:array of byte);
procedure DataSetToXLS(ds:TDataSet;fname:String);
procedure StringGridToXLS(grid:TStringGrid;fname:String);
implementation
procedure DataSetToXLS(ds:TDataSet;fname:String);
var c,r:Integer;
xls:TXLSWriter;
begin
xls:=TXLSWriter.create(fname);
if ds.FieldCount > xls.maxcols then
xls.maxcols:=ds.fieldcount+1;
try
xls.writeBOF;
xls.WriteDimension;
for c:=0 to ds.FieldCount-1 do
xls.Cellstr(0,c,ds.Fields[c].FieldName);
r:=1;
ds.first;
while (not ds.eof) and (r <= xls.maxrows) do begin
for c:=0 to ds.FieldCount-1 do
xls.WriteField(r,c,ds.Fields[c]);
inc(r);
ds.next;
end;
xls.writeEOF;
// <2002-11-17> dllee
// 更新 Dimension 應在 wirteEOF 之後，因為在此 if 內用了 Seek 改變 position
// if r > xls.maxrows then begin
// xls.maxrows:=r+1;
// xls.fstream.Seek(10,soFromBeginning);
// xls.WriteDimension;
// end;
// 但因為已將 maxrows 設為最大值，而且此格式就只能有 65535，所以，不再判斷。
finally
xls.free;
end;
end;
procedure StringGridToXLS(grid:TStringGrid;fname:String);
var c,r,rMax:Integer;
xls:TXLSWriter;
begin
xls:=TXLSWriter.create(fname);
rMax:=grid.RowCount;
if grid.ColCount > xls.maxcols then
xls.maxcols:=grid.ColCount+1;
if rMax > xls.maxrows then // 此格式最多只能存 65535 Rows
rMax:=xls.maxrows;
try
xls.writeBOF;
xls.WriteDimension;
for c:=0 to grid.ColCount-1 do
for r:=0 to rMax-1 do
xls.Cellstr(r,c,grid.Cells[c,r]);
xls.writeEOF;
finally
xls.free;
end;
end;
{ TXLSWriter }
constructor TXLSWriter.create(vFileName:string);
begin
inherited create;
if FileExists(vFilename) then
fStream:=TFileStream.Create(vFilename,fmOpenWrite)
else
fStream:=TFileStream.Create(vFilename,fmCreate);
maxCols:=100; // <2002-11-17> dllee Column 應該是不可能大於 65535, 所以不再處理
maxRows:=65535; // <2002-11-17> dllee 這個格式最大只能這麼大，請注意大的資料庫很容易就大於這個值
end;
destructor TXLSWriter.destroy;
begin
if fStream <> nil then
fStream.free;
inherited;
end;
procedure TXLSWriter.WriteBOF;
begin
Writeword(BOF_BIFF5);
Writeword(6); // count of bytes
Writeword(0);
Writeword(DOCTYPE_XLS);
Writeword(0);
end;
procedure TXLSWriter.WriteDimension;
begin
Writeword(DIMENSIONS); // dimension OP Code
Writeword(8); // count of bytes
Writeword(0); // min cols
Writeword(maxRows); // max rows
Writeword(0); // min rowss
Writeword(maxcols); // max cols
end;
procedure TXLSWriter.CellDouble(vCol, vRow: word; aValue: double;
vAtribut: TSetOfAtribut);
var FAtribut:array [0..2] of byte;
begin
Writeword(3); // opcode for double
Writeword(15); // count of byte
Writeword(vCol);
Writeword(vRow);
SetCellAtribut(vAtribut,fAtribut);
fStream.Write(fAtribut,3);
fStream.Write(aValue,8);
end;
procedure TXLSWriter.CellWord(vCol,vRow:word;aValue:word;vAtribut:TSetOfAtribut=[]);
var FAtribut:array [0..2] of byte;
begin
Writeword(2); // opcode for word
Writeword(9); // count of byte
Writeword(vCol);
Writeword(vRow);
SetCellAtribut(vAtribut,fAtribut);
fStream.Write(fAtribut,3);
Writeword(aValue);
end;
procedure TXLSWriter.CellStr(vCol, vRow: word; aValue: String;
vAtribut: TSetOfAtribut);
var FAtribut:array [0..2] of byte;
slen:byte;
begin
Writeword(4); // opcode for string
slen:=length(avalue);
Writeword(slen+8); // count of byte
Writeword(vCol);
Writeword(vRow);
SetCellAtribut(vAtribut,fAtribut);
fStream.Write(fAtribut,3);
fStream.Write(slen,1);
fStream.Write(aValue[1],slen);
end;
procedure SetCellAtribut(value:TSetOfAtribut;var FAtribut:array of byte);
var
i:integer;
begin
//reset
for i:=0 to High(FAtribut) do
FAtribut[i]:=0;
{Byte Offset Bit Description Contents
0 7 Cell is not hidden 0b
Cell is hidden 1b
6 Cell is not locked 0b
Cell is locked 1b
5-0 Reserved, must be 0 000000b
1 7-6 Font number (4 possible)
5-0 Cell format code
2 7 Cell is not shaded 0b
Cell is shaded 1b
6 Cell has no bottom border 0b
Cell has a bottom border 1b
5 Cell has no top border 0b
Cell has a top border 1b
4 Cell has no right border 0b
Cell has a right border 1b
3 Cell has no left border 0b
Cell has a left border 1b
2-0 Cell alignment code
general 000b
left 001b
center 010b
right 011b
fill 100b
Multiplan default align. 111b
}
// bit sequence 76543210
if acHidden in value then //byte 0 bit 7:
FAtribut[0] := FAtribut[0] + 128;
if acLocked in value then //byte 0 bit 6:
FAtribut[0] := FAtribut[0] + 64 ;
if acShaded in value then //byte 2 bit 7:
FAtribut[2] := FAtribut[2] + 128;
if acBottomBorder in value then //byte 2 bit 6
FAtribut[2] := FAtribut[2] + 64 ;
if acTopBorder in value then //byte 2 bit 5
FAtribut[2] := FAtribut[2] + 32;
if acRightBorder in value then //byte 2 bit 4
FAtribut[2] := FAtribut[2] + 16;
if acLeftBorder in value then //byte 2 bit 3
FAtribut[2] := FAtribut[2] + 8;
// <2002-11-17> dllee 最後 3 bit 應只有 1 種選擇
if acLeft in value then //byte 2 bit 1
FAtribut[2] := FAtribut[2] + 1
else if acCenter in value then //byte 2 bit 1
FAtribut[2] := FAtribut[2] + 2
else if acRight in value then //byte 2, bit 0 dan bit 1
FAtribut[2] := FAtribut[2] + 3
else if acFill in value then //byte 2, bit 0
FAtribut[2] := FAtribut[2] + 4;
end;
procedure TXLSWriter.WriteWord(w: word);
begin
fstream.Write(w,2);
end;
procedure TXLSWriter.WriteEOF;
begin
Writeword(BIFF_EOF);
Writeword(0);
end;
procedure TXLSWriter.WriteField(vCol, vRow: word; Field: TField);
begin
case field.DataType of
ftString,ftWideString,ftBoolean,ftDate,ftDateTime,ftTime:
Cellstr(vcol,vrow,field.asstring);
ftAutoInc,ftSmallint,ftInteger,ftWord:
CellWord(vcol,vRow,field.AsInteger);
ftFloat, ftBCD:
CellDouble(vcol,vrow,field.AsFloat);
else
Cellstr(vcol,vrow,EmptyStr); // <2002-11-17> dllee 其他型態寫入空白字串
end;
end;
end.
//-------------------------------然后加入工程。就可以调用DataSetToXLS或者StringGridToXLS等函数。直接生成XLS文件。这样的好处是机器不用安装excel，而且速度很快。
ccrun.com 2011-09-09
- 打赏
- 举报
google "site:ccrun.com OLE Excel"
zzbinfo 2011-09-09
- 打赏
- 举报
可以用ole直接操作，也可以用odbc的驱动新建这个是老妖以前写的你参考
void __fastcall TForm1::btn1Click(TObject *Sender)
{
Variant vExcelApp;
try
{
vExcelApp = Variant::CreateObject("Excel.Application");
}
catch(...)
{
MessageBox(0, "启动 Excel 出错, 可能是没有安装Excel.",
"提示", MB_OK | MB_ICONERROR);
vExcelApp = Unassigned;
return;
}
// 隐藏Excel界面
vExcelApp.OlePropertySet("Visible", false);
// 新建一个工作表
vExcelApp.OlePropertyGet("Workbooks").OleFunction("Add", 1); // 工作表
// 将剪贴板中的图像粘贴到Excel中
vExcelApp.OlePropertyGet("ActiveSheet").OleProcedure("Paste");
// 以当前时间为文件名，如20101009141700.xls
String strXlsFile = FormatDateTime("YYYYMMDDHHNNSS", Now());
// 注意这个路径
strXlsFile = "D:\\" + strXlsFile + ".xls";
// 保存这个Excel文件
vExcelApp.OlePropertyGet("ActiveWorkbook")
.OleFunction("SaveAs", strXlsFile.c_str());
// 退出Excel
vExcelApp.OleFunction("Quit");
vExcelApp = Unassigned;
// 工作结束
MessageBox(0, "结束！",
"提示", MB_OK | MB_ICONINFORMATION);
}
//---------------------------------------------------------------------------
七擒关羽 2011-09-09
- 打赏
- 举报
用XlGrid 由于安装这个鸟东西失败，现在用的是XlGrid的DLL文件，调用内置函数实现，网上的资料还是有的
zzbinfo 2011-09-09
- 打赏
- 举报
是不是脱离编译环境运行就正常了，你试试看[Quote=引用 7 楼 liliisgood 的回复:]
我按照老妖的代码写的，但是老是运行有问题，貌似是说无效的地址，程序会卡住，不过确实生成了一个Excel文件
#define PG OlePropertyGet
#define PS OlePropertySet
#define FN OleFunction
#define PR OleProcedure
Variant Ex;
//创建应用对象……
[/Quote]
我按照老妖的代码写的，但是老是运行有问题，貌似是说无效的地址，程序会卡住，不过确实生成了一个Excel文件
#define PG OlePropertyGet
#define PS OlePropertySet
#define FN OleFunction
#define PR OleProcedure
Variant Ex;
//创建应用对象……
[/Quote]
liliisgood 2011-09-09
- 打赏
- 举报
我按照老妖的代码写的，但是老是运行有问题，貌似是说无效的地址，程序会卡住，不过确实生成了一个Excel文件
#define PG OlePropertyGet
#define PS OlePropertySet
#define FN OleFunction
#define PR OleProcedure
Variant Ex;
//创建应用对象
Ex=Variant::CreateObject( "Excel.Application");
//新建一个工作表
Ex.PG("Workbooks").FN("Add",1);
String strXlsFile=1;
//路径
strXlsFile="D:\\"+strXlsFile+".xls";
//保存
Ex.PG("ActiveWorkbook").FN("SaveAs",strXlsFile.c_str());
//退出
Ex.FN("Quit");
Ex=Unassigned;
#define PG OlePropertyGet
#define PS OlePropertySet
#define FN OleFunction
#define PR OleProcedure
Variant Ex;
//创建应用对象
Ex=Variant::CreateObject( "Excel.Application");
//新建一个工作表
Ex.PG("Workbooks").FN("Add",1);
String strXlsFile=1;
//路径
strXlsFile="D:\\"+strXlsFile+".xls";
//保存
Ex.PG("ActiveWorkbook").FN("SaveAs",strXlsFile.c_str());
//退出
Ex.FN("Quit");
Ex=Unassigned;
zzbinfo 2011-09-09
- 打赏
- 举报
这个不是你的编译器的问题，你里面有断言错误，看代码怎么写的，那个地方有ASSERT
liliisgood 2011-09-09
- 打赏
- 举报
看了老妖的网站和代码，我用Ex.OlePropertyGet("Workbooks").OleFunction("Add", 1); 创建excel文件，但老是报错
“Assertion failed:Isbound();file C:\Builds\TP\emuvcl\utilcls.h,line 3606”意思是头文件有问题？是不是我的c++builder2009 有错误啊
“Assertion failed:Isbound();file C:\Builds\TP\emuvcl\utilcls.h,line 3606”意思是头文件有问题？是不是我的c++builder2009 有错误啊
