// 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_FILE
static 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;
}
商店管理系统源代码(五)
最新推荐文章于 2022-07-15 14:42:37 发布