Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi, in the data being exported from datatable i'd like to hide some particular column data if it is duplicated on the previous row for example:
Timesheet Job EmployeeName TimeOn TimeOff Group Qty Total
4/5/12 123 Joe 630 900 Test 45 90
4/5/12 123 Joe 630 900 Test 45 74
4/5/12 123 Joe 630 900 Test 45 82.22

In the above example I only want to see the data for Timesheet Job EmployeeName TimeOn TimeOff Group Qty once.

Here is my code to export to excel:
C#
private void ExportToExcel(Worksheet ws)
        {
            string strJob;
            string strC;

            int intStartRow;
            int intRows;
            int intColumns;
            Excel.Range SelectedCells;

            if (LIPSdb.dsResults.Tables.Contains("tbl_Daily_Timesheet"))
            {
                intRows = LIPSdb.dsResults.Tables["tbl_Daily_Timesheet"].Rows.Count;
                intColumns = (LIPSdb.dsResults.Tables["tbl_Daily_Timesheet"].Columns.Count - 1);
                if (intColumns > gintMaxColumn) gintMaxColumn = intColumns;

                int C = 0;

                intStartRow = gintRow;

                for (C = 1; C < intColumns; C++) //write out the column headers
                {
                    ws.Cells[gintRow, gintColumn + C] = LIPSdb.dsResults.Tables["tbl_Daily_Timesheet"].Columns[C].ColumnName;
                }

                foreach (DataRow dr in LIPSdb.dsResults.Tables["tbl_Daily_Timesheet"].Rows)
                {
                    gintRow++;  //write out the row data
                    for (C = 1; C < intColumns; C++)
                    {
                        ws.Cells[gintRow, gintColumn + C] = dr[C].ToString();
                    }
                }
            }
        }
Posted

Write a method to fix the start index for writing the data to the excel sheet as shown below:

C#
//Declare a DataRow for holding the previous row
//accessible for the getStartIndex method
DataRow previousRow;


C#
//In the following code set C to the start index 
//as obtained from getStartIndex method
foreach (DataRow dr in 
LIPSdb.dsResults.Tables["tbl_Daily_Timesheet"].Rows)
{
   gintRow++;  //write out the row data
   for (C = getStartIndex(dr); C < intColumns; C++)
   {
       ws.Cells[gintRow, gintColumn + C] = dr[C].ToString();
   }
}


C#
//Verify the index upto which the values are to be
//checked as per the requirement
private int getStartIndex(DataRow currentRow){
   if (previousRow == null) { 
      previousRow=currentRow;         
      return 1;
   }
   for(i=1; i < 8; i++){
      if (currentRow[i] != DBNull.Value && previousRow[i] != DBNull.Value && 
            !currentRow[i].ToString().Equals(previousRow[i].ToString())){
            previousRow=currentRow;         
            return 1; //check this value as per requirement
      }
   }
   return 8;   //check this value as per requirement
}
 
Share this answer
 
v3
Comments
pmcm 4-May-12 8:38am    
hi i added your code and tried to run it but got a runtime error:
"System.NullReferenceException was unhandled
Message=Object reference not set to an instance of an object." On the following line
<pre>if (currentRow[i] != DBNull.Value && previousRow[i] != DBNull.Value && !currentRow[i].ToString().Equals(previousRow[i].ToString()))</pre> i'm guessing this is because the previous row is the header detail. Any idea how I solve this?

Thanks for your help
VJ Reddy 4-May-12 9:29am    
Please see that previousRow is added as field of the class so that it is available to getStartIndex method. Put a break point in side the getStartIndex method and when the execution stops at the break point verify the values of the variable to find out which is causing the error, then accordingly modify the code.
Thank you.
Sandeep Mewara 4-May-12 10:01am    
5!
VJ Reddy 4-May-12 10:15am    
Thank you, Sandeep.
VJ Reddy - i'd like to thank you for your help, but i solved this by doing the following:

C#
int introwCount = 1;

                foreach (DataRow dr in LIPSdb.dsResults.Tables["tbl_Daily_Timesheet"].Rows)
                {
                    gintRow++;  //write out the row data
                    for (C = 1; C < intColumns; C++)
                    {
                        if (dr[2].ToString() != "")
                        {
                            if (introwCount == 1)
                            {
                                ws.Cells[gintRow, gintColumn + C] = dr[C].ToString();
                            }
                            else
                            {
                                ws.Cells[gintRow, 8] = dr[8].ToString();
                                ws.Cells[gintRow, 9] = dr[9].ToString();
                                ws.Cells[gintRow, 10] = dr[10].ToString();
                                ws.Cells[gintRow, 11] = dr[11].ToString();
                                ws.Cells[gintRow, 12] = dr[12].ToString();
                                ws.Cells[gintRow, 13] = dr[13].ToString();
                                ws.Cells[gintRow, 14] = dr[14].ToString();
                                ws.Cells[gintRow, 15] = dr[15].ToString();
                                ws.Cells[gintRow, 16] = dr[16].ToString();
                                ws.Cells[gintRow, 17] = dr[17].ToString();
                                ws.Cells[gintRow, 18] = dr[18].ToString();
                                ws.Cells[gintRow, 19] = dr[19].ToString();
                            }                            
                        }
                        if (dr[2].ToString() == "") ws.Cells[gintRow, gintColumn + C] = dr[C].ToString();                                           
                    }
                    introwCount++;

                    if (introwCount > 3)
                        introwCount = 1;
                }
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900