Click here to Skip to main content
Rate this: bad
good
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);
 
                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!!!! Smile | :)
Posted 10-Apr-12 8:56am
drolfson1.9K
Edited 10-Apr-12 10:04am
v2
Rate this: bad
good
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.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
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