c# Winform Make Pivot Table from Imported Excel file

[Booney440]

I populate a Excel file to a DataGridView1, I have a DataGridView2 I want to do a pivot Table to the file that's imported.

C# Pivot Table - CodeProject

I have found a program that show how to do so but it's all Hard coded , I need to do it from the file uploaded.

My data table is

#region Data Table
public static DataTable GetDataTableExcel(string strFileName, string Table)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");
conn.Open();
string strQuery = "SELECT * FROM [" + Table + "]";
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataSet ds = new System.Data.DataSet();
adapter.Fill(ds);
return ds.Tables[0];
}



public static string[] GetTableExcel(string strFileName)
{
string[] strTables = new string[100];
Catalog oCatlog = new Catalog();
ADOX.Table oTable = new ADOX.Table();
ADODB.Connection oConn = new ADODB.Connection();
oConn.Open(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";");
oCatlog.ActiveConnection = oConn;
if (oCatlog.Tables.Count > 0)
{
int item = 0;
foreach (ADOX.Table tab in oCatlog.Tables)
{
if (tab.Type == "TABLE")
{
strTables[item] = tab.Name;
item++;
}
}
}
return strTables;
}

form load

private void Form1_Load(object sender, EventArgs e)
{


dt.Columns.Add("ZN", Type.GetType("System.String"));
dt.Columns.Add("RawLnrFtDay", Type.GetType("System.String"));

// dt.Rows.Add(new object[] { dataGridView1.Rows.Cells[1].Value });


dt.Rows.Add(new object[] { "", "" });
dt.Rows.Add(new object[] { "", "" });


dataGridView2.DataSource = dt;

foreach (DataColumn dc in dt.Columns)
cboX.Items.Add(dc.ColumnName);
foreach (DataColumn dc in dt.Columns)
cboY.Items.Add(dc.ColumnName);
foreach (DataColumn dc in dt.Columns)
cboZ.Items.Add(dc.ColumnName);


}





Booney440

Continue reading...