Step-by-step resolution:
Step 1:
I passed the list data to a pre-formatted DataTable
(same as the image of the question) .
var dtable = new DataTable();
dtable.Columns.Add("Mercado", Type.GetType("System.String"));
dtable.Columns.Add("NSemana", Type.GetType("System.Int32"));
dtable.Columns.Add("Variacao", Type.GetType("System.Decimal"));
foreach(var linha in relatorio.Futuros.Semanas)
{
foreach(var registro in linha.MercadoDaSemana)
{
dtable.Rows.Add(new object[] { registro.Mercado.Simbolo, linha.SemanaDoAno, registro.TotalVariacaoMercado });
}
}
Step 2:
I pivoted DataTable
with the following algorithm:
public static class FormatarDados
{
public static DataTable ParaPivotTable(DataTable table, string columnX, string columnY, string columnZ, string nullValue, bool sumValues)
{
DataTable returnTable = new DataTable();
if(columnX == string.Empty) { columnX = table.Columns[0].ColumnName; }
returnTable.Columns.Add(columnY);
List<string> columnXValues = new List<string>();
foreach(DataRow dr in table.Rows)
{
string columnXTemp = dr[columnX].ToString();
if (!columnXValues.Contains(columnXTemp))
{
columnXValues.Add(columnXTemp);
returnTable.Columns.Add(columnXTemp);
}
}
if (columnY != string.Empty && columnZ != string.Empty)
{
List<string> columnYValues = new List<string>();
foreach (DataRow dr in table.Rows)
{
if (!columnYValues.Contains(dr[columnY].ToString()))
{
columnYValues.Add(dr[columnY].ToString());
}
}
foreach (string columnYValue in columnYValues)
{
DataRow drReturn = returnTable.NewRow();
drReturn[0] = columnYValue;
DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");
foreach (DataRow dr in rows)
{
string rowColumnTitle = dr[columnX].ToString();
foreach (DataColumn dc in returnTable.Columns)
{
if (dc.ColumnName == rowColumnTitle)
{
if (sumValues)
{
try
{
drReturn[rowColumnTitle] = Convert.ToDecimal(drReturn[rowColumnTitle]) + Convert.ToDecimal(dr[columnZ]);
}
catch
{
drReturn[rowColumnTitle] = dr[columnZ];
}
}
else
{
drReturn[rowColumnTitle] = dr[columnZ];
}
}
}
}
returnTable.Rows.Add(drReturn);
}
}
else
{
throw new Exception("Não existem colunas para fazer a inversão");
}
if (nullValue != string.Empty)
{
foreach(DataRow dr in returnTable.Rows)
{
foreach(DataColumn dc in returnTable.Columns)
{
if(dr[dc.ColumnName].ToString() == string.Empty)
{
dr[dc.ColumnName] = nullValue;
}
}
}
}
return returnTable;
}
}
Step 3:
I called the function ParaPivotTable
:
var tblRetorno = FormatarDados.ParaPivotTable(dtable, "NSemana", "Mercado", "Variacao", "-", false);
Step 4: *
I saved the pivot table data tblRetorno
into a .csv:
CommonEngine.DataTableToCsv(tblRetorno, pathArquivo, ',');
* I've used FileHelpers .
The algorithm of step 2 was the second example of this post here .