导出到Excel时格式化数据表 [英] Format datatable while exporting to Excel

问题描述

在从数据表中导出的数据中，如果某些特殊的列数据在上一行中重复了，我想隐藏一些特定的列数据，例如:
时间表作业EmployeeName TimeOn TimeOff组数量总计
12/4/5/123乔630900测试45 90
2012年5月4日123 Joe 630900测试45 74
2012年5月4日123 Joe 630900测试45 82.22

在上面的示例中，我只希望一次查看时间表作业EmployeeName TimeOn TimeOff组数量的数据.

这是我要导出到excel的代码:

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();
                    }
                }
            }
        }

推荐答案

编写一种方法来修复将数据写入excel工作表的起始索引，如下所示:

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
}

VJ Reddy-感谢您的帮助，但是我通过以下操作解决了这个问题:

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;
                }

这篇关于导出到Excel时格式化数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！