public static void ReadExcel()
{
string sheetName = string.Empty;
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\test.xlsx;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"";
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
DataTable table = connection.GetSchema("Tables");
if (table.Rows.Count > 0)
sheetName = table.Rows[0]["TABLE_NAME"].ToString();
if (!string.IsNullOrEmpty(sheetName))
{
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = string.Format("select * from [{0}]", sheetName);
command.CommandType = CommandType.Text;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
StringBuilder strBuilder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
strBuilder.Append(string.Format("{0}\t", reader[i]));
Console.WriteLine(strBuilder.ToString());
}
reader.Close();
}
connection.Close();
Console.ReadKey();
}
//public string ExcelFile {private get; set; }
public static DataTable ReadData(string excelFile)
{
if (!System.IO.File.Exists(excelFile))
return null;
OleDbConnection excelConnection = new OleDbConnection();
excelConnection.ConnectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source='{0}';Extended Properties='Excel 8.0;HDR=YES'", excelFile);
excelConnection.Open();
DataTable dtSchema = excelConnection.GetSchema("Tables");
if (dtSchema.Rows.Count == 0)
return null;
string strTableName = dtSchema.Rows[0]["Table_Name"] as string;
string strSQL = string.Format("select * from [{0}]", strTableName);
OleDbCommand cmdSelect = excelConnection.CreateCommand();
cmdSelect.CommandText = strSQL;
OleDbDataAdapter dbAdapter = new OleDbDataAdapter(cmdSelect);
DataTable dtResult=new DataTable();
dbAdapter.Fill(dtResult);
dbAdapter.Dispose();
excelConnection.Close();
excelConnection.Dispose();
return dtResult;
}
static internal List <SqlSchemaInfo> GetSchemaInfo(System.Data.OleDb.OleDbConnection con, List <SqlSchemaInfo> schemaList)
{
try
{
DataTable tbl = con.GetSchema(System.Data.OleDb.OleDbMetaDataCollectionNames.Tables);
foreach (DataRow row in tbl.Rows)
{
SqlSchemaInfo ssi = new SqlSchemaInfo();
ssi.Type = "TABLE";
ssi.Name = (string)row["table_name"];
schemaList.Add(ssi);
}
tbl = con.GetSchema(System.Data.OleDb.OleDbMetaDataCollectionNames.Views);
foreach (DataRow row in tbl.Rows)
{
SqlSchemaInfo ssi = new SqlSchemaInfo();
ssi.Type = "VIEW";
ssi.Name = (string)row["table_name"];
schemaList.Add(ssi);
}
}
catch
{
}
return(schemaList);
}
public DataTable GetTablesName()
{
OleDbConnection connection = new OleDbConnection();
DataTable dtTables = new DataTable();
connection.ConnectionString = this._DbConectionString;
try
{
connection.Open();
dtTables = connection.GetSchema("TABLES");
}
catch (OleDbException oledbex)
{
throw oledbex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return dtTables;
}
/// <summary>
/// Imports all sheets from an Excel file
/// </summary>
/// <param name="excelFile">The excel file path.</param>
/// <returns></returns>
public static DataSet ImportAll(string excelFile)
{
DataSet ds = new DataSet();
string connectionString = GetExcelConnectionString(excelFile, false, true);
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
//csv doesn't have worksheets, and table name == file name
if (Path.GetExtension(excelFile).Equals(".csv", StringComparison.OrdinalIgnoreCase))
{
LoadTableIntoDataSet(connection, Path.GetFileName(excelFile), ds);
return ds;
}
//xls and xlsx have worksheets, so load each one as a datatable
DataTable worksheets = connection.GetSchema("Tables");
foreach (DataRow row in worksheets.Rows)
{
//this can also return Excel named ranges
string tabName = (string)row["TABLE_NAME"];
//so look for sheets (excel puts $ after the name and may single-quote the name)
if (tabName.EndsWith("$") || tabName.EndsWith("$'"))
LoadTableIntoDataSet(connection, tabName, ds);
}
}
return ds;
}
public string[] GetColumnsList(string worksheet)
{
string[] columns;
try
{
OleDbConnection connection = new OleDbConnection(strConnection);
connection.Open();
DataTable tableColumns = connection.GetSchema("Columns", new string[] {null, null, worksheet + '$', null});
connection.Close();
columns = new string[tableColumns.Rows.Count];
for (int i = 0; i < columns.Length; i++)
{
columns[i] = (string)tableColumns.Rows[i]["COLUMN_NAME"];
}
}
catch
{
throw;
}
return columns;
}
private void button5_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "xls files (*.xls)|*.xls";
saveFileDialog1.FilterIndex = 2;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
DataTable dt = dataSet.Tables["student"].Copy();
//連線字串
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + saveFileDialog1.FileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\"");
connection.Open();
//建立工作表
try
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = connection;
DataTable cdt = connection.GetSchema("tables");
DataRow[] cdrs = cdt.Select("Table_Name = 'Sheet1'");
if (cdrs.Length == 0)
{
cmd.CommandText = "CREATE TABLE [Sheet1] ([學號] INTEGER,[班級] INTEGER,[姓名] VarChar)";
//新增Excel工作表
cmd.ExecuteNonQuery();
}
//增加資料
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["學號"] == null || dt.Rows[i]["班級"] == null || dt.Rows[i]["姓名"] == null)
{
MessageBox.Show(string.Format("有部分資料錯誤，請檢查 (學號:{0},班級:{1},姓名{2})", dt.Rows[i]["學號"], dt.Rows[i]["班級"], dt.Rows[i]["姓名"]));
}
else
{
cmd.CommandText = string.Format("INSERT INTO [Sheet1$] VALUES({0},{1},'{2}')", dt.Rows[i]["學號"], dt.Rows[i]["班級"], dt.Rows[i]["姓名"]);
cmd.ExecuteNonQuery();
}
}
MessageBox.Show(string.Format("學生資料成功匯出到 {0}", saveFileDialog1.FileName));
tssMessage.Text = "學生資料匯出成功!";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
connection.Close();
connection.Dispose();
}
}
protected override System.Data.DataTable ObtenerContenido(System.Data.DataTable contenido, FuenteInformacion fuenteInformacion, string rutaArchivo)
{
try
{
DataSet dsMsExcel = new DataSet();
using (System.Data.OleDb.OleDbConnection objOleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + rutaArchivo + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\""))
{
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
objOleConnection.Open();
DataTable worksheets = objOleConnection.GetSchema("Tables");
string hoja = worksheets.Rows[0][2].ToString();
System.Data.OleDb.OleDbCommand select = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + hoja + "]", objOleConnection);
select.CommandType = CommandType.Text;
adapter.SelectCommand = select;
dsMsExcel.Tables.Clear();
adapter.Fill(dsMsExcel);
if (dsMsExcel.Tables.Count > 0)
{
foreach (DataRow filaEstadoCuenta in dsMsExcel.Tables[0].Rows)
{
contenido.Rows.Add(filaEstadoCuenta.ItemArray);
}
contenido.Rows.Remove(contenido.Rows[0]);
return(contenido);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public DataTable GetTablesName(string FileName)
{
OleDbConnection connection = new OleDbConnection();
DataTable dtTables = new DataTable();
connection.ConnectionString = ConectionStringManagerAccess(FileName);
try
{
connection.Open();
dtTables = connection.GetSchema("TABLES");
for (int i = 0; i < dtTables.Rows.Count; i++)
{
if (dtTables.Rows[i]["TABLE_TYPE"].ToString() != "TABLE")
{
dtTables.Rows.RemoveAt(i);
i--;
}
}
}
catch (OleDbException oleex)
{
throw oleex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return dtTables;
}
private void button9_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "xls files (*.xls)|*.xls";
saveFileDialog1.FilterIndex = 2;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
DataTable dt = dataSet.Tables["pointlog"].Copy();
//連線字串
System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + saveFileDialog1.FileName + ";" +
"Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=0\"");
connection.Open();
try
{
//建立工作表
//dataGridView2.Columns["number"].HeaderText = "學號";
//dataGridView2.Columns["stu_class"].HeaderText = "班級";
//dataGridView2.Columns["name"].HeaderText = "姓名";
//dataGridView2.Columns["date"].HeaderText = "日期";
//dataGridView2.Columns["pt_case"].HeaderText = "獎勵事由";
//dataGridView2.Columns["point"].HeaderText = "獎勵點數";
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = connection;
DataTable cdt = connection.GetSchema("tables");
DataRow[] cdrs = cdt.Select("Table_Name = 'Sheet1'");
if (cdrs.Length == 0)
{
cmd.CommandText = "CREATE TABLE [Sheet1] ([班級] VarChar,[學號] INTEGER,[姓名] VarChar,[日期] VarChar,[獎勵事由] VarChar,[獎勵點數] INTEGER)";
//新增Excel工作表
cmd.ExecuteNonQuery();
}
//增加資料
for (int i = 0; i < dt.Rows.Count; i++)
{
cmd.CommandText = string.Format("INSERT INTO [Sheet1$] VALUES('{0}',{1},'{2}','{3}','{4}',{5})", dt.Rows[i]["stu_class"], dt.Rows[i]["number"], dt.Rows[i]["name"], dt.Rows[i]["date"], dt.Rows[i]["pt_case"], dt.Rows[i]["point"]);
cmd.ExecuteNonQuery();
}
MessageBox.Show(string.Format("點數清單成功匯出到 {0}", saveFileDialog1.FileName));
tssMessage.Text = "點數清單匯出成功!";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
connection.Close();
connection.Dispose();
}
}
public DataTable GetSchema(string collectionName)
{
DataTable results;
conn = new OleDbConnection(connection);
conn.Open();
results = conn.GetSchema(collectionName);
conn.Close();
return results;
}
public override System.Data.DataColumn[] ObtenerColumnas(FuenteInformacion fuenteInformacion, string rutaArchivo)
{
try
{
DataSet dsMsExcel = new DataSet();
using (System.Data.OleDb.OleDbConnection objOleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + rutaArchivo + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\""))
{
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
if (File.Exists(rutaArchivo))
{
objOleConnection.Open();
DataTable worksheets = objOleConnection.GetSchema("Tables");
string hoja = worksheets.Rows[0][2].ToString();
System.Data.OleDb.OleDbCommand select = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + hoja + "]", objOleConnection);
select.CommandType = CommandType.Text;
adapter.SelectCommand = select;
dsMsExcel.Tables.Clear();
adapter.Fill(dsMsExcel);
if (dsMsExcel.Tables.Count > 0)
{
DataRow col = dsMsExcel.Tables[0].Rows[0];
DataColumn[] columnas = new DataColumn[col.ItemArray.Length];
int index = 0;
string nombre;
foreach (object campo in col.ItemArray)
{
if (!string.IsNullOrEmpty(campo.ToString()))
{
nombre = campo.ToString().Trim();
}
else
{
nombre = "-----------" + index.ToString();
}
columnas[index] = new DataColumn(nombre);
index++;
}
return(columnas);
}
}
else
{
throw new Exception("El archivo " + Path.GetFileName(rutaArchivo) + " no existe. Corrija el mapeo y vuelva a intentarlo.");
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public void load_sheet_names()
{
connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File_PathtextBox1.Text + ";Extended Properties=Excel 12.0;";
OleDbConnection connection = new OleDbConnection(connectionstring);
connection.Open();
connection.GetSchema();
connection.GetSchema("Tables");
List<string> tables = new List<string>();
foreach (DataRow r in connection.GetSchema("Tables").Select("TABLE_TYPE = 'TABLE'"))
{
tables.Add(r["TABLE_NAME"].ToString());
}
foreach (var item in tables)
{
Excel_Sheet_Name_comboBox.Items.Add(item);
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
public IEnumerable<NFLEPMarkov> GetMarkovData(string FileName)
{
string sSheetName = null;
string sConnection = null;
DataTable dtTablesList = default(DataTable);
OleDbCommand oleExcelCommand = default(OleDbCommand);
OleDbDataReader oleExcelReader = default(OleDbDataReader);
OleDbConnection oleExcelConnection = default(OleDbConnection);
sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
oleExcelConnection = new OleDbConnection(sConnection);
oleExcelConnection.Open();
dtTablesList = oleExcelConnection.GetSchema("Tables");
if (dtTablesList.Rows.Count > 0)
{
sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString();
}
dtTablesList.Clear();
dtTablesList.Dispose();
if (!string.IsNullOrEmpty(sSheetName))
{
oleExcelCommand = oleExcelConnection.CreateCommand();
oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]";
oleExcelCommand.CommandType = CommandType.Text;
oleExcelReader = oleExcelCommand.ExecuteReader();
//nOutputRow = 0;
//var sheets = oleExcelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//oleExcelCommand.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"].ToString() + "] ";
//var adapter = new OleDbDataAdapter(oleExcelCommand);
//var ds = new DataSet();
//adapter.Fill(ds);
//object[] dataFill = new object[17];
using (var reader = oleExcelReader) { //will this work?
//using (var reader = oleExcelCommand.ExecuteReader())
//I think using gets rid of the reader, hopefully.
while (reader.Read()) {
yield return NFLEPMarkov.Create(reader);
}
}
}
oleExcelConnection.Close();
}
private void SaveColumnNames(OleDb.OleDbConnection conn, ref List <string> indexNames)
{
//Has to be called BEFORE SaveColumnValues()
System.Data.DataTable columns = conn.GetSchema("Columns");
//by default column names are sorted in alphabetical order
//(this is a stupid feature)
//so sort back to order in Excel
System.Data.DataRow[] rows = columns.Select("", "ORDINAL_POSITION");
foreach (System.Data.DataRow row in rows)
{
string columnName = (string)row["COLUMN_NAME"];
indexNames.Add(columnName);
}
}
//对下拉列表进行数据绑定
private void CBoxBind()
{
cbox_SheetName.Items.Clear();//清空下拉列表项
//连接Excel数据库
OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_Path.Text + ";Extended Properties=Excel 8.0");
olecon.Open();//打开数据库连接
System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
while (DTReader.Read())//循环读取
{
cbox_SheetName.Items.Add(DTReader["Table_Name"].ToString().Replace('$',' ').Trim());//将工作表名添加到下拉列表中
}
DTable = null;//清空表对象
DTReader = null;//清空表读取对象
olecon.Close();//关闭数据库连接
cbox_SheetName.SelectedIndex = 0;//设置下拉列表默认选项为第一项
}
public string Upload(HttpPostedFileBase uploadFile)
{
StringBuilder strValidations = new StringBuilder(string.Empty);
try
{
if (uploadFile.ContentLength > 0)
{
string filePath = Path.Combine(HttpContext.Server.MapPath("../Uploads"),
Path.GetFileName(uploadFile.FileName));
uploadFile.SaveAs(filePath);
DataSet ds = new DataSet();
//A 32-bit provider which enables the use of
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
{
conn.Open();
using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
{
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
string query = "SELECT * FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
//DataSet ds = new DataSet();
adapter.Fill(ds, "Items");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Now we can insert this data to database...
}
}
}
}
}
}
}
catch (Exception ex) { }
return("");
}
//获取所有工作表名称
private List<string> GetSheetName(string P_str_Excel)
{
List<string> P_list_SheetName = new List<string>();//实例化泛型集合对象
//连接Excel数据库
OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + P_str_Excel + ";Extended Properties=Excel 8.0");
olecon.Open();//打开数据库连接
System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
while (DTReader.Read())//循环读取
{
string P_str_Name = DTReader["Table_Name"].ToString().Replace('$', ' ').Trim();//记录工作表名称
if (!P_list_SheetName.Contains(P_str_Name))//判断泛型集合中是否已经存在该工作表名称
P_list_SheetName.Add(P_str_Name);//将工作表名添加到泛型集合中
}
DTable = null;//清空表对象
DTReader = null;//清空表读取对象
olecon.Close();//关闭数据库连接
return P_list_SheetName;//返回得到的泛型集合
}
public List <dynamic> GetTables(SqlHelper sqlHelper, string dataBaseName)
{
var list = new List <dynamic>();
var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString);
con.Open();
DataTable dt = con.GetSchema("tables");
foreach (DataRow dr in dt.Rows)
{
if (dr["TABLE_TYPE"].ToString() == "TABLE")
{
dynamic eo = new ExpandoObject();
eo.name = dr["TABLE_NAME"].ToString().FirstLetterToUpper();
list.Add(eo);
}
}
con.Close();
con.Dispose();
return(list);
}
private void button1_Click(object sender, EventArgs e)
{
try
{
comboBox1.Items.Clear();
OleDbConnection dbCon = new OleDbConnection(@"Provider=SQLNCLI10;Server=(local);Database=McDac;Trusted_Connection=yes");
dbCon.Open();
DataTable tables = dbCon.GetSchema("Tables", new string[] { null, null, null, "TABLE" }); //список всех таблиц
foreach (DataRow row in tables.Rows)
{
string TableName = row["TABLE_NAME"].ToString();
comboBox1.Items.Add(TableName);
}
dbCon.Close();
MessageBox.Show("Ready!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public List <dynamic> GetColumns(SqlHelper sqlHelper, string dataBaseName, string tableName)
{
var list = new List <dynamic>();
var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString);
con.Open();
DataTable dt = con.GetSchema("columns", new string[] { null, null, tableName });
foreach (DataRow dr in dt.Rows)
{
dynamic eo = new ExpandoObject();
eo.name = dr["COLUMN_NAME"].ToString().FirstLetterToUpper();
eo.type = dr["data_type"].ToString();
eo.length = dr["CHARACTER_MAXIMUM_LENGTH"].ToString();
eo.isnullable = dr["is_nullable"].ToString();
list.Add(eo);
}
con.Close();
con.Dispose();
return(list);
}
public string[] GetWorksheetList()
{
string[] worksheets;
try
{
OleDbConnection connection = new OleDbConnection(strConnection);
connection.Open();
DataTable tableWorksheets = connection.GetSchema("Tables");
connection.Close();
worksheets = new string[tableWorksheets.Rows.Count];
for (int i = 0; i < worksheets.Length; i++)
{
worksheets[i] = (string)tableWorksheets.Rows[i]["TABLE_NAME"];
worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
// removes the trailing $ and other characters appended in the table name
while (worksheets[i].EndsWith("$"))
worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
}
}
catch
{
/*
for (int i = 0; i < ex.Errors.Count; i++)
{
MessageBox.Show("Index #" + i + "\n" +
"Message: " + myException.Errors[i].Message + "\n" +
"Native: " +
myException.Errors[i].NativeError.ToString() + "\n" +
"Source: " + myException.Errors[i].Source + "\n" +
"SQL: " + myException.Errors[i].SQLState + "\n");
}
*/
throw;
}
return worksheets;
}
public ActionResult Excel(HttpPostedFileBase uploadFile)
{
StringBuilder strValidations = new StringBuilder(string.Empty);
try
{
string filePath = Path.Combine(HttpContext.Server.MapPath("../ufile"), Path.GetFileName(uploadFile.FileName));
if (uploadFile.ContentLength > 0)
{
Path.GetFileName(uploadFile.FileName);
uploadFile.SaveAs(filePath);
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("../files/") + uploadFile.FileName.Split('\\')[3].ToString() + ";Extended Properties=Excel 12.0;";
using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
{
conn.Open();
using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
{
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
string query = "SELECT * FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Items");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//这里添加到数据库操作
}
}
}
}
}
}
}
catch
{ }
return(View());
}
public ActionResult Excel(HttpPostedFileBase uploadFile)
{
StringBuilder strValidations = new StringBuilder(string.Empty);
try
{
string filePath = Path.Combine(HttpContext.Server.MapPath("../ufile"), Path.GetFileName(uploadFile.FileName));
if (uploadFile.ContentLength > 0)
{
Path.GetFileName(uploadFile.FileName);
uploadFile.SaveAs(filePath);
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("../files/") + uploadFile.FileName.Split('\\')[3].ToString() + ";Extended Properties=Excel 12.0;";
using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
{
conn.Open();
using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
{
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
string query = "SELECT * FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
DataSet ds = new DataSet();
adapter.Fill(ds, "Items");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//这里添加到数据库操作
}
}
}
}
}
}
}
catch
{ }
return View();
}
private static void OleDbReadCollectionName(string name)
{
using (OleDbConnection conn = new OleDbConnection(connstr))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
StringBuilder sb = new StringBuilder();
DataTable dataTable = conn.GetSchema(name);
foreach (DataColumn dataColumn in dataTable.Columns)
{
sb.Append(dataColumn.ColumnName.PadRight(40));
}
sb.AppendLine();
foreach (DataRow row in dataTable.Rows)
{
foreach (DataColumn column in dataTable.Columns)
{
sb.Append(row[column].ToString().PadRight(40));
}
sb.AppendLine();
}
using (StreamWriter writer = new StreamWriter(String.Format("oledb-{0}-schema.txt", name.ToLowerInvariant())))
{
writer.Write(sb.ToString());
writer.Flush();
}
}
}
public void dobivanjeTablica()
{
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\EvidencijaOtpremnica.mdb";
OleDbConnection conn = new OleDbConnection(connString);
conn.Open();
//dobivanje imena svih tablica
if (Broj_otpremnice == "")
{
string[] restrictions = new string[4];
restrictions[3] = "Table";
DataTable dt = conn.GetSchema("Tables", restrictions);
for (int i = 0; i < dt.Rows.Count; i++)
{
tableNames.Add(dt.Rows[i][2].ToString());
}
}
conn.Close();
}
public DataTable GetCumasFileData()
{
// http://www.connectionstrings.com/dbf-foxpro/
// http://stackoverflow.com/questions/22361457/c-sharp-read-from-dbf-files-into-a-datatable
// http://www.codeproject.com/Articles/24247/Load-a-DBF-into-a-DataTable
// https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection(v=vs.110).aspx
// http://stackoverflow.com/questions/11356878/get-data-in-a-dbf-file-using-c-sharp
DataTable resultData = new DataTable();
using (OleDbConnection connectionHandler = new OleDbConnection(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\diogo.marques\Downloads\Newest version\Newest version\Newest version\SALONMAN CURRENT\;Extended Properties=dBASE IV"))
{
// Open the connection, and if open successfully, you can try to query it
connectionHandler.Open();
if (connectionHandler.State == ConnectionState.Open)
{
string mySQL = "select * from Cumas"; // dbf table name
OleDbCommand MyQuery = new OleDbCommand(mySQL, connectionHandler);
OleDbDataAdapter DA = new OleDbDataAdapter(MyQuery);
DA.Fill(resultData);
var databaseName = connectionHandler.Database;
var serverVersion = connectionHandler.ServerVersion;
var site = connectionHandler.Site;
var schema = connectionHandler.GetSchema();
var container = connectionHandler.Container;
connectionHandler.Close();
}
return resultData;
}
}
public static DataTable GetOledbFirstTableData(this string dbFile)
{
string strConn = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + dbFile + "; Extended Properties=Excel 8.0;";
using (var conn = new OleDbConnection(strConn))
{
conn.Open();
var schema = conn.GetSchema("Tables");
string strSql = string.Format("select * from [{0}]", schema.Rows[0]["TABLE_NAME"]);
DataTable dt = null;
if (schema.Rows.Count > 0)
{
var ds = new DataSet();
var da = new OleDbDataAdapter(strSql, conn);
da.Fill(ds); // 填充DataSet
dt = ds.Tables[0];
}
return dt;
}
}
private static void ReadData(OleDbConnection connection,
OleDbCommand selectCommand, OleDbDataAdapter adapter)
{
DataTable tableInfo = connection.GetSchema("Tables");
List<string> sheetsName = new List<string>();
foreach (DataRow row in tableInfo.Rows)
{
sheetsName.Add(row["TABLE_NAME"].ToString());
}
for (int t = 0; t < sheetsName.Count; t++)
{
selectCommand.CommandText = "SELECT * FROM [" + sheetsName[t] + "]";
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
DataTable Sheet = new DataTable();
Sheet.TableName = sheetsName[t].Replace("$", "").Replace("'", "");
adapter.Fill(Sheet);
Console.WriteLine("NAME -> SCORE");
Console.WriteLine("--------------------------");
for (int i = 0; i < Sheet.Rows.Count; i++)
{
for (int j = 0; j < Sheet.Columns.Count; j++)
{
Console.Write(Sheet.Rows[i][j]);
if (j < Sheet.Columns.Count - 1)
{
Console.Write(" -> ");
}
}
Console.WriteLine();
}
}
}
/// <summary>
/// Gets the list of worksheets in the spreadsheet.
/// </summary>
///
public string[] GetWorksheetList()
{
string[] worksheets;
OleDbConnection connection = new OleDbConnection(strConnection);
connection.Open();
DataTable tableWorksheets = connection.GetSchema("Tables");
connection.Close();
worksheets = new string[tableWorksheets.Rows.Count];
for (int i = 0; i < worksheets.Length; i++)
{
worksheets[i] = (string)tableWorksheets.Rows[i]["TABLE_NAME"];
worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
// removes the trailing $ and other characters appended in the table name
while (worksheets[i].EndsWith("$", StringComparison.Ordinal))
worksheets[i] = worksheets[i].Remove(worksheets[i].Length - 1).Trim('"', '\'');
}
return worksheets;
}
private DataTable GetTableColumns(string tName)
{
DataTable tc = new DataTable();
try
{
if (this.raExportAccess.Checked == true)
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = this.txtDataSource.Text;
conn.Open();
string[] Restrictions = new string[4];
Restrictions[2] = tName;
tc = conn.GetSchema("Columns", Restrictions);
return tc;
}
else if (this.raExportMSSQL.Checked == true)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = this.txtDataSource.Text;
conn.Open();
string[] Restrictions = new string[4];
Restrictions[2] = tName;
tc = conn.GetSchema("Columns", Restrictions);
return tc;
}
else if (this.raExportMySql.Checked == true)
{
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = this.txtDataSource.Text;
conn.Open();
string[] Restrictions = new string[4];
Restrictions[2] = tName;
tc = conn.GetSchema("Columns", Restrictions);
return tc;
}
return tc;
}
catch (System.Exception)
{
return null;
}
}
private void FillAccessTable()
{
if (this.comTableName.Items.Count != 0)
return;
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = this.txtDataSource.Text;
try
{
conn.Open();
}
catch (System.Exception ex)
{
MessageBox.Show(rm.GetString("Error12") + ex.Message, rm.GetString("MessageboxError"), MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
DataTable tb = conn.GetSchema("Tables");
foreach (DataRow r in tb.Rows)
{
if (r[3].ToString() == "TABLE")
{
this.comTableName.Items.Add(r[2].ToString());
}
}
}
/// <summary>
/// Creates a Boral CMC specific CSV file from an excel file with DDW information
/// </summary>
/// <param name="sourceXlsFile">Source excel file</param>
/// <param name="outputFolder">Destination folder for the resulting CSV file</param>
/// <returns></returns>
internal static string ApplyCsvHack(string sourceXlsFile, string outputFolder)
{
var finalFile = sourceXlsFile;
const string xlsConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";
var fileName = Path.GetFileNameWithoutExtension(sourceXlsFile);
if (!string.IsNullOrEmpty(fileName))
{
// the final file that will be returned is the hacked CSV file
finalFile = Path.Combine(outputFolder, fileName + ".csv");
// delete the CSV file if it already exists
if (File.Exists(finalFile)) File.Delete(finalFile);
using (var conn = new OleDbConnection(string.Format(xlsConnString, sourceXlsFile)))
{
conn.Open();
string[] sheetNames = conn.GetSchema("Tables").AsEnumerable().Select(a => a["TABLE_NAME"].ToString()).ToArray();
foreach (string sheetName in sheetNames)
{
using (var sw = new StreamWriter(File.Create(finalFile), Encoding.Unicode))
{
using (var ds = new DataSet())
{
using (var adapter = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), conn))
{
adapter.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
var dr = ds.Tables[0].Rows[i];
string[] cells = dr.ItemArray.Select(a => a.ToString()).ToArray();
// ensure this is not an empty row before we write it out to the file
if (string.Join(",", cells).Replace(",","").Trim() == string.Empty)
{
continue;
}
// check whether or not the current line is for a DDW header
if (cells[0].ToLower() == "name" && cells[1].ToLower() == "payrollnumber")
{
// use only the required DDW header columns (some columns will be blank)
sw.WriteLine("{0}", string.Join(",", cells.Take(12)));
// also write the DDW header values ignoring trailing commas
i++;
dr = ds.Tables[0].Rows[i];
cells = dr.ItemArray.Select(a => a.ToString()).ToArray();
sw.WriteLine("{0}", string.Join(",", cells.Take(12)));
}
else
{
sw.WriteLine("{0}", string.Join(",", cells));
}
}
}
}
}
var strQ = File.ReadAllText(finalFile);
File.WriteAllText(finalFile, strQ);
}
}
}
return finalFile;
}
/// <summary>
/// 取所有表名
/// </summary>
/// <returns></returns>
public List<string> GetTableNameList()
{
List<string> list = new List<string>();
OleDbConnection Conn = new OleDbConnection(connectionString);
try
{
if (Conn.State == ConnectionState.Closed)
Conn.Open();
DataTable dt = Conn.GetSchema("Tables");
foreach (DataRow row in dt.Rows)
{
if (row[3].ToString() == "TABLE")
list.Add(row[2].ToString());
}
return list;
}
catch (Exception e)
{ throw e; }
finally { if (Conn.State == ConnectionState.Open) Conn.Close(); Conn.Dispose(); }
}
protected void AsyncFileUpload1_UploadedComplete(object sender, AjaxControlToolkit.AsyncFileUploadEventArgs e)
{
// Save file
string filename = System.IO.Path.GetFileName(AsyncFileUpload1.FileName);
AsyncFileUpload1.SaveAs(Server.MapPath("~/Files/") + filename);
// HATC Order
string CustCode = "40106010";
filename = Server.MapPath("~/Files/") + filename;
if (File.Exists(filename))
{
//List<MT05> Data = new List<MT05>();
string CustomerCode = "SAAPC";
DataTable dt = new DataTable();
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
//MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\ROKI\WorkEDI\EDI DATA_20170301\SD\KMT\Forecast_KMT.xls;Extended Properties=Excel 8.0;HDR=YES;IMEX=1;");
//string filename = @"D:\ROKI\WorkEDI\EDI DATA_20170301\SD\NMT_X\NMT\GeneralForecast20160622.csv";
//string filename = @"D:\ROKI\WorkEDI\EDI DATA_20170301\SA\APC\Oct\2016-11_FORECAST ORDER 05386-45320_EDI.xls";
MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filename + "';Extended Properties='Excel 12.0;HDR=YES;'");
//provider=Microsoft.ACE.OLEDB.12.0;Data Source='D:\\Programming\\Spreadsheet-Current.xlsx';Extended Properties='Excel 12.0;HDR=YES;'
MyConnection.Open();
DataTable dtSheet = MyConnection.GetSchema("Tables");
string SheetName = dtSheet.Rows[0]["TABLE_NAME"].ToString().Trim();
myCommand.Connection = MyConnection;
//sql = "select * from [05386-45320$]";
sql = "select * from [" + SheetName + "]";
myCommand.CommandText = sql;
OleDbDataReader reader = myCommand.ExecuteReader();
//while (reader.Read())
//{
//var val1 = reader[0].ToString();
//}
//myCommand.ExecuteNonQuery();
//System.Data.OleDb.OleDbDataReader dr = new System.Data.OleDb.OleDbDataReader();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, MyConnection);
//Dim y As New Odbc.OdbcDataAdapter(sql, MyConnection);
//dr = myCommand.ExecuteReader();
da.Fill(dt);
MyConnection.Close();
//MessageBox.Show("Success");
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
}
string issueDate = "";
if (dt.Rows.Count > 0)
{
issueDate = dt.Rows[4][5].ToString().Trim();
issueDate = issueDate.Replace("ISSUED DATE : ", " ");
List <MIndex> itemHead = new List <MIndex>();
using (SqlProcedure sp = new SqlProcedure("sp_APC_ForcastMonth_ClearData"))
{
sp.ExecuteNonQuery();
}
for (int i = 0; i < dt.Columns.Count; i++)
{
string mergeDate = "01/" + dt.Rows[6][i].ToString().Trim() + "/2008";
//mergeDate = mergeDate;
if (IsDateTime(mergeDate) == true)
{
if (IsDateTime("01/" + dt.Rows[6][i + 3].ToString().Trim() + "/2008") == true)
{
//MessageBox.Show(mergeDate);
MIndex iMonth = new MIndex();
if (Convert.ToDateTime(issueDate).Month < Convert.ToDateTime(mergeDate).Month)
{
if (Convert.ToDateTime(issueDate).Year < 2000)
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + (Convert.ToDateTime(issueDate).Year + 543).ToString();
}
else
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + Convert.ToDateTime(issueDate).Year.ToString();
}
iMonth.iTotal = i + 2;
}
else
{
if (Convert.ToDateTime(issueDate).Year < 2000)
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + (Convert.ToDateTime(issueDate).AddYears(1).Year + 543).ToString();;
}
else
{
iMonth.iMonth = "01/" + dt.Rows[6][i].ToString().Trim() + "/" + Convert.ToDateTime(issueDate).AddYears(1).Year.ToString();;
}
iMonth.iTotal = i + 2;
}
itemHead.Add(iMonth);
}
else
{
if (dt.Rows[6][i + 3].ToString().Trim() == "DROP")
{
//MessageBox.Show("Finish");
break;
}
}
}
}
if (itemHead.Count > 0)
{
for (int j = 9; j < dt.Rows.Count; j++)
{
if (SetNumber(dt.Rows[j][5].ToString().Trim()) > 0)
{
//MessageBox.Show(dt.Rows[j][5].ToString().Trim());
for (int iHead = 0; iHead < itemHead.Count; iHead++)
{
MyCompany.Data.Objects.APCForcastMonthImport Order = new MyCompany.Data.Objects.APCForcastMonthImport();
Order.OrderBy = CustCode;
Order.DeliveryDestination = "";
Order.CustomerMatCode = dt.Rows[j][6].ToString().Trim();
Order.PartsDevision = "1";
Order.CustomerPO = "";
Order.ReliabilityDevision = "P";
Order.Unit = "ST";
Order.PlngPeriod = "D";
Order.SAPCode = "";//SharedBusinessRules.getSAPCode(Order.CustomerMatCode);
//Order.DeliveryDate = itemHead[iHead].iMonth;
Order.DeliveryDate = Convert.ToDateTime(itemHead[iHead].iMonth.ToString().Substring(7, 4) + "-" + itemHead[iHead].iMonth.ToString().Substring(3, 3) + "-" + itemHead[iHead].iMonth.ToString().Substring(0, 2));
Order.Quantity = SetNumber(dt.Rows[j][itemHead[iHead].iTotal].ToString().Trim().Replace(",", "")).ToString().Trim();
Order.Insert();
//MT05 result = new MT05();
//result.OrderBy = CustomerCode;
//result.DeliveryDesination = "";
//result.CustomerMatCode = dt.Rows[j][6].ToString().Trim();
//result.PartsDivision = "1";
//result.CustomerPONo = "Not sure";
//result.ReliabilityDivision = "P";
//result.DeliveryDate = itemHead[iHead].iMonth;
//result.Quantity = SetNumber(dt.Rows[j][itemHead[iHead].iTotal].ToString().Trim().Replace(",", ""));
//result.Unit = "ST";
//result.PIngPeriod = "D";
//Data.Add(result);
}
}
}
}
}
}
}
private static string GetFirstSheet(OleDbConnection conn)
{
#if NET_2_0
DataTable dt = conn.GetSchema("Tables");
#else
DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
#endif
if (dt != null && dt.Rows.Count > 0)
return dt.Rows[0]["TABLE_NAME"].ToString();
else
throw new BadUsageException("A Valid sheet was not found in the workbook.");
}
public void LoadRepository()
{
//1. recursive delete \extracted [catching NonExisting exception]
string extractPath = @"..\..\extracted";
if (Directory.Exists(extractPath))
{
Directory.Delete(extractPath, true);
}
//2. create \extracted
Directory.CreateDirectory(extractPath);
//3. extract from sales_reports.zip into extracted
string zipPath = @"..\..\sales_reports.zip";
using (var zip1 = ZipFile.Read(zipPath))
{
foreach (var entry in zip1)
{
entry.Extract(extractPath, ExtractExistingFileAction.OverwriteSilently);
}
}
//4. get \extracted directories
var subfolders = Directory.GetDirectories(extractPath);
//5. foreach the directories
foreach (var dateFolderPath in subfolders)
{
string dateFolder = dateFolderPath.Split('\\').Last();
// parsing the date (parse fail = skip)
DateTime saleDate;
if (!DateTime.TryParse(dateFolder, out saleDate))
{
continue;
}
// get filenames
var saleReportsByDealer = Directory.GetFiles(dateFolderPath);
// foreach the filenames
foreach (var reportPath in saleReportsByDealer)
{
// check if .xls extension (fail = skip)
var fileInfo = new FileInfo(reportPath);
string extension = fileInfo.Extension;
if (extension != ".xls")
{
continue;
}
// parse int for DealerId (fail = skip)
string filename = fileInfo.Name.Split('.').First();
int dealerId;
if (!int.TryParse(filename, out dealerId))
{
continue;
}
// connect via OleDb + pull the rows + close connection
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + reportPath + ";"
+ "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
OleDbConnection db_Con = new OleDbConnection(connectionString);
#region Connection
db_Con.Open();
using (db_Con)
{
// this code is for discovering sheet names if they are not available
DataTable tables = db_Con.GetSchema("Tables");
var sheetNameList = new List<string>();
for (int i = 0; i < tables.Rows.Count; i++)
{
var name = tables.Rows[i][2].ToString();
sheetNameList.Add(name);
}
foreach (var sheetName in sheetNameList)
{
Sale sale = new Sale() { DealerId = dealerId, SaleDate = saleDate };
this.repo.Add<Sale>(sale);
string sqlString = "select * from [" + sheetName + "];";
OleDbCommand cmdGetRows = new OleDbCommand(sqlString, db_Con);
//cmdGetRows.Parameters.AddWithValue("@sheetName", sheetName);
OleDbDataReader reader = cmdGetRows.ExecuteReader();
using (reader)
{
SaleDetails details;
while (reader.Read())
{
details = this.DetailsRow(this.repo, reader, sale);
if (details != null)
{
this.repo.Add<SaleDetails>(details);
}
}
}
}
}//using db_Con
#endregion
}
}
}
public List<table> getTables(string cadconexion, string database)
{
OleDbConnection conexion = null;
try
{
List<table> lista = new List<table>();
conexion = new OleDbConnection(cadconexion);
miComando = new OleDbCommand("");
miComando.Connection = conexion;
conexion.ConnectionString = cadconexion;
conexion.Open();
System.Data.DataTable dt = new System.Data.DataTable();
dt = conexion.GetSchema(OleDbMetaDataCollectionNames.Tables, new String[] { null, null, null, null });
foreach (System.Data.DataRow rowDatabase in dt.Rows)
{
// exclude system tables...
if (rowDatabase["table_name"].ToString().IndexOf("TMP") == -1 && rowDatabase["table_name"].ToString().IndexOf("MSys") == -1 && rowDatabase.ItemArray[3].Equals("TABLE"))
{
string tableName = rowDatabase["table_name"].ToString();
table tab = new table();
tab.Name = tableName;
tab.TargetName = tab.Name;
lista.Add(tab);
}
}
return lista;
}
catch (Exception ep)
{
// lo.tratarError(ep, "Error en dbClass.new", "");
return null;
}
finally
{
conexion.Close();
}
}
public ActionResult Importar(HttpPostedFileBase upload)
{
try
{
if (upload != null && upload.ContentLength > 0)
{
//string filePath = Path.Combine(HttpContext.Server.MapPath("~/Uploads"), Path.GetFileName(upload.FileName));
string file = Path.GetFileName(upload.FileName);
//string path = WebConfigurationManager.AppSettings["CarpetaUpload"].ToString();
//string filePath = @"" + path + file;
string filePath = Path.Combine(System.Web.HttpContext.Current.Server.MapPath(@"~/"), file);
//finalFileNameWithPath = string.Format("{0}.xlsx", currentDirectorypath);
upload.SaveAs(filePath);
DataSet ds = new DataSet();
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
// string ConnectionString = WebConfigurationManager.AppSettings["ExcelConn"].ToString();
using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
{
conn.Open();
using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
{
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
string query = "SELECT * FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
adapter.Fill(ds, "Items");
if (ds.Tables.Count > 0)
{
List <Articulo> la = new List <Articulo>();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//TODO: POR SI HAY QUE ACTUALIZAR PRECIOS
//var idArt = 0;
var nombreArt = "";
var precioCompra = 0m;
var precioVenta = 0m;
var rubroId = 0;
var stockMax = 0;
var stockMin = 0;
var stockAct = 0;
var stockSuc1 = 0;
var stockSuc2 = 0;
//try { idProd = int.Parse(ds.Tables[0].Rows[i].ItemArray[0].ToString()); }
//catch { }
try { nombreArt = ds.Tables[0].Rows[i].ItemArray[0].ToString(); }
catch { }
try { precioCompra = decimal.Parse(ds.Tables[0].Rows[i].ItemArray[1].ToString()); }
catch { }
try { precioVenta = decimal.Parse(ds.Tables[0].Rows[i].ItemArray[2].ToString()); }
catch { }
try { rubroId = int.Parse(ds.Tables[0].Rows[i].ItemArray[3].ToString()); }
catch { }
try { stockMax = int.Parse(ds.Tables[0].Rows[i].ItemArray[4].ToString()); }
catch { }
try { stockMin = int.Parse(ds.Tables[0].Rows[i].ItemArray[5].ToString()); }
catch { }
try { stockAct = int.Parse(ds.Tables[0].Rows[i].ItemArray[6].ToString()); }
catch { }
try { stockSuc1 = int.Parse(ds.Tables[0].Rows[i].ItemArray[7].ToString()); }
catch { }
try { stockSuc2 = int.Parse(ds.Tables[0].Rows[i].ItemArray[8].ToString()); }
catch { }
Articulo a = new Articulo();
a.Nombre = nombreArt;
//Lleno el Nombre etiqueta. Si es necesario lo corto
var LongitudNombreEtiqueta = _configuracionServicios.GetLongitudNombreEtiqueta();
var LongitudNombre = a.Nombre.Length;
if (LongitudNombre > LongitudNombreEtiqueta)
{
a.NombreEtiqueta = a.Nombre.Remove(LongitudNombreEtiqueta, LongitudNombre - LongitudNombreEtiqueta);
}
else
{
a.NombreEtiqueta = a.Nombre;
}
a.PrecioActualCompra = precioCompra;
a.PrecioActualVenta = precioVenta;
a.RubroID = rubroId;
a.StockMaximo = stockMax;
a.StockMinimo = stockMin;
a.Habilitado = true;
//TODO (Agregar sucursales dinámicamente)
//Esto es Harcodeo del bueno!! Ssssabor! Azúca!
a.Stock = new List <StockArticuloSucursal>();
//Stock deposito
a.Stock = AsignoStockASucursales(stockAct, stockSuc1, stockSuc2);
la.Add(a);
}
}
_articulosServicios.ImportarArticulos(la);
ViewBag.Informacion = "Los articulos se han importado correctamente!";
}
}
}
}
}
catch (Exception ex)
{
ViewBag.Error = "Ocurrió un error al intentar importar los precios. Revise el archivo .xlsx" + ex;
}
return(View());
}
private Boolean convertJetDB(string sourceFileName, string destinationFileName)
{
Boolean success = false;
this.infoTextBox.AppendText("Opening JetDB Database...\r\n");
string message;
string caption;
MessageBoxButtons buttons;
DialogResult result;
//Open JetDB SQL Database
System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source= " + sourceFileName;
try
{
conn.Open();
this.infoTextBox.AppendText("JetDB Database Opened.\r\n");
// Insert code to process data.
//Get a list of all tables in DB
DataTable userTables = conn.GetSchema("Tables");
List <string> tablesList = new List <string>();
for (int i = 0; i < userTables.Rows.Count; i++)
{
tablesList.Add(userTables.Rows[i][2].ToString());
}
string[] tableArray = tablesList.ToArray();
DataSet dataSet = new DataSet();
//Now we retrieve data from each table with a select statement and add that data into the dataset object
foreach (string table in tableArray)
{
if (debugFlag)
{
this.infoTextBox.AppendText("Processing Table: " + table + "\r\n");
}
if ((table != "MSysACEs") && (table != "MSysObjects") && (table != "MSysQueries") && (table != "MSysRelationships")) //These tables give us read errors
{
DataTable dataTable = dataSet.Tables.Add(table);
using (OleDbCommand readRows = new OleDbCommand("SELECT * from " + table, conn)) //Use select command to get all data
{
OleDbDataAdapter adapter = new OleDbDataAdapter(readRows);
adapter.Fill(dataTable);
}
}
}
//Finally, we convert the dataset into a JSON string and save it into the output file.
string json = string.Empty;
json = JsonConvert.SerializeObject(dataSet, Newtonsoft.Json.Formatting.Indented);
File.WriteAllText(destinationFileName, json);
success = true;
}
catch (Exception error)
{
this.infoTextBox.AppendText("Failed to connect to data source. Error reading source file: \r\n" + error.ToString() + "\r\nCannot continue.\r\n");
message = "Failed to connect to data source. Error reading source file: \r\n" + error.ToString() + "\r\nCannot continue.\r\n";
caption = "Failed to connect to data source.";
buttons = MessageBoxButtons.OK;
result = MessageBox.Show(message, caption, buttons);
this.infoTextBox.AppendText("Error reading source file: " + result.ToString() + "\r\n");
success = false;
}
finally
{
conn.Close();
this.infoTextBox.AppendText("JetDB Database Closed.\r\n");
}
return(success);
}
public static bool TableExists(string connString, string table)
{
bool functionReturnValue = false;
//// Open connection to the database
using (OleDbConnection conn = new OleDbConnection(connString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
//// Specify restriction to get table definition schema
//// For reference on GetSchema see:
//// http://msdn2.microsoft.com/en-us/library/ms254934(VS.80).aspx
string[] restrictions = new string[4];
restrictions[2] = table;
DataTable dbTbl = conn.GetSchema("Tables", restrictions);
functionReturnValue = dbTbl.Rows.Count != 0;
dbTbl.Dispose();
}
return functionReturnValue;
}
public void GetSchema_Connection_Closed ()
{
OleDbConnection cn = new OleDbConnection ();
try {
cn.GetSchema ();
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#B3");
Assert.IsNotNull (ex.Message, "#B4");
}
try {
cn.GetSchema ("Tables");
Assert.Fail ("#B1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
Assert.IsNull (ex.InnerException, "#B3");
Assert.IsNotNull (ex.Message, "#B4");
}
try {
cn.GetSchema ((string) null);
Assert.Fail ("#C1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#C2");
Assert.IsNull (ex.InnerException, "#C3");
Assert.IsNotNull (ex.Message, "#C4");
}
try {
cn.GetSchema ("Tables", new string [] { "master" });
Assert.Fail ("#D1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#D2");
Assert.IsNull (ex.InnerException, "#D3");
Assert.IsNotNull (ex.Message, "#D4");
}
try {
cn.GetSchema ((string) null, new string [] { "master" });
Assert.Fail ("#E1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#E2");
Assert.IsNull (ex.InnerException, "#E3");
Assert.IsNotNull (ex.Message, "#E4");
}
try {
cn.GetSchema ("Tables", (string []) null);
Assert.Fail ("#F1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#F2");
Assert.IsNull (ex.InnerException, "#F3");
Assert.IsNotNull (ex.Message, "#F4");
}
try {
cn.GetSchema ((string) null, (string []) null);
Assert.Fail ("#G1");
} catch (InvalidOperationException ex) {
// Invalid operation. The connection is closed
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#G2");
Assert.IsNull (ex.InnerException, "#G3");
Assert.IsNotNull (ex.Message, "#G4");
}
}
public override bool process(string url, string filename, string source)
{
bool ret = true;
OleDbConnection con = null;
LibSys.StatusBar.Trace("IP: processing MDB file: " + filename);
try
{
con = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;data source=" + filename + "");
con.Open(); //connection must be opened
DataTable dt = con.GetSchema("Tables");
DataRow row = dt.Select("TABLE_TYPE='TABLE'")[0];
string tableName = row["TABLE_NAME"].ToString();
OleDbCommand cmd = new OleDbCommand("SELECT * from [" + tableName + "]", con); // creating query command
OleDbDataReader reader = cmd.ExecuteReader(); // executes query
int i = 0;
int errCnt = 0;
while (reader.Read()) // if can read row from database
{
try
{
SectionRow sr = new SectionRow()
{
RECRD = (int)reader.GetValue(0),
VESSLTERMS = reader.GetValue(1).ToString(),
CHART = reader.GetValue(2).ToString(),
AREA = reader.GetValue(3).ToString(),
CARTOCODE = reader.GetValue(4).ToString(),
SNDINGCODE = reader.GetValue(5).ToString(),
DEPTH = reader.GetValue(6).ToString(),
NATIVLAT = reader.GetValue(7).ToString(),
NATIVLON = reader.GetValue(8).ToString(),
LAT83 = reader.GetValue(9).ToString(),
LONG83 = reader.GetValue(10).ToString(),
LATDEC = (double)reader.GetValue(11),
LONDEC = -(double)reader.GetValue(12),
NATIVDATUM = reader.GetValue(13).ToString(),
CONVERT83 = reader.GetValue(14).ToString(),
GPACCURACY = reader.GetValue(15).ToString(),
GPQUALITY = reader.GetValue(16).ToString(),
GPSOURCE = reader.GetValue(17).ToString(),
QUADRANT = reader.GetValue(18).ToString(),
History = reader.GetValue(19).ToString(),
REFERENCE = reader.GetValue(20).ToString(),
YEARSUNK = reader.GetValue(21).ToString()
};
CreateInfo createInfo = new CreateInfo(); // we will recycle it in place, filling every time.
createInfo.init("wpt");
createInfo.name = (sr.VESSLTERMS + " " + sr.DEPTH).Trim();
createInfo.desc = sr.YEARSUNK;
createInfo.lat = sr.LATDEC;
createInfo.lng = sr.LONDEC;
createInfo.typeExtra = "unknown"; // type: ppl, school, park, locale, airport, reservoir, dam, civil, cemetery, valley, building
createInfo.source = source;
createInfo.comment = sr.History.Replace(". ", ".\r\n") + ";\r\n" + sr.REFERENCE;
m_insertWaypoint(createInfo);
}
catch (Exception excr)
{
errCnt++;
}
i++;
}
LibSys.StatusBar.Trace("OK: MDB file: '" + filename + "' processed; found table[" + tableName + "] records/waypoints count=" + i + " errors count=" + errCnt);
}
catch (Exception e)
{
LibSys.StatusBar.Error("FileAwoisMdb process() " + e.Message);
ret = false;
}
finally
{
if (con != null)
{
con.Close();
}
}
return ret;
}
public ActionResult Upload(HttpPostedFileBase uploadFile)
{
StringBuilder strValidations = new StringBuilder(string.Empty);
try
{
if (uploadFile.ContentLength > 0)
{
string filePath = Path.Combine(HttpContext.Server.MapPath("../Uploads"),
Path.GetFileName(uploadFile.FileName));
uploadFile.SaveAs(filePath);
DataSet ds = new DataSet();
//A 32-bit provider which enables the use of
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
{
conn.Open();
using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
{
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
string query = "SELECT * FROM [" + sheetName + "]";
//string query = "SELECT TradeDescription_Description, BasicType, BasicType_SGI, BasicType_CSI, BasicType_ECCN, SalesItem FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
//DataSet ds = new DataSet();
adapter.Fill(ds, "Items");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
//Now we can insert this data to database...
var convertcsi = false;
Int32 convertsgi = Convert.ToInt32(ds.Tables[0].Rows[i][2].ToString());
if (ds.Tables[0].Rows[i][3].ToString() == "Yes")
{
convertcsi = true;
}
else
{
convertcsi = false;
}
//Int32 convertcsi = Convert.ToBoolean(ds.Tables[0].Rows[i][3].ToString());
//Int32 convertsalesitem = Convert.ToInt32(ds.Tables[0].Rows[i][5].ToString());
var dbline = new Product {
Name = ds.Tables[0].Rows[i][0].ToString(), Phone = ds.Tables[0].Rows[i][1].ToString(), Id = convertsgi /*,Id = convertcsi,*//* BasicType_ECCN = ds.Tables[0].Rows[i][4].ToString(),*//* Address = convertsalesitem */
};
db.Products.Add(dbline);
db.SaveChanges();
}
}
}
}
}
}
return(RedirectToAction("Index"));//Testing Purpose
}
catch (Exception ex) { }
return(RedirectToAction("Edit"));//Testing Purpose
}
public string GetColumnType(SqlHelper sqlHelper, string dataBaseName, string tableName, string columnName)
{
if (tableName != _lastTableName || _tableInfos == null)
{
_tableInfos = new List <dynamic>();
var list = new List <dynamic>();
var con = new System.Data.OleDb.OleDbConnection(sqlHelper.DbConnectionString);
con.Open();
DataTable dt = con.GetSchema("columns", new string[] { null, null, tableName });
foreach (DataRow dr in dt.Rows)
{
string type = dr["data_type"].ToString().ToLower();
//2 SmallInt,
if (type == "2")
{
type = "short";
}
//3 int,
else if (type == "3")
{
type = "int";
}
//4 real,
else if (type == "4")
{
type = "float";
}
//5 float
else if (type == "5")
{
type = "double";
}
//6 Money,131 Decimal
else if (type == "6" || type == "131")
{
type = "decimal";
}
//7 DateTime,13 TimeStamp,133 DateTime,135 SmallDateTime
else if (type == "7" || type == "13" || type == "133" || type == "135")
{
type = "DateTime";
}
//11 bit
else if (type == "11")
{
type = "bool";
}
//17 TinyInt
else if (type == "17")
{
type = "byte";
}
//128 Binary,204 Binary,205 Image
else if (type == "128" || type == "204" || type == "205")
{
type = "byte[]";
}//129 Char,130 NChar,200 VarChar,201 Text,202 VarChar,203 Text
else if (type == "129" || type == "130" || type == "200" || type == "201" || type == "202" || type == "203")
{
type = "string";
}
else
{
type = "string";
}
dynamic tableInfo = new ExpandoObject();
tableInfo.columnName = dr["column_name"].ToString();
tableInfo.type = type;
_tableInfos.Add(tableInfo);
}
con.Close();
con.Dispose();
}
_lastTableName = tableName;
foreach (dynamic item in _tableInfos)
{
if (item.columnName == columnName.ToLower())
{
return(item.type);
}
}
return("string");
}
public ActionResult Save(IEnumerable <HttpPostedFileBase> attachments)
{
// The Name of the Upload component is "attachments"
foreach (var file in attachments)
{
// Some browsers send file names with full path. This needs to be stripped.
var fileName = Path.GetFileName(file.FileName);
var physicalPath = Path.Combine(Server.MapPath("~/App_Imports"), fileName);
string exteension = Path.GetExtension(fileName);
// The files are not actually saved in this demo
int counter = 1;
int verifica = 0;
while (System.IO.File.Exists(physicalPath))
{
counter++;
physicalPath = Path.Combine(HttpContext.Server.MapPath("~/App_Imports/"),
Path.GetFileNameWithoutExtension(fileName) + counter.ToString() + Path.GetExtension(fileName));
}
file.SaveAs(physicalPath);
DataSet dss = new DataSet();
string ConnectionString = "";
if (exteension == ".xls")
{
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + physicalPath + ";Extended Properties=Excel 8.0;";
verifica = 1;
}
if (exteension == ".xlsx")
{
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + physicalPath + ";Extended Properties=Excel 12.0;";
verifica = 1;
}
if (verifica == 0)
{
throw new Exception("Extensão não suportadaErro ao Salvar");
}
using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
{
conn.Open();
using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
{
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
string query = "SELECT * FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
adapter.Fill(dss, "Items");
if (dss.Tables.Count > 0)
{
if (dss.Tables[0].Rows.Count > 0)
{
try
{
for (int i = 0; i < dss.Tables[0].Rows.Count; i++)
{
UsuarioRegional usuarioregional = new UsuarioRegional();
int id;
// colocar as colunas aqui para importacao
//tentar customizar no .tt
// na index desta controller ao final do arquivo, gerou um codigo padrao para colocar aqui
try
{
db.UsuarioRegional.Add(usuarioregional);
}
catch (Exception erro)
{
throw new Exception(erro.ToString());
//return RedirectToAction("ErroAoSalvar");
}
usuarioregional = null;
}
}
catch (Exception erro)
{
string err = "<b>Erro Gerado na importação do arquivo, consulte os detalhes para mais informações </b> ";
err += "</br>";
err += _Funcoes.TrataErro(erro);
err += "</br>";
throw new Exception(err.ToString());
}
try
{
db.SaveChanges();
return(RedirectToAction("Index"));
}
catch (Exception dbEx)
{
if (dbEx is System.Data.Entity.Validation.DbEntityValidationException)
{
string errors = "O Arquivo não é válido, verifique as propriedades abaixo para mais detalhes </br> "; // dbEx.EntityValidationErrors.First(); //.ValidationErrors.First();
errors += "<b> Nenhum registro foi gravado.</b> A importação só será possível com o arquivo 100% correto. </br> ";
DbEntityValidationException ex = (DbEntityValidationException)dbEx;
foreach (var validationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in validationErrors.ValidationErrors)
{
errors += string.Format(" A propriedade : <b>{0}</b> não foi validado devido ao erro: <b> {1} </b>", validationError.PropertyName, validationError.ErrorMessage) + "</br>";
}
}
throw new Exception(errors.ToString());
}
else if (dbEx is System.Data.Entity.Infrastructure.DbUpdateException)
{
string err = "<b>Erro Gerado, consulte os detalhes para mais informações </b> ";
err += "</br>";
err += _Funcoes.TrataErro(dbEx);
err += "</br>";
err += dbEx.InnerException.InnerException.ToString();
throw new Exception(err.ToString());
}
else
{
string err = "<b>Erro Gerado, consulte os detalhes para mais informações </b> ";
err += "</br>";
err += _Funcoes.TrataErro(dbEx);
err += "</br>";
throw new Exception(err.ToString());
}
}
}
}
}
}
// Return an empty string to signify success
}
return(Content(""));
}
public ActionResult ImportNha(HttpPostedFileBase uploadFile)
{
StringBuilder strValidations = new StringBuilder(string.Empty);
try
{
if (uploadFile.ContentLength > 0)
{
string filePath = Path.Combine(HttpContext.Server.MapPath("/Uploads/files"), Path.GetFileName(uploadFile.FileName));
uploadFile.SaveAs(filePath);
DataSet ds = new DataSet();
string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
{
conn.Open();
using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
{
string sheetName = "Data$";
string query = "SELECT * FROM [" + sheetName + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
adapter.Fill(ds, "Items");
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
Nha nha = new Nha();
//fdsfdsf
int resultCount = 0;
int recordExcelCount = ds.Tables[0].Rows.Count;
using (TransactionScope tscope = new TransactionScope())
{
foreach (DataRow r in ds.Tables[0].Rows)
{
string matBang = Convert.ToString(r["Mặt bằng"]);
long matBangId = (_repository.GetRepository <MatBang>().Read(o => o.Name == matBang)) != null ? (_repository.GetRepository <MatBang>().Read(o => o.Name == matBang)).Id : 0;
string quan = Convert.ToString(r["Quận"]);
long quanId = (_repository.GetRepository <Quan>().Read(o => o.Name == quan)).Id;
string duong = Convert.ToString(r["Đường"]);
long duongId = (_repository.GetRepository <Duong>().Read(o => o.Name == duong)).Id;
string noiThatKhachThueCu = Convert.ToString(r["Nội thất khách thuê cũ"]);
long noiThatKhachThueCuId = (_repository.GetRepository <NoiThatKhachThueCu>().Read(o => o.Name == noiThatKhachThueCu)) != null ? (_repository.GetRepository <NoiThatKhachThueCu>().Read(o => o.Name == noiThatKhachThueCu)).Id : 1;
string danhGiaPhuHopVoi = Convert.ToString(r["Đánh giá phù hợp với"]);
long danhGiaPhuHopVoiId = (_repository.GetRepository <DanhGiaPhuHopVoi>().Read(o => o.Name == danhGiaPhuHopVoi)) != null ?(_repository.GetRepository <DanhGiaPhuHopVoi>().Read(o => o.Name == danhGiaPhuHopVoi)).Id : 0;
string capDoTheoDoi = Convert.ToString(r["Cấp độ theo dõi"]);
int capDoTheoDoiId = (_repository.GetRepository <CapDoTheoDoi>().Read(o => o.Name == capDoTheoDoi)) != null? (_repository.GetRepository <CapDoTheoDoi>().Read(o => o.Name == capDoTheoDoi)).Id : 1;
nha.MatBangId = matBangId == 0 ? null : Convert.ToString(matBangId);
nha.QuanId = quanId;
nha.DuongId = duongId;
nha.SoNha = r["Số nhà"] == DBNull.Value ? "" : r["Số nhà"].ToString();
nha.TenToaNha = r["Tên toàn nhà"] == DBNull.Value ? "" : (string)r["Tên toàn nhà"];
nha.MatTienTreoBien = r["Mặt tiền treo biển"] == DBNull.Value ? 0 : float.Parse(r["Mặt tiền treo biển"].ToString());
nha.BeNgangLotLong = r["Bề ngang lọt lòng"] == DBNull.Value ? 0 : float.Parse(r["Bề ngang lọt lòng"].ToString());
nha.DienTichDat = r["Diện tích đất"] == DBNull.Value ? 0 : float.Parse(r["Diện tích đất"].ToString());
nha.DienTichDatSuDungTang1 = r["Diện tích đất sử dụng tầng 1"] == DBNull.Value ? 0 : float.Parse(r["Diện tích đất sử dụng tầng 1"].ToString());
nha.SoTang = r["Số tầng"] == DBNull.Value ? 0 : int.Parse(r["Số tầng"].ToString());
nha.TongDienTichSuDung = r["Tổng diện tích sử dụng"] == DBNull.Value ? 0 : int.Parse(r["Tổng diện tích sử dụng"].ToString());
nha.DiChungChu = (string)r["Đi chung chủ"] == "Có" ? true : false;
nha.Ham = (string)r["Hầm"] == "Có" ? true : false;
nha.ThangMay = (string)r["Thang máy"] == "Có" ? true : false;
nha.NoiThatKhachThueCuId = noiThatKhachThueCuId;
nha.DanhGiaPhuHopVoiId = danhGiaPhuHopVoiId == 0? null : Convert.ToString(danhGiaPhuHopVoiId);
nha.TongGiaThue = r["Tổng giá thuê"] == DBNull.Value ? 0 : decimal.Parse(r["Tổng giá thuê"].ToString());
nha.GiaThueBQ = r["Giá thuê BQ"] == DBNull.Value ? 0 : decimal.Parse(r["Giá thuê BQ"].ToString());
nha.TenNguoiLienHeVaiTro = r["Tên người liên hệ - vai trò"] == DBNull.Value ? "" : (string)r["Tên người liên hệ - vai trò"];
nha.SoDienThoai = r["Số điện thoại"] == DBNull.Value ? "" : r["Số điện thoại"].ToString();
nha.NgayCNHenLienHeLai = r["Ngày CN hẹn liên hệ lại"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(r["Ngày CN hẹn liên hệ lại"]);
nha.CapDoTheoDoiId = capDoTheoDoiId;
nha.GhiChu = r["Ghi chú"] == DBNull.Value ? "" : (string)r["Ghi chú"];
nha.NgayTao = DateTime.Now;
nha.NguoiTaoId = AccountId;
nha.TrangThai = 0; //Chờ duyệt
int result = 0;
try
{
result = _repository.GetRepository <Nha>().Create(nha, AccountId);
}
catch { }
if (result > 0)
{
resultCount++;
}
}
if (resultCount == recordExcelCount)
{
tscope.Complete();
TempData["Success"] = "Import dữ liệu thành công!";
return(RedirectToAction("Index"));
}
else
{
Transaction.Current.Rollback();
tscope.Dispose();
TempData["Error"] = "Import dữ liệu không thành công, vui lòng thử lại!";
return(RedirectToAction("Index"));
}
}
}
}
}
}
}
}
catch (Exception ex)
{
TempData["Error"] = "Import dữ liệu không thành công, vui lòng thử lại!";
return(RedirectToAction("Index"));
}
return(RedirectToAction("Index"));
}
