bookscanunit.cpp

简单的C++小程序

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

#include <vcl.h>
#pragma hdrstop

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

TBookScan *BookScan;
bool ExcelOpen;
//---------------------------------------------------------------------------
__fastcall TBookScan::TBookScan(TComponent* Owner)
        : TForm(Owner)
{
        //this->Width = 600;
        //this->Height = 500;
        DBGrid1->Width = this->Width - 10;
        DBGrid1->Height = this->Height - 95;
        ADOQuery1->Active = false;
        mnuOutExcel->Enabled = false;
        ExcelOpen = false;
}
//---------------------------------------------------------------------------
void __fastcall TBookScan::FormClose(TObject *Sender, TCloseAction &Action)
{
        ADOQuery1->Close();
        if(ExcelOpen)
        {
                try
                {
                        Wb.OleProcedure("Close");//关闭表格
                        Ex.OleFunction("Quit");//退出Excel
                }
                catch(...)
                {
                }
                ExcelOpen = false;
        }
        Action = caFree;
}
//---------------------------------------------------------------------------

void __fastcall TBookScan::FormResize(TObject *Sender)
{
        DBGrid1->Width = this->Width - 10;
        DBGrid1->Height = this->Height - 95;
}
//---------------------------------------------------------------------------

void __fastcall TBookScan::Button1Click(TObject *Sender)
{
        AnsiString mysql;
        AnsiString scanstr;
        scanstr = Edit1->Text;

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

        if(scanstr.Length() > 0)
        {
                mysql = "SELECT 借书号,书号,名称,出版单位,编著者,出版日期,定价 FROM books ";
                mysql = mysql + "WHERE (名称 LIKE " + char(34) + char(37) + scanstr + char(37) + char(34);
                mysql = mysql + ")OR(出版单位 LIKE " + char(34) + char(37) + scanstr + char(37) + char(34);
                mysql = mysql + ")OR(编著者 LIKE " + char(34) + char(37) + scanstr + char(37) + char(34);
                mysql = mysql + ") ORDER BY 借书号";
                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;
                }
        }
        else
        {
                ShowMessage("请输入检索内容!");
        }

}
//---------------------------------------------------------------------------

void __fastcall TBookScan::mnuBookExitClick(TObject *Sender)
{
        this->Close();
}
//---------------------------------------------------------------------------

void __fastcall TBookScan::mnuOutExcelClick(TObject *Sender)
{
        //取报表文件CardSend.xls的完整目录名
        const AnsiString FileDirStr = GetCurrentDir();
        AnsiString ExcelFileName = FileDirStr + "\\xlstmp\\books.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 TBookScan::Edit1KeyDown(TObject *Sender, WORD &Key,
      TShiftState Shift)
{
        switch(Key)
        {
                case VK_RETURN:
                        this->Button1->Click();
                        break;
                case VK_ESCAPE:
                        Edit1->Text = "";
                        break;
        }
}
//---------------------------------------------------------------------------