Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: C#Excel
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
Edited 10-Apr-12 10:04am
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
Your Filters
0 Shai Vashdi 576
1 OriginalGriff 183
2 Tadit Dash 160
3 Sergey Alexandrovich Kryukov 136
4 praveen_07 115
0 Sergey Alexandrovich Kryukov 9,280
1 OriginalGriff 5,473
2 Peter Leow 4,100
3 Maciej Los 3,540
4 Abhinav S 3,333

Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 28 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid