Reading formatted Date from Excel file with OpenXml using C# and VB.Net in ASP.Net

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Hello Sir,

i have an excel file and i upload that excel these excel is created dynamically table and there data is saved in sql database.

i have an issue suppose excel have 4 column and two of them is date type when i save that excel data then in date column wrong type value is saved and rest value is save properly.

like 

Vendor	   DOB	
DELL	  29271	

this type save but in excel in dob colum the value is 20/02/1980

Sample Data

SalesNO Billing Document Billing Date Doc type GSTN
1000 Afrthuo 02/08/2017 ZFGD 18SDNADAJI12IASJ
2000 Az2587 02/08/2017 ZFGD 19OPAOSDAKDAK1
300 1234fgth 02/08/2017 ZFGD 13JADADJADJAGJD
500 5874LKj 02/08/2017 ZFGD 20AKDHAJDGAJDG
                    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(path, false))
                    {
                        using (DbContext db = new DbContext())
                        {
                            WorkbookPart wbPart = doc.WorkbookPart;
                            string cs = db.CONNECTION_STRING;
                            int worksheetcount = doc.WorkbookPart.Workbook.Sheets.Count();
                            Sheet sheet = (Sheet)doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(SheetNumber-1);                           
                            Worksheet worksheet = (doc.WorkbookPart.GetPartById(sheet.Id) as WorksheetPart).Worksheet;
                            IEnumerable<Row> rows = worksheet.GetFirstChild<SheetData>().Descendants<Row>();
                            var Rnumber = map.RowNumber; // in this Rnumber suppose my row is start from 4'th row then this value is come dynamic from textbox
                            DataTable dt = new DataTable();
                            foreach (Row row in rows)
                            {
                                if (row.RowIndex.Value == Rnumber)
                                {
                                    map.Count = RCount;
                                    query = obj.SaveFileName(map);
                                    AddQuery += "IF OBJECT_ID('dbo." + map.FileName + "', 'U') IS NULL ";
                                    AddQuery += "BEGIN ";
                                    AddQuery += "CREATE TABLE [dbo].[" + map.FileName + "](";
                                    foreach (Cell cell in row.Descendants<Cell>())
                                    {
                                        dt.Columns.Add(RemoveSpecialCharacters(GetValue(doc, cell)));
                                        AddQuery += "[" + RemoveSpecialCharacters(GetValue(doc, cell)).ToString() + "]" + " VARCHAR(MAX),";
                                    }
                                    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] = RemoveSpecialCharacters(GetValue(doc, cell)).ToString();
                                        i++;
                                    }
                                }
                            }                           
                            using (SqlConnection con1 = new SqlConnection(cs))
                            {
                                string newquery = "";                               
                                SqlCommand cmd = new SqlCommand(AddQuery);                                                                
                                cmd.Connection = con1;
                                con1.Open();                                
                                cmd.ExecuteNonQuery();
                                var dtColumn = obj.UpdateColumnDynamic();
                                cmd.CommandTimeout = 0;
                                SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con1);
                                sqlBulkCopy.DestinationTableName = "dbo." + "[" + map.FileName + "]";
                                sqlBulkCopy.BulkCopyTimeout = 0;
                                sqlBulkCopy.BatchSize = 50000;
                                sqlBulkCopy.WriteToServer(dt);
                                newquery = "ALTER TABLE dbo."+ "[" + map.FileName + "]" + " ADD Id INT IDENTITY(1,1) ";
                                SqlCommand sqlCommand = new SqlCommand(newquery);
                                sqlCommand.Connection = con1;
                                var dt1mm1 = obj.CreateSourceListmm(1,map.FileName);
                                sqlCommand.ExecuteNonQuery();                                
                                con1.Close();
                            }                           
                        }                        
                    } 

 

 

This question does not have replies that have been liked.