笔记-CppCLR_WinForms操作dataGridView导入Excel表格

1.添加Microsoft Excel 16 Object Library

2.添加相关定义及命名空间

using namespace Microsoft::Office::Interop::Excel;
#define mExcel   Microsoft::Office::Interop::Excel

//创建dataset
DataSet ^mDataSet;
//创建datatable
System::Data::DataTable^mDataTable;

3.导入操作函数ImporExcel

private:System::Void ImporExcel(String^FileName)
{
    //1. Create a new Excel application
    mExcel::Application^ exApp = gcnew mExcel::ApplicationClass();
    //打开工作表
    Workbook^   exWb = exApp->Workbooks->Open(FileName, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing,
                Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing);

    exApp->DisplayAlerts = false;
    exApp->Visible = false;//工作表不可见；

    int WorksheetsCount= exApp->Worksheets->Count;

    if (WorksheetsCount < 1)
    {
        exApp->Quit();
        exApp = nullptr;
        GC::Collect();
        return;
    }

    array<String^>^ SheetName = gcnew array<String^>(WorksheetsCount);
    for (int i = 0; i < WorksheetsCount; i++)
    {
        SheetName[i]=safe_cast<Worksheet^>(exApp->Worksheets[i + 1])->Name->ToString();
    }

    Worksheet^  exWs1 = safe_cast<Worksheet^>(exApp->Worksheets[1]);

    this->toolStripStatusLabel2->Text = SheetName[0];

    if (1)
    {
        //读数据
        {
            int Rowcount = exWs1->UsedRange->CurrentRegion->Rows->Count;//行计数
            int Colcount = exWs1->UsedRange->CurrentRegion->Columns->Count;//列计数

            if (Rowcount != 0 && Colcount != 0)
            {
                if (this->dataGridView1->Rows->Count != 0 || this->dataGridView1->Columns->Count != 0)
                {
                    this->dataGridView1->Columns->Clear();
                    this->mDataTable->Rows->Clear();
                    this->mDataTable->Columns->Clear();
                }
            }
            else
            {
                exApp->Quit();
                exApp = nullptr;
                exWs1 = nullptr;
                GC::Collect();
                return;
            }

            //创建列
            System::Data::DataColumn^mDataColumn;
            //创建行
            System::Data::DataRow^mDataRow;

            //添加DataTable列  
            for (int i = 0; i < Colcount; i++)
            {
                mDataColumn = gcnew System::Data::DataColumn();
                this->mDataTable->Columns->Add(mDataColumn);
            }

            //将excel的行单元格数据添加到DataTable行    
            for (int i = 0; i < Rowcount - 1; i++)
            {
                mDataRow = this->mDataTable->NewRow();
                for (int j = 0; j < Colcount; j++)
                {
                    mDataRow[j] = safe_cast<Range^>(exWs1->Cells[i + 1, j + 1])->Text;
                }
                this->mDataTable->Rows->Add(mDataRow);
            }

            System::Windows::Forms::DataGridViewCellStyle^  dataGridViewCellStyle = (gcnew System::Windows::Forms::DataGridViewCellStyle());
            dataGridViewCellStyle->Alignment = System::Windows::Forms::DataGridViewContentAlignment::MiddleCenter;
            for (int j = 0; j < Colcount; j++)
            {
                this->dataGridView1->Columns[j]->DefaultCellStyle = dataGridViewCellStyle;//居中对齐
                this->dataGridView1->Columns[j]->SortMode = System::Windows::Forms::DataGridViewColumnSortMode::NotSortable;//禁止排序
            }
            exWs1 = nullptr;
        }

    }           
        exApp->Quit();
        exApp = nullptr;
        exWs1 = nullptr;
        GC::Collect();

    }

3.打开Excel文件

private: System::Void toolStripButton1_Click_1(System::Object^  sender, System::EventArgs^  e) 
{
    this->openFileDialog1->Filter = "XLSX 表格|*.xlsx|XLS 表格|*.xls";
    this->openFileDialog1->DefaultExt = "xlsx";

    if (this->openFileDialog1->ShowDialog() == System::Windows::Forms::DialogResult::OK)
    {
        try
        {
            ImporExcel(this->openFileDialog1->FileName);//导入数据
        }
        catch (Exception^ e)
        {
            System::Windows::Forms::MessageBox::Show("导入失败", "错误", System::Windows::Forms::MessageBoxButtons::OK,
                System::Windows::Forms::MessageBoxIcon::Error);
        }
    }
}

4.运行效果