关于EXCEL导入ORACLE10

我的方法是先把数据库中的数据导出到excel表，然后在excel表中整理好格式，再重新导入

你首先将excel数据源读出来填充到一个datatable  然后遍历datatable 再insert into 到oracle表中.
另外还可以看看NPOI 导入的方式
附一个很久前写的string strSaveFilePath = Server.MapPath("download/");
            if (ExcelFileUploader.HasFile)
            {
                FileInfo fileinfo = new FileInfo(ExcelFileUploader.PostedFile.FileName);
                if ((fileinfo.Extension == ".xls") || (fileinfo.Extension == ".xlsx"))
                {
                    ExcelFileUploader.PostedFile.SaveAs(Server.MapPath("download/") + "ImportMobile.xls");
                }
                else
                {
                    Response.Write("<script language='javascript'>alert('");
                    Response.Write(DMCommon.TransLang("Batch_import_file_error")); //请选择Microsoft Excel文件!
                    Response.Write("')</script>");
                    Response.Write("<script>document.location=document.location;</script>");
                }
            }
            string xlsPath = strSaveFilePath + "ImportMobile.xls"; // 设备导入文件保存路径
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";" +
                            "data source=" + xlsPath;
            string sql = "SELECT * FROM [Mobile$]"; // 查询名为MobilePhones的Sheet
            DataSet dsExcelMobile = new DataSet();
            try
            {
                OleDbDataAdapter oda = new OleDbDataAdapter(sql, connStr);
                oda.Fill(dsExcelMobile);    // 填充DataSet  【设备编号、IMEI、蓝牙】
            }
            catch(Exception ex)
            {
                Response.Write("<script language='javascript'>alert('");
                Response.Write(DMCommon.TransLang("Read_import_file_error")); // 未能读取设备信息，请检查模板Excel文件!
                Response.Write("')</script>");
                Response.Write("<script>document.location=document.location;</script>");
                return;
            }
//然后你可以对这个table 循环插入到到你的oracle表里但还是推荐用NPOI 可以去参考这方面的例子

http://download.csdn.net/source/222157

我正在写。。还没写完
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;using Microsoft.Office.Interop.Excel;using System.Text;
using System.Data.OleDb;namespace Excel_Oracle
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {        }
        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;            Microsoft.Office.Interop.Excel.Range range;            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("utf-8"));


            string strAll = reader.ReadToEnd();
            reader.Close();
            string strData = strAll.Substring(strAll.IndexOf("\r\n") + 2).Replace(",\r\n", ",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 data\r\ninfile '{0}' \r\nappend into table {1}\r\n" +
                  "FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\r\n(";
            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) + ");\r\nexit;";
            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 + "\r\n";
            while (!r.EndOfStream)
            {
                line = r.ReadLine();
                //textBox3.Text += line + "\r\n";
                //textBox3.Update();
            }
            p.WaitForExit();            //可以自行解决掉临时文件csv，ctl和sql，代码略去
        }        protected void Button1_Click(object sender, EventArgs e)
        {
            TransferData(@"E:\Chengdu Staffing Report 20110321.xlsx", "Chengdu Resources", "cb_demo/cb_1234@cb_demo");
        }
    }
}

还有个验证类using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Drawing;
using System.Runtime.InteropServices;using Excel = Microsoft.Office.Interop.Excel;using System.Globalization;
using System.Threading;using LillyIntegration.Common;public class ExcelOperation
{
    // 如果客户端计算机运行的是英语版本的 Excel,并且当前用户的区域设置配置为英语之外的某个语言，则 Excel 将尝试查找针对所配置语言的语言包。如果没有找到所需语言包，则会报告错误。
    CultureInfo oldCultureInfo = Thread.CurrentThread.CurrentCulture;
    ExcelInstance excelInstance;    public ExcelOperation(ExcelInstance excelInstance)
    {
        this.excelInstance = excelInstance;
        Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
    }    #region Public Excel Operation Methods    public void OpenExcelSheet(string fileDirectory, string fileName, long sheetNumber)
    {
        string filePath = Path.Combine(fileDirectory, fileName);
        if (File.Exists(filePath))
        {
            try
            {
                // Open file and get workbook instance
                this.excelInstance.ExcelWorkbook = this.excelInstance.ExcelApp.Workbooks._Open(filePath,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception ex)
            {
                LillyIntegration.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
                throw new Exception("Can't open " + fileName + " on the server! " + ex.Message);
            }
        }
        else
        {
            throw new Exception("Open excel error: can't find +" + fileName + " !");
        }        if (this.excelInstance.ExcelWorkbook.Worksheets.Count != 0)
        {
            // choose which sheet to process
            this.excelInstance.ExcelWorksheet = this.excelInstance.ExcelWorkbook.Sheets[sheetNumber] as Excel.Worksheet;
        }
        else
        {
            throw new Exception(fileName + " does not contain any worksheet!");
        }
    }    public void InitialColor(Color foreColor, Color backColor)
    {
        Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.UsedRange;
        range.Font.Color = System.Drawing.ColorTranslator.ToOle(foreColor);
        range.Interior.Color = System.Drawing.ColorTranslator.ToOle(backColor);
    }    public string ReadExcelCell(long rowNumber, long columnNumber)
    {
        Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
        object cellValue = range.get_Value(Type.Missing);
        DateTime? dateTime = cellValue as DateTime?;
        if (dateTime.HasValue)
        {
            IFormatProvider culture = new CultureInfo("zh-CN", true);
            cellValue = dateTime.Value.ToString(culture);
        }        return cellValue == null ? string.Empty : cellValue.ToString().WTrim();
    }    public string ReadExcelCell(long rowNumber, string columnName)
    {
        long columnNumber = this.GetColumnNumberByColumnName(columnName);
        return ReadExcelCell(rowNumber, columnNumber);
    }    public void AddComment(long rowNumber, long columnNumber, string comment)
    {
        try
        {
            if (!string.IsNullOrEmpty(comment))
            {
                Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
                range.AddComment(comment);
            }
        }
        catch (Exception ex)
        {
            LillyIntegration.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
            throw new Exception("Error eccured when add Comment" + ex.Message);
        }
    }    public void DelComment()
    {
        throw new NotImplementedException();
    }    public string GetColumnNameByColumnNumber(long columnNumber)
    {
        Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[1, columnNumber];
        string cellValue = range.Text.ToString();
        return cellValue;
    }    public long GetColumnNumberByColumnName(string columnName)
    {
        return this.ReadOneRow(1).IndexOf(columnName) + 1;
    }    public long UsedRowNumber()
    {
        return (this.excelInstance.ExcelWorksheet.UsedRange.Rows.Count);
    }    public long UsedColumnNumber()
    {
        return (this.excelInstance.ExcelWorksheet.UsedRange.Columns.Count);
    }    public IList<string> ReadOneRow(long rowNumber)
    {
        IList<string> retList = new List<string>();
        for (long columnNumber = 1; columnNumber <= this.UsedColumnNumber(); columnNumber++)
        {
            retList.Add(this.ReadExcelCell(rowNumber, columnNumber));
        }
        return retList;
    }    public void WriteExcelCell(long rowNumber, long columnNumber, string cellValue)
    {
        throw new NotImplementedException();
    }    public void SetExcelCellFontColor(long rowNumber, long columnNumber, Color color)
    {
        Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
        range.Font.Color = System.Drawing.ColorTranslator.ToOle(color);
    }    public void SetExcelCellBackgroundColor(long rowNumber, long columnNumber, Color color)
    {
        Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
        range.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);
    }    public void SetExcelCellMark(long rowNumber, long columnNumber, Color foreColor, Color backColor, string comment)
    {
        Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, columnNumber];
        range.Font.Color = System.Drawing.ColorTranslator.ToOle(foreColor);
        range.Interior.Color = System.Drawing.ColorTranslator.ToOle(backColor);
        if (!string.IsNullOrEmpty(comment))
        {
            range.AddComment(comment);
        }
    }    public void SetExcelRowBackgroundColor(long rowNumber, long startColumnNumber, long columnCount, Color color)
    {
        for (long i = startColumnNumber; i <= columnCount; i++)
        {
            Excel.Range range = (Excel.Range)this.excelInstance.ExcelWorksheet.Cells[rowNumber, i];
            range.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);
        }
    }    public void SaveExcel(string targetDirectory, string fileName)
    {
        bool oldDisplayAlertState = this.excelInstance.ExcelApp.DisplayAlerts;
        string filePath = Path.Combine(targetDirectory, fileName);
        DirectoryInfo directoryInfo = new DirectoryInfo(targetDirectory);
        try
        {
            if (directoryInfo.Exists == false)
            {
                directoryInfo.Create();
            }
        }
        catch (Exception ex)
        {
            LillyIntegration.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
            throw new Exception("Can't create the directory which used to save the excel! " + ex.Message);
        }
        try
        {
            this.excelInstance.ExcelApp.DisplayAlerts = false;
            this.excelInstance.ExcelWorkbook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, true);
            this.excelInstance.ExcelApp.DisplayAlerts = oldDisplayAlertState;
        }
        catch (Exception ex)
        {
            LillyIntegration.Logging.Log4NetHelper.GetLogger().Error(ex.Message, ex);
            throw new Exception("Can't save the excel! " + ex.Message);
        }
    }    public void CloseExcelInstance()
    {
        Thread.CurrentThread.CurrentCulture = oldCultureInfo;        if (this.excelInstance.ExcelWorkbook != null)
        {
            this.excelInstance.ExcelWorkbook.Close(false, Type.Missing, Type.Missing);
        }        if (this.excelInstance.ExcelApp != null)
        {
            this.excelInstance.ExcelApp.DisplayAlerts = false;
            this.excelInstance.ExcelApp.Quit();
        }        if (this.excelInstance.ExcelWorksheet != null)
        {
            if (Marshal.ReleaseComObject(this.excelInstance.ExcelWorksheet) >= 0)
            {
                this.excelInstance.ExcelWorksheet = null;
            }
        }        if (this.excelInstance.ExcelWorkbook != null)
        {
            if (Marshal.ReleaseComObject(this.excelInstance.ExcelWorkbook) >= 0)
            {
                this.excelInstance.ExcelWorkbook = null;
            }
        }        if (this.excelInstance.ExcelApp != null)
        {
            if (Marshal.ReleaseComObject(this.excelInstance.ExcelApp) >= 0)
            {
                this.excelInstance.ExcelApp = null;
            }
        }        GC.GetTotalMemory(false);        GC.Collect();        GC.WaitForPendingFinalizers();        GC.Collect();        GC.GetTotalMemory(true);
    }    #endregion
}

这个也不错SQL*LOADER 也可以。

调试易

关于EXCEL导入ORACLE10

解决方案 »