C# 导出为二维表头的EXCEL

#include "StdAfx.h"
#include "ExportData.h"
using namespace System::Windows ::Forms ;
using namespace TDLTESYSTEM;
ExportData::ExportData(DataTable^ dt,String^ filename,String^ title)
{
this->dt=dt;
this->filename =filename;
this->title =title;
ExportToExcel(dt,filename,title);
}
int ExportData::ExportToExcel(DataTable^ dt,String^ filename,String^ title)
{
 try
    {
        int sheetRows = 65535;//设置Sheet的行数,此为最大上限,本来是65536,因表头要占去一行
        int sheetCount = (dt->Rows->Count - 1) / sheetRows + 1;//计算Sheet数
  System::GC::Collect();//垃圾回收

  Excel::Application^ excel;
  Excel::Workbook^ xBk;
  Excel::Worksheet^ xSt = nullptr;
  excel = gcnew Excel::ApplicationClass();
  xBk = excel->Workbooks->Add(true);
  //Excel::_Worksheet ^ss;
  
  //ss->GetType();
        //定义循环中要使用的变量
        int dvRowStart;
        int dvRowEnd;
        int rowIndex = 0;
        int colIndex = 0;
        //对全部Sheet进行操作
        for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
        {
            //初始化Sheet中的变量
            rowIndex = 1;
            colIndex = 1;
            //计算起始行
            dvRowStart = sheetIndex * sheetRows;
            dvRowEnd = dvRowStart + sheetRows - 1;
            if (dvRowEnd > dt->Rows->Count - 1)
            {
                dvRowEnd = dt->Rows->Count - 1;
            }
            //创建一个Sheet
            if (nullptr == xSt)
            {
    xSt = (Excel::Worksheet ^)xBk->Worksheets->Add(System::Type::Missing, System::Type::Missing, 1, System::Type::Missing);
            }
            else
            {
    xSt = (Excel::Worksheet^)xBk->Worksheets->Add(System::Type::Missing, xSt, 1, System::Type::Missing);
            }
            //设置Sheet的名称
            xSt->Name = title;
            if (sheetCount > 1)
            {
    xSt->Name += System::Convert ::ToString ((int)(sheetIndex + 1));
            }
            //取得标题
 
    //合并单元格
   System::Object ^ cell1=excel->Cells [rowIndex,colIndex];
   System::Object ^ cell2=excel->Cells [rowIndex,dt->Columns ->Count ];
   Microsoft::Office ::Interop ::Excel ::Range ^ range=(Microsoft::Office ::Interop ::Excel ::Range ^)xSt->Range [cell1,cell2];
   range->MergeCells =true;
   //一级表头
   excel->Cells [rowIndex,colIndex]=title;
   excel->ActiveCell ->Font ->Size =20;
   excel->ActiveCell ->HorizontalAlignment =Microsoft::Office ::Interop ::Excel ::Constants ::xlCenter ;//居中

   //二级表头
   for(int i =0;i<dt->Columns->Count;i++)
   {
                excel->Cells[rowIndex+1, colIndex++] = dt->Columns[i]->ColumnName;
            }
            //取得表格中数据
            int drvIndex;
            for (drvIndex = dvRowStart; drvIndex <= dvRowEnd; drvIndex++)
            {
    DataRow^ row = dt->Rows[drvIndex];
                //新起一行，当前单元格移至行首
                rowIndex++;
                colIndex = 1;
                for(int i =0;i<dt->Columns->Count;i++)
                {
     if (dt->Columns[i]->DataType == System::Type::GetType("System.DateTime"))
                    {
      excel->Cells[rowIndex+1, colIndex] = System::Convert::ToString(row[dt->Columns[i]->ColumnName]);//time2;
                    }
                    else if (dt->Columns[i]->DataType == System::Type::GetType("System.String"))
                    {
                        excel->Cells[rowIndex+1, colIndex] = row[dt->Columns[i]->ColumnName]->ToString();
                    }
                    else
                    {
                        excel->Cells[rowIndex+1, colIndex] = row[dt->Columns[i]->ColumnName]->ToString();
                    }
                    colIndex++;
                }
            }
        xBk->SaveCopyAs(filename);
        xBk->Close(false, nullptr, nullptr);
        excel->Quit();

  System::Runtime::InteropServices::Marshal::ReleaseComObject(xBk);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(excel);
        System::Runtime::InteropServices::Marshal::ReleaseComObject(xSt);

        xBk = nullptr;
        excel = nullptr;
        xSt = nullptr;
  System::GC::Collect();
        //返回写入服务器Excel文件的路径
        return 1;
    }
    catch(Exception^ ex)
    {
  MessageBox::Show(ex->ToString());
        return -1;
    }
   
}

转载于:https://www.cnblogs.com/fmlblog/p/3188095.html