C#关于EXCEL读取和写入到数据库的问题 数据库是ORACLE1、可不可以把EXCEL的信息读取到datagridview中,然后直接保存到数据库中?2、可不可以通过编写的软件,直接把数据库中的信息显示到EXCEL中? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 using System.Data; using System.Data.OleDb; using System.IO; using System.Text; protected void Page_Load(object sender, EventArgs e) { } public void ExcelToDS(string path) { string strSheetName = "sheet1"; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; //Sql语句 string strExcel = "select * from [" + strSheetName + "$] "; DataSet ds = new DataSet(); //连接数据源 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); //适配到数据源 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn); adapter.Fill(ds, "data"); conn.Close(); GridView1.DataSource = ds.Tables["data"]; GridView1.DataBind(); Label1.Text = ds.Tables[0].Rows.Count.ToString(); SqlAccess.Sqldatabase sql = new SqlAccess.Sqldatabase(); StringBuilder sb = new StringBuilder(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { sb.Append("insert into data values('" + ds.Tables[0].Rows[i]["companyname"] + "','" + ds.Tables[0].Rows[i]["telname"] + "','" + ds.Tables[0].Rows[i]["qytel"] + "','" + ds.Tables[0].Rows[i]["fax"] + "');"); } sql.ExecuteNonQuery(CommandType.Text, sb.ToString()); } protected void Button1_Click(object sender, EventArgs e) { string fileName = null; try { Boolean fileOK = false; String path = Server.MapPath("./doc/"); if (FileUpload2.HasFile) { String fileExtension = System.IO.Path.GetExtension(FileUpload2.FileName).ToLower(); String[] allowedExtensions = { ".xls" }; //C#读取Excel中数据 for (int i = 0; i < allowedExtensions.Length; i++) { if (fileExtension == allowedExtensions[i]) { fileOK = true; } } } if (fileOK) { fileName = "r_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + "_" + DateTime.Now.Millisecond + System.IO.Path.GetExtension(FileUpload2.FileName).ToLower(); if (File.Exists(path + fileName)) { Random rnd = new Random(10000); fileName = fileName + rnd.Next(); } FileUpload2.PostedFile.SaveAs(path + fileName); } else { } } catch (Exception exp) { } ExcelToDS(Server.MapPath(".") + "\\doc\\"+fileName); } http://www.csharpwin.com/csharpspace/6905r7737.shtml 1.EXCEL可以做为数据源,自然可以读到datagridview中 string strCon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + newfilename + ";Extended Properties=Excel 8.0;"; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = " SELECT * FROM [prd$] "; myConn.Open(); OleDbCommand myCommand = new OleDbCommand(strCom, myConn); OleDbDataReader ddr = myCommand.ExecuteReader();2.从数据库读入程序就不说了,比如存在一个list里,写入EXCEL很简单 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //开启excel excel.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Sheets ExcelSheets = excel.Worksheets; //建立一个新的工作表 excel.Cells[1, 1] = "文件"; excel.Cells[1, 2] = "代号"; excel.Cells[1, 3] = "公司"; if (list != null) { for (int i = 0; i < list.Count; i++) { excel.Cells[i + 1, 1] = "'" + list[i].FileType; excel.Cells[i + 1, 2] = "'" + list[i].Fileid; excel.Cells[i + 1, 3] = "'" + list[i].Cardcom; } } excel.Workbooks.Close(); excel.Quit();//关闭excel程序 return true; excel导入datasetstring strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; using(OleDbConnection OleConn = new OleDbConnection(strConn)){ OleConn.Open(); String sql = "SELECT * FROM [Sheet1$]"; OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataSet ds= new DataSet(); OleDaExcel.Fill(ds); OleConn.Close();}打开EXCEL模板赋值给单元格 如果EXCEL有单元格合并之类的,怎么体现到datagridview中 C# 如何终止被挂起的线程 C#在winform中直接画图的问题 关于timer的使用问题 一个比较复杂的查询 高分跪求:多文件上传 我是刚学ASP.NET大家帮我看看这个问题!(新人中的新人提问)谢谢大家! 克隆2003系统到一个新硬盘,无法登录进入系统,一登录便退出,要怎么办才好??? 将word作为数据源可以吗???????? 100分再开一贴 刚才的问题还是无法解决 上周五借了运气给我的人请进来领分 winform中的发布和安装布署的区别是什么??? 如何接管IE右键菜单中的某个菜单项
using System.Data; using System.Data.OleDb; using System.IO; using System.Text; protected void Page_Load(object sender, EventArgs e) { } public void ExcelToDS(string path) { string strSheetName = "sheet1"; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'"; //Sql语句 string strExcel = "select * from [" + strSheetName + "$] "; DataSet ds = new DataSet(); //连接数据源 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); //适配到数据源 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn); adapter.Fill(ds, "data"); conn.Close(); GridView1.DataSource = ds.Tables["data"]; GridView1.DataBind(); Label1.Text = ds.Tables[0].Rows.Count.ToString(); SqlAccess.Sqldatabase sql = new SqlAccess.Sqldatabase(); StringBuilder sb = new StringBuilder(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { sb.Append("insert into data values('" + ds.Tables[0].Rows[i]["companyname"] + "','" + ds.Tables[0].Rows[i]["telname"] + "','" + ds.Tables[0].Rows[i]["qytel"] + "','" + ds.Tables[0].Rows[i]["fax"] + "');"); } sql.ExecuteNonQuery(CommandType.Text, sb.ToString()); } protected void Button1_Click(object sender, EventArgs e) { string fileName = null; try { Boolean fileOK = false; String path = Server.MapPath("./doc/"); if (FileUpload2.HasFile) { String fileExtension = System.IO.Path.GetExtension(FileUpload2.FileName).ToLower(); String[] allowedExtensions = { ".xls" }; //C#读取Excel中数据 for (int i = 0; i < allowedExtensions.Length; i++) { if (fileExtension == allowedExtensions[i]) { fileOK = true; } } } if (fileOK) { fileName = "r_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + "_" + DateTime.Now.Millisecond + System.IO.Path.GetExtension(FileUpload2.FileName).ToLower(); if (File.Exists(path + fileName)) { Random rnd = new Random(10000); fileName = fileName + rnd.Next(); } FileUpload2.PostedFile.SaveAs(path + fileName); } else { } } catch (Exception exp) { } ExcelToDS(Server.MapPath(".") + "\\doc\\"+fileName); }
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [prd$] "; myConn.Open(); OleDbCommand myCommand = new OleDbCommand(strCom, myConn);
OleDbDataReader ddr = myCommand.ExecuteReader();
2.从数据库读入程序就不说了,比如存在一个list里,写入EXCEL很简单 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //开启excel
excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Sheets ExcelSheets = excel.Worksheets; //建立一个新的工作表
excel.Cells[1, 1] = "文件";
excel.Cells[1, 2] = "代号";
excel.Cells[1, 3] = "公司";
if (list != null)
{
for (int i = 0; i < list.Count; i++)
{
excel.Cells[i + 1, 1] = "'" + list[i].FileType;
excel.Cells[i + 1, 2] = "'" + list[i].Fileid;
excel.Cells[i + 1, 3] = "'" + list[i].Cardcom;
}
} excel.Workbooks.Close();
excel.Quit();//关闭excel程序
return true;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
using(OleDbConnection OleConn = new OleDbConnection(strConn))
{
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet ds= new DataSet();
OleDaExcel.Fill(ds);
OleConn.Close();
}打开EXCEL模板赋值给单元格