Dbg2Excel_Word

悠悠 2023-11-19 07:48 227阅读 0赞

//在xe版本以上dbg转化为excel和word文件
    #pragma hdrstop
    
    #include "Unit1.h"
    #include "ComObj.hpp"  //excel 表要用
    
    //1:显示保存
    
    
    void __fastcall DBGrid2Excel(TDBGrid *dbg, String strXlsFile)
    {
        if ( !dbg->DataSource->DataSet->Active )
       {
       return  ;
    
       }
    
      Variant ExcelApp,Workbook1,Sheet1; //ole万能变量，定义excel对象使用
      int i=0,j=1;
      try{
         ExcelApp=Variant::CreateObject("Excel.Application"); //创建应用对象
    	 Workbook1=ExcelApp.OlePropertyGet("ActiveWorkBook");//创建工作簿对象
    	 ExcelApp.OlePropertyGet("WorkBooks").OleFunction("Add");//添加一个工作薄
    	 Sheet1=ExcelApp.OlePropertyGet("ActiveSheet");//创建工作区
    	 ExcelApp.OlePropertySet("Visible",false); //使Excel可见
    
       }
    
    	catch(...)
    
     {
      MessageDlg("无法启动Excel,可能尚未安装或文件已经损坏！",mtError,TMsgDlgButtons()<<mbYes,0);
      return;
     }
    
    
     //表格的行数
     int nRowCount(dbg->DataSource->DataSet->RecordCount+1) ;
     nRowCount=nRowCount<2? 2:nRowCount;
     //表格的列数
     int nColCount(dbg->Columns->Count) ;
     nColCount=nColCount<1? 1:nColCount;
    
     // 设置单元格的宽度
        for(int i=0; i<nColCount; i++)
        {
    		int nColWidth = dbg->Columns->Items[i]->Width;
    		 ExcelApp.OlePropertyGet("Columns", i + 1)
    				.OlePropertySet("ColumnWidth", nColWidth / 7);
    	}
    
    
    //在第一行写字段
     for(j=0;j<dbg->Columns->Count;++j)
    
     {
     // 标题行的行高
     ExcelApp.OlePropertyGet("Rows", 1).OlePropertySet("RowHeight", 20);
     Sheet1.OlePropertyGet("Cells",1,j+1).OlePropertySet("Value",WideString(dbg->Columns->Items[j]->Title->Caption ));
     // 设置列名单元格的背景色
    		Variant vInter = Sheet1.OlePropertyGet(
                    "Cells", 1, j + 1).OlePropertyGet("Interior");
            vInter.OlePropertySet("ColorIndex", 15); // 灰色
            vInter.OlePropertySet("Pattern", 1); // xlSolid
    		vInter.OlePropertySet("PatternColorIndex", -4105); // xlAutomatic
      }
    
    //----------------------------------------------------------------------------
      //将DBGrid中得数据写入EXCEL
     dbg->DataSource->DataSet->First() ;
    
      for(int i=0; i<nRowCount-1; i++)
        {
           // 普通数据行的行高16
            ExcelApp.OlePropertyGet("Rows", i + 2).OlePropertySet("RowHeight", 16);
    		// 63 63 72 75 6E 2E 63 6F 6D
    		for(int j=0; j<dbg->Columns->Count; j++)
            {
    		Sheet1.OlePropertyGet("Cells", i + 2, j + 1).OlePropertySet("Value",WideString(dbg->DataSource->DataSet->FieldByName(dbg->Columns->Items[j]->FieldName)->AsString));
            }
            dbg->DataSource->DataSet->Next();
    	}
    
      // 保存Excel文档并退出
        ExcelApp.OlePropertyGet("ActiveWorkbook")
    			.OleFunction("SaveAs", WideString(strXlsFile));
        ExcelApp.OleFunction("Quit");
        ExcelApp = Unassigned;
        // 工作结束
    
    
    
    
    	// 工作结束
    
    }
    //---------------------------------------------------------------------------
    void __fastcall DBGrid3Excel(TDBGrid *dbg)
    {
        if ( !dbg->DataSource->DataSet->Active )
       {
       return  ;
    
       }
    
      Variant ExcelApp,Workbook1,Sheet1; //ole万能变量，定义excel对象使用
      int i=0,j=1;
      try{
         ExcelApp=Variant::CreateObject("Excel.Application"); //创建应用对象
    	 Workbook1=ExcelApp.OlePropertyGet("ActiveWorkBook");//创建工作簿对象
    	 ExcelApp.OlePropertyGet("WorkBooks").OleFunction("Add");//添加一个工作薄
    	 Sheet1=ExcelApp.OlePropertyGet("ActiveSheet");//创建工作区
    	 ExcelApp.OlePropertySet("Visible",true); //使Excel可见
    
       }
    
    	catch(...)
    
     {
      MessageDlg("无法启动Excel,可能尚未安装或文件已经损坏！",mtError,TMsgDlgButtons()<<mbYes,0);
      return;
     }
    
    
     //表格的行数
     int nRowCount(dbg->DataSource->DataSet->RecordCount+1) ;
     nRowCount=nRowCount<2? 2:nRowCount;
     //表格的列数
     int nColCount(dbg->Columns->Count) ;
     nColCount=nColCount<1? 1:nColCount;
    
     // 设置单元格的宽度
        for(int i=0; i<nColCount; i++)
        {
    		int nColWidth = dbg->Columns->Items[i]->Width;
    		 ExcelApp.OlePropertyGet("Columns", i + 1)
    				.OlePropertySet("ColumnWidth", nColWidth / 7);
    	}
    
    
    //在第一行写字段
     for(j=0;j<dbg->Columns->Count;++j)
    
     {
     // 标题行的行高
     ExcelApp.OlePropertyGet("Rows", 1).OlePropertySet("RowHeight", 20);
     Sheet1.OlePropertyGet("Cells",1,j+1).OlePropertySet("Value",WideString(dbg->Columns->Items[j]->FieldName));
     // 设置列名单元格的背景色
    		Variant vInter = Sheet1.OlePropertyGet(
                    "Cells", 1, j + 1).OlePropertyGet("Interior");
            vInter.OlePropertySet("ColorIndex", 15); // 灰色
            vInter.OlePropertySet("Pattern", 1); // xlSolid
    		vInter.OlePropertySet("PatternColorIndex", -4105); // xlAutomatic
      }
    
    //----------------------------------------------------------------------------
      //将DBGrid中得数据写入EXCEL
     dbg->DataSource->DataSet->First() ;
    
      for(int i=0; i<nRowCount-1; i++)
        {
           // 普通数据行的行高16
            ExcelApp.OlePropertyGet("Rows", i + 2).OlePropertySet("RowHeight", 16);
    		// 63 63 72 75 6E 2E 63 6F 6D
    		for(int j=0; j<dbg->Columns->Count; j++)
            {
    		Sheet1.OlePropertyGet("Cells", i + 2, j + 1).OlePropertySet("Value",WideString(dbg->DataSource->DataSet->FieldByName(dbg->Columns->Items[j]->FieldName)->AsString));
            }
            dbg->DataSource->DataSet->Next();
    	}
    
        Sheet1 = Unassigned;
        ExcelApp = Unassigned;
        // 工作结束
    
    }
    //----------------------------------------------------------------------------
    //DBGrid转化为word表格
    void __fastcall DBGrid2Word(TDBGrid *dbg)
    {
       if(!dbg->DataSource->DataSet->Active) // 数据集没有打开就返回
            return;
        Variant vWordApp, vTable, vCell;
        try
        {
            vWordApp = Variant::CreateObject("Word.Application");
        }
        catch(...)
        {
    	    MessageDlg("无法启动Word,可能尚未安装或文件已经损坏！",mtError,TMsgDlgButtons()<<mbYes,0);
            vWordApp = Unassigned;
            return;
        }
        // 隐藏Word界面
        vWordApp.OlePropertySet("Visible", true);
        // 新建一个文档
        vWordApp.OlePropertyGet("Documents").OleFunction("Add");
        //
        Variant vSelect = vWordApp.OlePropertyGet("Selection");
        // 设置一下字体，大小
        vSelect.OlePropertyGet("Font").OlePropertySet("Size", dbg->Font->Size);
        vSelect.OlePropertyGet("Font").OlePropertySet("Name", WideString(dbg->Font->Name));
        // 要插入表格的行数
        int nRowCount(dbg->DataSource->DataSet->RecordCount + 1);
        nRowCount = nRowCount < 2? 2: nRowCount;
        // 要插入表格的列数
        int nColCount(dbg->Columns->Count);
        nColCount = nColCount < 1? 1: nColCount;
        // 在Word文档中插入与DBGrid行数列数基本相同的一个表格
        vWordApp.OlePropertyGet("ActiveDocument").OlePropertyGet("Tables")
            .OleProcedure("Add",
            vSelect.OlePropertyGet("Range"),
            nRowCount, // 行数
            nColCount, // 列数
            1, // DefaultTableBehavior:=wdWord9TableBehavior
            0); // AutoFitBehavior:=wdAutoFitFixed
        // 操作这个表格
        vTable = vWordApp.OlePropertyGet("ActiveDocument").
            OleFunction("Range").OlePropertyGet("Tables").OleFunction("Item", 1);
        // 设置单元格的宽度
        for(int i=0; i<nColCount; i++)
        {
            int nColWidth = dbg->Columns->Items[i]->Width;
            vTable.OlePropertyGet("Columns").OleFunction("Item", i + 1)
                    .OlePropertySet("PreferredWidthType", 3); // wdPreferredWidthPoints
            vTable.OlePropertyGet("Columns").OleFunction("Item", i + 1)
                    .OlePropertySet("PreferredWidth", nColWidth);
        }
        //----------------------------------------------------------------------------
       
        // 先将列名写入Word表格
        for(int j=0; j<dbg->Columns->Count; j++)
        {
            vCell = vTable.OleFunction("Cell", 1, j + 1);
            vCell.OlePropertySet("Range", WideString(dbg->Columns->Items[j]->FieldName));
            // 列名单元格背景颜色 // wdColorGray125
            vCell.OlePropertyGet("Shading")
                    .OlePropertySet("BackgroundPatternColor", 14737632);
        }
        // 将DBGrid中的数据写入Word表格
        dbg->DataSource->DataSet->First();
        for(int i=0; i<nRowCount; i++)
        {
            // 63 63 72 75 6E 2E 63 6F 6D
            for(int j=0; j<dbg->Columns->Count; j++)
            {
                vCell = vTable.OleFunction("Cell", i + 2, j + 1);
                vCell.OlePropertySet("Range", WideString(dbg->DataSource->DataSet->FieldByName(dbg->Columns->Items[j]->FieldName)->AsString));
            }
            dbg->DataSource->DataSet->Next();
        }
       
        
        Application->ProcessMessages();
        vWordApp = Unassigned;
      // 工作结束
      
    }
    
    
    
    
    // 测试代码
    /*DBGrid2Word(DBGrid1");
      DBGrid3Excel(DBGrid1");
      DBGrid2Excel(DBGrid1, "C:\\123.xls");
      */