public JsonResult LoadExcel()
{
List <DET_AGENTEC> ld = new List <DET_AGENTEC>();
if (Request.Files.Count > 0)
{
HttpPostedFileBase file = Request.Files["FileUpload"];
////string extension = System.IO.Path.GetExtension(file.FileName);
IExcelDataReader reader = ExcelReaderFactory.CreateReader(file.InputStream);
// 2. Use the AsDataSet extension method
DataSet result = reader.AsDataSet();
// The result of each spreadsheet is in result.Tables
// 3.DataSet - Create column names from first row
DataTable dt = result.Tables[0];
ld = objAList(dt);
reader.Close();
}
List <Flujos> ff = new List <Flujos>();
List <USUARIO> usuarios = new List <USUARIO>();
List <CLIENTE> clientes = new List <CLIENTE>();
List <PAI> paises = new List <PAI>();
foreach (DET_AGENTEC da in ld)
{
Flujos f = new Flujos();
////---------------------------------------USUARIO
f.USUARIOC_ID = da.USUARIOC_ID;
f.USUARIOC_IDX = true;
USUARIO u = usuarios.FirstOrDefault(x => x.ID.Equals(f.USUARIOC_ID));
if (u == null)
{
u = db.USUARIOs.Where(x => x.ID.Equals(f.USUARIOC_ID) && x.ACTIVO == true).FirstOrDefault();
if (u == null)
{
f.USUARIOC_IDX = false;
}
else
{
usuarios.Add(u);
}
}
if (!f.USUARIOC_IDX)
{
f.USUARIOC_ID = "<span class='red white-text'>" + f.USUARIOC_ID + "</span>";
}
////---------------------------------------PAIS
f.PAIS_ID = da.PAIS_ID;
f.PAIS_IDX = true;
PAI p = paises.FirstOrDefault(x => x.LAND.Equals(f.PAIS_ID));
if (p == null)
{
p = db.PAIS.Where(x => x.LAND.Equals(f.PAIS_ID) && x.ACTIVO && x.SOCIEDAD_ID != null).FirstOrDefault();
if (p == null)
{
f.PAIS_IDX = false;
}
else
{
paises.Add(p);
}
}
if (!f.PAIS_IDX)
{
f.PAIS_ID = "<span class='red white-text'>" + f.PAIS_ID + "</span>";
}
////---------------------------------------CLIENTE
f.KUNNR = da.KUNNR;
f.KUNNRX = true;
CLIENTE c = clientes.FirstOrDefault(x => x.KUNNR.Equals(f.KUNNR));
if (c == null)
{
c = db.CLIENTEs.Where(cc => cc.KUNNR.Equals(f.KUNNR) && cc.ACTIVO).FirstOrDefault();
if (c == null)
{
f.KUNNRX = false;
}
else
{
clientes.Add(c);
}
}
if (!f.KUNNRX)
{
f.KUNNR = "<span class='red white-text'>" + f.KUNNR + "</span>";
}
f.VERSION = da.VERSION.ToString();
f.POS = da.POS.ToString();
////---------------------------------------USUARIOA
f.USUARIOA_ID = da.USUARIOA_ID;
f.USUARIOA_IDX = true;
USUARIO ua = usuarios.FirstOrDefault(x => x.ID.Equals(f.USUARIOA_ID));
if (ua == null)
{
ua = db.USUARIOs.Where(x => x.ID.Equals(f.USUARIOA_ID) && x.ACTIVO == true).FirstOrDefault();
if (ua == null)
{
f.USUARIOA_IDX = false;
}
else
{
usuarios.Add(ua);
}
}
if (!f.USUARIOA_IDX)
{
f.USUARIOA_ID = "<span class='red white-text'>" + f.USUARIOA_ID + "</span>";
}
if (da.MONTO == null)
{
f.MONTO = null;
}
else
{
f.MONTO = da.MONTO.ToString();
}
f.PRESUPUESTO = da.PRESUPUESTO.ToString();
ff.Add(f);
}
JsonResult jl = Json(ff, JsonRequestBehavior.AllowGet);
return(jl);
}
public bool ReadExcelFile(string pathToFile)
{
string zipFilePath = HttpContext.Current.Server.MapPath("~/UploadedFiles");
string extractImagesTo = HttpContext.Current.Server.MapPath("~/UploadedFiles");
string pathToExcelFileOnServer = string.Empty;
using (ZipArchive archive = ZipFile.OpenRead(pathToFile))
{
foreach (ZipArchiveEntry entry in archive.Entries)
{
if (entry.FullName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase) || entry.FullName.EndsWith("xls", StringComparison.OrdinalIgnoreCase))
{
if (File.Exists(Path.Combine(zipFilePath, entry.Name)))
{
File.Delete(Path.Combine(zipFilePath, entry.Name));
}
pathToExcelFileOnServer = Path.Combine(zipFilePath, entry.Name);
entry.ExtractToFile(Path.Combine(zipFilePath, entry.Name));
}
else
{
if (!entry.FullName.EndsWith(".png", StringComparison.OrdinalIgnoreCase) && !entry.FullName.EndsWith(".jpg", StringComparison.OrdinalIgnoreCase))
{
continue;
}
if (File.Exists(Path.Combine(extractImagesTo, entry.Name)))
{
File.Delete(Path.Combine(extractImagesTo, entry.Name));
}
entry.ExtractToFile(Path.Combine(extractImagesTo, entry.Name));
}
}
}
if (!string.IsNullOrEmpty(pathToExcelFileOnServer))
{
using (var stream = File.Open(pathToExcelFileOnServer, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
var dt = new DataTable();
dt.Columns.Add("productID");
dt.Columns.Add("title");
dt.Columns.Add("description");
dt.Columns.Add("price");
dt.Columns.Add("image");
foreach (DataTable table in result.Tables)
{
foreach (DataRow dr in table.Rows.Cast <DataRow>().Skip(1)) //Skipping header
{
dt.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString());
}
}
using (var sqlBulkCopy = new SqlBulkCopy(DbConnectionAbstractClass.ConnectionString))
{
sqlBulkCopy.BatchSize = 5000;
sqlBulkCopy.DestinationTableName = "dbo.Products";
sqlBulkCopy.ColumnMappings.Add("productId", "productID");
sqlBulkCopy.ColumnMappings.Add("title", "title");
sqlBulkCopy.ColumnMappings.Add("description", "description");
sqlBulkCopy.ColumnMappings.Add("price", "price");
sqlBulkCopy.ColumnMappings.Add("image", "imageName");
sqlBulkCopy.WriteToServer(dt);
}
}
}
return(true);
}
return(false);
}
public ActionResult <List <KPIDataSource> > ProcessInput([FromForm] KPIInput input)
{
using (var stream = input.KPIInputExcel.OpenReadStream())
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
List <KPIDataSource> kpiDataSources = new List <KPIDataSource>();
foreach (DataTable table in result.Tables)
{
string dsName = "";
List <int> enumToLookFor = new List <int>();
KPIDataSource kpiDataSource = new KPIDataSource();
try
{
dsName = table.Rows[0].ItemArray[0].ToString();
if (dsName.EndsWith("Table", StringComparison.InvariantCultureIgnoreCase))
{
dsName = dsName.Substring(0,
dsName.LastIndexOf("Table", StringComparison.InvariantCultureIgnoreCase)).Trim();
//colNum = int.Parse(table.Rows[0].ItemArray[1].ToString());
kpiDataSource.Name = dsName;
// process headers
for (int col = 0; col < table.Rows[1].ItemArray.Length; col++)
{
string label = table.Rows[1].ItemArray[col].ToString();
if (string.IsNullOrEmpty(label))
{
continue;
}
ColumnDetails colDetails = new ColumnDetails();
kpiDataSource.Columns.Add(colDetails);
if (label.EndsWith("List"))
{
colDetails.IsEnumeration = true;
colDetails.Label = label.Substring(0,
label.LastIndexOf("List", StringComparison.InvariantCultureIgnoreCase)).Trim();
enumToLookFor.Add(col);
}
else
{
colDetails.Label = label;
}
}
kpiDataSources.Add(kpiDataSource);
if (table.Rows.Count < 3)
{
continue;
}
// process data
for (int dataRowIndex = 2; dataRowIndex < table.Rows.Count; dataRowIndex++)
{
for (int dataCol = 0; dataCol < table.Rows[dataRowIndex].ItemArray.Length; dataCol++)
{
if (enumToLookFor.IndexOf(dataCol) > -1)
{
kpiDataSource.Columns[dataCol].Enumeration.Add(
table.Rows[dataRowIndex].ItemArray[dataCol].ToString());
}
}
}
foreach (var enumCol in enumToLookFor)
{
kpiDataSource.Columns[enumCol].Enumeration =
kpiDataSource.Columns[enumCol].Enumeration.Distinct().ToList();
}
}
}
catch
{
continue;
}
}
return(kpiDataSources);
}
}
public List <Student> ReadCSVFile(string filePath)
{
List <Student> students = new List <Student>();
FileInfo file = new FileInfo(filePath);
var fullName = file.FullName;
if (file != null && file.Length > 0)
{
var filepath = fullName;
if (file.Extension == ".xls" || file.Extension == ".xlsx")
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
//open file and returns as Stream
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var t = reader.AsDataSet(new ExcelDataSetConfiguration()
{
// Gets or sets a value indicating whether to set the DataColumn.DataType
// property in a second pass.
UseColumnDataType = true,
// Gets or sets a callback to obtain configuration options for a DataTable.
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
// Gets or sets a value indicating the prefix of generated column names.
EmptyColumnNamePrefix = "Column",
// Gets or sets a value indicating whether to use a row from the
// data as column names.
UseHeaderRow = true,
// Gets or sets a callback to determine which row is the header row.
// Only called when UseHeaderRow = true.
ReadHeaderRow = (rowReader) =>
{
// F.ex skip the first row and use the 2nd row as column headers:
//rowReader.Read();
},
// Gets or sets a callback to determine whether to include the
// current row in the DataTable.
FilterRow = (rowReader) =>
{
return(true);
},
// Gets or sets a callback to determine whether to include the specific
// column in the DataTable. Called once per column after reading the
// headers.
FilterColumn = (rowReader, columnIndex) =>
{
return(true);
}
}
});
foreach (var col in t.Tables[0].Columns)
{
var c = col as DataColumn;
c.ColumnName = c.ColumnName
.Replace(" ", "")
.Replace("(", "")
.Replace(")", "")
.Replace("-", "")
.Replace("/", "");
}
students = t.Tables[0].ToList <Student>();
}
}
}
}
return(students);
}
public UserDataImportWindowViewModel()
{
this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.TwitchIDColumn));
this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.TwitchUsernameColumn));
this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.MixerUsernameColumn));
this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.LiveViewingHoursColumn));
this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.LiveViewingMinutesColumn));
this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.OfflineViewingHoursColumn));
this.Columns.Add(new UserDataImportColumnViewModel(UserDataImportColumnViewModel.OfflineViewingMinutesColumn));
foreach (CurrencyModel currency in ChannelSession.Settings.Currency.Values)
{
this.Columns.Add(new UserDataImportColumnViewModel(currency.Name));
}
foreach (UserDataImportColumnViewModel column in this.Columns)
{
this.columnDictionary[column.Name] = column;
}
this.UserDataFileBrowseCommand = this.CreateCommand(() =>
{
string filePath = ChannelSession.Services.FileService.ShowOpenFileDialog("Valid Data File Types|*.txt;*.csv;*.xls;*.xlsx");
if (!string.IsNullOrEmpty(filePath))
{
this.UserDataFilePath = filePath;
}
});
this.ImportButtonCommand = this.CreateCommand(async() =>
{
this.ImportButtonState = false;
try
{
int usersImported = 0;
int failedImports = 0;
if (string.IsNullOrEmpty(this.UserDataFilePath) || !File.Exists(this.UserDataFilePath))
{
await DialogHelper.ShowMessage(Resources.InvalidDataFile);
return;
}
if (!this.Columns[0].ColumnNumber.HasValue && !this.Columns[1].ColumnNumber.HasValue)
{
await DialogHelper.ShowMessage(Resources.DataFileRequiredColumns);
return;
}
List <UserDataImportColumnViewModel> importingColumns = new List <UserDataImportColumnViewModel>();
foreach (UserDataImportColumnViewModel column in this.Columns.Skip(2))
{
if (column.ColumnNumber.HasValue && column.ColumnNumber <= 0)
{
await DialogHelper.ShowMessage(Resources.DataFileInvalidColumn);
return;
}
importingColumns.Add(column);
}
Dictionary <string, CurrencyModel> nameToCurrencies = new Dictionary <string, CurrencyModel>();
foreach (CurrencyModel currency in ChannelSession.Settings.Currency.Values)
{
nameToCurrencies[currency.Name] = currency;
}
List <List <string> > lines = new List <List <string> >();
string extension = Path.GetExtension(this.UserDataFilePath);
if (extension.Equals(".txt") || extension.Equals(".csv"))
{
string fileContents = await ChannelSession.Services.FileService.ReadFile(this.UserDataFilePath);
if (!string.IsNullOrEmpty(fileContents))
{
foreach (string line in fileContents.Split(new string[] { "\n", Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries))
{
List <string> splits = new List <string>();
foreach (string split in line.Split(new char[] { ' ', '\t', ',', ';' }))
{
splits.Add(split);
}
lines.Add(splits);
}
}
else
{
await DialogHelper.ShowMessage(Resources.DataFileImportFailed);
}
}
else if (extension.Equals(".xls") || extension.Equals(".xlsx"))
{
using (var stream = File.Open(this.UserDataFilePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
if (result.Tables.Count > 0)
{
for (int i = 0; i < result.Tables[0].Rows.Count; i++)
{
List <string> values = new List <string>();
for (int j = 0; j < result.Tables[0].Rows[i].ItemArray.Length; j++)
{
values.Add(result.Tables[0].Rows[i].ItemArray[j].ToString());
}
lines.Add(values);
}
}
}
}
}
if (lines.Count > 0)
{
foreach (List <string> line in lines)
{
try
{
long twitchID = 0;
if (this.columnDictionary[UserDataImportColumnViewModel.TwitchIDColumn].ArrayNumber >= 0)
{
long.TryParse(line[this.columnDictionary[UserDataImportColumnViewModel.TwitchIDColumn].ArrayNumber], out twitchID);
}
string twitchUsername = null;
if (this.columnDictionary[UserDataImportColumnViewModel.TwitchUsernameColumn].ArrayNumber >= 0)
{
twitchUsername = line[this.columnDictionary[UserDataImportColumnViewModel.TwitchUsernameColumn].ArrayNumber];
}
string mixerUsername = null;
if (this.columnDictionary[UserDataImportColumnViewModel.MixerUsernameColumn].ArrayNumber >= 0)
{
mixerUsername = line[this.columnDictionary[UserDataImportColumnViewModel.MixerUsernameColumn].ArrayNumber];
}
bool newUser = true;
UserDataModel user = null;
if (twitchID > 0 && !string.IsNullOrEmpty(twitchUsername))
{
user = await ChannelSession.Settings.GetUserDataByPlatformID(StreamingPlatformTypeEnum.Twitch, twitchID.ToString());
if (user != null)
{
newUser = false;
}
else
{
UserViewModel userViewModel = await UserViewModel.Create(new Twitch.Base.Models.NewAPI.Users.UserModel()
{
id = twitchID.ToString(),
login = twitchUsername,
display_name = twitchUsername,
});
user = userViewModel.Data;
}
}
else if (twitchID > 0)
{
user = await ChannelSession.Settings.GetUserDataByPlatformID(StreamingPlatformTypeEnum.Twitch, twitchID.ToString());
if (user != null)
{
newUser = false;
}
else
{
Twitch.Base.Models.NewAPI.Users.UserModel twitchUser = await ChannelSession.TwitchUserConnection.GetNewAPIUserByID(twitchID.ToString());
if (twitchUser != null)
{
UserViewModel userViewModel = await UserViewModel.Create(twitchUser);
user = userViewModel.Data;
}
}
}
else if (!string.IsNullOrEmpty(twitchUsername))
{
user = await ChannelSession.Settings.GetUserDataByPlatformUsername(StreamingPlatformTypeEnum.Twitch, twitchUsername);
if (user != null)
{
newUser = false;
}
else
{
Twitch.Base.Models.NewAPI.Users.UserModel twitchUser = await ChannelSession.TwitchUserConnection.GetNewAPIUserByLogin(twitchUsername);
if (twitchUser != null)
{
UserViewModel userViewModel = await UserViewModel.Create(twitchUser);
user = userViewModel.Data;
}
}
}
else if (!string.IsNullOrEmpty(mixerUsername))
{
// TODO
//#pragma warning disable CS0612 // Type or member is obsolete
// UserDataModel mixerUserData = ChannelSession.Settings.GetUserDataByUsername(StreamingPlatformTypeEnum.Mixer, mixerUsername);
//#pragma warning restore CS0612 // Type or member is obsolete
// if (mixerUserData != null)
// {
// newUser = false;
// }
// else
// {
// user = new UserDataModel()
// {
// MixerID = uint.MaxValue,
// MixerUsername = mixerUsername
// };
// }
}
if (user != null)
{
if (newUser)
{
ChannelSession.Settings.SetUserData(user);
}
int iValue = 0;
if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.LiveViewingHoursColumn, line, out iValue))
{
user.ViewingHoursPart = iValue;
}
if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.LiveViewingMinutesColumn, line, out iValue))
{
user.ViewingMinutesPart = iValue;
}
if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.OfflineViewingHoursColumn, line, out iValue))
{
user.OfflineViewingMinutes = iValue;
}
if (this.GetIntValueFromLineColumn(UserDataImportColumnViewModel.OfflineViewingMinutesColumn, line, out iValue))
{
user.OfflineViewingMinutes = iValue;
}
foreach (var kvp in nameToCurrencies)
{
if (this.GetIntValueFromLineColumn(kvp.Key, line, out iValue))
{
kvp.Value.SetAmount(user, iValue);
}
}
ChannelSession.Settings.UserData.ManualValueChanged(user.ID);
usersImported++;
this.ImportButtonText = string.Format("{0} {1}", usersImported, MixItUp.Base.Resources.Imported);
}
else
{
failedImports++;
}
}
catch (Exception ex)
{
Logger.Log(LogLevel.Error, "User Data Import Failure - " + line);
Logger.Log(ex);
failedImports++;
}
}
}
await ChannelSession.SaveSettings();
if (failedImports > 0)
{
await DialogHelper.ShowMessage(string.Format(Resources.ImportWithFailures, usersImported, failedImports));
}
else
{
await DialogHelper.ShowMessage(string.Format(Resources.ImportSuccess, usersImported));
}
}
catch (Exception ex)
{
Logger.Log(ex);
await DialogHelper.ShowMessage(Resources.ImportFailed);
}
this.ImportButtonText = MixItUp.Base.Resources.ImportData;
this.ImportButtonState = true;
});
}
private void GetExclData(string FilePath)
{
try
{
//Get Data from Excel File
using (FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream);
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true // Use first row as ColumnNames
}
});
if (result.Tables.Count > 0)
{
switch (YearSource)
{
#region Getting Components Data
case YearsOf.Components:
comlist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_PART
{
PartNo = r.Field <object>("Part No.").ToString(),
PartName = r.Field <object>("Part Name").ToString(),
WholeQty = Convert.ToDecimal(r.Field <object>("Whole Qty.")),
WholeUnit = r.Field <object>("Whole Unit").ToString(),
ConversionQty = Convert.ToDecimal(r.Field <object>("Conversion Qty.")),
ConversionUnit = r.Field <object>("Conversion Unit").ToString(),
WholePrice = Convert.ToDecimal(r.Field <object>("Whole Price")),
ConversionPrice = Convert.ToDecimal(r.Field <object>("Conversion Price")),
ExpDate = Convert.ToDateTime(r.Field <object>("Exp. Date")),
ExpRateUS = Convert.ToDecimal(r.Field <object>("Exp. Rate(US)")),
ExpRateYEN = Convert.ToDecimal(r.Field <object>("Exp. Rate(YEN)")),
PreviousPrice = Convert.ToDecimal(r.Field <object>("Previous Price"))
}).ToList();
break;
#endregion
#region Getting Tabulation Data
case YearsOf.MaintainanceTable:
switch (YearofMaintenanceTable_Sub)
{
case MaintainanceTableSub.PlasticInjection:
pilist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_PI
{
MoldNo = r.Field <object>("Mold No.").ToString(),
MoldName = r.Field <object>("Mold Name").ToString(),
Oz = r.Field <object>("Oz").ToString(),
PurgePerG = Convert.ToDecimal(r.Field <object>("Purge/Gram")),
SPH = Convert.ToDecimal(r.Field <object>("Shots/Hour")),
Cavity = Convert.ToDecimal(r.Field <object>("Cavity")),
PPH = Convert.ToDecimal(r.Field <object>("Pieces/Hour"))
}).ToList();
break;
case MaintainanceTableSub.VacuumPlating:
vplist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_VP
{
PartNo = r.Field <object>("Part No.").ToString(),
PartName = r.Field <object>("Part Name").ToString(),
SourceData = r.Field <object>("Source Data").ToString(),
}).ToList();
break;
case MaintainanceTableSub.Assembly:
assylist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_H_ASSY
{
PartNo = r.Field <object>("Part No.").ToString(),
PartName = r.Field <object>("Part Name").ToString(),
RATEPERHOUR = Convert.ToDecimal(r.Field <object>("Rate/Hour"))
}).ToList();
break;
}
break;
#endregion
#region Getting Process Data
case YearsOf.ProcessSetup:
proclist = result.Tables[0].AsEnumerable().Select(r => new tbl_000_PROCESS
{
SubProcessCode = r.Field <object>("Subprocess Code").ToString(),
ProcessCode = r.Field <object>("Process Code").ToString(),
ItemDescription = r.Field <object>("Description").ToString(),
StandardA = Convert.ToDecimal(r.Field <object>("SP/HC")),
StandardB = Convert.ToDecimal(r.Field <object>("Cavity/PH")),
Remarks = r.Field <object>("Remarks").ToString()
}).ToList();
break;
#endregion
}
}
}
}
catch
{
throw new Exception("Invalid Type or File is in Use!");
}
}
//, out int count
public List <PayslipMaster> GetRec(PaySlipMasterViewModel model)
{
List <PayslipMaster> payslipMasters = new List <PayslipMaster>();
var fileName = $"{Directory.GetCurrentDirectory()}{@"\wwwroot\files"}" + "\\" + model.ExcelFile.FileName;
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
//int cnt = 0;
using (var stream = System.IO.File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
while (reader.Read())
{
if (!reader.GetValue(0).ToString().Contains("SNO"))
{
bool exists = db.PayslipMasters.Any(p =>
p.Month == model.Month &&
p.Year == model.Year &&
p.EmployeeCode == reader.GetValue(1).ToString());
if (exists)
{
continue;
}
MonthsEnum month = GetFinDate(model.Month);
var payslip = new PayslipMaster()
{
EmployeeCode = reader.GetValue(1).ToString(),
WorkingDays = Convert.ToInt32(reader.GetValue(2).ToString()),
//GrossPay = Convert.ToDouble(reader.GetValue(3).ToString()),
Basic = Convert.ToDouble(reader.GetValue(3).ToString()),
HRA = Convert.ToDouble(reader.GetValue(4).ToString()),
TA = Convert.ToDouble(reader.GetValue(5).ToString()),
LTA = Convert.ToDouble(reader.GetValue(6).ToString()),
CEA = Convert.ToDouble(reader.GetValue(7).ToString()),
SPL = Convert.ToDouble(reader.GetValue(8).ToString()),
Arrears = Convert.ToDouble(reader.GetValue(9).ToString()),
MonthlyGross = Convert.ToDouble(reader.GetValue(10).ToString()),
EmpEPF = Convert.ToDouble(reader.GetValue(11).ToString()),
EmpESI = Convert.ToDouble(reader.GetValue(12).ToString()),
TAX = Convert.ToDouble(reader.GetValue(13).ToString()),
TDS = Convert.ToDouble(reader.GetValue(14).ToString()),
OtherDeduction = Convert.ToDouble(reader.GetValue(15).ToString()),
TotalDeductions = Convert.ToDouble(reader.GetValue(16).ToString()),
TakeHome = Convert.ToDouble(reader.GetValue(17).ToString()),
Month = model.Month,
Year = model.Year,
CreatedBy = User.Identity.Name,
CreatedDate = DateTime.Now,
FinDate = new DateTime(model.Year, (int)month, (int)month == 3 ? 31 : 1)
};
payslipMasters.Add(payslip);
//cnt++;
}
}
}
}
//Inserted = true;
//count = cnt;
return(payslipMasters);
}
public XLSFileReader(FileStream openFileStream)
{
XlsFileReader = ExcelReaderFactory.CreateReader(openFileStream);
}
/// <summary>
/// Gets a datareader from an excel file (can be xls or xlsx).
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static IDataReader GetDataReader(string filePath)
{
var fs = new FileStream(filePath, FileMode.Open);
return(ExcelReaderFactory.CreateReader(fs));
}
private void CreateDictionary()
{
using (var stream = File.Open(filename, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
var table = result.Tables[6];
if (!table.Rows[0][0].ToString().Contains("Pos"))
{
throw new Exception("wrong table");
}
int key = -1;
string subtype = "";
foreach (DataRow row in table.Rows)
{
string val = (row[0]).ToString();
int num = -1;
if (int.TryParse(val, out num))
{
key = num;
propertys[key] = row[2].ToString();
subtype = "";
}
else if (val.StartsWith("-"))
{
key = -1;
}
else if (key > 0)
{
if (!string.IsNullOrWhiteSpace(row[1].ToString()))
{
string key_name = subtype + row[1].ToString();
if (descDict[key].ContainsKey(key_name))
{
throw new Exception("double entrys in description at key " + key + " with value " + row[1].ToString());
}
switch (key)
{
case 3:
break;
default:
descDict[key].Add(key_name, row[2].ToString());
break;
}
}
else
{
// subtype description
string potential_subkey = row[2].ToString().Trim();
switch (key)
{
case 6:
if (potential_subkey.Contains("ZFL, ZFS, ZFG"))
{
subtype = "Z_";
}
else if (potential_subkey.Contains("AZP"))
{
subtype = "AZP_";
}
break;
case 7:
if (potential_subkey.Contains("Pumpengröße 5-31cm3"))
{
subtype = "ZA_";
}
else if (potential_subkey.Contains("Pumpengröße 32-50cm3"))
{
subtype = "ZB_";
}
else if (potential_subkey.Contains("Pumpengröße 19cm3"))
{
subtype = "019_";
}
else if (potential_subkey.Contains("Pumpengröße 32-45cm3"))
{
subtype = "032_";
}
else if (potential_subkey.Contains("Pumpengröße 63cm3"))
{
subtype = "063_";
}
else if (potential_subkey.Contains("Pumpengröße 80cm3"))
{
subtype = "080_";
}
else if (potential_subkey.Contains("Pumpengröße 100cm3"))
{
subtype = "100_";
}
else if (potential_subkey.Contains("Pumpengröße 140cm3"))
{
subtype = "140_";
}
break;
case 12:
if (potential_subkey.Contains("Leistungsregler S1, S2, S3"))
{
subtype = "S_";
}
else if (potential_subkey.Contains("Digitalregler D1…D8"))
{
subtype = "D_";
}
else if (potential_subkey.Contains("Doppelzahnradpumpe"))
{
subtype = "Z_";
}
break;
default:
break;
}
}
}
}
}
}
}
private void btnBrowse_Click(object sender, EventArgs e)
{
labelSelectChart.Text = "";
labelBasicStacked.Visible = false;
labelLineChart.Visible = false;
labelDoughNutChart.Visible = false;
labelRowChart.Visible = false;
labelColumnChart.Visible = false;
labelPieChart.Visible = false;
labelSelectChart.Visible = false;
pictureBoxColumn.Visible = false;
pictureBoxPie.Visible = false;
pictureBoxRow.Visible = false;
pictureBoxDoughNut.Visible = false;
pictureBoxStacked.Visible = false;
pictureBoxLine.Visible = false;
pieChart_Population.Visible = false;
cartesianChartStacked.Visible = false;
pieChart1.Visible = false;
cartesianChartLine.Visible = false;
labelLineChart.Visible = false;
pictureBoxLine.Visible = false;
cartesianChart_Basic_Row.Visible = false;
cartesianChartBasicColumn.Visible = false;
comboBoxSheetSelected.Text = " ";
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{
Filter = "Excel 97-2003 Workbook|*.xls|Excel Workbook|*.xlsx|CSV|*.csv", ValidateNames = true, Multiselect = false
})
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
string path = openFileDialog.FileName;
FileInfo info = new FileInfo(path);
textBoxFilePath.Text = openFileDialog.FileName;
textBoxFileType.Text = info.Extension;
textBoxfilename.Text = info.Name;
if (info.Extension == ".csv" || info.Extension == "CSV")
{
labelExcelTable.ResetText();
try
{
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateCsvReader(stream))
{
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
tableCollection = result.Tables;
comboBoxSheetSelected.Items.Clear();
foreach (DataTable table in tableCollection)
{
comboBoxSheetSelected.Items.Add(table.TableName);
}
labelExcelTable.Text = "Excel Data in Tabular Form is As Follow" + "->Total No of Rows are ::" + reader.RowCount;
//DataSet copy = result.Copy();
//firstLiveChart(copy);
//if (filena)
//if (textBoxfilename.Text == "Pie_Chart.xlsx")
//{
// labelPieChart.Visible = true;
// pictureBoxPie.Visible = true;
//}
}
}
}
catch (Exception error)
{
MessageBox.Show(error.Message, "Wrong File Format Selected", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
labelExcelTable.ResetText();
try
{
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
tableCollection = result.Tables; //result is from the Class dataset (which Stores in memory relational databases)
comboBoxSheetSelected.Items.Clear();
foreach (DataTable table in tableCollection)
{
comboBoxSheetSelected.Items.Add(table.TableName);
}
labelExcelTable.Text = "Excel Data in Tabular Form is As Follow" + "->Total No of Rows are ::" + reader.RowCount;
}
}
}
catch (Exception error)
{
MessageBox.Show(error.Message, "Wrong File Format Selected", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
/// <summary>
/// Основная функция для вытягивания из Excel нужных данных
/// </summary>
private void Sta()
{
//Сюда сохраняем все листы. Создаем таблицу для всех Листов с названием этого листа.
List <DataTable> AllSheets = new List <DataTable>();
//Открываем для чтения наш файл
using (var stream = File.Open(FilePath, FileMode.Open, FileAccess.Read))
{
//Наш открытый поток читаем как файл Excel
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
//Номер листа
int sheetNumber = 0;
//Читаем по листам
do
{
//Название листа
var nameList = reader.Name;
//Таблица для текущего листа
DataTable currentSheet = new DataTable(nameList);
//Количество столбцов
var columnCount = reader.FieldCount;
//Добавляем в таблицу пустые столбцы с названием А+"номер"
for (int i = 0; i < columnCount; i++)
{
//Новый столбец
DataColumn columnNew = new DataColumn
{
DataType = System.Type.GetType("System.String"),
ColumnName = "A" + (i + 1)
};
//Добавляем столбец в таблицу
currentSheet.Columns.Add(columnNew);
}
//Читаем построчно!
while (reader.Read())
{
//Новая строка
DataRow rowNew = currentSheet.NewRow();
//Проходим по всем столбцам
for (int i = 0; i < columnCount; i++)
{
//Значение ячейки
string cellValue = null;
//Проверка что текущая ячейка не пуста
if (reader.GetValue(i) != null)
{
//Удаляем лишние пробелы
cellValue = reader.GetValue(i).ToString().Trim();
}
if (string.IsNullOrWhiteSpace(cellValue))
{
cellValue = null;
}
//Добавляем в строку с название столбца А+"номер"
rowNew["A" + (i + 1)] = cellValue;
}
//Добавляем готовую стоку в таблицу
currentSheet.Rows.Add(rowNew);
}
//Добавляем готовую таблицу листа в Список всех листов
AllSheets.Add(currentSheet);
//Кол-во листов +1
sheetNumber++;
//Читаем следующий лист
} while (reader.NextResult());
}
}
//Получит готовые Курсы
GetCourses(AllSheets);
//Получить готовый титульный лист
GetTitle(AllSheets);
//Вывод на консоль
ConsoleOut();
}
private async Task <IActionResult> LoadFile(FileInfo selectedFile)
{
if (selectedFile.Extension.ToUpper() == ".CSV") //not necessary as already filtering...
{
//use the CSVReader to read in the data
string message;
DataTable dataTable = CSVConverter.CSVDataToDataTable(selectedFile.OpenRead(), Program.UserCulture); //loading from disk so allow the CSV parser to just use the current culture
if (dataTable == null) //then failed to be read
{
message = "The CSV data needs to be in the correct format.";
}
else //got datatable but need to check
{
message = dataTable.CheckDataTable();
}
if (message == null)
{
//save in database
await SaveDatasetToDatabase(selectedFile.Name, dataTable);
try //to delete but if fails doesn't matter
{
System.IO.File.Delete(selectedFile.FullName);
}
catch (IOException)
{
//ignore error
}
TempData["InfoMessage"] = "File imported successfully!";
}
else
{
TempData["ErrorMessage"] = message;
}
return(RedirectToAction("Index")); //return to file loader/loaded files display screen
}
else if (selectedFile.Extension.ToUpper() == ".XLS" || selectedFile.Extension.ToUpper() == ".XLSX") //excel
{
//read in xls file
using (FileStream stream = selectedFile.OpenRead())
{
using (IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream))
{
DataSet excelDataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//if there is more than one worksheet then ask the user which sheet they want, otherwise automatically load it
if (excelDataSet.Tables.Count == 0)
{
TempData["ErrorMessage"] = "Error reading file. No sheets with valid data could be found in the Excel workbook.";
return(RedirectToAction("Index"));
}
else if (excelDataSet.Tables.Count == 1) //one excel sheet
{
DataTable dataTable = excelDataSet.Tables[0];
string message = dataTable.CheckDataTable();
if (message == null)
{
//save in database
await SaveDatasetToDatabase(selectedFile.Name, dataTable);
try //to delete...
{
System.IO.File.Delete(selectedFile.FullName);
}
catch (IOException)
{
//...if fails doesn't matter
}
TempData["InfoMessage"] = "File imported successfully!";
}
else
{
TempData["ErrorMessage"] = message;
}
return(RedirectToAction("Index")); //return to file loader/loaded files display screen
}
else //more than 1 excel sheet
{
List <string> tableNames = new List <string>();
foreach (DataTable t in excelDataSet.Tables)
{
tableNames.Add(t.TableName);
}
TempData["TableNames"] = tableNames;
return(RedirectToAction("SheetSelector", new { filename = selectedFile.FullName })); //go to sheet selection screen
}
}
}
}
else
{
throw new ArgumentException("File format not recognised.");
}
}
public async Task <bool> ImportExcelForLearners(string fileName)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
await using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = false,
}
};
var dataSet = reader.AsDataSet(conf);
var dataTable = dataSet.Tables[0];
for (var i = 1; i < dataTable.Rows.Count; i++)//Each row of the file
{
var person = new Person()
{
NationalId = dataTable.Rows[i].ItemArray[0].ToString(),
Title = dataTable.Rows[i].ItemArray[1].ToString(),
FirstName = dataTable.Rows[i].ItemArray[2].ToString(),
LastName = dataTable.Rows[i].ItemArray[3].ToString(),
PersonsDob = DateTime.ParseExact(dataTable.Rows[i].ItemArray[4].ToString(), "yyyy/MM/dd HH:mm:ss", CultureInfo.CurrentCulture),
GenderId = (await _lookUpService.GetGenders()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[5].ToString()))?.id,
NationalityId = (await _lookUpService.GetNationalities()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[6].ToString()))?.id,
CitizenshipStatusId = (await _lookUpService.GetCitizenships()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[7].ToString()))?.id,
DisabilityStatusId = (await _lookUpService.GetDisabilities()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[8].ToString()))?.id,
HomeLanguageId = (await _lookUpService.GetHomeLanguages()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[9].ToString()))?.id,
EquityId = (await _lookUpService.GetEquities()).Find(a => a.name.Equals(dataTable.Rows[i].ItemArray[10].ToString()))?.id,
Email = dataTable.Rows[i].ItemArray[11].ToString(),
PhoneNumber = dataTable.Rows[i].ItemArray[12].ToString(),
CreatedBy = "DataImport",
DateCreated = DateTime.Now,
};
var homeAddress = new Address()
{
HouseNo = dataTable.Rows[i].ItemArray[13].ToString(),
StreetName = dataTable.Rows[i].ItemArray[14].ToString(),
SuburbId = _lookUpService.GetSuburbs().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[15].ToString()))?.id,
CityId = _lookUpService.GetCities().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[16].ToString()))?.id,
ProvinceId = _lookUpService.GetProvinces().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[17].ToString()))?.id,
CountryId = _lookUpService.GetCountries().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[18].ToString()))?.id,
PostalCode = dataTable.Rows[i].ItemArray[19].ToString(),
AddressTypeId = 1,
CreatedBy = "DataImport",
DateCreated = DateTime.Now,
};
var postalAddress = new Address()
{
HouseNo = dataTable.Rows[i].ItemArray[20].ToString(),
StreetName = dataTable.Rows[i].ItemArray[21].ToString(),
SuburbId = _lookUpService.GetSuburbs().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[22].ToString()))?.id,
CityId = _lookUpService.GetCities().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[23].ToString()))?.id,
ProvinceId = _lookUpService.GetProvinces().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[24].ToString()))?.id,
CountryId = _lookUpService.GetCountries().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[25].ToString()))?.id,
PostalCode = dataTable.Rows[i].ItemArray[26].ToString(),
AddressTypeId = 1,
CreatedBy = "DataImport",
DateCreated = DateTime.Now,
};
person.Address.Add(homeAddress);
person.Address.Add(postalAddress);
var learner = new Learner
{
SchoolId = _lookUpService.GetSchools().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[27].ToString())).id,
SchoolGradeId = _lookUpService.GetSchoolGrades().Result.FirstOrDefault(a => a.name.Equals(dataTable.Rows[i].ItemArray[28].ToString())).id,
YearSchoolCompleted = Utils.GetDate(dataTable.Rows[i].ItemArray[29].ToString()),
AppliedYn = Const.FALSE,
RecruitedYn = Const.FALSE,
CreatedBy = "DataImport",
DateCreated = DateTime.Now,
Person = person,
};
learner.Person = person;
_learnerContext.Learner.Add(learner);
await _learnerContext.SaveChangesAsync();
}
}
}
return(true);
}
public Customer ReadFile(string filePath)
{
Customer customer = new Customer();
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
while (reader.Read())
{
if (reader.GetFieldType(0) == typeof(string) && reader.GetString(0) != null && reader.GetFieldType(1) == typeof(string))
{
string key = reader.GetString(0).Trim();
string value = reader.GetString(1);
switch (key)
{
case "RAZÃO SOCIAL":
customer.CompanyName = value;
break;
case "NOME FANTASIA":
customer.FantasyName = value;
break;
case "ENDEREÇO":
customer.Address = value;
break;
case "BAIRRO":
customer.District = value;
break;
case "CEP":
customer.CEP = value;
break;
case "CIDADE":
customer.City = value;
break;
case "ESTADO":
customer.State = value;
break;
case "CNPJ":
customer.CNPJ = value;
break;
case "I.E.":
customer.IE = value;
break;
case "TELEFONE":
customer.Phone = value;
break;
case "CELULAR":
customer.CellularNumber = value;
break;
case "CONTATO":
customer.Contact = value;
break;
case "E-MAIL":
customer.Emails.Add(value);
break;
default:
break;
}
}
else if (reader.GetFieldType(0) == typeof(string) && reader.GetString(0) == null && reader.GetFieldType(1) == typeof(string) && reader.GetString(1) != null)
{
string value = reader.GetString(1);
if (value.Contains("@"))
{
customer.Emails.Add(value);
}
}
}
}
}
return(customer);
}
public long ImportTable(string filePathAndName, RootPathObject rootPath, bool downloadToLocal = false, long limit = -1)
{
var rowsRead = 0;
var rowsSkipped = 0;
List <string> headerColumns = new List <string>();
List <int> columnIndexes;
List <ExcelCell> orderedExcelCells = rootPath.ModeSettings.ExcelModeSettings.GetOrderedExcelCells();
Dictionary <string, object> excelCellsValues = new Dictionary <string, object>();
if (orderedExcelCells.Count > 0)
{
var cellsStreamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
using (var memoryStream = new MemoryStream())
{
cellsStreamWrapper.Stream.CopyTo(memoryStream);
memoryStream.Position = 0;
using (var reader = ExcelReaderFactory.CreateReader(cellsStreamWrapper.Stream))
{
var currentRow = 0;
foreach (var cell in orderedExcelCells)
{
while (reader.Read() && currentRow < cell.RowIndex)
{
currentRow++;
}
var rawValue = reader.GetValue(cell.ColumnIndex);
if (!excelCellsValues.TryAdd(cell.ColumnName, rawValue))
{
excelCellsValues.Add(cell.GetUniqueName(), rawValue);
}
currentRow++;
}
}
}
// close down stream
cellsStreamWrapper.Close();
}
var streamWrapper = Utility.Utility.GetStream(filePathAndName, rootPath.FileReadMode, downloadToLocal);
using (var memoryStream = new MemoryStream())
{
streamWrapper.Stream.CopyTo(memoryStream);
memoryStream.Position = 0;
using (var reader = ExcelReaderFactory.CreateReader(memoryStream))
{
// skip lines
if (rootPath.SkipLines > 0)
{
while (reader.Read() && rowsSkipped < rootPath.SkipLines)
{
rowsSkipped++;
}
}
else
{
reader.Read();
}
// get desired column indexes
columnIndexes = rootPath.ModeSettings.ExcelModeSettings.GetAllExcelColumnIndexes();
if (columnIndexes.Count == 0)
{
columnIndexes = Enumerable.Range(0, reader.FieldCount).ToList();
}
// get column names
foreach (var i in columnIndexes)
{
if (rootPath.ModeSettings.ExcelModeSettings.HasHeader)
{
var field = reader.GetValue(i)?.ToString();
if (string.IsNullOrWhiteSpace(field))
{
field = $"NO_HEADER_COLUMN_{i}";
}
if (headerColumns.Contains(field))
{
headerColumns.Add($"{field}_DUPLICATE_{i}");
}
else
{
headerColumns.Add(field);
}
}
else
{
headerColumns.Add($"COLUMN_{i}");
}
}
// setup db table
var querySb = new StringBuilder($"CREATE TABLE IF NOT EXISTS [{_schemaName}].[{_tableName}] (");
foreach (var column in headerColumns)
{
querySb.Append(
$"[{column}] VARCHAR({int.MaxValue}),");
}
foreach (var cell in excelCellsValues)
{
querySb.Append(
$"[{cell.Key}] VARCHAR({int.MaxValue}),");
}
querySb.Length--;
querySb.Append(");");
var query = querySb.ToString();
Logger.Debug($"Create table query: {query}");
var cmd = new SqlDatabaseCommand
{
Connection = _conn,
CommandText = query
};
cmd.ExecuteNonQuery();
// prepare insert cmd with parameters
querySb = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] (");
foreach (var column in headerColumns)
{
querySb.Append($"[{column}],");
}
foreach (var cell in excelCellsValues)
{
querySb.Append($"[{cell.Key}],");
}
querySb.Length--;
querySb.Append(") VALUES (");
foreach (var column in headerColumns)
{
var paramName = $"@param{headerColumns.IndexOf(column)}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
foreach (var cell in excelCellsValues)
{
var paramName = $"@param{cell.Key.Replace(" ", "")}";
querySb.Append($"{paramName},");
cmd.Parameters.Add(paramName);
}
querySb.Length--;
querySb.Append(");");
query = querySb.ToString();
Logger.Debug($"Insert record query: {query}");
cmd.CommandText = query;
// read records
var trans = _conn.BeginTransaction();
try
{
// read all lines from file
while (reader.Read() && rowsRead < limit)
{
foreach (var column in headerColumns)
{
var rawValue = reader.GetValue(columnIndexes[headerColumns.IndexOf(column)])
?.ToString();
cmd.Parameters[$"@param{headerColumns.IndexOf(column)}"].Value = rawValue;
}
foreach (var cell in excelCellsValues)
{
var rawValue = cell.Value?.ToString();
cmd.Parameters[$"@param{cell.Key.Replace(" ", "")}"].Value = rawValue;
}
cmd.ExecuteNonQuery();
rowsRead++;
// commit every 1000 rows
if (rowsRead % 1000 == 0)
{
trans.Commit();
trans = _conn.BeginTransaction();
}
}
// commit any pending inserts
trans.Commit();
// close down stream
streamWrapper.Close();
}
catch (Exception e)
{
// rollback on error
trans.Rollback();
Logger.Error(e, e.Message);
throw;
}
}
}
return(rowsRead);
}
public async Task <IActionResult> ParseCompaniesKK(string Url)
{
try
{
if (string.IsNullOrEmpty(Url))
{
Url = Startup.Configuration["eGovCompaniesDownloadUrl"];
}
//скачивание страницы в виде HTML
HtmlDocument html = new HtmlDocument();
using (var client = new WebClient())
{
using (Stream stream = client.OpenRead(new Uri(Url)))
{
var reader = new StreamReader(stream, Encoding.UTF8);
var html_ = reader.ReadToEnd();
html.LoadHtml(html_);
}
}
// получение ссылок на zip-файлы со скачанной страницы
HtmlNode root = html.DocumentNode;
List <string> fileUrls = new List <string>();
foreach (HtmlNode node in root.Descendants())
{
if (node.Name == "a")
{
if (node.GetAttributeValue("href", "").Trim().Contains("getImg?id=ESTAT"))
{
fileUrls.Add($"{(new Uri(Url).Scheme)}://{(new Uri(Url).Host)}{node.GetAttributeValue("href", "").Trim()}");
}
}
}
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086117");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086118");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086119");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086120");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086121");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086123");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086124");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086125");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086126");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086127");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086128");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086129");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086131");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086132");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086133");
//fileUrls.Add("http://stat.gov.kz/getImg?id=ESTAT086134");
// скачивание zip-файлов
int fileCount = 0;
string sContentRootPath = _hostingEnvironment.WebRootPath;
sContentRootPath = Path.Combine(sContentRootPath, "Downloads");
List <string> zipFiles = new List <string>();
foreach (string url in fileUrls)
{
using (var client = new WebClient())
{
fileCount++;
string fileName = Path.ChangeExtension(fileCount.ToString(), ".zip");
var filePath = Path.Combine(sContentRootPath, Path.GetFileName(fileName));
zipFiles.Add(filePath);
client.Headers.Add("User-Agent", "Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/46.0.2490.33 Safari/537.36");
client.DownloadFile(url, filePath);
}
}
// разархивирование zip-файлов
List <string> xlsFiles = new List <string>();
foreach (string zipFile in zipFiles)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (ZipFile zip = ZipFile.Read(zipFile))
{
foreach (ZipEntry filefromzip in zip)
{
filefromzip.Extract(sContentRootPath, ExtractExistingFileAction.OverwriteSilently);
xlsFiles.Add(Path.Combine(sContentRootPath, filefromzip.FileName));
}
}
}
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\11.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\15.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\19.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\23.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\27.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\31.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\35.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\39.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\43.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\47.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\51.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\55.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\59.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\63.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\71.xls");
//xlsFiles.Add(@"D:\Documents\Google Drive\repos\Clever\CleverAPI\wwwroot\Downloads\75.xls");
// получение данных с Excel-файлов
List <CompanyKK> companiesKKNew = new List <CompanyKK>();
foreach (string xlsFile in xlsFiles)
{
var file = new FileInfo(xlsFile);
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var stream = System.IO.File.Open(xlsFile, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader.Read())
{
}
} while (reader.NextResult());
var result = reader.AsDataSet();
for (var i = 4; i < result.Tables[0].Rows.Count; i++)
{
string[] dateRegister = result.Tables[0].Rows[i][3].ToString().Split('.');
companiesKKNew.Add(new CompanyKK()
{
BIN = result.Tables[0].Rows[i][0].ToString(),
NameKK = result.Tables[0].Rows[i][1].ToString(),
NameRU = result.Tables[0].Rows[i][2].ToString(),
DateRegister = !string.IsNullOrEmpty(result.Tables[0].Rows[i][3].ToString()) ? (DateTime?)(new DateTime(Convert.ToInt32(dateRegister[2]), Convert.ToInt32(dateRegister[1]), Convert.ToInt32(dateRegister[0]))) : null,
OKED = result.Tables[0].Rows[i][4].ToString(),
ActivityKindKK = result.Tables[0].Rows[i][5].ToString(),
ActivityKindRU = result.Tables[0].Rows[i][6].ToString(),
OKEDSecondary = result.Tables[0].Rows[i][7].ToString(),
KRP = result.Tables[0].Rows[i][8].ToString(),
KRPNameKK = result.Tables[0].Rows[i][9].ToString(),
KRPNameRU = result.Tables[0].Rows[i][10].ToString(),
CATO = result.Tables[0].Rows[i][11].ToString(),
LocalityKK = result.Tables[0].Rows[i][12].ToString(),
LocalityRU = result.Tables[0].Rows[i][13].ToString(),
LegalAddress = result.Tables[0].Rows[i][14].ToString(),
HeadName = result.Tables[0].Rows[i][15].ToString()
});
}
}
}
}
int importedCount = 0,
deletedCount = 0;
foreach (CompanyKK companyKK in _context.CompanyKK.Where(c => c.ExpiredDateTime == null))
{
if (companiesKKNew.FirstOrDefault(c => c.NameRU == companyKK.NameRU) == null)
{
companyKK.ExpiredDateTime = null;
_context.Entry(companyKK).State = EntityState.Modified;
deletedCount++;
}
}
foreach (CompanyKK companyKK in companiesKKNew)
{
if (_context.CompanyKK.FirstOrDefault(c => c.NameRU == companyKK.NameRU) == null)
{
_context.CompanyKK.Add(companyKK);
importedCount++;
}
}
_context.SaveChanges();
//385 830 were
//386 603 new
return(Ok($"Imported: {importedCount.ToString()}, deleted: {deletedCount.ToString()}"));
}
catch (Exception exp)
{
return(Json(exp.Message));
}
}
public ActionResult UploadToServer(HttpPostedFileBase file)
{
ViewBag.Message = null;
string _path = null;
try
{
if (file.ContentLength > 0)
{
string _FileName = Path.GetFileName(file.FileName);
_path = Path.Combine(Server.MapPath("~/UploadFiles/"), _FileName);
file.SaveAs(_path);
}
}
catch (Exception ErrorMessage)
{
ViewBag.Message = ErrorMessage.Message;
return(View());
}
finally
{
if (ViewBag.Message == null)
{
FileStream stream = new FileStream(_path, FileMode.OpenOrCreate, FileAccess.Read);
var reader = ExcelReaderFactory.CreateReader(stream);
if (reader.Name == "Sheet1")
{
reader.Read();
//SqlConnection Connection = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS;Integrated Security=sspi;database=FileAnalysis");
SqlConnection con = new SqlConnection("Server=WIN-P2S8E7IH0S7\\SQLEXPRESS; Initial Catalog = FileAnalysis; User ID = sa; Password = Passw0rd@12;");
while (reader.Read())
{
int i;
con.Open();
SqlCommand cmd = new SqlCommand("insertintoppmdetailsoffile @ProjectNumber,@ProjectName,@ResourceNumber,@ResourceName,@TaskName,@Summary,@DateMentioned,@HoursMentioned,@ResourceRole,@ResourceType,@BillingCode,@ResourceHourlyRate,@ProgrameeProjectManager,@AfeDescrimination,@ProgrameeGroup,@Programee,@ProgrameeManager,@BussinessLead,@UAVP,@ITSABuildingCategory,@FundingCategory,@AFENumber,@ServiceCategory,@BillingRateOnShore,@BillingRateOffShore", con);
cmd.Parameters.AddWithValue("@ProjectNumber", reader[0].ToString());
cmd.Parameters.AddWithValue("@ProjectName", reader.GetString(1));
cmd.Parameters.AddWithValue("@ResourceNumber", reader[2].ToString());
cmd.Parameters.AddWithValue("@ResourceName", reader[3].ToString());
cmd.Parameters.AddWithValue("@TaskName", reader.GetString(4));
cmd.Parameters.AddWithValue("@Summary", reader.GetString(5));
cmd.Parameters.AddWithValue("@DateMentioned", reader.GetDateTime(6));
cmd.Parameters.AddWithValue("@HoursMentioned", reader.GetDouble(7));
cmd.Parameters.AddWithValue("@ResourceRole", reader.GetString(8));
cmd.Parameters.AddWithValue("@ResourceType", reader.GetString(9));
cmd.Parameters.AddWithValue("@BillingCode", reader.GetString(10));
cmd.Parameters.AddWithValue("@ResourceHourlyRate", reader.GetDouble(11));
cmd.Parameters.AddWithValue("@ProgrameeProjectManager", reader.GetString(12));
cmd.Parameters.AddWithValue("@AfeDescrimination", reader.GetString(13));
cmd.Parameters.AddWithValue("@ProgrameeGroup", reader.GetString(14));
cmd.Parameters.AddWithValue("@Programee", reader.GetString(15));
cmd.Parameters.AddWithValue("@ProgrameeManager", reader.GetString(16));
cmd.Parameters.AddWithValue("@BussinessLead", reader.GetString(17));
if (reader.GetString(18) == null || reader.GetString(18) == "")
{
cmd.Parameters.AddWithValue("@UAVP", "Not Avilable");
}
else
{
cmd.Parameters.AddWithValue("@UAVP", reader[18].ToString());
}
cmd.Parameters.AddWithValue("@ITSABuildingCategory", reader.GetString(19));
cmd.Parameters.AddWithValue("@FundingCategory", reader.GetString(20));
cmd.Parameters.AddWithValue("@AfeNumber", reader.GetString(21));
cmd.Parameters.AddWithValue("@ServiceCategory", reader.GetString(22));
cmd.Parameters.AddWithValue("@BillingRateOnShore", reader[23].ToString());
cmd.Parameters.AddWithValue("@BillingRateOffShore", reader[24].ToString());
i = cmd.ExecuteNonQuery();
con.Close();
}
}
MissingDates();
FillingTotalHours();
}
}
return(RedirectToAction("GettingAll"));
}
//POI入库
public string AnalyzePOIData(string filePath, string type)
{
string result = "ok";
try
{
if (type == "upload")
{
db.Execute("delete from T_POI");
}
DataTable sourceTable = db.GetDataTable("select top 0 * from T_POI");
DataTable districtTable = db.GetDataTable("select * from D_District");
DataTable poiTable = db.GetDataTable("select * from D_POICategory");
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var data = reader.AsDataSet();
DataTable poi = data.Tables[0];
for (var i = 1; i < poi.Rows.Count; i++)
{
DataRow dr = poi.Rows[i];
string district = dr[5].ToString();
int dCode = 0;
foreach (DataRow ddr in districtTable.Rows)
{
if (district == ddr["MC"].ToString())
{
dCode = Convert.ToInt32(ddr["DM"]);
break;
}
}
int poiCode = 0;
string category = dr[1].ToString();
foreach (DataRow ddr in poiTable.Rows)
{
if (category == ddr["MC"].ToString())
{
poiCode = Convert.ToInt32(ddr["DM"]);
break;
}
}
DataRow insert = sourceTable.NewRow();
insert["Name"] = dr[0].ToString();
double[] tp = Wgs84ToGcj02(Convert.ToDouble(dr[4]), Convert.ToDouble(dr[3]));
insert["Longitude"] = tp[0].ToString();
insert["Latitude"] = tp[1].ToString();
insert["Address"] = dr[2].ToString();
insert["DistrictCode"] = dCode;
insert["Area"] = dr[6].ToString();
insert["Category"] = poiCode;
if (type == "edit")
{
db.Execute("delete from T_POI where Name = '" + dr[0].ToString() + "'");
}
sourceTable.Rows.Add(insert);
}
db.ExecuteTransactionScopeInsert(sourceTable, "T_POI");
}
}
}
catch (Exception ex)
{
result = ex.Message + "||" + ex.StackTrace;
}
return(result);
}
public List <lichHoc> dongBoLicHoc(WebDriverWait wait, ChromeDriver chromeDriver, string MSSV)
{
if (File.Exists(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls")))
{
System.IO.File.Delete(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls"));
}
List <lichHoc> records = new List <lichHoc>();
chromeDriver.Url = "http://qldt.actvn.edu.vn/CMCSoft.IU.Web.Info/Reports/Form/StudentTimeTable.aspx";
wait.Until(driver => driver.FindElement(By.Id("drpType")).Displayed);
((IJavaScriptExecutor)chromeDriver).ExecuteScript("document.getElementById('drpType').value = 'B'");
((IJavaScriptExecutor)chromeDriver).ExecuteScript("document.getElementById('btnView').click()");
for (var i = 0; i < 30; i++)
{
if (File.Exists(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls")))
{
break;
}
Thread.Sleep(1000);
}
chromeDriver.Close();
chromeDriver.Quit();
if (File.Exists(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls"))) //helps to check if the zip file is present
{
using (var stream = System.IO.File.Open(HttpContext.Current.Server.MapPath("/download/ThoiKhoaBieuSinhVien.xls"), FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader.Read())
{
// reader.GetDouble(0);
}
} while (reader.NextResult());
// 2. Use the AsDataSet extension method
DataSet result = reader.AsDataSet();
DataTable dt = result.Tables[0];
//List<string> lstTinh = db.AsEnumerable().Select(x => x["Column0"].ToString()).Distinct().ToList();
if (dt.Rows.Count > 2)
{
foreach (DataRow row in dt.Rows)
{
if (dt.Rows.IndexOf(row) > 10)
{
if (row[0].ToString() != "")
{
List <DateTime> lstNgayHoc = new List <DateTime>();
string caHoc = row[8].ToString();
string monHoc = row[3].ToString();
string maHocPhan = row[1].ToString();
string lopHocPhan = row[4].ToString();
string giaoVien = row[7].ToString();
DateTime f = DateTime.Parse(row[10].ToString().Split('-')[0]);
DateTime t = DateTime.Parse(row[10].ToString().Split('-')[1]);
switch (row[0].ToString())
{
case "2": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Monday); break;
case "3": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Tuesday); break;
case "4": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Wednesday); break;
case "5": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Thursday); break;
case "6": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Friday); break;
case "7": lstNgayHoc = GetWeekdayInRange(f, t, DayOfWeek.Saturday); break;
}
for (int i = 0; i < lstNgayHoc.Count; i++)
{
lichHoc lh = new lichHoc();
lh.mssv = MSSV;
lh.lopHocPhan = lopHocPhan;
lh.caHoc = caHoc;
lh.maHocPhan = maHocPhan;
lh.monHoc = monHoc;
lh.giaoVien = giaoVien;
lh.ngayHoc = lstNgayHoc[i].ToString("dd/MM/yyyy");
records.Add(lh);
}
}
else
{
return(records);
}
}
}
return(records);
}
else
{
return(records);
}
}
}
}
return(records);
}
private void ImportExcelGraphikToolStripMenuItem_Click(object sender, EventArgs e)
{
string filePath = "";
string fileName = "";
DataTableCollection tableCollection;
DialogResult dr = MessageBox.Show("При импорте все старые данные будут перезаписаны на новые\n" +
"без возможности отмены действия.\n" +
"Вы уверены, что хотите импортировать данные?", "Подтверждение действия", MessageBoxButtons.YesNo);
if (dr == DialogResult.Yes)
{
try
{
string excelPath;
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls"
})
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
filePath = openFileDialog.FileName;
fileName = Path.GetFileName(filePath);
this.Cursor = CursorOnLoad.ChangeCoursor(this.Cursor);
excelPath = openFileDialog.FileName;
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
}
});
tableCollection = result.Tables;
string sqlDelProcess = "DELETE FROM StudyProcess";
using (SqlConnection connection = new SqlConnection(Strings.ConStr))//Удаление старого ГУП
{
connection.Open();
SqlCommand command = new SqlCommand(sqlDelProcess, connection);
command.ExecuteNonQuery();
}
foreach (DataTable table in tableCollection)
{
DataTable dt = tableCollection[table.TableName.ToString()];
dataGridView1.DataSource = dt;
int startX = -1, startY = -1;
string[] datesStart = new string[0];
string[] datesEnd = new string[0];
int datesLen = 0;
DateTime tempDate;
int coord;
bool horoshVnosit = false;
string groupNum = "";
string twoNumGr = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
coord = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (dataGridView1[j, i].Value.ToString().Trim().ToLower() == "группа")
{
startX = j;
startY = i;
}
string s = dataGridView1[j, i].Value.ToString().Trim().ToLower();
Regex regex = new Regex(@"^\w*\s?(\d{4}\s?-\s?\d{4})\w*");
MatchCollection matches = regex.Matches(s);
if (matches.Count > 0)
{
string sqlEditGYP = String.Format("UPDATE SystemTable SET StudyProcessPeriod = N'{0}'", matches[0]);
using (SqlConnection connection = new SqlConnection(Strings.ConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sqlEditGYP, connection);
command.ExecuteNonQuery();
}
StudyProcessPeriod();
}
if (i == startY + 2 && startY != -1)
{
if (dataGridView1[j, i].Value.ToString().Trim() != "")
{
datesLen++;
Array.Resize(ref datesStart, datesLen);
Array.Resize(ref datesEnd, datesLen);
datesStart[datesLen - 1] = dataGridView1[j, i].Value.ToString().Trim();
tempDate = DateTime.Parse(datesStart[datesLen - 1]).AddDays(6);
datesEnd[datesLen - 1] = tempDate.ToString("dd/MM/yyyy");
}
}
if (groupNum != "" && !horoshVnosit)
{
twoNumGr = groupNum[0].ToString();
twoNumGr += groupNum[1].ToString();
int temp;
try
{
temp = Convert.ToInt32(twoNumGr);
}
catch
{
horoshVnosit = true;
}
}
if (i >= startY + 6 && startY != -1 && !horoshVnosit)
{
if (j == 0)
{
groupNum = dataGridView1[j, i].Value.ToString().Trim();
}
else
{
if (dataGridView1[j, i].Value.ToString().Trim() != "")
{
string typeValue = dataGridView1[j, i].Value.ToString().Trim().ToLower();
string sqlAddProcessLine = String.Format("INSERT INTO StudyProcess " +
"(GroupNumber, WeekDateStart, WeekDateEnd, WeekType) " +
"VALUES (N'{0}',N'{1}',N'{2}',N'{3}')"
, groupNum, datesStart[coord], datesEnd[coord], typeValue);
using (SqlConnection connect = new SqlConnection(Strings.ConStr))
{
connect.Open();
SqlCommand command = new SqlCommand(sqlAddProcessLine, connect);
int h = command.ExecuteNonQuery();
if (h == 0)
{
MessageBox.Show("Error!!");
}
}
coord++;
}
}
}
}
}
}
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
StudyProcessPeriod();
}
string nameFromTable = "";
string sqlProv = String.Format("SELECT StydyProcessNowFileName FROM SystemTable");
using (SqlConnection connection = new SqlConnection(Strings.ConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sqlProv, connection);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (!reader.IsDBNull(0))
{
nameFromTable = reader.GetString(0);
}
}
}
if (nameFromTable != "")
{
File.Delete(Strings.excel_shablons_folder + nameFromTable);
}
if (!File.Exists(Strings.excel_shablons_folder + fileName))
{
File.Copy(filePath, Strings.excel_shablons_folder + fileName);
}
string sqlEditStud = String.Format("UPDATE SystemTable SET StydyProcessNowFileName = N'{0}'", fileName);
using (SqlConnection connection = new SqlConnection(Strings.ConStr))
{
connection.Open();
SqlCommand command = new SqlCommand(sqlEditStud, connection);
command.ExecuteNonQuery();
}
this.Cursor = CursorOnLoad.ChangeCoursor(this.Cursor);
}
public ExcelExtensionReponse Test(string fileName, string[] validationSet, string[] regexSet, string[] columnLengths)
{
ExcelExtensionReponse excelExtensionReponse = new ExcelExtensionReponse();
string JSONString = string.Empty;
IExcelDataReader excelReader;
try
{
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
if (fileName.Contains("xlsx"))
{
excelReader = ExcelReaderFactory.CreateReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
excelReader.Close();
List <string> getValidationErrors;
bool getDesiredColumnExistence = ColExistence(result, validationSet, out getValidationErrors);
if (getDesiredColumnExistence)
{
for (int i = 0; i < result.Tables[0].Columns.Count; i++)
{
bool regexStatus = regexValdiation(result.Tables[0].DefaultView.ToTable(false, result.Tables[0].Columns[i].ColumnName), regexSet[i], Int32.Parse(columnLengths[i]));
if (!regexStatus)
{
throw new Exception("'" + result.Tables[0].Columns[i].ColumnName + "' column has identified invalid data");
}
}
foreach (string dataColumn in validationSet)
{
if (DataExtensions.HasNull(result.Tables[0].DefaultView.ToTable(true, dataColumn)))
{
throw new Exception("'" + dataColumn + "' column has identified invalid data");
}
}
var regexItem = new Regex("[^0-9a-zA-Z]+");
for (int i = 0; i < result.Tables[0].Columns.Count; i++)
{
if (regexItem.IsMatch(result.Tables[0].Columns[i].ColumnName.ToString()))
{
result.Tables[0].Columns[i].ColumnName = "S_" + Regex.Replace(result.Tables[0].Columns[i].ColumnName, @"[^0-9a-zA-Z]+", "");
}
}
result.AcceptChanges();
JSONString = JsonConvert.SerializeObject(result.Tables[0]);
excelExtensionReponse.ExcelExtensionReponseData = JSONString;
excelExtensionReponse.success = true;
}
else
{
excelExtensionReponse.exception = new ArgumentException("Required Columns " + JsonConvert.SerializeObject(getValidationErrors) + " are not found");
}
}
catch (Exception ex)
{
excelExtensionReponse.exception = ex;
}
return(excelExtensionReponse);
}
private List <CaboFrowardMVC.Models.Masivo> UploadRecordsToDataBase(string fileName)
{
try
{
string pasaporte = "";
string rut = "";
string dv = "";
string patente = "";
string tipoVehiculo = "";
string lancha = "";
string nave = "";
string chofer = "";
string motivo = "";
string pc = "";
string dvEmpresa = "";
string NombreEmpresa = "";
var records = new List <CaboFrowardMVC.Models.Masivo>();
using (var stream = System.IO.File.Open(Path.Combine(Server.MapPath("~/Content/Files/"), fileName), FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
while (reader.Read())
{
if (reader.GetValue(6) != null)
{
pasaporte = reader.GetValue(6).ToString();
}
else
{
pasaporte = "";
}
if (reader.GetValue(1) != null)
{
rut = reader.GetValue(1).ToString();
}
else
{
rut = "";
}
if (reader.GetValue(2) != null)
{
dv = reader.GetValue(2).ToString();
}
else
{
dv = "";
}
if (reader.GetValue(11) != null)
{
dvEmpresa = reader.GetValue(11).ToString();
}
else
{
dvEmpresa = "";
}
if (reader.GetValue(12) != null)
{
NombreEmpresa = reader.GetValue(12).ToString();
}
else
{
NombreEmpresa = "";
}
if (reader.GetValue(13) != null)
{
patente = reader.GetValue(13).ToString();
}
else
{
patente = "";
}
if (reader.GetValue(14) != null)
{
tipoVehiculo = reader.GetValue(14).ToString();
}
else
{
tipoVehiculo = "";
}
if (reader.GetValue(15) != null)
{
lancha = reader.GetValue(15).ToString();
}
else
{
lancha = "";
}
if (reader.GetValue(16) != null)
{
nave = reader.GetValue(16).ToString();
}
else
{
nave = "";
}
if (reader.GetValue(17) != null)
{
chofer = reader.GetValue(17).ToString();
}
else
{
chofer = "N";
}
if (reader.GetValue(18) != null)
{
motivo = reader.GetValue(18).ToString();
}
else
{
motivo = "0";
}
if (reader.GetValue(19) != null)
{
pc = reader.GetValue(19).ToString();
}
else
{
pc = "";
}
if (pasaporte != "" || rut != "")
{
records.Add(new CaboFrowardMVC.Models.Masivo
{
idNacionalidad = reader.GetValue(0).ToString(),
Rut = rut,
Dv = dv,
Nombre = reader.GetValue(3).ToString(),
ApellidoPat = reader.GetValue(4).ToString(),
ApellidoMat = reader.GetValue(5).ToString(),
pasaporte = pasaporte,
idLocacion = reader.GetValue(7).ToString(),
FechaDesde = reader.GetValue(8).ToString(),
FechaHasta = reader.GetValue(9).ToString(),
RutEmpresa = reader.GetValue(10).ToString(),
Patente = patente,
TipVehiculo = tipoVehiculo,
Lancha = lancha,
Nave = nave,
Motivo = motivo,
PC = pc,
DvEmpresa = dvEmpresa,
NombreEmpresa = NombreEmpresa,
Chofer = chofer
});
}
}
}
}
return(records);
}
catch (Exception exp)
{
String R = exp.Message.ToString();
return(null);
}
//if (records.Any())
//{
// db.Users.AddRange(records);
// db.SaveChanges();
//}
}
public ImportResultDto GetImportDetails <T>(IFormFile file, ImportType importOption, int userId) where T : class
{
if (file.Length <= 0)
{
return(new ImportResultDto());
}
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using var ms = new MemoryStream();
file.CopyTo(ms);
var reader = ExcelReaderFactory.CreateReader(ms, new ExcelReaderConfiguration()
{
FallbackEncoding = Encoding.UTF8
});
var workbook = reader.AsDataSet();
if (workbook.Tables.Count <= 0)
{
return(null);
}
var table = reader.AsDataSet().Tables[0];
var rows = (from DataRow row in table.Rows select row).ToList();
switch (importOption)
{
case ImportType.AlternativeNoUpdate:
var importDetails = rows.Skip(1).Take(rows.Count() - 1).Select(currentRow =>
{
int.TryParse(currentRow[0].ToString() ?? "0", out var cartonNo);
if (currentRow.ItemArray.Length != 2)
{
throw new ServiceException(new ErrorMessage[]
{
new ErrorMessage()
{
Code = string.Empty,
Message = $"Invalid file format"
}
});
}
return(new ExcelParseAlternativeNoUpdateViewModel()
{
AlternativeNo = currentRow[1].ToString(),
CartonNo = cartonNo
});
});
var result = importDetails.Where(importDetails => importDetails.CartonNo != 0).ToList();
return(_importDataManagerRepository.GetAlternativeNoUpdateResult(result, userId));
case ImportType.DestructionDateUpdate:
var importDestructionDetails = rows.Skip(1).Take(rows.Count() - 1).Select(currentRow =>
{
int.TryParse(currentRow[0].ToString() ?? "0", out var cartonNo);
if (currentRow.ItemArray.Length != 3)
{
throw new ServiceException(new ErrorMessage[]
{
new ErrorMessage()
{
Code = string.Empty,
Message = $"Invalid file format"
}
});
}
return(new ExcelParseDestructioDateUpdateViewModel()
{
DestructionDate = currentRow[1].ToString(),
DestructionTimeFrame = currentRow[2].ToString(),
CartonNo = cartonNo
});
});
var resultDestruction = importDestructionDetails.Where(importDestructionDetails => importDestructionDetails.CartonNo != 0).ToList();
return(_importDataManagerRepository.GetDestructionDateUpdateResult(resultDestruction, userId));
default:
throw new Exception("Import not implemented");
}
}
public bool LerExcel(string caminho)
{
bool sucesso = false;
try
{
FileStream stream = File.Open(caminho, FileMode.Open, FileAccess.Read);
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
bool resultTitulo = false;
string tituloCorreto = "";
try
{
reader.Read();
string titulo = reader.GetString(0);
titulo = titulo + ',' + reader.GetString(1);
titulo = titulo + ',' + reader.GetString(2);
titulo = titulo + ',' + reader.GetString(3);
titulo = titulo + ',' + reader.GetString(4);
titulo = titulo + ',' + reader.GetString(5);
titulo = titulo + ',' + reader.GetString(6);
titulo = titulo + ',' + reader.GetString(7);
titulo = titulo + ',' + reader.GetString(8);
titulo = titulo + ',' + reader.GetString(9);
titulo = titulo + ',' + reader.GetString(10);
titulo = titulo + ',' + reader.GetString(11);
string tituloCor = "Divisao,Unidade,Tipo,Regime,Duracao,Data,Hora,Sala,Disciplina,Turma,Especial,Curso";
tituloCorreto = tituloCor;
resultTitulo = string.Compare(titulo, tituloCorreto, true) == 0;
}
catch
{
resultTitulo = false;
}
if (resultTitulo)
{
while (reader.Read())
{
Prova p = new Prova();
try
{
string nomeTurma = "";
string nomeDisciplina = "";
string nomeCurso = "";
p.Divisao = PreencheDivisao(reader.GetString(0));
p.Unidade = PreencheUnidade(reader.GetString(1));
p.Tipo = PreencheTipoProva(reader.GetString(2));
p.Regime = PreencheTipoCurso(reader.GetString(3));
p.Duracao = reader.GetDateTime(4).TimeOfDay;
p.Data = reader.GetDateTime(5);
p.Hora = reader.GetDateTime(6).TimeOfDay;
try
{
p.Sala_Codigo = RetornaSala(reader.GetString(7));
}
catch
{
p.Sala_Codigo = RetornaSala(reader.GetDouble(7).ToString());
}
nomeDisciplina = reader.GetString(8);
nomeCurso = reader.GetString(11);
p.Disciplina_Codigo = RetornaDisciplina(nomeDisciplina, TipoCursoConversor.TipoCursoParaNumero(p.Regime), nomeCurso);
nomeTurma = reader.GetString(9);
p.Turma_Codigo = RetornaTurma(nomeTurma, TipoCursoConversor.TipoCursoParaNumero(p.Regime));
p.Especial = RetornaEspecial(reader.GetString(10));
}
catch
{
System.Windows.Forms.MessageBox.Show("Erro ao validar dados da planilha, por favor, verifique o arquivo e tente novamente.", "Erro");
}
if (p.Sala_Codigo <= 0 | p.Disciplina_Codigo <= 0 | p.Turma_Codigo <= 0)
{
sucesso = false;
break;
}
else
{
ListaProvas.Add(p);
sucesso = true;
}
sucesso = true;
}
}
else
{
System.Windows.Forms.MessageBox.Show("Formato de Planilha Incorreto, por favor utilizar uma planilha no formato:\n Divisao,Unidade,Tipo,Regime,Duracao,Data,Hora,Sala,Disciplina,Turma,Especial,Curso", "Erro");
}
} while (reader.NextResult());
}
}
catch
{
System.Windows.MessageBox.Show("O arquivo está aberto. Por favor, feche-o antes de continuar");
}
return(sucesso);
}
private void BtnOpen_Click(object sender, EventArgs e)
{
categoryItemsLength = categoryItems.Count + 1;
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xlsx", ValidateNames = true
})
{
FileStream fsYear = File.Open("Ekonomi_base_sheets.xlsx", FileMode.Open, FileAccess.Read);
FileStream fsMonth = File.Open("seb_" + monthStrings[monthIndex] + "_2020.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader readerYear, readerMonth;
if (ofd.FilterIndex == 1)
{
readerYear = ExcelReaderFactory.CreateOpenXmlReader(fsYear);
readerMonth = ExcelReaderFactory.CreateOpenXmlReader(fsMonth);
}
else
{
readerYear = ExcelReaderFactory.CreateReader(fsYear);
readerMonth = ExcelReaderFactory.CreateReader(fsMonth);
}
monthSet = readerMonth.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
yearSet = new DataSet();
DataTable monthTable = new DataTable("Januari");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Februari");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Mars");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("April");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Maj");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Juni");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Juli");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Augusti");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("September");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Oktober");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("November");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("December");
yearSet.Tables.Add(monthTable);
monthTable = new DataTable("Summering");
yearSet.Tables.Add(monthTable);
// remove unnecessary columns
DataTable dt = monthSet.Tables[0];
if (monthIndex > 9)
{
dt.Columns[3].Expression = "";
}
else
{
dt.Columns.Remove(dt.Columns[5]);
dt.Columns.Remove(dt.Columns[2]);
dt.Columns.Remove(dt.Columns[1]);
}
monthSet.Tables[0].AcceptChanges();
foreach (DataRow row in monthSet.Tables[0].Rows)
{
string year = row[0].ToString();
// remove rows with a an empty description
if (row[1] == DBNull.Value)
{
row.Delete();
}
//radera alla rader som inte börjar med ett datum
else if (!year.ToString().Contains("2020"))
{
row.Delete();
}
else if (row[1].ToString().Contains("ISK"))
{
row.Delete();
}
else if (row[1].ToString().Contains("PREMIE FÖRS"))
{
row.Delete();
}
else if (Convert.ToInt32(row[2]) > 0)
{
row.Delete();
}
}
//important to reorder table when we delete
monthSet.Tables[0].AcceptChanges();
// sumerize induvidual caregory
monthSet.Tables[0].AcceptChanges();
tempDataTable = monthSet.Tables[0].Clone();
DataRow titleRow = tempDataTable.NewRow();
var hyra = new List <string>()
{
"HYRES"
};
categorization(hyra);
var bredband = new List <string>()
{
"TELEN", "COMV"
};
categorization(bredband);
var basutgifter = new List <string>()
{
"DFS", "SPOTIFY", "AEA", "SVEGOT", "WORLDCLASS", "ENKLA VARDAG", "CSN", "EON ", "E ON", "DINA FÖRSÄ"
};
categorization(basutgifter);
var mat = new List <string>()
{
"COOP", "NETT", "ICA", "WILLYS", "LIDL", "SELECTA", "PIZZ", "PIZ", "VISUALISERIN", "ENOTEKET", "PRESSBYRÅN", "RESQ CLUB", "GRILLEN", "ESPRESSO", "RESTAURANG", "KROG", "CAFE", "CIRCLE K", "PREEM", "MAX", "KOND", "HEMKÖP", "FALAFEL", "PIGEONSTREET"
};
categorization(mat);
var sprit = new List <string>()
{
"SYSTEM", "SALIGA", "ARBIS", "CROMWELL", "LERO", "VÄRDENS", "KARHUSET", "BROOKLYN", "TRÄDGÅR", "LION", "WATTS", "S 12", "O LEARYS", "BAR", "SOFO", "SKANETRAFIKE", "HUSET UNDER", "SODERKELLARE", "BROADWAY", "RESTAURANG K", "BORGEN"
};
categorization(sprit);
var swish = new List <string>()
{
"467"
};
categorization(swish);
var resor = new List <string>()
{
"ÖSTG", "SNELLTAGET", "SJ INTERNETB", "TAXI", "SJ MOB", "SJ AB"
};
categorization(resor);
var klader = new List <string>()
{
"BROTHERS", "MQ", "DRESSMAN INTERNETB", "SELLPY", "HM"
};
categorization(klader);
// we dont need to insert rows from category 'others' because they are already in source table
titleRow = tempDataTable.NewRow();
titleRow[0] = categoryItems[categoryItems.Count - 1].title;
tempDataTable.Rows.InsertAt(titleRow, tempDataTable.Rows.Count);
// sumerize category others
foreach (DataRow row in monthSet.Tables[0].Rows)
{
categoryItems[categoryItems.Count - 1].sum += Convert.ToInt32(row[2]);
}
monthSet.Tables[0].AcceptChanges();
int rowIndex = 0;
// insert categorized tabel from tempDataTable to monthSet table
foreach (DataRow row in tempDataTable.Rows)
{
DataRow newRow = monthSet.Tables[0].NewRow();
newRow[0] = row[0];
newRow[1] = row[1];
newRow[2] = row[2];
monthSet.Tables[0].Rows.InsertAt(newRow, rowIndex);
rowIndex++;
}
addSumerizedCategories(monthSet, yearSet);
readerMonth.Close();
readerYear.Close();
}
}
private void Import(bool useOffset)
{
var result = ImportFileDialog.ShowDialog(this);
if (result != DialogResult.OK)
{
return;
}
var strings = new Dictionary <string, string>();
try
{
using (var stream = File.Open(ImportFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var content = reader.AsDataSet();
var table = content.Tables[0];
for (var i = 0; i < table.Rows.Count; i++)
{
string key;
string value;
if (useOffset)
{
key = table.Rows[i][0].ToString();
value = table.Rows[i][2].ToString();
}
else
{
key = table.Rows[i][1].ToString();
value = table.Rows[i][2].ToString();
}
if (!string.IsNullOrEmpty(key) && !strings.ContainsKey(key))
{
strings.Add(key, value);
}
}
}
}
foreach (var subtitle in _subtitles)
{
var key = useOffset ? subtitle.Offset.ToString() : subtitle.Text;
if (!string.IsNullOrEmpty(key) && strings.ContainsKey(key))
{
subtitle.Translation = strings[key];
}
}
SubtitleGridView.Invalidate();
UpdateLabel();
}
catch (Exception e)
{
MessageBox.Show($"No se ha podido abrir el fichero.\r\n{e.GetType()}: {e.Message}", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void FillDB(string path, IProgress <double> controller)
{
FileStream stream;
IExcelDataReader reader;
stream = File.Open(path.Remove(path.Length - 6, 6) + "xlsx", FileMode.Open, FileAccess.Read);
reader = ExcelReaderFactory.CreateReader(stream);
SQLiteConnection database;
SQLiteCommand command;
int currentRow;
string format;
string transactionType;
string Paid = "";
database = new SQLiteConnection("Data Source=" + path + ";Version=3;");
database.Open();
currentRow = 2;
//Skip first row
reader.Read();
while (reader.Read())
{
transactionType = Convert.ToString(reader.GetValue(8));
if (transactionType == null)
{
break;
}
else if (Convert.ToString(reader.GetValue(5)).StartsWith("leg"))
{
//Do nothing - ignore customer events
currentRow++;
continue;
}
else if (transactionType == "PaymentCashless")
{
Paid = Convert.ToString(reader.GetValue(12)).Replace(",", ".");
}
else if (transactionType == "DebtBuyout" ||
transactionType == "BusinessBonus" ||
transactionType == "RechargeWebRefund")
{
//Ignore reload events
currentRow++;
continue;
}
format = "INSERT INTO CardInfo " +
"(AccountID, Date, Paid, Product, Machine) values " +
"(\"" + Convert.ToString(reader.GetValue(7)) + "\", " +
"\"" + Convert.ToString(reader.GetValue(0)) + "\", " +
Paid + ", " +
"\"" + Convert.ToString(reader.GetValue(10)) + "\"," +
"\"" + Convert.ToString(reader.GetValue(14)) + "\")";
command = new SQLiteCommand(format, database);
command.ExecuteNonQuery();
currentRow++;
controller.Report(Convert.ToDouble(currentRow - 2));
}
stream.Close();
stream.Dispose();
database.Close();
}
public async Task SaveClients(IFormFile file)
{
var a = file.OpenReadStream();
var reader = ExcelReaderFactory.CreateReader(a).AsDataSet(new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
});
foreach (DataTable dataTable in reader.Tables)
{
foreach (DataRow dataRow in dataTable.Rows)
{
var address = dataRow[1].ToString();
var house = await _context.Houses.FirstOrDefaultAsync(x => x.Address == address);
if (house == null)
{
house = new House
{
Address = address
};
var response = await Geocoder.GeocodeAddress(house.Address);
house.Latitude = response.Data.Items[0].Coordinates[0];
house.Longitude = response.Data.Items[0].Coordinates[1];
await _context.Houses.AddAsync(house);
await _context.SaveChangesAsync();
var entrancesList = new List <Models.Entrance>();
for (var i = 0; i < response.Data.Items[0].Entrances.Length; i++)
{
entrancesList.Add(new Models.Entrance
{
HouseId = house.Id,
Number = i + 1,
Latitude = response.Data.Items[0].Entrances[i].Coordinates[0],
Longitude = response.Data.Items[0].Entrances[i].Coordinates[1]
});
}
await _context.Entrances.AddRangeAsync(entrancesList);
await _context.SaveChangesAsync();
}
var enter = await _context.Entrances.FirstOrDefaultAsync(x => x.HouseId == house.Id && x.Number == int.Parse(dataRow[2].ToString()));
if (enter != null)
{
var client = new Client
{
EntranceId = enter.Id,
FullName = dataRow[0].ToString(),
ApartmentNumber = int.Parse(dataRow[3].ToString()),
PhoneNumber = dataRow[4].ToString(),
TariffPlan = dataRow[5].ToString()
};
await _context.Clients.Upsert(client).On(x => new { x.EntranceId, x.ApartmentNumber }).RunAsync();
}
}
}
}
public ActionResult UpdateSpreadSheet(HttpPostedFileBase file)
{
var then = DateTime.UtcNow;
var dataSet = GetDataSetFromFile(file.InputStream);
var model = DatasetToModel(dataSet);
var updatedModel = _service.UpdateStockCheck(model);
var stringModel = ModelToStringArray(updatedModel);
var now = DateTime.UtcNow;
var diff = now - then;
return(File(WriteCsvToMemory(stringModel), "text/csv", file.FileName + " Updated " + DateTime.UtcNow.ToString("yyyy-MM-dd") + ".csv"));
StockCheckModel DatasetToModel(DataSet ds)
{
var table = ds.Tables[0];
var columns = GetColumnNames(table);
var data = ds.Tables[0].AsEnumerable().Skip(1).Select(x => new StockCheckModelItem
{
Number = x.Field <object>("Number")?.ToString() ?? "",
Name = x.Field <string>("Name") ?? "",
ColourName = x.Field <string>("Colour") ?? "",
Quantity = x.Field <double>("Quantity"),
Condition = x.Field <string>("Cond") ?? "",
Description = x.Field <string>("Desc") ?? "",
Location = x.Field <object>("Location")?.ToString() ?? "",
OtherFields = x.ItemArray.Skip(7).Select(y => y.ToString())
}).ToList();
return(new StockCheckModel {
Items = data.ToList(), ColumnNames = columns
});
}
IEnumerable <string> GetColumnNames(DataTable t)
{
var names = new List <string>();
var header = t.Rows[0];
var i = 0;
header.ItemArray.ForEach(x =>
{
t.Columns[i].ColumnName = x.ToString();
names.Add(x.ToString());
i++;
});
return(names);
}
DataSet GetDataSetFromFile(Stream fileStream)
{
using (var reader = ExcelReaderFactory.CreateReader(fileStream))
{
var thing = reader.HeaderFooter;
return(reader.AsDataSet());
}
}
IEnumerable <IEnumerable <string> > ModelToStringArray(StockCheckModel m)
{
var arr = m.Items.Select(item =>
{
var strs = new List <string>
{
item.Number,
item.Name,
item.ColourName,
item.Quantity.ToString(),
item.Condition,
item.Description,
item.Location
};
strs = strs.Concat(item.OtherFields).ToList();
return(strs);
}).ToList();
arr.Insert(0, m.ColumnNames.ToList());
return(arr);
}
}