Asp.net使用Oracle SQL*LOADER导入Excel表数据及相关问题解决
1.Oracle 的SQL*LOADER相关知识
http://blog.csdn.net/fjnucse/archive/
2.实现功能：
将excel表数据导入到oracle中
3.实现步骤：
（1）项目引用Microsoft.Office.Interop.Excel.dll 文件
（2）用com组件读取excel文件
（3）将excel文件保存为csv格式，并且处理表头和最后一个字段为null情况
（4）根据Excel结构 建表。（可选择）
（5）生成sqlldr的控制文件.ctl
（6）用sqlldr命令导入数据
4.实现代码：
代码
/// <SUMMARY>
/// excel导入到oracle
/// </SUMMARY>
/// <PARAM name="excelFile">文件名</PARAM>
/// <PARAM name="tableName">表名</PARAM>
/// <PARAM name="sheetName">sheet名</PARAM>
/// <PARAM name="sqlplusString">oracle命令sqlplus连接串 username/password@server</PARAM>
/// <returns>string</returns>
public string ExcelToOracle(string excelFile, string tableName, string sheetName, string sqlplusString)
{
string sheetNameOfFile = string.Empty;
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;
}
else
{
sheetNameOfFile = sheet.Name;
}
}
if (ObjWorkSheet == null)
{
ObjWorkBook.Close(false, Type.Missing, Type.Missing);
ObjExcel.Quit();
throw new Exception(string.Format("{0} ——sheet名称必须是{1} !!", sheetNameOfFile, 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, System.Text.Encoding.GetEncoding("gb2312"));
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;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, sheetName+"$", null });
conn.Close();
//生成sqlldr用到的控制文件,文件结构参考sql*loader功能，本示例已逗号分隔csv，数据带逗号的用引号括起来。
string strControl = "load data\r\ninfile '{0}' \r\nReplace into table {1}\r\n" +
"FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'\r\n(";
strControl = string.Format(strControl, strCsvPath, tableName);
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);
strSql += "LKBH varchar2(255),";
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("userid={0} control={1} log={2} bad={3}", sqlplusString, strCtlPath, "c:\\Excel\\input.log", "c:\\Excel\\input.bad");
p.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Hidden;
p.StartInfo.LoadUserProfile = true;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.UseShellExecute = false;
p.StartInfo.CreateNoWindow = true;
p.Start();
System.IO.StreamReader r = p.StandardOutput;//截取输出流
string line = r.ReadLine();//每次读取一行
string result = line + "\r\n";
while (!r.EndOfStream)
{
line = r.ReadLine();
result += line + "\r\n";
}
p.WaitForExit();
p.Close();
p.Dispose();
//可以自行解决掉临时文件csv，ctl和sql，代码略去
return result;
}
5.遇到问题
（1）第一次成功导入test.xls表后，要是想此时删除、修改、复制、移动test.xls文件，都会被提示 被另一个进程占用。
（电脑环境：windows server 2003 sp2 +iis 6.0）这是因为默认情况下，IIS 6.0在工作进程隔离模式下运行。需要配置成IIS 5.0 隔离模式下运行。这就需要修改 machine.config文件
（C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config）中配置节点<processModel />元素。修改如下：
<system.web>
<processModel
enable="true"
timeout="Infinite"
idleTimeout="Infinite"
shutdownTimeout="0:00:05" requestLimit="Infinite"
requestQueueLimit="5000" restartQueueLimit="10"
memoryLimit="60" webGarden="false" cpuMask="0xffffffff"
userName="system" password="AutoGenerate" logLevel="Errors"
clientConnectedCheck="0:00:05" comAuthenticationLevel="Connect"
comImpersonationLevel="Impersonate" responseRestartDeadlockInterval="00:09:00"
responseDeadlockInterval="00:03:00" maxWorkerThreads="20" maxIoThreads="20" />
参考资料：
1.使用 ASP.NET 进程模型http://doc.51windows.net/iismmc/?url=/iismmc/htm/aaconusingaspnetprocessmodel.htm
2.ASP.NET配置文件machine.config讲解http://blog.csdn.net/hanxuema2008/archive/2008/11/21/3344409.aspx
（2） 如果将将网站发布出来，运行时，可能会出现一切正常，但就是没有将数据导入到数据库中。
这可能是因为将代码p.StartInfo.Arguments = string.Format("userid={0} control={1} log={2} bad={3}", sqlplusString, strCtlPath, "c:\\Excel\\input.log", "c:\\Excel\\input.bad");简写成p.StartInfo.Arguments = string.Format("{0} {1} ", sqlplusString, strCtlPath);所导致的。后者在vs2008 开发环境下没有问题，但发布出来就导致sqlldr语句错误。
