Click here to Skip to main content
15,877,384 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi plz help me how i can do this assignment.
1 using Sql table to create excel file.
2 With that Excel file i want to put data to Sql table in windows c#.

Thanks in Advance
Phoolchand y.
Posted
Updated 17-Feb-12 21:55pm
v9

 
Share this answer
 
Comments
Varun Sareen 18-Feb-12 4:13am    
my vote 5: good link
 
Share this answer
 
Comments
phoolchand yadav 17-Feb-12 4:45am    
Here i want Excel file to sql table
First to make a add reference to excel namespace in your project .
using Excel = Microsoft.Office.Interop.Excel;

This is for Sql Tables to genrate Excel file.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using RKLib.ExportData;
using System.IO;
using System.Data.OleDb;
using System.Data.Common;
using Excel = Microsoft.Office.Interop.Excel;

namespace TextToExcelWindows
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{

SqlConnection cnn;
string connectionString ;
string sql;
int i;
int j;

Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.ApplicationClass();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

connectionString = "Server=AKS13\\SQL2005;uid=sa;pwd=sql2005;Database=ExcelDB;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "select ExcelColumns from tbMapping";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataTable dt = new DataTable();
dscmd.Fill(dt);

// Headers.
for (i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}



// Content
for (i = 0; i < dt.Rows.Count; i++)
{
for (j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}

xlWorkBook.SaveAs("C:\\mapping.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

MessageBox.Show("Excel file created , you can find the file C:\\mapping.xls");

}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}


This one for Excel file to Sql table interms of saving data to sql table

string file = "C:\\informations2.xls";

string constring;
constring = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + file + ";" + "Extended Properties=\"Excel 8.0;IMEX=1\";";
// constring = "Provider=Microsoft.Ace.OLEDB.12.0;" + "Data Source=" + filepath1 + ";" + "Extended Properties='Excel 12.0;IMEX=1\'";
OleDbConnection objConn = new OleDbConnection(constring);
objConn.Open();
DataTable dtExcelSchema;
dtExcelSchema = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + SheetName + "]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataTable dt = new DataTable();
objAdapter1.Fill(dt);
foreach (DataRow dr in dt.Rows)
{

SqlConnection cn = new SqlConnection("Server=AKS13\\SQL2005;uid=sa;pwd=sql2005;Database=ExcelDB");
cn.Open();
string cmdstng = "insert into SqlToExcel(Id,Fname,Lname,Address,City,State,TelephoneNo)values('" + dr.ItemArray[0] + "','" + dr.ItemArray[1] + "','" + dr.ItemArray[2] + "','" + dr.ItemArray[3] + "','" + dr.ItemArray[4] + "','" + dr.ItemArray[5] + "','" + dr.ItemArray[6] + "') ";
SqlCommand cmd = new SqlCommand(cmdstng, cn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record added Successfully..!");
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900