使用VB或VC操作Excel 2003

乌9319

　　1.VB操作EXCEL2003
　　‘************************************************************************‘‘ 演示用VB.NET 设置 Excel 单元格值和风格的小程序(例程)‘ 程序功能是：打开文件，设置单元格的风格和值，冻结窗口等‘ 网上类似的程序很多，但Excel 2003的SaveAs函数由新版本的函数代替，原函数‘ 为_saveas，这个程序同时也演示了如何使用新函数，另外也演示了如何冻结窗格‘‘ 关键字：Excel 2003 SaveAs 冻结窗格 单元格 风格‘‘‘ 开发环境：Visual Studio .NET 2003‘ Excel环境：Excel 2003‘ 作者：高宏伟(DukeJoe)‘ 时间：2006-03-12 11:27‘ 地点：黑龙江省哈尔滨市平房区‘ 注释：无‘‘***********************************************************************Module Module1Sub Main()Dim ThisApplication As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.ApplicationDim ThisWorkbook As Microsoft.Office.Interop.Excel.WorkbookDim ThisSheets As Microsoft.Office.Interop.Excel.SheetsDim ThisWorkSheet As Microsoft.Office.Interop.Excel.WorksheetDim ThisRange As Microsoft.Office.Interop.Excel.RangeThisApplication.Visible = TrueThisWorkbook = ThisApplication.Workbooks.Add()Console.WriteLine(ThisWorkbook.Name)ThisSheets = ThisWorkbook.WorksheetsThisWorkSheet = ThisSheets(1)Console.WriteLine(ThisWorkSheet.Name)’ 设置整个sheet的填充色为白色ThisWorkSheet.Cells.Interior.Color = RGB(255, 255, 255)ThisWorkSheet.Cells.ClearContents()ThisRange = ThisWorkSheet.Range("A1:C5")Console.WriteLine(ThisRange.Cells(1, 1).Value)’ThisRange.Interior.Color = 0ThisRange.ClearFormats()ThisRange.Cells(1, 1).Value = "哈尔滨市平房区"ThisRange.Cells(1, 2).Value = "高宏伟"ThisRange.Cells(1, 3).Value = "QQ：21807822"ThisRange.Cells(2, 1).Value = "1"ThisRange.Cells(3, 1).Value = "2"ThisRange.Cells(4, 1).Value = "3"ThisRange.Cells(5, 1).Value = "4"’ 为Range的四周和内部加上边框ThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuousThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuousThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuousThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuousThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuousThisRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous’ 设置第一行的格式（背景色、粗体、颜色、列宽）ThisRange.Range("A1:C1").Interior.ColorIndex = 47ThisRange.Range("A1:C1").Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolidThisRange.Range("A1:C1").Font.ColorIndex = 6ThisRange.Range("A1:C1").Font.Bold = TrueThisRange.EntireColumn.ColumnWidth = 18.63ThisRange.Range("A2:C5").Interior.ColorIndex = 16ThisRange.Range("A2:C5").Interior.Pattern = Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolidThisRange.Range("A2:C5").Font.ColorIndex = 2’ 冻结窗格ThisApplication.ActiveWindow.FreezePanes = FalseThisApplication.Range("A2").Select()ThisApplication.ActiveWindow.FreezePanes = TrueThisWorkbook.SaveAs("D:/Visual Studio Projects/VBAReportDemo/bin/VBAReportDemo.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, _"", "", False, False, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, _Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges, False)ThisWorkbook.Close()ThisApplication.Quit()Console.WriteLine("press any key to continue")’Console.ReadLine()End SubEnd Module
　　2.VC操作EXCEL2003
　　/******************************************************************************** 这是上面VBAReportDemo的对应程序* 演示使用VC操作Excel 2003* 功能：设置单元格，风格，冻结窗格。以及如何将对应的VB代码翻译成VC** 时间：2007-04-16 09:31* 作者：高宏伟(DukeJoe)* QQ：21807822* Blog：http://dukejoe.yeah.net* 注释：本代码可单独研究，但最好和上面的VBAReportDemo对应看比较好** 开发环境：Visual Studio.NET 2003* 操作系统：Windows XP Home Edition Service Pack 2*******************************************************************************/#include <iostream>using namespace std ;#import "C:/Program Files/Common Files/Microsoft Shared/OFFICE11/mso.dll" rename("RGB", "MSRGB")#import "C:/Program Files/Common Files/Microsoft Shared/VBA/VBA6/VBE6EXT.OLB" raw_interfaces_only, /rename("Reference", "ignorethis"), rename("VBE", "JOEVBE")#import "C:/Program Files/Microsoft Office/OFFICE11/excel.exe" exclude("IFont", "IPicture") /rename("RGB", "ignorethis"), rename("DialogBox", "ignorethis"), rename("VBE", "JOEVBE"), /rename("ReplaceText", "JOEReplaceText"), rename("CopyFile","JOECopyFile"), /rename("FindText", "JOEFindText"), rename("NoPrompt", "JOENoPrompt")using namespace Office;using namespace VBIDE;using namespace Excel ;int ExportExcelFile() ;int main(int argc, char* argv[]){if(FAILED(::CoInitialize(NULL)))return 1 ;ExportExcelFile() ;::CoUninitialize();return 0;}int ExportExcelFile(){_ApplicationPtr pApplication = NULL ;_WorkbookPtr pThisWorkbook = NULL ;_WorksheetPtr pThisWorksheet = NULL ;SheetsPtr pThisSheets = NULL ;RangePtr pThisRange = NULL ;_variant_t vt ;Excel::XlFileFormat vFileFormat ;Excel::XlSaveAsAccessMode vSaveAsAccessMode ;Excel::XlSaveConflictResolution vSaveConflictResolution ;pApplication.CreateInstance("Excel.Application");pApplication->PutVisible (0,VARIANT_TRUE);pThisWorkbook = pApplication->GetWorkbooks()->Add() ;pThisSheets = pThisWorkbook->GetWorksheets() ;pThisWorksheet = pThisSheets->GetItem((short)1);// 设置整个sheet的填充色为白色pThisWorksheet->GetCells()->GetInterior()->PutColor(RGB(255, 255, 255));pThisWorksheet->GetCells()->ClearContents() ;pThisRange = pThisWorksheet->GetRange("A1:C5") ;pThisRange->ClearFormats() ;// 如果有不会的，可以在debug文件夹的excel.tlh里找找//pThisRange->GetItem(1,1) ;pThisRange->PutItem(1, 1, _variant_t("哈尔滨市平房区")) ;pThisRange->PutItem(1, 2, _variant_t("高宏伟")) ;pThisRange->PutItem(1, 3, _variant_t("QQ:21807822")) ;pThisRange->PutItem(2, 1, _variant_t("1")) ;pThisRange->PutItem(3, 1, _variant_t("2")) ;pThisRange->PutItem(4, 1, _variant_t("3")) ;pThisRange->PutItem(5, 1, _variant_t("4")) ;// 为Range的四周和内部加上边框pThisRange->GetBorders()->GetItem(xlEdgeLeft)->PutLineStyle(xlContinuous) ;pThisRange->GetBorders()->GetItem(xlEdgeTop)->PutLineStyle(xlContinuous) ;pThisRange->GetBorders()->GetItem(xlEdgeRight)->PutLineStyle(xlContinuous) ;pThisRange->GetBorders()->GetItem(xlEdgeBottom)->PutLineStyle(xlContinuous) ;pThisRange->GetBorders()->GetItem(xlInsideHorizontal)->PutLineStyle(xlContinuous) ;pThisRange->GetBorders()->GetItem(xlInsideVertical)->PutLineStyle(xlContinuous) ;// 设置第一行的格式（背景色、粗体、颜色、列宽）pThisRange->GetRange("A1:C1")->GetInterior()->ColorIndex = 47 ;pThisRange->GetRange("A1:C1")->GetInterior()->Pattern = xlPatternSolid ;pThisRange->GetRange("A1:C1")->GetFont()->ColorIndex = 6 ;pThisRange->GetRange("A1:C1")->GetFont()->Bold = TRUE ;pThisRange->GetEntireColumn()->ColumnWidth = 18.63 ;pThisRange->GetRange("A2:C5")->GetInterior()->ColorIndex = 16 ;pThisRange->GetRange("A2:C5")->GetInterior()->Pattern = xlPatternSolid ;pThisRange->GetRange("A2:C5")->GetFont()->ColorIndex = 2 ;// 冻结窗格pApplication->ActiveWindow->FreezePanes = FALSE ;pApplication->Range["A2"]->Select() ;pApplication->ActiveWindow->FreezePanes = TRUE ;// 存盘退出vSaveAsAccessMode = xlNoChange ;vFileFormat = xlWorkbookNormal ;vSaveConflictResolution = xlLocalSessionChanges ;pThisWorkbook->SaveAs(_variant_t("D://Visual Studio Projects//VCReportDemo//joe.xls"), vFileFormat,_variant_t(""),_variant_t(""), _variant_t(false),_variant_t(false), vSaveAsAccessMode, vSaveConflictResolution, _variant_t(false)) ;pThisWorkbook->Close();pApplication->Quit();return 0 ;}
　　
　　原文网址：http://blog.donews.com/dukejoe/archive/2007/04/16/1156319.aspx