如何用bcb读取excel表格中任一行、列的数据？

[azuly]

如题，谢谢！

[Behard]

看我以前写的一个函数：
/*******************************************************************************
* Function Name : AddData
* Description : 填充数据
* Return : -
* Parameters : *lpData[] - 具体的数据（指针数组）,【标题】
: iCount - 数据的数目
: iRowsPoint - 对应一行
* Author : Behard
* Date : 2002/09/02
*******************************************************************************/
extern "C" void __declspec(dllexport) __stdcall AddData(//Variant MSExcel,
char *lpData[], //具体的数据（数组指针）
int iCount, //数据的数目
int iRowsPoint) //那一行
{
int i;
char strSet[20] ;

try{
//特别注意：OlePropertyGet 等函数的参数是从 1 开始的，非 0
for(i=0; i<iCount /*&& i<STRINGNUMBER*/; i++)
{
//为单元格填值
MSExcel.OlePropertyGet("ActiveWorkBook").OlePropertyGet("ActiveSheet").OlePropertySet("Cells",iRowsPoint,(Variant)(i+1),((AnsiString)"'"+(*(lpData+i))).c_str());//.OlePropertySet("Value", "12");

//设置对齐方式
//垂直对齐 2:左端对齐，3：居中， 4：右端对齐 注意：不是 1、2、3
if(i<='Z'-'A')sprintf(strSet, "%c%d", ('A'+i), iRowsPoint );
else sprintf(strSet, "%c%c%d", ('A'+ i/('Z'-'A'+1) -1), ('A'+ i%('Z'-'A'+1) ), iRowsPoint );
MSExcel.OlePropertyGet("ActiveWorkBook").OlePropertyGet("ActiveSheet").OlePropertyGet("Range",strSet).OlePropertySet("HorizontalAlignment",2);
//水平对齐 1:顶端对齐，2：居中， 3：底端对齐
MSExcel.OlePropertyGet("ActiveWorkBook").OlePropertyGet("ActiveSheet").OlePropertyGet("Range",strSet).OlePropertySet("VerticalAlignment",2);
}
}
catch (Exception &exception)
{
Application->ShowException(&exception);
}
//ShowMessage("AddData!") ;
}

[azuly]

谢谢诸位，正在慢慢看......

其实我只是要读取而已，简单一些代码可以吧？

[dhua_fj]

MSExcel.OlePropertyGet("ActiveWorkBook").OlePropertyGet("ActiveSheet")
.OlePropertyGet("Range","A1").OlePropertyGet("Value")//取值
或.OlePropertyGet("FormulaR1C1")//取公式

[BSC]

//---------------------------------------------------------------------------
#ifndef ExcelWorkH
#define ExcelWorkH
//---------------------------------------------------------------------------
#include <Classes.hpp>
#include <Controls.hpp>
#include <StdCtrls.hpp>
#include <Dialogs.hpp>
#include <DB.hpp>
#include <ADODB.hpp>
//---------------------------------------------------------------------------
class TXclWorksheet
{
private:
Variant* m_pExcel;
Variant* m_pWorkBook;
Variant* m_pWorkSheet;
//TdxTreeList* m_pTreeList;
//TMain* m_pMain;
bool m_Suspended;
bool m_bAlreadyOpened;
bool m_bSheetExists;
bool m_bWorsheetFirstTime;

AnsiString m_Title1;
AnsiString __fastcall IndicesToExcel(int iCol, int iRow);
// void __fastcall RecursiveTreeListFill(TdxTreeListNode* pNode, int& iCol, int& iRow, int& iLvl, bool& bOnlyVisible);
void __fastcall FillColumnTitles(int& iCol, int& iRow);
protected:
Variant __fastcall GetCell(int iCol, int iRow);
Variant __fastcall GetCellValue(int iCol, int iRow);
void __fastcall SetCellValue(int iCol, int iRow, Variant vValue);
public:
__fastcall TXclWorksheet(/*TMain* pMain, */bool bSuspended);
__fastcall ~TXclWorksheet();

void __fastcall OpenExcel();
void __fastcall CloseExcel();

void __fastcall SaveWorkbook(AnsiString Name);
void __fastcall LoadWorkbook(AnsiString Name);

Variant* __fastcall CreateWorkBook();
Variant* __fastcall CreateWorkSheet(AnsiString Name = "UTPOWER");
void __fastcall SetVisiable(bool isVisable);
void __fastcall PrintPreview();
void __fastcall Print();

Variant __fastcall MergeCells(int iColMin, int iRowMin, int iColMax, int iRowMax);
Variant __fastcall GetRange(int iColMin, int iRowMin, int iColMax, int iRowMax);

//H是否行显示数据
void __fastcall CreateExcelReport(TDataSet* DSource,int iCol, int iRow, bool H);
//设置单元格属性
void __fastcall SetCellStyleBlock(int iColMin, int iRowMin, int iColMax, int iRowMax,
bool isMerge,bool isBold,int ColorIdx,int FSize,AnsiString FName,AnsiString TitleArr);

void __fastcall ADODataSetToExcel(TCustomADODataSet *ADataSet, const String AFileName);

__property Variant __fastcall Cell[int iCol][int iRow] = { read = GetCell };
__property Variant __fastcall CellValue[int iCol][int iRow] = { read = GetCellValue,write = SetCellValue };
};
#endif

//---------------------------------------------------------------------------
#ifndef SettingStyleBlock
#define SettingStyleBlock(MsExcel,Sheet,isMerge,isBold,Rg,ColorIdx,FSize,FName,TitleArr) \
MsExcel = Sheet.OlePropertyGet("Range", Rg);\
if (isMerge)\
MsExcel.OleFunction("Merge");\
MsExcel.OlePropertyGet("Borders").OlePropertySet("ColorIndex",ColorIdx);\
MsExcel.OlePropertySet("HorizontalAlignment", 3);\
MsExcel.OlePropertySet("VerticalAlignment", 2);\
MsExcel.OlePropertyGet("Font").OlePropertySet("Size",FSize);\
MsExcel.OlePropertyGet("Font").OlePropertySet("Name",FName);\
if (isBold)\
MsExcel.OlePropertyGet("Font").OlePropertySet("Bold",true);\
MsExcel.OlePropertySet("Value",TitleArr);
#endif

[BSC]

#pragma hdrstop

#include "ExcelWork.h"

//---------------------------------------------------------------------------
#pragma package(smart_init)
//---------------------------------------------------------------------------
__fastcall TXclWorksheet::TXclWorksheet(/*TMain* pMain, */bool bSuspended)
{
m_Suspended = bSuspended;
m_pExcel = NULL;
m_pWorkBook = NULL;
m_pWorkSheet = NULL;
}
__fastcall TXclWorksheet::~TXclWorksheet()
{
delete m_pWorkBook;
delete m_pWorkSheet;
delete m_pExcel;
m_pWorkBook = NULL;
m_pWorkSheet = NULL;
}
void __fastcall TXclWorksheet::OpenExcel()
{
Variant xcl_wbook, xcl_wsheet;
if(!m_pExcel)
{
m_pExcel = new Variant;

try
{
*m_pExcel = Variant::GetActiveObject("excel.application");
m_bAlreadyOpened = true;
}
catch(...)
{
try
{
*m_pExcel = Variant::CreateObject("excel.application");
m_bAlreadyOpened = false;
}
catch(...)
{
MessageDlg("请检查是否安装了Excel程序", mtError, TMsgDlgButtons() << mbOK, NULL);
}
}
}
}
//---------------------------------------------------------------------------
//创建工作薄
Variant* __fastcall TXclWorksheet::CreateWorkBook()
{
if(!m_pExcel)
OpenExcel();
if(m_pExcel)
{
if(!m_pWorkBook)
m_pWorkBook = new Variant;
Variant vAllWorkbooks;
try
{
vAllWorkbooks = m_pExcel->OlePropertyGet("Workbooks");
}
catch(...)
{
delete m_pExcel;
delete m_pWorkBook;

m_pExcel = NULL;
m_pWorkBook = NULL;

return CreateWorkBook();
}

*m_pWorkBook = vAllWorkbooks.OleFunction("Add");
m_bWorsheetFirstTime = true;
}
return m_pWorkBook;
}
//---------------------------------------------------------------------------
//创建工作薄上的页
Variant* __fastcall TXclWorksheet::CreateWorkSheet(AnsiString Name)
{
if(!m_pWorkBook)
CreateWorkBook();
if(m_pWorkBook)
{
m_bSheetExists = m_pWorkSheet;
if(!m_pWorkSheet)
m_pWorkSheet = new Variant;
try
{
bool b_found = false;
Variant v_count = m_pWorkBook->OlePropertyGet("WorkSheets").OlePropertyGet("Count");
for(int i = 0; i < v_count; ++i)
if(m_pWorkBook->OlePropertyGet("WorkSheets", Variant(i + 1)).OlePropertyGet("Name") == Name)
{
*m_pWorkSheet = m_pWorkBook->OlePropertyGet("WorkSheets", Variant(i + 1));
b_found = true;
if(m_bSheetExists)//清空Excel数据
{
if (MessageDlg("是否清空<"+Name+">页",mtWarning, TMsgDlgButtons() << mbYes+mbNo, 0)==mrYes)
{
PropertyGet Range("Range");
PropertySet SetValue("Value");
Range.ClearArgs();
SetValue.ClearArgs();
}
}
break;
}

if(m_bWorsheetFirstTime)
{
*m_pWorkSheet = m_pWorkBook->OlePropertyGet("WorkSheets", Variant(1));
}
else if(!b_found)
{
*m_pWorkSheet = m_pWorkBook->OlePropertyGet("WorkSheets").OleFunction("Add");
}
}
catch(...)
{
delete m_pWorkBook;
delete m_pWorkSheet;

m_pWorkBook = NULL;
m_pWorkSheet = NULL;

if(m_pExcel && !int(m_pExcel->OlePropertyGet("WorkBooks").OlePropertyGet("Count")))
{
m_pExcel->OleFunction("Quit");
delete m_pExcel;
m_pExcel = NULL;
}

return CreateWorkSheet(Name);
}
PropertySet psName("Name");
m_pWorkSheet->Exec(psName << Name);

m_Title1 = Name;//标题头1

if (!m_Suspended)
{
m_pExcel->OlePropertySet("Visible", Variant(true));
m_pWorkSheet->OlePropertySet("Visible", Variant(true));
}
else
{
m_pExcel->OlePropertySet("Visible", Variant(false));
//m_pWorkSheet->OlePropertySet("Visible", Variant(false));
}
m_bWorsheetFirstTime = false;
}
return m_pWorkSheet;
}

[BSC]

//---------------------------------------------------------------------------
//合并单元格
Variant __fastcall TXclWorksheet::MergeCells(int iColMin, int iRowMin, int iColMax, int iRowMax)
{
Variant vRange = GetRange(iColMin, iRowMin, iColMax, iRowMax);
vRange.OlePropertySet("MergeCells", Variant(true));
return vRange;
}
//---------------------------------------------------------------------------
//获得工作页范围
Variant __fastcall TXclWorksheet::GetRange(int iColMin, int iRowMin, int iColMax, int iRowMax)
{
PropertyGet pgRange("Range");
return m_pWorkSheet->Exec(pgRange << String(IndicesToExcel(iColMin, iRowMin)
+ ":" + IndicesToExcel(iColMax, iRowMax)));
}
//---------------------------------------------------------------------------
//得到单元格
Variant __fastcall TXclWorksheet::GetCell(int iCol, int iRow)
{
Variant vRtn = "";
if(m_pWorkSheet)
{
PropertyGet pgRange("Range");
vRtn = m_pWorkSheet->Exec(pgRange << IndicesToExcel(iCol, iRow));
}
return vRtn;
}
//---------------------------------------------------------------------------
//得到单元格的值
Variant __fastcall TXclWorksheet::GetCellValue(int iCol, int iRow)
{
Variant vRtn = "";
if(m_pWorkSheet)
{
PropertyGet pgRange("Range");
vRtn = m_pWorkSheet->Exec(pgRange << IndicesToExcel(iCol, iRow)).OlePropertyGet("Value");
}
return vRtn;
}
//---------------------------------------------------------------------------
//设置单元格的值
void __fastcall TXclWorksheet::SetCellValue(int iCol, int iRow, Variant vValue)
{
if(m_pWorkSheet)
{
PropertySet psValue("Value");

GetCell(iCol, iRow).Exec(psValue << vValue);
GetCell(iCol, iRow).OlePropertySet("HorizontalAlignment", 3);
GetCell(iCol, iRow).OlePropertySet("VerticalAlignment", 2);
GetCell(iCol, iRow).OlePropertyGet("Borders").OlePropertySet("ColorIndex",0);
}
}
//---------------------------------------------------------------------------
//将行和列转换成Excel对应的行列标示
AnsiString __fastcall TXclWorksheet::IndicesToExcel(int iCol, int iRow)
{
AnsiString strRtn = "";
int iMod = iCol % int('Z' - 64);
int iRest = iCol / int('Z' - 64);
strRtn += iRest ? String(char(iRest + 64)) : String("");
strRtn += String(char(iMod + 'A')) + IntToStr(iRow + 1);
return strRtn;
}
//---------------------------------------------------------------------------
//关闭Excel文件
void __fastcall TXclWorksheet::CloseExcel()
{
// 保存Excel文件
if (m_pWorkBook)
{
try
{
//HWND hPrevApp = ::FindWindow(NULL,"Microsoft Excel");
//if (hPrevApp)
//{
m_pWorkBook->OleProcedure("SaveAs");
// 关闭工作簿
m_pWorkBook->OleProcedure("Close");
// 退出Excel
m_pExcel->OleFunction("Quit");
//}
}
catch(...)
{
//MessageDlg("Excel程序退出异常", mtError, TMsgDlgButtons() << mbOK, NULL);
Abort;
}
}
}

//---------------------------------------------------------------------------
//保存工作薄
void __fastcall TXclWorksheet::SaveWorkbook(AnsiString Name)
{
if (m_pWorkBook)
{
AnsiString excelFile = Name;
m_pWorkBook->OleProcedure("Save",excelFile.c_str());
}
}
//---------------------------------------------------------------------------
//装载工作薄
void __fastcall TXclWorksheet::LoadWorkbook(AnsiString Name)
{
if (!m_pExcel)
{
OpenExcel();
}
if (!m_pWorkBook)
{
m_pWorkBook = new Variant;
}
if (!m_pWorkSheet)
{
m_pWorkSheet = new Variant;
}
//打开Excel文件
AnsiString excelFile = Name;//ExtractFilePath(Application->ExeName) + "文件名.xls";
m_pExcel->OlePropertyGet("WorkBooks").OleProcedure("Open", excelFile.c_str());
//获得当前工作簿的对象
*m_pWorkBook = m_pExcel->OlePropertyGet("ActiveWorkBook");
//选中第一个工作表作为当前的工作表
*m_pWorkSheet = m_pWorkBook->OlePropertyGet("Sheets", 1);
m_pWorkBook->OlePropertyGet("Sheets", 1).OleProcedure("Select");
if (!m_Suspended)
{
m_pExcel->OlePropertySet("Visible", Variant(true));
m_pWorkSheet->OlePropertySet("Visible", Variant(true));
}
else
{
m_pExcel->OlePropertySet("Visible", Variant(false));
//m_pWorkSheet->OlePropertySet("Visible", Variant(false));
}
}

//---------------------------------------------------------------------------
//组织数据源创建Excel报表
void __fastcall TXclWorksheet::CreateExcelReport(TDataSet* DSource,int iCol, int iRow,bool H)
{
int CurrCol, CurrRow;
CurrCol = iCol;
CurrRow = iRow;
Screen->Cursor=crHourGlass;
if (H)//行排列
{
for (int i=0;i<DSource->Fields->Count;i++)
{
if(DSource->Fields->Fields[i]->Visible)
{
SetCellValue(CurrCol,2,DSource->Fields->Fields[i]->DisplayLabel);
CurrCol++;
}
}
//标题头
//MergeCells(0,0,CurrCol,1);
//SetCellValue(0,0,m_Title1);

int j=0;
DSource->First();
while(!DSource->Eof)
{
CurrCol=0;
for (int i=0;i<DSource->Fields->Count;i++)
{
if(DSource->Fields->Fields[i]->Visible)
{
SetCellValue(CurrCol,j+3,DSource->Fields->Fields[i]->AsString);
CurrCol++;
}
}
Application->ProcessMessages();
DSource->Next();
j++;
}
}
else//竖排列
{
for (int i=0;i<DSource->Fields->Count;i++)
{
if(DSource->Fields->Fields[i]->Visible)
{
CurrRow++;
SetCellValue(1,CurrRow,DSource->Fields->Fields[i]->DisplayLabel);
}
}
int j=0;
DSource->First();
while(!DSource->Eof)
{
CurrRow=0;
for (int i=0;i<DSource->Fields->Count;i++)
{
if(DSource->Fields->Fields[i]->Visible)
{
CurrRow++;
SetCellValue(j+2,CurrRow,DSource->Fields->Fields[i]->AsString);
}
}
Application->ProcessMessages();
DSource->Next();
j++;
}
}
Screen->Cursor=crDefault;
}