C++代码操作EXCEL(新建，读写，保存)

类库导入

1. 右键—>建立类向导—>Add Class—>From a Type Lib…—>File(选中EXCEL.EXE)， 就可以生成excel.h，excel.cpp
   
   
   

初始化COM

if (!AfxOleInit())
{
	AfxMessageBox(AFX_IDP_FAILED_TO_CREATE);
	return false;
}

打开EXCEL文件，没有就新建BOOK

CString strFile = excel文件path;
_Application ExcelApp; 
Workbooks wbsMyBooks; 
_Workbook wbMyBook; 
Worksheets wssMysheets; 
_Worksheet wsMysheet; 
try
{
	Range rgMyRge; 
	if (!ExcelApp.CreateDispatch("Excel.Application",NULL)) 
	{ 
		return;
	} 
	// Excelの警告不表示
	ExcelApp.SetDisplayAlerts(FALSE);
	COleVariant option((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
	wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true); 
	// excel文件存在时
	if(hasExcelFile){
		wbMyBook.AttachDispatch(wbsMyBooks.Open(strFile, 
			option,option,option,option,option,option,
			option,option,option,option,option,option)); 
	}
	// excel文件不存在时
	else
	{
		wbMyBook = wbsMyBooks.Add(option);
	}
	wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);		
	wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t((long)1)),true);
}

保存 退出进程

		// Excel保存
		wbMyBook.SaveAs(COleVariant(strFilePath),
			_variant_t(vtMissing),
			_variant_t(vtMissing),
			_variant_t(vtMissing),
			_variant_t(vtMissing),
			_variant_t(vtMissing),
			0,
			_variant_t(vtMissing),
			_variant_t(vtMissing),
			_variant_t(vtMissing),
			_variant_t(vtMissing));
		wbMyBook.Close(_variant_t(false), _variant_t(strFilePath), _variant_t(true));
		wsMysheet.ReleaseDispatch();
		wssMysheets.ReleaseDispatch();
		wbMyBook.ReleaseDispatch();
		wbsMyBooks.ReleaseDispatch();
		ExcelApp.ReleaseDispatch();

EXCEL的基本操作

读cell内容

VARIANT var;
VariantInit(&var);
rgMyRge.AttachDispatch(wsMysheet.GetRange(COleVariant("A1"),COleVariant("A1")));
var = rgMyRge.GetValue();
if(var.vt == VT_BSTR)
{
	strValue = var.bstrVal;
}
rgMyRge.ReleaseDispatch();

往cell里写内容

rgMyRge.AttachDispatch(wsMysheet.GetRange(COleVariant("A1"),COleVariant("A1")));
rgMyRge.SetValue(COleVariant(strValue));
rgMyRge.ReleaseDispatch();

设置cell的格式

A1-D100的全部cell设置成文字列的格式

rgMyRge.AttachDispatch(wsMysheet.GetRange(COleVariant("A1"), COleVariant("D100")));
rgMyRge.SetNumberFormat(COleVariant("@"));
rgMyRge.ReleaseDispatch();

合并单元格

rgMyRge.AttachDispatch(wsMysheet.GetRange(COleVariant("A1"), COleVariant("A2")));
rgMyRge.SetMergeCells((COleVariant)(short)(TRUE));
rgMyRge.ReleaseDispatch();

设置列宽

rgMyRge.AttachDispatch(wsMysheet.GetRange(COleVariant("A1"), COleVariant("A1")));
rgMyRge.SetColumnWidth(COleVariant((long)20));
rgMyRge.ReleaseDispatch();

设置背景色

//背景色
Interior it;
rgMyRge.AttachDispatch(wsMysheet.GetRange(COleVariant("A1"), COleVariant("B20")));
it.AttachDispatch(rgMyRge.GetInterior());
it.SetColorIndex(_variant_t(long(20)));
it.ReleaseDispatch();
rgMyRge.ReleaseDispatch();

ColorIndex的颜色对应表

字体颜色设置

//字体颜色
Font font;
font.AttachDispatch(rgMyRge.GetFont());
font.SetColorIndex(COleVariant(long(3)));
font.ReleaseDispatch();

cell的外框线设置

//枠線
Borders bord;
rgMyRge.AttachDispatch(wsMysheet.GetRange(COleVariant("A1"), COleVariant("A1")));
bord.AttachDispatch(rgMyRge.GetBorders());
bord.SetColorIndex(_variant_t(long(1)));
bord.SetLineStyle(_variant_t(long(1)));
bord.SetWeight(_variant_t(long(2)));
bord.ReleaseDispatch();
rgMyRge.ReleaseDispatch();