商店管理系统源代码(五)

// MyExcel.h: interface for the CMyExcel class.////////////////////////////////////////////////////////////////////////#if !defined(AFX_MYEXCEL_H__5E68A78D_06D3_43B7_BC44_B557B4C15F4C__INCLUDED_)#define AFX_MYEXCEL_H__5E68A78D_06D3_43B7_BC44_B557B4C15F4C__INCLUDED_#if _MSC_VER > 1000#pragma once#endif // _MSC_VER > 1000#include "excel.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//字体垂直对齐方式#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 MyBackGroundStyle{public://背景颜色long Color;//背景图案short Pattern;//背景图案颜色long PatternColor;//透明与否BOOL transparent;//构造函数MyBackGroundStyle();};class MyFont{public:MyFont();//名字CString Name;long size;long ForeColor;BOOL Bold;//斜体BOOL Italic;//中间线BOOL StrikeThrough;BOOL Shadow;//下标BOOL Subscript;//上标BOOL Superscript;short UnderLine;};class MyAlignment{public:short HorizontalAlignment;short VerticalAlignment;MyAlignment();};class MyNumberFormat{public:MyNumberFormat();//设置参数CString strValue;CString GetTextSetup();CString GetNumberSetup(BOOL bSeparator,int iNumDecimal);//bInChinese:TRUE-"￥",FALSE-"$"CString GetMoneySetup(BOOL bInChinese,int iNumDecimal);CString GetDateSetup(BOOL bInChinese);CString GetTimeSetup(BOOL bInChinese);CString GetGeneralSetup();CString GetSpecialNumberSetup(BOOL bInChinese);CString GetPercentNumberSetup(int iNumDecimal);CString GetFractionNumberSetup(int DownSum,int DownNum);CString GetScientificNumberSetup(int iNumDecimal);CString GetPostalcodeSetup(int Num);CString GetDateTimeSetup(BOOL bInChinese);};class CMyExcel  {public:CString GetAllWorkSheetName();_Application MyApp;_Workbook MyBook;_Worksheet MySheet;Workbooks MyBooks;Worksheets MySheets;Range MyRange;CString strFilePath;CMyExcel();virtual ~CMyExcel();BOOL Open();BOOL Open(CString strFile);BOOL OpenSheet(CString strSheet);BOOL SetItemText(long Row,long Col,CString strText);CString GetItemText(long Row,long Col);void Exit();void SetVisible(BOOL bVisible);BOOL IsFileExist(CString strFn,BOOL bDir);void Save();void SaveAs(CString strPath);void AddSheet(CString strSheet);void DeleteSheet123();void GetRange(CString strBegin,CString strEnd);void AddComment(long Row,long Col,CString strText);void AutoColFit();void AutoRowFit();void SetWrapText(BOOL bTrue);void SetFont(MyFont font);void GetAllRange();void SetMergeCells(BOOL bTrue);void SetBackGroundStyle(MyBackGroundStyle BackStyle);void SetBorderLine(short position,MyBorder Border);void SetAlignment(MyAlignment XMyAlignment);long GetRows();long GetCols();void SetNumberFormat(MyNumberFormat XNumberFormat);void SetColumnWidth(int iWidth);void SetRowHeight(int iHeight);void PrintOut(short numCopy);void PrePrintOut(BOOL bEnable);void InsertPicture(CString strFilePath);void SetBackPicture(CString strFilePath);long Find(CString str);void SetSheetVisible(BOOL bVisible);void AutoFilter();void DeleteRow(long line);};#endif // !defined(AFX_MYEXCEL_H__5E68A78D_06D3_43B7_BC44_B557B4C15F4C__INCLUDED_)// MyExcel.cpp: implementation of the CMyExcel class.////////////////////////////////////////////////////////////////////////#include "stdafx.h"#include "MyExcel.h"#ifdef _DEBUG#undef THIS_FILEstatic char THIS_FILE[]=__FILE__;#define new DEBUG_NEW#endif//////////////////////////////////////////////////////////////////////// Construction/Destruction//////////////////////////////////////////////////////////////////////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;}MyBooks.AttachDispatch(MyApp.GetWorkbooks(),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;}MyBooks.AttachDispatch(MyApp.GetWorkbooks(),TRUE);lpDisp = MyBooks.Open(strFile,vtMissing,vtMissing,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 length;length = MySheets.GetCount();for(long i = 1;i <= length;i++){lpDisp = MySheets.GetItem(_variant_t((long)i));MySheet.AttachDispatch(lpDisp,TRUE);if(MySheet.GetName() == strSheet){lpDisp = MySheet.GetCells();MyRange.AttachDispatch(lpDisp,TRUE);return TRUE;}}return FALSE;}long CMyExcel::GetRows(){long lRowsNum;//Range range = MyRange.GetEntireRow();Range range,usedRange;usedRange.AttachDispatch(MySheet.GetUsedRange());range.AttachDispatch(usedRange.GetRows());lRowsNum = range.GetCount();//lRowsNum = range.GetCount();range.ReleaseDispatch();usedRange.ReleaseDispatch();return lRowsNum;}long CMyExcel::GetCols(){long lColNum;Range range,usedRange;usedRange.AttachDispatch(MySheet.GetUsedRange());range.AttachDispatch(usedRange.GetColumns());lColNum = range.GetCount();range.ReleaseDispatch();usedRange.ReleaseDispatch();return lColNum;}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);AfxMessageBox(_T(strText),MB_OK | MB_ICONERROR);return strValue;}*/Range range;//range.AttachDispatch(MyRange.GetItem(_variant_t(Row),_variant_t(Col)).pdispVal);range.AttachDispatch(MyRange.GetItem(COleVariant((long)Row),COleVariant((long)Col)).pdispVal);//_variant_t vtVal = range.GetValue2();COleVariant vtVal = range.GetText();//COleVariant vtVal = range.GetValue2();/*if(vtVal.vt == VT_BSTR){vtVal.ChangeType(VT_BSTR);strValue = vtVal.bstrVal;}*/if(vtVal.vt == VT_EMPTY){range.ReleaseDispatch();return strValue;}vtVal.ChangeType(VT_BSTR);strValue = vtVal.bstrVal;range.ReleaseDispatch();return strValue;}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)的范围，所以返回空值",Row,Col,lRow,lCol);AfxMessageBox(_T(strText),MB_OK | MB_ICONERROR);return FALSE;}*/MyRange.SetItem(_variant_t(Row),_variant_t(Col),_variant_t(strText));return TRUE;}void CMyExcel::AutoFilter(){Range range;range = MySheet.GetRange(_variant_t("A1"),_variant_t("G1"));//range.Select();range.AutoFit();MySheet.SetEnableAutoFilter(TRUE);//range.AutoFilter(_variant_t((long)1),_variant_t())}void CMyExcel::Save(){MyBook.Save();}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;}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);}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::AddComment(long Row,long Col,CString strText){Range range;range.AttachDispatch(MyRange.GetItem(_variant_t((long)Row),_variant_t((long)Col)).pdispVal,TRUE);range.AddComment(_variant_t(strText));range.ReleaseDispatch();}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));}void CMyExcel::SetVisible(BOOL bVisible){/*if(bVisible == TRUE){if(strFilePath != ""){if(IsFileExist(strFilePath,false)){ShellExecute(NULL,"open",strFilePath,NULL,NULL,SW_SHOW);}else{CString strName;strName = "路径:" + strFilePath + "错误，不能打开显示!";AfxMessageBox(strName,MB_OK | MB_ICONERROR);}}else{AfxMessageBox("请先保存文件后,方可打开!",MB_OK | MB_ICONINFORMATION);}}*/MyApp.SetVisible(bVisible);}void CMyExcel::DeleteRow(long line){Range range;CString strCell;strCell.Format(_T("A%d"),line);range.AttachDispatch(MySheet.GetRange(COleVariant(strCell),COleVariant(strCell)));range.AttachDispatch(range.GetEntireRow());range.Delete(COleVariant((long)xlUp));range.ReleaseDispatch();}void CMyExcel::SetNumberFormat(MyNumberFormat XNumberFormat){MyRange.SetNumberFormat(_variant_t(XNumberFormat.strValue));}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);}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));}void CMyExcel::SetSheetVisible(BOOL bVisible){if(bVisible == TRUE){MySheet.Activate();}}CString CMyExcel::GetAllWorkSheetName(){LPDISPATCH lpDisp = NULL;long length;CString strName;length = MySheets.GetCount();for(long i = 1;i <= length;i++){lpDisp = MySheets.GetItem(_variant_t((long)i));MySheet.AttachDispatch(lpDisp,TRUE);strName = strName + MySheet.GetName() + "\t";}return strName;}