Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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:
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 4-May-12 2:16am
pmcm860
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Write a method to fix the start index for writing the data to the excel sheet as shown below:
 
//Declare a DataRow for holding the previous row
//accessible for the getStartIndex method
DataRow previousRow;
 
//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();
   }
}
 
//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
}
  Permalink  
v3
Comments
pmcm at 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 at 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 at 4-May-12 10:01am
   
5!
VJ Reddy at 4-May-12 10:15am
   
Thank you, Sandeep.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

VJ Reddy - i'd like to thank you for your help, but i solved this by doing the following:
 
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;
                }
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 230
1 PIEBALDconsult 150
2 DamithSL 125
3 BillWoodruff 108
4 Garth J Lancaster 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 4 May 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100