MFC导入导出EXCEL文件

1.在多文档程序中，选中解决方案右击鼠标添加类，选择TypeLib中的MFC类

在该类中选择可用的类型库为excel相关库，选择接口CWorksheet，CWorksheet是，CWorkbook，CWorkbooks,CFont，CBorders。点击确定

生成这些文件中将“#import “C:\Program Files (x86)\Microsoft Office\Office12\EXCEL.EXE” no_namespace”注释掉。在这些文件中出现错误不要担心，这是因为还未调用此文件。
2.具体实现过程：

#pragma once
#include "afxwin.h"
#include "ToString.h"

// ExcelToDS 对话框

class ExcelToDS : public CDialogEx
{
    DECLARE_DYNAMIC(ExcelToDS)

public:
    ExcelToDS(CWnd* pParent = NULL);   // 标准构造函数
    virtual ~ExcelToDS();

// 对话框数据
    enum { IDD = IDD_DLG_EXCELTODS };

protected:
    virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV 支持

    DECLARE_MESSAGE_MAP()
public:
    //CRange UnitRge;
    afx_msg void OnBnClickedButton1();
    virtual BOOL OnInitDialog();
    void toDB();
    ToString ts;
    afx_msg void OnBnClickedButton2();
    afx_msg void OnBnClickedOk();
    afx_msg void OnCbnSelchangeCombo1();
    afx_msg void OnBnClickedBtnBrows();
    // 输出到Excel选择列表框
    afx_msg void OnBnClickedBtnBrows2();
    int flag1 ;
    int flag2;
};

// ExcelToDS.cpp : 实现文件
//

#include "stdafx.h"
#include "BusSim.h"
#include "ExcelToDS.h"
#include "afxdialogex.h"
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
#include "Mysql.h"
#include "SetDefine.h"
#include "ToString.h"
#include<map>
#include "CBorders.h"
#include "CFont0.h"


#include "CApplication.h"
using namespace std;
// ExcelToDS 对话框
typedef pair<long,long> P;
IMPLEMENT_DYNAMIC(ExcelToDS, CDialogEx)

ExcelToDS::ExcelToDS(CWnd* pParent /*=NULL*/)
    : CDialogEx(ExcelToDS::IDD, pParent)
{

}

ExcelToDS::~ExcelToDS()
{
}

void ExcelToDS::DoDataExchange(CDataExchange* pDX)
{
    CDialogEx::DoDataExchange(pDX);

}


BEGIN_MESSAGE_MAP(ExcelToDS, CDialogEx)
    ON_BN_CLICKED(IDC_BUTTON1, &ExcelToDS::OnBnClickedButton1)
    ON_BN_CLICKED(IDC_BUTTON2, &ExcelToDS::OnBnClickedButton2)
    ON_BN_CLICKED(IDOK, &ExcelToDS::OnBnClickedOk)
    ON_CBN_SELCHANGE(IDC_COMBO1, &ExcelToDS::OnCbnSelchangeCombo1)
    ON_BN_CLICKED(IDC_BTN_BROWS, &ExcelToDS::OnBnClickedBtnBrows)
    ON_BN_CLICKED(IDC_BTN_BROWS2, &ExcelToDS::OnBnClickedBtnBrows2)
END_MESSAGE_MAP()


// ExcelToDS 消息处理程序

map<pair<long,long>,CString> strResult;
pair<long,long> strResultNum;
void ExcelToDS::OnBnClickedButton1()
{
    // TODO: ÔÚ´ËÌí¼Ó¿Ø¼þ֪ͨ´¦Àí³ÌÐò´úÂë

    if(flag1==1)
    {
        MessageBox(_T("导入成功"),_T("提示"));
        flag1 = 0;
    }
    else
    {
        MessageBox(_T("导入失败"),_T("提示"),MB_OK|MB_ICONWARNING);
    }

}
void ExcelToDS::toDB()
{

}

BOOL ExcelToDS::OnInitDialog()
{
    CDialogEx::OnInitDialog();
    //SelectTable.SetCurSel(0);//设置Combox的默认选项

    flag1 = 0;
    flag2 = 0;
    return TRUE;  
}


void ExcelToDS::OnBnClickedButton2()
{
    if(flag2==1)
    {
        flag2 = 0;
        MessageBox(_T("导出成功"),_T("提示"));
    }
}


void ExcelToDS::OnBnClickedOk()
{
    // TODO: ÔÚ´ËÌí¼Ó¿Ø¼þ֪ͨ´¦Àí³ÌÐò´úÂë
    CDialogEx::OnOK();
}

//导入
void ExcelToDS::OnBnClickedBtnBrows()
{
    // TODO: ÔÚ´ËÌí¼Ó¿Ø¼þ֪ͨ´¦Àí³ÌÐò´úÂë
//ref:https://blog.csdn.net/susik/article/details/5841168

// }
    ::CoInitialize(NULL);
    CApplication app;//建立excel应用程序类
    CRange range;//EXCEL单元格选择范围
    CWorkbooks books;//工作簿集合
    CWorksheets sheets;//工作表集合
    CWorkbook book;//工作簿
    CWorksheet sheet;//工作表

    //star excel测试EXCEL服务器是否启动正常
    if (!app.CreateDispatch(_T("Excel.Application")))
    {
        MessageBox(_T("无法启动Excel启动器"),_T("提示"),MB_OK|MB_ICONWARNING);
        return ;
    }
    //open *.xls
    books.AttachDispatch(app.get_Workbooks());//得到工作表
        //get file path
    //LPCTSTR szFilter = _T("txt(*.txt)|*.txt|excel(*.xls)|*.xls|All Filter(*.*)|*.*||");
    LPCTSTR szFilter = _T("excel(*.xls)|*.xls*|All Filter(*.*)|*.*||");//待打开文件过滤
    CFileDialog dlg(TRUE,NULL,NULL,OFN_HIDEREADONLY,szFilter);//建立打开文件对话框
    if (IDOK != dlg.DoModal())//判断点击的按钮是否为“确定”
    {
        return ;//不是“确定”返回
    }
    CString strPath = dlg.GetPathName();//得到文件路径
    //COleVariant类为VARIANT数据类型的包装，在自动化程序中，通常都使用VARIANT数据类型进行参数传递。
    //故下列程序中，函数参数都是通过COleVariant类来转换了的
    GetDlgItem(IDC_EDIT_IMPORT_PATH)->SetWindowTextW(strPath);
    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    LPDISPATCH lpDisp = books.Open(strPath,covOptional,covOptional,covOptional,
                                    covOptional,covOptional,covOptional,covOptional,
                                    covOptional,covOptional,covOptional,covOptional,
                                    covOptional,covOptional,covOptional);
    //get workbook
    book.AttachDispatch(lpDisp);

    //get worksheets
    sheets.AttachDispatch(book.get_Worksheets());

    //get the current active sheet
    lpDisp = book.get_ActiveSheet();
    sheet.AttachDispatch(lpDisp);

    //get used regional information
    CRange UsedRange;
    UsedRange.AttachDispatch(sheet.get_UsedRange());

    //get used line numbers
    range.AttachDispatch(UsedRange.get_Rows());
    long iRowNum = range.get_Count();

    //get used column numbers
    range.AttachDispatch(UsedRange.get_Columns());
    long iColNum = range.get_Count();

    //read the starting line and column
    long iStarRow = UsedRange.get_Row();
    long iStarCol = UsedRange.get_Column();

    //read the table values
    long tdrow = iStarRow+1;
    long tdcol = iStarCol;

    for (;iStarRow <= iRowNum;iStarRow++)
    {
        for (iStarCol = UsedRange.get_Column();iStarCol <= iColNum;iStarCol++)
        {
            range.AttachDispatch(sheet.get_Cells());
            range.AttachDispatch(range.get_Item(COleVariant(iStarRow),COleVariant(iStarCol)).pdispVal);
            COleVariant vResult = range.get_Value2();
            CString str;
            if (vResult.vt == VT_BSTR)//character string
            {
                str = vResult.bstrVal;
            }
            else if (vResult.vt == VT_R8)//8 byte of digital
            {
                str.Format(_T("%f"),vResult.dblVal);
            }
            else if (vResult.vt == VT_DATE)//date time
            {
                SYSTEMTIME st;
                VariantTimeToSystemTime(vResult.date,&st);
            }
            else if (vResult.vt == VT_EMPTY)//blank space
            {
                str = "";
            }
            strResultNum.first = iStarRow;
            strResultNum.second = iStarCol;
            strResult[strResultNum] = str;
        }
    }

    Mysql *mysql = new Mysql();
    char *Msg = ""; 
    mysql->Connmysql(HOST,PORT,DBNAME,USER,PASSWD,CHARSET,Msg);
    char sqlstr[1005];//submit data to database
    char *sql[205];//string pointer array

    sql[0] = "insert into tb_student(id,name,class,college) values(\""; 
    if(iColNum==4)
    {
        GetDlgItem(IDC_EDIT_EXCEL_IMPORT)->SetWindowTextW(_T("线缆"));
        for(int i=tdrow;i<=iRowNum;i++)
        {
            int pos = 1;
            for(int j=tdcol;j<=iColNum;j++)
            {
                CString temp = strResult[P(i,j)];
                sql[pos++] = ts.tostring(temp);
                if(j==iColNum)
                {
                    sql[pos++] = "\")";
                }
                else
                {
                    sql[pos++]="\",\"";
                }
            }
            strcpy(sqlstr,sql[0]);
            for(int i=1;i<pos;i++)
            {
                strcat(sqlstr,sql[i]);
            }
            char *Msg1="";
            if(mysql->InsertData(sqlstr,Msg1)==0)
            {
                flag1 = 1;
            }
        }
    }
    mysql->CloseMysqlconn();
    delete(mysql);
    strResult.clear();
    //release
    sheet.ReleaseDispatch();
    sheets.ReleaseDispatch();
    book.Close(covOptional,COleVariant(strPath),covOptional);
    books.Close();
    app.Quit();
    //CoUninitialize();
}
//导出
void ExcelToDS::OnBnClickedBtnBrows2()
{
    ::CoInitialize(NULL);
    CFileDialog dlg(FALSE,NULL,NULL,OFN_HIDEREADONLY,_T("excel(*.xls)|*.xls|txt(*.txt)|*.txt|All Filter(*.*)|*.*||"));
    if (IDOK != dlg.DoModal())
    {
        return ;
    }
    CString strPath = dlg.GetPathName();//得到文件路径 
    GetDlgItem(IDC_EDIT_EXPORT_PATH)->SetWindowTextW(strPath);
    Mysql *vsmysql = new Mysql();
    char *Msg=""; char *Msg1 = "";
    vsmysql->Connmysql(HOST,PORT,DBNAME,USER,PASSWD,CHARSET,Msg);
    int index = m_CmbExport.GetCurSel();
    CString str;
     m_CmbExport.GetLBText(index,str);
    char *sql = ts.tostring(str);
    if(strcmp(sql,"学生")==0)
    {
        strResultNum.first = 1;
        strResultNum.second = 12;
        strResult[P(1,1)] = _T("学号\0");
        strResult[P(1,2)] = _T("姓名\0");
        strResult[P(1,3)] = _T("班级\0");
        strResult[P(1,4)] = _T("学院\0");

        char  sqlstr[1005] = "select * from tb_student";
        string res = vsmysql->SelectData(sqlstr,12,Msg1);
        int len1 = res.length();
        int p = 2; int q = 1;
        string temp = "";
        for(int i=0;i<len1;i++)
        {
            if(res[i]=='@')
            {
                q = 1;
                p ++;
                strResultNum.first ++;
            }
            else if(res[i]=='$')
            {
                strResult[P(p,q)] = ts.toLPCT(temp);
                q ++;
                temp = "";
            }
            else
            {
                temp += res[i];
            }
        }
        flag2 = 1;
    }

    vsmysql->CloseMysqlconn();
    delete(vsmysql);

    CString fname = dlg.GetPathName();

    //define the objects
    CApplication objApp;
    CWorkbooks objBooks;
    CWorkbook objBook;
    CWorksheets objSheets;
    CWorksheet objSheet;
    CRange objRange;
    CFont0 font;

    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
    COleVariant covTrue((short)TRUE),
                covFalse((short)FALSE),
                varFormat((short)-4143),
                varCenter((short)-4108),
                varLeft((short)-4131),
                varText(_T("TEXT"),VT_BSTR);

    //creat excel
    objApp.m_bAutoRelease = TRUE;
    if (!objApp.CreateDispatch(_T("Excel.Application")))
    {
        AfxMessageBox(_T("Failed to connect to excel!"));
        return ;
    }

    //get Workbooks
    objBooks = objApp.get_Workbooks();

    //open excel file
    objBook.AttachDispatch(objBooks.Add(_variant_t("")));

    //get worksheets
    objSheets = objBook.get_Worksheets();

    //get worksheet
    objSheet = objSheets.get_Item((_variant_t)short(1));

    //set worksheet name
    CString sheetname = _T("sheetname");
    objSheet.put_Name(sheetname);

    CBorders border;

    objRange.AttachDispatch(objSheet.get_Cells(),TRUE);//获取单元格
    objRange.put_NumberFormat(COleVariant(L"@")); //将单元格设置为文本类型

    //write to cells
    CString s1;
    CString s;
    pair<long,long> sNum;
    CRange objRange1;
    int row,col;
    CString strRow;
    CString strName = fname;
    char cCell;
    for (row = 1;row <= strResultNum.first;row++)
    {
        for (col = 1;col <= strResultNum.second;col++)
        {
            //get the unit head
            cCell = 'A' + col - 1;
            strName.Format(_T("%c"),cCell);
            strRow.Format(_T("%d"),row);
            strName += strRow;

            s1.Format(strName);
            objRange1 = objSheet.get_Range(_variant_t(s1),_variant_t(s1));


            //get the unit value
            sNum.first = row;
            sNum.second = col;
            s = strResult[sNum];
            objRange1.put_FormulaR1C1(_variant_t(s));

            objRange1.AttachDispatch(objSheet.get_UsedRange());//获取已使用的单元格
            border.AttachDispatch(objRange1.get_Borders());//加边框
            border.put_LineStyle(_variant_t((long)1)); //设置线型
        }

    }
    //设置字体与字号
    font.AttachDispatch(objRange1.get_Font());                       //获得单元格字体

    font.put_Size(_variant_t((long)10));                             //设置字号

    //设置标题的样式
    objRange1.AttachDispatch(objSheet.get_Range(_variant_t("A1"),_variant_t("Z1")));   //获取范围


    font.AttachDispatch(objRange1.get_Font());   


    font.put_Bold(_variant_t((long)2));//粗体

    font.put_Size(_variant_t((long)11));//设置标题字号11号

    font.put_ColorIndex(_variant_t((long)5));   //设置标题字体颜色为蓝色



    objRange1.AttachDispatch(objSheet.get_Columns());                 //获得调整列

    objRange1.AutoFit();                                          //设置单元格自动调整




    //save
    objBook.SaveAs(_variant_t(fname),varFormat,covOptional,covOptional,covOptional,covOptional,0,
                    covOptional,covOptional,covOptional,covOptional,covOptional);

    //release
    objApp.Quit();
    objRange.ReleaseDispatch();
    objSheet.ReleaseDispatch();
    objSheets.ReleaseDispatch();
    objBook.ReleaseDispatch();
    objBooks.ReleaseDispatch();
 //在文本控件里显示选择的路径
}