bool WriteExcelFile_OfficeInterop(String^ path, DataSet^ dt,
const std::vector<std::string>& sheetName, bool hideColumnName)
{
// If the file already exists, delete it and then generate file
if (System::IO::File::Exists(path))
{
try
{
System::IO::File::Delete(path);
}
catch (System::Exception^ e)
{
System::Windows::Forms::MessageBox::Show("Delete Excel File Failed, Please Check File Statue.",
"Delete File Error",
System::Windows::Forms::MessageBoxButtons::OK,
System::Windows::Forms::MessageBoxIcon::Error);
return false;
}
}
Excel::Application^ excelApp = nullptr;
Excel::_Workbook^ workbook = nullptr;
Excel::Worksheet^ worksheet = nullptr;
Excel::Range^ range = nullptr;
Excel::Range^ firstRow = nullptr;
Excel::Sort^ sort = nullptr;
bool ret = true;
try
{
//Create a new instance of Excel application
excelApp = gcnew Excel::ApplicationClass();
//Create a new workbook
workbook = excelApp->Workbooks->Add(true);
for (int i = 0; i < dt->Tables->Count; ++i)
{
//Create a new worksheet
worksheet = (Excel::Worksheet^)workbook->Sheets->Add(Type::Missing, workbook->Sheets[workbook->Worksheets->Count], Type::Missing, Excel::XlSheetType::xlWorksheet);
//Set the name of the worksheet
worksheet->Name = StringToSystemString(sheetName[i]);
// headerRowIndex == 0 means that header is hid
int headerRowIndex = 0;
if (hideColumnName == false)
{
// the header will occupy 1 row
headerRowIndex = 1;
//Fill the header row with column names
for (int j = 0; j < dt->Tables[i]->Columns->Count; ++j)
{
worksheet->Cells[1, j + 1] = dt->Tables[i]->Columns[j]->ColumnName;
}
}
//Fill the data rows using range object
int numCols = dt->Tables[i]->Columns->Count;
int numRows = dt->Tables[i]->Rows->Count;
range = worksheet->Range[worksheet->Cells[headerRowIndex + 1, 1],
worksheet->Cells[headerRowIndex + numRows, numCols]];
// 使用二维数组加速写入
array<System::Object^, 2>^ dataArray = gcnew array<System::Object^, 2>(numRows, numCols);
for (int row = 0; row < numRows; ++row)
{
for (int col = 0; col < numCols; ++col)
{
dataArray[row, col] = dt->Tables[i]->Rows->default[row]->default[col];
}
}
range->Value2 = dataArray;
// UsedRange : not empty column
worksheet->UsedRange->Columns->AutoFit();
}
// delete default empty sheet which is created when create a workbook
safe_cast<Excel::Worksheet^>(workbook->Sheets["Sheet1"])->Delete();
// select the first sheet
safe_cast<Excel::_Worksheet^>(workbook->Sheets[1])->Activate();
//Save the workbook
workbook->SaveAs(path, Type::Missing, Type::Missing,
Type::Missing, Type::Missing, Type::Missing, Microsoft::Office::Interop::Excel::XlSaveAsAccessMode::xlNoChange,
Type::Missing, Type::Missing, Type::Missing, Type::Missing, Type::Missing);
//Close the workbook
workbook->Close(Type::Missing, Type::Missing, Type::Missing);
//Quit the Excel application
excelApp->Quit();
}
catch (System::Exception^ e)
{
System::Windows::Forms::MessageBox::Show(e->Message,
"Write File Error",
System::Windows::Forms::MessageBoxButtons::OK,
System::Windows::Forms::MessageBoxIcon::Error);
//Close the workbook and quit the Excel application
if (workbook != nullptr)
{
workbook->Close(Type::Missing, Type::Missing, Type::Missing);
}
if (excelApp != nullptr)
{
excelApp->Quit();
}
ret = false;
}
finally
{
//Release COM objects
if (sort != nullptr)
{
Marshal::ReleaseComObject(sort);
}
if (firstRow != nullptr)
{
Marshal::ReleaseComObject(firstRow);
}
if (range != nullptr)
{
Marshal::ReleaseComObject(range);
}
if (worksheet != nullptr)
{
Marshal::ReleaseComObject(worksheet);
}
if (workbook != nullptr)
{
Marshal::ReleaseComObject(workbook);
}
if (excelApp != nullptr)
{
Marshal::ReleaseComObject(excelApp);
}
}
return ret;
}
C++/CLI使用Office.Interop库创建excel,同时解决写入速度慢的问题
最新推荐文章于 2024-04-25 20:09:15 发布