Hello Sir,
I browse excel file in excel file column name have space before or after or middle and have special character in column name like
and i am creating dynamic table with excel so i want InvoiceNumber while creating table in c#.
suppse in excel i have 10 column in excel have spaces, special character etc then create excat table.
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
{
using (DbContext db = new DbContext())
{
string cs = db.CONNECTION_STRING;
Sheet sheet = doc.WorkbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id.Value) as WorksheetPart).Worksheet;
IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
DataTable dt = new DataTable();
//bool firstRow = true;
foreach (Row row in rows)
{
if (row.RowIndex.Value == 1)
{
AddQuery += "IF OBJECT_ID('dbo." + map.FileName + "', 'U') IS NULL ";
AddQuery += "BEGIN ";
AddQuery += "CREATE TABLE [dbo].[" + map.FileName + "](";
// AddQuery += "id int identity , ";
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Columns.Add(GetValue(doc, cell));
AddQuery += "[" + GetValue(doc, cell).ToString() + "]" + " VARCHAR(MAX),";
}
//firstRow = false;
AddQuery = AddQuery.TrimEnd(',');
AddQuery += ")";
AddQuery += " END";
}
else
{
dt.Rows.Add();
int i = 0;
foreach (Cell cell in row.Descendants<Cell>())
{
dt.Rows[dt.Rows.Count - 1][i] = GetValue(doc, cell);
i++;
}
}
}
using (SqlConnection con1 = new SqlConnection(cs))
{
string newquery = "";
SqlCommand cmd = new SqlCommand(AddQuery);
cmd.Connection = con1;
con1.Open();
cmd.ExecuteNonQuery();
cmd.CommandTimeout = 0;
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con1);
sqlBulkCopy.DestinationTableName = "dbo." + "[" + map.FileName + "]";
sqlBulkCopy.BulkCopyTimeout = 0;
sqlBulkCopy.BatchSize = 50000;
sqlBulkCopy.WriteToServer(dt);
sqlCommand.Connection = con1;
// con1.Open();
sqlCommand.ExecuteNonQuery();
con1.Close();
}
}
}
private string GetValue(SpreadsheetDocument doc, Cell cell)
{
string value = cell.CellValue.InnerText;
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
{
return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(int.Parse(value)).InnerText;
}
return value;
}
this is code when i browse excel and click save button then it dynamically create table.
if in excel file
Invoice Number* P /O S I nvoice@ Amount #Tax Amou nt Doc Type$
Inv-01 07 1000 100 INV
Inv-02 09 1010 110 invoice
Inv-03 33 1020 120 invioce
Inv-04 45 1030 130 Credit Note
Inv-05 23 1040 140 Revised note
Inv-06 12 1050 150 Debit Note
Inv-06 12 1050 150 Debit Note
Inv-06 12 1050 150 Credit Note
