Is there a tutorial for a Excel Datasource made report?

[adiboing]

Hello,

like my subject title says, do you know where I could find a tutorial about creating a whole report with a Microsoft Excel (.xls) Datasource?

 

peace Adi

 

[cbarlow3]

I couldn't find anything about it in the iReport Ultimate Guide, but I was able to get it to work in iReport within a matter of minutes. Here's what I did:

1. I have an Excel file called Cities.xls (attached) that I want to use as a data source.

2. In the iReport toolbar across the top, I clicked on the "Report Datasources" icon and I click "New" to create a new connection/datasource.  My "Datasource" dialog box lists seventeen different datasource types that are supported in iReport Professional 3.7.1.1.  Near the bottom is "Microsoft Excel (xls) data source".  I select that one.

3. In the "Microsoft Excel (xls) data source) dialog box that comes up, I enter a Name of "CitiesSpreadsheet", for the Excel file I click the "Browse" button and find my file, then I click the "Get columns name from the first row of the file" button, which makes column names id, city, state, and population appear.  I also check the "Skip the first line (the column names will be read from the first line) checkbox, otherwise the column names in my spreadsheet will also  be read in as actual data.  I click the "Test" button, and when it says "Connection Test Successful" (screenshot attached shows both dialog boxes and the popup), I dismiss the popup and click "Save" on one dialog box and "Close" on the other.

4. Now I see "CitiesSpreadsheet" as one of the datasources I can specify in the iReport toolbar dropdown (in fact, it's the one that's selected, since I just created it).  Now I can create a new report: File | New | Letter | Open this template.  I provide a report name of "ExcelDatasourceExample", "Next", and "Finish".

5. Now for the fun part:  in the Designer view, I click the Report query icon (just to the right of "Preview") to bring up the Report query dialog, but instead of entering an SQL query in the "Report query" pane, I click on the "Excel Datasource" tab and then click on the "Get fields from datasource" button (screenshot attached).  One problem I see is that all my fields show up as being of type java.lang.String, even though in my original spreadsheet, I had some of these (like the "population" column) defined as numbers.  But you can go into the field in the Report Inspector and change the data type there.

6. From there, I can drag my fields into the detail band and format them, add static headers, style, a variable to sum the population field for the Summary band, etc.  jrxml for finished report pasted below, and pdf result attached.  Good luck!

Carl

 

 

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="ExcelDatasourceExample" language="groovy" pageWidth="612" pageHeight="792" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">	<property name="ireport.zoom" value="1.0"/>	<property name="ireport.x" value="0"/>	<property name="ireport.y" value="0"/>	<style name="BandedTable" isDefault="false">		<conditionalStyle>			<conditionExpression><![CDATA[$V{REPORT_COUNT}%2==1]]></conditionExpression>			<style isDefault="false" style="BandedTable" backcolor="#99FF99"/>		</conditionalStyle>	</style>	<queryString>		<![CDATA[]]>	</queryString>	<field name="id" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="city" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="state" class="java.lang.String">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<field name="population" class="java.lang.Integer">		<fieldDescription><![CDATA[]]></fieldDescription>	</field>	<variable name="PopulationTotal" class="java.lang.Integer" calculation="Sum">		<variableExpression><![CDATA[$F{population}]]></variableExpression>	</variable>	<background>		<band splitType="Stretch"/>	</background>	<title>		<band height="20" splitType="Stretch">			<staticText>				<reportElement x="0" y="0" width="572" height="20"/>				<textElement>					<font size="14" isBold="true"/>				</textElement>				<text><![CDATA[Example of Report from an Excel Spreadhseet Data Source]]></text>			</staticText>		</band>	</title>	<columnHeader>		<band height="20" splitType="Stretch">			<staticText>				<reportElement x="0" y="0" width="53" height="20"/>				<textElement>					<font size="12" isBold="true"/>				</textElement>				<text><![CDATA[iD]]></text>			</staticText>			<staticText>				<reportElement x="53" y="0" width="125" height="20"/>				<textElement>					<font size="12" isBold="true"/>				</textElement>				<text><![CDATA[City]]></text>			</staticText>			<staticText>				<reportElement x="178" y="0" width="57" height="20"/>				<textElement>					<font size="12" isBold="true"/>				</textElement>				<text><![CDATA[state]]></text>			</staticText>			<staticText>				<reportElement x="235" y="0" width="100" height="20"/>				<textElement textAlignment="Right">					<font size="12" isBold="true"/>				</textElement>				<text><![CDATA[Population]]></text>			</staticText>		</band>	</columnHeader>	<detail>		<band height="20" splitType="Stretch">			<textField>				<reportElement style="BandedTable" mode="Opaque" x="0" y="0" width="53" height="20"/>				<textElement/>				<textFieldExpression class="java.lang.String"><![CDATA[$F{id}]]></textFieldExpression>			</textField>			<textField>				<reportElement style="BandedTable" mode="Opaque" x="53" y="0" width="125" height="20"/>				<textElement/>				<textFieldExpression class="java.lang.String"><![CDATA[$F{city}]]></textFieldExpression>			</textField>			<textField>				<reportElement style="BandedTable" mode="Opaque" x="178" y="0" width="57" height="20"/>				<textElement/>				<textFieldExpression class="java.lang.String"><![CDATA[$F{state}]]></textFieldExpression>			</textField>			<textField pattern="#,##0">				<reportElement style="BandedTable" mode="Opaque" x="235" y="0" width="100" height="20"/>				<textElement textAlignment="Right"/>				<textFieldExpression class="java.lang.Integer"><![CDATA[$F{population}]]></textFieldExpression>			</textField>		</band>	</detail>	<pageFooter>		<band height="20" splitType="Stretch">			<textField>				<reportElement x="452" y="0" width="80" height="20"/>				<textElement textAlignment="Right"/>				<textFieldExpression class="java.lang.String"><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>			</textField>			<textField evaluationTime="Report">				<reportElement x="532" y="0" width="40" height="20"/>				<textElement/>				<textFieldExpression class="java.lang.String"><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>			</textField>		</band>	</pageFooter>	<summary>		<band height="20" splitType="Stretch">			<textField pattern="#,##0">				<reportElement x="235" y="0" width="100" height="20"/>				<textElement textAlignment="Right"/>				<textFieldExpression class="java.lang.Integer"><![CDATA[$V{PopulationTotal}]]></textFieldExpression>			</textField>			<staticText>				<reportElement x="120" y="0" width="115" height="20"/>				<textElement textAlignment="Right">					<font size="12" isBold="true"/>				</textElement>				<text><![CDATA[Total Population: ]]></text>			</staticText>		</band>	</summary></jasperReport>

[zqtest]

 Hi, carl, I have a question, that I can't get exactly the same format as ur PDF file. I want to know that u just drag the field to the detail band? and I drag a table to the report,  it compiles good, but in the preview file, i couldn't see the table, I don't konw why.

Hope u could see my reply :) thanks

Post Edited by zqtest at 05/11/2011 09:40

[cbarlow3]

Yes, I just dragged individual fields into the detail band.  I did NOT drag a Table element from the palette for two reasons:

1. I'm still using JasperServer 3.x, so I'm still using iReport 3.7.1.1 Professional, which supports List elements but does not yet support Tables.

2. First, a Table element doesn't automatically know what you want to put it in it...it won't assume that you started with an Excel table and that you want to display that same table unchanged.  Thank goodness it doesn't make that assumption or it would be a pretty inflexible tool!  But maybe more importantly, a Table element (like a List element) doesn't even use the main data set (the results of your SQL query, or in this case, the fields you pulled from the Excel table).  Instead, you have to create a NEW dataset with a NEW query.  It doesn't even share the main input parameters unless you make the effort of creating input parameters for the new dataset and mapping some/all of the main parameters to these dataset parameters.  Lists and tables can be pretty handy when you want to output this format of data on information that isn't directly related to your main query, but it's kind of redundant when the information is directly tied to your main query, and I don't think there's a way around that.

Somewhere on here I have a post of the steps I use to create a List, but I won't go through them here, because my main point in this case is that you don't want to use a List or Table if your goal is just to display the fields from your main dataset.  Since List and Table seem to have some nice formatting features as well as letting you output information from a subordinate dataset, maybe there is or will be some ability to let you tie a List or Table to the MAIN dataset and query instead of always creating a new one.  As far as I know, that ability doesn't currently exist without you essentially duplicating the logic from your main query.

Carl

[zqtest]

thank u, carl.

I've already got the right format of list, and then I'll try the table element  to see how it works well,

and also, carl, one question more, are u familiar with the differences between Jasperreport and Birt? which one is better to use? and also like the jfreechart, jchart, other open source reporting tools ?

thanks for ur help :)

[cbarlow3]

I haven't used BIRT, so I can't help you there.  Two others you might look at (but I've only looked at their self-proclaimed feature set, not actually taken them for a test drive) are Pentaho and LogiXML.  Here's a slightly older (2009) list that might peak your interest: http://apps.open-libraries.com/best-open-source-reporting-tools/.  One of the things that tipped us towards using JasperSoft at our company was the training opportunities they offer, which we thought would be good not only for us but also for the clients (credit unions in our case) who end up using the software we write, since we're embedding JasperReports Server into our application.  I was very impressed with how smoothly their first nationwide conference went last February, and I really appreciated that their CEO Brian Gentile took the time and initiative to come visit us when he was in San Diego earlier this year, even though we're currently only a small startup company.  That kind of personal attention to detail from the top is a big part of how we do business:  even though we're a startup, most of us have worked together for about 20 years, so we're comfortable with the fact that we all share that customer service priority as a mindset, and it's refreshing when we work with other companies who share that passion for service.

Carl

[zqtest]

Thank you very much, carl, this document is very nice! thanks :)

[rashmi_prabhu]

Hi,

I am new to ireport so can anyone help me out. I have an excel sheet with me in which there are two sheets,Sheet1 and Sheet2.Now im using this excel sheet as datasource to produce reports in ireport.But when i try to retreive data its showing me data of  sheet1 only.so can anyone suggest me how to retreive data fom both the sheets.

[adiboing]

@rashmi_prabhu: I also asked this once in a thread, but nobody cares... All I now is that you can copy your second sheet information into a seperate excel file and use this as another datasource.

[christi2]

Hi

I have a related question.  I am trying out iReport for the first time, and I am trying to use a subdataset with an Excel spreadsheet as data source. (Some report elements such as 'Table' seem to require a subdataset)

My Excel spreadsheet looks like this:

 

Soda	Respondents
Coke	100
Pepsi	100
7-Up	33
Sunkist	20
Welches Grape	10

My xml code for the subdataset looks like this:

    <subDataset name="Dataset_1" uuid="0ed8ae74-6ddf-4e05-afba-dda8894f0c25">
        <field name="Soda" class="java.lang.String"/>
        <field name="Respondents" class="java.lang.Number"/>
    </subDataset>

Must I set up a query to get hold of the actual Soda and Respondents data? What must this query look like? 

Any feedback would be appreciated.