VC++实现对Excel操作（3）

//保存Excel，我都是用的这个，以前直接用API实现的时候，总是会出现一些问题，比如只会保存一个Sheet等，但封装成类后没有出现过了

void CMyExcel::SaveAs(CString strPath)
{
	if(IsFileExist(strPath,FALSE) == TRUE)
	{
		DeleteFile(strPath);
	}
	MyBook.SaveAs(_variant_t(strPath),vtMissing,vtMissing,vtMissing,vtMissing,vtMissing
		,0,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);
	strFilePath = strPath;
}

BOOL CMyExcel::IsFileExist(CString strFn,BOOL bDir)
{
	HANDLE handle;
	LPWIN32_FIND_DATA lpFindData = new WIN32_FIND_DATA;
	BOOL bFind = false;
	if(lpFindData)
	{
		handle = FindFirstFile(strFn,lpFindData);
		bFind = (handle != INVALID_HANDLE_VALUE);
		if(bFind)
		{
			if(bDir)
			{
				bFind = (lpFindData->dwFileAttributes & FILE_ATTRIBUTE_DIRECTORY) != NULL;
			}
			FindClose(handle);
		}
		delete lpFindData;
	}
	return bFind;
}
//增加Sheet
void CMyExcel::AddSheet(CString strSheet)
{
	LPDISPATCH lpDisp = NULL;
	lpDisp = MySheets.Add(vtMissing,_variant_t(MySheets.GetItem(COleVariant(MySheets.GetCount()))),vtMissing,vtMissing);
	MySheet.AttachDispatch(lpDisp,TRUE);
	MySheet.SetName(strSheet);
	lpDisp = MySheet.GetCells();
	MyRange.AttachDispatch(lpDisp,TRUE);
}
//使用Open()函数后，会默认新建三个Sheet，这个函数就是删除这三个Sheet
void CMyExcel::DeleteSheet123()
{
	_Worksheet sheet;
	sheet = MySheets.GetItem(COleVariant("Sheet1"));
	sheet.Activate();
	sheet.Delete();
	sheet = MySheets.GetItem(COleVariant("Sheet2"));
	sheet.Activate();
	sheet.Delete();
	sheet = MySheets.GetItem(COleVariant("Sheet3"));
	sheet.Activate();
	sheet.Delete();
}


void CMyExcel::GetRange(CString strBegin,CString strEnd)
{
	MyRange = MySheet.GetRange(_variant_t(strBegin),_variant_t(strEnd));
}

void CMyExcel::AutoColFit()
{
	Range range = MyRange.GetEntireColumn();
	range.AutoFit();
	range.ReleaseDispatch();
}

void CMyExcel::AutoRowFit()
{
	Range range = MyRange.GetEntireRow();
	range.AutoFit();
	range.ReleaseDispatch();
}
//设置一个单元格中的内容是否换行
void CMyExcel::SetWrapText(BOOL bTrue)
{
	MyRange.SetWrapText(_variant_t((short)bTrue));
}
//让Excel显示
void CMyExcel::SetVisible(BOOL bVisible)
{
	MyApp.SetVisible(TRUE);

}

void CMyExcel::SetNumberFormat(MyNumberFormat XNumberFormat)
{
	MyRange.SetNumberFormat(_variant_t(XNumberFormat.strValue));
}
//关闭Excel
void CMyExcel::Exit()
{
	COleVariant covFalse((short)FALSE);
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
	MyRange.ReleaseDispatch();
	MySheet.ReleaseDispatch();
	MySheets.ReleaseDispatch();
	MyBook.Close(covFalse,_variant_t(strFilePath),covOptional);
	MyBook.ReleaseDispatch();
	MyBooks.Close();
	MyBooks.ReleaseDispatch();
	MyApp.Quit();
	MyApp.ReleaseDispatch();
	CoUninitialize();
}
//设置字体
void CMyExcel::SetFont(MyFont font)
{
	Font f = MyRange.GetFont();
	f.SetName(_variant_t(font.Name));
	//f.SetShadow((_variant_t)(short)font.Shadow);
	f.SetSize((_variant_t)(short)font.size);
	f.SetUnderline((_variant_t)(short)font.UnderLine);
	f.SetBold((_variant_t)(short)font.Bold);
	f.SetColor((_variant_t)(long)font.ForeColor);
	f.SetItalic((_variant_t)(short)font.Italic);
	f.SetStrikethrough((_variant_t)(short)font.StrikeThrough);
	f.SetSubscript((_variant_t)(short)font.Subscript);
	f.SetSuperscript((_variant_t)(short)font.Subscript);
	f.ReleaseDispatch();
}

void CMyExcel::PrePrintOut(BOOL bEnable)
{
	COleVariant covOptional((short)bEnable);
	MySheet.PrintPreview(covOptional);
}

void CMyExcel::PrintOut(short numCopy)
{
	COleVariant covTrue((short)TRUE);
	COleVariant covFalse((short)FALSE);
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
	MySheet.PrintOut(vtMissing,vtMissing,_variant_t((short)numCopy),vtMissing,vtMissing,vtMissing,covTrue,vtMissing,vtMissing);
}
//默认设置字体，因为日本那边用Arial字体的Excel表格数据出现问题少，而且看的舒服，所以要求用这个
MyFont::MyFont()
{
	Name = "Arial";
	size = 10;
	ForeColor = RGB(0,0,0);
	Bold = FALSE;
	Italic = FALSE;
	StrikeThrough = FALSE;
	Shadow = FALSE;
	Subscript = FALSE;
	Superscript = FALSE;
	UnderLine = xlUnderlineStyleNone;
}

MyBackGroundStyle::MyBackGroundStyle()
{
	Color = RGB(255,255,255);
	Pattern = xlSolid;
	PatternColor = RGB(255,0,0);
	transparent = FALSE;
}
//设置单元格内容对齐方式
MyAlignment::MyAlignment()
{
	HorizontalAlignment = xlGeneral;
	VerticalAlignment = xlCenter;
}

MyNumberFormat::MyNumberFormat()
{
	strValue = "G/通用格式";
}

CString MyNumberFormat::GetTextSetup()
{
	strValue = "@";
	return strValue;
}

CString MyNumberFormat::GetGeneralSetup()
{
	strValue = "G/通用格式";
	return strValue;
}

CString MyNumberFormat::GetNumberSetup(BOOL bSeparator,int iNumDecimal)
{
	CString str = "0";

	return str;
}

CString MyNumberFormat::GetDateSetup(BOOL bInChinese)
{
	if(bInChinese == TRUE)
	{
		strValue = "yyyy\"年\"m\"月\"d\"日\";@";
	}
	else
	{
		strValue = "yyyy-m-d;@";
	}
	return strValue;
}

CString MyNumberFormat::GetDateTimeSetup(BOOL bInChinese)
{
	if(bInChinese == TRUE)
	{
		strValue = "yyyy\"年\"m\"月\"d\"日\" h\"时\"mm\"分\"ss\"秒\";@";
	}
	else
	{
		strValue = "yyyy-m-d h:mm:ss;@";
	}
	return strValue;
}

CString MyNumberFormat::GetSpecialNumberSetup(BOOL bInChinese)
{
	if(bInChinese == TRUE)
	{
		strValue = "[DBNum1][$-804]G/通用格式";
	}
	else
	{
		strValue = "[DBNum2][$-804]G/通用格式";
	}
	return strValue;
}

CString MyNumberFormat::GetFractionNumberSetup(int DownSum,int DownNum)
{
	CString str = "0";

	return str;	
}

CString MyNumberFormat::GetMoneySetup(BOOL bInChinese,int iNumDecimal)
{
	CString str = "0";

	return str;	
}

CString MyNumberFormat::GetScientificNumberSetup(int iNumDecimal)
{
	CString str = "0";

	return str;
}

CString MyNumberFormat::GetPercentNumberSetup(int iNumDecimal)
{
	CString str = "0";

	return str;
}

CString MyNumberFormat::GetTimeSetup(BOOL bInChinese)
{
	CString str = "0";

	return str;
}

CString MyNumberFormat::GetPostalcodeSetup(int Num)
{
	CString str = "0";

	return str;
}

void CMyExcel::SetAlignment(MyAlignment XMyAlignment)
{
	MyRange.SetHorizontalAlignment(_variant_t(XMyAlignment.HorizontalAlignment));
	MyRange.SetVerticalAlignment(_variant_t(XMyAlignment.VerticalAlignment));
}