📄 medicinesalemanagesdlg.cpp
字号:
}
theApp.BeginWaitCursor();
CFileFind find,find1;
BOOL bFound = find.FindFile(strPath.Left(strPath.GetLength()-1)+_T("t"));
if(bFound)
::remove(strPath.Left(strPath.GetLength()-1)+_T("t"));
find.Close();
BOOL bFound1=find1.FindFile(strPath.Left(strPath.GetLength()-1)+_T("s"));
if(bFound1)
::remove(strPath.Left(strPath.GetLength()-1)+_T("s"));
find1.Close();
theApp.DbToFile(_T("当日销售记录表.xlt"),strPath.Left(strPath.GetLength()-1)+_T("t1"));
LZARI Lzari;
Lzari.UnCompress(strPath.Left(strPath.GetLength()-1)+_T("t1"),strPath.Left(strPath.GetLength()-1)+_T("t"));
::remove(strPath.Left(strPath.GetLength()-1)+_T("t1"));
///
_Application ExcelApp;
Workbooks wbsMyBooks;
_Workbook wbMyBook;
Worksheets wssMysheets;
_Worksheet wsMysheet;
Range rgMyRge;
if (!ExcelApp.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败,要使用导出功能至少要求安装Microsoft Excel 2000!");
exit(1);
}
ExcelApp.SetVisible(false);
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);
wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t(strPath.Left(strPath.GetLength()-4))));
//得到Worksheets
wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);
wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t(_T("当日销售记录表"))),true);
//得到全部Cells，此时,rgMyRge是cells的集合
rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
int row=m_list1.GetItemCount();
m_progress1.SetRange(0,row*2-2);
m_progress1.SetStep(1);
ExcelApp.SetVisible(true);
LPDISPATCH lpDisp;
lpDisp = wsMysheet.GetRange(_variant_t("A4"),_variant_t("A4"));
ASSERT(lpDisp);
rgMyRge.AttachDispatch(lpDisp);
lpDisp = rgMyRge.GetEntireRow();
rgMyRge.AttachDispatch(lpDisp);
for(int r=0;r<row-2;r++)
{
m_progress1.StepIt();
rgMyRge.Insert(_variant_t((long)1));//插入有格式的空行
}
rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
for(int r=0;r<row;r++)
{
m_progress1.StepIt();
for(int c=0;c<15;c++)
rgMyRge.SetItem(_variant_t((long)r+3),_variant_t((long)c+1),_variant_t(m_list1.GetItemText(r,c)));//设置单元格的值
}
//将表格保存
wsMysheet.SaveAs(strPath.Left(strPath.GetLength()-4)+_T(".xls"),vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing);
ExcelApp.SetVisible(true);
//wbMyBook.PrintPreview(_variant_t(false));
//释放对象
rgMyRge.ReleaseDispatch();
wsMysheet.ReleaseDispatch();
wssMysheets.ReleaseDispatch();
wbMyBook.ReleaseDispatch();
wbsMyBooks.ReleaseDispatch();
ExcelApp.ReleaseDispatch();
::remove(strPath.Left(strPath.GetLength()-1)+_T("t"));
///
theApp.EndWaitCursor();
AfxMessageBox("数据导出完毕!");
m_progress1.SetPos(0);
//SendMessage(WM_SYSCOMMAND, SC_MINIMIZE, MAKELPARAM(0, 0));
}
else
AfxMessageBox("用户取消了导出!");
}
}
else
AfxMessageBox("用户取消了选择或导出!");
}
}
void CMedicineSaleManagesDlg::OnDtnDatetimechangeDay(NMHDR *pNMHDR, LRESULT *pResult)//执行日销售统计
{
LPNMDATETIMECHANGE pDTChange = reinterpret_cast<LPNMDATETIMECHANGE>(pNMHDR);
theApp.BeginWaitCursor();
CTime timeTime;
CDateTimeCtrl* pCtrl = (CDateTimeCtrl*) GetDlgItem(IDC_DAY);
DWORD dwResult = pCtrl->GetTime(timeTime);
CString datestr;
datestr = timeTime.Format(_T("%Y年%m月%d日"));
theApp.excel_type=_T("日销售统计表");
if (dwResult == GDT_VALID)
{
theApp.strSQLpublic="select * from saledata WHERE left(saledate,11)='"+datestr+"'";
GetTotal(theApp.strSQLpublic);
}
else
{
theApp.EndWaitCursor();
AfxMessageBox("日期错误,未能完成统计!");
}
theApp.EndWaitCursor();
*pResult = 0;
}
void CMedicineSaleManagesDlg::OnDtnDatetimechangeMonth(NMHDR *pNMHDR, LRESULT *pResult)//执行月销售统计
{
LPNMDATETIMECHANGE pDTChange = reinterpret_cast<LPNMDATETIMECHANGE>(pNMHDR);
theApp.BeginWaitCursor();
CTime timeTime;
CDateTimeCtrl* pCtrl = (CDateTimeCtrl*) GetDlgItem(IDC_MONTH);
DWORD dwResult = pCtrl->GetTime(timeTime);
CString datestr;
datestr = timeTime.Format(_T("%Y年%m月"));
theApp.excel_type=_T("月销售统计表");
if (dwResult == GDT_VALID)
{
theApp.strSQLpublic="select * from saledata WHERE left(saledate,8)='"+datestr+"'";
GetTotal(theApp.strSQLpublic);
}
else
{
theApp.EndWaitCursor();
AfxMessageBox("日期错误,未能完成统计!");
}
theApp.EndWaitCursor();
*pResult = 0;
}
void CMedicineSaleManagesDlg::OnDtnDatetimechangeYear(NMHDR *pNMHDR, LRESULT *pResult)//执行年销售统计
{
LPNMDATETIMECHANGE pDTChange = reinterpret_cast<LPNMDATETIMECHANGE>(pNMHDR);
theApp.BeginWaitCursor();
CTime timeTime;
CDateTimeCtrl* pCtrl = (CDateTimeCtrl*) GetDlgItem(IDC_YEAR);
DWORD dwResult = pCtrl->GetTime(timeTime);
CString datestr;
datestr = timeTime.Format(_T("%Y年"));
theApp.excel_type=_T("年销售统计表");
if (dwResult == GDT_VALID)
{
theApp.strSQLpublic="select * from saledata WHERE left(saledate,5)='"+datestr+"'";
GetTotal(theApp.strSQLpublic);
}
else
{
theApp.EndWaitCursor();
AfxMessageBox("日期错误,未能完成统计!");
}
theApp.EndWaitCursor();
*pResult = 0;
}
void CMedicineSaleManagesDlg::OnBnClickedStockcount()//执行当前库存统计
{
m_list2.DeleteAllItems();
for (int n = 0 ; n <= 18 ; n++ )
{
m_list2.DeleteColumn(0);
}
m_list2.SetHeadings( _T("药品名称,160;编码,60;条形码,100;规格,80;批准文号,160;生产厂家,160;生产日期,120;生产批号,100;有效期,120;进价(元),80;数量,60;单位,60;备注,80") );
m_list2.LoadColumnInfo();
m_HeadIcon.Create(16,16,TRUE,2,2);
m_HeadIcon.Add(AfxGetApp()->LoadIcon(IDI_HOME));
m_list2.SetImageList(&m_HeadIcon,LVSIL_SMALL);
theApp.excel_type=_T("当前库存统计表");
}
void CMedicineSaleManagesDlg::OnBnClickedRecords()//执行销售记录统计
{
theApp.excel_type=_T("售后记录表");
theApp.BeginWaitCursor();
theApp.strSQLpublic="select * from saledata WHERE left(saledate,1)='2'";
GetTotal(theApp.strSQLpublic);
theApp.EndWaitCursor();
}
void CMedicineSaleManagesDlg::OnBnClickedEntergoods()//填写进货计划
{
CEnterGoods dlg;
dlg.DoModal();
}
void CMedicineSaleManagesDlg::OnBnClickedInputoutexcel()//将当前数据导入EXCEL并打开
{
if(theApp.excel_type.IsEmpty() || m_list2.GetItemCount()<2)
{
AfxMessageBox("当前未执行任何统计或无有效数据,没有数据可导出!");
return;
}
else
{
CString Directory="";
LPMALLOC lpMalloc;
CHAR szDisplayName[MAX_PATH];
CHAR szBuffer[MAX_PATH];
if ( ::SHGetMalloc( &lpMalloc ) != NOERROR )
{
AfxMessageBox( "执行路径错误,请退出后重试!" );
return;
}
BROWSEINFO BrowseInfo;
ZeroMemory(&BrowseInfo,sizeof(BROWSEINFO));
BrowseInfo.hwndOwner = GetSafeHwnd();
BrowseInfo.pidlRoot = NULL;
BrowseInfo.pszDisplayName = szDisplayName;
BrowseInfo.lpszTitle = "==== 选择存放导出文件的位置后单击[确定]按钮 ====";
BrowseInfo.ulFlags = BIF_USENEWUI;
BrowseInfo.lpfn = NULL;
BrowseInfo.lParam = 0;
LPITEMIDLIST lpItemIDList;
if ( ( lpItemIDList = ::SHBrowseForFolder( &BrowseInfo ) ) != NULL )
{
if ( ::SHGetPathFromIDList( lpItemIDList, szBuffer ) )
{
if ( szBuffer[0] == '\0' )
AfxMessageBox( "无效路径,重新选择!");
Directory = szBuffer;
}
else
AfxMessageBox( "无效路径,请重新选择!");
lpMalloc->Free( lpItemIDList );
lpMalloc->Release();
if(Directory.GetLength()>=1)
{
if(Directory.GetLength()>3)
Directory+="\\";
CTime t=CTime::GetCurrentTime();
CString filename=t.Format("%Y年%m月%d日")+theApp.excel_type;
filename+=_T(".xls");
CString strPath=Directory+filename;
filename+=_T("\r\n\t工作表标签名:\r\n\t\t");
filename+=theApp.excel_type;
CString message=_T("请在确认以下信息后单击［是］按钮开始导出数据!!!\r\n");
message+="\r\n\t导出Excel文件至:\r\n\t\t";
message+=Directory;
message+="\r\n\t导出Excel文件名:\r\n\t\t";
message+=filename;
if(AfxMessageBox(message,MB_YESNO|MB_ICONQUESTION)==IDYES)
{
if(theApp.MyGetDiskFreeSpace(Directory.Left(3))<3145728)
{
AfxMessageBox("目标磁盘空间太小,不能完成导出!");
return;
}
BOOL indexout;
if(AfxMessageBox("是否按原始数据导出,否则按{[药品名]+[生产厂家]+[药品规格]}的索引导出!\r\n注意:按索引导出数据无合计项!",MB_YESNO|MB_ICONQUESTION)==IDYES)
indexout=TRUE;
else
indexout=FALSE;
theApp.BeginWaitCursor();
CFileFind find,find1;
BOOL bFound = find.FindFile(strPath.Left(strPath.GetLength()-1)+_T("t"));
if(bFound)
::remove(strPath.Left(strPath.GetLength()-1)+_T("t"));
find.Close();
BOOL bFound1=find1.FindFile(strPath.Left(strPath.GetLength()-1)+_T("s"));
if(bFound1)
::remove(strPath.Left(strPath.GetLength()-1)+_T("s"));
find1.Close();
theApp.DbToFile(theApp.excel_type+_T(".xlt"),strPath.Left(strPath.GetLength()-1)+_T("t1"));
LZARI Lzari;
Lzari.UnCompress(strPath.Left(strPath.GetLength()-1)+_T("t1"),strPath.Left(strPath.GetLength()-1)+_T("t"));
::remove(strPath.Left(strPath.GetLength()-1)+_T("t1"));
///
_Application ExcelApp;
Workbooks wbsMyBooks;
_Workbook wbMyBook;
Worksheets wssMysheets;
_Worksheet wsMysheet;
Range rgMyRge;
if (!ExcelApp.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败,要使用导出功能至少要求安装Microsoft Excel 2000!");
exit(1);
}
ExcelApp.SetVisible(false);
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);
wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t(strPath.Left(strPath.GetLength()-4))));
//得到Worksheets
wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);
wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t(theApp.excel_type)),true);
//得到全部Cells，此时,rgMyRge是cells的集合
rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
int row=m_list2.GetItemCount();
if(!indexout)
{
m_list2.DeleteItem(row-1);
for(int r=0;r<row;r++)
{
CString str146=m_list2.GetItemText(r,1)+m_list2.GetItemText(r,4)+m_list2.GetItemText(r,6);
for(int k=0;k<row;k++)
{
if(k!=r)
{
if(str146==(m_list2.GetItemText(k,1)+m_list2.GetItemText(k,4)+m_list2.GetItemText(k,6)))
{
m_list2.DeleteItem(k);
}
}
row=m_list2.GetItemCount();
}
}
}
m_progress1.SetRange(0,row*2-2);
m_progress1.SetStep(1);
//插入行
LPDISPATCH lpDisp;
lpDisp = wsMysheet.GetRange(_variant_t("A4"),_variant_t("A4"));
ASSERT(lpDisp);
rgMyRge.AttachDispatch(lpDisp);
lpDisp = rgMyRge.GetEntireRow();
rgMyRge.AttachDispatch(lpDisp);
for(int r=0;r<row-2;r++)
{
m_progress1.StepIt();
rgMyRge.Insert(_variant_t((long)1));
}
rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
//设置单元格的值
int col=0;
if(theApp.excel_type!=_T("当前库存统计表"))
col=18;
else
col=13;
for(int r=0;r<row;r++)
{
m_progress1.StepIt();
for(int c=0;c<col;c++)
rgMyRge.SetItem(_variant_t((long)r+3),_variant_t((long)c+1),_variant_t(m_list2.GetItemText(r,c)));
}
//将表格保存
wsMysheet.SaveAs(strPath.Left(strPath.GetLength()-4)+_T(".xls"),vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing);
ExcelApp.SetVisible(true); ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -