January 26, 2018 at 1:55 pm
Good Morning Sir/Mam,
I would like to ask if it is possible to use the Oracle Client in C# to Extract Data from Excel and insert it into a Data Table? i currently was able to do this using OleDB but i am not sure if it is possible in Oracle Client. My Code Are as follows.
private static DataSet ImportExcelData(string fileName, bool hasHeaders)
{
string hdr = hasHeaders ? "Yes" : "No";
string connString = "Driver=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileName + ";Extended Properties=\"Excel 8.0;HDR=" +
hdr + ";IMEX=1\"";
DataSet output = new DataSet();
try
{
using (OracleConnection conn = new OracleConnection(connString))
{
conn.Open();
string[] restrictions = new string[4] { null, null, null, "TABLE" };
/* DataTable schemaTable = conn.GetSchema(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });*/
DataTable schemaTable = conn.GetSchema("Columns", restrictions);
foreach (DataRow schemaRow in schemaTable.Rows)
{
string sheet = schemaRow["TABLE_NAME"].ToString();
OracleCommand cmd = new OracleCommand("SELECT * FROM [" + sheet + "]", conn);
cmd.CommandType = CommandType.Text;
DataTable outputTable = new DataTable(sheet);
output.Tables.Add(outputTable);
new OracleDataAdapter(cmd).Fill(outputTable);
}
}
}
catch (OracleException ex)
{
MessageBox.Show(ex.InnerException.ToString(), "May Mali");
_log.Error(ex);
if (ex.ErrorCode == -2147467259)
{
MessageBox.Show("Specified file is open and being edited. Please complete the edit before proceeding.", "Import Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
else
{
MessageBox.Show(ex.Message, "Import Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
return null;
}
catch (Exception ex)
{
_log.Error(ex);
MessageBox.Show(ex.Message + " ImportExcelData", "Import Error", MessageBoxButtons.OK, MessageBoxIcon.Stop);
}
return output;
}
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy