when I export the data from spreadsheet to datatable it retursn few junk values.
Does anyone has any idea about this.???
Hi,
Thanks for considering Aspose.
It might be possible that some cells in rows/columns are hidden and those junk values comes from these cells. Please check and make sure about it. If the problem still persists, could you post your template file with sample code here to show the issue.
Thank you.
there are no hidden rows…
workbook.Open(“C:\mi.xls”)
Dim worksheet As worksheet = workbook.Worksheets(0)
<br>Dim dataTable As dataTable = New dataTable()
<br>dataTable = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxRow + 1, worksheet.Cells.MaxColumn + 1)<br>DataTable
DataGrid1.DataSource = dataTable
<br>DataGrid1.ShowHeader = False
<br>DataGrid1.DataBind()<br><br>this is the code.... <br>the datatypes are not consistent throughout the excel sheet. <br>probably that's the reason for getting few junk values.... <br><br>but then again .. that's the reason why we have choosen Aspose. <br><br>any help will be greatly appreciated<br><br>thank you...<br>If it is necessary for the template then I will send you the template... <br><br>and how to send a template file... <br><br><br>Hi,
Yes it will be better if you could post your template file. It will help us to sort out the things. For attaching your template file here, Zip your template file, click "Options" tab, click Add / Remove button to browse to select the file and save it.
Thank you.
I could't find any options tab here....
But I can't wait for you reply and then send you other mail ... I am attaching the zip file and here is the link for that...
http://www.sendspace.com/file/rg4m5k
If you check cells "CF29" and "CF31"....
this is where we are getting junk values....
and also is there any particular way of gettting the cell values...
I mean we can set values into the cells using ..
cells("CF29).setvalue("name")
is there anything similar to that .. (like getvalue())
Which version of Aspose.Cells are you using? If you are using an old version of Aspose.Cells and it's an evaluation copy, dummy values may be injected. However, in the latest version, this limitation is removed.
If you still cannot figure out the problem, please upload your file here.
When you reply here, you can see "Compose", "Option", "Related", "Preview" tab. If you don't see them, drag your vertical scroll bar to the top of page.
To get a cell value, you can use Cell.Value, Cell.StringValue, Cell.DoubleValue properties.
Hi,
Well, I checked your template file downloading from the link you provided. I checked cells "CF29" and "CF31", I noticed the related cells values in the exported datatable, these are 38910 and 38917. I think you are talking about these values as junk / extra values. Well, these values are actually the resultant values of the formulas attached to the cells, both cells "CF29" and "CF31" have formulas in them e.g ., cell "CF29" has the formula like i.e., ...........=IF(AV29="h",P29 + (BA29/24),P29+BA29) and if you calculate this formula it will give you result 38910. Similarly the cell "CF31" has a formula in it and if you calculate the formula in MS Excel, it will give you 38917 value.
if you want to get the formula string of a cell, say cell "CF29", you may write code like:
Dim workbook As Workbook = New Workbook()
workbook.Open("d:\test\mi.xls")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim cells As Cells = worksheet.Cells
Dim fstring As String = cells("CF29").Formula
..........
and if you want to get the resultant value of a cell, say cell "CF29", you may write code like:
Dim workbook As Workbook = New Workbook()
workbook.Open("d:\test\mi.xls")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim cells As Cells = worksheet.Cells
Dim v As Integer = cells("CF29").IntValue
..........
Thank you.
Well but in Excel sheet you have dates in those particular cells… and I need those dates… to be present in the datatable.
and If I used cells(“CF29”).IntValue I get 38910 but not the date right???
so what should I do now???
If I write cells(“CF29”).datetimeValue , I am getting it…
but can’t we get it in datatable…
and also … just like we open the excel sheet with workbook object
workbook.ope(“d:\test\mi.xls”)
is there anythign like closing the workbook…
I checked the api I couldn’t find any…
The reason why I am asking this is … if I open the same excel sheet in two diff functions… .with two diff workbook objects… does it give me an error… because we are not closing the workbook before open it again… (b’coz we get an error when we open the excel sheet and run the program … it says … another application is using …)
so do we get similar kind of error…
I mean if we write … .the code something like…
Dim workbook As workbook = New workbook()
Dim workbook1 As workbook = New workbook()
workbook.Open(“C:\mi.xls”)
workbook1.Open(“C:\mi.xls”)
I tried this on my local system … and I don’t have any prblm but sometimes these kind of things work … eventhough thata not the right way to do… If once it goes to production and then if it shows a prblm then its a really big task … .
plzz clarify all the above questions…
thank you…
Hi,
Thanks for considering Aspose.
Well, if those particular cells have date values, you should use cells("CF29").DateTimeValue. Aspose.Cells opens the excel files in read-only mode, so you have to make sure that the spreadsheets should not be accessed or opened by any one. When you call Workbook.Save() method all the resouces, data etc. related the workbook object will be null. We totally rely on .Net Garbage Collector to release the resources related objects.
We will further explain you soon.
Thank you.
For your scenario, please try ExportDataTableAsString method.
We use File.OpenRead method to load the file. So you can concurrently open those files without problem.
Hey … thnx for all your help… its working now.
Its working with ExportdatatableAsString().
Hi,
I have the same problem again, I used ExportdatatableAsstring()
when I uploaded the excel sheet it actuallly transfer data to datatable and from datatable to database.
the problem is when I am getting junk values for the calculated fields for few excel sheets and few other excel sheets are working absolutely fine, do you have any idea about this.
please find the file attached.
BP29
BP31
BP33
BP35
BP37
BP39
all these cell values are returning “#NAME?” value but not the exact value.
why is it behaving like this.
but for few files its showing the required value.
Please look into this ASAP.
Thank you,
This problem is caused by MROUND function. Aspose.Cells formula calculation engine doesn’t support this addin function. We will work to support it soon. Hopefully we will make it within 1-2 days.
But why is that its working for few values… and not working for few other…
Hi,
Well, I think for other worksheets cells Aspose.Cells calculation engine might support the formulas used in those values. Any how we will support MROUND function very soon.
Thank you.
I am actually talking about the same worksheet cells.
we upload the same worksheet with different values.
Some worksheets get uploaded fine some doesn’t and more intrestingly the same worksheet which has a problem when we uploaded has actually worked fine when uploaded from a different computer.
I thought probably there are few add-ins to be installed on the computer but everything works on the server so it really doesn’t matter from which computer it gets uploaded…
Let me know if you have any idea.
Thank you,
If you don't install a needed addin in your machine, MS Excel will calculate the formula result as "#NAME?" and save it into the file.
When Aspose.Cells export data from this file, it will also return "#NAME?".
To make the data correct, please:
1. Use MS Excel to open your file and save it again. Please make sure the required add-ins are installed.
2. Call Workbook.CalculateFormula method before exporting data. However, since MROUND is not supported now, Aspose.Cells will also return "#NAME?". After we support this function, all will be fine.