Gembox not always parsing excel file properly

Question

0.00/5 (No votes)

See more:

I am attempting to import an excel file (that was exported from MS Project) into a datatable so I can then process it into a database for reporting purposes. Every once in a while the exported file will not be fully extracted to the datatable. With the last file I get the forst 6 columns out of 12. If I open the file in Excel and double-click the heading of the last column so it auto-fits the data, then re-save the file (either to the same name or to a new name) it will then import fine.

Has anyone else run into this and know of a way to programmatically get past it so the users do not have to open and re-save the file? The code I am using to do the extraction is below.

C#

                fileTable = Path.GetFileName(txtPathFileName.Text);
                string myPath = Path.GetFullPath(txtPathFileName.Text);

                SpreadsheetInfo.SetLicense(AppHelper.GemBoxLicense());

                var excelFile = new ExcelFile();
                if (openFileDialog1.FileName.ToLower().EndsWith(".xlsx"))
                    excelFile.LoadXlsx(openFileDialog1.FileName, XlsxOptions.None);
                else
                    excelFile.LoadXls(fileTable);

                if (excelFile.Worksheets.Count > 0)
                {
                    var workSheet = excelFile.Worksheets[0];
                    var colNames = from col in workSheet.Columns.Cast<ExcelColumn>()
                                   where col.Cells[0].Value != null
                                   select col.Cells[0].Value.ToString();

                    foreach (string colName in colNames)
                    {
                        dt.Columns.Add(colName, typeof(string));
                    }

                    // this event converts integer data to string since gembox does not do this automatically
                    workSheet.ExtractDataEvent += (mySender, ex) =>
                    {
                        if (ex.ErrorID == ExtractDataError.WrongType)
                        {
                            ex.DataTableValue = ex.ExcelValue == null ? null : ex.ExcelValue.ToString();
                            ex.Action = ExtractDataEventAction.Continue;
                        }
                    };

                    workSheet.ExtractToDataTable(dt, workSheet.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow,
                        workSheet.Rows[1], workSheet.Columns[0]);

//........ much processing occurs here. ;-)

Thank you,

Don

Oh, PS NEEDZ TEH CODEZ QUICKZ!!!! :-)

Posted 10-Apr-12 8:56am

drolfson

Updated 10-Apr-12 10:04am

v2

Add a Solution

Comments

Mario Z 26-Apr-15 1:31am

As macsys mentioned the problem is with columns, see the remarks of ExcelColumnCollection[^].
In short you need to use CalculateMaxUsedColumns method.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2012-04-10T10:40:00

Solution 1

You're going to have to contact GemBox Software on this one. It is extremely unlikely the few people here who may have used this library will ever see your post.

There's a support link on their site.

Posted 10-Apr-12 10:40am

Dave Kreskowiak

macsys · Answer 2 · 2012-09-27T20:33:00

HI,

you have to manually count the columns as the column.Count does not always provide the correct answer. A simple ways is to do it like

C#

ExcelWorksheet worksheet = excelFile.Worksheets[0];

for (cols = 0; cols < worksheet.Rows[0].AllocatedCells.Count; cols++)
{
    if (firstRowHasHeaders)
    {
        if (worksheet.Rows[0].Cells[cols].Value != null)
        ColCount ++;
    }

}