excel cell format did not work on generated report

[kingsteff]

Hello,

my current problem is that the excel file that is the result of the report, looks completey so as desired, but when i open it in ms-excel and i want to fromat a cell by clicking rightmousebutton on a cell and choose "fomat cell" this isnt possible.

the dialog do not even pop up then.

 anyone has an idea to fix this?

 

thx

steff

Post Edited by kingsteff at 07/01/2009 12:09

[Teodor Danciu]

Hi,

 

Can you send a file showing this problem?

 

Thank you,

Teodor

 

[kiran_raja]

I am facing the same problem. As mentioned in the post before post. The popup doesnt appear on right clicking and selecting the format cells option. I have attached a sample file for your reference.

[abaking]

Hello all,

Have you ever figured this out?  I'm encountering the same issue.

Thanks,

Arnold

[abaking]

Hello,

Here's a sample file for this issue.

Thanks.

[kiran_raja]

I was able to view the popup on right clicking and selecting the "format cells" option in the excel file you have uploaded. I am using office 2003.

Well recently i had faced a similar issue in the excel files exported from jasper reports. In jrxml file for <textfield> tag i had either not given a foreground color for the text or in some cases set the foreground color to "black"(forecolor="black"). This was the issue, when i set for all the <statictext> and <textfield> the forecolor as #060404, everything worked perfectly well.

[abaking]

I find it interesting that you can. Perhaps we're not talking of the same thing...

 

I'm able to see the popup when doing the ff:

- click on the column/row header for the whole cell (the one on the left of "A" or above "1"), which selects all the cells

- right-click

- select "format cells"

 

However, selecting just a single cell (any cell whose value was generated out of jasperreports) and doing the right-click does not display the popup for me.

 

I'll try changing the "black" to #060404 and see how that affects it.

 

I am using Excel 2003 too.

 

Thanks much!

[abaking]

as it turns out, I don't have any color tag/value in my XML.

Code:

<?xml version="1.0" encoding="UTF-8"?><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="QA_ingred" language="groovy" pageWidth="572" pageHeight="15000" columnWidth="555" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isIgnorePagination="true">	<property name="ireport.zoom" value="2.0"/>	<property name="ireport.x" value="0"/>	<property name="ireport.y" value="0"/>	<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">		<defaultValueExpression><![CDATA["C:\\Documents and Settings\\ABaking\\Reports\\Optiva\\"]]></defaultValueExpression>	</parameter>	<parameter name="frmId" class="java.lang.Integer"/>	<queryString>		<![CDATA[selectf.formula_id,f.formula_code,f.version,f.description,li.line_id,li.label_text,li.quantity,isnull(x.func,'') funcfrom fsformula finner join fsobjectreference r on r.object_type='formula' and r.object_key=f.formula_id and r.r_object_type='label'inner join fslabelingred li on li.label_id=r.r_object_key and section_id=1 and line_id>0left join ( select alias_code6,max(func) func from dbo.getingred($P{frmId}) group by alias_code6) x on x.alias_code6 = li.label_textwhere f.formula_id=$P{frmId}]]>	</queryString>	<field name="formula_id" class="java.lang.Integer"/>	<field name="formula_code" class="java.lang.String"/>	<field name="version" class="java.lang.String"/>	<field name="description" class="java.lang.String"/>	<field name="line_id" class="java.lang.Integer"/>	<field name="label_text" class="java.lang.String"/>	<field name="quantity" class="java.lang.Double"/>	<field name="func" class="java.lang.String"/>	<variable name="total_qty" class="java.lang.Double" calculation="Sum">		<variableExpression><![CDATA[$F{quantity}]]></variableExpression>	</variable>	<title>		<band height="20">			<textField>				<reportElement x="0" y="0" width="572" height="20"/>				<box>					<topPen lineWidth="1.0"/>					<leftPen lineWidth="1.0"/>					<bottomPen lineWidth="1.0"/>					<rightPen lineWidth="1.0"/>				</box>				<textElement>					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{formula_code}.concat(": ").concat($F{description})]]></textFieldExpression>			</textField>		</band>	</title>	<columnHeader>		<band height="15" splitType="Stretch">			<staticText>				<reportElement x="0" y="0" width="200" height="15"/>				<box>					<topPen lineWidth="1.0"/>					<leftPen lineWidth="1.0"/>					<bottomPen lineWidth="1.0"/>					<rightPen lineWidth="1.0"/>				</box>				<textElement textAlignment="Center">					<font size="8" isBold="true"/>				</textElement>				<text><![CDATA[ingredients]]></text>			</staticText>			<staticText>				<reportElement x="200" y="0" width="261" height="15"/>				<box>					<topPen lineWidth="1.0" lineStyle="Solid"/>					<leftPen lineWidth="1.0" lineStyle="Solid"/>					<bottomPen lineWidth="1.0" lineStyle="Solid"/>					<rightPen lineWidth="1.0" lineStyle="Solid"/>				</box>				<textElement textAlignment="Center">					<font size="8" isBold="true"/>				</textElement>				<text><![CDATA[Function]]></text>			</staticText>			<staticText>				<reportElement x="461" y="0" width="111" height="15"/>				<box>					<topPen lineWidth="1.0" lineStyle="Solid"/>					<leftPen lineWidth="1.0" lineStyle="Solid"/>					<bottomPen lineWidth="1.0" lineStyle="Solid"/>					<rightPen lineWidth="1.0" lineStyle="Solid"/>				</box>				<textElement textAlignment="Center">					<font size="8" isBold="true"/>				</textElement>				<text><![CDATA[Qty %]]></text>			</staticText>		</band>	</columnHeader>	<detail>		<band height="30" splitType="Prevent">			<textField isStretchWithOverflow="true">				<reportElement stretchType="RelativeToBandHeight" x="0" y="0" width="200" height="13"/>				<box>					<pen lineWidth="0.5"/>					<topPen lineWidth="0.5"/>					<leftPen lineWidth="1.0"/>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement>					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{label_text}]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true" pattern="###0.000000">				<reportElement stretchType="RelativeToBandHeight" x="461" y="0" width="111" height="13"/>				<box>					<pen lineWidth="0.5"/>					<topPen lineWidth="0.5"/>					<leftPen lineWidth="0.5"/>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="1.0"/>				</box>				<textElement textAlignment="Right">					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.Double"><![CDATA[$F{quantity}]]></textFieldExpression>			</textField>			<textField isStretchWithOverflow="true">				<reportElement stretchType="RelativeToBandHeight" x="200" y="0" width="261" height="13" isPrintWhenDetailOverflows="true"/>				<box>					<pen lineWidth="0.5"/>					<topPen lineWidth="0.5"/>					<leftPen lineWidth="0.5"/>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement>					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{func}]]></textFieldExpression>			</textField>		</band>	</detail>	<summary>		<band height="67">			<staticText>				<reportElement x="0" y="0" width="200" height="15"/>				<box>					<topPen lineWidth="1.0" lineStyle="Solid"/>					<leftPen lineWidth="1.0" lineStyle="Solid"/>					<bottomPen lineWidth="1.0" lineStyle="Solid"/>					<rightPen lineWidth="1.0" lineStyle="Solid"/>				</box>				<textElement>					<font size="8" isBold="true"/>				</textElement>				<text><![CDATA[Total]]></text>			</staticText>			<staticText>				<reportElement x="200" y="0" width="261" height="15"/>				<box>					<topPen lineWidth="1.0" lineStyle="Solid"/>					<leftPen lineWidth="1.0" lineStyle="Solid"/>					<bottomPen lineWidth="1.0" lineStyle="Solid"/>					<rightPen lineWidth="1.0" lineStyle="Solid"/>				</box>				<textElement textAlignment="Center">					<font size="8" isBold="true"/>				</textElement>				<text><![CDATA[]]></text>			</staticText>			<textField pattern="###0.000000">				<reportElement x="461" y="0" width="111" height="15"/>				<box>					<topPen lineWidth="1.0" lineStyle="Solid"/>					<leftPen lineWidth="1.0" lineStyle="Solid"/>					<bottomPen lineWidth="1.0" lineStyle="Solid"/>					<rightPen lineWidth="1.0" lineStyle="Solid"/>				</box>				<textElement textAlignment="Right">					<font size="8"/>				</textElement>				<textFieldExpression class="java.lang.Double"><![CDATA[$V{total_qty}]]></textFieldExpression>			</textField>			<subreport>				<reportElement x="0" y="15" width="572" height="52" isRemoveLineWhenBlank="true"/>				<subreportParameter name="frmId">					<subreportParameterExpression><![CDATA[$P{frmId}]]></subreportParameterExpression>				</subreportParameter>				<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>				<subreportExpression class="java.lang.String"><![CDATA[$P{SUBREPORT_DIR} + "QA_allergens.jasper"]]></subreportExpression>			</subreport>		</band>	</summary></jasperReport>

[abaking]

By popup, I meant the dialog box for formatting the cell. The context-sensitive menu does show up.

 

I'll try adding a color tag

 

Thanks.

[abaking]

The issue seems to not have any correlation with color.  On the same report, cells on the Page Header part are okay (cell format dialog box pops up as expected).  However, the cell format popup fails to pop up when used on the cell on the Group Header part.

Code:

<?xml version="1.0" encoding="UTF-8"?><jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="QuantitativeAnalysis" language="groovy" pageWidth="572" pageHeight="3500" columnWidth="572" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">	<property name="ireport.zoom" value="1.0"/>	<property name="ireport.x" value="0"/>	<property name="ireport.y" value="0"/>	<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">		<defaultValueExpression><![CDATA["C:\\Documents and Settings\\ABaking\\Reports\\Optiva\\"]]></defaultValueExpression>	</parameter>	<parameter name="division" class="java.lang.String"/>	<parameter name="frmId" class="java.lang.Integer"/>	<parameter name="fgIds" class="java.lang.String"/>	<queryString>		<![CDATA[select description fgName from fsformulawhere formula_id in ($P{fgIds})]]>	</queryString>	<field name="fgName" class="java.lang.String"/>	<group name="FinishedGood" isStartNewPage="true" keepTogether="true">		<groupExpression><![CDATA[$F{fgName}]]></groupExpression>		<groupHeader>			<band height="15">				<textField isStretchWithOverflow="true">					<reportElement x="0" y="0" width="572" height="15" isPrintWhenDetailOverflows="true"/>					<textElement>						<font size="10" isBold="true"/>					</textElement>					<textFieldExpression class="java.lang.String"><![CDATA["Final Product Name: ".concat($F{fgName})]]></textFieldExpression>				</textField>			</band>		</groupHeader>		<groupFooter>			<band height="50" splitType="Prevent">				<subreport>					<reportElement stretchType="RelativeToBandHeight" x="0" y="0" width="572" height="50"/>					<subreportParameter name="frmId">						<subreportParameterExpression><![CDATA[$P{frmId}]]></subreportParameterExpression>					</subreportParameter>					<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>					<subreportExpression class="java.lang.String"><![CDATA[$P{SUBREPORT_DIR} + "QA_ingred.jasper"]]></subreportExpression>				</subreport>			</band>		</groupFooter>	</group>	<pageHeader>		<band height="50">			<staticText>				<reportElement x="0" y="0" width="200" height="15" isPrintWhenDetailOverflows="true"/>				<textElement>					<font size="10"/>				</textElement>				<text><![CDATA[Quantitative Formula Report ]]></text>			</staticText>			<textField pattern="MM/dd/yyyy">				<reportElement x="461" y="0" width="111" height="15"/>				<textElement>					<font size="10"/>				</textElement>				<textFieldExpression class="java.util.Date"><![CDATA[new Date()]]></textFieldExpression>			</textField>			<staticText>				<reportElement x="200" y="0" width="261" height="15"/>				<textElement textAlignment="Right">					<font size="10"/>				</textElement>				<text><![CDATA[Date: ]]></text>			</staticText>			<textField>				<reportElement x="0" y="15" width="572" height="35"/>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="24"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$P{division}]]></textFieldExpression>			</textField>		</band>	</pageHeader></jasperReport>

[abaking]

Turns out the border color (or lack of it) is the culprit. I explicitly set border color for cells with borders to #060404 and that fixed the issue.