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 learn.microsoft.com/en-us/dotnet/standard/parallel-programming/…. it doesn't seem like the order matters so this should work for you — Andrei
– Andrei, Commented Nov 14, 2018 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
– Panagiotis Kanavos, Commented Nov 14, 2018 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
– Panagiotis Kanavos, Commented Nov 14, 2018 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
– Stana Macala, Commented Nov 14, 2018 at 14:58

M. Goodman · Accepted Answer · 2018-11-14 14:38:28Z

0

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).

answered Nov 14, 2018 at 14:38

M. Goodman

1519 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Panagiotis Kanavos Over a year ago

The question is not about storing to the database.

Stana Macala · Accepted Answer · 2018-11-16 11:36:26Z

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;

Collectives™ on Stack Overflow

Creating groups/blocks from table based on value

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related