笔记-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.导出操作函数ExporExcel

private:System::Void ExporExcel(String^FileName)
{
    //空数据
    if (this->dataGridView1->Rows->Count == 0 || this->dataGridView1->Columns->Count == 0)
    {
        System::Windows::Forms::MessageBox::Show("数据为空", "错误", System::Windows::Forms::MessageBoxButtons::OK,
            System::Windows::Forms::MessageBoxIcon::Error);
        return;
    }
    //Create a new Excel application
    mExcel::Application^ exApp = gcnew mExcel::ApplicationClass();
    exApp->DisplayAlerts = false;
    exApp->Visible = false;
    //新建默认只有一个sheet的工作表，
    Workbook^   exWb = exApp->Workbooks->Add(XlWBATemplate::xlWBATWorksheet);
    if(1)//datatable方式
    {
        //保存标定数据
        if (this->dataGridView1->Rows->Count != 0 && this->dataGridView1->Columns->Count != 0)
        {
            Worksheet^  exWs1 = safe_cast<Worksheet^>(exApp->Worksheets[1]);
            //exWs1->Name = "COD标定数据";
            //创建列
            System::Data::DataColumn^column;
            //创建行
            System::Data::DataRow^rows;
            写入表体数据；将DataTable数据存入数组矩阵
            array<String^, 2>^ arrBody = gcnew array<String^, 2>(this->mDataTable->Rows->Count, this->mDataTable->Columns->Count);
            for (int i = 0; i < this->mDataTable->Rows->Count; i++)//行
            {
                for (int j = 0; j < this->mDataTable->Columns->Count; j++)//列
                {
                    arrBody[i, j] = this->mDataTable->Rows[i][j]->ToString();
                }
            }
            exWs1->Range[exWs1->Cells[1, 1], exWs1->Cells[this->mDataTable->Rows->Count, this->mDataTable->Columns->Count]]->Value2 = arrBody;
            {
                    //添加边框；
                    exWs1->Range[exWs1->Cells[1, 1], exWs1->Cells[this->mDataTable->Rows->Count + 1, this->mDataTable->Columns->Count]]->Borders->LineStyle = mExcel::XlLineStyle::xlContinuous;
                    //行自适应
                    safe_cast<Range^>(exWs1->Columns)->AutoFit();
            }
        }

    }
        //保存Excel文件
        exWb->SaveAs(FileName, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, mExcel::XlSaveAsAccessMode::xlNoChange, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing);
        //exWs1->SaveAs(FileName, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing);
        exApp->Quit();
        GC::Collect();
    }

3.保存Excel文件

private: System::Void toolStripButton2_Click(System::Object^  sender, System::EventArgs^  e) 
{
    this->saveFileDialog1->FileName = System::DateTime::Now.ToString("yyyy-MM-dd_HH-mm-ss");
    this->saveFileDialog1->Filter = "XLSX 表格|*.xlsx|XLS 表格|*.xls";
    this->saveFileDialog1->DefaultExt = "xlsx";
    if (this->saveFileDialog1->ShowDialog() == System::Windows::Forms::DialogResult::OK)
    {
        try
        {
            ExporExcel(this->saveFileDialog1->FileName);//导出excel
            System::Windows::Forms::MessageBox::Show("导出成功", "提示", System::Windows::Forms::MessageBoxButtons::OK,
                System::Windows::Forms::MessageBoxIcon::Information);
        }

        catch (Exception^ e)
        {
            System::Windows::Forms::MessageBox::Show("导出失败", "错误", System::Windows::Forms::MessageBoxButtons::OK,
                System::Windows::Forms::MessageBoxIcon::Error);
        }

    }

}

4.运行效果