请安装office驱动。
public static DataSet ExcelToDS(string Pathm, string TableName, string type)
{
DataSet ds = new DataSet();
string strConn = "";
if (type == "excel2003")
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Pathm + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
}
else if (type == "excel2007")
{
strConn = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + Pathm + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
}
try
{
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", TableName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, TableName);
}
catch (Exception)
{
return ds;
}
return ds;
}
public void DataSet()
{
string type2 = FileUpload1.FileName;
var type3 = type2.Substring(type2.LastIndexOf('.'));
if(type3.Equals(".xls") || type3.Equals(".xlsx"))
{
string newName = Server.MapPath("..//App_Data//Uploads//") + DateTime.Now.ToString("hhmmss") + ".xls";
FileUpload1.SaveAs(newName);
string connStr = string.Empty;
if (type3.Equals(".xlsx"))
{
connStr = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + newName +
";Extended Properties='Excel 12.0 Xml; HDR=YES; IMEX=1'";
}
else if (type3.Equals(".xls"))
{
connStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + newName +
";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
}
OleDbConnection conn = new OleDbConnection(connStr);
if (conn.State.ToString() == "Closed")
{
conn.Open();
}
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
var tableName = dt.Rows[0][2].ToString().Trim();
var str1 = string.Format("Select * from [{0}]", tableName+"A:E");
OleDbDataAdapter oda = new OleDbDataAdapter(str1, conn);
DataSet ds = new DataSet();
oda.Fill(ds);
conn.Close();
File.Delete( newName );
return ds
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace _8
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 带事务的批量操作数据。
/// </summary>
/// <param name="sqlInsert">SQL</param>
/// <returns>操作是否成功</returns>
public static bool OperateDatasWithTransaction(List<string> sqlList)
{
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnStr"]);
myConnection.Open();
SqlCommand myCommand = myConnection.CreateCommand();
SqlTransaction myTrans;
// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
// Assign transaction object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
foreach (string item in sqlList)
{
myCommand.CommandText = item;
myCommand.ExecuteNonQuery();
}
myTrans.Commit();
return true;
}
catch (Exception)
{
myTrans.Rollback();
}
finally
{
myConnection.Close();
}
return false;
}
private System.Data.DataTable GetExcelTable(string uploadPath)
{
DataSet ds = new DataSet();
string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";//HDR为yes 则第一数据行为列名,为no 则自动为列加列名F1 F2 F3
OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
try
{
Conn.Open();
string sql_str = "select * from [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
da.Fill(ds, "excel_data");
Conn.Close();
}
catch
{
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
return null;
}
finally
{
Conn.Dispose();
}
if (ds == null)
{
return null;
}
if (ds.Tables.Count < 1)
{
return null;
}
return ds.Tables[0];
}
protected void AddExcel(DataSet ds, ref bool s)
{
try
{
System.Data.DataTable dt = ds.Tables[0];
// string fileName ="C:\\数据处理文件夹\\"+ Guid.NewGuid() + ".xls";
//string str = DateTime.Now.ToShortDateString();
// string[] strar=str.Split(' ');
// string str1 =strar[0]+strar[1];
SaveFileDialog sf = new SaveFileDialog();
sf.InitialDirectory = "C:\\";
sf.Filter = "excel文件（*.xls）|*.xls";
if (sf.ShowDialog()==DialogResult.OK)
{
string fileName = sf.FileName; //"C:\\数据处理文件夹\\" +str+ ".xls";
Excel.Application excel = new Excel.ApplicationClass();
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
for (colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
excel.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
}
excel.Visible = false;
// excel.ActiveWorkbook.s
excel.ActiveWorkbook.SaveAs(fileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//excel.Save(fileName);
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
}
catch
{
s = false;
}
// }
}
private void button1_Click(object sender, EventArgs e)
{
this.openFileDialog1.InitialDirectory = "C:\\";
this.openFileDialog1.Filter = "excel文件（*.xls）|*.xls";
this.openFileDialog1.FilterIndex = 2;
this.openFileDialog1.RestoreDirectory = true;
this.openFileDialog1.Title = "打开excel文件";
if (openFileDialog1.ShowDialog()==DialogResult.OK)
{
string sss = openFileDialog1.FileName;
textBox1.Text = sss;
System.Data.DataTable dt;
//OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["ConnStr"]);
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
//string constr= System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString;
// string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
bool success = true;
dt = GetExcelTable(sss);
success = input(dt, conn, success,"Budget");//总额
// success = input(dt, conn, success, "Budget_Detail");//明细
}
}
private bool input(System.Data.DataTable dt, SqlConnection conn, bool success,string flag)
{
if (dt != null)
{
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
List<string> L = new List<string>();
if (flag == "Budget")
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[0].ToString() != "")
{
string s = "insert into [Budget] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "')";
// OleDbCommand cmd = new OleDbCommand( s,conn);
L.Add(s);
}
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i].ItemArray[0].ToString() != "")
{
string s = "insert into [Budget_Detail] values('" + dt.Rows[i].ItemArray[0] + "','" + dt.Rows[i].ItemArray[1] + "','" + dt.Rows[i].ItemArray[2] + "','" + dt.Rows[i].ItemArray[3] + "','" + dt.Rows[i].ItemArray[4] + "','" + dt.Rows[i].ItemArray[5] + "','" + dt.Rows[i].ItemArray[6] + "','" + dt.Rows[i].ItemArray[9] + "')";
// OleDbCommand cmd = new OleDbCommand( s,conn);
L.Add(s);
}
}
}
if (!OperateDatasWithTransaction(L))
{
success = false;
}
}
catch (Exception eer)
{
MessageBox.Show(eer.ToString());
success = false;
conn.Close();
}
if (success)
{
label1.Text = "导入成功";
if (flag == "Budget")
MessageBox.Show("预算资金总账导入成功！", "信息提示");
else
MessageBox.Show("预算资金明细导入成功!", "信息提示");
}
else
{
label1.Text = "导入失败";
if (flag == "Budget")
MessageBox.Show("预算资金总账导入失败！", "信息提示");
else
MessageBox.Show("预算资金明细导入失败!", "信息提示");
}
}
return success;
}
private void button3_Click(object sender, EventArgs e)
{
cleartb();
}
private void cleartb()
{
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
bool w = true;
SqlConnection conn = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "truncate table [Budget] truncate table [Budget_Detail]";
try
{
cmd.ExecuteNonQuery();
}
catch
{
w = false;
}
if (w)
{
label1.Text = "表已清空";
MessageBox.Show("表已清空","信息提示");
}
else
{ label1.Text = "清空失败"; MessageBox.Show("表清空失败", "信息提示"); }
}
private void cleartbing()
{
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
bool w = true;
SqlConnection conn = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "truncate table [Order]";
try
{
cmd.ExecuteNonQuery();
}
catch
{
w = false;
}
if (!w)
label1.Text = "处理数据失败";
}
private void button2_Click(object sender, EventArgs e)
{
this.openFileDialog2.InitialDirectory = "C:\\";
this.openFileDialog2.Filter = "excel文件（*.xls）|*.xls";
this.openFileDialog2.FilterIndex = 2;
this.openFileDialog2.RestoreDirectory = true;
this.openFileDialog2.Title = "打开excel文件";
if (openFileDialog2.ShowDialog() == DialogResult.OK)
{
string sss = openFileDialog2.FileName;
textBox2.Text = sss;
System.Data.DataTable dt;
//OleDbConnection conn = new OleDbConnection(ConfigurationSettings.AppSettings["ConnStr"]);
string constr = System.Configuration.ConfigurationSettings.AppSettings["ConnStr"];
//string constr= System.Configuration.ConfigurationManager.ConnectionStrings("constr").ConnectionString;
// string constr = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
SqlConnection conn = new SqlConnection(constr);
bool success = true;
dt = GetExcelTable(sss);
//success = input(dt, conn, success, "Budget");//总额
success = input(dt, conn, success, "Budget_Detail");//明细
}
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
}
}
