这个功能怎么实现呢,现在只实现了导出
string extension = "xls";
ExportFormat format = ExportFormat.Text; string selectedItem = "Excel"; SaveFileDialog dialog = new SaveFileDialog()
{
DefaultExt = extension,
Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, selectedItem),
FilterIndex = 1
}; if (dialog.ShowDialog() == true)
{
using (Stream stream = dialog.OpenFile())
{
grdResult1.Export(stream,
new GridViewExportOptions()
{
Format = format,
ShowColumnHeaders = true,
});
}
}
}
导入的能不能帮帮忙,急!,现在想要实现的是从EXCEL里读出数据保存到ORACLE数据库表里
string extension = "xls";
ExportFormat format = ExportFormat.Text; string selectedItem = "Excel"; SaveFileDialog dialog = new SaveFileDialog()
{
DefaultExt = extension,
Filter = String.Format("{1} files (*.{0})|*.{0}|All files (*.*)|*.*", extension, selectedItem),
FilterIndex = 1
}; if (dialog.ShowDialog() == true)
{
using (Stream stream = dialog.OpenFile())
{
grdResult1.Export(stream,
new GridViewExportOptions()
{
Format = format,
ShowColumnHeaders = true,
});
}
}
}
导入的能不能帮帮忙,急!,现在想要实现的是从EXCEL里读出数据保存到ORACLE数据库表里
解决方案 »
- 如何取出KEY值?
- 我读区了一个数据库得到这个数据库的ID然后在根据ID在查找一编数据库为什么出问题?
- 求高手指导mycmd.ExecuteNonQuery()错误是什么原因
- 数据库存储图片网页显示时如何与其他网页元素共存(一个页面实现)?望高手指教,感兴趣的帮顶!
- 如何在DataGrid中绑定数据库中的图片文件。
- 各位大哥斑竹帮帮我,如何用datagrid显示的数据限定每栏的字数?并在后面带上…!???
- ASP.NET2.0GridView鼠标滑过,如何显示图片?
- 求.net下的可录入的下拉框(组合框)控件
- 清除Session的问题,急啊
- 彷徨中……(vb.net or c#)
- 初学:DATALIST怎么显示主表关联的其他两、三个表的内容。
- 自己写了个根据输入的数字动态添加控件的,感觉还有要改的地方,求高手指点
using System.Data;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
//引用-com-microsoft excel objects 11.0
namespace WindowsApplication5
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <SUMMARY>
/// excel导入到oracle
/// </SUMMARY>
/// <PARAM name="excelFile">文件名</PARAM>
/// <PARAM name="sheetName">sheet名</PARAM>
/// <PARAM name="sqlplusString">oracle命令sqlplus连接串</PARAM>
public void TransferData(string excelFile, string sheetName, string sqlplusString)
{
string strTempDir = System.IO.Path.GetDirectoryName(excelFile);
string strFileName = System.IO.Path.GetFileNameWithoutExtension(excelFile);
string strCsvPath = strTempDir +""+strFileName + ".csv";
string strCtlPath = strTempDir + "" + strFileName + ".Ctl";
string strSqlPath = strTempDir + "" + strFileName + ".Sql";
if (System.IO.File.Exists(strCsvPath))
System.IO.File.Delete(strCsvPath);
//获取excel对象
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet = null;
ObjWorkBook = ObjExcel.Workbooks.Open(excelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in ObjWorkBook.Sheets)
{
if (sheet.Name.ToLower() == sheetName.ToLower())
{
ObjWorkSheet = sheet;
break;
}
}
if (ObjWorkSheet == null) throw new Exception(string.Format("{0} not found!!", sheetName));
//保存为csv临时文件
ObjWorkSheet.SaveAs(strCsvPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, false, false, false, Type.Missing, Type.Missing, false);
ObjWorkBook.Close(false, Type.Missing, Type.Missing);
ObjExcel.Quit();
//读取csv文件,需要将表头去掉,并且将最后一列为null的字段处理为显示的null,否则oracle不会识别,这个步骤有没有好的替换方法?
System.IO.StreamReader reader = new System.IO.StreamReader(strCsvPath,Encoding.GetEncoding("gb2312"));
string strAll = reader.ReadToEnd();
reader.Close();
string strData = strAll.Substring(strAll.IndexOf("rn") + 2).Replace(",rn",",Null");
byte[] bytes = System.Text.Encoding.Default.GetBytes(strData);
System.IO.Stream ms = System.IO.File.Create(strCsvPath);
ms.Write(bytes, 0, bytes.Length);
ms.Close();
//获取excel表结构
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, sheetName+"$", null });
//生成sqlldr用到的控制文件,文件结构参考sql*loader功能,本示例已逗号分隔csv,数据带逗号的用引号括起来。
string strControl = "load datarninfile '{0}' rnappend into table {1}rn"+
"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'rn(";
strControl = string.Format(strControl, strCsvPath,sheetName);
foreach (System.Data.DataRow drowColumns in table.Select("1=1", "Ordinal_Position"))
{
strControl += drowColumns["Column_Name"].ToString() + ",";
}
strControl = strControl.Substring(0, strControl.Length - 1) + ")";
bytes=System.Text.Encoding.Default.GetBytes(strControl);
ms= System.IO.File.Create(strCtlPath);
ms.Write(bytes, 0, bytes.Length);
ms.Close();
//生成初始化oracle表结构的文件
string strSql = @"drop table {0};
create table {0}
(";
strSql = string.Format(strSql, sheetName);
foreach (System.Data.DataRow drowColumns in table.Select("1=1", "Ordinal_Position"))
{
strSql += drowColumns["Column_Name"].ToString() + " varchar2(255),";
}
strSql = strSql.Substring(0, strSql.Length - 1) + ");rnexit;";
bytes = System.Text.Encoding.Default.GetBytes(strSql);
ms = System.IO.File.Create(strSqlPath);
ms.Write(bytes, 0, bytes.Length);
ms.Close();
//运行sqlplus,初始化表
System.Diagnostics.Process p = new System.Diagnostics.Process();
p.StartInfo = new System.Diagnostics.ProcessStartInfo();
p.StartInfo.FileName = "sqlplus";
p.StartInfo.Arguments = string.Format("{0} @{1}", sqlplusString, strSqlPath);
p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
p.StartInfo.UseShellExecute = false;
p.StartInfo.CreateNoWindow = true;
p.Start();
p.WaitForExit();
//运行sqlldr,导入数据
p = new System.Diagnostics.Process();
p.StartInfo = new System.Diagnostics.ProcessStartInfo();
p.StartInfo.FileName = "sqlldr";
p.StartInfo.Arguments = string.Format("{0} {1}", sqlplusString, strCtlPath);
p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.UseShellExecute = false;
p.StartInfo.CreateNoWindow = true;
p.Start();
System.IO.StreamReader r = p.StandardOutput;//截取输出流
string line = r.ReadLine();//每次读取一行
textBox3.Text += line + "rn";
while (!r.EndOfStream)
{
line = r.ReadLine();
textBox3.Text += line + "rn";
textBox3.Update();
}
p.WaitForExit();
//可以自行解决掉临时文件csv,ctl和sql,代码略去
}
private void button1_Click(object sender, EventArgs e)
{
TransferData(@"D:test.xls", "Sheet1", "username/password@servicename");
}
}
}
using System.Data.OleDb;没有这两个命名空间