I am having an excel file with multiple number of sheets to read to dataset.
I want to read each of the sheets to dataset tables, and to create the records for table to table values reference, and finally need to add all these to a n sql datatable.
Is there anyone to help me out?
Loading
Jeetendra GundPosted Sep 16, 2013, 8:01 AM
try this code,
public void CreateMasterTable(byte[] ExcelFile, DatabaseDetails authenticationToken, string tableName)
{
try
{
if (authenticationToken != null && authenticationToken.DatabaseDetail != null)
{
if (null == ExcelFile)
throw new Exception();
SqlConnection conn = new SqlConnection();
ConnectionString ="Data Source=jeetendra;" +
"Initial Catalog=Employee;" +
"User id=sa;" +
"Password=server@2010;
conn.Open();
if (
IntegraObjectContext.Database.SqlQuery
"Select count(*) from INFORMATION_SCHEMA.COLUMNS where table_name='" + tableName + "s'").
FirstOrDefault() > 0)
{
IntegraObjectContext.Database.ExecuteSqlCommand("DROP TABLE " + tableName + "s");
}
var UnCompressedFabricMasterExcelFile = ConversionHelper.GetUnCompressedData(ExcelFile);
var tempPath = Path.GetTempPath();
tempPath += "Excel\\";
if (!Directory.Exists(tempPath))
Directory.CreateDirectory(tempPath);
var fileName = Guid.NewGuid();
ConversionHelper.ConvertByteDataToImage(tempPath + fileName + ".xls",
UnCompressedFabricMasterExcelFile);
var Source = tempPath + fileName + ".xls";
var ColumnNameTypeList = ReadExcelSheet.GetColumnNamesFromExcelFile(Source, tableName);
#region Working For Fields and AlterTableCommand
string alterTableCommand = null;
//" ALTER TABLE [dbo].[" + tableName + "s] ADD CONSTRAINT [" + tableName + "_" + tableName + "Id] DEFAULT (newid()) FOR [" + tableName + "Id] ";
string fields = null; //"[" + tableName + "Id] UNIQUEIDENTIFIER NOT NULL,";
foreach (var colNameWithType in ColumnNameTypeList)
{
if (string.IsNullOrEmpty(colNameWithType.ColumnName) ||
string.IsNullOrEmpty(colNameWithType.ColumnType)) continue;
string strDefault = null;
if ((colNameWithType.ColumnType.Contains("nvarchar") ||
colNameWithType.ColumnType.Contains("nchar") || colNameWithType.ColumnType.Equals("ntext") ||
colNameWithType.ColumnType.Equals("text") ||
colNameWithType.ColumnType.Contains("varchar")))
{
strDefault = "'NA'";
}
var allowNull = "NULL";
if (!colNameWithType.AllowNull)
allowNull = "NOT NULL";
fields += "[" + colNameWithType.ColumnName + "] " + colNameWithType.ColumnType + " " + allowNull +
",";
if (!string.IsNullOrEmpty(strDefault))
alterTableCommand += " ALTER TABLE [dbo].[" + tableName + "s] ADD CONSTRAINT [" + tableName +
"_" + colNameWithType.ColumnName + "] DEFAULT (" + strDefault +
") FOR [" + colNameWithType.ColumnName + "] ";
}
if (fields != null) fields = fields.Remove(fields.Length - 1);
fields = fields + ")";
//fields = fields + " CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED ( [" + tableName +
// "Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] ";
#endregion Working For Fields and AlterTableCommand
var createTableQuery = "CREATE TABLE [" + tableName + "s] (" + fields + alterTableCommand;
SqlCommand cmd = new SqlCommand("",ConnectionString );
cmd.CommandType = CommandType.Text;
cmd.CommandText = createTableQuery ;
var nCount = cmd.ExecuteNonQuery();
conn.Close();
}
return;
}
catch (Exception exception)
{
CLog.ExceptionError(exception);
throw;
}
}
Regards,
Jeetendra
Suthish NairPosted Sep 13, 2013, 8:22 AM
Mohan GopiPosted Sep 13, 2013, 5:27 AM
For Reading Excel to Dataset:
Call this below function and it will return Dataset.
DataSet dsExcelValue=ImportExceltoDataset(ExcelFilePath)
public static DataSet ImportExceltoDataset(string fileName)
{
Microsoft.Office.Interop.Excel.Application oXL;
Workbook oWB;
Worksheet oSheet;
Range oRng;
// creat a Application object
oXL = new Microsoft.Office.Interop.Excel.Application();
try
{
// get WorkBook object
oWB = oXL.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
// get WorkSheet object
int M = oXL.Worksheets.Count;
for (int sheetnum = 1; sheetnum <= M; sheetnum++)
{
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[sheetnum];
for (int j = oSheet.UsedRange.Cells.Columns.Count + 10; j > 0; j--)
{
if (j > oSheet.UsedRange.Cells.Columns.Count)
{
if (string.IsNullOrEmpty(((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).Text.ToString()))
{
((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).EntireColumn.Delete(null);
}
}
else
{
if (string.IsNullOrEmpty(((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).Text.ToString()))
{
int IsDeleteCunt = 1;
for (int i = 1; i < oSheet.UsedRange.Cells.Rows.Count; i++)
{
if (string.IsNullOrEmpty(((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j]).Text.ToString()))
{
IsDeleteCunt++;
}
else
{
break;
}
}
if (IsDeleteCunt == oSheet.UsedRange.Cells.Rows.Count)
{
((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j]).EntireColumn.Delete(null);
}
}
}
}
}
DataSet ds = new DataSet();
string WrkshtName = "";
for (int N = 1; N <= M; N++)
{
oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.Sheets[N];
WrkshtName = oSheet.Name;
System.Data.DataTable dt = new System.Data.DataTable(WrkshtName);
ds.Tables.Add(dt);
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = oSheet.UsedRange.Cells.Columns.Count;
int iValue = oSheet.UsedRange.Cells.Rows.Count;
int EmptyColumnCount = 1;
// get data columns
for (int j = 1; j <= jValue; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j];
string strValue = oRng.Text.ToString();
if (strValue.Trim() == "")
{
EmptyColumnCount++;
}
dt.Columns.Add(strValue, System.Type.GetType("System.String"));
}
if (EmptyColumnCount >= jValue)
{
ds.Tables.Remove(WrkshtName);
}
else
{
//get data in cell
for (int i = 2; i <= iValue; i++)
{
dr = ds.Tables[WrkshtName].NewRow();
int k = 0;
EmptyColumnCount = 1;
for (int j = 1; j <= jValue; j++)
{
oRng = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i, j];
((Range)oSheet.Cells[1, j]).EntireColumn.AutoFit();
string strValue = oRng.Text.ToString();
if (strValue.Trim() == "")
{
EmptyColumnCount++;
}
dr[k] = strValue;
k++;
}
if (EmptyColumnCount < jValue)
{
ds.Tables[WrkshtName].Rows.Add(dr);
}
}
}
}
return ds;
}
catch (Exception ex)
{
return null;
}
}
Form DataSet to Sql Database:
Pass your output Dataset as Datatable by Datatable to this function, it will create table in Database and Dump DataTable Data into sql Table.
public void LoadImportData(DataTable dtCol, string TblName)
{
SqlConnection con =new SqlConnection(connection string); // Your Sql Server Connection.
if (CreateTableInDatabase(dtCol, TblName))
{
SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = TblName;
bc.BatchSize = dtCol.Rows.Count;
con.Open();
bc.WriteToServer(dtCol);
bc.Close();
con.Close();
}
}
public bool CreateTableInDatabase(DataTable dtSchemaTable, string tableName)
{
int i = 0;
string ctStr = "";
int EmptyHearder = 0;
ctStr = "CREATE TABLE [dbo].[" + tableName + "](\r\n";
foreach (DataColumn ddt in dtSchemaTable.Columns)
{
i++;
string MyString = "";
if (ddt.ColumnName.Trim() != "")
{
MyString = ddt.ColumnName;
}
else
{
EmptyHearder++;
MyString = "EmptyHeader_" + EmptyHearder.ToString();
}
if (!Char.IsLetter(MyString.Trim()[0]))
{
MyString = MyString.Remove(0, 1);
}
MyString = MyString.Replace('(', '_');
MyString = MyString.Replace(")", "");
MyString = RemoveSpecialCharacters(MyString);
ddt.ColumnName = MyString;
string dataty = ddt.DataType.Name.ToString().Trim().ToLower();
if (dataty == "string" || dataty == "" || dataty == "object")
{
ctStr += " [" + ddt.ColumnName.Trim() + "][nvarchar](MAX) NULL";
}
else if (dataty.Substring(0, 3) == "int")
{
bool isVar = false;
for (int j = 0; j < dtSchemaTable.Rows.Count; j++)
{
string dt = dtSchemaTable.Rows[j][ddt].ToString();
if (dt.Length > 10)
{
isVar = true;
}
}
if (isVar == false)
{
ctStr += " [" + ddt.ColumnName.Trim() + "][bigint] NULL";
}
else
{
ctStr += " [" + ddt.ColumnName.Trim() + "][nvarchar](MAX) NULL";
}
}
else if (dataty.Substring(0, 3) == "byt")
{
ctStr += " [" + ddt.ColumnName.Trim() + "][image] NULL";
}
else if (dataty == "real" || dataty == "double")
{
ctStr += " [" + ddt.ColumnName.Trim() + "][float] NULL";
}
else if (dataty == "boolean")
{
ctStr += " [" + ddt.ColumnName.Trim() + "][Bit] NULL";
}
else if (dataty == "date")
{
ctStr += " [" + ddt.ColumnName.Trim() + "][date] NULL";
}
else
{
ctStr += " [" + ddt.ColumnName.Trim() + "][" + ddt.DataType.Name.Trim() + "] NULL";
}
if (i <= dtSchemaTable.Columns.Count)
{
ctStr += ",";
}
ctStr += "\r\n";
}
ctStr += " [S_No] [bigint] IDENTITY(1,1) NOT NULL , \r\n ";
ctStr += " [IconID] [Image] NULL \r\n ) ; ";
SqlConnection conn = objCommonDAL.GetConnection();
SqlCommand command = conn.CreateCommand();
command.CommandText = ctStr;
conn.Open();
command.ExecuteNonQuery();
conn.Close();
return true;
}
public static string RemoveSpecialCharacters(string str)
{
StringBuilder sb = new StringBuilder();
foreach (char c in str)
{
if ((c >= '0' && c <= '9') || (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z') || c == '_')
{
sb.Append(c);
}
}
return sb.ToString();
}
I hope this will help you.
Thank's
Mohan G
Sanjeeb LenkaPosted Sep 13, 2013, 4:42 AM
Refer to this link it may help you. there is a example matching your requirement.
http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx