|
如何在C++中将数据库数据分行和列保存到Excel中？ 程序中的数据在StringGrid控件中显示的，那如何按照StringGrid显示的格式分行分列保存到Excel表格呢？请看如下两种方法的实现：
第一种方法：采用的一格一格填充数据
Variant ExcelApp,WorkBook1,WorkSheet1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
AnsiString FileName = ExtractFileDir(Application->ExeName )+ "\\a.xls";
try
{
ExcelApp=Variant::CreateObject("Excel.Application");
}
catch(...)
{
ShowMessage("Sorry!Excel cannot be launched");
return;
}
ExcelApp.OlePropertySet("Visible",true);
ExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open",FileName.c_str());
WorkBook1=ExcelApp.OlePropertyGet("ActiveWorkBook");
WorkSheet1=WorkBook1.OlePropertyGet("ActiveSheet");
for(int i=0;i<StringGrid1->RowCount;i++)
{
for(int j=0;j<StringGrid1->ColCount;j++)
{
WorkSheet1.OlePropertyGet("Cells", i+1 , j+1 )
.OlePropertySet("Value",StringGrid1->Cells[j][i].c_str() ) ;
}
}
ExcelApp.OlePropertyGet("ActiveWorkbook")
.OleFunction("SaveAs", FileName.c_str());
ExcelApp.OleFunction("Quit");
WorkSheet1 = Unassigned;
WorkBook1 = Unassigned;
ExcelApp = Unassigned;
}
第二种方法：直接从ADO把数据导出来
Variant ExcelApp;
Variant WorkBook1;
Variant Sheet1;
Variant Range;
Variant Table;
Variant QueryTables;
int Count;
AnsiString ITemp,IStr;
AnsiString sSQL,sSQLHj, sWhere, sStartDate, sEndDate, sDate;
try
{
ExcelApp=CreateOleObject ("Excel.Application");
}
catch(...)
{
ShowMessage("运行出错，请确认装了Excel");
return;
}
ExcelApp.OlePropertyGet("WorkBooks").OleFunction("Add");
ExcelApp.OlePropertyGet("workbooks").OleFunction("Add", "E:\\Lxrb6.xls");
WorkBook1=ExcelApp.OlePropertyGet("ActiveWorkBook");
Sheet1 = WorkBook1.OlePropertyGet("ActiveSheet");
WorkBook1.OlePropertyGet("Sheets", 1).OleProcedure("Select");
Sheet1.OlePropertySet("name","小热报6");
Range=Sheet1.OlePropertyGet("Range","A9");
QueryTables=Sheet1.OlePropertyGet("QueryTables");
sSQL="select * from Table" //这里的数据很多，我随便简化下！
qryTmp->Active=false;
qryTmp->SQL->Clear();
qryTmp->SQL->Add(sSQL);
qryTmp->Active=true;
Count=qryTmp->RecordCount+12;
IStr="A9:M"+IntToStr(Count);
Table=QueryTables.OleFunction("Add",qryTmp->Recordset,Range);
Table.OlePropertySet("FieldNames",false);
Range=Sheet1.OlePropertyGet("Range", IStr.c_str());
Sheet1.OlePropertyGet("Range", "A:K").OlePropertyGet("Columns").OleProcedure("AutoFit"); //自动列宽
Table.OleProcedure("Refresh",true);
WorkBook1.OleFunction("SaveAs", "E:\\6.xls");
ShowMessage("导出完毕，请检查");
qryTmp->Active=false;
ExcelApp.Exec(Procedure("Quit"));
ExcelApp = Unassigned;
看看哪种合适，就用哪种吧，嘻嘻！
| 
