Gembox并不总是正确解析excel文件

问题描述

我试图将Excel文件(从MS Project导出)导入数据表，以便随后将其处理到数据库中以进行报告.每隔一段时间，导出的文件将不会完全提取到数据表中.对于最后一个文件，我从第12列中获取了第6列.如果我在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. ;-)

谢谢

唐

哦，PS NEEDZ TEH CODEZ QUICKZ ！！！ :-)

Thank you,

Don

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

推荐答案

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

}

这篇关于Gembox并不总是正确解析excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！