C# - Error screenshot while adding Data Header column in data table

[Gani tpt]

I am trying to convert excel to data table. Refer attached Excel file ==> Sample ExcelData File

I am getting error "Additional information: A column named 'F' already belongs to this DataTable."

I have posted similar thread, but, it will not exactly match my requirement. that's why posted again here.

Similar post : How to add top header column on top of the data table...?

i have written the below code with adding of new header column like "A","B","C",.... (like excel column header). This will overwrite the existing column and through error.

But, i don't want to overwrite the exiting column. On top the data table column should come like "A","B","C",...

Error screenshot below.




Note : This logic i have to apply in many places in my project and it is critical one.

private void button1_Click_1(object sender, EventArgs e)
{

string sheetName = "sheet1";
string tempdt = "DTtemp";
string path = "C:\\Proj\\Test\\ExcelData.xlsx"; // Attached sample excel file
DataTable dttable = new DataTable();
dttable = XLtoDT(path, Shtname, tempdt);

}

public static DataTable XLtoDT(string XLpath, string sheetName, string dtSource)
{

DataTable dt2 = new DataTable();
DataTable dtResult = null;
int totalSheet = 0; //No of sheets on excel file
using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + XLpath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
{
objConn.Open();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//string sheetName = string.Empty;
if (dt != null)
{
var tempDataTable = (from dataRow in dt.AsEnumerable()
where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
select dataRow).CopyToDataTable();
dt = tempDataTable;
totalSheet = dt.Rows.Count;
//sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
cmd.Connection = objConn;
cmd.CommandType = CommandType.Text;
sheetName = string.Concat(sheetName, "$");
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, dtSource);

dtResult = ds.Tables[dtSource];


//for removing empty rows
dtResult = dtResult.Rows.Cast<DataRow>().Where(row => !row.ItemArray.All(field =>
field is System.DBNull || string.Compare((field as string).Trim(),
string.Empty) == 0)).CopyToDataTable();

dt2 = dtResult;
StripEmptyRows(dt2);


for (int index = 0; index < dt2.Columns.Count; index++)
{
var colIndex = index + 1;
dt2.Columns[index].ColumnName = ExcelColumnNameFromNumber(colIndex).
ToString().Trim(); // Error : Additional information: A column named 'F' already belongs to this DataTable.
}

objConn.Close();
return dt2; //Returning Data table
}

}

public static string ExcelColumnNameFromNumber(int pIndex)
{
var chars = Enumerable.Range(0,26).Select((i) =>
((char)(Convert.ToInt32('A') + i)).ToString()).ToArray();

pIndex -= 1;

string columnName = null;
var quotient = pIndex / 26;

if (quotient > 0)
{
columnName = ExcelColumnNameFromNumber(quotient) + chars[pIndex % 26];
}
else
{
columnName = chars[pIndex % 26].ToString();
}

return columnName;

}



public static DataTable StripEmptyRows(DataTable dt)
{
List<int> rowIndexesToBeDeleted = new List<int>();
int indexCount = 0;
foreach (var row in dt.Rows)
{
var r = (DataRow)row;
int emptyCount = 0;
int itemArrayCount = r.ItemArray.Length;
foreach (var i in r.ItemArray) if (string.IsNullOrWhiteSpace(i.ToString())) emptyCount++;

if (emptyCount == itemArrayCount) rowIndexesToBeDeleted.Add(indexCount);

indexCount++;
}

int count = 0;
foreach (var i in rowIndexesToBeDeleted)
{
dt.Rows.RemoveAt(i - count);
count++;
}

return dt;
}


Final output would be like below in data table format.



Waiting for favorable solution...pls. help.

Continue reading...