Creating groups/blocks from table based on value

Question

I have input table in datagridview (output is showed in green) and I need to get to this output:

'Start of block'   'Size'  'TypKar'
 1.2.2017 0:00:02   14      6280
 1.2.2017 0:03:33    2      3147
 1.2.2017 0:04:17    2      4147
 1.2.2017 0:04:28    2      6280
 1.2.2017 0:04:59   10      3147

Right now I use for loop in which I write first entry and then I count until value in column TypKar changes. When it changes, I write date and type and start counting from 1.

for(int i = 0; i < dviewExport.RowCount; i++)
{
    //first line in excel
    if(totalCount == 0) 
    {
        totalCount = 32; 
        signCount = 1;
        excelWsExport.Cells[totalCount, 2] = (DateTime)dviewExport[0, i].Value;
        excelWsExport.Cells[totalCount, 3] = 1;
        excelWsExport.Cells[totalCount, 4] = dviewExport["TypKar", i].Value;
        continue;
    }
    //value is same = just increment
    if((excelWsExport.Cells[totalCount, 4] as Excel.Range).Value.ToString() == dviewExport["TypKar", i].Value.ToString())
    {
        excelWsExport.Cells[totalCount, 3] = (excelWsExport.Cells[totalCount, 3] as Excel.Range).Value + 1;
        signCount++;
        if(maxCount < signCount)
            maxCount = signCount;
    }
    //value changed = write new line and restart incrementing
    else
    {
        totalCount++;
        signCount = 1;
        excelWsExport.Cells[totalCount, 2] = (DateTime)dviewExport[0, i].Value;
        excelWsExport.Cells[totalCount, 3] = 1;
        excelWsExport.Cells[totalCount, 4] = dviewExport["TypKar", i].Value;
    }
}

Problem is, that I write it to excel and when data have several thousands of rows it takes a lot of time.

Is it possible to speed it up with excel interop - write it to array and then paste array to excel / sql / ling or anything else?

I tried to find similar problem and get some answers but I don't know how to describe my problem.

have you thought about using a `Parallel.For` loop, I did something similar recently and reduced my run-time from 35 min to 5 minutes https://learn.microsoft.com/en-us/dotnet/standard/parallel-programming/how-to-write-a-simple-parallel-for-loop. it doesn't seem like the order matters so this should work for you — Andrei, Nov 14 '18 at 14:31
Excel Interop can only *slow* things down by *at least* an order of magnitude, as each call is a cross-process call. If you want to generate an Excel file you don't need a DataGridView either. You can use a library like EPPlus to create real Excel files without using Excel very easily. For example, loading a DataTable into a sheet is as easy as `sheet.Cells.LoadFromDataTable(dataTable);` — Panagiotis Kanavos, Nov 14 '18 at 14:42
@Andrei this won't help at all. You can't access the same Excel instance from multiple clients/threads like this — Panagiotis Kanavos, Nov 14 '18 at 14:43
Data I load to dgv are either from Access db or SQL Server. Also I have to send it to excel, because of next work afterwards — Stana Macala, Nov 14 '18 at 14:58

score 0 · Answer 1 · answered Nov 14 '18 at 14:38

In one of the applications I'm working on right now I use something similar to:

string connectionString = "my connection string";



            for (int i = 0; i < dataGridView1.RowCount - 1; i++)
            {
                DataGridViewRow row = dataGridView1.Rows[i];

                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();

                try
                {

                    var queryString = "INSERT INTO [SQLdb] " +
                        "(columnNamesInDB) " +
                        "VALUES (@dataBeingRead)";
                    SqlCommand comm = new SqlCommand(queryString, conn);
                    comm.ExecuteNonQuery();
                    comm.Close();
                  }
catch (Exception e)
{
//catch behavior
}

To loop through every value in the grid view and insert into an SQL server. Works pretty quickly for our purposes (~1000 range currently).

The question is *not* about storing to the database. – Panagiotis Kanavos Nov 14 '18 at 14:44 — Panagiotis Kanavos, Nov 14 '18 at 14:44

score 0 · Answer 2 · answered Nov 16 '18 at 11:36

Based on Export a C# List of Lists to Excel I managed to fast things up by creating generic lists, then pasting it to object lists with two dimensions and then these created lists to excel range. This is way more faster than writing each time to excel cell.

Problem is that Excel does not like List<T> or either list[]. You have to send to excel object[,] (two dimensional) and since I had just one dimension, I made second dimesion 1.

//create generic lists
List<DateTime> listDate = new List<DateTime>();                                     
List<int> listSize = new List<int>();                                       
List<string> listSign = new List<string>();                                     

//fill lists with data from wherever
for(int i = 0; i < dviewExport.RowCount; i++)                                       
{                                       
    if(listSign.Count == 0)                             
    {                               
        signCount = 1;                          
        listDate.Add((DateTime)dviewExport[0, i].Value);                            
        listSize.Add(1);                            
        listSign.Add((string)dviewExport[$"{Sign}", i].Value);                      
        continue;                           
    }                               
    if(listSign[listSign.Count - 1] == dviewExport[$"{Sign}", i].Value.ToString())                              
    {                               
        listSize[listSize.Count - 1] += 1;                          
        signCount++;                            
        if(maxCount < signCount)                            
            maxCount = signCount;                       
    }                               
    else                                
    {                               
        signCount = 1;                          
        listDate.Add((DateTime)dviewExport[0, i].Value);                            
        listSize.Add(1);                            
        listSign.Add((string)dviewExport[$"{Sign}", i].Value);                          
    }                               
}       

//create two dimensional object lists with size of generic lists
object[,] outDate = new object[listDate.Count, 1];                                      
object[,] outSize = new object[listSize.Count, 1];                                      
object[,] outSign = new object[listSign.Count, 1];                                      

//fill two dimensional object lists with data from generic lists
for(int row = 0; row < listDate.Count; row++)
{
    outDate[row, 0] = listDate[row];                                    
    outSize[row, 0] = listSize[row];                                    
    outSign[row, 0] = listSign[row];
}                           

//set Excel ranges and paste lists
range = excelWsExport.get_Range($"B32:B{32 + listDate.Count}", Type.Missing);                                       
range.NumberFormat = "d.MM.yyyy H:mm:ss";                                       
range.Value = outDate;                                      

range = excelWsExport.get_Range($"C32:C{32 + listSize.Count}", Type.Missing);                                       
range.Value = outSize;                                      

range = excelWsExport.get_Range($"D32:D{32 + listSign.Count}", Type.Missing);                                       
range.Value = outSign;

Creating groups/blocks from table based on value

2 Answers2