how can i achieve this excel format in asp.net c# 3.5

Question

Report Abc- Example

Excel Format 2007 (I have to acheive this below format)

Job Number : 123________         Job Date : ___________    Destination : ________

_________________________________________________________________________________

Voucher No.    |   Voucher Date |  Expence |  Income 
_________________________________________________________________________________
 
 100           |  21/May/2021   |  100     |  50
 101           |  20/may/2020   |  200     |  100
_________________________________________________________________________________

Gross Profit : -150       Total |  300     |  150

_________________________________________________________________________________
_________________________________________________________________________________ 


Job Number : 124________         Job Date : ___________    Destination : ________

_________________________________________________________________________________

Voucher No.    |   Voucher Date |  Expence |  Income 
_________________________________________________________________________________
 
 105           |  21/May/2021   |  100     |  500
 109           |  20/may/2020   |  500     |  500
_________________________________________________________________________________

Gross Profit : 400       Total |  600     |  1000

This Job Number and Voucher No. Shows multiple(N) time...... This my Datatable coming From Database...(Original Data)

JobNo   |  JobDate   |  Destination | VoucherNo    |   Voucher Date   |  Expence  |  Income 
--------------------------------------------------------------------------------------------
123     |                 India     |  100         |     21/May/2021  |  100      |  50 
123     |                 India     |  101         |     20/may/2020  |  200      |  100   
124     |                 Canada    |  105         |     21/May/2021  |  100      |  500 
124     |                 Canada    |  109         |     20/may/2020  |  500      |  500   
---------------------------------------------------------------------------------------------

I have Tried this ...I'm able Change Datatable..as Below

JobNo   |  JobDate   |  Destination | VoucherNo    |   Voucher Date   |  Expence  |  Income 
--------------------------------------------------------------------------------------------
123     |                 India     |  100         |     21/May/2021  |  100      |  50 
123     |                 India     |  101         |     20/may/2020  |  200      |  100  
------------------------------------------------------------------------------------------------
Gross Profit: -150                                                       300         600
---------------------------------------------------------------------------------------------- 
124     |                  Canada   |  105         |     21/May/2021  |  100      |  500 
124     |                  Canada   |  109         |     20/may/2020  |  500      |  500   
---------------------------------------------------------------------------------------------
Gross Profit: 400                                                     |  600      |  1000
---------------------------------------------------------------------------------------------

I have Export the Data as Below Format.......

Report ABC From ABC Date to XYZ Date

JobNo   |  JobDate   |  Destination | VoucherNo    |   Voucher Date   |  Expence  |  Income 
--------------------------------------------------------------------------------------------
123     |                 India     |  100         |     21/May/2021  |  100      |  50 
        |                           |  101         |     20/may/2020  |  200      |  100  
------------------------------------------------------------------------------------------------
Gross Profit: -150                                                       300         600
---------------------------------------------------------------------------------------------- 
124     |                  Canada   |  105         |     21/May/2021  |  100      |  500 
        |                           |  109         |     20/may/2020  |  500      |  500   
---------------------------------------------------------------------------------------------
Gross Profit: 400                                                     |  600      |  1000
---------------------------------------------------------------------------------------------

But I want to acheive this ..

Job Number : 123________         Job Date : ___________    Destination : ________

_________________________________________________________________________________

Voucher No.    |   Voucher Date |  Expence |  Income 
_________________________________________________________________________________
 
 100           |  21/May/2021   |  100     |  50
 101           |  20/may/2020   |  200     |  100
_________________________________________________________________________________

Gross Profit : -150       Total |  300     |  150

_________________________________________________________________________________
_________________________________________________________________________________ 


Job Number : 124________         Job Date : ___________    Destination : ________

_________________________________________________________________________________

Voucher No.    |   Voucher Date |  Expence |  Income 
_________________________________________________________________________________
 
 105           |  21/May/2021   |  100     |  500
 109           |  20/may/2020   |  500     |  500
_________________________________________________________________________________

Gross Profit : 400       Total |  600     |  1000

This Job Number and Voucher No. Shows multiple(N) time......

I'm Getting Data From Sql Server as Database..

I have using asp.net 3.5 framework...please don't suggest any ddl..i have not right add any ddl file add at client Side....

How can i achieve this ...any help will be appreciated....Thank U!!

Answer 1

Ok, this is one of those problems in which MORE thinking then doing is the solution!

First up, you have some data that is NOT really columns, and then inside that, you want a grid of data.

so, we simply have to group our data by JobNo.

And since this is NOT really columns, but some text data, then a repeater control is the job for that task.

Now, inside of the repeater, we need that table/grid like. So inside the repeater control, we drop in a gridview.

So, our markup will look like this:

   <div style="width:38%">
        <br />
        <asp:Repeater ID="Repeater1" runat="server">
            <ItemTemplate>
            <asp:Label runat="server" Text="Job Number:"></asp:Label>
                <asp:TextBox ID="txtJobNum" runat="server" Text='<%# Eval("JobNo") %>'></asp:TextBox>
                <asp:Label ID="Label1" runat="server" Text="Job Date; _____________" Style="margin-left:20px"></asp:Label>
                <asp:Label ID="Label2" runat="server" Text="Job Destination _____________"  Style="margin-left:20px"></asp:Label>
                <br />
                <br />
                <asp:GridView ID="GridView1" runat="server" ShowFooter="true"></asp:GridView>
                <hr />
                <br />
            </ItemTemplate>
        </asp:Repeater>
        <br />
    </div>

So the repater is our "heading area", and inside we have the Grid.

So, on page load, we fill the repeter, and for each repeater row event, we fill out the grid.

So the code looks like this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If IsPostBack = False Then

        Using cmdSQL As New SqlCommand("SELECT JobNo FROM jobs GROUP BY JobNo",
                        New SqlConnection(My.Settings.TEST4))

            cmdSQL.Connection.Open()
            Repeater1.DataSource = cmdSQL.ExecuteReader
            Repeater1.DataBind()

        End Using

    End If
End Sub

Protected Sub Repeater1_ItemDataBound(sender As Object, e As RepeaterItemEventArgs) Handles Repeater1.ItemDataBound

    Dim txtJobNum As TextBox = e.Item.FindControl("txtJobNum")

    Dim gv As GridView = e.Item.FindControl("GridView1")

    Using cmdSQL As New SqlCommand("Select * from jobs where JobNo = " & txtJobNum.Text, New SqlConnection(My.Settings.TEST4))
        cmdSQL.Connection.Open()
        Dim MyTable As New DataTable
        MyTable.Load(cmdSQL.ExecuteReader)
        gv.DataSource = MyTable
        gv.DataBind()


        ' now add total row
        Dim Expense As Decimal = 0
        Dim Income As Decimal = 0
        For Each OneRow In MyTable.Rows
            Expense += OneRow("Expense")
            Income += OneRow("Income")
        Next

        Dim Profit As Decimal = Income - Expense

        Dim ix As Integer = gv.FooterRow.Cells.Count
        gv.FooterRow.Cells(1).Text = "Gross Profit ="
        gv.FooterRow.Cells(2).Text = Profit
        gv.FooterRow.Cells(ix - 2).Text = Expense
        gv.FooterRow.Cells(ix - 1).Text = Income

    End Using
End Sub

Edit: - this post was not tagged as C# - so I posted as vb.

However, as c#, the code would look like:

protected void Page_Load(object sender, System.EventArgs e)
{
    if (IsPostBack == false)
    {
        using (SqlCommand cmdSQL = new SqlCommand("SELECT JobNo FROM jobs GROUP BY JobNo", new SqlConnection(My.Settings.TEST4)))
        {
            cmdSQL.Connection.Open();
            Repeater1.DataSource = cmdSQL.ExecuteReader;
            Repeater1.DataBind();
        }
    }
}

protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
    TextBox txtJobNum = e.Item.FindControl("txtJobNum");

    GridView gv = e.Item.FindControl("GridView1");

    using (SqlCommand cmdSQL = new SqlCommand("Select * from jobs where JobNo = " + txtJobNum.Text, new SqlConnection(My.Settings.TEST4)))
    {
        cmdSQL.Connection.Open();
        DataTable MyTable = new DataTable();
        MyTable.Load(cmdSQL.ExecuteReader);
        gv.DataSource = MyTable;
        gv.DataBind();


        // now add total row
        decimal Expense = 0;
        decimal Income = 0;
        foreach (var OneRow in MyTable.Rows)
        {
            Expense += OneRow("Expense");
            Income += OneRow("Income");
        }

        decimal Profit = Income - Expense;

        int ix = gv.FooterRow.Cells.Count;
        gv.FooterRow.Cells(1).Text = "Gross Profit =";
        gv.FooterRow.Cells(2).Text = Profit;
        gv.FooterRow.Cells(ix - 2).Text = Expense;
        gv.FooterRow.Cells(ix - 1).Text = Income;
    }
}

And the output looks like this:

Now in above, I just had the data in a table. It not clear how/where/when/what your data is being imported into. But get the data into a data table. If you only have one table, and can't use SQL group by, then simply make a 2nd table, and loop out the JobNo into that table, and bind that to the repeater.

The approach, the concept, the idea here?

Your heading part and text is the repeater control - since it not really a table.

Then the grid part/table? That can be the grid view. You fill it for each group row, and then of course total up the results and shove that into the footing row.