将Excel文件导出到Sql表 [英] Export Excel file to Sql table

问题描述

嗨 请帮助我如何完成这项任务.
1使用Sql表创建excel文件.
2使用该Excel文件，我想将数据放入Windows c#中的Sql表中.
在此先感谢
Phoolchand y.

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.

推荐答案

将数据从Excel导入到SQL Server [ ^ ]

这可能会帮助....

将数据导出到CSV/Excel的C#类库文件 [ ^ ]

this might help....

C# class library for exporting data to CSV/Excel file[^]

首先在项目中添加对excel名称空间的引用.
使用Excel = Microsoft.Office.Interop.Excel;

这是供Sql Table生成Excel文件.
使用系统；
使用System.Collections.Generic;
使用System.ComponentModel;
使用System.Data;
使用System.Drawing;
使用System.Linq;
使用System.Text;
使用System.Windows.Forms;
使用System.Data.SqlClient;
使用RKLib.ExportData;
使用System.IO;
使用System.Data.OleDb;
使用System.Data.Common;
使用Excel = Microsoft.Office.Interop.Excel;

命名空间TextToExcelWindows
{
公共局部类Form2:Form
{
公共Form2()
{
InitializeComponent();
}

private void button1_Click(对象发送者，EventArgs e)
{

SqlConnection cnn;
字符串connectionString;
字符串sql;
int i;
int j;

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

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

connectionString =服务器= AKS13 \\ SQL2005; uid = sa; pwd = sql2005;数据库= ExcelDB;";
cnn =新的SqlConnection(connectionString);
cnn.Open();
sql =从tbMapping中选择ExcelColumns";
SqlDataAdapter dscmd =新的SqlDataAdapter(sql，cnn);
DataTable dt = new DataTable();
dscmd.Fill(dt);

//标头.
对于(i = 0; i< dt.Columns.Count; i ++)
{
xlWorkSheet.Cells [1，i + 1] = dt.Columns [i] .ColumnName;
}



//内容
对于(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文件，您可以找到文件C:\\ mapping.xls");

}
私有void releaseObject(object obj)
{
试试
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch(ex ex例外)
{
obj = null;
MessageBox.Show(释放对象时发生异常" + ex.ToString());
}
终于
{
GC.Collect();
}
}


这是一个将Excel文件保存到sql表的项目，将数据保存到sql表

字符串文件="C:\\ informations2.xls";

字符串constring;
constring ="Provider = Microsoft.Jet.OLEDB.4.0;" +数据源=" +文件+;" +扩展属性= \" Excel 8.0; IMEX = 1 \;";
//constring ="Provider = Microsoft.Ace.OLEDB.12.0;" +数据源=" + filepath1 +;" +扩展属性=" Excel 12.0; IMEX = 1 \";
OleDbConnection objConn =新的OleDbConnection(constring);
objConn.Open();
DataTable dtExcelSchema;
dtExcelSchema = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables，null);
字符串SheetName = dtExcelSchema.Rows [0] ["TABLE_NAME"].ToString();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + SheetName +]"，objConn);
OleDbDataAdapter objAdapter1 =新的OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataTable dt = new DataTable();
objAdapter1.Fill(dt);
foreach(在dt.rows中的DataRow dr)
{

SqlConnection cn =新的SqlConnection("Server = AKS13 \\ SQL2005; uid = sa; pwd = sql2005; Database = ExcelDB");
cn.Open();
string cmdstng =插入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 =新的SqlCommand(cmdstng，cn);
cmd.ExecuteNonQuery();
MessageBox.Show(记录添加成功..！");
}

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..!");
}

这篇关于将Excel文件导出到Sql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！