excel.cpp

在BCB环境下

//---------------------------------------------------------------------------

#include <vcl.h>
#pragma hdrstop

#include "Excel.h"
//-----------------------------------------------------------------

//-------------------------------------------------------------------------------
#define TNP TNoParam()
#define V TVariant
//---------------------------------------------------------------------
#define COL (Col++)
//------------------------------------------------------------------------
#define ColorTestMenu 1
#define ColorTestItem  10
#define ColorTestCount 0//35
#define ColorItremName 16
//--------------------------------------------------------------------------
struct ReportName
{
	String a;
	String b;
	String c;
	String d;
	ReportName()
	{
		a = "测试报告.xls";
		b = "速率图_数据测试.xls";
		c = "速率图_数据交互测试.xls";
		d = "速率图_断点续传测试.xls";
	}
};

struct SheetName
{
	String a;
	String b;
	String c;
	String d;
	SheetName()
	{
		a = "报告";
		b = "速率图";
		c = "区间数据";
		d = "全部数据";
	}
};
//---------------------------------------------------------------------------------
//09.1.4 wzl add start:
#define DIRECTION_DOWNLOAD                  0                       // 下载传输方向
#define DIRECTION_UPLOAD                    1                       // 上传传输方向
#define DIRECTION_NOTRANSFER                2                       // 没有传输

#define RESTORE_NO                          0                       // 非续传
#define RESTORE_YES                         1                       // 续传


//测试 状态
#define STATE_NONE                          0                       // 空状态
#define STATE_CONNECTED                     1                       // 连接上
#define STATE_DISCONNECTED                  2                       // 断开
#define STATE_TRANSFERING                   3                       // 传输中

#define STATE1_CONNECTED              "Connected"                        // 连接上
#define STATE2_DISCONNECTED        "Disconnected"                               // 断开
#define STATE3_TRANSFERING          "Transferring"                              // 传输中
//09.1.4 wzl add end.
//---------------------------------------------------------------------------------


//------------------------------------------------
#pragma package(smart_init)
#pragma link "Excel_2K_SRVR"
#pragma resource "*.dfm"
TExcelReport *ExcelReport;
//---------------------------------------------------------------------------
__fastcall TExcelReport::TExcelReport(TComponent* Owner)
    : TForm(Owner)
{
	Row = 3;
	Col = 0;
	sValue = "";
	sSavePath = "";
	bSMS = true;
	bData = true;
	bDataIT = true;
	bFitful = true;
	
	m_vSMSTestResult.clear();
	m_vTDSTestResult.clear();
	m_vPictureData.clear();
	FtpTestSet InitStrc;
	m_FtpITResult = InitStrc;
	
	 m_vFitfulResult.clear();
	 m_vFitfulPicData.clear();
	PPPvsAdvFTPTestParam FitfulInit; //断点续传测试结果
	m_FitfulTestResult = FitfulInit;
	//-------------------------------------------------
/*	for(UINT i=0; i<3; i++)
	{
		TSendResult send;
		m_vSMSTestResult.push_back(send);

		TDSTestPlanItems Data;
		m_vTDSTestResult.push_back(Data);
	}	*/
}
void TExcelReport::InitRowCol()
{
	sValue = "";
	Row++;
	Col = 1;
}

/**********************************************************************
* 函数名称：NoResult
* 功能描述：判断是否有结果数据
* 输入参数：
* 输出参数：无
* 返 回 值： BOOL
* 其它说明：// 其它说明
* 修改日期      版本号     修改人       修改内容
* -----------------------------------------------
* 2009-3-13    V1.0        wzl         First Version
***********************************************************************/
bool TExcelReport::NoResult()
{	
	if(m_vSMSTestResult.size() == 0)
	bSMS = false;
	
	if(m_vTDSTestResult.size() == 0)
	bData = false;	
	
	 if(m_FtpITResult.m_CallTotal == 0 && m_FtpITResult.m_SendSMSTotal == 0)
	 bDataIT = false;

	 if(m_vFitfulResult.size() == 0)
	 bFitful = false;

	if(bSMS ==  false && bData  ==  false && bDataIT ==  false && bFitful ==  false)
	return false;
	else
	return true;	
}
/**********************************************************************
* 函数名称：HideSheet
* 功能描述：隐藏EXCEL 表单，并把ExcelWorkbook1 指向当前活动表。
* 输入参数：String SheetName, String ActiveSheet
* 输出参数：无
* 返 回 值： 无
* 其它说明：// 其它说明
* 修改日期      版本号     修改人       修改内容
* -----------------------------------------------
* 2009-3-24    V1.0        wzl         First Version
***********************************************************************/
 void TExcelReport::HideSheet(String SheetName, String ActiveSheet, UINT USheetIndex)
 {
 	try
 	{
	 	if(SheetName != NULL && SheetName != "")
	 	{
			ExcelWorksheet1->ConnectTo(ExcelWorkbook1->Worksheets->get_Item(V(SheetName)));
			//SheetIndex = 1;
			ExcelWorksheet1->set_Visible(0, false);
	 	}
	 	
		if(ActiveSheet != NULL && ActiveSheet != "")
		{
		 	ExcelWorksheet1->ConnectTo(ExcelWorkbook1->Worksheets->get_Item(V(ActiveSheet)));

		}
		
		SheetIndex = USheetIndex;	 	
 	}
 	catch(...)
 	{
		ShowMessage("Hide sheet error !");
		return;
 	}		
 }
//---------------------------------------------------------------------------
/**********************************************************************
* 函数名称：OpenExcel
* 功能描述：打开EXCEL 模板
* 输入参数：const String ExcelName
* 输出参数：无
* 返 回 值： BOOL
* 其它说明：// 其它说明
* 修改日期      版本号     修改人       修改内容
* -----------------------------------------------
* 2009-3-13    V1.0        wzl         First Version
***********************************************************************/
bool TExcelReport::OpenExcel(const String ExcelName)
{
	if(ExcelName != "fist")
	{
		ExcelApplication1 = new TExcelApplication(this);
		ExcelWorkbook1 = new TExcelWorkbook(this);
		ExcelWorksheet1 = new TExcelWorksheet(this);
	}
	
       try
    {
        ExcelApplication1->ConnectKind = ckRunningOrNew;
        ExcelApplication1->Connect();
    }
    catch(...)
    {
        MessageBox(0, "启动 Excel 出错, 可能是没有安装Excel.",
			"STOP", MB_OK | MB_ICONERROR);
           // ExcelApplication1->Disconnect();
            ExcelApplication1->Quit();
            return false;
    }
    ExcelApplication1->set_Caption((WideString)"Excel Server Invoked by BCB");
    //操作中EXCEL是否可见
    ExcelApplication1->set_Visible(0,false);

    AnsiString ModalFile = "";
 //   if(ExcelName == NULL)
  // {
        ModalFile = ExtractFilePath(Application->ExeName) + "report.xls";
  /*  }
    else
    {
       ModalFile = ExcelName;
    }*/
    if(FileExists(ModalFile) == false)
    {
        ShowMessage(ModalFile + " is not fond!");
        return false;
    }
    //打开指定的文件，需要修改为自己本地的一个文件路径
    ExcelWorkbook1->ConnectTo(ExcelApplication1->Workbooks->Open((WideString)ModalFile,
                                  TNP, TNP, TNP, TNP,
                                  TNP, TNP, TNP, TNP,
                                  TNP, TNP, TNP, TNP, 0));
   ExcelWorksheet1->ConnectTo(ExcelWorkbook1->Worksheets->get_Item(V("报告")));
   SheetIndex = 1;
//---------------------------------------------------------------
	RangePtr R;   
	R =  ExcelWorksheet1->get_Range(V("A1"), V("A300"));
	R->set_ColumnWidth(V(23));
	R =  ExcelWorksheet1->get_Range(V("C1"), V("C300"));
	R->set_ColumnWidth(V(18));
	R =  ExcelWorksheet1->get_Range(V("E1"), V("E300"));
	R->set_ColumnWidth(V(18));
	R =  ExcelWorksheet1->get_Range(V("G1"), V("G300"));
	R->set_ColumnWidth(V(18));
    return true;
}
//---------------------------------------------------------------------------
/**********************************************************************
* 函数名称：SaveExcel
* 功能描述：另存该EXCEL 报告到指定路径
* 输入参数：const String SavePath
* 输出参数：无
* 返 回 值： BOOL
* 其它说明：// 其它说明
* 修改日期      版本号     修改人       修改内容
* -----------------------------------------------
* 2009-3-13    V1.0        wzl         First Version
***********************************************************************/
bool TExcelReport::SaveExcel(String FileName, bool bNotes)
{
    try
    {
		String Time = FormatDateTime("yyyymmdd_hhmmss",Now());
		String sFileName = "测试报告" + Time + ".xls";
		String  FileBox = ExtractFilePath(Application->ExeName) + "TestReport\\";        
		
		String FilePath = "";
		if(FileName == NULL ||FileName == "" )
		{			
			FilePath = FileBox;
			FileName = sFileName;
		}
		else
		{	
			if(sSavePath == "")
			{
				FilePath = FileBox + Time + "\\";;
				CreateDir(FilePath.c_str());  
				sSavePath = FilePath;
			}
			else
			{
				FilePath  = sSavePath;				
			}
		}  

		ExcelWorksheet1->SaveAs((WideString)(FilePath+FileName), TNP);

		//  ExcelApplication1->set_DisplayAlerts(0,false);  //去掉保存提示
		// ExcelApplication1->Save();     //   保存目标文件
		ExcelWorksheet1->Disconnect();
		ExcelWorkbook1->Disconnect();
		ExcelApplication1->Disconnect();
		ExcelApplication1->Quit();

		delete ExcelWorksheet1;
		delete ExcelWorkbook1;
		delete ExcelApplication1;
		
	  if(bNotes)
	  {	  	
		AnsiString	 FileAbout = "该【" + FileName + "】已经保存到：\n" +  FilePath;
		MessageBox(0, FileAbout.c_str(),"Path Of This Report", MB_OK | MB_ICONINFORMATION);
	  }
	
		return true;
    }
    catch(...)
    {
         MessageBox(0, "Fail to save report.","Warning", MB_OK | MB_ICONINFORMATION);
         return false;
    }
}
//-----------------------------------------------------------------------------
//---------------------------------------------------------------------------
/**********************************************************************
* 函数名称：SetCell
* 功能描述：向指定的单元填充数据和进行格式设置
* 输入参数：UINT  row, UINT col, String value, UINT backcolor, bool bmerge
* 输出参数：无
* 返 回 值： 无
* 其它说明：// 其它说明
* 修改日期      版本号     修改人       修改内容
* -----------------------------------------------
* 2009-3-13    V1.0        wzl         First Version
***********************************************************************/
void TExcelReport::SetCell(UINT  row, UINT col, String value, UINT backcolor, bool bmerge, bool bInit)
{
	if(bInit)
	{
		InitRowCol();
	}
    assert(Row >= 1);
	assert(Col >= 1);

	String strCol = "";
	switch(Col)
	{
		case 1: strCol = "A"; break;
		case 2: strCol = "B"; break;
		case 3: strCol = "C"; break;
		case 4: strCol = "D"; break;
		case 5: strCol = "E"; break;
		case 6: strCol = "F"; break;
		case 7: strCol = "G"; break;
		case 8: strCol = "H"; break;		
		default:  break;		
	}

      String strCell = strCol + IntToStr(Row);
      RangePtr R;     
       if(bmerge)
      {
      	  String strCol = "H" + IntToStr(Row);
      	  R =  ExcelWorksheet1->get_Range(V(strCell), V(strCol));
         R->set_HorizontalAlignment(V(3));//1水平：4靠右；3居中；2靠左.
         R->Merge(TNP);
         R =  ExcelWorksheet1->get_Range(V(strCell), V(strCell));
      }
      else
      {
		 R =  ExcelWorksheet1->get_Range(V(strCell), V(strCell));