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