求帮忙看一下代码,功能:MFC 从mysql数据库导出excel 。
导出后得的excel,有些电脑显示中文乱码,有些正常,该怎么修改啊?
void Cmysql0922Dlg::OnBnClickedButton4() //mysql导出excel
{
TCHAR szFilter[] = _T("Files(*.xls;*.xlsx)|*.xls;*.xlsx|All Files (*.*)|*.*||");
//CFileDialog类 实现文件打开(导入)和文件保存
CFileDialog FileDialog(FALSE, _T("xlsx"), NULL, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, szFilter, this);
if (FileDialog.DoModal() != IDOK)
{
return;
}
CString cStrFile = FileDialog.GetPathName(); //选择保存路径
if (::PathFileExists(cStrFile))
DeleteFile(cStrFile);
COleVariant covTrue((short)TRUE), covFalse((short)FALSE), covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CApplication app; //Excel程序
CWorkbooks books; //工作簿集合
CWorkbook book; //工作簿
CWorksheets sheets; //工作表集合
CWorksheet sheet; //工作表
CRange range; //使用区域
CoUninitialize();
book.PrintPreview(_variant_t(false));
if (CoInitialize(NULL) == S_FALSE)
{
MessageBox("初始化COM支持库失败!");
return;
}
//创建Excel服务器(启动Excel)
if (!app.CreateDispatch(_T("Excel.Application"))) //创建IDispatch接口对象
{
MessageBox(_T("创建Excel服务失败!"));
return;
}
//记录导出时间
clock_t startTime, finishTime;
startTime = clock();
//设置表
app.put_Visible(TRUE); //操作时可见
app.put_UserControl(FALSE);
app.put_DisplayFullScreen(FALSE);
app.put_DisplayAlerts(FALSE);
books = app.get_Workbooks();//获取工作簿
book = books.Add(covOptional);//新建工作簿
sheets = book.get_Worksheets();//获取工作表
sheet = sheets.get_Item(COleVariant((short)1)); //得到第一个工作表
CListCtrl* pList = (CListCtrl*)GetDlgItem(IDC_LIST);
CHeaderCtrl* pmyHeaderCtrl = pList->GetHeaderCtrl(); //获取表头
int m_cols = pmyHeaderCtrl->GetItemCount(); //获取列数
int m_rows = pList->GetItemCount(); //获取行数
TCHAR lpBuffer[256];
HDITEM hdi; //HDIITEM是个结构体,存储文件头部信息。
hdi.mask = HDI_TEXT; // pszText 和 cchTextMax 成员有效。
hdi.pszText = lpBuffer; //指向项字符串的指针
hdi.cchTextMax = 256;
CString colname;
int iRow, iCol;
for (iCol = 0; iCol < m_cols; iCol++)//将列表的标题头写入EXCEL
{
GetCellName(1, iCol + 1, colname); //获取单元格名称(行,列,colname就是对应表格的A1,B1,C1,D1)
range = sheet.get_Range(COleVariant(colname), COleVariant(colname));
pmyHeaderCtrl->GetItem(iCol, &hdi); //获取表头每列的信息
range.put_Value2(COleVariant(hdi.pszText)); //put_Value2 赋值,设置每列的内容
int nWidth = pList->GetColumnWidth(iCol) / 6;
//得到第iCol+1列
range.AttachDispatch(range.get_Item(_variant_t((long)(iCol + 1)), vtMissing).pdispVal, true);
//设置列宽
range.put_ColumnWidth(_variant_t((long)nWidth));
}
range = sheet.get_Range(COleVariant(_T("A1 ")), COleVariant(colname));
range.put_RowHeight(_variant_t((long)50));//设置行的高度
range.put_VerticalAlignment(COleVariant((short)-4108));//xlVAlignCenter = -4108
COleSafeArray saRet; //COleSafeArray类是用于处理任意类型和维数的数组的类
DWORD numElements[] = { m_rows,m_cols }; //行列写入数组
saRet.Create(VT_BSTR, 2, numElements); //创建所需的数组
range = sheet.get_Range(COleVariant(_T("A2 ")), covOptional); //从A2开始
range = range.get_Resize(COleVariant((short)m_rows), COleVariant((short)m_cols)); //表的区域
long index[2];
for (iRow = 1; iRow <= m_rows; iRow++)//将列表内容写入EXCEL
{
for (iCol = 1; iCol <= m_cols; iCol++)
{
index[0] = iRow - 1;
index[1] = iCol - 1;
CString szTemp;
szTemp = pList->GetItemText(iRow - 1, iCol - 1); //取得pList控件中的内容
BSTR bstr = szTemp.AllocSysString(); //The AllocSysString method allocates a new BSTR string that is Automation compatible
saRet.PutElement(index, bstr); //把pList控件中的内容放入saRet
SysFreeString(bstr);
}
}
range.put_Value2(COleVariant(saRet)); //将得到的数据的saRet数组值放入表格
book.SaveCopyAs(COleVariant(cStrFile)); //保存到cStrFile文件
finishTime = clock();
CString time;
time.Format("已成功导出到Excel文件,用时 %g 秒!", (double)(finishTime - startTime) / CLOCKS_PER_SEC);
MessageBox(time);
book.put_Saved(true);
books.Close();
book.ReleaseDispatch();
books.ReleaseDispatch();
app.ReleaseDispatch();
app.Quit();
}
//导入导出excel调用的函数
void Cmysql0922Dlg::GetCellName(int nRow, int nCol, CString& strName)
{
int nSeed = nCol;
CString strRow;
char cCell = 'A' + nCol - 1;
strName.Format(_T("%c"), cCell);
strRow.Format(_T("%d "), nRow);
strName += strRow;
}
```