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?
