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

Last Reply one month ago By dharmendr

Posted one month 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();
                            }                           
                        }                        
                    } 

 

 

You are viewing reply posted by: dharmendr one month ago.
Posted one month ago Modified on one month ago

Hi telldurges,

The value of the DataType property is null for numeric and date types. It contains the value CellValues.SharedString for strings and CellValues.Boolean for Boolean values.

There is a way to distinguish between date and number cell formats using the NumberFormatId on the CellFormat and convert the number cantains in the Date cell to Date Format.

Check this example. Now please take its reference and correct your code.

HTML

<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnImport" runat="server" Text="Import" OnClick="ImportExcel" />
<hr />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

Namespaces

C#

using System.IO;
using System.Data;
using System.Collections.Generic;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

VB.Net

Imports System.IO
Imports System.Data
Imports System.Collections.Generic
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet

Code

C#

protected void ImportExcel(object sender, EventArgs e)
{
    //Save the uploaded Excel file.
    string filePath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
    FileUpload1.SaveAs(filePath);
    string AddQuery = "";
    int SheetNumber = 1;
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, false))
    {
        WorkbookPart wbPart = doc.WorkbookPart;
        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 = 3;// 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)
        {
            //Use the first row to add columns to DataTable.
            if (row.RowIndex.Value == 1)
            {
                //map.Count = RCount;
                //query = obj.SaveFileName(map);
                AddQuery += "IF OBJECT_ID('dbo." + "Test" + "', 'U') IS NULL ";
                AddQuery += "BEGIN ";
                AddQuery += "CREATE TABLE [dbo].[" + "Test" + "](";
                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
            {
                // Read dynamic row from excel given by user input.
                if (row.RowIndex.Value > Rnumber)
                {
                    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++;
                    }
                }
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

private enum Formats
{
    General = 0,
    Number = 1,
    Decimal = 2,
    Currency = 164,
    Accounting = 44,
    DateShort = 14,
    DateLong = 165,
    Time = 166,
    Percentage = 10,
    Fraction = 12,
    Scientific = 11,
    Text = 49
}

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;
    }
    else if (cell.DataType == null) // number & dates.
    {
        int styleIndex = (int)cell.StyleIndex.Value;
        CellFormat cellFormat = doc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[int.Parse(cell.StyleIndex.InnerText)] as CellFormat;
        uint formatId = cellFormat.NumberFormatId.Value;

        if (formatId == (uint)Formats.DateShort || formatId == (uint)Formats.DateLong)
        {
            double oaDate;
            if (double.TryParse(cell.InnerText, out oaDate))
            {
                value = DateTime.FromOADate(oaDate).ToShortDateString();
            }
        }
        else
        {
            value = cell.InnerText;
        }
    }
    return value;
}

public string RemoveSpecialCharacters(string text)
{
    return System.Text.RegularExpressions.Regex.Replace(text, @"(\s+|\*|\#|\@|\$)", "");
}

VB.Net

Protected Sub ImportExcel(ByVal sender As Object, ByVal e As EventArgs)
    'Save the uploaded Excel file.
    Dim filePath As String = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
    FileUpload1.SaveAs(filePath)
    Dim AddQuery As String = ""
    Dim SheetNumber As Integer = 1

    Using doc As SpreadsheetDocument = SpreadsheetDocument.Open(filePath, False)
        Dim wbPart As WorkbookPart = doc.WorkbookPart
        Dim sheet As Sheet = CType(doc.WorkbookPart.Workbook.Sheets.ChildElements.GetItem(SheetNumber - 1), Sheet)
        Dim worksheet As Worksheet = (TryCast(doc.WorkbookPart.GetPartById(sheet.Id), WorksheetPart)).Worksheet
        Dim rows As IEnumerable(Of Row) = worksheet.GetFirstChild(Of SheetData)().Descendants(Of Row)()
        Dim Rnumber = 3 'in this Rnumber suppose my row is start from 4'th row then this value is come dynamic from textbox
        Dim dt As DataTable = New DataTable()

        For Each row As Row In rows
            'Use the first row to add columns to DataTable.
            If row.RowIndex.Value = 1 Then
                'map.Count = RCount;
                'query = obj.SaveFileName(map);
                AddQuery += "IF OBJECT_ID('dbo." & "Test" & "', 'U') IS NULL "
                AddQuery += "BEGIN "
                AddQuery += "CREATE TABLE [dbo].[" & "Test" & "]("

                For Each cell As Cell In row.Descendants(Of Cell)()
                    dt.Columns.Add(RemoveSpecialCharacters(GetValue(doc, cell)))
                    AddQuery += "[" & RemoveSpecialCharacters(GetValue(doc, cell)).ToString() & "]" & " VARCHAR(MAX),"
                Next

                AddQuery = AddQuery.TrimEnd(","c)
                AddQuery += ")"
                AddQuery += " END"
            Else
                ' Read dynamic row from excel given by user input.
                If row.RowIndex.Value > Rnumber Then
                    dt.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As Cell In row.Descendants(Of Cell)()
                        dt.Rows(dt.Rows.Count - 1)(i) = RemoveSpecialCharacters(GetValue(doc, cell)).ToString()
                        i += 1
                    Next
                End If
            End If
        Next

        GridView1.DataSource = dt
        GridView1.DataBind()
    End Using
End Sub

Private Enum Formats
    General = 0
    Number = 1
    [Decimal] = 2
    Currency = 164
    Accounting = 44
    DateShort = 14
    DateLong = 165
    Time = 166
    Percentage = 10
    Fraction = 12
    Scientific = 11
    Text = 49
End Enum

Private Function GetValue(ByVal doc As SpreadsheetDocument, ByVal cell As Cell) As String
    Dim value As String = cell.CellValue.InnerText
    If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
        Return doc.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements.GetItem(Integer.Parse(value)).InnerText
    ElseIf cell.DataType Is Nothing Then ' number & dates.
        Dim styleIndex As Integer = CInt(cell.StyleIndex.Value)
        Dim cellFormat As CellFormat = TryCast(doc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements(Integer.Parse(cell.StyleIndex.InnerText)), CellFormat)
        Dim formatId As UInteger = cellFormat.NumberFormatId.Value

        If formatId = CUInt(Formats.DateShort) OrElse formatId = CUInt(Formats.DateLong) Then
            Dim oaDate As Double

            If Double.TryParse(cell.InnerText, oaDate) Then
                value = DateTime.FromOADate(oaDate).ToShortDateString()
            End If
        Else
            value = cell.InnerText
        End If
    End If
    Return value
End Function

Public Function RemoveSpecialCharacters(ByVal text As String) As String
    Return System.Text.RegularExpressions.Regex.Replace(text, "(\s+|\*|\#|\@|\$)", "")
End Function