您当前的位置:首页 > 计算机 > 编程开发 > VC/VC++

MyExcel(封装类、方便对EXCEL文件操作)

时间:09-16来源:作者:点击数:
CDSY,CDSY.XYZ

此类方便了VC对EXCEL的操作。功能有:创建指定名称的EXCEL、打开新的Excel文件、打开名为strSheet的表、打开strFile文件、设置(ROW,COL)的字符strText、取得(ROW,COL)的字符、显示excel、查找此文件是否存在、保存为strPath、添加新的表、列自动展开、行自动展开、设置自动换行、设置字体、设置当前单元格颜色、合并单元格、设置背景、设置边框、设置对齐方式、设置列宽、设置行高等等。这样功能使用十分方便。源码可以直接使用,添加到项目中。有任何问题可评论或私信。

源码如下

头文件MyExcel.h

//使用说明:
//此类的设计方便了VC对EXCEL的操作。主要功能如下:
//	A.打开EXCEL文档进行修改和保存
//	B.新建EXCEL文档进行操作存储
//	B.读取和填写EXCEL中的数据
//	C.设置EXCEL中边框的参数
//	D.设置EXCEL中背景颜色的参数
//	E.打印和预览
//	F.自动回收内存
//	G.在显示之后,此类将自动关闭不能进行其它相关的操作
//---------------------------------------------------------
#include "excel9.h"
#include <comdef.h>
//下滑线的种类
#define xlUnderlineStyleDouble -4119
#define xlUnderlineStyleDoubleAccounting 5
#define xlUnderlineStyleNone -4142
#define xlUnderlineStyleSingle 2
#define xlUnderlineStyleSingleAccounting 4
//边框方位
#define xlDiagonalDown 5
#define xlDiagonalUp 6
#define xlEdgeBottom 9
#define xlEdgeLeft 7
#define xlEdgeRight 10
#define xlEdgeTop 8
#define xlInsideHorizontal 12
#define xlInsideVertical 11
//边框划线类型
#define xlContinuous 1
#define xlDash -4115
#define xlDashDot 4
#define xlDashDotDot 5
#define xlDot -4118
#define xlDouble -4119
#define xlLineStyleNone -4142
#define xlNone -4142
#define xlSlantDashDot 13
//字体水平对齐方式
#define xlGeneral 1
#define xlCenter -4108
#define xlLeft -4131
#define xlRight -4152
#define xlFill 5
#define xlJustify -4130
//字体垂直对齐方式
// Download by http://www.codefans.net
#define xlTop -4160
#define xlBottom -4107
//边框划线的粗细
#define xlHairline 1
#define xlMedium -4138
#define xlThick 4
#define xlThin 2
//背景图案的类型
#define xlSolid 1
#define xlGray8 18
#define xlGray75 -4126
#define xlGray50 -4125
#define xlGray25 -4124
#define xlGray16 17
#define xlHorizontal -4128
#define xlVertical -4166
#define xlDown -4121
#define xlUp -4162
#define xlChecker 9
#define xlSemiGray75 10
#define xlLightHorizontal 11
#define xlLightVertical 12
#define xlLightDown 13
#define xlLightUp 14
#define xlGrid 15
#define xlCrissCross 16
//边框设置参数类
class MyBorder
{
public:
	//线条形状
	short LineStyle;
	//粗线
    short Weight;
	//颜色
    long Color;
	//构造函数
	MyBorder();
};
//背景设置参数类
class MyBackStyle
{
public:
	//背景颜色
	long Color;
	//背景图案
    short Pattern;
	//背景图案颜色
    long PatternColor;
	//透明不透明
	BOOL transparent;
	//构造函数
	MyBackStyle();
};
//字体设置参数类
class MyFont
{
public:
	//名字
    CString Name;
	//大小
	long size;
	//前景
	long ForeColor;
	//粗体
	BOOL Bold;
	//斜体
	BOOL Italic;
	//中间线
	BOOL Strikethrough;
	//阴影
	BOOL Shadow;
	//下标
	BOOL Subscript;
	//上标
	BOOL Superscricp;
	//下划线
	short Underline;
	//构造函数
	MyFont();
};
//文字对齐方式设置参数类
class MyAlignment
{
public:
	//水平对齐
	short HorizontalAlignment;
	//垂直对齐
	short VerticalAlignment;
	//构造函数
	MyAlignment();
};
//列数据类型的设置参数类
class MyNumberFormat
{
public:
	MyNumberFormat();
	//设置参数
	CString strValue;
	//返回文本类型的设置
	CString GetText();
	//返回数字格式的设置
	//blnBox: 使用分隔符 RightSum: 小数位数 
	CString GetNumber(BOOL blnBox,int RightSum);
	//返回货币格式的设置
	//blnChinese: TURE:"¥",FALSE:"$"
	//RightSum: 小数位数 
	CString GetMoney(BOOL blnChinese,int RightSum);
	//返回日期格式的设置
	//blnChinese: TURE:"年月日",FALSE:"-"
	CString GetDate(BOOL blnChinese);
	//返回时间格式的设置
	//blnChinese: TURE:"时分秒",FALSE:":"
	CString GetTime(BOOL blnChinese);
	//返回常规设置
	CString GetGeneral();
	//返回特殊数字的设置
	//blnChinese: TURE:大写,FALSE:小写
	CString GetDBNumber(BOOL blnChinese);
	//返回百分数的设置
	//RightSum: 小数位数 
	CString GetPercentNumBer(int RightSum);
	//返回分数的设置
	//DownSum:分母位数 DownNum(0): 固定分母数(0)
	CString GetFractionNumBer(int DownSum,int DownNum);
	//返回科学计数的设置
	//RightSum: 小数位数 
	CString GetTechNumBer(int RightSum);
	//返回6位邮政编码格式
	//Sum: 固定邮政编码的位数
	CString GetPost(int Sum);
	//返回日期加时间的设置
	//blnChinese: TURE:"年月日时分秒",FALSE:"-:"
	CString GetDateTime(BOOL blnChinese);
};
class CMyExcel
{
public:
	//Excel的应用
	_Application   MyApp;  
	_Workbook   MyBook;
	_Worksheet   MySheet;   
	Workbooks   MyBooks;   
	Worksheets   MySheets;   
	Range   MyRange;  
	CString strFilePath;
	excelFont myfont;
	//构造函数
	CMyExcel();
	//析构函数
	~CMyExcel();

	//创建指定名称的EXCEL
	BOOL CreateExcel(CString strFile);
	//创建指定名称的EXCEL
	BOOL OpenExcel(CString strFile);
	//打开新的Excel文件
	BOOL Open();
	//打开strFile文件
	BOOL Open(CString strFile);
	//打开名为strSheet的表
	BOOL OpenSheet(CString strSheet);
	//设置(ROW,COL)的字符strText
	BOOL SetItemText(long Row,long Col,CString strText);
	//取得(ROW,COL)的字符
	CString GetItemText(long Row,long Col);
	//退出excel
	void Exit();
	//显示excel
	void SetVisible(BOOL blnVisible);
	//查找此文件是否存在
	BOOL IsFileExist(CString strFn, BOOL bDir);
	//自动保存(针对打开已经存在的文件)
	void Save();
	//保存为strPath
	void SaveAs(CString strPath);
	//添加新的表
	void AddSheet(CString strSheet);
	//得到新的选择区域
	void GetRange(CString strBegin,CString strEnd);
	//列自动展开
	void AutoColFit();
	//行自动展开
	void AutoRowFit();
	//设置自动换行
	void SetWrapText(BOOL blnTrue);
	//设置字体
	void SetFont(MyFont font);
	//设置当前单元格颜色
	void SetCurColor(int nrow, int ncolumn, COLORREF  color);
	//得到整个区域
	void AutoRange();
	//合并单元格
	void SetMergeCells(BOOL blnTrue);
	//设置背景
	void SetBackStyle(MyBackStyle BackStyle);
	//设置边框
	void SetBorderLine(short Xposition,MyBorder XBorder);
	//设置对齐方式
	void SetAlignment(MyAlignment XMyAlignment);
	//得到列数
	long GetRowS();
	//得到行数
	long GetColS();
	//设置数据的类型
	void SetNumberFormat(MyNumberFormat XNumberFormat);
	//设置列宽
	void SetColumnWidth(int intWidth);
	//设置行高
	void SetRowHeight(int intHeight);
	//打印
	//CopySum:打印的份数
	void PrintOut(short CopySum);
	//打印预览
	//blnEnable:TRUE-允许修改 FALSE-不允许
	void PrePrintOut(BOOL blnEnable);
	//插入图片
	//strFilePath:文件名路径
	void InsertPicture(CString strFilePath);
	//设置背景图片
	//strFilePath:文件名路径
	void SetBackPicture(CString strFilePath);
	//返回当前程序所在路径
	CString GetAppPath();
	// 得到当前文件的行数
	long GetCurRow();
	// 得到当前文件的列数
	long GetCurCol();

};

源文件MyExcel.cpp

#include"pch.h"
//#include "stdafx.h"
#include "MyExcel.h"
CMyExcel::CMyExcel()
{
	strFilePath=_T("");
}

CString CMyExcel::GetAppPath()
{
	char lpFileName[MAX_PATH];
	GetModuleFileName(AfxGetInstanceHandle(),lpFileName,MAX_PATH);

	CString strFileName = lpFileName;
	int nIndex = strFileName.ReverseFind ('\\');
	
	CString strPath;

	if (nIndex > 0)
		strPath = strFileName.Left (nIndex);
	else
		strPath = "";
	return strPath;
}
CMyExcel::~CMyExcel()
{
	COleVariant covFalse((short)FALSE);
	COleVariant	covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); 
	MyRange.ReleaseDispatch();
	myfont.ReleaseDispatch();
	MySheet.ReleaseDispatch();
	MySheets.ReleaseDispatch();
	MyBook.Close(covFalse,_variant_t(strFilePath),covOptional);
	MyBook.ReleaseDispatch();
	MyBooks.Close();
	MyBooks.ReleaseDispatch();
	MyApp.Quit();
	MyApp.ReleaseDispatch();
	CoUninitialize();
}



//创建指定名称的EXCEL
BOOL CMyExcel::CreateExcel(CString strFile)
{
	CoInitialize(NULL);
	if (!MyApp.CreateDispatch("Excel.Application",NULL))
	{
		AfxMessageBox(_T("EXCEL初始化时出错!"),MB_OK|MB_ICONERROR);
		return FALSE;
	}
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);   


	MyBooks.AttachDispatch(MyApp.GetWorkbooks(),TRUE);

	MyBook.AttachDispatch(MyBooks.Add(covOptional),TRUE);
	
	MySheets.AttachDispatch(MyBook.GetWorksheets(),TRUE);
	
	MySheet.AttachDispatch(MySheets.GetItem(_variant_t((long)1)), TRUE);
	MySheet.SetName("Sheet1");

	MyRange.AttachDispatch(MySheet.GetCells(),TRUE);


	return TRUE;


}


BOOL CMyExcel::OpenExcel(CString strFile)
{
	CoInitialize(NULL);
	if (!MyApp.CreateDispatch("Excel.Application",NULL))
	{
		AfxMessageBox(_T("EXCEL初始化时出错!"),MB_OK|MB_ICONERROR);
		return FALSE;
	}
	
	LPDISPATCH lpDisp=NULL;

	MyBooks=MyApp.GetWorkbooks();
	MyBook = MyBooks.Open(strFile, vtMissing, vtMissing, vtMissing, 
		vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,vtMissing);
	MySheets = MyBook.GetSheets();
	//	MySheets.Add(vtMissing, vtMissing, _variant_t((long)1), vtMissing);
	MySheet.AttachDispatch(MySheets.GetItem(_variant_t((long)1)), TRUE);
	MySheet.SetName("Sheet1");

	lpDisp=MySheet.GetCells();
	MyRange.AttachDispatch(lpDisp,TRUE);

	return TRUE;




}


BOOL CMyExcel::Open()
{
	LPDISPATCH lpDisp=NULL;
	COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);   
	CoInitialize(NULL);
	if (!MyApp.CreateDispatch("Excel.Application",NULL))
	{
		AfxMessageBox(_T("EXCEL初始化时出错!"),MB_OK|MB_ICONERROR);
		return FALSE;
	}
	lpDisp=MyApp.GetWorkbooks();
	MyBooks.AttachDispatch(lpDisp,TRUE);
	lpDisp = MyBooks.Add(covOptional); 
	MyBook.AttachDispatch(lpDisp,TRUE);
	lpDisp=MyBook.GetWorksheets();
	MySheets.AttachDispatch(lpDisp,TRUE);


	return TRUE;

}
BOOL CMyExcel::Open(CString strFile)
{
	CoInitialize(NULL);
	if (!MyApp.CreateDispatch("Excel.Application",NULL))
	{
		AfxMessageBox(_T("EXCEL初始化时出错!"),MB_OK|MB_ICONERROR);
		return FALSE;
	}

	MyBooks=MyApp.GetWorkbooks();
	MyBook = MyBooks.Open(strFile, vtMissing, vtMissing, vtMissing, 
		vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,vtMissing);
	MySheets = MyBook.GetSheets();
	//	MySheets.Add(vtMissing, vtMissing, _variant_t((long)1), vtMissing);
	MySheet.AttachDispatch(MySheets.GetItem(_variant_t((long)1)), TRUE);
	MySheet.SetName("Sheet1");

	return TRUE;

}
BOOL CMyExcel::OpenSheet(CString strSheet)
{
	LPDISPATCH  lpDisp=NULL;
	long len;
	len=MySheets.GetCount();
	for(long i=1;i<=len;i++)
	{
		lpDisp=MySheets.GetItem((_variant_t)(long)i);
		MySheet.AttachDispatch(lpDisp,TRUE);
		CString str=MySheet.GetName();
		if(MySheet.GetName()==strSheet)
		{
			lpDisp=MySheet.GetCells();
			MyRange.AttachDispatch(lpDisp,TRUE);
			//myfont.AttachDispatch(MyRange.GetFont());
			return TRUE;
		}
	}
	return FALSE;
}


BOOL CMyExcel::SetItemText(long Row,long Col,CString strText)
{
	/*
	long lRow=0,lCol=0;
	lRow=GetRowS();
	lCol=GetColS();
	if(Row>lRow ||Col>lCol)
	{
		CString strText;
		strText.Format("由于(%d,%d)已经超过了(%d,%d)的范围,所以在(%d,%d)设置失败!"
			,Row,Col,lRow,lCol,Row,Col);
		AfxMessageBox(_T(strText),MB_OK|MB_ICONERROR);
		return FALSE;
	}
	*/
	MyRange.SetItem(_variant_t(Row), _variant_t(Col), _variant_t(strText));
	return TRUE;
}

//设置当前单元格颜色
void CMyExcel::SetCurColor(int nrow, int ncolumn, COLORREF  color)
{

	
//	Range rgRgeValue;
//	rgRgeValue.AttachDispatch(MyRange.GetItem(_variant_t(long(nrow)), _variant_t(long(ncolumn))).pdispVal);
// 	myfont.AttachDispatch(rgRgeValue.GetFont());
// 	myfont.SetColor(COleVariant(long(0XFF0000)));

// 	Interior interclr;
// 	rgRgeValue.AttachDispatch(MySheet.GetRange(COleVariant(_T("A1")),COleVariant(_T("A1"))));
// 	_variant_t vtVal = rgRgeValue.GetValue();
// 	interclr.AttachDispatch(rgRgeValue.GetInterior());
//     interclr.SetColorIndex(_variant_t((long)255));
// 	interclr.ReleaseDispatch();
// 	rgRgeValue.ReleaseDispatch();
	


/*
	VARIANT lpDisp=MyRange.GetItem(_variant_t(long(nrow)), _variant_t(long(ncolumn)));
	Range rgRgeValue;
	rgRgeValue.AttachDispatch(lpDisp.pdispVal, TRUE);
	_variant_t vtVal = rgRgeValue.GetValue();


	excelFont ft;
	ft.AttachDispatch(rgRgeValue.GetFont());
	ft.SetColor(COleVariant(long(255)));

	ft.ReleaseDispatch();
	rgRgeValue.ReleaseDispatch();
*/


 	CString mstr = "";
	int rem = ncolumn%26;
	int nnn = ncolumn/26;
	char cc = 'A'+ rem - 1;
	char cd = 'A'+ nnn - 1;
	
	if (ncolumn < 26)
	{
		mstr.Format("%c%d", cc, nrow);	
	}else{
		mstr.Format("%c%c%d",  cd, cc, nrow);	
	}
	
	Range rgRgeValue;
	rgRgeValue.AttachDispatch(MyRange.GetRange(COleVariant(mstr), COleVariant(mstr)));
  	_variant_t vtVal = rgRgeValue.GetValue();
	Interior interclr;
	interclr.AttachDispatch(rgRgeValue.GetInterior());
	interclr.SetColor(_variant_t(long(color)));
	interclr.ReleaseDispatch();
	rgRgeValue.ReleaseDispatch();
	

}

CString CMyExcel::GetItemText(long Row,long Col)
{
	CString strValue=_T("");
	/*
	long lRow=0,lCol=0;
	lRow=GetRowS();
	lCol=GetColS();
	if(Row>lRow ||Col>lCol)
	{
		CString strText;
		strText.Format("由于(%d,%d)已经超过了(%d,%d)的范围,所以返回空值"
			,Row,Col,lRow,lCol,Row,Col);
		AfxMessageBox(_T(strText),MB_OK|MB_ICONERROR);
		return strValue;
	}
	*/
	VARIANT lpDisp=MyRange.GetItem(_variant_t(Row), _variant_t(Col));
	Range rgRgeValue;
	rgRgeValue.AttachDispatch(lpDisp.pdispVal, TRUE);
	_variant_t vtVal = rgRgeValue.GetValue();
	if (vtVal.vt == VT_EMPTY)
	{
		rgRgeValue.ReleaseDispatch();
		strValue = _T("");
		rgRgeValue.ReleaseDispatch();
		return strValue;
	}
	vtVal.ChangeType(VT_BSTR);
	strValue= vtVal.bstrVal;
	rgRgeValue.ReleaseDispatch();
	return strValue;
}
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);
	strFilePath=strPath;
//	AfxMessageBox(_T("Excel保存成功"),MB_OK|MB_ICONINFORMATION);
}
void CMyExcel::Save()
{
	MyBook.Save();
//	AfxMessageBox(_T("Excel保存成功"),MB_OK|MB_ICONINFORMATION);
}
void CMyExcel::AddSheet(CString strSheet)
{
	LPDISPATCH  lpDisp=NULL;
	lpDisp=MySheets.Add(vtMissing,vtMissing,vtMissing,vtMissing);
	MySheet.AttachDispatch(lpDisp,TRUE);
	MySheet.SetName(strSheet);
	lpDisp=MySheet.GetCells();
	MyRange.AttachDispatch(lpDisp,TRUE);
}
void CMyExcel::GetRange(CString strBegin,CString strEnd)
{
	MyRange=MySheet.GetRange(_variant_t(strBegin),_variant_t(strEnd));
}
void CMyExcel::AutoColFit()
{
	Range rg=MyRange.GetEntireColumn();
	rg.AutoFit();
	rg.ReleaseDispatch();
}
void CMyExcel::AutoRowFit()
{
	Range rg=MyRange.GetEntireRow();
	rg.AutoFit();	
	rg.ReleaseDispatch();
}
void CMyExcel::SetWrapText(BOOL blnTrue)
{
	MyRange.SetWrapText((_variant_t)(short)blnTrue);
}
void CMyExcel::SetVisible(BOOL blnVisible)
{
	if(blnVisible==TRUE)
		if(strFilePath!="")
		{
			if(IsFileExist(strFilePath,FALSE))
			{
				Exit();
				ShellExecute(NULL,"open",strFilePath,NULL,NULL,SW_SHOW);
			}
			else
			{
				CString strName;
				strName="路径"+strFilePath+"错误,不能打开显示!";
				AfxMessageBox(strFilePath,MB_OK|MB_ICONINFORMATION);
			}
		}
		else
		{
			AfxMessageBox("请先存文件后,方可打开!",MB_OK|MB_ICONINFORMATION);
		}
}
void CMyExcel::SetFont(MyFont font)
{
	excelFont 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::SetAlignment(MyAlignment XMyAlignment)
{
	MyRange.SetHorizontalAlignment((_variant_t)(short)XMyAlignment.HorizontalAlignment);
	MyRange.SetVerticalAlignment((_variant_t)(short)XMyAlignment.VerticalAlignment);
}
void CMyExcel::AutoRange()
{
	LPDISPATCH  lpDisp=NULL;
	lpDisp=MySheet.GetCells();
	MyRange.AttachDispatch(lpDisp,TRUE);
}
void CMyExcel::SetMergeCells(BOOL blnTrue)
{
	if(blnTrue==TRUE)
	{
		int i,j;
		long Row=GetRowS();
		long Col=GetColS();
		for(j=2;j<=Col;j++) SetItemText(1,j,"");
		for(i=2;i<=Row;i++)
			for(j=1;j<=Col;j++)
				SetItemText(i,j,"");
	}
	MyRange.SetMergeCells((_variant_t)(short)blnTrue);
}
void CMyExcel::SetBackStyle(MyBackStyle BackStyle)
{
	LPDISPATCH  lpDisp=NULL;
	Interior Itor;
	lpDisp=MyRange.GetInterior();
	Itor.AttachDispatch(lpDisp,TRUE);
	if(BackStyle.transparent==TRUE)
		Itor.SetColorIndex((_variant_t)(short)xlNone);
	else
	{
		Itor.SetColor((_variant_t)(long)BackStyle.Color);
		Itor.SetPattern((_variant_t)(short)BackStyle.Pattern);
		Itor.SetPatternColor((_variant_t)(long)BackStyle.PatternColor);
	}
	Itor.ReleaseDispatch();

}
void CMyExcel::SetBorderLine(short Xposition,MyBorder XBorder)
{
	long Row,Col;
	Row=GetRowS();
	Col=GetColS();
	if(Row==1)
		if(Xposition==xlInsideHorizontal) return;
	if(Col==1)
		if(Xposition==xlInsideVertical) return;
	LPDISPATCH  lpDisp=NULL;
	lpDisp=MyRange.GetBorders();   
	Borders   bds;   
	bds.AttachDispatch(lpDisp);   
	Border   bd;   
	lpDisp=bds.GetItem((long)Xposition);   
	bd.AttachDispatch(lpDisp);   
	bd.SetLineStyle((_variant_t)(short)XBorder.LineStyle);
	bd.SetColor((_variant_t)(long)XBorder.Color);
	bd.SetWeight((_variant_t)(short)XBorder.Weight);
	bd.ReleaseDispatch();
	bds.ReleaseDispatch();
}
long CMyExcel::GetRowS()
{
	long len=0;
	Range rg=MyRange.GetEntireRow();	
	len=rg.GetCount();
	rg.ReleaseDispatch();
	return len;
}
long CMyExcel::GetColS()
{
	long len=0;
	Range rg=MyRange.GetEntireColumn();
	len=rg.GetCount();
	rg.ReleaseDispatch();
	return len;
}

long CMyExcel::GetCurRow()
{
	Range rg = MySheet.GetUsedRange();
	rg = rg.GetRows();

	long nRow = rg.GetCount();
	return nRow;
}


long CMyExcel::GetCurCol()
{
	Range rg = MySheet.GetUsedRange();
	rg = rg.GetColumns();
	
	long nCol = rg.GetCount();
	return nCol;
}

void CMyExcel::SetNumberFormat(MyNumberFormat XNumberFormat)
{
	CString strText=XNumberFormat.strValue;
	MyRange.SetNumberFormat(_variant_t(strText));
}
void CMyExcel::SetColumnWidth(int intWidth)
{
	double f=intWidth/8.08;
	MyRange.SetColumnWidth((_variant_t)(double)f);
}
void CMyExcel::SetRowHeight(int intHeight)
{
	double f=intHeight/8.08;
	MyRange.SetRowHeight((_variant_t)(double)f);
}
void CMyExcel::InsertPicture(CString strFilePath)
{
	LPDISPATCH  lpDisp=NULL;
	if(IsFileExist(strFilePath,FALSE))
	{
	//	lpDisp=MySheet.get
	}
}
void CMyExcel::SetBackPicture(CString strFilePath)
{
	if(IsFileExist(strFilePath,FALSE)==TRUE)
		MySheet.SetBackgroundPicture(strFilePath);
}
void CMyExcel::PrintOut(short CopySum)
{
	if(CopySum<=0) CopySum=1;
	COleVariant covTrue((short)TRUE); 
	COleVariant covFalse((short)FALSE); 
	COleVariant	covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); 
	MySheet.PrintOut(vtMissing,vtMissing,(_variant_t)(short)CopySum,vtMissing
		,vtMissing,vtMissing,covTrue,vtMissing);
}
void CMyExcel::PrePrintOut(BOOL blnEnable)
{
	COleVariant covOptional((short)blnEnable);
	MySheet.PrintPreview(covOptional);
}
BOOL CMyExcel::IsFileExist(CString strFn, BOOL bDir)
{
    HANDLE h;
	LPWIN32_FIND_DATA pFD=new WIN32_FIND_DATA;
	BOOL bFound=FALSE;
	if(pFD)
	{
		h=FindFirstFile(strFn,pFD);
		bFound=(h!=INVALID_HANDLE_VALUE);
		if(bFound)
		{
			if(bDir)
				bFound= (pFD->dwFileAttributes&FILE_ATTRIBUTE_DIRECTORY)!=NULL;
			FindClose(h);
		}
		delete pFD;
	}
	return bFound;
}
void CMyExcel::Exit()
{
	COleVariant covFalse((short)FALSE);
	COleVariant	covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR); 
	MyRange.ReleaseDispatch();
	myfont.ReleaseDispatch();
	MySheet.ReleaseDispatch();
	MySheets.ReleaseDispatch();
	MyBook.Close(covFalse,_variant_t(strFilePath),covOptional);
	MyBook.ReleaseDispatch();
	MyBooks.Close();
	MyBooks.ReleaseDispatch();
	MyApp.Quit();
	MyApp.ReleaseDispatch();
	CoUninitialize();
}
MyFont::MyFont()
{
	//名字
    Name="Microsoft Sans Serif";
	//大小
	size=12;
	//前景
	ForeColor=RGB(0,0,0);
	//粗体
	Bold=FALSE;
	//斜体
	Italic=FALSE;
	//中间线
	Strikethrough=FALSE;
	//阴影
	Shadow=FALSE;
	//下标
	Subscript=FALSE;
	//上标
	Superscricp=FALSE;
	//下划线
	Underline=xlUnderlineStyleNone;	
}
MyBorder::MyBorder()
{
	//线条形状
	LineStyle=xlContinuous;
	//粗线
    Weight=xlThin;
	//颜色
    Color=RGB(0,0,0);
}
MyBackStyle::MyBackStyle()
{
	//背景颜色
	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::GetText()
{
	strValue="@";
	return strValue;
}
CString MyNumberFormat::GetGeneral()
{
	strValue="G/通用格式";
	return strValue;
}
CString MyNumberFormat::GetNumber(BOOL blnBox,int RightSum)
{
	CString str="0";
	int i;
	if(RightSum<0) RightSum=0;
	if(blnBox==TRUE)
	{
		if(RightSum==0)
		{
			str="#,##0_ ";
			strValue=str;
			return strValue;
		}
		else
		{
			str="#,##0.";
			for(i=0;i<RightSum;i++) str=str+"0";
			str=str+"_ ";
			strValue=str;
			return strValue;	
		}
	}
	else
	{
		if(RightSum==0)
		{
			str="0_ ";
			strValue=str;
			return strValue;
		}
		else
		{
			str="0.";
			for(i=0;i<RightSum;i++) str=str+"0";
			str=str+"_ ";
			strValue=str;
			return strValue;	
		}
	}
}
CString MyNumberFormat::GetDate(BOOL blnChinese)
{
	if(blnChinese==TRUE)
		strValue="yyyy\"年\"m\"月\"d\"日\";@";
	else
		strValue="yyyy-m-d;@";
	return strValue;
}
CString MyNumberFormat::GetDateTime(BOOL blnChinese)
{
	if(blnChinese==TRUE)
		strValue="yyyy\"年\"m\"月\"d\"日\" h\"时\"mm\"分\"ss\"秒\";@";
	else
		strValue="yyyy-m-d h:mm:ss;@";
	return strValue;
}
CString MyNumberFormat::GetDBNumber(BOOL blnChinese)
{
	if(blnChinese==TRUE)
		strValue="[DBNum1][$-804]G/通用格式";
	else
		strValue="[DBNum2][$-804]G/通用格式";
	return strValue;
}
CString MyNumberFormat::GetFractionNumBer(int DownSum,int DownNum)
{	
	CString str;
	int i;
	if(DownNum>0 && DownSum>0)
	{
		AfxMessageBox("分母位数和固定数只能设置一个,默认返回分母位数的设置!"
			,MB_ICONINFORMATION|MB_OK);
	}
	if(DownSum<=0)
	{
		if(DownNum<=0)
		{
			str="# ?/1";
			strValue=str;
			return strValue;
		}
		else
		{
			str.Format("# ?/%d",DownNum);
			strValue=str;
			return strValue;
		}
	}
	else
	{
		str="# ?/";
		for(i=0;i<DownSum;i++) str=str+"?";
		strValue=str;
		return strValue;
	}
}
CString MyNumberFormat::GetMoney(BOOL blnChinese,int RightSum)
{
	CString str;
	int i;
	if(RightSum<=0) RightSum=0;
	if(blnChinese==TRUE)
	{
		if(RightSum==0)
		{
			str="#,##0";
			strValue="¥"+str+";"+"¥-"+str;
			return strValue;
		}
		else
		{
			str="#,##0.";
			for(i=0;i<RightSum;i++) str=str+"0";
			strValue="¥"+str+";"+"¥-"+str;
			return strValue;
		}

	}
	else
	{
		if(RightSum==0)
		{
			str="#,##0";
			strValue="$"+str+";"+"$-"+str;
			return strValue;
		}
		else
		{
			str="#,##0.";
			for(i=0;i<RightSum;i++) str=str+"0";
			strValue="$"+str+";"+"$-"+str;
			return strValue;
		}
	}
}
CString MyNumberFormat::GetPercentNumBer(int RightSum)
{
	CString str;
	int i;
	if(RightSum<=0) RightSum=0;
	if(RightSum==0)
	{
		str="0%";
		strValue=str;
		return strValue;
	}
	else
	{
		str="0.";
		for(i=0;i<RightSum;i++) str=str+"0";
		strValue=str+"%";
		return strValue;
	}
}
CString MyNumberFormat::GetTechNumBer(int RightSum)
{
	CString str;
	int i;
	if(RightSum<=0) RightSum=0;
	str="0.";
	for(i=0;i<RightSum;i++) str=str+"0";
	strValue=str+"E+00";
	return strValue;
}
CString MyNumberFormat::GetTime(BOOL blnChinese)
{
	if(blnChinese==TRUE)
	{
		strValue="h\"时\"mm\"分\"ss\"秒\";@";
		return strValue;
	}
	else
	{
		strValue="h:mm:ss;@";
		return strValue;
	}
}
CString MyNumberFormat::GetPost(int Sum)
{
	int i;
	CString str="";
	if(Sum<=0) Sum=1;
	for(i=0;i<Sum;i++)
		str=str+"0";
	strValue=str;
	return strValue;
}
CDSY,CDSY.XYZ
方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门
本栏推荐