发布：2023/3/22 22:25:46作者：管理员 来源：本站 浏览次数：427
public ActionResult testImport(string path)
{
string[] columnsStr = new string[] {"dataType", "customerCode", "factory", "projectNo", "countrieType", "productCategory", "modelName", "version", "scene", "remark" };
Stopwatch watch = new Stopwatch();
watch.Start(); //开始监视代码运行时间
FileStream fileStream = new FileStream(path, FileMode.Open);
IWorkbook workbook = null;
DataSet dataSet = new DataSet();
bool boolRow = false;//标记数据行
if (fileStream.Name.EndsWith(".xlsx"))
workbook = new XSSFWorkbook(fileStream);
else
workbook = new HSSFWorkbook(fileStream);
var DayValueStartColumn = DemandPlanLineDataService.DayValueStartColumn-1;
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
DataTable dataTable = new DataTable();//构建一个DataTable 对应一个Sheet 新增
DataTable updateTable = new DataTable();//构建一个DataTable 对应一个Sheet 更新
bool isExstisTable = false;
bool isClonTable = true;//是否存在 导入数据中既有更新又有新增，将原始的新增Table行拷贝到更新Table行中
Dictionary<int, Dictionary<string, object>> dicfileValues = new Dictionary<int, Dictionary<string, object>>();
ISheet sheet = workbook.GetSheetAt(i);
string dateBaseTable = "DemandPlanLine_" + planMain.Month.Replace("-", "_");
dataTable.TableName = dateBaseTable + "@Insert";//sheet.SheetName;//页签名为DataTable名
updateTable.TableName = dateBaseTable + "@Update";//sheet.SheetName;//页签名为DataTable名
var checkTable = SugarClient.Default.Context.Ado.SqlQuerySingle<string>($"select id from sysObjects where Id=OBJECT_ID(N'{dateBaseTable}') and xtype='U'");//返回动态类型
dynamic dateBaseModel = null;
List<dynamic> checkBaseDate = null;
if (checkTable!=null)
{
checkBaseDate = SugarClient.Default.Context.Ado.SqlQuery<dynamic>($"select Id,LineKey from {dateBaseTable} where _IsDeleted=0");//返回动态类型
isExstisTable = true;
}
//取数据
#region 取数据
int firtRowNum = sheet.FirstRowNum;
int lastRowNum = sheet.LastRowNum;
List<string> updateColumnName = new List<string>();
//处理行
for (int j = firtRowNum; j < lastRowNum; j++)
{
IRow row = sheet.GetRow(j);
DataRow dataRow = isExstisTable ? updateTable.NewRow() : dataTable.NewRow();
if (row == null)
row = sheet.CreateRow(j);
int firstCellNum = row.FirstCellNum;
int lastCellNum = row.LastCellNum;
Dictionary<string, object> keyValues = new Dictionary<string, object>();
//处理单元格
for (int k = firstCellNum; k < lastCellNum; k++)
{
if (j == 21)
{
}
object cellValue = null;
ICell cell = row.GetCell(k);
if (cell == null)
cell = row.CreateCell(k);
switch (cell.CellType)
{
case CellType.Unknown:
cellValue = cell.StringCellValue;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
cellValue = cell.DateCellValue;
else
cellValue = cell.NumericCellValue;
break;
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Formula:
cellValue = cell.StringCellValue;
break;
case CellType.Blank:
cellValue = cell.StringCellValue;
break;
case CellType.Boolean:
cellValue = cell.StringCellValue;
break;
case CellType.Error:
cellValue = cell.StringCellValue;
break;
default:
cellValue = cell.StringCellValue;
break;
}
Type type = cellValue.GetType();
if (j == firtRowNum)
{
if (cellValue.ToString().IsNullOrWhiteSpace())
continue;
if (isExstisTable)
{
if (k >= DayValueStartColumn)
updateTable.Columns.Add("dateId_" + cellValue.ToString().Replace("-", ""), Type.GetType("System.String"));
else
updateTable.Columns.Add(columnsStr[k], Type.GetType("System.String"));
}
else
{
if (k >= DayValueStartColumn)
dataTable.Columns.Add("dateId_" + cellValue.ToString().Replace("-", ""), Type.GetType("System.String"));
else
dataTable.Columns.Add(columnsStr[k], Type.GetType("System.String"));
}
}
else
{
if (k < DayValueStartColumn)
{
keyValues.Add(columnsStr[k], cellValue);
if (dicfileValues.ContainsKey(j))
dicfileValues[j] = keyValues;
else
dicfileValues.Add(j, keyValues);
}
if (cellValue.ToString().IsNullOrWhiteSpace())
continue;
if (k >= DayValueStartColumn)
dataRow[k] = Convert.ToInt32(cellValue);
else
dataRow[k] = cellValue;
boolRow = true;
}
}
if (j != firtRowNum)
{
string LineKey = null;
if (!dicfileValues.ContainsKey(j))
continue;
LineKey = dicfileValues[j]["factory"].ToString() + "@" + dicfileValues[j]["customerCode"] + "@" + dicfileValues[j]["projectNo"] + "@" + dicfileValues[j]["modelName"];
if ("@@@".Equals(LineKey.Trim(' ')))
continue;
if (isExstisTable)
{
dateBaseModel = checkBaseDate.Find(s => s.LineKey == LineKey);
//var KAKAK= dataRow; //= dataTable.NewRow();
}
}
if (isExstisTable && boolRow)
{
if (dateBaseModel == null)
{
if (isClonTable)
{ dataTable = updateTable.Clone(); isClonTable = false; }
dataTable.TableName = dateBaseTable + "@Insert";
dataTable.Rows.Add(dataRow.ItemArray);
}
else
updateTable.Rows.Add(dataRow);
}
else
{
if (boolRow)
dataTable.Rows.Add(dataRow);
}
}
#endregion
if (isExstisTable)
{
if (updateTable.Rows.Count > 0)
dataSet.Tables.Add(updateTable);
if (dataTable.Rows.Count>0)
dataSet.Tables.Add(dataTable);
}
else
dataSet.Tables.Add(dataTable);
SqlBulkCopyHelper.BulkCopy(dataSet, planMain, CurrentUser, checkBaseDate, dateBaseTable, isExstisTable);
}
watch.Stop(); //停止监视
TimeSpan timespan = watch.Elapsed; //获取当前实例测量得出的总时间
Trace.WriteLine(string.Format("打开窗口代码执行时间：{0}(毫秒)", timespan.TotalMilliseconds)); //总毫秒数
return null;
}
public static class SqlBulkCopyHelper
{
public static void BulkCopy(DataSet dataSet, DemandPlan demandplan, SysUser user,List<dynamic> checkBaseDate, string dateBaseTable, bool isExstisTable=false)
{
List<string> dataTableNames = new List<string>();
for (int g = 0; g < dataSet.Tables.Count; g++)
{
string TableName = dataSet.Tables[g].TableName;
dataTableNames.Add(TableName);
dataSet.Tables[g].Columns.Add("LineKey", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("_UpdateTime", Type.GetType("System.DateTimeOffset"));
dataSet.Tables[g].Columns.Add("_UpdatedBy", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("OperateFlag", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("Id", Type.GetType("System.Int64")).SetOrdinal(0);
dataSet.Tables[g].Columns.Add("DemandPlanId", Type.GetType("System.Int64")).SetOrdinal(1);
dataSet.Tables[g].Columns.Add("GroupKey", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("Month", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("Seq", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("_CreateTime", Type.GetType("System.DateTimeOffset"));
dataSet.Tables[g].Columns.Add("_CreatedBy", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("_IsDeleted", Type.GetType("System.Boolean"));
dataSet.Tables[g].Columns.Add("_DeletedBy", Type.GetType("System.String"));
dataSet.Tables[g].Columns.Add("_DeleteTime", Type.GetType("System.DateTimeOffset"));
dataSet.Tables[g].Columns.Add("Timestamp", Type.GetType("System.Int64"));
long GroupKey = long.MinValue;
List<dynamic> updateList = new List<dynamic>();
try
{
for (int i = 0; i < dataSet.Tables[g].Rows.Count; i++)
{
dynamic dateBaseModel = null;
string LineKey = dataSet.Tables[g].Rows[i]["factory"].ToString() + "@" + dataSet.Tables[g].Rows[i]["customerCode"] + "@" + dataSet.Tables[g].Rows[i]["projectNo"] + "@" + dataSet.Tables[g].Rows[i]["modelName"];
if (isExstisTable)
dateBaseModel = checkBaseDate.Find(s => s.LineKey == LineKey);
dataSet.Tables[g].Rows[i]["Id"] = TableName.Contains("Update")? dateBaseModel.Id : SnowFlakeSingle.Instance.NextId();
dataSet.Tables[g].Rows[i]["DemandPlanId"] = demandplan.Id;
if (i % 3 == 0)
{
GroupKey = long.Parse(dataSet.Tables[g].Rows[i]["Id"].ToString());
}
dataSet.Tables[g].Rows[i]["GroupKey"] = GroupKey;
dataSet.Tables[g].Rows[i]["LineKey"] = LineKey;
dataSet.Tables[g].Rows[i]["Month"] = demandplan.Month;
dataSet.Tables[g].Rows[i]["OperateFlag"] = EntityCode.DemandPlanDataOperateFlag.Create;
dataSet.Tables[g].Rows[i]["_CreateTime"] = DateTimeOffset.Now;
dataSet.Tables[g].Rows[i]["_CreatedBy"] = user._CreatedBy;
dataSet.Tables[g].Rows[i]["_IsDeleted"] = false;
}
}
catch (Exception ex)
{
throw;
}
}
DataTable InsertTable = dataSet.Tables[dataTableNames.Where(s => s.Contains("Insert")).FirstOrDefault()].Copy();//.Copy()因为DataTable加入了DataSet,不使用Copy()会导致后面SqlSugar.BulkCopy()和BulkUpdate一起使用时出现异常：A DataTable named 'DataTableName' already belongs to this DataSet.
DataTable UpdateTable = dataSet.Tables[dataTableNames.Where(s => s.Contains("Update")).FirstOrDefault()].Copy();//.Copy()因为DataTable加入了DataSet,不使用Copy()会导致后面SqlSugar.BulkCopy()和BulkUpdate一起使用时出现异常：A DataTable named 'DataTableName' already belongs to this DataSet.
if (!isExstisTable)
{
StringBuilder sqlCreate = new StringBuilder();
List<string> columnName = new List<string>();
if (UpdateTable ==null&& InsertTable != null && InsertTable.Rows.Count > 0)
{
for (int i = 0; i < InsertTable.Columns.Count; i++)
{
switch (InsertTable.Columns[i].ColumnName)
{
case "Id":
columnName.Add("[Id] [bigint] NOT NULL primary key");
break;
case "DemandPlanId":
columnName.Add("[DemandPlanId] [bigint] NOT NULL FOREIGN KEY REFERENCES [dbo].[DemandPlan](Id)");
break;
default:
if (InsertTable.Columns[i].DataType == Type.GetType("System.Int64"))
columnName.Add($"[{InsertTable.Columns[i].ColumnName}] [bigint] NULL");
else if (InsertTable.Columns[i].DataType == Type.GetType("System.DateTimeOffset"))
columnName.Add($"[{InsertTable.Columns[i].ColumnName}] [datetimeoffset](7) NULL");
else if (InsertTable.Columns[i].DataType == Type.GetType("System.Boolean"))
columnName.Add("[" + InsertTable.Columns[i].ColumnName + "] [bit] NULL");
else
columnName.Add("[" + InsertTable.Columns[i].ColumnName + "] [nvarchar](255) NULL");
break;
}
}
sqlCreate.Append($" CREATE TABLE {dateBaseTable}(").Append(string.Join(",", columnName)).Append($")");
SugarClient.Default.Context.Ado.ExecuteCommand(sqlCreate.ToString());
var defaultDbStr = App.GetService<IConfiguration>();
string sqlConnection = defaultDbStr.GetConnectionString(DbNames.DefaultDb.ToString());
using (var bulk = new SqlBulkCopy(sqlConnection))
{
bulk.DestinationTableName = dateBaseTable;
bulk.WriteToServer(InsertTable);
}
}
if (UpdateTable != null && InsertTable != null)
SqlSugarBulkUpdate(InsertTable, UpdateTable, dateBaseTable);
}
else
SqlSugarBulkUpdate(InsertTable, UpdateTable, dateBaseTable);
}
/// <summary>
/// SqlSugar 批量更新/新增
/// </summary>
/// <param name="InsertTable">新增数据的DataTable</param>
/// <param name="UpdateTable">更新数据的DataTable</param>
/// <param name="dateBaseTableName">目标数据库表名</param>
public static void SqlSugarBulkUpdate(DataTable InsertTable, DataTable UpdateTable, string dateBaseTableName)
{
try
{
if (InsertTable != null && InsertTable.Rows.Count > 0)
{
SugarClient.Default.Context.Fastest<DataTable>().AS(dateBaseTableName).BulkCopy(dateBaseTableName, InsertTable);
}
if (UpdateTable != null && UpdateTable.Rows.Count > 0)
SugarClient.Default.Context.Fastest<DataTable>().AS(dateBaseTableName).BulkUpdate(UpdateTable, new string[] { "Id" });
}
catch (Exception ex)
{
throw;
}
}
}
© Copyright 2014 - 2024 柏港建站平台 ejk5.com. 渝ICP备16000791号-4