Hello,
At the end of a report I count all the records returned and display the count in a new row’s first column. It fails if my first column’s previous rows hold non-string data (ie: is integer). The error states it can’t convert an int to string for the new row’s column.
Short of cycling through all the report’s columns trying to find one that doesn’t hold integers, how can I make this work? I need the new row’s columns to ignore the datatypes used in the previous rows. Here is how I’m creating the new row:
’ add a final datarow that displays the number of events returned
Dim rcASPOSE As DataRowCollection
Dim dataRowASPOSE As DataRow
Dim iCntASPOSERows As Integer
rcASPOSE = dtASPOSE.Rows
’get the count of rows in the report
iCntASPOSERows = dtASPOSE.Rows.Count
dataRowASPOSE = dtASPOSE.NewRow
’insert a new row displaying the number of rows returned
dataRowASPOSE(0) = "—End of Data: " & CStr(iCntASPOSERows) & " Events Returned—"
rcASPOSE.Add(dataRowASPOSE)
thank you!
Hi,
How do you put data to Excel object using Aspose.Excel? Please post more of your code here. That will be helpful to investigate this problem.
Hello, here is the Sub I have to export the data to Excel. As you can see, I am adding a new dataRow to the dataTable with data theat reads"—End of Data…". This works fine when I view in a datagrid.
However, if dataRowASPOSE(0) is is a column that holds all integer data, I get an error trying to export this with the new non-integer data appended to the column. The control trys to convert the appended column to int. Is there a way to add a new dataRow where the ASPOSE control doesn’t care about the data types for each column in the new row?
thanks again!
Imports Aspose.Excel
’Class-Level Protected objects for ASPOSE control
Protected dtASPOSE As New DataTable
Protected cmdASPOSE As New SqlCommand
Protected adaptorASPOSE As New SqlDataAdapter
'
’cmdExportExcel_Click
’Summary: Exporting to Excel calls the ASPOSE control which exports the retreived
’ data to a new MS Excel file.
'
Private Sub cmdExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdExportExcel.Click
Dim x As New Export
’ this is needed for liscensing
Dim path As String = MapPath(".")
Dim strExportName As String
path = path.Substring(0, path.LastIndexOf(""))
’ looks for the control license in the \bin directory
Dim licenseFile As String = path + “\bin\Aspose.Excel.lic”
Dim excel As Excel = New Excel(licenseFile, Me)
’ set up the name you want the export file to have (here it incorperates the current date)
strExportName = “MyReport” & Format(Date.Now(), “ddMMMyyyy”) & “.xls”
Me.dtASPOSE.Reset()
’ the source will be the sql string written in txtReportSQL
Me.cmdASPOSE.CommandText = CType(Me.FindControl(“txtReportSQL”), HtmlControls.HtmlInputHidden).Value
’ set the command type to text
Me.cmdASPOSE.CommandType = CommandType.Text
’ set the adaptor’s select command
Me.adaptorASPOSE.SelectCommand = cmdASPOSE
’ establish the database connection
Me.cmdASPOSE.Connection = New SqlConnection(ConfigurationSettings.AppSettings(“myDBConnection”))
’ fill the data table with the adaptor
Me.adaptorASPOSE.Fill(Me.dtASPOSE)
’ add a final datarow that displays the number of events returned
Dim rcASPOSE As DataRowCollection
Dim dataRowASPOSE As DataRow
Dim iCntASPOSERows As Integer
rcASPOSE = dtASPOSE.Rows
iCntASPOSERows = dtASPOSE.Rows.Count
dataRowASPOSE = dtASPOSE.NewRow
dataRowASPOSE(0) = “—End of Data: " & CStr(iCntASPOSERows) & " Events Returned—”
rcASPOSE.Add(dataRowASPOSE)
’ import data to the excel worksheet
Dim sheet As Worksheet = excel.Worksheets(0)
sheet.Cells.ImportDataTable(dtASPOSE, True, 0, 0)
While excel.Worksheets.Count > 1
excel.Worksheets.RemoveAt(excel.Worksheets.Count - 1)
End While
’ clean-up
dtASPOSE.Dispose()
adaptorASPOSE.Dispose()
cmdASPOSE.Connection.Close()
cmdASPOSE.Dispose()
’ open results in MS Excel
excel.Save(strExportName, SaveType.OpenInExcel, FileFormatType.Default, Me.Response)
End Sub
Now I understand your problem. I will investigate it and see if Aspose.Excel can be enhanced to meet you need.
Thanks for your patience.
Thank you very much! Great support!
Hi,
If a column’s data type is integer, it’s impossible to add a new row which contains string in the same column.
In your situation, I suggest to change your code to:
Me.adaptorASPOSE.Fill(Me.dtASPOSE)
’ import data to the excel worksheet
Dim sheet As Worksheet = excel.Worksheets(0)
sheet.Cells.ImportDataTable(dtASPOSE, True, 0, 0)
sheet.Cells(dtASPOSE.Count, 0).PutValue("—End of Data: " & CStr(iCntASPOSERows) & " Events Returned—")
Thank you. Works great; just had to make one minor change to the output line. I needed to get the count of rows and add one to place the output at the end of the report.
sheet.Cells((dtASPOSE.Rows.Count) + 1, 0).PutValue("—End of Data: " & CStr(iCntASPOSERows) & " Events Returned—")