MyExcel.cpp
上传用户:jzscgs158
上传日期:2022-05-25
资源大小:8709k
文件大小:14k
- #include "stdafx.h"
- #include "MyExcel.h"
- CMyExcel::CMyExcel()
- {
- strFilePath=_T("");
- }
- CMyExcel::~CMyExcel()
- {
- 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();
- }
- 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)
- {
- LPDISPATCH lpDisp=NULL;
- 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.Open(strFile, vtMissing, vtMissing, vtMissing,
- vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing,vtMissing);
- MyBook.AttachDispatch(lpDisp,TRUE);
- lpDisp=MyBook.GetWorksheets();
- MySheets.AttachDispatch(lpDisp,TRUE);
- strFilePath=strFile;
- 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);
- 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;
- }
- 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)
- {
- 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::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;
- }
- 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();
- 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;
- }