private void Bt_ImportOrigin_OnClick(object sender, RoutedEventArgs e)
{
var openFileDialog = new Microsoft.Win32.OpenFileDialog()
{
Filter = "Excel文件(*.xls,*.xlsx) |*.xls;*.xlsx"
};
var result = openFileDialog.ShowDialog();
if (result == true)
{
Hint.Text = "导入中，请稍候...";
ThreadPool.QueueUserWorkItem((w) =>
{
string strError = "";
var Importresult = AsposeExcelTools.ExcelFileToDataTable(openFileDialog.FileName, out currentInPutDataTable, out strError);
if (Importresult == true)
{
Update(() => { Hint.Text = "导入成功，请执行第二步";
Bt_StartCheck.IsEnabled = true; });
}
else
{
Update(() =>
{
Hint.Text = "导入失败。请重试。" + strError;
});
}
});
}
}
private static DataTable GetDtTable()
{
string path = HttpContext.Current.Request.MapPath("~/App_Data/检测返修报表.xlsx");
//调用ZK的ExcelHelper
//DataTable dtTable = ExcelHelper.ImportExceltoDt(path);
DataTable dtTable = new DataTable();
string str;
AsposeExcelTools.ExcelFileToDataTable(path, out dtTable, out str);
return(dtTable);
}
/// <summary>
/// 从附件列表中获取第一个Excel文件，并转换Excel数据为对应的DataTable返回
/// </summary>
/// <param name="guid">附件的Guid</param>
/// <returns></returns>
protected DataTable ConvertExcelFileToTable(string guid)
{
DataTable dt = null;
if (!string.IsNullOrEmpty(guid))
{
//获取上传附件的路径
string serverRealPath = BLLFactory <FileUpload> .Instance.GetFirstFilePath(guid);
if (!string.IsNullOrEmpty(serverRealPath))
{
//转换Excel文件到DatTable里面
string error = "";
dt = new DataTable();
AsposeExcelTools.ExcelFileToDataTable(serverRealPath, out dt, out error);
}
}
return(dt);
}
/// <summary>
/// 从Excel文件导入(角色)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private bool DoImport(string filePath)
{
bool returnValue = false;
// 鼠标忙碌状态
this.Cursor = Cursors.WaitCursor;
try
{
//将Excel表转换为DataTable
string error = "";
DataTable dataTable = new DataTable();
AsposeExcelTools.ExcelFileToDataTable(filePath, out dataTable, out error);
//检查Excell转为DataTable是否成功
if (error != "")
{
MessageBox.Show(error, "错误提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
//是否有记录
if (dataTable.Rows.Count > 0)
{
//将第一行数据给数据列置名称，以便于识别和调用数据。
int columnsCount = 0;
for (columnsCount = 0; columnsCount < dataTable.Columns.Count; columnsCount++)
{
dataTable.Columns[columnsCount].ColumnName = dataTable.Rows[0][columnsCount].ToString().Trim();
}
//循环处理每行数据
int rowsCount = 0;
int errorCount = 0;
BaseRoleEntity tempentity;
string statusCode = string.Empty;
string statusMessage = string.Empty;
DataTable dt = DotNetService.Instance.RoleService.GetDataTable(UserInfo);
for (rowsCount = 1; rowsCount < dataTable.Rows.Count; rowsCount++)
{
//角色名称不允许导入重复项
bool exists = BaseBusinessLogic.Exists(dt, BaseRoleEntity.FieldRealName, dataTable.Rows[rowsCount][BaseRoleEntity.FieldRealName].ToString());
if (!exists)
{
//清空实体
tempentity = new BaseRoleEntity();
//给实体赋值
tempentity.RealName = dataTable.Rows[rowsCount][BaseRoleEntity.FieldRealName].ToString();
tempentity.Code = dataTable.Rows[rowsCount][BaseRoleEntity.FieldCode].ToString();
tempentity.Description = dataTable.Rows[rowsCount][BaseRoleEntity.FieldDescription].ToString();
tempentity.Enabled = int.Parse(dataTable.Rows[rowsCount][BaseRoleEntity.FieldEnabled].ToString());
tempentity.CategoryCode = dataTable.Rows[rowsCount][BaseRoleEntity.FieldCategoryCode].ToString();
tempentity.AllowDelete = 1;
tempentity.AllowEdit = 1;
tempentity.DeletionStateCode = 0;
tempentity.IsVisible = 1;
DotNetService.Instance.RoleService.Add(UserInfo, tempentity, out statusCode, out statusMessage);
}
else
{
errorCount++;
}
}
this.Changed = true;
returnValue = true;
MessageBox.Show("共有" + (dataTable.Rows.Count - 1) + "条记录，"
+ (dataTable.Rows.Count - 1 - errorCount).ToString()
+ "条记录被成功导入！", "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
this.ProcessException(ex);
}
finally
{
// 设置鼠标默认状态
this.Cursor = Cursors.Default;
}
return(returnValue);
}
//从Excel导入数据
private void ImportExcel()
{
OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Filter = "Execl files (*.xls)|*.xls|(*.xlsx)|*.xlsx";
openDialog.FilterIndex = 0;
openDialog.RestoreDirectory = true;
openDialog.Title = "打开Excel文件";
string fileName = string.Empty;//要上传的文件名
if (openDialog.ShowDialog() == DialogResult.OK)
{
WinFileTransporter wFile = new WinFileTransporter();
fileName = openDialog.FileName;
string uriFiles = string.Empty;//保存到服务器路径
string folderName = Application.StartupPath + "\\importFiles";
if (!Directory.Exists(folderName))//如果路径不存在则创建
{
Directory.CreateDirectory(folderName);
}
try
{
if (wFile.UpLoadFile(fileName, folderName, out uriFiles, true))
{
DataTable dt = new DataTable();
string errorStr = string.Empty;
AsposeExcelTools.ExcelFileToDataTable(uriFiles, out dt, out errorStr);//读取表格数据，转换为datatable
List <DATestmap> errorList = new List <DATestmap>();
List <DATestmap> testmapList = new List <DATestmap>();
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DATestmap testmap = new DATestmap();
testmap.Customertestcode = dt.Rows[i]["医院项目代码(项目导入唯一标识)"].ToString().Trim();
testmap.Customertestname = dt.Rows[i]["医院项目名称(作为对应参考,不做导入标识)"].ToString().Trim();
testmap.Datestcode = dt.Rows[i]["达安项目代码(项目导入唯一标识)"].ToString().Trim();
testmap.Datestname = dt.Rows[i]["达安项目名称(作为对应参考,不做导入标识)"].ToString().Trim();
if (testmap.Customertestcode == string.Empty)
{
errorList.Add(testmap);
errorStr += "导入错误:医院项目代码为空,";
continue;
}
if (testmap.Datestcode == string.Empty)
{
errorList.Add(testmap);
errorStr += "导入错误:达安项目代码为空,";
continue;
}
if (testmap.Customertestname == string.Empty)
{
errorList.Add(testmap);
errorStr += "导入错误:医院项目名称为空,";
continue;
}
if (testmap.Datestname == string.Empty)
{
errorList.Add(testmap);
errorStr += "导入错误:达安项目名称为空,";
continue;
}
if (!IsExistHosp(testmap.Customertestcode, testmap.Customertestname))
{
errorList.Add(testmap);
errorStr += "导入错误:医院项目信息不存在,";
continue;
}
if (!IsExistDaan(testmap.Datestcode, testmap.Datestname))
{
errorList.Add(testmap);
errorStr += "导入错误:达安项目信息不存在,";
continue;
}
//如果在testmap表已经存在医院项目代码的记录，记录对象及错误信息
//if (IsExist(testmap.Customertestcode))
//{
// errorList.Add(testmap);
// errorStr += "导入失败:该医院项目已存在对应的匹配信息,";
// continue;
//}
errorList.Add(testmap);
errorStr += "导入成功,";
testmapList.Add(testmap);
}
//if (testmapBll.ImportDaTestmap(testmapList))
if (testmapBll.SaveDATestmapList(testmapList))
{
//弹出错误信息
//if (errorList != null)
//{
FrmItemHoapitalAndDaanImportError errorFrm = new FrmItemHoapitalAndDaanImportError();
errorFrm.StrFailed = errorStr;
errorFrm.testmapList = errorList;
errorFrm.StartPosition = FormStartPosition.CenterScreen;
errorFrm.ShowDialog();
BindData();
//}
//else
//{
// ShowMessageHelper.ShowBoxMsg("导入成功!");
//}
}
else
{
ShowMessageHelper.ShowBoxMsg("导入失败!");
}
}
else
{
ShowMessageHelper.ShowBoxMsg("没有数据导入!");
}
}
}
catch (Exception e)
{
ShowMessageHelper.ShowBoxMsg("导入的过程中发生了异常:" + e.Message);
}
finally
{
//删除文件
if (File.Exists(uriFiles))
{
File.Delete(uriFiles);
}
}
}
}
public ActionResult Import(string guid)
{
CommonResult result = new CommonResult();
result.Success = false;
try
{
var attach = BLLFactory <FileUpload> .Instance.GetByAttachGUID(guid).FirstOrDefault();
if (attach != null)
{
var filename = Server.MapPath("~\\" + attach.BasePath + "\\" + attach.SavePath);
var dt = new System.Data.DataTable();
string err = null;
if (AsposeExcelTools.ExcelFileToDataTable(filename, out dt, out err))
{
//清空中间表
BLLFactory <Core.BLL.MidConcentrator> .Instance.DeleteByCondition("1=1");
BLLFactory <Core.BLL.MidCustomerMeter> .Instance.DeleteByCondition("1=1");
//采集器信息
var entity = new Core.Entity.MidConcentrator();
var row = dt.Rows[2];
var nvcName = row[0].ToString();
var nvcAddr = row[1].ToString();
var vcAddr = row[2].ToString();
var intProtocol = row[3].ToString().ToInt();
var intCount = row[4].ToString().ToInt();
var intCommMode = row[5].ToString().ToInt();
var intCOM = row[6].ToString().ToInt();
var vcParam = row[7].ToString();
var vcSimNo = row[8].ToString();
var intStatus = row[9].ToString().ToInt();
var clientIP = "127.0.0.1";
entity.VcClientIP = clientIP;
entity.IntCOM = intCOM;
entity.IntCommMode = intCommMode;
entity.IntCount = intCount;
entity.IntProtocol = intProtocol;
entity.IntStatus = intStatus;
entity.NvcAddr = nvcAddr;
entity.NvcName = nvcName;
entity.VcAddr = vcAddr;
entity.VcParam = vcParam;
entity.VcSimNo = vcSimNo;
//DbTransaction dbTransaction = BLLFactory<Core.BLL.MidConcentrator>.Instance.CreateTransaction();
BLLFactory <Core.BLL.MidConcentrator> .Instance.Insert(entity);
//dbTransaction.Commit();
//客户及表信息
for (int i = 5; i < dt.Rows.Count; i++)
{
var item = dt.Rows[i];
var IntCustCode = item[0].ToString().ToInt();
var NvcName = item[1].ToString();
var NvcAddr = item[2].ToString();
var VcTelNo = item[3].ToString();
var VcMobile = item[4].ToString();
var VcIDNo = item[5].ToString();
var IntPriceNo = item[6].ToString().ToInt();
var VcCustType = item[7].ToString();
var IntStatusUser = item[8].ToString().ToInt();
var DteOpen = Convert.ToDateTime(item[9].ToString());
var NvcInvName = item[10].ToString();
var NvcInvAddr = item[11].ToString();
var NvcVillage = item[12].ToString();
var VcBuilding = item[13].ToString();
var IntUnitNum = item[14].ToString().ToInt();
var IntRoomNum = item[15].ToString().ToInt();
var IntNumber = item[16].ToString().ToInt();
var VcAddr = item[17].ToString();
var NvcAddrIns = item[18].ToString();
var VcAssetNo = item[19].ToString();
var VcBarCode = item[20].ToString();
var IntOrig = item[21].ToString().ToInt();
var IntChannal = item[22].ToString().ToInt();
var IntProtocol = item[23].ToString().ToInt();
var DtCreate = Convert.ToDateTime(item[24].ToString());
var IntCycle = item[25].ToString().ToInt();
var IntState = item[26].ToString().ToInt();
var mMeter = new Core.Entity.MidCustomerMeter();
mMeter.DtCreate = DateTime.Now;
mMeter.DteOpen = DateTime.Now;
mMeter.IntChannal = IntChannal;
mMeter.IntCustCode = IntCustCode;
mMeter.IntCycle = IntCycle;
mMeter.IntNumber = IntNumber;
mMeter.IntOrig = IntOrig;
mMeter.IntPriceNo = IntPriceNo;
mMeter.IntProtocol = IntProtocol;
mMeter.IntRoomNum = IntRoomNum;
mMeter.IntState = IntState;
mMeter.IntStatus = IntStatusUser;
mMeter.IntUnitNum = IntUnitNum;
//mMeter.IntUserID=
mMeter.NvcAddr = NvcAddr;
mMeter.NvcAddrIns = NvcAddrIns;
mMeter.NvcCustType = VcCustType;
mMeter.NvcInvAddr = NvcInvAddr;
mMeter.NvcInvName = NvcInvName;
mMeter.NvcName = NvcName;
mMeter.NvcVillage = NvcVillage;
mMeter.VcAddr = VcAddr;
mMeter.VcAssetNo = VcAssetNo;
mMeter.VcBarCode = VcBarCode;
mMeter.VcBuilding = VcBuilding;
mMeter.VcClientIP = clientIP;
//mMeter.VcContractNo=
mMeter.VcIDNo = VcIDNo;
mMeter.VcMobile = VcMobile;
mMeter.VcTelNo = VcTelNo;
BLLFactory <Core.BLL.MidCustomerMeter> .Instance.Insert(mMeter);
//BLLFactory<Core.BLL.MidCustomerMeter>.Instance.Insert(mMeter, dbTransaction);
}
//dbTransaction.Commit();
//@sClientIP VARCHAR(64), --< !--客户端IP-- >
//@iConcFlag INTEGER, --< !--采集器标志0:如果已存在相同采集器，则提示错误，终止导入档案信息 1:如果已存在同地址采集器，将客户与表信息导入，表挂接在同地址采集器下-- >
//@iMeterFlag INTEGER, --< !--一户多表标志0:一户一表，MidCustomerMeter.IntCustCode唯一 1:一户多表，MidCustomerMeter.IntCustCode相同的记录认为是同一客户-- >
List <SqlParameter> param = new List <SqlParameter>();
param.Add(new SqlParameter("@sClientIP", SqlDbType.VarChar, 64)
{
Value = clientIP
});
param.Add(new SqlParameter("@iConcFlag", SqlDbType.VarChar, 64)
{
Value = 1
});
param.Add(new SqlParameter("@iMeterFlag", SqlDbType.VarChar, 64)
{
Value = 1
});
param.Add(new SqlParameter("@sReturn", SqlDbType.VarChar, 256)
{
Direction = ParameterDirection.Output
});
//param[0].Value = clientIP;
//param[1].Value = 1;
//param[2].Value = 1;
//param[3].Direction = ParameterDirection.Output;
BLLFactory <Core.BLL.ArcConcentratorInfo> .Instance.ExecStoreProc("up_ImportArchive", param);
if (param[3].Value.ToString() == "0")
{
result.Success = true;
}
else
{
result.ErrorMessage = "执行up_ImportArchive存储过程出错!错误如下:" + param[3].Value.ToString();
}
}
else
{
result.ErrorMessage = err;
}
}
}
catch (Exception ex)
{
result.ErrorMessage = ex.Message;
}
return(ToJsonContent(result));
}