//将已经按照姓名汇总的数据分别输出到excel文件中
void CGhQuery::OnBnClickedClassifyExcel()
{
// 添加文件保存目录
CString str,strPath;
// ZeroMemory(szPath, sizeof(szPath));
BROWSEINFO bi;
bi.hwndOwner = m_hWnd;
bi.pidlRoot = NULL;
bi.pszDisplayName = str.GetBuffer(MAX_PATH);
bi.lpszTitle = "请选择汇总信息存放目录：";
bi.ulFlags = 0;
bi.lpfn = NULL;
bi.lParam = 0;
bi.iImage = 0;
//弹出选择目录对话框
LPITEMIDLIST lp = SHBrowseForFolder(&bi);
str.ReleaseBuffer();
SHGetPathFromIDList(lp,strPath.GetBuffer(MAX_PATH)) ;
strPath.ReleaseBuffer();
if(strPath.GetLength() <3){
AfxMessageBox("选择的目录无效,请重新选择可读写的目录....");
return;
}
UpdateData();
CFormGhList *pf=(CFormGhList*)theApp.pParentGrid.pGrid;
int iRows=pf->m_grid.GetRowCount();
int iCols=pf->m_grid.GetColumnCount();
if( iRows < 3) {
AfxMessageBox("没有数据需要进行分类汇总输出,请先进行数据分类汇总....");
return;
}
int iType=m_Type.GetCurSel();
CString strJournalType;
if( iType == CB_ERR || iType == 1 )strJournalType="报刊合计";
else m_Type.GetLBText(iType,strJournalType);
CString strPath2;
strPath2.Format("(%s)%s-%s",strJournalType,m_strStart,m_strEnd);
if( strPath.Right(1) == '\\' ) //路径选择为根目录时,目录后会自动附加'\\'字符,如果是正常的目录,则没有附加的'\\',所以要进行判断
strPath=strPath+strPath2;
else
strPath=strPath+"\\"+strPath2;
//判断目录是否存在
WIN32_FIND_DATA fd;
HANDLE hFind = FindFirstFile(strPath, &fd);
// BOOL bFilter = (FALSE == dwFilter) ? TRUE : fd.dwFileAttributes & dwFilter;
// BOOL RetValue = ((hFind != INVALID_HANDLE_VALUE) && bFilter) ? TRUE : FALSE;
FindClose(hFind);
if( fd.dwFileAttributes & FILE_ATTRIBUTE_DIRECTORY )
{
AfxMessageBox("当前目前下已存在要导出的文件目录,如需导出,请重新选择目录...");
return;
}
if( ! CreateDirectory(strPath,NULL) ){
AfxMessageBox("Create directory is error !!");
return;
}
strPath+="\\";
const int COL_FACTORY=1,COL_NAME=2,COL_PRICE=3,COL_SQ=0;
_Application app2;
Workbooks books2;
_Workbook book2;
Worksheets sheets2;
_Worksheet sheet2;
COleVariant vOpt(DISP_E_PARAMNOTFOUND,VT_ERROR);
const int IDC_PROGRESS_EXCEL=WM_USER+10003;
CProgressEx pp;
CRect r1(0,0,400,25);
pp.Create(WS_CHILD |PBS_SMOOTH|WS_BORDER ,r1,AfxGetMainWnd()->GetForegroundWindow(),IDC_PROGRESS_EXCEL);
pp.UpdateWindow();
pp.ShowWindow(SW_SHOW);
pp.CenterWindow();
pp.Invalidate();
pp.SetRange32(0,iRows );
pp.strView="正在准备导出...请稍等!!";
app2.CreateDispatch("excel.application");
if(!app2){
AfxMessageBox("请注意系统中必须装有EXCEL97 或者 EXCEL2000 ,否则此功能不能使用...");
return;
}
CString strFileName;
strFileName.Format("%sspec.xls",LoginInformation.path);
books2=app2.GetWorkbooks();
Range vRange;
SQ_EXCEL9::Font font;
long x,y;
CString str1,str2;
app2.SetVisible(false);
// pp.strView="";
books2=app2.GetWorkbooks(); //OPEN excel template
book2=books2.Open(strFileName,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt);
sheets2=book2.GetWorksheets();
sheet2=sheets2.GetItem(COleVariant((short)1));
CString szFlgFactory,szTmp;
CString szOutFile,szCell;
int xlsRow(3),clrRow(0); //clrRow记录文件最后一行行号
for(int i=1; i< iRows ;i++)
{
szFlgFactory=pf->m_grid.GetItemText(i,COL_FACTORY);
if( xlsRow ==3 ){
szCell="a1";
vRange=sheet2.GetRange(_variant_t(szCell),_variant_t(szCell) );
vRange.SetValue(_variant_t(szFlgFactory));
szOutFile.Format("%s%s(%s-%s).xls",strPath,szFlgFactory,m_strStart,m_strEnd);
if( clrRow ){
szCell.Format("A3:%c%d",'A'+COL_PRICE,clrRow+1);
vRange=sheet2.GetRange(_variant_t(szCell),_variant_t(szCell) );
szCell="";
vRange.SetValue(_variant_t(szCell));
clrRow=0;
}
}
if( i !=1 && xlsRow!= 3 ){
szTmp=pf->m_grid.GetItemText(i-1,COL_FACTORY);
if( szTmp.Compare(szFlgFactory) !=0 ){
szCell.Format("%c%d",'A'+COL_PRICE,xlsRow);
vRange=sheet2.GetRange(_variant_t(szCell),_variant_t(szCell) );
szCell.Format("=sum(D3:D%d)",xlsRow-1);
vRange.SetValue(_variant_t(szCell));
book2.SaveAs(_variant_t(szOutFile),vOpt,vOpt,vOpt,vOpt,vOpt,0L,vOpt,vOpt,vOpt,vOpt);
xlsRow=3;
i--;
continue;
}
}
for(int iCol=0; iCol< iCols ; iCol++){ //输出到excel表格
szCell.Format("%c%d",'A'+iCol,xlsRow);
vRange=sheet2.GetRange(_variant_t(szCell),_variant_t(szCell) );
if( iCol == 0 ) szCell.Format("%d",xlsRow-2); //xlsRow 起始行为3，第一列为序号列，
else
szCell=pf->m_grid.GetItemText(i,iCol);
vRange.SetValue(_variant_t(szCell));
}
xlsRow++;
clrRow=xlsRow;
pp.strView.Format("%2.0f%%",double(i)/iRows*100);
pp.SetPos(i);
}
pp.SendMessage(WM_CLOSE);
sheet2.ReleaseDispatch();
sheets2.ReleaseDispatch();
book2.SetSaved(true); //设置已存储标志，在quit是不提示更改保存
book2.Close(_variant_t(false ),vOpt,vOpt);
books2.Close();
book2.ReleaseDispatch();
books2.ReleaseDispatch();
app2.Quit();
app2.ReleaseDispatch();
}
void ToExcel(CMSHFlexGrid * pg)
{
_Application app2;
Workbooks books2;
_Workbook book2;
Worksheets sheets2;
_Worksheet sheet2;
COleVariant vOpt2(DISP_E_PARAMNOTFOUND,VT_ERROR);
CProgressEx pp;
CRect r1(0,0,400,25);
pp.Create(WS_CHILD |PBS_SMOOTH|WS_BORDER ,r1,AfxGetMainWnd()->GetForegroundWindow(),IDC_PROGRESS_EXCEL);
pp.UpdateWindow();
pp.ShowWindow(SW_SHOW);
pp.CenterWindow();
pp.Invalidate();
pp.SetRange32(0,pg->GetRows() );
pp.strView="正在准备导出...请稍等!!";
// if( ! app2 ){
app2.CreateDispatch("excel.application");
if(!app2){
AfxMessageBox("请注意系统中必须装有EXCEL97 或者 EXCEL2000 ,否则此功能不能使用...");
return;
}
books2=app2.GetWorkbooks();
// book2=books2.Open(strFileName,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt,vOpt);
book2=books2.Add(vOpt2);
sheets2=book2.GetWorksheets();
sheet2=sheets2.GetItem(COleVariant((short)1));
// }
//
Range vRange;
Font font;
long x,y;
CString str1,str2;
app2.SetVisible(false);
pp.strView="";
for (x=0; x<pg->GetRows(); x++)
{
for( y=0 ; y< pg->GetCols(0) ; y++ )
{
if( y<26)str1.Format("%c%ld",'A'+y , x+1 );
else str1.Format("%c%c%ld",'A'+ ( y/26 -1) ,'A'+y % 26,x+1 );
vRange=sheet2.GetRange(_variant_t(str1),_variant_t(str1)); // 医疗机构
// font=vRange.GetFont();
// font.SetName(_O(FONTNAME));
// font.SetSize(_O((short)STANDARDFONT));
vRange.SetValue(COleVariant(pg->GetTextMatrix(x,y)));
}
pp.strView.Format("%2.0f%%",double(x)/pg->GetRows()*100);
pp.SetPos(x);
}
app2.SetVisible(true);
pp.DestroyWindow();
sheet2.ReleaseDispatch();
sheets2.ReleaseDispatch();
// book.Close(_O((short)0),vOpt,vOpt); //关闭EXCEL,不保存文件
book2.ReleaseDispatch();
// books2.Close();
books2.ReleaseDispatch();
app2.ReleaseDispatch();
}