首页 > C++ > Excel的C++操作

Excel的C++操作

2012年11月28日 发表评论 阅读评论

最近老板让我去帮他朋友去弄一个莫名其妙的项目，其中要用到在程序中读写Excel数据。其实很久以前就就有遇到项目中要用上Excel的，但是最后不是无视掉了，就是换成.txt来读写，再不是，就是用.CSV来伪装。。。

虽然我知道不难，但是就是懒得去弄，最近被迫，只好去网上找一下别人的代码了，反正我不可能会去自己写的，顶多改改别人的代码。。。

嘛~找到的别人的程序里面可以用的，或者说比较好用的有两份，研究了一下使用状况，其中一份因为是和数据库有关，所以必须要写入一项header作为ID，因为这是数据库这种东西所必需的，但是那个代码里面呢，这个Header是要写在Excel第一行的，虽然研读+调试都没把这个东西彻底去掉，所以放弃了。。

另外一份虽然文件很多有很大，但是比起之前那个，实用性强多了。方法嘛，就是OLE，有点复杂，代码很长，有空具体研究一下【估计这句话八成是扯淡。。】，反正研究了一下怎么用，感觉还是方便又实用的。。

首先，版权问题。。。我该怎么去找作者要版权呢？？算了。。好像是外国人写的。。。

反正嘛，用的时候添加下面四个文件，两个cpp,两个h。

虽然提供的东西里面还有很多别的神奇的功能，比如从剪贴板插入图片什么之类的，但是那些东西暂时用不上，下面几个是最常用的，也是最好用的。

最简单的，写数据，读数据，这不用说吧。

CXLEzAutomation XL;
XL.SetCellValue(10,10,"Vespa");
AfxMessageBox(XL.GetCellValue(3,5));

保存文件

CString szFileName;
CXLEzAutomation XL(FALSE);
XL.SetCellValue(3,5,"Vespa");
static char BASED_CODE szFilter[] = "Chart Files (*.xls)|*.xls|Worksheet Files (*.xls)|*.xls|Data Files (*.xls;*.xlc)|*.xls; *.xlc|All Files (*.*)|*.*||";

CFileDialog    DataWrite(FALSE, // TRUE for FileOpen, FALSE for FileSaveAs
    "xls", NULL,
    OFN_PATHMUSTEXIST|OFN_OVERWRITEPROMPT,
    szFilter,
    NULL);

int nFileWrite = DataWrite.DoModal();

if(IDOK == nFileWrite)
{
    szFileName = DataWrite.GetPathName();
    XL.SaveFileAs(szFileName);
    XL.ReleaseExcel();
}

读取文件

static char BASED_CODE szFilter[] = "Worksheet Files (*.xls)|*.xls|Chart Files (*.xls)|*.xlc|Data Files (*.xlc;*.xls)|*.xlc; *.xls|All Files (*.*)|*.*||";

CFileDialog    DataRead(TRUE, // TRUE for FileOpen, FALSE for FileSaveAs
    "xls", NULL,
    OFN_PATHMUSTEXIST|OFN_OVERWRITEPROMPT,
    szFilter,
    NULL);

int nFileRead = DataRead.DoModal();

if(IDOK == nFileRead)
{
    CString szFileName = DataRead.GetPathName();
    if(szFileName.IsEmpty())
        return;
    CXLEzAutomation XL;
    if(!XL.OpenExcelFile(szFileName))
    {
        XL.ReleaseExcel();
        MessageBox("Failed to Open Excel File", "Error", MB_OK);
        return;
    }
}

插入图片

static char BASED_CODE szFilter[] = "BMP Files (*.bmp)|*.bmp|PCX Files (*.pcx)|*.pcx|TIF Files (*.tif)|*.tif|JPEG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif|All Files (*.*)|*.*||";

CFileDialog    OpenPic(TRUE, // TRUE for FileOpen, FALSE for FileSaveAs
    "bmp", NULL,
    OFN_PATHMUSTEXIST|OFN_OVERWRITEPROMPT,
    szFilter,
    NULL);

int nFileRead = OpenPic.DoModal();

if(IDOK == nFileRead)
{
    CString szFileName;
    szFileName = OpenPic.GetPathName();
    CXLEzAutomation XL;
    XL.InsertPictureFromFile(szFileName, 3, 3);

}

前面两行那种格式可以批量写入数据，后面一行表示可以画图。

CString szData = "111\t222\t333\n444";
XL.ExportCString(szData);

XL.CreateXYChart(2);//画第二列

代码在下面，4份文件复制出来，添加到工程就可以用了~突然发现了一个超好用的东西，本页跳转~所以就做个索引吧。

索引：
 Visit the XLEzAutomation.h
Visit the XLEzAutomation.cpp
Visit the XLAutomation.h
Visit the XLAutomation.cpp

// EzAutomation.h: interface for the CXLEzAutomation class.
//
//////////////////////////////////////////////////////////////////////

#if !defined(AFX_EzAutomation_H__D140B9A3_1995_40AC_8E6D_8F23A95A63A2__INCLUDED_)
#define AFX_EzAutomation_H__D140B9A3_1995_40AC_8E6D_8F23A95A63A2__INCLUDED_

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

#include "XLAutomation.h"
#define xlNormal -4143

class CXLEzAutomation  
{
public:
    BOOL InsertPictureFromBuffer(BYTE* pImage, int Column, int Row, double dXScale, double dYScale);
    BOOL OpenExcelFile(CString szFileName);
    BOOL PlacePictureToClipboard(BYTE* pImage);
    BOOL InsertPictureFromClipboard(int Column, int Row);
    BOOL InsertPictureFromFile(CString szFileName, int Column, int Row);
    CString GetCellValue(int nColumn, int nRow);
    BOOL SaveFileAs(CString szFileName);
    BOOL DeleteRow(int nRow);
    BOOL ReleaseExcel();
    BOOL SetCellValue(int nColumn, int nRow, CString szValue);
    BOOL ExportCString(CString szDataCollection);
    BOOL UpdateXYChart(int nYColumn);
    BOOL CreateXYChart(int nYColumn);
    CXLEzAutomation();
    CXLEzAutomation(BOOL bVisible);
    virtual ~CXLEzAutomation();

protected:
    CXLAutomation* m_pXLServer;
};

#endif // !defined(AFX_EzAutomation_H__D140B9A3_1995_40AC_8E6D_8F23A95A63A2__INCLUDED_)


XLEzAutomation.cpp:

// EzAutomation.cpp: implementation of the CXLEzAutomation class.
//This wrapper classe is provided for easy access to basic automation  
//methods of the CXLAutoimation.
//Only very basic set of methods is provided here.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "XLEzAutomation.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//

//////////////////////////////////////////////////////////////////////
//
//
//////////////////////////////////////////////////////////////////////
CXLEzAutomation::CXLEzAutomation()
{
    //Starts Excel with bVisible = TRUE and creates empty worksheet 
    m_pXLServer = new CXLAutomation;
}

CXLEzAutomation::CXLEzAutomation(BOOL bVisible)
{
    //Can be used to start Excel in background (bVisible = FALSE)
    m_pXLServer = new CXLAutomation(bVisible);

}

CXLEzAutomation::~CXLEzAutomation()
{
    if(NULL != m_pXLServer)
        delete m_pXLServer;
}

//Create XY plot of the type xlXYScatterLinesNoMarkers
BOOL CXLEzAutomation::CreateXYChart(int nYColumn)
{
    return m_pXLServer->CreateXYChart(nYColumn);
}
//Force update data source range . If new data points have been added to
//the data source, these points will be added to the plot.
//Default assumes data are placed in A and B columns of the Excel worksheet.
BOOL CXLEzAutomation::UpdateXYChart(int nYColumn)
{
    return m_pXLServer->UpdatePlotRange(nYColumn);
}
//Use clipboard to export szDataCollection to Excel worksheet
BOOL CXLEzAutomation::ExportCString(CString szDataCollection)
{
    return m_pXLServer->PasteStringToWorksheet(szDataCollection);

}
//Set cell value: Worksheet.Cells(nColumn, nRow).Value = szValue
BOOL CXLEzAutomation::SetCellValue(int nColumn, int nRow, CString szValue)
{
    BOOL bResult;

    bResult = m_pXLServer->SetCellsValueToString((double) nColumn, (double)nRow, szValue);
    return bResult;
}
//Quit Excel
BOOL CXLEzAutomation::ReleaseExcel()
{
    return m_pXLServer->ReleaseExcel();
}
//Delete line from worksheet
BOOL CXLEzAutomation::DeleteRow(int nRow)
{
    return m_pXLServer->DeleteRow(nRow);
}
//Save workbook as Excel file
BOOL CXLEzAutomation::SaveFileAs(CString szFileName)
{
    return m_pXLServer->SaveAs(szFileName, xlNormal,  _T(""), _T(""), FALSE, FALSE);
}
//Returns Worksheet.Cells(nColumn, nRow).Value
CString CXLEzAutomation::GetCellValue(int nColumn, int nRow)
{
    return m_pXLServer->GetCellValueCString(nColumn, nRow);
}
//Insert picture from file. Position it at (Column, Row) on the worksheet. 
//The method resizes the picture to 50% of the original size (see 0.5, 0.5)
BOOL CXLEzAutomation::InsertPictureFromFile(CString szFileName, int Column, int Row)
{
    return m_pXLServer->InsertPictureToWorksheet(szFileName, Column, Row, 0.5, 0.5);
}
//Insert picture which was previously placed on clipboard
//Does not resize picture (see 0.0, 0.0)
BOOL CXLEzAutomation::InsertPictureFromClipboard(int Column, int Row)
{
    return m_pXLServer->InsertPictureToWorksheet(NULL, Column, Row, 0.0, 0.0);
}
//Place image to clipboard using XLAutomation method
BOOL CXLEzAutomation::PlacePictureToClipboard(BYTE *pImage)
{
    return m_pXLServer->PlaceImageToClipboard(pImage);
}
//Open Excell file
BOOL CXLEzAutomation::OpenExcelFile(CString szFileName)
{
    return m_pXLServer->OpenExcelFile(szFileName);
}
//Insert picture from buffer. If pImage = NULL, the picture from clipboard will be inserted.
//The picture is placed at (Column, Row) on worksheet
//The picture can be resized in % of its original size
//by specifying dXScale and dYScale
//If no resize is needed, make dXScal = 0.0 and dYScale = 0.0 
BOOL CXLEzAutomation::InsertPictureFromBuffer(BYTE *pImage, int Column, int Row, double dXScale, double dYScale)
{
    return m_pXLServer->InsertPictureToWorksheet(pImage, Column, Row, dXScale, dYScale);
}


XLAutomation.h:

// XLAutomation.h: interface for the CXLAutomation class.
//
//////////////////////////////////////////////////////////////////////

#if !defined(AFX_XLAUTOMATION_H__E020CE95_7428_4BEF_A24C_48CE9323C450__INCLUDED_)
#define AFX_XLAUTOMATION_H__E020CE95_7428_4BEF_A24C_48CE9323C450__INCLUDED_

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

class CXLAutomation  
{

#define MAX_DISP_ARGS 10
#define DISPARG_NOFREEVARIANT 0x01
#define DISP_FREEARGS 0x02
#define DISP_NOSHOWEXCEPTIONS 0x03
#define xlWorksheet -4167
#define xl3DPie -4102
#define xlRows 1
#define xlXYScatter -4169
#define xlXYScatterLines 74
#define xlXYScatterSmoothNoMarkers 73
#define xlXYScatterSmooth 72
#define xlXYScatterLinesNoMarkers 75
#define xlColumns 2
#define xlNormal -4143
#define xlUp -4162

public:
    BOOL OpenExcelFile(CString szFileName);
    BOOL InsertPictureToWorksheet(BYTE* pImage, int Column, int Row, double dPicWidth, double dPicHeight);
    BOOL PlaceImageToClipboard(BYTE* pImage);
    BOOL InsertPictureToWorksheet(CString szFileName, int Column, int Row, double dPicWidth, double dPicHeight);
    CString GetCellValueCString(int nColumn, int nRow);
    BOOL SaveAs(CString szFileName, int nFileFormat, CString szPassword, CString szWritePassword, BOOL bReadOnly, BOOL bBackUp);
    BOOL DeleteRow(long nRow);
    BOOL ReleaseExcel();
    BOOL PasteStringToWorksheet(CString pDataBuffer);
    BOOL UpdatePlotRange(int nYColumn);
    BOOL AddArgumentCStringArray(LPOLESTR lpszArgName,WORD wFlags, LPOLESTR *paszStrings, int iCount);
    BOOL SetRangeValueDouble(LPOLESTR lpszRef, double d);
    BOOL CreateXYChart(int nYColumn);
    BOOL SetCellsValueToString(double Column, double Row, CString szStr);
    BOOL AddArgumentOLEString(LPOLESTR lpszArgName, WORD wFlags, LPOLESTR lpsz);
    BOOL AddArgumentCString(LPOLESTR lpszArgName, WORD wFlags, CString szStr);
    BOOL CreateWorkSheet();
    BOOL AddArgumentDouble(LPOLESTR lpszArgName, WORD wFlags, double d);
    BOOL AddArgumentBool(LPOLESTR lpszArgName, WORD wFlags, BOOL b);
    BOOL AddArgumentInt2(LPOLESTR lpszArgName, WORD wFlags, int i);
    BOOL AddArgumentDispatch(LPOLESTR lpszArgName, WORD wFlags, IDispatch * pdisp);
    void AddArgumentCommon(LPOLESTR lpszArgName, WORD wFlags, VARTYPE vt);
    BOOL InitOLE();
    CXLAutomation();
    CXLAutomation::CXLAutomation(BOOL bVisible);
    virtual ~CXLAutomation();

protected:
    void ShowException(LPOLESTR szMember, HRESULT hr, EXCEPINFO *pexcep, unsigned int uiArgErr);
    void ReleaseDispatch();
    BOOL SetExcelVisible(BOOL bVisible);
    void ReleaseVariant(VARIANTARG *pvarg);
    void ClearAllArgs();
    void ClearVariant(VARIANTARG *pvarg);

    int            m_iArgCount;
    int            m_iNamedArgCount;
    VARIANTARG    m_aVargs[MAX_DISP_ARGS];
    DISPID        m_aDispIds[MAX_DISP_ARGS + 1];        // one extra for the member name
    LPOLESTR    m_alpszArgNames[MAX_DISP_ARGS + 1];    // used to hold the argnames for GetIDs
    WORD        m_awFlags[MAX_DISP_ARGS];

    BOOL ExlInvoke(IDispatch *pdisp, LPOLESTR szMember, VARIANTARG * pvargReturn,
            WORD wInvokeAction, WORD wFlags);
    IDispatch* m_pdispExcelApp;
    IDispatch *m_pdispWorkbook;
    IDispatch *m_pdispWorksheet;
    IDispatch *m_pdispActiveChart;
    BOOL StartExcel();
};

#endif // !defined(AFX_XLAUTOMATION_H__E020CE95_7428_4BEF_A24C_48CE9323C450__INCLUDED_)


XLAutomation.cpp:

XLAutomation.cpp

【完】

本文内容遵从CC版权协议，转载请注明出自http://www.kylen314.com

分类: C++ 标签: , ,
  1. 本文目前尚无任何评论.
验证码：7 + 6 = ?

友情提示：留言可以使用大部分html标签和属性;

添加代码示例:[code lang="cpp"]your code...[/code]

添加公式请用Latex代码，前后分别添加两个$$