This is a migrated thread and some comments may be shown as answers.

When exported, it is converted to text i need original format.

12 Answers 141 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

M Kumar asked on 14 Jun 2019, 10:16 AM

hi sir,

Below are the issues raised by warehouse on dynamic report:

Point Issue Expected result

A ------ Column D and column E is merge , This shouldn’t merge as they need to use for reporting purpose(the example show in below screenshoot)

http://prntscr.com/o1rmmc

B ------ When exported, it is converted to text. Please export it out to excel as numbers(like date as date integer as number)(the example show in below screenshoot)

http://prntscr.com/o1rjiv

C ------ Column A with blank value generated Please remove the column. This is not needed(the example show in below screenshoot)

http://prntscr.com/o1rnnp

This three issue rectify soon

12 Answers, 1 is accepted

answered on 19 Jun 2019, 07:53 AM

ValueTypes.png

Hi M Kumar,

A ------ Column D and column E is merge , This shouldn’t merge as they need to use for reporting purpose(the example show in below screenshoot)
Cell merging in Excel export is usually due to mispositioned items in the report. When exporting to Excel, the Reporting engine tries to maintain the items positions. If the report contains a table, make sure its cell contents are aligned with the cell bounds. If the report is a band report, make sure the items are aligned with each other and that the top and height match for all the items in a row.
Check the Design Considerations for Excel Rendering article for more details.

B ------ When exported, it is converted to text. Please export it out to excel as numbers(like date as date integer as number)(the example show in below screenshoot)
If the value of a TextBox is set to a number without '=' (e.g. to '123') it will be interpreted as String. If you need the type to be, for example, Integer, it will be necessary to set is as '=123'. If the value is coming from a data field, it will preserve the type of the field. Check the attached screenshot for clarity.

C ------ Column A with blank value generated Please remove the column. This is not needed(the example show in below screenshoot)
The reporting engine tries to preserve the designed layout of the report. I suspect that the blank cell is there to account for blank space that is set in the report design. If you want to remove the blank cell it will be necessary to remove the blank space in the report definition.

If you need further clarifications you may send us the report definition for local investigation.

Regards,
Todor
Progress Telerik

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items

answered on 21 Jun 2019, 09:57 AM

A ------ Column D and column E is merge , This shouldn’t merge as they need to use for reporting purpose(the example show in below screenshoot)

U say mispositioned in the report where it in the screen shot

http://prntscr.com/o4saj6

B ------ When exported, it is converted to text. Please export it out to excel as numbers(like date as date integer as number)

i use like this

Dim detailsTxt As New Telerik.Reporting.TextBox

detailsTxt.Value = "=Fields.[" + col.ColumnName.ToString & "]"
detail.Items.Add(detailsTxt)

in this how we the set If the value is coming from a data field.

answered on 26 Jun 2019, 07:12 AM

Hi M Kumar,

A ------ U say mispositioned in the report where it in the screen shot
In order to specify which are the mispositioned items we will need the report definition. On the screenshot I cannot see all the items that include cells from the column with the merged cells.

B ------ When exported, it is converted to text. Please export it out to excel as numbers(like date as date integer as number). The value is coming from a data field.
When the value comes from a data field its type will be preserved. If the data is coming as string and you need it converted to other type you may use our built-in Conversion Functions.

Regards,
Todor
Progress Telerik

answered on 28 Jun 2019, 11:02 AM

hi sir,

"B ------ When exported, it is converted to text. Please export it out to excel as numbers(like date as date integer as number). The value is coming from a data field.
When the value comes from a data field its type will be preserved. If the data is coming as string and you need it converted to other type you may use our built-in Conversion Functions."

u reply the above line from that i use the

http://prntscr.com/o7v33b

but when i use the cint it throw the error convertion integer to string

answered on 03 Jul 2019, 10:16 AM

CIntError.png

Hello M Kumar,

There may be a conversion error if the value passed to CInt function is not a valid Integer - check for example the attached screenshot.
If the incoming value type is not known, you may use a Custom User Function to handle the conversion and return the value with a proper type as Object to be displayed in the report.

Regards,
Todor
Progress Telerik

answered on 16 Jul 2019, 10:31 AM

Hi,

A ------ Colum A and merge Column D and column E is merge , This shouldn’t merge as they need to use for reporting purpose the below code is for generating excel in "datas" is a datatable in this we have all value.

the "Dim headerTxt As New Telerik.Reporting.TextBox" is used for header and "Dim detailsTxt As New Telerik.Reporting.TextBox" is used for columns.

Code for using to generate the excel:

For Each col As System.Data.DataColumn In datas.Columns

Dim datas1 As New DataTable

If HttpContext.Current.Session("databaseslt") IsNot Nothing Then

Dim tempstr1 As String = ""
tempstr1 = "select [Type] from [" + HttpContext.Current.Session("databaseslt") + "_position] where LField='" + col.ColumnName.ToString + "'"
DoNotify(tempstr1)
Dim sqlcon1 As New SqlConnection(HttpContext.Current.Session("FTCS"))

Dim sqlcmd1 As New SqlDataAdapter(tempstr1, sqlcon1)
sqlcon1.Open()
sqlcmd1.Fill(datas1)
sqlcon1.Close()
sqlcmd1.Dispose()
DoNotify(datas1.Rows(0)("Type").ToString)
End If

Dim headerTxt As New Telerik.Reporting.TextBox
headerTxt.Size = New SizeU(Unit.Inch(1.05), Unit.Inch(0.2))
headerTxt.Location = New Drawing.PointU(Unit.Inch(iniLeft), Unit.Inch(0.01))
headerTxt.Style.Font.Bold = True
headerTxt.Style.Font.Size = New Unit(10)
headerTxt.Style.VerticalAlign = VerticalAlign.Middle
headerTxt.Style.TextAlign = HorizontalAlign.Left
headerTxt.StyleName = "Caption"
headerTxt.Style.BackgroundColor = Color.FromArgb(121, 167, 227)
headerTxt.Value = col.ColumnName.ToString
headerTxt.CanGrow = True
headerTxt.TextWrap = False
headerTxt.Multiline = True
labelsGroupHeader.Items.Add(headerTxt)
Dim detailsTxt As New Telerik.Reporting.TextBox
detailsTxt.Size = New SizeU(Unit.Inch(1.05), Unit.Inch(0.2))
detailsTxt.Location = New Drawing.PointU(Unit.Inch(iniLeft), Unit.Inch(0.01))
detailsTxt.Style.Font.Size = New Unit(10)

detailsTxt.Style.VerticalAlign = VerticalAlign.Middle
detailsTxt.Style.TextAlign = HorizontalAlign.Left
detailsTxt.StyleName = "Data"
detailsTxt.CanGrow = True
detailsTxt.TextWrap = False

detailsTxt.Multiline = True

detailsTxt.Format = Format("{0:N}")
detailsTxt.Value = "=Fields.[" + col.ColumnName.ToString & "]"

detail.Items.Add(detailsTxt)

iniLeft = iniLeft + iniWidth

Next

the below screen shot show the merge column in excel.

http://prntscr.com/ofpfff

the below screen shot show the column before export

http://prntscr.com/ofphq4

answered on 19 Jul 2019, 07:22 AM

Hello M Kumar,

The horizontal merge of cells in Excel export is usually due to vertically misaligned report items in the entire report. I suspect that there may be an item above/below the Table that occupies only cell A or B and as the first cell of the Table is longer or misaligned with respect to this item the merge occurs.
The second issue is probably due to text that is longer (may have white space at the back) than the width of the cell, and is wrapped.

I cannot say what is the exact reason for the issues without a runnable report. I see also that the report definition is modified with code.
We will need the entire report with the code behind and sample data to try to explain the observed layout and to suggest a solution or workaround.

Regards,
Todor
Progress Telerik

answered on 24 Jul 2019, 05:13 AM

hi,

B ------ When exported, it is converted to text. Please export it out to excel as numb
Dim datas1 As New DataTable

If HttpContext.Current.Session("databaseslt") IsNot Nothing Then

Dim tempstr1 As String = ""
DoNotify(tempstr1)
tempstr1 = "select [Type] from [" + HttpContext.Current.Session("databaseslt") + "_position] where LField='" + col.ColumnName.ToString + "'"
DoNotify(tempstr1)
Dim sqlcon1 As New SqlConnection(HttpContext.Current.Session("FTCS"))

Dim sqlcmd1 As New SqlDataAdapter(tempstr1, sqlcon1)
sqlcon1.Open()
sqlcmd1.Fill(datas1)
sqlcon1.Close()
sqlcmd1.Dispose()
DoNotify(datas1.Rows(0)("Type").ToString)
End If

Dim headerTxt As New Telerik.Reporting.TextBox
headerTxt.Size = New SizeU(Unit.Inch(1.05), Unit.Inch(0.2))
headerTxt.Location = New Drawing.PointU(Unit.Inch(iniLeft), Unit.Inch(0.01))
headerTxt.Style.Font.Bold = True
headerTxt.Style.Font.Size = New Unit(10)
headerTxt.Style.VerticalAlign = VerticalAlign.Middle
headerTxt.Style.TextAlign = HorizontalAlign.Left
headerTxt.StyleName = "Caption"
headerTxt.Style.BackgroundColor = Color.FromArgb(121, 167, 227)
headerTxt.Value = col.ColumnName.ToString
headerTxt.CanGrow = True
headerTxt.TextWrap = False
headerTxt.Multiline = True
labelsGroupHeader.Items.Add(headerTxt)
Dim detailsTxt As New Telerik.Reporting.TextBox
detailsTxt.Size = New SizeU(Unit.Inch(1.05), Unit.Inch(0.2))
detailsTxt.Location = New Drawing.PointU(Unit.Inch(iniLeft), Unit.Inch(0.01))
detailsTxt.Style.Font.Size = New Unit(10)
detailsTxt.Multiline = True

detailsTxt.Style.VerticalAlign = VerticalAlign.Top
detailsTxt.Style.TextAlign = HorizontalAlign.Left
detailsTxt.StyleName = "Data"
detailsTxt.CanGrow = True
detailsTxt.TextWrap = False

If datas1.Rows(0)("Type").ToString = "Number" Then
DoNotify("Number")
detailsTxt.Format = Format("{0: N}")

ElseIf datas1.Rows(0)("Type").ToString = "Date" Then
DoNotify("Date")
detailsTxt.Format = Format("{0:D}")

'DoNotify("After conversion")
End If
detailsTxt.Value = "=Fields.[" + col.ColumnName.ToString & "]"

detail.Items.Add(detailsTxt)

iniLeft = iniLeft + iniWidth
'datas.Dispose()
'DoNotify("Addd" + Convert.ToString(iniLeft))
Next

pls check and reply

answered on 24 Jul 2019, 05:49 AM

hi,

The code i say number work but not work, in sql amount column is bigint and give the format like this

If datas1.Rows(0)("Type").ToString = "Number" Then
DoNotify("Number")
detailsTxt.Format = Format("{0: N}")

end if

in reportviewer show like this

http://prntscr.com/oj8dww

excel also same

http://prntscr.com/oj8eoa

when double click it show original value.

answered on 26 Jul 2019, 12:31 PM

Hi M Kumar,

In the code I see that the text of the content of detailsTxt is set like :

detailsTxt.Value = "=Fields.[" + col.ColumnName.ToString & "]"

It is not clear what is the content type of col (e.g. col.DataType). This type defines the type of the data in the TextBox, hence how the data will be exported also in Excel. Note that I cannot test the example code as the value we are interested in comes from a session/database I cannot access.

I tested with long type that corresponds to SQL bigint. The value was correctly displayed in both Excel formats as number.
I will test further if you sent us a runnable example with sample data that demonstrates the problem.

Regards,
Todor
Progress Telerik