Xlsio and parallel.for is possible?

[Manolo]

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]

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]

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]

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]

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