方法1:通过数据库
(1)导出excel
CDatabase database;
CString sDriver = _T("MICROSOFT EXCEL DRIVER (*.XLS)"); // Excel安装驱动
CString sExcelFile = _T("c:\\系统告警信息导出文件.xls"); // 要建立的Excel文件
if (_waccess(sExcelFile.AllocSysString(),0)!= -1)
remove(sExcelFile);//删除
TRY
{
// 创建进行存取的字符串
CString sSql;
sSql.Format(_T("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s"),
sDriver, sExcelFile, sExcelFile);
// 创建数据库 (既Excel表格文件)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
// 创建表结构(姓名、年龄)
sSql = _T("CREATE TABLE 表名(编号 TEXT, 字段1TEXT, 字段2TEXT, 字段3TEXT, 字段4TEXT, 字段5TEXT,[字段6] TEXT)");
database.ExecuteSQL(sSql);
for( int iCount=0; iCount <m_list.GetItemCount();iCount++)
{
CString strNum,strMsi,strPhoneNum,strType,strResult,strDescribe,strTime;
strNum = m_list.GetItemText(iCount, 0);
strMsi = m_list.GetItemText(iCount, 1);
strPhoneNum = m_list.GetItemText(iCount, 2);
strType = m_list.GetItemText(iCount, 3);
strResult = m_list.GetItemText(iCount, 4);
strDescribe = m_list.GetItemText(iCount, 5);
strTime = m_list.GetItemText(iCount, 6);
// 插入数值
sSql.Format(_T("INSERT INTO 表名(编号, 字段1, 字段2, 字段3, 字段4, 字段5,\
[字段6]) VALUES ('%s','%s','%s', '%s','%s','%s', '%s')"),
strNum,strMsi,strPhoneNum, strType,strResult,strDescribe, strTime);
database.ExecuteSQL(sSql);
}
}
// 关闭数据库
database.Close();
}
CATCH_ALL(e)
{
TRACE1("Excel驱动没有安装: %s",sDriver);
MessageBox(_T( "导出数据失败!"), _T( "错误" ), MB_OK);
}
END_CATCH_ALL;
(2)导入excel
CString strFileFilter(_T("XLS File(*.xls)|*.xls|XLSX File(*.xlsx)|*.xlsx||"));
CFileDialog fileOpen(TRUE, _T("*.xls;*.xlsx"), _T(""), OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,strFileFilter , NULL);
if (IDOK != fileOpen.DoModal())
return ;
CString sExcelFile = fileOpen.GetPathName();
CString sSql;
CString sDriver = _T("MICROSOFT EXCEL DRIVER (*.XLS)"); // Excel安装驱动
sSql.Format(_T("DRIVER={%s};DSN='''';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s"),
sDriver, sExcelFile, sExcelFile);
CDatabase database;
CRecordset rs;
if( !database.OpenEx(sSql,CDatabase::noOdbcDialog) )
return;
m_list.DeleteAllItems();
CRecordset recordset(&database);
sSql = _T("select * from 白名单 ");
recordset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
CString strPhoneNumber,strVender,strIMSI,strIMEI,strRollCall;
int nIndex = 0;
while( !recordset.IsEOF( ) )
{
recordset.GetFieldValue(short(0),strPhoneNumber);
recordset.GetFieldValue(short(1),strVender);
recordset.GetFieldValue(short(2),strIMSI);
recordset.GetFieldValue(short(3),strIMEI);
recordset.GetFieldValue(short(4),strRollCall);
recordset.MoveNext();
}
方法2:调用libxl动态库
#pragma once
#include <vector>
#include <string>
//! 读取excel中的数据
struct CLibXlInfo;
class _declspec(dllexport) CExcelReaderLibXl
{
public:
CExcelReaderLibXl(void);
virtual ~CExcelReaderLibXl(void);
public:
bool Open(CString& strXlsFile);
bool Close();
bool Save();//保存
public:
long GetSheetCount();
long GetRows(long lSheetIndex);
long GetCols(long lSheetIndex);
bool ReadRowTexts(long lSheetIndex , long lRow, std::vector<CString>& arrValues);
//写入数据
bool WirteCellText(long lSheetIndex , long lRow, long lCol, CString strValue);
private:
CLibXlInfo* m_pBookInfo;
};
#include "StdAfx.h"
#include "ExcelReaderLibXl.h"
#include "libxl-3.7.2.0/libxl.h"
struct CLibXlInfo
{
CLibXlInfo ()
: m_pBook(NULL)
{
}
libxl::Book* m_pBook;
CString m_strFileName;
};
CExcelReaderLibXl::CExcelReaderLibXl(void)
: m_pBookInfo(new CLibXlInfo)
{
}
CExcelReaderLibXl::~CExcelReaderLibXl(void)
{
Close();
delete m_pBookInfo;
}
bool CExcelReaderLibXl::Open(CString& strXlsFile)
{
if (!Close())
return false;
CString strExt(CComVar(strXlsFile).GetFileExtOnly());
strExt.MakeUpper();
m_pBookInfo->m_pBook = NULL;
if (strExt == _T("XLS"))
m_pBookInfo->m_pBook = xlCreateBook();
else if (strExt == _T("XLSX"))
m_pBookInfo->m_pBook = xlCreateXMLBook();
if (NULL == m_pBookInfo->m_pBook)
return false;
//解锁
m_pBookInfo->m_pBook->setKey(_T("Halil Kural"), _T("windows-2723210a07c4e90162b26966a8jcdboe"));
if (!m_pBookInfo->m_pBook->load(strXlsFile))
{
Close();
return false;
}
m_pBookInfo->m_strFileName = strXlsFile;
return true;
}
//获取页数
long CExcelReaderLibXl::GetSheetCount()
{
if (NULL == m_pBookInfo->m_pBook)
return -1;
return m_pBookInfo->m_pBook->sheetCount();
}
long CExcelReaderLibXl::GetRows(long lSheetIndex)
{
if (NULL == m_pBookInfo->m_pBook)
return -1;
if (lSheetIndex < 0 || lSheetIndex >= m_pBookInfo->m_pBook->sheetCount())
return -1;
libxl::Sheet* pSheet = m_pBookInfo->m_pBook->getSheet(lSheetIndex);
if (NULL == pSheet)
return -1;
return (pSheet->lastRow() - pSheet->firstRow() + 1);
}
long CExcelReaderLibXl::GetCols(long lSheetIndex)
{
if (NULL == m_pBookInfo->m_pBook)
return -1;
if (lSheetIndex < 0 || lSheetIndex >= m_pBookInfo->m_pBook->sheetCount())
return -1;
libxl::Sheet* pSheet = m_pBookInfo->m_pBook->getSheet(lSheetIndex);
if (NULL == pSheet)
return -1;
return (pSheet->lastCol() - pSheet->firstCol() + 1);
}
//读取一行数据
bool CExcelReaderLibXl::ReadRowTexts(long lSheetIndex , long lRow, std::vector<CString>& arrValues)
{
arrValues.clear();
if (NULL == m_pBookInfo->m_pBook)
return false;
if (lSheetIndex < 0 || lSheetIndex >= m_pBookInfo->m_pBook->sheetCount())
return false;
libxl::Sheet* pSheet = m_pBookInfo->m_pBook->getSheet(lSheetIndex);
if (NULL == pSheet)
return false;
long lRowNum = pSheet->lastRow() - pSheet->firstRow() + 1;
long lColNum = pSheet->lastCol() - pSheet->firstCol() + 1;
if (lRowNum < lRow)
return false;
CString strValue;
//行列从0开始
for (long lColIndex = 0; lColIndex < lColNum; ++lColIndex)
{
strValue.Empty();
libxl::CellType curCellType = pSheet->cellType(lRow, lColIndex);
if (curCellType == libxl::CellType::CELLTYPE_NUMBER)
{
strValue.Format(_T("%f"), pSheet->readNum(lRow, lColIndex));
strValue.TrimRight(_T("0"));
strValue.TrimRight(_T("."));
}
else
{
strValue = pSheet->readStr(lRow, lColIndex);
}
arrValues.push_back(strValue);
}
return true;
}
bool CExcelReaderLibXl::Close()
{
if (NULL == m_pBookInfo->m_pBook)
return true;
m_pBookInfo->m_pBook->release();
m_pBookInfo->m_pBook = NULL;
return true;
}
bool CExcelReaderLibXl::WirteCellText( long lSheetIndex , long lRow, long lCol, CString strValue )
{
libxl::Sheet* pSheet = m_pBookInfo->m_pBook->getSheet(0);
if (NULL == pSheet)
return false;
libxl::Font* pFont = m_pBookInfo->m_pBook->addFont();
if (NULL == pFont)
return false;
pFont->setName(_T("宋体"));
pFont->setSize(10);
pFont->setColor(libxl::Color::COLOR_BLACK);
libxl::Format* pFormat = m_pBookInfo->m_pBook->addFormat();
if (NULL == pFormat)
return false;
pFormat->setFont(pFont);
pFormat->setAlignH(libxl::AlignH::ALIGNH_LEFT);
pFormat->setPatternForegroundColor(libxl::Color::COLOR_NONE);
if (!pSheet->writeStr(lRow,lCol, strValue,pFormat))
return false;
return true;
}
bool CExcelReaderLibXl::Save()
{
return m_pBookInfo->m_pBook->save(m_pBookInfo->m_strFileName);
}