readstatunit.cpp

简单的C++小程序

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

#include <vcl.h>
#pragma hdrstop

#include "ReadStatUnit.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
#pragma link "Excel_2K_SRVR"

TReaderStat *ReaderStat;
bool ExcelOpen;
//---------------------------------------------------------------------------
__fastcall TReaderStat::TReaderStat(TComponent* Owner)
        : TForm(Owner)
{
        //this->Width = 600;
        //this->Height = 500;
        DBGrid1->Width = this->Width - 30;
        DBGrid1->Height = this->Height - 135;
        mnuOutExcel->Enabled = false;
        ExcelOpen = false;
}
//---------------------------------------------------------------------------
void __fastcall TReaderStat::FormClose(TObject *Sender,
      TCloseAction &Action)
{
        ADOQuery1->Close();
        if(ExcelOpen)
        {
                try
                {
                        Wb.OleProcedure("Close");//关闭表格
                        Ex.OleFunction("Quit");//退出Excel
                }
                catch(...)
                {
                }
                ExcelOpen = false;
        }
        Action = caFree;
}
//---------------------------------------------------------------------------
void __fastcall TReaderStat::FormResize(TObject *Sender)
{
        DBGrid1->Width = this->Width - 10;
        DBGrid1->Height = this->Height - 95;        
}
//---------------------------------------------------------------------------
void __fastcall TReaderStat::Button1Click(TObject *Sender)
{
        AnsiString mysql;
        AnsiString PassNo;
        PassNo = Edit1->Text.Trim();

        if(ExcelOpen)
        {
                try
                {
                        Wb.OleProcedure("Close");//关闭表格
                        Ex.OleFunction("Quit");//退出Excel
                }
                catch(...)
                {
                }
                ExcelOpen = false;
        }

        if(PassNo.Length() > 0)
        {
                mysql = "SELECT 姓名,借书证号,注销 FROM cards where 注销 = False AND 借书证号 = ";
                mysql = mysql + char(34) + PassNo + char(34);
                ADOQuery1->Active = false;
                ADOQuery1->SQL->Clear();
                ADOQuery1->SQL->Add(mysql);
                ADOQuery1->Active = true;
                if(ADOQuery1->RecordCount == 0)
                {
                        ShowMessage("借书证号不存在或已注销!");
                        ADOQuery1->Active = false;
                        return;
                }
                Label2->Caption = ADOQuery1->FieldByName("姓名")->AsString + "-借书明细";

                mysql = "SELECT 借阅日期,借书号,名称,编著者,出版单位 FROM lendsql ";
                mysql = mysql + "WHERE lendsql.[借书证号] = " + char(34) + PassNo + char(34);
                ADOQuery1->Active = false;
                ADOQuery1->SQL->Clear();
                ADOQuery1->SQL->Add(mysql);
                ADOQuery1->Active = true;
                StatusBar2->Panels->Items[0]->Text = "共借了" + IntToStr(ADOQuery1->RecordCount) +
                                                "本书";
                if(ADOQuery1->RecordCount > 0)
                {
                        mnuOutExcel->Enabled = true;
                }
        }
}
//---------------------------------------------------------------------------
void __fastcall TReaderStat::mnuOutExcelClick(TObject *Sender)
{
        //取报表文件CardSend.xls的完整目录名
        const AnsiString FileDirStr = GetCurrentDir();
        AnsiString ExcelFileName = FileDirStr + "\\xlstmp\\reader.xls";
        AnsiString DestinationFile;
        
        try
        {
                ADOQuery1->Active = True;

                if(!FileExists(ExcelFileName))
                {
                        Application->MessageBox("报表模板文件不存在,无法打开！","错误",MB_ICONSTOP|MB_OK);
                        return;
                }

                if(SaveDialog1->Execute())
                {
                        DestinationFile = SaveDialog1->FileName;
                        const char *ExtStr = ".xls";
                        const char *DesStr = DestinationFile.c_str();
                        if(!StrPos(DesStr,ExtStr))
                        {
                                DestinationFile += AnsiString(ExtStr);
                        }
                        //ShowMessage(DestinationFile);
                        //将Excel表格文件拷贝到目录下
                        if(!CopyFile(ExcelFileName.c_str(),DestinationFile.c_str(),false))
                        {
                                Application->MessageBox("复制文件操作失败,Excel文件可能正在使用中！","错误",MB_ICONSTOP|MB_OK);
                                return;
                        }

                        //建立Excel的Ole对象Ex
                        try
                        {
                                Ex = Variant::CreateObject("Excel.Application");
                        }
                        catch(...)
                        {
                                Application->MessageBox("无法启动Excel","错误",MB_ICONSTOP|MB_OK);
                                return;
                        }
                        //设置Excel是否可见
                        //Ex.OlePropertySet("Visible",false);
                        Ex.OlePropertySet("Visible",true);
                        //打开指定的Excel报表文件。报表文件中最好设定只有一个Sheet。
                        Ex.OlePropertyGet("WorkBooks").OleProcedure("Open",DestinationFile.c_str());
                        Wb = Ex.OlePropertyGet("ActiveWorkBook");
                        Sheet = Wb.OlePropertyGet("ActiveSheet");//获得当前默认的Sheet

                        AnsiString strRowTemp;
                        AnsiString strRange;
                        int iCols,iRows;//记录列数和行数

                        iRows = 1;
                        int f_count = ADOQuery1->Fields->Count;
                        for(iCols=1;iCols<(f_count + 1);iCols++)
                        {
                                Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value",ADOQuery1->Fields->Fields[iCols-1]->FieldName);
                        }
                        //画表格边框
                        char start_col = 'A';
                        char end_col = start_col + f_count - 1;
                        strRange = AnsiString(start_col) + IntToStr(iRows) + ":" + AnsiString(end_col) + IntToStr(iRows);
                        ERange = Sheet.OlePropertyGet("Range",strRange.c_str());
                        EBorders = ERange.OlePropertyGet("Borders");
                        EBorders.OlePropertySet("linestyle",xlContinuous);
                        EBorders.OlePropertySet("weight",xlThin);
                        EBorders.OlePropertySet("colorindex",xlAutomatic);

                        iRows++;

                        while(!ADOQuery1->Eof)
                        {
                                //循环取字段的数据放到Excel表对应的行列中
                                for(iCols=1;iCols<(f_count + 1);iCols++)
                                {
                                        strRowTemp = ADOQuery1->Fields->Fields[iCols-1]->AsString;
                                        Sheet.OlePropertyGet("Cells",iRows,iCols).OlePropertySet("Value",strRowTemp.c_str());
                                }
                                //画该行的表格边框
                                strRange = AnsiString(start_col) + IntToStr(iRows) + ":" + AnsiString(end_col) + IntToStr(iRows);
                                ERange = Sheet.OlePropertyGet("Range",strRange.c_str());
                                EBorders = ERange.OlePropertyGet("Borders");
                                EBorders.OlePropertySet("linestyle",xlContinuous);
                                EBorders.OlePropertySet("weight",xlThin);
                                EBorders.OlePropertySet("colorindex",xlAutomatic);

                                iRows++;
                                ADOQuery1->Next();
                        }
                
                        Wb.OleProcedure("Save");//保存表格
                        //Wb.OleProcedure("Close");//关闭表格
                        //Ex.OleFunction("Quit");//退出Excel
                        ExcelOpen = true;
                        ADOQuery1->First();
                }
                
        }
        catch(...)
        {
                Application->MessageBox("操作Excel表格错误！","错误",MB_ICONSTOP|MB_OK);
                Ex.OleFunction("Quit");
                ExcelOpen = false;
        }
        SetCurrentDir(FileDirStr);        
}
//---------------------------------------------------------------------------
void __fastcall TReaderStat::Edit1KeyDown(TObject *Sender, WORD &Key,
      TShiftState Shift)
{
        switch(Key)
        {
                case VK_RETURN:
                        this->Button1->Click();
                        break;
                case VK_ESCAPE:
                        Edit1->Text = "";
                        break;
        }        
}
//---------------------------------------------------------------------------
void __fastcall TReaderStat::mnuReadStatExitClick(TObject *Sender)
{
        this->Close();        
}
//---------------------------------------------------------------------------