Gembox并不总是正确解析excel文件 [英] Gembox not always parsing excel file properly
问题描述
我试图将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!!!! :-)
推荐答案
您将必须与此联系GemBox Software. 可能使用此库的少数人很少会看到您的帖子.
他们的网站上有一个支持链接.
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.
HI,
您必须手动将列计为column.Count并不总是提供正确的答案.一种简单的方法是像
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 ++;
}
}
这篇关于Gembox并不总是正确解析excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!