Fast read and write excel file

Question

Fast read and write excel file

SEUNGWOO NAM 1

Hi.
I'm using a library,which is Xlnt library, to read and write excel file at MFC framework application.
Actually, in .NET(C#) framework, I easily use Microsoft.Office.Interop.Excel.Application to read and write excel file. It is very fast.

I have to convert .Net Framework application code to MFC Framework application. Because of conducting some reliability test( C# code makes a lot of extra fee, but C++ code is free).

How can I use something like Microsoft.Office.Interop.Excel.Application at MFC framework? It is performance over Xlnt Library ( 5 times faster ).

My excel file consists of 10,000 rows with 105 colums( but, almost are vacant. I use only 1~5 columns and 43~48 columns and 83~93 columns and 105 column). are there any technique to read and write some column segments to avoid full scanning all cells?

thank you for reading this. I'm looking forward to your answer.

Best regards.

2 answers

Your answer

Answer 1

Castorix31 90,686

How can I use something like Microsoft.Office.Interop.Excel.Application at MFC framework?

With #import to generate interfaces

Foir example for Office 16, I use :

#import "C:\Program Files (x86)\Microsoft Office\root\VFS\ProgramFilesCommonX86\Microsoft Shared\OFFICE16\mso.dll" rename("RGB", "MSRGB") rename("DocumentProperties", "WordDocumentProperties")
#import "C:\Program Files (x86)\Microsoft Office\root\VFS\ProgramFilesCommonX86\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB" raw_interfaces_only, rename("Reference", "ignorethis"), rename("VBE", "testVBE")
#import "C:\Program Files (x86)\Microsoft Office\root\Office16\Excel.exe" exclude("IFont", "IPicture") rename("RGB", "ignorethis"), rename("DialogBox", "ignorethis"), rename("VBE", "testVBE"), rename("ReplaceText", "EReplaceText"), rename("CopyFile","ECopyFile"), rename("FindText", "EFindText"), rename("NoPrompt", "ENoPrompt")

SEUNGWOO NAM 1 Reputation point

2021-01-28T01:55:04.487+00:00
Thank you for your attention!
When I conducted your solution, a lot of compile errors occured.
the details are follow.

<#import files error>

I only conducted two import among three. Because I cannot find the [VBE6EXT.OLB] file at any directory path.

I used [mso.dll], [excel.exe] files, which are located in each "C:\Program Files\Microsoft Office\root\vfs\ProgramFilesCommonX64\Microsoft Shared\OFFICE16\mso.dll" and "C\Program Files\Microsoft Office\root\Office16\Excel.exe", instead of your posted path(Becase I cannot find).

Eventually, I import 1 and 3 path in your solution. But Visual Studio 2010 alerted many errors in excel.tlh and mso.tlh, which are in my project\Debug directory path.

How can I solve above problems?

Should I do some pre-processing process to conduct your solution?

Your solution only can be conducted at x86 application?

How can I get VBE6EXT.OLB file?

Thank you for reading this.
Castorix31 90,686 Reputation points

2021-01-28T05:34:22.99+00:00

As you said you used MFC, you can follow MSDN doc :

How to automate Excel from MFC and Visual C++ 2005 or Visual C++ .NET
Jeanine Zhang-MSFT 11,356 Reputation points Microsoft External Staff

2021-01-28T06:22:28.033+00:00

@SEUNGWOO NAM

How can I get VBE6EXT.OLB file

Please check if you have installed the 64-bits Microsoft Office Applications correctly. I suggest you could refer to the link: https://social.msdn.microsoft.com/Forums/vstudio/en-US/4cec587c-8504-4fe0-b947-c98b29b452ee/looking-for-64bits-vbe6extolb?forum=vcgeneral

But Visual Studio 2010 alerted many errors in excel.tlh and mso.tlh

Could you please provide more details about the errors?
SEUNGWOO NAM 1 Reputation point

2021-01-28T08:45:18.73+00:00

Hi Castorix31!

I tried some method to improve to read and write excel file performance.
Your post looks like the method( the procedure is that first, register excel.exe to class and use CWorkbooks, CWorkbook, CWorksheets, ..., CRange, and CRange::get_Item() ).

It is twice as slower as xlnt library. But I may have missed some progress of the procedure.
I'll try once more.

Thank you so much!
SEUNGWOO NAM 1 Reputation point

2021-01-28T08:58:53.453+00:00
Hi. JeanineZhang!

[How Can I Get VBE6EXT.OLB file]

As you think, I missed some order at the install process. This is because that VBE6EXT.OLB doesn't exist.

[But Visual Studio 2010 alerted many errors in excel.tlh and mso.tlh]

The error's type is syntax error.

Some of the errors are as follows

error C1003: The number of errors exceeded 100. Stop compilation. ...\vbe7.tlh
error C2059: 'a constant' ...\mso.tlh
error C2143: ';' is not before'}'. ... \mso.tlh
error C2225: ';' is not before 'Add'. ...\mso.tlh
error C2504: '_IMsoDispObj': No base class defined. ...\mso.tlh
error C4430: There is no format specifier. Assume int. Note: C++ does not support native int. ...\mso.tlh

Thank you so much!

Answer 2

Petrus 【KIM】 546

Use OpenXML SDK
* https://www.nuget.org/packages/Open-XML-SDK/
* https://download.microsoft.com/download/5/5/3/553C731E-9333-40FB-ADE3-E02DC9643B31/OpenXMLSDKToolV25.msi

AND
Compile MFC with CLR
* https://learn.microsoft.com/en-us/cpp/dotnet/how-to-compile-mfc-and-atl-code-by-using-clr?view=msvc-160

SEUNGWOO NAM 1 Reputation point

2021-01-28T08:59:45.543+00:00

Hi. PetrusKim!

I'll try to your recommendations.

Thank you!

Share via

Fast read and write excel file

2 answers

Your answer