Click here to Skip to main content
13,044,282 members (123,687 online)
Rate this:
Please Sign up or sign in to vote.
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.

                fileTable = Path.GetFileName(txtPathFileName.Text);
                string myPath = Path.GetFullPath(txtPathFileName.Text);
                var excelFile = new ExcelFile();
                if (openFileDialog1.FileName.ToLower().EndsWith(".xlsx"))
                    excelFile.LoadXlsx(openFileDialog1.FileName, XlsxOptions.None);
                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,


Posted 10-Apr-12 8:56am
Updated 10-Apr-12 10:04am
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.
Rate this: bad
Please Sign up or sign in to vote.

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.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2


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

        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 ++;

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170713.1 | Last Updated 28 Sep 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100