Click here to Skip to main content
11,705,617 members (54,515 online)
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
drolfson2.1K
Edited 10-Apr-12 10:04am
v2
Comments
Mario Z at 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
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
0 OriginalGriff 213
1 Sergey Alexandrovich Kryukov 170
2 Inimicos 60
3 ProgramFOX 50
4 Richard MacCutchan 45
0 OriginalGriff 9,053
1 Sergey Alexandrovich Kryukov 8,347
2 CPallini 5,189
3 Maciej Los 4,726
4 Mika Wendelius 3,626


Advertise | Privacy | Mobile
Web04 | 2.8.150819.1 | Last Updated 28 Sep 2012
Copyright © CodeProject, 1999-2015
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