private String[] GetExcelSheetNames(string excelFile, bool blnXlsx = false)
{
System.Data.OleDb.OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
String connString = null;
if (blnXlsx)
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1\"";
}
else
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
}
objConn = new System.Data.OleDb.OleDbConnection(connString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i += 1;
}
return excelSheets;
}
catch (Exception ex)
{
throw (new Exception("Cannot Read Excel Sheet Names -" + ex.Message));
}
finally
{
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
public Form1()
{
InitializeComponent();
comboBox2.Text = "Column";
comboBox2.Items.Add("Column");
comboBox2.Items.Add("Lines");
comboBox2.Items.Add("Pie");
comboBox2.Items.Add("Bar");
comboBox2.Items.Add("Funnel");
comboBox2.Items.Add("PointAndFigure");
comboBox1.Items.Clear();
if (System.IO.File.Exists("your_base.mdb"))
{
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in cbTb.Rows)
{
string tbName = row["TABLE_NAME"].ToString();
comboBox1.Items.Add(tbName);
}
connectDb.Close();
}
}
public static bool AnalyzeExcel(ExcelXMLLayout layout)
{
System.Data.OleDb.OleDbConnection conn = null;
try
{
conn = new System.Data.OleDb.OleDbConnection(MakeConnectionString(layout.solution.path));
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, layout.sheet + "$", null });
layout.Clear();
System.Diagnostics.Debug.WriteLine("Start Analyze [" + table.Rows.Count + "]");
foreach (System.Data.DataRow row in table.Rows)
{
string name = row["Column_Name"].ToString();
System.Diagnostics.Debug.WriteLine(name);
// 测试数据类型
ExcelXMLLayout.KeyType testType = ExcelXMLLayout.KeyType.Unknown;
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
string.Format("select [{0}] from [{1}$]", name, layout.sheet), conn
);
System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
System.Diagnostics.Debug.WriteLine(r[0].GetType());
if (r[0].GetType() == typeof(System.Double))
{
testType = ExcelXMLLayout.KeyType.Integer;
break;
}
if (testType == ExcelXMLLayout.KeyType.String)
{
break;
}
testType = ExcelXMLLayout.KeyType.String;
}
r.Close();
cmd.Dispose();
}
layout.Add(name, testType);
}
table.Dispose();
conn.Close();
return true;
}
catch (Exception outErr)
{
lastError = string.Format("无法分析，Excel 无法打开\r\n{0}", outErr.Message);
}
return false;
}
public void ConvertExcelToCsv(int worksheetNumber = 1)
{
if (!System.IO.File.Exists(excelFilePath)) throw new FileNotFoundException(excelFilePath);
if (File.Exists(csvOutputFile)) throw new ArgumentException("File exists: " + csvOutputFile);
// connection string
var cnnStr = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO\"", excelFilePath);
var cnn = new System.Data.OleDb.OleDbConnection(cnnStr);
// get schema, then data
var dt = new DataTable();
try
{
cnn.Open();
var schemaTable = cnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (schemaTable.Rows.Count < worksheetNumber) throw new ArgumentException("The worksheet number provided cannot be found in the spreadsheet");
string worksheet = schemaTable.Rows[worksheetNumber - 1]["table_name"].ToString().Replace("'", "");
string sql = String.Format("select * from [{0}]", worksheet);
var da = new System.Data.OleDb.OleDbDataAdapter(sql, cnn);
da.Fill(dt);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
throw e;
}
finally
{
// free resources
cnn.Close();
}
// write out CSV data
using (var wtr = new StreamWriter(csvOutputFile))
{
foreach (DataRow row in dt.Rows)
{
bool firstLine = true;
foreach (DataColumn col in dt.Columns)
{
if (!firstLine) { wtr.Write(","); } else { firstLine = false; }
var data = row[col.ColumnName].ToString().Replace("\"", "\"\"");
wtr.Write(String.Format("\"{0}\"", data));
}
wtr.WriteLine();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (System.IO.File.Exists("your_base.mdb"))
{
comboBox1.Items.Clear();
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
DataTable cbTb = connectDb.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in cbTb.Rows)
{
string tbName = row["TABLE_NAME"].ToString();
comboBox1.Items.Add(tbName);
}
connectDb.Close();
}
else
{
MessageBox.Show("База данных еще не создана. Воспользуйтесь кнопкой 'загрузить данные' для создания БД.", "Внимание!", MessageBoxButtons.OK,
MessageBoxIcon.Warning);
}
}
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.DefaultExt = "*.xls;*.xlsx";
ofd.Filter = "Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx";
ofd.Title = "Выберите документ для загрузки данных";
if (ofd.ShowDialog() == DialogResult.OK)
{
textBox1.Text = ofd.FileName;
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
ofd.FileName +
";Extended Properties='Excel 12.0 XML;HDR=YES;';";
System.Data.OleDb.OleDbConnection con =
new System.Data.OleDb.OleDbConnection(constr);
con.Open();
DataSet ds = new DataSet();
DataTable schemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad =
new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
DataTable tb = ds.Tables[0];
con.Close();
dataGridView1.DataSource = tb;
con.Close();
}
else
{
MessageBox.Show("Вы не выбрали файл для открытия",
"Загрузка данных...", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private bool SaveCheck()
{
bool bFlag = true;
if (lblUploadFile.Text.Length == 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "请选择Excel工资文件！");
}
else if (GetOpCodeFieldName().Length == 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "请选择工号字段！");
}
else if (GetOpNameFieldName().Length == 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "请选择姓名字段！");
}
else
{
string _OpCodeFieldName = GetOpCodeFieldName();
string _OpNameFieldName = GetOpNameFieldName();
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "] Where 1<>1", strConn);
DataSet ds1 = new DataSet();
oada.Fill(ds1);
conn.Close();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string _SQL = "Select Top 1 1 From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_OpCodeFieldName].ToString() + "' And OpName<>'" + ds.Tables[0].Rows[i][_OpNameFieldName].ToString() + "'";
if (SysClass.SysGlobal.GetExecSqlIsExist(_SQL))
{
DataRow row = ds1.Tables[0].NewRow();
for (int k = 0; k < ds1.Tables[0].Columns.Count; k++)
{
row[ds1.Tables[0].Columns[k].ColumnName] = ds.Tables[0].Rows[i][ds1.Tables[0].Columns[k].ColumnName].ToString();
}
ds1.Tables[0].Rows.Add(row);
}
}
gvAbnormal.DataSource = ds1.Tables[0].DefaultView;
gvAbnormal.DataBind();
gvAbnormal.Visible = gvAbnormal.Rows.Count > 0;
if (gvAbnormal.Rows.Count > 0)
{
bFlag = false;
Dialog.OpenDialogInAjax(txtOrganName, "下列工号和姓名与系统内有异，请检查！");
}
}
return(bFlag);
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (SaveCheck())
{
string sUpdateSQL = "", sEditSQL = "", sCustomInsertSQL = "", sCustomEditSQL = "";
lblProcess.Text = "正在导入数据，请稍候。";
DateTime dtbegin = DateTime.Now;
int iUp = 0;
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
conn.Close();
DataSet dsImportSet = LoadImportSetByDataSet();
string _GetOpCodeFieldName = GetOpCodeFieldName();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
sUpdateSQL = " if not Exists(Select top 1 1 From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "') "
+ " begin";
sCustomInsertSQL = " if not Exists(Select top 1 1 From SysUserExt_Info Where UserID in (Select ID From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "')) "
+ " begin";
sUpdateSQL += " Insert Into SysUser_Info (SystemID , Guid, OrganID, Password";
sEditSQL = " Update SysUser_Info Set SystemID=SystemID, OrganID=";
sCustomInsertSQL += " Insert Into SysUserExt_Info (UserID ";
sCustomEditSQL = " Update SysUserExt_Info Set UserID=SysUser_Info.ID";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
if (sTargetFieldName != "Organ")
{
if (sTargetFieldName.Length > 0)
{
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().IndexOf("|") > 0)
{
string _TargerName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
_TargerName = "Column_" + _TargerName.Substring(2, _TargerName.Length - 2);
sCustomInsertSQL += "," + _TargerName;
}
else
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
}
}
}
}
sUpdateSQL += ")"
+ " Values("
+ SysClass.SysParams.GetPurviewSystemID().ToString()
+ ", '" + SysClass.SysGlobal.GetCreateGUID() + "'";
sCustomInsertSQL += ")"
+ " (Select ID ";
string _OrganFieldName = GetOrganFieldName();
if ((_OrganFieldName.Length > 0) && (SysClass.SysOrgan.GetOrganIDByOrganName(ds.Tables[0].Rows[i][_OrganFieldName].ToString()) > 0))
{
int _TOrganID = SysClass.SysOrgan.GetOrganIDByOrganName(ds.Tables[0].Rows[i][_OrganFieldName].ToString());
if (_TOrganID == 0)
{
_TOrganID = _OrganID;
}
sUpdateSQL += ", " + _TOrganID.ToString();
sEditSQL += _TOrganID.ToString();
}
else
{
//sUpdateSQL += ", " + _OrganID.ToString();
sUpdateSQL += ", 58"; //不存在的单位导入临时单位下
sEditSQL += "OrganID";
}
sUpdateSQL += " ,'111111'";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string sSourceFieldName = dsImportSet.Tables[0].Rows[n]["SourceName"].ToString();
if (sTargetFieldName != "Organ")
{
if (sTargetFieldName.Length > 0)
{
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().IndexOf("|") > 0)
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
sCustomInsertSQL += ",'" + sValue + "'";
string _TargerName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string _A = _TargerName.Substring(0, 1);
_TargerName = _TargerName.Substring(2, _TargerName.Length - 2);
if (_A == "1")
{
sCustomEditSQL += ",Column_" + _TargerName + "='" + sValue + "'";
}
else
{
if (sValue.Length == 0)
{
sCustomEditSQL += "," + "Column_" + _TargerName + "=0";
}
else
{
sCustomEditSQL += "," + "Column_" + _TargerName + "=" + sValue;
}
}
}
else
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
string _TargerName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
if (_TargerName == "IsCanLogin")
{
if (sValue == "是")
{
sValue = "1";
}
else
{
sValue = "0";
}
sUpdateSQL += "," + sValue;
sEditSQL += "," + _TargerName + "=" + sValue + "";
}
else if (_TargerName == "Sex")
{
if (sValue == "女")
{
sValue = "1";
}
else
{
sValue = "0";
}
sUpdateSQL += ",'" + sValue + "'";
sEditSQL += "," + _TargerName + "='" + sValue + "'";
}
else
{
sUpdateSQL += ",'" + sValue + "'";
sEditSQL += "," + _TargerName + "='" + sValue + "'";
}
}
}
}
}
sCustomEditSQL += " From SysUser_Info Where SysUser_Info.OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "' And SysUserExt_Info.UserID=SysUser_Info.ID; ";
sCustomInsertSQL += " From SysUser_Info Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "'); end "
+ " else begin " + sCustomEditSQL + " end;";
sEditSQL += " Where OpCode='" + ds.Tables[0].Rows[i][_GetOpCodeFieldName].ToString() + "';";
sUpdateSQL += ");";
sUpdateSQL += " end else begin " + sEditSQL + " end; " + sCustomInsertSQL;
if (CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateSQL + " end;") > 0)
{
iUp += 1;
lblProcess.Text = "正在导入第" + iUp.ToString() + "条记录，总" + ds.Tables[0].Rows.Count.ToString() + "条";;
}
}
string _SQL = "begin"
+ " Insert Into SysUserRoles_Info (SystemID, UserID, RoleID) (Select 2, ID, 30 From SysUser_Info Where ID not in (Select UserID From SysUserRoles_Info Where SystemID=2));"
+ " Delete from SysUserRoles_Info Where UserID not in (Select ID From SysUser_Info);"
+ " end;";
CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + _SQL + " end;");
if (sUpdateSQL.Length > 0)
{
if (iUp > 0)
{
lblProcess.Text = "已经成功导入 " + iUp.ToString() + " 条记录。";
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入成功" + iUp.ToString() + " 条记录……", "User_Import.aspx?OrganID=" + _OrganID.ToString());
}
else
{
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入失败……");
}
}
}
}
public static DataSet ExcelUpload(HttpPostedFileBase file)
{
DataSet ds = new DataSet();
if (file.ContentLength > 0)
{
string fileExtension = System.IO.Path.GetExtension(file.FileName);
string UploadUrl = System.Configuration.ConfigurationManager.AppSettings["UploadUrl"].ToString() + DateTime.Now.ToString("yyyyMMdd") + "/";
UploadUrl = HttpContext.Current.Server.MapPath(UploadUrl);
MakeFolder(UploadUrl);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = UploadUrl + file.FileName.Replace(fileExtension, "") + DateTime.Now.ToString("yyyyMMddHHmmss") + fileExtension;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
file.SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
System.Data.OleDb.OleDbConnection excelConnection = new System.Data.OleDb.OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
string query = string.Empty; // string.Format("Select * from [{0}]", excelSheets[0]);
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
query = string.Format(" Select * from [{0}]", excelSheets[t]);
System.Data.OleDb.OleDbConnection excelConnection1 = new System.Data.OleDb.OleDbConnection(excelConnectionString);
//string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds, excelSheets[t]);
}
t++;
}
}
else if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = HttpContext.Current.Server.MapPath(UploadUrl) + HttpContext.Current.Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
HttpContext.Current.Request.Files["FileUpload"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
}
System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
//string sInsert = "INSERT INTO ExcelUploadData";
//for (int nSheet = 0; nSheet < ds.Tables.Count; nSheet++)
//{
// DataTable dt = ds.Tables[nSheet];
// for (int nRow = 0; nRow < dt.Rows.Count; nRow++)
// {
// sbSql.Append("\n").Append(" SELECT ");
// for (int nCol = 1; nCol <= dt.Columns.Count; nCol++)
// {
// }
// for (int n = 12 - dt.Columns.Count; n <= 12; n++)
// {
// sbSql.Append(", null");
// }
// sbSql.Append(", " + SessionHelper.UserInfo.AccountCode + ", GETDATE()" );
// if ((nRow + 1) % 10 == 0)
// {
// sbSql = new System.Text.StringBuilder();
// sbSql.Append("\n").Append(sInsert);
// }
// }
//}
return(ds);
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (fpUpload.HasFile)
{
string sWJBH = SysClass.SysGlobal.GetCreateGUID();
char[] de = { '\\' };
string[] Afilename = fpUpload.FileName.Split(de);
string strFileName = Afilename[Afilename.Length - 1];
string[] AExt = fpUpload.FileName.Split('.');
string strExt = "";
if (AExt.Length > 1)
{
strExt = AExt[AExt.Length - 1];
}
string sUploadFile = sWJBH;
if (strExt.Length > 0)
{
sUploadFile = sUploadFile + '.' + strExt;
}
string sPathFile = Server.MapPath("..") + "\\" + SysClass.SysUploadFile.UploadDirectory + "\\ImportExcel\\" + sUploadFile;
sPathFile = Server.MapPath("../" + SysClass.SysUploadFile.UploadDirectory);
if (Directory.Exists(sPathFile) == false)
{
Directory.CreateDirectory(sPathFile);
}
sPathFile = Server.MapPath("../" + SysClass.SysUploadFile.UploadDirectory + "/ImportExcel");
if (Directory.Exists(sPathFile) == false)
{
Directory.CreateDirectory(sPathFile);
}
sPathFile = Server.MapPath("..") + "\\" + SysClass.SysUploadFile.UploadDirectory + "\\ImportExcel\\" + sUploadFile;
fpUpload.SaveAs(sPathFile);
lblUploadFile.Text = sPathFile;
lblUploadFileName.Text = fpUpload.FileName;
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
//CyxPack.CommonOperation.DataBinder.BindGridViewData(gvLists, ds);
conn.Close();
DataSet dk = SysClass.SysUserSalary.GetUserSalaryFieldsImportLstByDataSet(" and 1<>1");
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
DataRow row = dk.Tables[0].NewRow();
row["SourceName"] = ds.Tables[0].Columns[i].ColumnName;
if (ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName) != null)
{
row["TargetName"] = ddlTargetName.Items.FindByText(ds.Tables[0].Columns[i].ColumnName).Value;
}
dk.Tables[0].Rows.Add(row);
}
gvImportSet.DataSource = dk.Tables[0].DefaultView;
gvImportSet.DataBind();
}
else
{
lblProcess.Text = "请选择上传XLS文件。";
}
}
protected void btnAdd_Click(object sender, EventArgs e)
{
if (SaveCheck())
{
string SalaryRecGuid = SysClass.SysGlobal.GetCreateGUID();
string sUpdateSQL = "", sUpdateFieldsSQL = "";
if (ddlImportRec.SelectedIndex == 0)
{
sUpdateSQL = " Insert into UserUserImportRec_Info (SalaryRecGuid, SalaryYears, Description) Values('" + SalaryRecGuid + "', '" + txtUserSalaryYears.Text + "', '" + ddlDescription.SelectedItem + "');";
CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateSQL + " end;");
}
else
{
SalaryRecGuid = ddlImportRec.SelectedValue.ToString();
sUpdateSQL = " Update UserUserImportRec_Info Set Description='" + ddlDescription.SelectedItem + "' Where SalaryRecGuid='" + SalaryRecGuid + "';";
sUpdateFieldsSQL += " Delete From UserUserImportRecFields_Info Where SalaryRecGuid='" + SalaryRecGuid + "';";
}
lblProcess.Text = "正在导入数据，请稍候。";
DateTime dtbegin = DateTime.Now;
int iUp = 0;
string strConn, sheetname = "Sheet1$";
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + lblUploadFile.Text + ";Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
conn.Open();
DataTable dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (dtExcelSchema.Rows.Count > 0)
{
sheetname = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.OleDb.OleDbDataAdapter oada = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
conn.Close();
DataSet dsImportSet = LoadImportSetByDataSet();
//--导入字段列表
for (int k = 0; k < dsImportSet.Tables[0].Rows.Count; k++)
{
if (dsImportSet.Tables[0].Rows[k]["TargetName"].ToString().Length > 0)
{
sUpdateFieldsSQL += " Insert UserUserImportRecFields_Info (SalaryRecGuid, FieldName, SortID) Values('" + SalaryRecGuid.ToString() + "','" + dsImportSet.Tables[0].Rows[k]["TargetName"].ToString() + "'," + k.ToString() + ");";
}
}
if (CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateFieldsSQL + " end;") > 0)
{
}
//--导入数据
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (ddlImportRec.SelectedIndex > 0)
{
sUpdateSQL = " Update UserSalary_Info Set SalaryDate=SalaryDate";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string sSourceFieldName = dsImportSet.Tables[0].Rows[n]["SourceName"].ToString();
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().Length > 0)
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
if ((sTargetFieldName.IndexOf("Text") >= 0) ||
((sTargetFieldName.IndexOf("OpCode") >= 0)))
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString() + "='" + sValue + "'";
}
else
{
if (sValue.Length > 0)
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString() + "=" + sValue;
}
else
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString() + "=0";
}
}
}
}
sUpdateSQL += " Where SalaryYears='" + txtUserSalaryYears.Text + "' And SalaryRecGuid='" + SalaryRecGuid + "' And OpCode='" + ds.Tables[0].Rows[i][GetOpCodeFieldName()].ToString() + "'";
}
else
{
sUpdateSQL = " Insert Into UserSalary_Info (SalaryYears, SalaryRecGuid, SalaryDate";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
if (dsImportSet.Tables[0].Rows[n]["TargetName"].ToString().Length > 0)
{
sUpdateSQL += "," + dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
}
}
sUpdateSQL += ")"
+ " Values("
+ " '" + txtUserSalaryYears.Text + "'"
+ ",'" + SalaryRecGuid + "'"
+ " ,Getdate()";
for (int n = 0; n < dsImportSet.Tables[0].Rows.Count; n++)
{
string sTargetFieldName = dsImportSet.Tables[0].Rows[n]["TargetName"].ToString();
string sSourceFieldName = dsImportSet.Tables[0].Rows[n]["SourceName"].ToString();
if (sTargetFieldName.Length > 0)
{
string sValue = ds.Tables[0].Rows[i][sSourceFieldName].ToString();
if ((sTargetFieldName.IndexOf("Text") >= 0) ||
((sTargetFieldName.IndexOf("OpCode") >= 0)))
{
sUpdateSQL += ",'" + sValue + "'";
}
else
{
if (sValue.Length > 0)
{
sUpdateSQL += "," + sValue;
}
else
{
sUpdateSQL += ",0";
}
}
}
}
sUpdateSQL += ");";
}
if (CyxPack.OperateSqlServer.DataCommon.QueryData("begin " + sUpdateSQL + " end;") > 0)
{
iUp += 1;
lblProcess.Text = "正在导入第" + iUp.ToString() + "条记录，总" + ds.Tables[0].Rows.Count.ToString() + "条";;
}
}
if (sUpdateSQL.Length > 0)
{
if (iUp > 0)
{
lblProcess.Text = "已经成功导入 " + iUp.ToString() + " 条记录。";
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入成功" + iUp.ToString() + " 条记录……", "SalaryImport_Step1.aspx");
}
else
{
Dialog.OpenDialogInAjax(upForm, "恭喜您，数据导入失败……");
}
}
}
}
public List <DataTable> ReadXLSFile(List <string> listFiles)
{
listTables = new List <DataTable>();
DataSet dataSet1 = new DataSet();
DataTable dt = new DataTable();
String[] sheetName = null;
System.Data.OleDb.OleDbDataAdapter dataAdapter1;
try
{
foreach (var item in listFiles)
{
if (System.IO.Path.GetExtension(item).ToLower().Equals(".csv"))
{
dt = CSVReader.GetDataTable(item);
if (null == dt)
{
//log
return(null);
}
listTables.Add(dt);
}
else
{ //not cvs file -> XLSX file
//reading Excel sheets names
if (InitConnectionString(item) == false)
{
MyLogger.Instance.Write(" InitConnectionString(item) == false");
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.Write(@"D:\log.txt", "InitConnectionString(item) == false");
return(null);
}
if (OpenConnection() == false)
{
MyLogger.Instance.Write("OpenConnection() == false");
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.Write(@"D:\log.txt", "Cannot open connection !");
//write.WriteLine("Cannot open connection !");
return(null);
}
//write.WriteLine(" open connection !");
dt = _connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
sheetName = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
sheetName[i] = row["TABLE_NAME"].ToString();
i++;
}
dataAdapter1 = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName[0] + "]", _connection);
dataSet1 = new DataSet();
dataAdapter1.Fill(dataSet1);
// write.WriteLine(dataSet1.Tables[0].Rows.Count);
listTables.Add(dataSet1.Tables[0]);
MyLogger.Instance.Write(" dataSet1.Tables[0]: rows count: " + dataSet1.Tables[0].Rows.Count);
_connection.Close();
}
}
_connection.Close();
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.Write(@"D:\log.txt", "_connection.Close();");
return(listTables);
}
catch (Exception ex)
{
MyLogger.Instance.Write("error ReadXLSFile: " + ex.Message);
// Easynet.Edge.UI.WebPages.Classes.Convertors.MyLogger.Instance.Write(@"D:\log.txt", "error ReadXLSFile: " + ex.Message);
//WriteToEventLog("Converter: \n" + ex.ToString());
_connection.Close();
_connection.Dispose();
dt.Dispose();
dataSet1.Dispose();
return(null);
}
}
public static List <TableInfo> GetShemaTables(DbConfig config = null)
{
if (config == null)
{
config = DbConfig.Default;
}
List <TableInfo> tables = new List <TableInfo>();
DataTable shemaTables = null;
int tabNameIndex = 0, tabRowCount = 0;
if (config.DbType == "System.Data.OleDb")
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = config.ConnectionString;
conn.Open();
shemaTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
tabNameIndex = shemaTables.Columns.IndexOf("TABLE_NAME");
tabRowCount = shemaTables.Rows.Count;
for (int i = 0; i < tabRowCount; i++)
{
DataRow tabDataRow = shemaTables.Rows[i];
string strTable = tabDataRow.ItemArray.GetValue(tabNameIndex).ToString();
DataTable dtColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, strTable, null });
DataTable dtPrimarys = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Primary_Keys, new string[] { null, null, strTable });
int tColumnsCount = dtColumns.Rows.Count;
int tColumnNameIndex = dtColumns.Columns.IndexOf("COLUMN_NAME");
int tColumnTypeNameIndex = dtColumns.Columns.IndexOf("DATA_TYPE");
TableInfo tInfo = new TableInfo();
tInfo.Name = strTable;
tInfo.ColList = new List <ColumnInfo>();
for (int j = 0; j < tColumnsCount; j++)
{
DataRow drColumn = dtColumns.Rows[j];
ColumnInfo cInfo = new ColumnInfo();
cInfo.Name = drColumn.ItemArray.GetValue(tColumnNameIndex).ToString();
cInfo.TypeName = GetCSharpTypeByAccess(drColumn.ItemArray.GetValue(tColumnTypeNameIndex).ToString());
cInfo.Description = string.Empty;
foreach (DataRow row in dtPrimarys.Rows)
{
if (row["COLUMN_NAME"].ToString() == cInfo.Name)
{
cInfo.IsPrimaryKey = true;
cInfo.IsIdentity = true;
break;
}
}
tInfo.ColList.Add(cInfo);
}
tables.Add(tInfo);
}
conn.Close();
conn.Dispose();
conn = null;
}
else
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = config.ConnectionString;
conn.Open();
shemaTables = conn.GetSchema("Tables");
DataTable dtColumns = conn.GetSchema("Columns");
int colIndex = dtColumns.Columns.IndexOf("COLUMN_NAME");
int typIndex = dtColumns.Columns.IndexOf("DATA_TYPE");
tabNameIndex = shemaTables.Columns.IndexOf("TABLE_NAME");
tabRowCount = shemaTables.Rows.Count;
string sqlColumns = "SELECT OBJECT_NAME(c.object_id) [TableName],c.name [ColumnName],ISNULL(ex.value,'') [DescriptionName] FROM sys.columns c LEFT OUTER JOIN sys.extended_properties ex ON ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description' WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0";
DataTable dtColumnDescriptions = new DataTable();
var adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn);
adapter.Fill(dtColumnDescriptions);
sqlColumns = "select o.name as [TableName],c.name as [ColumnName] from sysindexes i join sysindexkeys k on i.id = k.id and i.indid = k.indid join sysobjects o on i.id = o.id join syscolumns c on i.id=c.id and k.colid = c.colid where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)";
DataTable dtColumnPrimaryKeys = new DataTable();
adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn);
adapter.Fill(dtColumnPrimaryKeys);
sqlColumns = "select b.name as [TableName],a.name as [ColumnName],a.is_identity as [IsIdentity] from sys.columns a inner join sys.objects b on a.object_id=b.object_id ";
DataTable dtColumnIdentitys = new DataTable();
adapter = new System.Data.SqlClient.SqlDataAdapter(sqlColumns, conn);
adapter.Fill(dtColumnIdentitys);
for (int i = 0; i < tabRowCount; i++)
{
DataRow itemDataRow = shemaTables.Rows[i];
string strTable = itemDataRow.ItemArray.GetValue(tabNameIndex).ToString();
DataRow[] drColumns = dtColumns.Select("TABLE_NAME='" + strTable + "'");
int drColumnsLength = drColumns.Length;
DataRow[] drDescriptions = dtColumnDescriptions.Select("TableName='" + strTable + "'");
DataRow[] drPrimaryKeys = dtColumnPrimaryKeys.Select("TableName='" + strTable + "'");
DataRow[] drIdentitys = dtColumnIdentitys.Select("TableName='" + strTable + "'");
TableInfo tInfo = new TableInfo();
tInfo.Name = strTable;
tInfo.ColList = new List <ColumnInfo>();
for (int j = 0; j < drColumnsLength; j++)
{
DataRow tmpDataRow = drColumns[j];
ColumnInfo cInfo = new ColumnInfo();
cInfo.Name = tmpDataRow.ItemArray.GetValue(colIndex).ToString();
cInfo.TypeName = GetCSharpTypeBySqlServer(tmpDataRow.ItemArray.GetValue(typIndex).ToString());
var tmpDescription = drDescriptions.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name);
cInfo.Description = (tmpDescription != null) ? tmpDescription["DescriptionName"].ToString() : string.Empty;
var tmpPrimaryKey = drPrimaryKeys.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name);
cInfo.IsPrimaryKey = (tmpPrimaryKey != null);
var tmpIsIdentity = drIdentitys.FirstOrDefault(m => m["ColumnName"].ToString() == cInfo.Name);
cInfo.IsIdentity = (tmpIsIdentity != null ? (tmpIsIdentity["IsIdentity"].ToString().ToLower() == "true" || tmpIsIdentity["IsIdentity"].ToString() == "1") : false);
tInfo.ColList.Add(cInfo);
}
tables.Add(tInfo);
}
conn.Close();
conn.Dispose();
conn = null;
}
return(tables);
}
public ActionResult Create(FormCollection collection, HttpPostedFileBase ArchAuto, HttpPostedFileBase ArchManual)
{
try
{
if (ArchAuto != null &&
ArchAuto.ContentLength > 0 &&
ArchAuto.FileName.EndsWith("txt") &&
ArchManual != null &&
ArchManual.ContentLength > 0 &&
(ArchManual.FileName.EndsWith("xls") || ArchManual.FileName.EndsWith("xlsx"))
)
{
string pathArchManual = "";
if (!Directory.Exists(Server.MapPath("Excels")))
{
Directory.CreateDirectory(Server.MapPath("Excels"));
}
var readerArchAuto = new BinaryReader(ArchAuto.InputStream);
string resultArchAuto = System.Text.Encoding.UTF8.GetString(readerArchAuto.ReadBytes(ArchAuto.ContentLength));
string[] lineasArchAuto = Regex.Split(resultArchAuto, "\r\n");
Relevamientos objRelevamiento = new Relevamientos();
objRelevamiento.Observaciones = collection["Observaciones"];
objRelevamiento.FechaCarga = DateTime.Now;
objRelevamiento.FechaInicio = DateTime.Parse(lineasArchAuto[0].Split(';')[0].ToString());
objRelevamiento.FechaFinal = DateTime.Parse(lineasArchAuto[lineasArchAuto.Length - 1].Split(';')[0].ToString());
objRelevamiento.IdEstado = 1;
objRelevamiento.IdTrampa = int.Parse(lineasArchAuto[0].Split(';')[2].ToString());
db.Relevamientos.Add(objRelevamiento);
//db.SaveChanges();
pathArchManual = Server.MapPath("Excels") + @"\" + ArchManual.FileName;
if (System.IO.File.Exists(pathArchManual))
{
System.IO.File.Delete(pathArchManual);
}
ArchManual.SaveAs(pathArchManual);
string cnnStr = "";
if (pathArchManual.EndsWith(".xlsx"))
{
//Excel 2007
cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
else
{
//Excel 97-2003
//http://www.connectionstrings.com/excel (leer sobre la clave de registro TypeGuessRows)
cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
System.Data.OleDb.OleDbConnection oCnn = new System.Data.OleDb.OleDbConnection(cnnStr);
System.Data.OleDb.OleDbDataAdapter oDa = null;
DataTable dtArchManual = new DataTable();
try
{
oCnn.Open();
//Obtenemos los nombres de las hojas del Excel.
DataTable dtHojas = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dtHojas.Rows.Count > 0)
{
string firstSheet = dtHojas.Rows[0]["TABLE_NAME"].ToString().Trim();
string selectCmd = "select * from [" + firstSheet + "]";
oDa = new System.Data.OleDb.OleDbDataAdapter(selectCmd, oCnn);
oDa.Fill(dtArchManual);
}
oCnn.Close();
dtArchManual.Columns.Add("Fecha");
foreach (DataRow drFila in dtArchManual.Rows)
{
int iAño = 0;
int iMes = 0;
int iDia = 0;
int.TryParse(drFila[0].ToString(), out iAño);
int.TryParse(drFila[1].ToString(), out iMes);
int.TryParse(drFila[2].ToString(), out iDia);
if (iAño > 0 && iMes > 0 && iDia > 0)
{
DateTime tFecha = new DateTime(iAño, iMes, iDia);
drFila["Fecha"] = tFecha.Date.ToShortDateString();
}
if (drFila["Fecha"].ToString() == objRelevamiento.FechaFinal.ToShortDateString())
{
foreach (DataColumn dcColumna in dtArchManual.Columns)
{
if (dcColumna.Ordinal > 2)
{
//CORREGIR SELECCION DE INSECTO
Insectos objInsecto = (from obj in db.Insectos where obj.NombreCientifico == dcColumna.ColumnName select obj).FirstOrDefault();
if (objInsecto != null)
{
int Cantidad = 0;
int.TryParse(drFila[dcColumna.Ordinal].ToString(), out Cantidad);
if (Cantidad > 0)
{
LecturasManuales objLecturasManuales = new LecturasManuales();
objLecturasManuales.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturasManuales.IdInsecto = objInsecto.IdInsecto;
objLecturasManuales.Cantidad = Cantidad;
objLecturasManuales.IdEstado = 1;
db.LecturasManuales.Add(objLecturasManuales);
}
}
}
}
//db.SaveChanges();
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oCnn.State == ConnectionState.Open)
{
oCnn.Close();
}
}
if (oDa != null)
{
oDa.Dispose();
}
if (oCnn != null)
{
oCnn.Dispose();
}
//var objRelevamiento2 = (from obj in db.Relevamientos select obj).OrderByDescending(i => i.IdRelevamiento).First();
foreach (string Linea in lineasArchAuto)
{
string[] arrDatos = Linea.Split(';');
if (arrDatos.Length > 1 && arrDatos[1].ToString() == "LECTURA")
{
Lecturas objLecturas = new Lecturas();
objLecturas.IdEstado = 1;
objLecturas.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturas.Frecuencia = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objLecturas.Aleteos = int.Parse(arrDatos[3]);
objLecturas.FechaLectura = DateTime.Parse(arrDatos[0]);
db.Lecturas.Add(objLecturas);
}
else if (arrDatos.Length > 1 && arrDatos[1].ToString() == "ESTADO")
{
Monitoreos objMonitoreos = new Monitoreos();
objMonitoreos.IdEstado = 1;
objMonitoreos.IdRelevamiento = objRelevamiento.IdRelevamiento;
objMonitoreos.Humedad = double.Parse(arrDatos[3], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Temperatura = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Bateria = double.Parse(arrDatos[4], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.FechaMonitoreo = DateTime.Parse(arrDatos[0]);
db.Monitoreos.Add(objMonitoreos);
}
}
db.SaveChanges();
return(Redirect("~/Alarmas/GenerarAlarmas"));
}
else
{
return(View());
}
}
catch (Exception ex)
{
throw ex;
}
}
public System.Collections.Generic.List<string> GetTables(string file)
{
string connString = "";
if (Global.filepassword != "")
{
if(Path.GetExtension(file).ToString().ToUpper() != ".ACCDB")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Jet OLEDB:Database Password="******"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Jet OLEDB:Database Password="******".ACCDB")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file;
else
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Persist Security Info=False;";
}
System.Data.DataTable tables = null;
bool val = true;
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connString))
{
try
{
connection.Open();
tables = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
valPass = true;
}
catch (Exception ee)
{
if (ee.Message == "Not a valid password.")
{
//fnGetTab();
val = false;
//MessageBox.Show("Enter Password");
frmPasswordDialog frm = new frmPasswordDialog();
//this.Close();
frm.ShowDialog();
if (Global.filepassword != "")
{
valPass = true;
}
//this.ShowDialog();
}
}
}
System.Collections.Generic.List<string> Tables = new System.Collections.Generic.List<string>();
if (val == true)
{
for (int i = 0; i < tables.Rows.Count; i++)
{
Tables.Add(tables.Rows[i][2].ToString());
}
}
else
{
//txtMdbPassword.Enabled = true;
//txtMdbPassword.Focus();
//if (Global.filepassword != "")
//{
// val = true;
// using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file+";Jet OLEDB:Database Password="******"TABLE" });
// }
// catch (Exception ee)
// {
// if (ee.Message == "Not a valid password.")
// {
// //fnGetTab();
// val = false;
// ////MessageBox.Show("Enter Password");
// //frmPasswordDialog frm = new frmPasswordDialog();
// ////this.Close();
// //frm.ShowDialog();
// //this.ShowDialog();
// }
// }
// if (val == true)
// {
// for (int i = 0; i < tables.Rows.Count; i++)
// {
// Tables.Add(tables.Rows[i][2].ToString());
// }
// }
// }
//}
}
return Tables;
}
public ActionResult Create(FormCollection collection, HttpPostedFileBase ArchAuto, HttpPostedFileBase ArchManual)
{
try
{
if (ArchAuto != null &&
ArchAuto.ContentLength > 0 &&
ArchAuto.FileName.EndsWith("txt") &&
ArchManual != null &&
ArchManual.ContentLength > 0 &&
(ArchManual.FileName.EndsWith("xls") || ArchManual.FileName.EndsWith("xlsx"))
)
{
string pathArchManual = "";
if (!Directory.Exists(Server.MapPath("Excels"))) Directory.CreateDirectory(Server.MapPath("Excels"));
var readerArchAuto = new BinaryReader(ArchAuto.InputStream);
string resultArchAuto = System.Text.Encoding.UTF8.GetString(readerArchAuto.ReadBytes(ArchAuto.ContentLength));
string[] lineasArchAuto = Regex.Split(resultArchAuto, "\r\n");
Relevamientos objRelevamiento = new Relevamientos();
objRelevamiento.Observaciones = collection["Observaciones"];
objRelevamiento.FechaCarga = DateTime.Now;
objRelevamiento.FechaInicio = DateTime.Parse(lineasArchAuto[0].Split(';')[0].ToString());
objRelevamiento.FechaFinal = DateTime.Parse(lineasArchAuto[lineasArchAuto.Length - 1].Split(';')[0].ToString());
objRelevamiento.IdEstado = 1;
objRelevamiento.IdTrampa = int.Parse(lineasArchAuto[0].Split(';')[2].ToString());
db.Relevamientos.Add(objRelevamiento);
//db.SaveChanges();
pathArchManual = Server.MapPath("Excels") + @"\" + ArchManual.FileName;
if (System.IO.File.Exists(pathArchManual)) System.IO.File.Delete(pathArchManual);
ArchManual.SaveAs(pathArchManual);
string cnnStr = "";
if (pathArchManual.EndsWith(".xlsx"))
{
//Excel 2007
cnnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
else
{
//Excel 97-2003
//http://www.connectionstrings.com/excel (leer sobre la clave de registro TypeGuessRows)
cnnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
cnnStr += ";Data Source=" + pathArchManual + ";";
}
System.Data.OleDb.OleDbConnection oCnn = new System.Data.OleDb.OleDbConnection(cnnStr);
System.Data.OleDb.OleDbDataAdapter oDa = null;
DataTable dtArchManual = new DataTable();
try
{
oCnn.Open();
//Obtenemos los nombres de las hojas del Excel.
DataTable dtHojas = oCnn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
if (dtHojas.Rows.Count > 0)
{
string firstSheet = dtHojas.Rows[0]["TABLE_NAME"].ToString().Trim();
string selectCmd = "select * from [" + firstSheet + "]";
oDa = new System.Data.OleDb.OleDbDataAdapter(selectCmd, oCnn);
oDa.Fill(dtArchManual);
}
oCnn.Close();
dtArchManual.Columns.Add("Fecha");
foreach (DataRow drFila in dtArchManual.Rows)
{
int iAño = 0;
int iMes = 0;
int iDia = 0;
int.TryParse(drFila[0].ToString(), out iAño);
int.TryParse(drFila[1].ToString(), out iMes);
int.TryParse(drFila[2].ToString(), out iDia);
if (iAño > 0 && iMes > 0 && iDia > 0)
{
DateTime tFecha = new DateTime(iAño, iMes, iDia);
drFila["Fecha"] = tFecha.Date.ToShortDateString();
}
if (drFila["Fecha"].ToString() == objRelevamiento.FechaFinal.ToShortDateString())
{
foreach (DataColumn dcColumna in dtArchManual.Columns)
{
if (dcColumna.Ordinal > 2)
{
//CORREGIR SELECCION DE INSECTO
Insectos objInsecto = (from obj in db.Insectos where obj.NombreCientifico == dcColumna.ColumnName select obj).FirstOrDefault();
if (objInsecto != null)
{
int Cantidad = 0;
int.TryParse(drFila[dcColumna.Ordinal].ToString(), out Cantidad);
if (Cantidad > 0)
{
LecturasManuales objLecturasManuales = new LecturasManuales();
objLecturasManuales.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturasManuales.IdInsecto = objInsecto.IdInsecto;
objLecturasManuales.Cantidad = Cantidad;
objLecturasManuales.IdEstado = 1;
db.LecturasManuales.Add(objLecturasManuales);
}
}
}
}
//db.SaveChanges();
break;
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (oCnn.State == ConnectionState.Open) oCnn.Close();
}
if (oDa != null) { oDa.Dispose(); }
if (oCnn != null) { oCnn.Dispose(); }
//var objRelevamiento2 = (from obj in db.Relevamientos select obj).OrderByDescending(i => i.IdRelevamiento).First();
foreach (string Linea in lineasArchAuto)
{
string[] arrDatos = Linea.Split(';');
if (arrDatos.Length > 1 && arrDatos[1].ToString() == "LECTURA")
{
Lecturas objLecturas = new Lecturas();
objLecturas.IdEstado = 1;
objLecturas.IdRelevamiento = objRelevamiento.IdRelevamiento;
objLecturas.Frecuencia = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objLecturas.Aleteos = int.Parse(arrDatos[3]);
objLecturas.FechaLectura = DateTime.Parse(arrDatos[0]);
db.Lecturas.Add(objLecturas);
}
else if (arrDatos.Length > 1 && arrDatos[1].ToString() == "ESTADO")
{
Monitoreos objMonitoreos = new Monitoreos();
objMonitoreos.IdEstado = 1;
objMonitoreos.IdRelevamiento = objRelevamiento.IdRelevamiento;
objMonitoreos.Humedad = double.Parse(arrDatos[3], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Temperatura = double.Parse(arrDatos[2], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.Bateria = double.Parse(arrDatos[4], System.Globalization.CultureInfo.InvariantCulture);
objMonitoreos.FechaMonitoreo = DateTime.Parse(arrDatos[0]);
db.Monitoreos.Add(objMonitoreos);
}
}
db.SaveChanges();
return Redirect("~/Alarmas/GenerarAlarmas");
}
else
{
return View();
}
}
catch (Exception ex)
{
throw ex;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("Выбери год", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox2.Text.ToString() == "")
{
MessageBox.Show("Выбери месяц", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text == "")
{
MessageBox.Show("Выбери файл", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string strConn;
//Check for Excel version
if (textBox1.Text.Substring(textBox1.Text.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=0\"";
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataTable shemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)shemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
if (System.IO.File.Exists("your_base.mdb"))
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
ADOX.Catalog cat = new ADOX.Catalog();
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
cat.Create(String.Format(connstr, "your_base.mdb"));
cat = null;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
Querry("CREATE TABLE SURVEY(Anim varchar(255), weight int, height int, s_year int, s_month int);", "your_base.mdb");
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
//string comm = "Insert into SURVEY (Anim, weight, height) VALUES (hare, 10, 20)";
//System.Data.OleDb.OleDbDataAdapter dbAdp = new System.Data.OleDb.OleDbDataAdapter(comm, conStr);
//dbAdp.Update(ds.Tables[0]);
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
con.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("Выбери год", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (comboBox2.Text.ToString() == "")
{
MessageBox.Show("Выбери месяц", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (textBox1.Text == "")
{
MessageBox.Show("Выбери файл", "Внимание!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
string strConn;
//Check for Excel version
if (textBox1.Text.Substring(textBox1.Text.LastIndexOf('.')).ToLower() == ".xlsx")
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 12.0;HDR=YES; IMEX=0\"";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=YES; IMEX=0\"";
}
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(strConn);
con.Open();
DataSet ds = new DataSet();
DataTable shemaTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
string sheet1 = (string)shemaTable.Rows[0].ItemArray[2];
string select = String.Format("SELECT * FROM [{0}]", sheet1);
System.Data.OleDb.OleDbDataAdapter ad = new System.Data.OleDb.OleDbDataAdapter(select, con);
ad.Fill(ds);
if (System.IO.File.Exists("your_base.mdb"))
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
int year = Convert.ToInt32(comboBox1.Text.ToString());
int month = Convert.ToInt32(comboBox2.Text.ToString());
ADOX.Catalog cat = new ADOX.Catalog();
string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
cat.Create(String.Format(connstr, "your_base.mdb"));
cat = null;
string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your_base.mdb;Jet OLEDB:Engine Type=5";
Querry("CREATE TABLE SURVEY(Anim varchar(255), weight int, height int, s_year int, s_month int);", "your_base.mdb");
System.Data.OleDb.OleDbConnection connectDb = new System.Data.OleDb.OleDbConnection(conStr);
connectDb.Open();
System.Data.OleDb.OleDbCommand myCMD = new System.Data.OleDb.OleDbCommand();
myCMD.Connection = connectDb;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myCMD.CommandText = "Insert into SURVEY (Anim, weight, height, s_year, s_month) VALUES (\"" +
ds.Tables[0].Rows[i][0] + "\", " + ds.Tables[0].Rows[i][1] + ", " + ds.Tables[0].Rows[i][2] + ", " + year + ", " + month + ")";
myCMD.ExecuteNonQuery();
}
//string comm = "Insert into SURVEY (Anim, weight, height) VALUES (hare, 10, 20)";
//System.Data.OleDb.OleDbDataAdapter dbAdp = new System.Data.OleDb.OleDbDataAdapter(comm, conStr);
//dbAdp.Update(ds.Tables[0]);
MessageBox.Show("Данные загружены в БД", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
con.Close();
}
}
private void btnIniciar_Click(object sender, EventArgs e)
{
try
{
this.txtPath.Clear();
OpenFileDialog openfile = new OpenFileDialog();
openfile.Title = string.Format("{0}{1}", "Migrador de ", oTipoMigrador);
openfile.Filter = "Archivos de Excel|*.xlsx;*.xls";
if (openfile.ShowDialog() == DialogResult.OK)
{
this.txtPath.Text = openfile.FileName;
}
if (this.txtPath.Text.Length > 0)
{
if (System.IO.File.Exists(this.txtPath.Text))
{
var cadena = string.Empty;
var ext = System.IO.Path.GetExtension(this.txtPath.Text);
switch (ext)
{
case ".xls": //Excel 97-03
cadena = string.Format("{0}{1}{2}", "Provider = Microsoft.jet.OLEDB.4.0; Data source=", this.txtPath.Text, ";Extended Properties=\"Excel 8.0;HDR=yes;\";");
break;
case ".xlsx": //Excel 07-
cadena = string.Format("{0}{1}{2}", "Provider = Microsoft.ACE.OLEDB.12.0; Data source=", this.txtPath.Text, ";Extended Properties=\"Excel 8.0;HDR=yes;\";");
break;
}
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(cadena);
conn.Open();
DataTable dtExcelSchema;
dtExcelSchema = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
var SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
conn.Close();
System.Data.OleDb.OleDbDataAdapter excelAdapter = new System.Data.OleDb.OleDbDataAdapter("Select * from [" + SheetName + "]", conn);
DataTable dtDatos = new DataTable();
excelAdapter.Fill(dtDatos);
this.dgvDatos.DataSource = dtDatos;
}
}
}
catch (Exception ex)
{
Util.MensajeError(ex.Message, GlobalClass.NombreApp);
}
}