unit4.cpp

此程序将Acesses文件调入EXCEL,WORD

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

#include <vcl.h>
#pragma hdrstop
#include <comobj.hpp>
#include "Unit4.h"
//---------------------------------------------------------------------------
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm4 *Form4;
//---------------------------------------------------------------------------
__fastcall TForm4::TForm4(TComponent* Owner)
        : TForm(Owner)
{
}
//---------------------------------------------------------------------------

void __fastcall TForm4::Button2Click(TObject *Sender)
{
      Variant vWordApp, vTable, vCell;
        int iCount=0;
        try
        {
                vWordApp = Variant::CreateObject("Word.Application");
        }
        catch(...)
        {
                vWordApp = Unassigned;
                MessageBox(NULL,"启动Word出错,可能是没有安装Word.","ToWord",MB_OK|MB_ICONERROR);
                return;
        }
        // 隐藏Word界面
        vWordApp.OlePropertySet("Visible", false);
        // 新建一个文档
        vWordApp.OlePropertyGet("Documents").OleFunction("Add");

        Variant vSelect = vWordApp.OlePropertyGet("Selection");
        // 设置一下字体，大小
        vSelect.OlePropertyGet("Font").OlePropertySet("Size", 10);
        vSelect.OlePropertyGet("Font").OlePropertySet("Name", "宋体");

        Variant vExcelApp, vWorkbook, vSheet, vRange;

        AnsiString strExcelFile=GetCurrentDir() + "\\student.xls";
        if(!FileExists(strExcelFile))
        {
                MessageBox(NULL,"文件不存在！","提示",MB_OK);
                return;
        }

        try{

                vExcelApp = Variant::CreateObject("Excel.Application");
        }
        catch(...)
        {
                vExcelApp=Unassigned;
                MessageBox(NULL,"无法启动Excel,可能尚未安装或文件已经损坏！","提示",MB_OK);
                return;
        }

        try{
                vExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open", strExcelFile.c_str());
                vWorkbook = vExcelApp.OlePropertyGet("ActiveWorkbook");
                vSheet = vWorkbook.OlePropertyGet("ActiveSheet");
        }
        catch(...)
        {
                vExcelApp.OleFunction("Quit");
                vExcelApp = Unassigned;
                MessageBox(NULL,"文件被破坏！","提示",MB_OK);
                return;
        }

        int iRowCount=vSheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows")
                .OlePropertyGet("Count");
        int iColCount=vSheet.OlePropertyGet("UsedRange").OlePropertyGet("Columns")
                .OlePropertyGet("Count");


        // 在Word文档中插入一个表格
        vWordApp.OlePropertyGet("ActiveDocument").OlePropertyGet("Tables").OleProcedure("Add",
                vSelect.OlePropertyGet("Range"),
                iRowCount, // 行数
                iColCount, // 列数
                1, // DefaultTableBehavior:=wdWord9TableBehavior
                0); // AutoFitBehavior:=wdAutoFitFixed
        // 操作这个表格
        vTable = vWordApp.OlePropertyGet("ActiveDocument").OleFunction("Range")
                .OlePropertyGet("Tables").OleFunction("Item", 1);
        // 设置单元格的宽度
        for(int i=1; i<iColCount; i++)
        {
                int iColWidth = vExcelApp.OlePropertyGet("Columns", i).OlePropertyGet("ColumnWidth");
                vTable.OlePropertyGet("Columns").OleFunction("Item", i)
                        .OlePropertySet("PreferredWidthType", 3); // wdPreferredWidthPoints
                vTable.OlePropertyGet("Columns").OleFunction("Item", i)
                        .OlePropertySet("PreferredWidth", iColWidth);
        }

        for(int i=1; i<=iRowCount; i++)
        {
                // 63 63 72 75 6E 2E 63 6F 6D
                for(int j=1; j<=iColCount; j++)
                {
                        vCell = vTable.OleFunction("Cell", i , j);
                        AnsiString strValue = AnsiString(vSheet.OlePropertyGet("Cells", i, j)
                                .OlePropertyGet("Value"));
                        vCell.OlePropertySet("Range",strValue.c_str());
                }
                iCount++;
        }



        AnsiString strWordFile=GetCurrentDir() + "\\Make\\ExcelToWord_Student.doc";
        DeleteFile(strWordFile);
        vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("SaveAs", strWordFile.c_str());
        vWordApp.OlePropertyGet("ActiveDocument").OleProcedure("Close");
        vWordApp.OleProcedure("Quit");
        vWordApp = Unassigned;
        vExcelApp.OleFunction("Quit");
        vExcelApp = Unassigned;
        MessageBox(NULL,(AnsiString("ExcelToWord共导入记录数：")+IntToStr(iCount-1)).c_str(),
                "ExcelToWord", MB_OK|MB_ICONINFORMATION);


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

void __fastcall TForm4::Button1Click(TObject *Sender)
{
     Variant vExcelApp, vSheet;
        int iCount=0;
        try
        {
                vExcelApp = Variant::CreateObject("Excel.Application");
        }
        catch(...)
        {
                vExcelApp=Unassigned;
                MessageBox(NULL,"启动Excel出错,可能是没有安装Excel.","ToExcel",MB_OK|MB_ICONERROR);
                return;
        }
        // 隐藏Excel界面
        vExcelApp.OlePropertySet("Visible", false);
        // 新建一个工作表
        vExcelApp.OlePropertyGet("Workbooks").OleFunction("Add", 1); // 工作表
        // 操作这个工作表
        vSheet = vExcelApp.OlePropertyGet("ActiveWorkbook").OlePropertyGet("Sheets", 1);
        // 设置Excel文档的字体
        vSheet.OleProcedure("Select");
        vSheet.OlePropertyGet("Cells").OleProcedure("Select");
        vExcelApp.OlePropertyGet("Selection").OlePropertyGet("Font").OlePropertySet("Size", 10);
        vExcelApp.OlePropertyGet("Selection").OlePropertyGet("Font").OlePropertySet("Name", "宋体");
        vExcelApp.OlePropertyGet("Selection").OlePropertyGet("Font").OlePropertySet("FontStyle", "常规");
        vSheet.OlePropertyGet("Cells", 1, 1).OleProcedure("Select");


        Variant vWordApp, vTable, vCell;
        
        AnsiString strWordFile=GetCurrentDir() + "\\student.doc";
        if(!FileExists(strWordFile))
        {
                MessageBox(NULL,"文件不存在！","提示",MB_OK);
                return;
        }

        try
        {
                vWordApp = Variant::CreateObject("Word.Application");
        }
        catch(...)
        {
                vWordApp = Unassigned;
                MessageBox(NULL, "启动Word出错,可能是没有安装Word.","提示",MB_OK|MB_ICONERROR);
                return;
        }
        // 隐藏Word界面
        vWordApp.OlePropertySet("Visible", false);
        try
        {

                vWordApp.OlePropertyGet("Documents").OleProcedure("Open", strWordFile.c_str());
                vTable = vWordApp.OlePropertyGet("ActiveDocument").OleFunction("Range")
                        .OlePropertyGet("Tables").OleFunction("Item", 1);
        }
        catch(...)
        {
                vWordApp.OleFunction("Quit");
                vWordApp = Unassigned;
                MessageBox(NULL,"文件被破坏！","提示",MB_OK);
                return;
        }


        int iRowCount=vTable.OlePropertyGet("Rows").OlePropertyGet("Count");
        int iColCount=vTable.OlePropertyGet("Columns").OlePropertyGet("Count");

        for(int i=1; i<=iRowCount; i++)
        {
                vExcelApp.OlePropertyGet("Rows", i).OlePropertySet("RowHeight", 16);
                for(int j=1; j<=iColCount; j++)
                {
                        vCell = vTable.OleFunction("Cell", i, j);

                        int iTextLength=AnsiString(vCell.OlePropertyGet("Range").OlePropertyGet("Text")).Length();
                        AnsiString strValue=AnsiString(vCell.OlePropertyGet("Range").OlePropertyGet("Text"));

                        vSheet.OlePropertyGet("Cells", i, j).OlePropertySet("Value",(strValue.SubString(0,iTextLength-2)).c_str());
                }
                iCount++;
        }

        AnsiString strExcelFile=GetCurrentDir() + "\\Make\\WordToExcel_Student.xls";
        DeleteFile(strExcelFile);
        vExcelApp.OlePropertyGet("ActiveWorkbook").OleFunction("SaveAs", strExcelFile.c_str());
        vExcelApp.OlePropertyGet("ActiveWorkbook").OleProcedure("Close");
        vExcelApp.OleFunction("Quit");
        vSheet = Unassigned;
        vExcelApp = Unassigned;
        vWordApp.OleFunction("Quit");
        vWordApp = Unassigned;

        MessageBox(NULL,(AnsiString("WordToExcel共导入记录数：")+IntToStr(iCount-1)).c_str(),"WordToExcel",MB_OK);

}
//---------------------------------------------------------------------------
void __fastcall TForm4::Button3Click(TObject *Sender)
{
    Close();
}
//---------------------------------------------------------------------------