Return value of findstring if not found

aatjan2 · September 27, 2006, 7:20am

Hi,

I try to find a member of ColumnNamelist arry in the first row of a spreadsheet with the following code

If all the members are found the sheet is well formed (apart from other errors). the following code is foreseen:-

Dim ColumnNameList As String() = {"Client_naam", "Client_adres_straat", "internnr", "Client_adres_huisnummer", "Client_adres_pcd", "Client_adres_wpl", "Client_huisarts", "Client_geslacht", "Client_geboortedatum"}

For ArrIndex = 0 To ColumnNameList.Length - 1

Dim cell As Cell = MyWorkSheet.Cells.FindString(ColumnNameList(ArrIndex), Nothing, cellArea, False)

IF Cell NOT found Then <<<<?????

ExcelSheetError = True

Else

NameFound = True

End If

Many thanks

Aat Jan

After sending this mail i did a test:- in the above mentioned array i introduced an extra element: "notexisting"

MySheet.Cells etc crashes on this element. that does not matter if i can trap this error ...

amjad.sahi · September 27, 2006, 11:17am

Hi Jan,

Thanks for considering Aspose.

Well, Cells.FindString() method finds string. It's an overloaded method, one of its version can find string in cellarea. I am not fully clear about your need. As far as I could understand, you want that if the area contains all the elements of the array, then the sheet will be well formed other wise not well formed. I created an excel file named findstring.xls, input the array elements in some of cells of the first row of the first sheet and tested. It works fine.You can omit sum elements of array in the sheet and test too.

I adjusted your code below you may check:

Dim ArrIndex As Integer

Dim ExcelSheetError As Boolean

Dim NameFound As Boolean

Dim wb As Workbook = New Workbook()

wb.Open("d:\findstring.xls")

Dim MyWorkSheet As Worksheet = wb.Worksheets(0)

Dim cellArea As CellArea

cellArea.StartRow = 0

cellArea.StartColumn = 0

cellArea.EndRow = 0

cellArea.EndColumn = 23

Dim ColumnNameList As String() = {"Client_naam", "Client_adres_straat", "internnr", "Client_adres_huisnummer", "Client_adres_pcd", "Client_adres_wpl", "Client_huisarts", "Client_geslacht", "Client_geboortedatum"}

For ArrIndex = 0 To ColumnNameList.Length - 1

Dim cell As Cell = MyWorkSheet.Cells.FindString(ColumnNameList(ArrIndex), Nothing, cellArea, False)

If cell Is Nothing Then

ExcelSheetError = True

NameFound = False

Exit For

Else

NameFound = True

End If

MsgBox("NameFound: " & NameFound)

If NameFound = True Then

MsgBox("Well Formed Sheet")

Else

MsgBox("Not a Well Formed Sheet")

End If

If you still have some querie(s), elaborate you question and send us your template file with sample source code, we will try to resolve it ASAP.

Regards

Amjad Sahi

Aspose Nanjing Team

Laurence · September 27, 2006, 5:11pm

FindString will return null(Nothing) if no corresponding cell is found. I will update the docs to describe this issue more clearly. Thank you.

aatjan2 · September 28, 2006, 1:38am

You did fully understand my need!

Now i have still one problem. My sheet contains a column with a birthdate. The format in the sheet is "general". A typical example of the content is like 24031989.

The sheet needs to be imported in an sqlserver table.

I tried the following code:-

If NewCell <> "0" Then

Dim MyDateStr As String = Right(NewCell, 2) + "-"+ Mid(NewCell, 5, 2) + "-" + Left(NewCell, 4)

Dim MyDate As Date = CType(MyDateStr, Date)

MyWorkSheet.Cells(I, DatumColumn).PutValue(MyDate)

Else

NewCell = ""

MyWorkSheet.Cells(I, DatumColumn).PutValue(NewCell)

End If

However, when importing the result in SQL server it still complains about the format and it throws an exception. What do you think?

Laurence · September 28, 2006, 1:55am

Please check Numbers Format. You should format this column as date.

aatjan2 · September 28, 2006, 5:05am

Hi Laurence

This did not helped. What did was to

copy the header (row 0) to a new spreadsheet.
Then a format of the date column in excel.
And then a copy of rows (row 1 -> row max) of the old spreadsheet.

This worked. A test with DTS (data transformation services) proofed that. So the workaround could be doing this by Aspose.Cells

Do you agree?

Aat jan

aatjan2 · September 28, 2006, 1:06pm

Hi, this did not work.

What works is (without the help of Aspose.Cells, so manually):-

make a new excel sheet
copy all the rows to the new one
import this via DTS

What not works (so with Aspose.Cells):-

im MyWorkBook2 As Workbook = New Workbook

MyWorkBook2.Open(pathSchijf)

' spreadsheet is nu geopend

Dim MyWorkSheet2 As Worksheet = MyWorkBook2.Worksheets(0)

'b. opvragen aantal rijen en kolommen (RowMax, ColMax)

Dim MaxNoCol2 As Integer = MyWorkSheet.Cells.Columns.Count

Dim NoColToCopy = MaxNoCol2 +1

Dim MaxNoRow2 As Integer = MyWorkSheet.Cells.Rows.Count

Dim dt As DataTable = MyWorkSheet2.Cells.ExportDataTableAsString(1, 0, MaxNoRow2, NoColToCopy)

'nu de inhoud van

Dim ClientMaster As Workbook = New Workbook

ClientMaster.Open("c:\inetpub\wwwroot\apoonline3\ClientMaster.xls")

Dim NewWorksheet As Worksheet = ClientMaster.Worksheets(0)

' nu een copy van row 1 tot RowNoMax en van colomn 0 tot ColNoMax in een

' dataset

NewWorksheet.Cells.ImportDataTable(dt, False, 1, 0)

pathSchijf= "C:\" + CType(ApotheekParent, String) + "\klantenupload\" + "ClientMaster.xls"

ClientMaster.Save(pathSchijf)

DTS keeps complaining about about the format of a file column in the resultfile, ClientMaster.xls: the column Client_geboortedatum. Further there is in the resultfile a problem with internnr.

I'm at the end of my knowledge. It would be fantastic if you could help me

I have attached for you convenience both files. The one that is read in the import datatable and the result (ClientMaster.xls). I'm at the end of my knowledge. It would be fantastic if you could help me

With regards, Aat Jan

Laurence · September 28, 2006, 5:43pm

Are you trying to export data from an Excel file to Sql Server through DTS? If yes, I think you'd better to use Cells.ExportDataTable method to retrieve data from an Excel file and populate this DataTable into Sql Server.

I don't find your attached files.

aatjan2 · September 28, 2006, 11:23pm

DTS is just a way of testing. at the end it should be handled by a tsql query. Needs more to be done with the data. I attached the files now (did it also earlier).

Laurence · September 29, 2006, 12:36am

Is the data type of birthdate in sql server smalldatetime? Range of smalldatetime is January 1, 1900, through June 6, 2079.

In your file, some birthdate is 1800-01-01. I think this is out of range which caused your problem.

Laurence · September 29, 2006, 12:58am

This may be not the cause of problem for two files all contain these birthdate but one is fine but another failed.

But I still think it's better to remove those birthdate with 01-01-1800.

Could you please post your test project here? That can help me the figure out what caused your problem.

Laurence · September 29, 2006, 1:03am

How do you put data into sql server? What happens if you use Cells.ExportDataTable to export data in Excel file into a DataTable then put it into the sql server?

aatjan2 · September 29, 2006, 1:25am

No, just datetime. I shall change it to 1900-01-01

aatjan2 · September 29, 2006, 1:28am

Did not change anything.

Laurence · September 29, 2006, 1:33am

Could you debug into your program to see which line of data caused this problem?

aatjan2 · September 29, 2006, 1:44am

Latest status:-

I saved the ExcelSheet as a txt file, Tab delimited. Then i imported this file in a new excelfile via Import External data and defined the problem column as date. Saved it and runned the DTS task again. This was processed without any problems.

Does this give you clues enough? How to get rid with this problem via Aspose.Cells?

Many thanks for you help

aatjan2 · September 29, 2006, 1:55am

There is no coding in Asp.net which failes. It is the DTS task which failes, so the content of the produced cells is not ok. That was proofed by the last test.

I do not agree with your finding that one spreadsheet is ok. None of the sheets can be imported by DTS (btw: both are in Excel 2003 format)

The coding which produces the sheet is:

If NameFound = True Then

For I = 1 To MaxNoRow - 1

Dim CellToChange = MyWorkSheet.Cells(I, DatumColumn)

Dim NewCell As String = CellToChange.StringValue

Dim MyDateStr As String = CType(NewCell, String)

If MyDateStr <> "0" Then

' komt binnen als YYYYMMDD

' Sql server wil het zientals 28-9-2006

' Aspose ondersteund m/d/yy

MyDateStr = Left(MyDateStr, 4) + "-" + Mid(MyDateStr, 5, 2) + "-" + Right(MyDateStr, 2)

' 'Dim MyDateStrAs String = Mid(NewCell, 5, 2) + "/" + Right(NewCell, 2) + "/" + Mid(NewCell, 3, 2)

'MyWorkSheet.Cells(I, DatumColumn).Style.Custom("yyyy-mm-dd")

'Dim Message As String = MyDateStr + " "

'Me.Page.Response.Write(Message)

'MyWorkSheet.Cells(I, DatumColumn).Style.Number = 14

Else

MyDateStr = "1900-01-01"

''Dim MyDate As Date = CType(NewCell, Date)

''MyWorkSheet.Cells(I, DatumColumn).Style.Number = 14

''MyWorkSheet.Cells(I, DatumColumn).PutValue(NewCell)

End If

MyWorkSheet.Cells(I, DatumColumn).PutValue(MyDateStr)

MyWorkBook.Save(pathSchijf)

Dim MyWorkBook2 As Workbook = New Workbook

MyWorkBook2.Open(pathSchijf)

' spreadsheet is nu geopend

Dim MyWorkSheet2 As Worksheet = MyWorkBook2.Worksheets(0)

'b. opvragen aantal rijen en kolommen (RowMax, ColMax)

Dim MaxNoCol2 As Integer = MyWorkSheet.Cells.Columns.Count

Dim NoColToCopy = MaxNoCol2 +1

Dim MaxNoRow2 As Integer = MyWorkSheet.Cells.Rows.Count

Dim dt As DataTable = MyWorkSheet2.Cells.ExportDataTableAsString(1, 0, MaxNoRow2, NoColToCopy)

'nu de inhoud van

Dim ClientMaster As Workbook = New Workbook

ClientMaster.Open("c:\inetpub\wwwroot\apoonline3\ClientMaster.xls")

Dim NewWorksheet As Worksheet = ClientMaster.Worksheets(0)

' nu een copy van row 1 tot RowNoMax en van colomn 0 tot ColNoMax in een

' dataset

NewWorksheet.Cells.ImportDataTable(dt, False, 1, 0)

pathSchijf= "C:\" + CType(ApotheekParent, String) + "\klantenupload\" + "ClientMaster.xls"

ClientMaster.Save(pathSchijf)

Literal3.Text = "bestand is opgeslagen"

Laurence · September 29, 2006, 1:59am

No clues.

What happens if you don't use DTS? I don't find any difference with your two files. So I think maybe you can try another approach to export data into sql server.

Aspose.Cells created file has a little difference with MS Excel created file. MS Excel can handle this difference well. However, I think DTS cannot handle it. You can try to use my suggested approach to put data into sql server.

aatjan2 · September 29, 2006, 2:03am

I tried to read the files with an TSQL query. This query is crashing on the date column:

SqlException: Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +742
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +195
ApoOnline3.PanelControls.UploadHistory.UploadHistoryTableControl.HandleRecordButton_Click(Object sender, EventArgs e) +1578
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e) +108
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +138
System.Web.UI.Page.ProcessRequestMain() +1292

Laurence · September 29, 2006, 2:15am

Aspose.Cells created files does have problems to work with DTS or TSQL.

After saving the files, you can use the following approach to retrieve data from file into a DataTable.

dim wb as Workbook = new Workbook()

wb.Open("c:\clientmaster.xls")

dim cells as Cells = wb.Worksheets(0).Cells

dim dt as DataTable = cells.ExportDataTableAsString(1, 0, cells.MaxDataRow - 1, cells.MaxColumn)

Then you can use your own code to put this DataTable into sql server.