Vfrdtyky

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.

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

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;