We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Xlsio and parallel.for is possible?

Thread ID:

Created:

Updated:

Platform:

Replies:

128943 Feb 17,2017 04:27 AM Feb 21,2017 06:48 AM ASP.NET Web Forms 4
loading
Tags: XlsIO
Manolo
Asked On February 17, 2017 04:27 AM

Hi,

I need create an excel, I use this function, but it cost 1 hour. For accelerate the process I've tried to use ParallelFor, but I get an error in xlsio

for (int i = 2; i < rowCount; i++)
            {
                var numero = worksheet.Range[i, 1].Text;

                Parallel.For(55, columnCount, j =>
                {
                    //OJO! si accedo a worksheet.Columns se empieza desde 0
                    //pero con worksheet.Range desde 1
                    var codigoAnomalia = worksheet.Columns[j].Cells.First().Value;
                    var resultadoAnomalia = worksheet.Range[i, j + 1].Text;
                    var descripcionAnomalia = worksheet.Range[i, j + 2].Text;
                    var gravedadAnomalia = worksheet.Range[i, j + 3].Text;

                    sheetAnomalia.InsertRow(i);
                    sheetAnomalia.Range[i, 1].Text = numero;
                    sheetAnomalia.Range[i, 2].Text = codigoAnomalia;
                    sheetAnomalia.Range[i, 3].Text = resultadoAnomalia;
                    sheetAnomalia.Range[i, 4].Text = descripcionAnomalia ?? "";
                    sheetAnomalia.Range[i, 5].Text = gravedadAnomalia ?? "";

                });
                //for (int j = 55; j < columnCount; j+=3)
                //{
                //    //OJO! si accedo a worksheet.Columns se empieza desde 0
                //    //pero con worksheet.Range desde 1
                //    var codigoAnomalia = worksheet.Columns[j].Cells.First().Value;
                //    var resultadoAnomalia = worksheet.Range[i, j + 1].Text;
                //    var descripcionAnomalia = worksheet.Range[i, j + 2].Text;
                //    var gravedadAnomalia = worksheet.Range[i, j + 3].Text;

                //    sheetAnomalia.InsertRow(i);
                //    sheetAnomalia.Range[i, 1].Text = numero;
                //    sheetAnomalia.Range[i, 2].Text = codigoAnomalia;
                //    sheetAnomalia.Range[i, 3].Text = resultadoAnomalia;
                //    sheetAnomalia.Range[i, 4].Text = descripcionAnomalia ?? "";
                //    sheetAnomalia.Range[i, 5].Text = gravedadAnomalia ?? "";
                //}
            }

is it possible use parallel.for in xlsio?

Abirami Varadharajan [Syncfusion]
Replied On February 20, 2017 07:43 AM

Hi Manolo, 

Parallel execution of same instance of workbook or Excel Engine could result in exceptions. It is recommended to use single workbook in a single thread or different workbooks in parallel thread.
 
  
However, we request you to share us the rows and columns count and information on how you process the workbook. We can analyze this further to provide a valid solution to achieve the requirement with improved performance. 
  
Regards, 
Abirami. 


Manolo
Replied On February 20, 2017 11:40 AM

ok,

I've an excel with a lot of columns. I need import these datas into SQL Server

But these columns can be pivoted in rows (each 3 columns is data from one row), so my idea is create a new sheet with the columns pivoted and after import to sql server. The process without parallelize cost 1 hour.

I put the code:

 private string PreprocesarFicheroInspecciones(string filename)
        {
            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
           
            IWorkbook workbook = application.Workbooks.Open(filename);

            IWorksheet worksheet = workbook.Worksheets[0];
            worksheet.Name = "Sheet";
           
           int columnCount = worksheet.Columns.Count();
           int rowCount = worksheet.Rows.Count();

           // new sheet with the columns pivoted
            IWorksheet sheetAnomalia = workbook.Worksheets.Create("Anomalias");
            sheetAnomalia.InsertRow(1);
            sheetAnomalia.Range[1, 1].Text = "Numero";
            sheetAnomalia.Range[1, 2].Text = "CodigoAnomalia";
            sheetAnomalia.Range[1, 3].Text = "ResultadoAnomalia";
            sheetAnomalia.Range[1, 4].Text = "ComentarioAnomalia";
            sheetAnomalia.Range[1, 5].Text = "GravedadAnomalia";

            // I get a list of index for pivot. From column 55 to end, and each 3 columns is a new row
            var indices = Enumerable.Range(55, columnCount).Where(i => i % 3 != 0).ToList();

            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

            for (int i = 2; i < rowCount; i++)
            {
                var numero = worksheet.Range[i, 1].Text;

               // Try parallelize this loop, after parallelize the main loop
                Parallel.ForEach(indices, j =>
                {
                    //worksheet.Columns start from 0 but when I useworksheet.Range from 1
                    // get the values from the original sheet
                    var codigoAnomalia = worksheet.Columns[j].Cells.First().Value;
                    var resultadoAnomalia = worksheet.Range[i, j + 1].Text;
                    var descripcionAnomalia = worksheet.Range[i, j + 2].Text;
                    var gravedadAnomalia = worksheet.Range[i, j + 3].Text;

                    // Save the values in the new sheet
                    sheetAnomalia.InsertRow(i);
                    sheetAnomalia.Range[i, 1].Text = numero;
                    sheetAnomalia.Range[i, 2].Text = codigoAnomalia;
                    sheetAnomalia.Range[i, 3].Text = resultadoAnomalia;
                    sheetAnomalia.Range[i, 4].Text = descripcionAnomalia ?? "";
                    sheetAnomalia.Range[i, 5].Text = gravedadAnomalia ?? "";

                });          
            }          
            stopWatch.Stop();
            TimeSpan ts = stopWatch.Elapsed;
}

Other question... how can I format the code in a post?

Manolo
Replied On February 21, 2017 03:06 AM

Finally, I save all data in a list and after save the data in a new sheet.

It cost seconds, manipullating the sheet 1 hour.

And maybe interesant that Parallel.For works with XLSIO.

Thanks!

Abirami Varadharajan [Syncfusion]
Replied On February 21, 2017 06:48 AM

Hi Manolo, 
 
Thank you for updating us. 
 
We are glad that issue is resolved at your end. It would be grateful if you could update the code that resolve the issue. 
 
Regarding formatting the code: 
 
Currently, we do not have the option to format the code snippets in a forum post. We will consider this as a feature request and include this in our website. 
 
Regards, 
Abirami 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;