//Excel.cpp
#include "stdafx.h"
#include <tchar.h>
#include "Excel.h"
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CApplication Excel::application;
Excel::Excel() :isLoad(false)
{
}
Excel::~Excel()
{
//close();
}
bool Excel::initExcel()
{
//创建Excel 2000服务器(启动Excel)
if (!application.CreateDispatch(_T("Excel.application"), nullptr))
{
MessageBox(nullptr, _T("创建Excel服务失败,你可能没有安装EXCEL，请检查!"), _T("错误"), MB_OK);
return FALSE;
}
application.put_DisplayAlerts(FALSE);
return true;
}
bool Excel::initExcelEx()
{
//创建Excel 2000服务器(启动Excel)
if (!application.CreateDispatch(_T("Excel.application")))
{
MessageBox(nullptr, _T("创建Excel服务失败,你可能没有安装EXCEL，请检查!"), _T("错误"), MB_OK);
return FALSE;
}
application.put_Visible(TRUE); //是否显示execl
application.put_UserControl(FALSE);
application.put_DisplayFullScreen(FALSE);//设置全屏显示
application.put_DisplayAlerts(FALSE);//屏蔽警告
return true;
}
/*
m_openFilePath = OpenPath;
if (!m_ExcelApp.CreateDispatch(_T("Excel.Application")))
{
AfxMessageBox(_T("居然你连OFFICE都没有安装吗？"), MB_OK | MB_ICONWARNING);
return;
}
m_ExcelApp.put_Visible(TRUE); //是否显示execl
m_ExcelApp.put_UserControl(FALSE);
m_ExcelApp.put_DisplayFullScreen(FALSE);//设置全屏显示
m_ExcelApp.put_DisplayAlerts(FALSE);//屏蔽警告
*/
/*打开工作簿*/
//m_ExcelBooks = m_ExcelApp.get_Workbooks();
//try
//{
// m_ExcelBook = m_ExcelBooks.Add(_variant_t(m_openFilePath));//打开execl文件
//}
//catch (CException* e)
//{
// m_ExcelBook = m_ExcelBooks.Add(vtMissing);//找不到就新建一个
//}
/*得到工作薄中1Sheet容器*/
//m_ExcelSheets.AttachDispatch(m_ExcelBook.get_Worksheets());
//m_ExcelSheet = m_ExcelBook.get_ActiveSheet();//获取当前工作表
//m_ExcelSheet = m_ExcelSheets.get_Item(COleVariant((short)1));//获取第一个工作表
void Excel::release()
{
application.Quit();
application.ReleaseDispatch();
application = nullptr;
}
bool Excel::open(const char* fileName)
{
//先关闭文件
close();
//利用模板建立新文档
books.AttachDispatch(application.get_Workbooks(), true);
LPDISPATCH lpDis = nullptr;
lpDis = books.Add(COleVariant(CString(fileName)));
if (lpDis)
{
workBook.AttachDispatch(lpDis);
sheets.AttachDispatch(workBook.get_Worksheets());
openFileName = fileName;
return true;
}
return false;
}
void Excel::close(bool ifSave)
{
//如果文件已经打开，关闭文件
if (!openFileName.IsEmpty())
{
//如果保存,交给用户控制,让用户自己存，如果自己SAVE，会出现莫名的等待
if (ifSave)
{
//show(true);
}
else
{
workBook.Close(COleVariant(short(FALSE)), COleVariant(openFileName), covOptional);
books.Close();
}
//清空打开文件名称
openFileName.Empty();
}
sheets.ReleaseDispatch();
workSheet.ReleaseDispatch();
currentRange.ReleaseDispatch();
workBook.ReleaseDispatch();
books.ReleaseDispatch();
}
//关闭execl
void Excel::CloseTable(CString strSavePath)
{
m_saveFilePath = strSavePath;
workBook.SaveCopyAs(COleVariant(m_saveFilePath));
workBook.put_Saved(TRUE);
// 释放对象
books.ReleaseDispatch();
workBook.ReleaseDispatch();
sheets.ReleaseDispatch();
workSheet.ReleaseDispatch();
m_ExcelRange.ReleaseDispatch();
application.Quit();
application.ReleaseDispatch();
/*
m_saveFilePath = strSavePath;
m_ExcelBook.SaveCopyAs(COleVariant(m_saveFilePath));
m_ExcelBook.put_Saved(TRUE);
// 释放对象
m_ExcelBooks.ReleaseDispatch();
m_ExcelBook.ReleaseDispatch();
m_ExcelSheets.ReleaseDispatch();
m_ExcelSheet.ReleaseDispatch();
m_ExcelRange.ReleaseDispatch();
m_ExcelApp.Quit();
m_ExcelApp.ReleaseDispatch();
*/
}
void Excel::saveAsXLSFile(const CString &xlsFile)
{
workBook.SaveAs(COleVariant(xlsFile),
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
0,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional);
return;
}
int Excel::getSheetCount()
{
return sheets.get_Count();
}
CString Excel::getSheetName(long tableID)
{
CWorksheet sheet;
sheet.AttachDispatch(sheets.get_Item(COleVariant((long)tableID)));
CString name = sheet.get_Name();
sheet.ReleaseDispatch();
return name;
}
void Excel::preLoadSheet()
{
CRange used_range;
used_range = workSheet.get_UsedRange();
VARIANT ret_ary = used_range.get_Value2();
if (!(ret_ary.vt & VT_ARRAY))
{
return;
}
//
safeArray.Clear();
safeArray.Attach(ret_ary);
}
//按照名称加载sheet表格，也可提前加载所有表格
bool Excel::loadSheet(long tableId, bool preLoaded)
{
LPDISPATCH lpDis = nullptr;
currentRange.ReleaseDispatch();
currentRange.ReleaseDispatch();
lpDis = sheets.get_Item(COleVariant((long)tableId));
if (lpDis)
{
workSheet.AttachDispatch(lpDis, true);
currentRange.AttachDispatch(workSheet.get_Cells(), true);
}
else
{
return false;
}
isLoad = false;
//如果进行预先加载
if (preLoaded)
{
preLoadSheet();
isLoad = true;
}
return true;
}
bool Excel::loadSheet(CString sheet, bool preLoaded)
{
LPDISPATCH lpDis = nullptr;
currentRange.ReleaseDispatch();
currentRange.ReleaseDispatch();
lpDis = sheets.get_Item(COleVariant(sheet));
if (lpDis)
{
workSheet.AttachDispatch(lpDis, true);
currentRange.AttachDispatch(workSheet.get_Cells(), true);
}
else
{
return false;
}
isLoad = false;
//如果进行预先加载
if (preLoaded)
{
preLoadSheet();
isLoad = true;
}
return true;
}
int Excel::getColumnCount()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(workSheet.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Columns(), true);
int count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
int Excel::getRowCount()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(workSheet.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Rows(), true);
int count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
bool Excel::isCellString(long iRow, long iColumn)
{
CRange range;
range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
COleVariant vResult = range.get_Value2();
//VT_BSTR标示字符串
if (vResult.vt == VT_BSTR)
{
return true;
}
return false;
}
bool Excel::isCellInt(long iRow, long iColumn)
{
CRange range;
range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
COleVariant vResult = range.get_Value2();
//VT_BSTR标示字符串
if (vResult.vt == VT_INT || vResult.vt == VT_R8)
{
return true;
}
return false;
}
CString Excel::getCellString(long iRow, long iColumn)
{
COleVariant vResult;
CString str;
//字符串
if (isLoad == false)
{
CRange range;
range.AttachDispatch(currentRange.get_Item(COleVariant((long)iRow), COleVariant((long)iColumn)).pdispVal, true);
vResult = range.get_Value2();
range.ReleaseDispatch();
}
//如果数据依据预先加载了
else
{
long read_address[2];
VARIANT val;
read_address[0] = iRow;
read_address[1] = iColumn;
safeArray.GetElement(read_address, &val);
vResult = val;
}
if (vResult.vt == VT_BSTR)
{
str = vResult.bstrVal;
}
//整数
else if (vResult.vt == VT_INT)
{
str.Format(_T("%d"), vResult.pintVal);
}
//8字节的数字
else if (vResult.vt == VT_R8)
{
str.Format(_T("%0.0f"), vResult.dblVal);
}
//时间格式
else if (vResult.vt == VT_DATE)
{
SYSTEMTIME st;
VariantTimeToSystemTime(vResult.date, &st);
CTime tm(st);
str = tm.Format(_T("%Y-%m-%d"));
}
//单元格空的
else if (vResult.vt == VT_E
