public static List<FZExcelProduct> ReadProductExcel(string path)
{
var book = new LinqToExcel.ExcelQueryFactory(path);
var query =
from row in book.Worksheet()
let item = new FZExcelProduct
{
Code = row["Kodi"].Cast<string>(),
Title = row["Titulli"].Cast<string>(),
Retail = row["RETAIL"].Cast<string>(),
Buy = row["Blen Fzone"].Cast<string>(),
Sell = row["Shet Fzone"].Cast<string>(),
Desc = row["Pershkrimi"].Cast<string>(),
Sex = row["F/M/U"].Cast<string>(),
Category = row["Kategoria"].Cast<string>(),
//ImgList = row["Lista foto"].Cast<string>(),
SizeXS = row["XS"].Cast<string>(),
SizeS = row["S"].Cast<string>(),
SizeM = row["M"].Cast<string>(),
SizeL = row["L"].Cast<string>(),
SizeXL = row["XL"].Cast<string>(),
SizeXXL = row["XXL"].Cast<string>(),
//OneSize = row["Mase Unike"].Cast<string>(),
//Quantity = row["Sasia"].Cast<string>()
}
select item;
return query.ToList();
}
public override IQueryable <PosData> Parse(int _Model, string filePath)
{
IQueryable <PosData> _PosList;
PosColumnMap _PosColumnMap = _posColumnMapService.GetByID(_Model);
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <PosData>(d => d.Year, _PosColumnMap.Year);
_Excel.AddMapping <PosData>(d => d.MonthYear, _PosColumnMap.MonthYear);
_Excel.AddMapping <PosData>(d => d.Distributor, _PosColumnMap.Distributor);
_Excel.AddMapping <PosData>(d => d.Customer, _PosColumnMap.Customer);
_Excel.AddMapping <PosData>(d => d.ISOCountryCode, _PosColumnMap.ISOCountryCode);
_Excel.AddMapping <PosData>(d => d.Country, _PosColumnMap.Country);
_Excel.AddMapping <PosData>(d => d.SalesArea, _PosColumnMap.SalesArea);
_Excel.AddMapping <PosData>(d => d.SalesManager, _PosColumnMap.SalesManager);
_Excel.AddMapping <PosData>(d => d.City, _PosColumnMap.City);
_Excel.AddMapping <PosData>(d => d.PostCode, _PosColumnMap.PostCode);
_Excel.AddMapping <PosData>(d => d.PartNo, _PosColumnMap.PartNo);
_Excel.AddMapping <PosData>(d => d.BaseCurrency, _PosColumnMap.BaseCurrency);
_Excel.AddMapping <PosData>(d => d.Qty, _PosColumnMap.Qty.ToString());
_Excel.AddMapping <PosData>(d => d.TotalSalesBaseCurreny, _PosColumnMap.TotalSalesBaseCurrency);
_Excel.AddMapping <PosData>(d => d.TotalSalesEUR, _PosColumnMap.TotalSalesEUR);
_Excel.AddMapping <PosData>(d => d.ProductSeries, _PosColumnMap.ProductSeries);
// every time get first sheet data
_PosList = from x in _Excel.Worksheet <PosData>(0)
select x;
return(_PosList);
}
public static List <AfiliadoModel> Read(string path)
{
try
{
var book = new LinqToExcel.ExcelQueryFactory(path);
var query = (from row in book.Worksheet(ConfigurationHelper.SheetName)
let item = new AfiliadoModel
{
Numero = row[ConfigurationHelper.Id].Cast <string>(),
Documento = row[ConfigurationHelper.DocumentColumn].Cast <string>(),
Nombre = row[ConfigurationHelper.NameColumn].Cast <string>(),
Apellido = row[ConfigurationHelper.LastnameColumn].Cast <string>(),
Localidad = row[ConfigurationHelper.StateColumn].Cast <string>(),
Provincia = row[ConfigurationHelper.ProvinceColumn].Cast <string>(),
}
select item).ToList();
return(query);
}
catch (Exception ex)
{
var message = string.Format("{0} => {1}", "ReadDataHelper", "Read");
Log.Error(message, ex);
throw;
}
}
private bool GetQualityCotrols(FileInfo file, string attribute)
{
try
{
var excel = new LinqToExcel.ExcelQueryFactory(file.ToString());
var columnsNames = excel.GetColumnNames(excel.GetWorksheetNames().ToList().First()).ToList();
foreach (var item in excel.GetWorksheetNames().ToList())
{
foreach (LinqToExcel.Row r in excel.Worksheet(item).ToList())
{
var enumerator = r.GetEnumerator();
while (enumerator.MoveNext())
{
if (string.IsNullOrEmpty(enumerator.Current.Value.ToString()))
{
enumerator.MoveNext();
continue;
}
var coded = enumerator.Current.Value.ToString();
GetIdRemarkLabel remarkId = GetIdRemarkLabelBL.GetIdRemarkLabel(columnsNames[r.IndexOf(enumerator.Current)]);
enumerator.MoveNext();
SetClientsCatalogsNextelBL.SetClientsCatalogsNextel(attribute, remarkId.IDRemarkLabel, coded, enumerator.Current.Value.ToString());
}
}
}
return(true);
}
catch (Exception)
{
return(false);
}
}
private void ObterXLS(object sender, EventArgs e)
{
try
{
OpenFileDialog openFileDialog1 = new OpenFileDialog
{
InitialDirectory = @"C:\",
Title = "Abrir arquivo XLS",
CheckFileExists = true,
CheckPathExists = true,
DefaultExt = "xls",
Filter = "Arquivos Excel (*.xls)|*.xls",
FilterIndex = 1,
RestoreDirectory = true,
ReadOnlyChecked = true,
ShowReadOnly = true
};
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
var book = new LinqToExcel.ExcelQueryFactory(@"" + openFileDialog1.FileName);
if (book != null)
{
MessageBox.Show("hello");
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static TCollection Read <TCollection, TItem>(Framework.DataStreamServiceResult file)
where TCollection : List <TItem>, new()
where TItem : class, new()
{
string _tempFilePath = Path.Combine(file.TempFilePath, file.FileName);
(new FileInfo(_tempFilePath)).Directory.Create();
using (var fileStream = File.Create(_tempFilePath))
{
file.Result.CopyTo(fileStream);
}
var excelSheet = new LinqToExcel.ExcelQueryFactory(_tempFilePath);
var result = (from t in excelSheet.Worksheet <TItem>(0)
select t).AsEnumerable();
TCollection collection = new TCollection();
collection.AddRange(result);
File.Delete(_tempFilePath);
return(collection);
}
public void TestGetResultsFromExcell()
{
var operation = SecretSharingBenchmarkReport.OperationType.RandomSecretReconstruction;
var filename = "256recon";
var keylength = 256;
var benchmarks = new LinqToExcel.ExcelQueryFactory(filename + ".xlsx");
var results = new List <SecretSharingBenchmarkReport>();
foreach (var row in benchmarks.Worksheet(0))
{
var n = row[0].Cast <int>();
for (int k = 0; k <= 10 && k * 5 <= n; k++)
{
var report = new SecretSharingBenchmarkReport();
report.keyLength = keylength;
report.Operation = operation;
report.n = n;
report.k = k == 0 ? 1 : k * 5;
report.TotalElapsedMilliseconds = Convert.ToDouble(
row[k + 1].Cast <string>().Split('\n')[1]);
results.Add(report);
}
}
var serializer = new XMLSerializer <PersistanceReport>();
PersistanceReport pr = new PersistanceReport();
pr.Reports = results;
serializer.Serialize(pr, "results\\" + filename + ".xml");
}
public override IQueryable <Edi_Pos> ParseEdiPos(string filePath)
{
IQueryable <Edi_Pos> _Edi_PosQuery;
List <Edi_Pos> _Edi_PosList = new List <Edi_Pos>();
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Edi_Pos>(d => d.Type, "Type");
_Excel.AddMapping <Edi_Pos>(d => d.Distributor, "DIST");
_Excel.AddMapping <Edi_Pos>(d => d.Address, "Address");
_Excel.AddMapping <Edi_Pos>(d => d.City, "CITY");
_Excel.AddMapping <Edi_Pos>(d => d.State, "STATE");
_Excel.AddMapping <Edi_Pos>(d => d.ZIP, "ZIP");
_Excel.AddMapping <Edi_Pos>(d => d.Series, "Series");
_Excel.AddMapping <Edi_Pos>(d => d.PartNo, "PARTNO");
_Excel.AddMapping <Edi_Pos>(d => d.Quantity, "QTY");
_Excel.AddMapping <Edi_Pos>(d => d.Cost, "COST");
_Excel.AddMapping <Edi_Pos>(d => d.Price, "PRICE");
_Excel.AddMapping <Edi_Pos>(d => d.ResellingExt, "RESEXT");
_Excel.AddMapping <Edi_Pos>(d => d.ACCT, "ACCT");
_Excel.AddMapping <Edi_Pos>(d => d.CustomerName, "NAME");
_Excel.AddMapping <Edi_Pos>(d => d.MarketCode, "Corporate Market");
_Excel.AddMapping <Edi_Pos>(d => d.MarketCode, "Market Code");
_Excel.AddMapping <Edi_Pos>(d => d.Market, "Market Segment");
_Excel.AddMapping <Edi_Pos>(d => d.SubSegmentCode, "Sub-Segment Code");
_Excel.AddMapping <Edi_Pos>(d => d.SubSegment, "Sub-Segment");
_Excel.AddMapping <Edi_Pos>(d => d.Remarks, "Remarks");
_Excel.AddMapping <Edi_Pos>(d => d.ShipDate, "SHIP DATE");
_Excel.AddMapping <Edi_Pos>(d => d.ShipDate2, "SHIP DATE 2");
_Excel.AddMapping <Edi_Pos>(d => d.ShipMonth, "SHIP MONTH");
_Excel.AddMapping <Edi_Pos>(d => d.ShipQuarter, "SHIP QUARTER");
_Excel.AddMapping <Edi_Pos>(d => d.YY, "YY");
_Excel.AddMapping <Edi_Pos>(d => d.CountryCode, "COUNTRY CODE");
_Excel.AddMapping <Edi_Pos>(d => d.Country, "COUNTRY ");
_Excel.AddMapping <Edi_Pos>(d => d.Region, "Region");
int _Start = 1;
// _Edi_PosQuery = from x in _Excel.Worksheet<Edi_Pos>(0)
// select x;
_Edi_PosQuery = from x in _Excel.Worksheet <Edi_Pos>("Data")
select x;
foreach (Edi_Pos c in _Edi_PosQuery)
{
try
{
c.ShipDate2 = c.ShipDate2 != null?DateTime.Parse(c.ShipDate2).ToString("yyyy/MM/dd") : "";
c.Status = "T";
_Edi_PosList.Add(c);
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
_Edi_PosQuery = _Edi_PosList.AsQueryable();
return(_Edi_PosQuery);
}
public static List <FZExcelProduct> ReadProductExcel(string path)
{
var book = new LinqToExcel.ExcelQueryFactory(path);
var query =
from row in book.Worksheet()
let item = new FZExcelProduct
{
Code = row["Kodi"].Cast <string>(),
Title = row["Titulli"].Cast <string>(),
Retail = row["RETAIL"].Cast <string>(),
Buy = row["Blen Fzone"].Cast <string>(),
Sell = row["Shet Fzone"].Cast <string>(),
Desc = row["Pershkrimi"].Cast <string>(),
Sex = row["F/M/U"].Cast <string>(),
Category = row["Kategoria"].Cast <string>(),
//ImgList = row["Lista foto"].Cast<string>(),
SizeXS = row["XS"].Cast <string>(),
SizeS = row["S"].Cast <string>(),
SizeM = row["M"].Cast <string>(),
SizeL = row["L"].Cast <string>(),
SizeXL = row["XL"].Cast <string>(),
SizeXXL = row["XXL"].Cast <string>(),
//OneSize = row["Mase Unike"].Cast<string>(),
//Quantity = row["Sasia"].Cast<string>()
}
select item;
return(query.ToList());
}
public IEnumerable<QuestionAndAnswer> ReadQuestionsAndAnswers()
{
var excelDataFactory = new LinqToExcel.ExcelQueryFactory(@"QuestionsAndAnswersData.xlsx");
var rows = excelDataFactory.Worksheet("Sheet1");
foreach (var row in rows)
{
yield return new QuestionAndAnswer {Question = row[0], Answer = row[1]};
}
}
/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_Scrap> _Budget_ScrapList = new List <Budget_Scrap>();
IQueryable <Scrap> _ScrapList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Scrap>(d => d.ScrapType, "ScrapType");
_Excel.AddMapping <Scrap>(d => d.PartNumber, "PartNumber");
_Excel.AddMapping <Scrap>(d => d.Reason, "Reason");
_Excel.AddMapping <Scrap>(d => d.Month, "Month");
_Excel.AddMapping <Scrap>(d => d.Quantity, "Quantity");
_Excel.AddMapping <Scrap>(d => d.PurchasePrice, "PurchasePrice");
_Excel.AddMapping <Scrap>(d => d.BookValue, "BookValue");
// every time get first sheet data
_ScrapList = from x in _Excel.Worksheet <Scrap>(0)
select x;
int _Start = 2;
foreach (Scrap c in _ScrapList)
{
if (_Start == 2 || _Start == 3 || c.PartNumber == null)
{
_Start++;
continue;
}
try
{
Budget_Scrap _Budget_Scrap = new Budget_Scrap();
_Budget_Scrap.ItemId_ScrapType = _ItemCatalog.Where(x => x.ClassName == "ScrapType" && x.Name.Trim() == c.ScrapType.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "ScrapType" && x.Name.Trim() == c.ScrapType.Trim()).First().ItemId : "";
_Budget_Scrap.PartNumber = c.PartNumber;
_Budget_Scrap.Reason = c.Reason;
_Budget_Scrap.Month = c.Month;
_Budget_Scrap.Quantity = this._commonFileService.GetExcelMinusNumber(c.Quantity);
_Budget_Scrap.PurchasePrice = this._commonFileService.GetExcelMinusNumber(c.PurchasePrice);
_Budget_Scrap.BookValue = this._commonFileService.GetExcelMinusNumber(c.BookValue);
_Budget_Scrap.Date = (DateTime.Now.Year + 1).ToString();
_Budget_Scrap.DepartmentId = base.DepartmentId;
_Budget_Scrap.Version = base.Version;
_Budget_Scrap.Factory = base.Factory;
_Budget_ScrapList.Add(_Budget_Scrap);
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
_Budget.Scrap = _Budget_ScrapList;
return(_Budget);
}
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_HeadCountHR> _Budget_HeadCountHRList = new List <Budget_HeadCountHR>();
IQueryable <CustomHeadcount> _CustomHeadcountList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <CustomHeadcount>(d => d.Account, "Employee Code");
_Excel.AddMapping <CustomHeadcount>(d => d.AltwName, "Employee Name");
_Excel.AddMapping <CustomHeadcount>(d => d.HR, "銷/管/研");
_Excel.AddMapping <CustomHeadcount>(d => d.DirectType, "DL/IDL");
_Excel.AddMapping <CustomHeadcount>(d => d.Func, "Func");
_Excel.AddMapping <CustomHeadcount>(d => d.Title, "Title");
// every time get first sheet data
_CustomHeadcountList = from x in _Excel.Worksheet <CustomHeadcount>(0)
select x;
int _Start = 2;
string _JobFunction = base.Factory == "LT" ? "JobFunction" : "JobFunctionKSZ";
string _Title = base.Factory == "LT" ? "Title" : "TitleKSZ";
foreach (CustomHeadcount ch in _CustomHeadcountList)
{
try
{
Budget_HeadCountHR _Budget_HeadCountHR = new Budget_HeadCountHR();
_Budget_HeadCountHR.Account = ch.Account;
_Budget_HeadCountHR.AltwName = ch.AltwName;
string _ItemId_HR = "";
if (ch.HR != null && ch.HR != "")
{
_ItemId_HR = _ItemCatalog.Where(x => x.ClassName == "HR" && x.Name.Trim() == ch.HR.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "HR" && x.Name == ch.HR).First().ItemId : "";
}
_Budget_HeadCountHR.ItemId_HR = _ItemId_HR;
_Budget_HeadCountHR.ItemId_DirectType = _ItemCatalog.Where(x => x.ClassName == "DirectType" && x.Name.Trim() == ch.DirectType.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "DirectType" && x.Name.Trim() == ch.DirectType.Trim()).First().ItemId : "";
_Budget_HeadCountHR.ItemId_JobFunction = _ItemCatalog.Where(x => x.ClassName == _JobFunction && x.Name.Trim() == ch.Func.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == _JobFunction && x.Name.Trim() == ch.Func.Trim()).First().ItemId : "";
_Budget_HeadCountHR.ItemId_Title = _ItemCatalog.Where(x => x.ClassName == _Title && x.Name.Trim() == ch.Title.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == _Title && x.Name.Trim() == ch.Title.Trim()).First().ItemId : "";
_Budget_HeadCountHR.IsDel = false;
_Budget_HeadCountHRList.Add(_Budget_HeadCountHR);
}
catch (Exception ex)
{
string _Ch = JsonConvert.SerializeObject(ch);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _Ch);
}
_Start++;
}
_Budget.HeadCountHR = _Budget_HeadCountHRList;
return(_Budget);
}
void ExcelGetValues()
{
var baseExcel = new LinqToExcel.ExcelQueryFactory(@"./Base/Base.xlsx");
var v1 = from c in baseExcel.Worksheet <ExcelModel>("Name") select c;
firstName.Clear();
secondName.Clear();
foreach (var v in v1)
{
firstName.Add(v.valueFN);
secondName.Add(v.valueSN);
}
}
private static void MegaSena()
{
var book = new LinqToExcel.ExcelQueryFactory(@"c:\temp\mega.xlsx");
var query =
from row in book.Worksheet("mega")
let item = new
{
Concurso = row["Concurso "].Cast <int>(),
Local = row["Local "].Cast <string>(),
D1 = row["Coluna 1 "].Cast <int>(),
D2 = row["Coluna 2 "].Cast <int>(),
D3 = row["Coluna 3 "].Cast <int>(),
D4 = row["Coluna 4 "].Cast <int>(),
D5 = row["Coluna 5 "].Cast <int>(),
D6 = row["Coluna 6 "].Cast <int>()
}
where row["Coluna 1 "].Cast <int>() != 0
select item;
List <int> todosNumeros = new List <int>();
todosNumeros.AddRange(query.Select(x => x.D1).ToArray());
todosNumeros.AddRange(query.Select(x => x.D2).ToArray());
todosNumeros.AddRange(query.Select(x => x.D3).ToArray());
todosNumeros.AddRange(query.Select(x => x.D4).ToArray());
todosNumeros.AddRange(query.Select(x => x.D5).ToArray());
todosNumeros.AddRange(query.Select(x => x.D6).ToArray());
var queryGrupo = todosNumeros
.GroupBy(x => x)
.Select(g => new { Numero = g.Key, Quantidade = g.Count() }).ToList();
var ultimo = query.Select(x => x.Concurso).ToList().OrderByDescending(x => x).FirstOrDefault();
Console.WriteLine($"MEGA SENA Total de Resultados {query.Count()}");
Console.WriteLine("");
Console.WriteLine($"MEGA SENA Ultimo Consurso da Planilha {ultimo}");
Console.WriteLine("");
foreach (var item in queryGrupo.OrderByDescending(x => x.Quantidade))
{
Console.WriteLine($"Numero {item.Numero} - Repeticoes {item.Quantidade}");
}
Console.WriteLine("");
}
private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
var file = openFileDialog1.FileName;
lblFile.Text = file;
lblFile.Show();
var excel = new LinqToExcel.ExcelQueryFactory(openFileDialog1.FileName);
if (file.Substring(file.Length - 3) == "csv")
{
dataGridView1.DataSource = excel.Worksheet <measure>().ToList();
}
else
{
cmbSheets.DataSource = excel.GetWorksheetNames().ToList();
}
}
public List <LinkTargetDownloadInfo> DownloadLinkTargetInfo()
{
var projectFile = new LinqToExcel.ExcelQueryFactory(DOWNLOADED_CONTENT_INFO_CSV);
List <LinkTargetDownloadInfo> projectList = (from row in projectFile.Worksheet(DOWNLOAD_CSV_SHEET_NAME)
let item = new LinkTargetDownloadInfo()
{
Id = row["Id"].Cast <string>(),
URL = row["URL"].Cast <string>(),
TargetLocation = row["TargetLocation"].Cast <string>(),
IsDownloadedSuccessful = row["IsDownloadedSuccessful"].Cast <bool>(),
DownloadTime = row["DownloadTime"].Cast <string>(),
DownloadException = row["DownloadException"].Cast <string>(),
TargetChanged = row["TargetChanged"].Cast <string>()
}
select item).ToList();
return(projectList);
}
public ActionResult UploadData(int WeekId)
{
string fileName = string.Empty;
string destinationPath = string.Empty;
var uniqueGUID = Guid.NewGuid();
var file_Uploader = Request.Files["UploadedImage"];
if (file_Uploader != null)
{
fileName = Path.GetFileName(file_Uploader.FileName);
destinationPath = Path.Combine(Server.MapPath("~/Upload/"), uniqueGUID + "_" + fileName);
file_Uploader.SaveAs(destinationPath);
}
string userName = _cookieHelper.GetCookie("userid");
//string fileName = System.Web.HttpContext.Current.Server.MapPath("~/Upload/File.xlsx");
string sheetNameFirst = "Summary";
var bookFirst = new LinqToExcel.ExcelQueryFactory(destinationPath);
var dataFirstTab = from x in bookFirst.Worksheet(sheetNameFirst.TrimEnd())
select x;
var worksheetsList = bookFirst.GetWorksheetNames().ToList();
var dataSecondTab = from y in bookFirst.Worksheet(worksheetsList[0])
select y;
int error = this._projectSummaryService.SaveDataFromExcelSheet(worksheetsList, dataFirstTab, dataSecondTab, WeekId, userName, destinationPath);
if (error == -1)
{
return(Json(new { Message = "Failure,First Tab Excel Format Is Not Correct" }));
}
else if (error == -2)
{
return(Json(new { Message = "Failure" }));
}
else
{
return(Json(new { Message = "Success" }));
}
}
public List <ProjectDetails> ReadAllProjectInfo()
{
var projectFile = new LinqToExcel.ExcelQueryFactory(@CSV_FILE_PATH);
List <ProjectDetails> projectList = (from row in projectFile.Worksheet(CSV_SHEET_NAME)
let item = new ProjectDetails()
{
Repository = row["Repository"].Cast <string>(),
Language = row["Language"].Cast <string>(),
URL = row["URL"].Cast <string>(),
FilePath = row["FilePath"].Cast <string>(),
Line = row["Line"].Cast <int>(),
HTTPStatus = row["HTTPStatus"].Cast <string>(),
Domain = row["Domain"].Cast <string>(),
HasURLRevised = row["HasURLRevised"].Cast <string>()
}
select item).ToList();
return(projectList);
}
public IEnumerable <TModel> ParseExcelFile <TModel>(string fileName, ref string errorMsg)
{
var excelFile = new LinqToExcel.ExcelQueryFactory(fileName);
IEnumerable <string> workSheetNames = excelFile.GetWorksheetNames();
IEnumerable <TModel> sheetData = new List <TModel>();
foreach (string sheetName in workSheetNames)
{
List <string> excelHeaders = excelFile.GetColumnNames(sheetName).ToList();
List <string> props = typeof(TModel).GetProperties().Select(x => x.Name).ToList();
bool isSubset = !excelHeaders.Except(props).Any();
if (isSubset)
{
errorMsg = "";
var data = from row in excelFile.Worksheet <TModel>(sheetName)
select row;
sheetData = data.AsEnumerable();
}
}
return(sheetData);
}
public void ProcessExcelFile(string fileName, Action <int> progress)
{
progress(0);
var book = new LinqToExcel.ExcelQueryFactory(fileName);
book.DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Ace;
int rowNumber = 1;
_count = 0;
var tranactionData =
from row in book.Worksheet("Sheet1")
let item = new TransactionData
{
Code = row["Account"].Cast <string>(),
Description = row["Description"].Cast <string>(),
Currency = row["Currency Code"].Cast <string>(),
Amount = row["Amount"].Cast <decimal>(),
}
select item;
var rowsToProcess = tranactionData.Count();
foreach (var datum in tranactionData)
{
datum.RowNumber = rowNumber++;
if (datum.IsValid)
{
_dal.AddTransaction(datum);
_count++;
}
else
{
FailedTransactions.Add(datum);
}
progress((int)((double)datum.RowNumber / rowsToProcess * 100));
}
}
public List <T> ImportData <T>()
where T : class, IbaseProperties, new()
{
List <T> results = new List <T>();
string fName = null;
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "xls文件|*.xls";
DialogResult result = openFileDialog.ShowDialog();
if (result == DialogResult.OK)
{
fName = openFileDialog.FileName;
}
else
{
return(null);
}
var excelfile = new LinqToExcel.ExcelQueryFactory(fName);
var tsheet = excelfile.Worksheet <T>(0);
//var tsheet = excelfile.Worksheet(0);
var query = from t in tsheet select t;
foreach (var q in query)
{
//if (Add(q) > 0)
//{
results.Add(q);
//}
}
return(results);
}
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalogList = new List <ItemCatalog>();
IQueryable <ItemCatalog> _CustomItemCatalogList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <ItemCatalog>(d => d.ClassName, "ClassName");
_Excel.AddMapping <ItemCatalog>(d => d.ItemId, "ItemId");
_Excel.AddMapping <ItemCatalog>(d => d.Name, "Name");
// every time get first sheet data
_CustomItemCatalogList = from x in _Excel.Worksheet <ItemCatalog>(0)
select x;
int _Start = 2;
foreach (ItemCatalog ic in _CustomItemCatalogList)
{
try
{
ItemCatalog _ItemCatalog = new ItemCatalog();
_ItemCatalog.ClassName = ic.ClassName;
_ItemCatalog.ItemId = ic.ItemId;
_ItemCatalog.Name = ic.Name;
_ItemCatalog.IsDel = false;
_ItemCatalogList.Add(_ItemCatalog);
}
catch (Exception ex)
{
string _Ic = JsonConvert.SerializeObject(ic);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _Ic);
}
_Start++;
}
_Budget.ItemCatalog = _ItemCatalogList;
return(_Budget);
}
private void CompararXlsProcessado(object sender, EventArgs e)
{
Filiais temp = new Filiais();
List <Nota> listaNotas;
try
{
OpenFileDialog openFileDialog1 = new OpenFileDialog
{
InitialDirectory = @"C:\",
Title = "Abrir arquivo XLS",
CheckFileExists = true,
CheckPathExists = true,
Multiselect = false,
DefaultExt = "xls",
Filter = "Arquivos Excel (*.xls)|*.xls| Arquivos Excel (*.xlsx)|*.xlsx | Todos (*)|*.*",
FilterIndex = 1,
RestoreDirectory = true,
ReadOnlyChecked = true,
ShowReadOnly = true
};
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
foreach (string name in openFileDialog1.FileNames)
{
listaNotas = new List <Nota>();
baseDiretory = Path.GetDirectoryName(name);
var file = new LinqToExcel.ExcelQueryFactory(@"" + name);
if (file != null)
{
var querry =
from row in file.Worksheet("Sheet")
let item = new
{
dsFantasia = row["dsFantasia"].Cast <String>(),
nmCNPJ = row["nmCNPJ"].Cast <String>(),
nmArquivo = row["nmArquivo"].Cast <String>(),
dtNota = row["dtNota"].Cast <DateTime>(),
descricao = row["descricao"].Cast <String>(),
}
select item;
foreach (var g in querry)
{
if (!g.descricao.Equals("ok"))
{
listaNotas.Add(new Nota
{
dsFantasia = g.dsFantasia,
nmCNPJ = g.nmCNPJ,
nmArquivo = g.nmArquivo,
dtNota = g.dtNota.ToString("yyyyMMdd"),
descricao = "",
vlAtos = 0,
vlContabil = 0,
vlMercadoria = 0
});
}
}
try
{
listaNotas = Nota.notasAgrupadasPorChave(listaNotas, dateTimePicker1.Value);
label2.Text = "Comparando Arquivo: ";
progressBar2.Maximum = listaNotas.Count();
progressBar2.Minimum = 0;
progressBar2.Value = 0;
progressBar2.Step = 1;
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CompararDiaErro(object sender, EventArgs e)
{
Filiais temp = new Filiais();
List <Filiais> filiais = temp.listarFiliais();
List <Nota> listaNotas;
try
{
OpenFileDialog openFileDialog1 = new OpenFileDialog
{
InitialDirectory = @"C:\",
Title = "Abrir arquivo XLS",
CheckFileExists = true,
CheckPathExists = true,
Multiselect = false,
DefaultExt = "xls",
Filter = "Arquivos Excel (*.xls)|*.xls| Arquivos Excel (*.xlsx)|*.xlsx | Todos (*)|*.*",
FilterIndex = 1,
RestoreDirectory = true,
ReadOnlyChecked = true,
ShowReadOnly = true
};
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
foreach (string name in openFileDialog1.FileNames)
{
listaNotas = new List <Nota>();
baseDiretory = Path.GetDirectoryName(name);
string nameArqv = Path.GetFileNameWithoutExtension(name);
nameArqv = RemoveDiacritics(nameArqv);
try
{
temp = null;
temp = filiais.Find(x => x.ds_fantasia.Contains(nameArqv));
if (temp == null)
{
MessageBox.Show("Nome do Arquivo não refere a uma Filial");
break;
}
}
catch (Exception exe)
{
MessageBox.Show(exe.Message);
}
var file = new LinqToExcel.ExcelQueryFactory(@"" + name);
if (file != null)
{
var querry =
from row in file.Worksheet("Sheet")
let item = new
{
cdChave = row["Chave acesso"].Cast <String>(),
vlMercadoria = row["Valor mercadorias"].Cast <float>(),
vlContabil = row["Valor contábil"].Cast <float>(),
dtNota = row["Data do doc#"].Cast <DateTime>(),
}
select item;
foreach (var g in querry)
{
listaNotas.Add(new Nota
{
cdChave = g.cdChave,
dsFantasia = temp.ds_fantasia,
nmCNPJ = temp.nu_cnpj,
nmArquivo = nameArqv,
vlMercadoria = g.vlMercadoria,
vlContabil = g.vlContabil,
dtNota = g.dtNota.ToString("yyyyMMdd"),
descricao = "",
vlAtos = 0
});
}
try
{
listaNotas = Nota.notasAgrupadasPorChave(listaNotas, dateTimePicker1.Value);
label2.Text = "Comparando Arquivo: ";
progressBar2.Maximum = listaNotas.Count();
progressBar2.Minimum = 0;
progressBar2.Value = 0;
progressBar2.Step = 1;
CompararNotas(listaNotas);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnBrowse_Click(object sender, EventArgs e)
{
lstPNRsProcessed = new List<PNRsProcessed>();
DialogResult result = fileDialog.ShowDialog();
if (result == DialogResult.OK) //
{
string file = fileDialog.FileName;
try
{
var sData = new LinqToExcel.ExcelQueryFactory(file);
lstFlightRequest = (from row in sData.Worksheet<FlightRequest>("Sheet1") where !string.IsNullOrEmpty(row.Origin) && !string.IsNullOrEmpty(row.Destination) select row).ToList();
if (lstFlightRequest != null && lstFlightRequest.Count > 0)
{
btnProcessPNRs.Enabled = true;
lblUploadInfo.Text = string.Format("Total {0} record(s) to process", lstFlightRequest.Count);
}
else
{
btnProcessPNRs.Enabled = false;
lblUploadInfo.Text = "There are no record in Excel file. Please check.";
}
}
catch (Exception ex)
{
MessageBox.Show((ex.InnerException != null ? ex.InnerException.Message.ToString() : ex.Message), "Exception: On browse button click", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private static void UpdateBasedOnExcelFile(List <IssueItem> issuesInRedmineProject,
List <StatItem> statItems,
bool allWithinDirectory)
{
//********************************************************************************************************/
//read data from Excel
List <string> filesToProcess = null;
if (allWithinDirectory)
{
filesToProcess = Directory.EnumerateFiles(MOM_FILES_DIR, "*.xlsx").ToList();
}
else
{
filesToProcess = new List <string>();
filesToProcess.Add(MOM_FILE_PATH);
}
foreach (string singleXSLXfile in filesToProcess)
{
var xlsx = new LinqToExcel.ExcelQueryFactory(singleXSLXfile);
output.WriteLine("File: {0}", singleXSLXfile);
foreach (string tabName in xlsx.GetWorksheetNames())
{
output.WriteLine("--------------------------------------------");
output.WriteLine("Processing of {0}...", tabName);
output.WriteLine("--------------------------------------------");
StatItem statItem = new StatItem(tabName);
statItem.Env = tabName;
var query =
from row in xlsx.Worksheet(tabName)
let item = new
{
ProblemID = row["Problem ID"].Cast <string>(),
ProblemCode = row["Problem Code"].Cast <string>(),
MessageId = row["Message ID"].Cast <string>(),
EventCode = row["Event Code"].Cast <string>(),
Details = row["Details"].Cast <string>(),
SenderCode = row["Sender Code"].Cast <string>(),
}
select item;
IdentifiableName p = IdentifiableName.Create <Project>(Consts.PROJECT_NAMES.MOM.PROBLEMS);
foreach (var itemFromExcel in query)
{
//look for the item in RM
var redmineIssue = issuesInRedmineProject.Where(issueFromRedmine => issueFromRedmine.Env == tabName && issueFromRedmine.ProblemId == itemFromExcel.ProblemID).FirstOrDefault();
if (redmineIssue != null && string.IsNullOrEmpty(redmineIssue.SenderCode))
{
if (!string.IsNullOrEmpty(itemFromExcel.SenderCode))
{
var issue = RMManegerService.RMManager.GetObject <Issue>(redmineIssue.Id.ToString(), null);
issue.Subject = issue.Subject + string.Format(" - {0}", itemFromExcel.SenderCode);
RMManegerService.RMManager.UpdateObject(redmineIssue.Id.ToString(), issue);
redmineIssue.SenderCode = itemFromExcel.SenderCode;
statItem.Updated++;
// string subject = redmineIssue.sub
//string subject = string.Format("{0} - {1} - {2} - {3} - {4}", tabName, itemFromExcel.ProblemID, itemFromExcel.EventCode, itemFromExcel.ProblemCode, itemFromExcel.SenderCode);
}
else
{
statItem.NotUpdated++;
}
}
else
{
statItem.NotUpdated++;
}
}
statItems.Add(statItem);
}
}
}
/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <Budget_DeptKPI> _Budget_DeptKPIList = new List <Budget_DeptKPI>();
IQueryable <Budget_DeptKPI> _DeptKPIList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Budget_DeptKPI>(d => d.GoalName, " GoalName");
_Excel.AddMapping <Budget_DeptKPI>(d => d.PIC_Name, "PIC_Name");
_Excel.AddMapping <Budget_DeptKPI>(d => d.LastYear, "LastYear");
_Excel.AddMapping <Budget_DeptKPI>(d => d.YTD, "YTD");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Jan, "Jan");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Feb, "Feb");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Mar, "Mar");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Apr, "Apr");
_Excel.AddMapping <Budget_DeptKPI>(d => d.May, "May");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Jun, "Jun");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Jul, "Jul");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Aug, "Aug");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Sep, "Sep");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Oct, "Oct");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Nov, "Nov");
_Excel.AddMapping <Budget_DeptKPI>(d => d.Dec, "Dec");
// every time get first sheet data
_DeptKPIList = from x in _Excel.Worksheet <Budget_DeptKPI>("Upload")
select x;
int _Start = 2;
foreach (Budget_DeptKPI c in _DeptKPIList)
{
if (_Start == 2 || c.GoalName == null)
{
_Start++;
continue;
}
try
{
Budget_DeptKPI _Budget_DeptKPI = new Budget_DeptKPI();
_Budget_DeptKPI.GoalName = c.GoalName;
_Budget_DeptKPI.PIC_Name = c.PIC_Name;
_Budget_DeptKPI.LastYear = c.LastYear;
_Budget_DeptKPI.YTD = c.YTD;
_Budget_DeptKPI.Jan = c.Jan;
_Budget_DeptKPI.Feb = c.Feb;
_Budget_DeptKPI.Mar = c.Mar;
_Budget_DeptKPI.Apr = c.Apr;
_Budget_DeptKPI.May = c.May;
_Budget_DeptKPI.Jun = c.Jun;
_Budget_DeptKPI.Jul = c.Jul;
_Budget_DeptKPI.Aug = c.Aug;
_Budget_DeptKPI.Sep = c.Sep;
_Budget_DeptKPI.Oct = c.Oct;
_Budget_DeptKPI.Nov = c.Nov;
_Budget_DeptKPI.Dec = c.Dec;
_Budget_DeptKPI.Date = (DateTime.Now.Year + 1).ToString();
_Budget_DeptKPI.DepartmentId = base.DepartmentId;
_Budget_DeptKPI.Version = base.Version;
_Budget_DeptKPI.Factory = base.Factory;
_Budget_DeptKPIList.Add(_Budget_DeptKPI);
}
catch (Exception ex)
{
string _C = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _C);
}
_Start++;
}
_Budget.DeptKPI = _Budget_DeptKPIList;
return(_Budget);
}
/* 1.上傳檔案
* 2.抓出資料/刪除檔案
* 3.匯入資料到sql server
*
*
*
* */
public string import(HttpPostedFileBase fileuploadExcel, HttpServerUtilityBase Server)
{
//上傳檔案
string filename = "";
string fullFilename = "";
if (fileuploadExcel != null && fileuploadExcel.ContentLength > 0)
{
filename = Path.GetFileName(fileuploadExcel.FileName);
string picExten = System.IO.Path.GetExtension(filename);
filename = DateTime.Now.ToString("yyyyMMddhhmmss") + picExten;
fullFilename = Path.Combine(Server.MapPath("~/Content/uploads"), filename);
fileuploadExcel.SaveAs(fullFilename);
}
//抓出Excel資料
var sData = new LinqToExcel.ExcelQueryFactory(fullFilename);
var rows = (from row in sData.Worksheet("Sheet1") select row).ToList();
DataTable dt = new DataTable();
//
dt.Columns.Add("DateX");
dt.Columns.Add("dat");
dt.Columns.Add("lonX");
dt.Columns.Add("chll");
dt.Columns.Add("sa");
dt.Columns.Add("cerrentX");
dt.Columns.Add("cerrentY");
dt.Columns.Add("waveh");
dt.Columns.Add("tide");
dt.Columns.Add("uv");
foreach (var item in rows)
{
DataRow dr = dt.NewRow();
dr["DateX"] = item[0];
dr["dat"] = item[1];
dr["lonX"] = item[2];
dr["chll"] = item[3];
dr["sa"] = item[4];
dr["cerrentX"] = item[5];
dr["cerrentY"] = item[6];
dr["waveh"] = item[7];
dr["tide"] = item[8];
dr["uv"] = item[9];
dt.Rows.Add(dr);
}
//匯入資料到暫存table
string BStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
try
{
SqlConnection Bulkcn = new SqlConnection(BStr);//SqlBulkCopy裡面就只能放SqlConnection，不能放別的像是OleDbConnection
SqlBulkCopy SBC = new SqlBulkCopy(Bulkcn);
//複製到目的地的哪個資料表
SBC.DestinationTableName = "SeaData";
//設定你要複製過去的DataTable的每個欄位要對應到目的地的哪個欄位
//SBC.ColumnMappings.Add("DataTable的欄位A", "資料庫裡的資料表的的欄位A");
SBC.ColumnMappings.Add("DateX", "DateX");
SBC.ColumnMappings.Add("dat", "dat");
SBC.ColumnMappings.Add("lonX", "lonX");
SBC.ColumnMappings.Add("chll", "chll");
SBC.ColumnMappings.Add("sa", "sa");
SBC.ColumnMappings.Add("cerrentX", "cerrentX");
SBC.ColumnMappings.Add("cerrentY", "cerrentY");
SBC.ColumnMappings.Add("waveh", "waveh");
SBC.ColumnMappings.Add("tide", "tide");
SBC.ColumnMappings.Add("uv", "uv");
Bulkcn.Open();
//假設DT1是已經有資料的DataTable，直接放進去就可以開始寫入了
SBC.WriteToServer(dt);
SBC.Close();
Bulkcn.Close();
}
catch (Exception ex)
{
return("資料上傳異常1" + ex.Message);
}
////從暫存table轉資料到正式資料表，會過濾重覆的資料
//CodeDao dao = new CodeDao();
//string transresult = dao.transData();
//if (!transresult.Equals("OK"))
//{
// return "資料上傳異常2";
//}
////刪除匯入的資料
//dao.clearnTmpData();
return("上傳完成");
}
/// <summary>
/// if import part time hr excel need get factor departmentId column
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_HeadCount> _Budget_HeadCountList = new List <Budget_HeadCount>();
IQueryable <Headcount> _HeadcountList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Headcount>(d => d.AltwName, "AltwName");
_Excel.AddMapping <Headcount>(d => d.JobFunction, "JobFunction");
_Excel.AddMapping <Headcount>(d => d.Title, "Title");
_Excel.AddMapping <Headcount>(d => d.DirectType, "DirectType");
_Excel.AddMapping <Headcount>(d => d.Current, "Current");
_Excel.AddMapping <Headcount>(d => d.JulLast, "JulLast");
_Excel.AddMapping <Headcount>(d => d.AugLast, "AugLast");
_Excel.AddMapping <Headcount>(d => d.SepLast, "SepLast");
_Excel.AddMapping <Headcount>(d => d.OctLast, "OctLast");
_Excel.AddMapping <Headcount>(d => d.NovLast, "NovLast");
_Excel.AddMapping <Headcount>(d => d.DecLast, "DecLast");
_Excel.AddMapping <Headcount>(d => d.Jan, "Jan");
_Excel.AddMapping <Headcount>(d => d.Feb, "Feb");
_Excel.AddMapping <Headcount>(d => d.Mar, "Mar");
_Excel.AddMapping <Headcount>(d => d.Apr, "Apr");
_Excel.AddMapping <Headcount>(d => d.May, "May");
_Excel.AddMapping <Headcount>(d => d.Jun, "Jun");
_Excel.AddMapping <Headcount>(d => d.Jul, "Jul");
_Excel.AddMapping <Headcount>(d => d.Aug, "Aug");
_Excel.AddMapping <Headcount>(d => d.Sep, "Sep");
_Excel.AddMapping <Headcount>(d => d.Oct, "Oct");
_Excel.AddMapping <Headcount>(d => d.Nov, "Nov");
_Excel.AddMapping <Headcount>(d => d.Dec, "Dec");
_Excel.AddMapping <Headcount>(d => d.Reason, "Reason");
_Excel.AddMapping <Headcount>(d => d.Remark, "Remark");
if (base.PartTime)
{
_Excel.AddMapping <DeptExpense>(d => d.Factory, "Factory");
_Excel.AddMapping <DeptExpense>(d => d.DepartmentId, "DepartmentId");
}
// every time get first sheet data
_HeadcountList = from x in _Excel.Worksheet <Headcount>(0)
select x;
int _Start = 2;
foreach (Headcount h in _HeadcountList)
{
if (_Start == 2 || _Start == 3 || h.AltwName == null && !base.PartTime)
{
_Start++;
continue;
}
try
{
Budget_HeadCount _Budget_HeadCount = new Budget_HeadCount();
string _FactoryType = base.Factory == "LT" ? "L" : "K";
string _NewEmployee = _FactoryType + Guid.NewGuid().ToString("N").Substring(0, 18);
_Budget_HeadCount.Account = this._budget_HeadCountHRService.GetAll().Where(x => x.AltwName == h.AltwName).ToList().Count > 0 ? this._budget_HeadCountHRService.GetAll().Where(x => x.AltwName == h.AltwName).First().Account : _NewEmployee;
_Budget_HeadCount.JulLast = h.JulLast == "" ? 0 : h.JulLast == "(1)" ? -1 : Convert.ToInt32(h.JulLast);
_Budget_HeadCount.AugLast = h.AugLast == "" ? 0 : h.AugLast == "(1)" ? -1 : Convert.ToInt32(h.AugLast);
_Budget_HeadCount.SepLast = h.SepLast == "" ? 0 : h.SepLast == "(1)" ? -1 : Convert.ToInt32(h.SepLast);
_Budget_HeadCount.OctLast = h.OctLast == "" ? 0 : h.OctLast == "(1)" ? -1 : Convert.ToInt32(h.OctLast);
_Budget_HeadCount.NovLast = h.NovLast == "" ? 0 : h.NovLast == "(1)" ? -1 : Convert.ToInt32(h.NovLast);
_Budget_HeadCount.DecLast = h.DecLast == "" ? 0 : h.DecLast == "(1)" ? -1 : Convert.ToInt32(h.DecLast);
_Budget_HeadCount.Jan = h.Jan == "" ? 0 : h.Jan == "(1)" ? -1 : Convert.ToInt32(h.Jan);
_Budget_HeadCount.Feb = h.Feb == "" ? 0 : h.Feb == "(1)" ? -1 : Convert.ToInt32(h.Feb);
_Budget_HeadCount.Mar = h.Mar == "" ? 0 : h.Mar == "(1)" ? -1 : Convert.ToInt32(h.Mar);
_Budget_HeadCount.Apr = h.Apr == "" ? 0 : h.Apr == "(1)" ? -1 : Convert.ToInt32(h.Apr);
_Budget_HeadCount.May = h.May == "" ? 0 : h.May == "(1)" ? -1 : Convert.ToInt32(h.May);
_Budget_HeadCount.Jun = h.Jun == "" ? 0 : h.Jun == "(1)" ? -1 : Convert.ToInt32(h.Jun);
_Budget_HeadCount.Jul = h.Jul == "" ? 0 : h.Jul == "(1)" ? -1 : Convert.ToInt32(h.Jul);
_Budget_HeadCount.Aug = h.Aug == "" ? 0 : h.Aug == "(1)" ? -1 : Convert.ToInt32(h.Aug);
_Budget_HeadCount.Sep = h.Sep == "" ? 0 : h.Sep == "(1)" ? -1 : Convert.ToInt32(h.Sep);
_Budget_HeadCount.Oct = h.Oct == "" ? 0 : h.Oct == "(1)" ? -1 : Convert.ToInt32(h.Oct);
_Budget_HeadCount.Nov = h.Nov == "" ? 0 : h.Nov == "(1)" ? -1 : Convert.ToInt32(h.Nov);
_Budget_HeadCount.Dec = h.Dec == "" ? 0 : h.Dec == "(1)" ? -1 : Convert.ToInt32(h.Dec);
_Budget_HeadCount.Date = (DateTime.Now.Year + 1).ToString();
_Budget_HeadCount.Version = base.Version;
_Budget_HeadCount.DepartmentId = base.PartTime ? h.DepartmentId : base.DepartmentId;
_Budget_HeadCount.Factory = base.PartTime ? h.Factory : base.Factory;
_Budget_HeadCount.PartTime = base.PartTime;
_Budget_HeadCount.CurrentHC = h.Current == "1" ? true : false;
string _ItemId_Reason = "";
if (!base.PartTime)
{
if (h.Reason != "Pls Choose 請選擇")
{
_ItemId_Reason = _ItemCatalog.Where(x => x.ClassName == "Reason" && x.Name.Trim() == h.Reason.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "Reason" && x.Name.Trim() == h.Reason.Trim()).First().ItemId : "";
}
_Budget_HeadCount.ItemId_Reason = _ItemId_Reason;
}
_Budget_HeadCount.Remark = h.Remark;
_Budget_HeadCountList.Add(_Budget_HeadCount);
if (h.Current == null && !base.PartTime)
{
insertHeadCountHR(h, _ItemCatalog, _NewEmployee);
}
}
catch (Exception ex)
{
string _h = JsonConvert.SerializeObject(h);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _h);
}
_Start++;
}
_Budget.HeadCount = _Budget_HeadCountList;
return(_Budget);
}
public Batch Parse()
{
var fileInfo = new FileInfo(FilePath);
if (!fileInfo.Exists)
{
throw new FileNotFoundException(string.Format("The file '{0}' does not exist", FilePath));
}
// The file represents the batch. The name of the batch is the name of the file.
var batch = new Batch();
batch.Name = DetermineFileName(fileInfo);
batch.SubBatches = new List<SubBatch>();
try
{
using (var factory = new LinqToExcel.ExcelQueryFactory(FilePath))
{
// The work sheets are the sub batches.
var subBatchNames = factory.GetWorksheetNames();
foreach (var subBatchName in subBatchNames)
{
// Loop the sub batches.
var subBatch = new SubBatch
{
Code = subBatchName
};
var subBatchRows = factory.Worksheet(subBatchName);
// Read the rows. The rows are matched using the row number.
subBatch.SimCards = subBatchRows.Select(row => new SIMCard
{
TrackingCode = row[0].Value.ToString(),
SubBatch = subBatch,
DateCaptured = DateTime.Parse(row[3].Value.ToString()),
CapturedBy = row[4].Value.ToString(),
Network = new Network
{
Name = row[5].Value.ToString()
},
Package = new Package
{
Name = row[6].Value.ToString()
},
ICCID = row[7].Value.ToString(),
IMSI = row[8].Value.ToString(),
MSISDN = row[9].Value.ToString(),
Group = new Group
{
Name = row[10].Value.ToString()
}
}).ToList();
batch.SubBatches.Add(subBatch);
}
}
}
catch (Exception)
{
// perhaps some cleaning up?
throw;
}
return batch;
}
private static void ProcessExcelFile(List <IssueItem> issuesInRedmineProject, List <StatItem> statItems, List <string> envsNotExistingInConfigs)
{
//********************************************************************************************************/
//read data from Excel
var xlsx = new LinqToExcel.ExcelQueryFactory(MOM_FILE_PATH);
foreach (string tabName in xlsx.GetWorksheetNames())
{
output.WriteLine("--------------------------------------------");
output.WriteLine("Processing of {0}...", tabName);
output.WriteLine("--------------------------------------------");
MOMEnvSettings momEnvSettings = null;
if (!MOM_ENV_SETTINGS.TryGetValue(tabName, out momEnvSettings))
{
output.WriteLine("No MOMEnvSettings for {0}", tabName);
envsNotExistingInConfigs.Add(tabName);
//output.ReadKey();
}
else
{
output.WriteLine("Start processing: {0}", tabName);
StatItem statItem = new StatItem();
statItem.Env = tabName;
var query =
from row in xlsx.Worksheet(tabName)
let item = new
{
ProblemID = row["Problem ID"].Cast <string>(),
ProblemCode = row["Problem Code"].Cast <string>(),
MessageId = row["Message ID"].Cast <string>(),
EventCode = row["Event Code"].Cast <string>(),
Details = row["Details"].Cast <string>(),
SenderCode = row["Sender Code"].Cast <string>(),
}
select item;
IdentifiableName p = IdentifiableName.Create <Project>(Consts.PROJECT_NAMES.MOM.PROBLEMS);
foreach (var itemFromExcel in query)
{
string subject = string.Format("{0} - {1} - {2} - {3} - {4}", tabName, itemFromExcel.ProblemID, itemFromExcel.EventCode, itemFromExcel.ProblemCode, itemFromExcel.SenderCode);
//check if such the item exists in the Redmine project
var redmineIssue = issuesInRedmineProject.Where(issueFromRedmine => issueFromRedmine.Env == tabName && issueFromRedmine.ProblemId == itemFromExcel.ProblemID);
if (redmineIssue.Count() == 0)
{
string details = string.Format("{0}\r\nMessage link: {1}\r\nProblem link: {2}", itemFromExcel.Details, momEnvSettings.GetMessageLink(itemFromExcel.MessageId), momEnvSettings.GetProblemLink(itemFromExcel.MessageId));
var newIssue = new Issue {
Subject = subject, Project = p, Description = details
};
RMManegerService.RMManager.CreateObject(newIssue);
//add a new item to local cached items from redmine
IssueItem item = new IssueItem();
item.Env = tabName;
item.ProblemId = itemFromExcel.ProblemID;
issuesInRedmineProject.Add(item);
statItem.Added++;
}
else
{
output.WriteLine("Issue exists! {0}", subject);
statItem.AlreadyExisted++;
}
}
statItems.Add(statItem);
}
}
}
/// <summary>
/// p.s mapping name maybe need to change
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public override Budget Parse(string filePath)
{
Budget _Budget = new Budget();
List <ItemCatalog> _ItemCatalog = this._itemCatalogService.GetAll().ToList();
List <Budget_Traveling> _Budget_TravelingList = new List <Budget_Traveling>();
IQueryable <Traveling> _TravelingList;
LinqToExcel.ExcelQueryFactory _Excel = new LinqToExcel.ExcelQueryFactory(filePath);
_Excel.AddMapping <Traveling>(d => d.AltwName, "AltwName");
_Excel.AddMapping <Traveling>(d => d.Country, "Country");
_Excel.AddMapping <Traveling>(d => d.Days, "Days");
_Excel.AddMapping <Traveling>(d => d.Purpose, "Purpose");
_Excel.AddMapping <Traveling>(d => d.Type, "Type");
_Excel.AddMapping <Traveling>(d => d.Remark, "Remark");
_Excel.AddMapping <Traveling>(d => d.Jan, "Jan");
_Excel.AddMapping <Traveling>(d => d.Feb, "Feb");
_Excel.AddMapping <Traveling>(d => d.Mar, "Mar");
_Excel.AddMapping <Traveling>(d => d.Apr, "Apr");
_Excel.AddMapping <Traveling>(d => d.May, "May");
_Excel.AddMapping <Traveling>(d => d.Jun, "Jun");
_Excel.AddMapping <Traveling>(d => d.Jul, "Jul");
_Excel.AddMapping <Traveling>(d => d.Aug, "Aug");
_Excel.AddMapping <Traveling>(d => d.Sep, "Sep");
_Excel.AddMapping <Traveling>(d => d.Oct, "Oct");
_Excel.AddMapping <Traveling>(d => d.Nov, "Nov");
_Excel.AddMapping <Traveling>(d => d.Dec, "Dec");
// every time get first sheet data
_TravelingList = from x in _Excel.Worksheet <Traveling>(0)
select x;
int _Start = 2;
foreach (Traveling c in _TravelingList)
{
if (_Start == 2 || _Start == 3 || c.AltwName == null)
{
_Start++;
continue;
}
try
{
Budget_Traveling _Budget_Traveling = new Budget_Traveling();
_Budget_Traveling.ItemId_TravelingPurpose = _ItemCatalog.Where(x => x.ClassName == "TravelingPurpose" && x.Name.Trim() == c.Purpose.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "TravelingPurpose" && x.Name.Trim() == c.Purpose.Trim()).First().ItemId : "";
// _Budget_Traveling.Account = this._budget_HeadCountHRService.GetAll().Where(x => x.AltwName == c.AltwName).First().Account;
_Budget_Traveling.AltwName = c.AltwName;
_Budget_Traveling.Country = c.Country;
_Budget_Traveling.Days = c.Days == null ? 0 : int.Parse(c.Days, System.Globalization.NumberStyles.AllowThousands);
_Budget_Traveling.ItemId_TravelingType = _ItemCatalog.Where(x => x.ClassName == "TravelingType" && x.Name.Trim() == c.Type.Trim()) != null?_ItemCatalog.Where(x => x.ClassName == "TravelingType" && x.Name.Trim() == c.Type.Trim()).First().ItemId : "";
_Budget_Traveling.Remark = c.Remark;
_Budget_Traveling.Jan = this._commonFileService.GetExcelMinusNumber(c.Jan);
_Budget_Traveling.Feb = this._commonFileService.GetExcelMinusNumber(c.Feb);
_Budget_Traveling.Mar = this._commonFileService.GetExcelMinusNumber(c.Mar);
_Budget_Traveling.Apr = this._commonFileService.GetExcelMinusNumber(c.Apr);
_Budget_Traveling.May = this._commonFileService.GetExcelMinusNumber(c.May);
_Budget_Traveling.Jun = this._commonFileService.GetExcelMinusNumber(c.Jun);
_Budget_Traveling.Jul = this._commonFileService.GetExcelMinusNumber(c.Jul);
_Budget_Traveling.Aug = this._commonFileService.GetExcelMinusNumber(c.Aug);
_Budget_Traveling.Sep = this._commonFileService.GetExcelMinusNumber(c.Sep);
_Budget_Traveling.Oct = this._commonFileService.GetExcelMinusNumber(c.Oct);
_Budget_Traveling.Nov = this._commonFileService.GetExcelMinusNumber(c.Nov);
_Budget_Traveling.Dec = this._commonFileService.GetExcelMinusNumber(c.Dec);
_Budget_Traveling.Date = (DateTime.Now.Year + 1).ToString();
_Budget_Traveling.DepartmentId = base.DepartmentId;
_Budget_Traveling.Version = base.Version;
_Budget_Traveling.Factory = base.Factory;
_Budget_TravelingList.Add(_Budget_Traveling);
}
catch (Exception ex)
{
string _c = JsonConvert.SerializeObject(c);
this.ListError.Add(" row : " + _Start.ToString() + " , row data has error format:" + ex.Message + "\r\n data:" + _c);
}
_Start++;
}
if (_Budget_TravelingList.Count > 0)
{
_Budget.DeptExpense = setDeptExpense(_Budget_TravelingList);
}
_Budget.Traveling = _Budget_TravelingList;
return(_Budget);
}
private string ProccessProviderFile(string FilePath, string ErrorFilePath, string CustomerPublicId)
{
//get excel rows
LinqToExcel.ExcelQueryFactory XlsInfo = new LinqToExcel.ExcelQueryFactory(FilePath);
List <ExcelProviderModel> oPrvToProcess =
(from x in XlsInfo.Worksheet <ExcelProviderModel>(0)
select x).ToList();
List <ExcelProviderResultModel> oPrvToProcessResult = new List <ExcelProviderResultModel>();
//process Provider
oPrvToProcess.Where(prv => !string.IsNullOrEmpty(prv.NumeroIdentificacion)).All(prv =>
{
try
{
#region Operation
//Validar el provider
ProviderModel Provider = new ProviderModel();
ProviderModel oResultValidate = new ProviderModel();
ProviderModel oInfoValidate = new ProviderModel();
oResultValidate = DocumentManagement.Provider.Controller.Provider.ProviderGetByIdentification(prv.NumeroIdentificacion, Convert.ToInt32(prv.TipoIdentificacion), CustomerPublicId);
//Create ProviderCustomerInfo
List <ProviderInfoModel> ListCustomerProviderInfo = new List <ProviderInfoModel>();
ProviderInfoModel CustomerProviderInfo = new ProviderInfoModel();
if (oResultValidate == null)
{
CustomerProviderInfo.ProviderInfoType = new CatalogModel()
{
ItemId = 401
};
CustomerProviderInfo.Value = "201";
ListCustomerProviderInfo.Add(CustomerProviderInfo);
}
CustomerProviderInfo = new ProviderInfoModel();
CustomerProviderInfo.ProviderInfoId = oResultValidate != null ?
oResultValidate.RelatedProviderCustomerInfo.Where(x => x.ProviderInfoType.ItemId == 378).
Select(x => x.ProviderInfoId).FirstOrDefault() : 0;
CustomerProviderInfo.ProviderInfoType = new CatalogModel()
{
ItemId = 378
};
CustomerProviderInfo.Value = prv.DigitoVerificacion;
ListCustomerProviderInfo.Add(CustomerProviderInfo);
//Create Provider
ProviderModel ProviderToCreate = new ProviderModel()
{
CustomerPublicId = CustomerPublicId,
Name = prv.RazonSocial,
IdentificationType = new Provider.Models.Util.CatalogModel()
{
ItemId = Convert.ToInt32(prv.TipoIdentificacion)
},
IdentificationNumber = prv.NumeroIdentificacion,
Email = prv.Email,
RelatedProviderCustomerInfo = ListCustomerProviderInfo
};
if (oResultValidate == null)
{
DocumentManagement.Provider.Controller.Provider.ProviderUpsert(ProviderToCreate);
ProviderToCreate.CustomerPublicId = DocumentManagement.Models.General.InternalSettings.Instance[DocumentManagement.Models.General.Constants.C_Settings_PublicId_Publicar].Value;
ListCustomerProviderInfo.All(x =>
{
x.ProviderInfoId = 0;
return(true);
});
DocumentManagement.Provider.Controller.Provider.ProviderCustomerInfoUpsert(ProviderToCreate);
}
else
{
ProviderToCreate.ProviderPublicId = oResultValidate.ProviderPublicId;
DocumentManagement.Provider.Controller.Provider.ProviderCustomerInfoUpsert(ProviderToCreate);
}
oPrvToProcessResult.Add(new ExcelProviderResultModel()
{
PrvModel = prv,
Success = true,
Error = "Se ha creado el Proveedor '" + ProviderToCreate.ProviderPublicId + "'",
});
#endregion
}
catch (Exception err)
{
oPrvToProcessResult.Add(new ExcelProviderResultModel()
{
PrvModel = prv,
Success = false,
Error = "Error :: " + err.Message + " :: " +
err.StackTrace +
(err.InnerException == null ? string.Empty :
" :: " + err.InnerException.Message + " :: " +
err.InnerException.StackTrace),
});
}
return(true);
});
//save log file
#region Error log file
try
{
using (System.IO.StreamWriter sw = new System.IO.StreamWriter(ErrorFilePath))
{
string strSep = ";";
sw.WriteLine
("\"RazonSocial\"" + strSep +
"\"TipoIdentificacion\"" + strSep +
"\"NumeroIdentificaion\"" + strSep +
"\"DigitoVerificacion\"" + strSep +
"\"Email\"" + strSep +
"\"CampanaSalesforce\"" + strSep +
"\"PublicIdPublicar\"" + DocumentManagement.Models.General.Constants.C_Settings_PublicId_Publicar.ToString() +
"\"Success\"" + strSep +
"\"Error\"");
oPrvToProcessResult.All(lg =>
{
sw.WriteLine
("\"" + lg.PrvModel.RazonSocial + "\"" + strSep +
"\"" + lg.PrvModel.TipoIdentificacion + "\"" + strSep +
"\"" + lg.PrvModel.NumeroIdentificacion + "\"" + strSep +
"\"" + lg.PrvModel.DigitoVerificacion + "\"" + strSep +
"\"" + lg.PrvModel.Email + "\"" + strSep +
"\"" + lg.PrvModel.CampanaSalesforce + "\"" + strSep +
"\"" + lg.Success + "\"" + strSep +
"\"" + lg.Error + "\"");
return(true);
});
sw.Flush();
sw.Close();
}
//load file to s3
string strRemoteFile = ProveedoresOnLine.FileManager.FileController.LoadFile
(ErrorFilePath,
DocumentManagement.Models.General.InternalSettings.Instance[DocumentManagement.Models.General.Constants.C_Settings_File_ExcelDirectory].Value);
//remove temporal file
if (System.IO.File.Exists(ErrorFilePath))
{
System.IO.File.Delete(ErrorFilePath);
}
return(strRemoteFile);
}
catch { }
return(null);
#endregion
}
private void lbLists_MouseDoubleClick(object sender, MouseEventArgs e)
{
var sList = Lists.Where(x => x.Title == lbLists.SelectedItem.ToString());
//Get view
//Views.Clear();
List list = clientContext.Web.Lists.GetByTitle(sList.First().Title.ToString());
//ViewCollection viewColl = list.Views;
//clientContext.Load(viewColl,
// views => views.Include(
// view => view.Title,
// view => view.Id));
//clientContext.ExecuteQuery();
//foreach (SP.View view in viewColl)
//{
// lbViews.Items.Add(view.Title);
// Views.Add(new AllView { Name = view.Title, Id = view.Id });
//}
SP.List oList = clientContext.Web.Lists.GetByTitle(sList.First().Title.ToString());
var camlQuery = new CamlQuery {
ViewXml = "<View><RowLimit>100000</RowLimit></View>"
};
ListItemCollection collListItem = oList.GetItems(camlQuery);
clientContext.Load(collListItem,
items => items.Include(
item => item.Id,
item => item.DisplayName,
item => item.HasUniqueRoleAssignments));
clientContext.ExecuteQuery();
// MessageBox.Show("In list "+sList.First().Id.ToString()+Environment.NewLine+"Found items: "+ collListItem.Count);
DialogResult dialogResult = MessageBox.Show("Really delete " + collListItem.Count + " items?", "Deleting items list " + sList.First().Title.ToString(), MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
//Delete items
CamlQuery q = new CamlQuery();
q.ViewXml = "<View><RowLimit>10000</RowLimit></View>";
// We get the results
SP.ListItemCollection coll = list.GetItems(q);
clientContext.Load(coll);
clientContext.ExecuteQuery();
if (coll.Count > 0)
{
for (int i = coll.Count - 1; i >= 0; i--)
{
coll[i].DeleteObject();
progressBar1.Value = (int)(i / collListItem.Count) * 100;
int percent = (int)(((double)progressBar1.Value / (double)progressBar1.Maximum) * 100);
progressBar1.Refresh();
progressBar1.CreateGraphics().DrawString(percent.ToString() + "%",
new Font("Arial", (float)8.25, FontStyle.Regular),
Brushes.Black,
new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
}
clientContext.ExecuteQuery();
}
MessageBox.Show("Done!");
progressBar1.Value = 0;
}
else if (dialogResult == DialogResult.No)
{
//do something else
DialogResult dialogResult2 = MessageBox.Show("Import rows?", "Import from Excel in list " + sList.First().Title.ToString(), MessageBoxButtons.YesNo);
if (dialogResult2 == DialogResult.Yes)
{
// Create an instance of the open file dialog box.
OpenFileDialog openFileDialog1 = new OpenFileDialog();
// Set filter options and filter index.
openFileDialog1.Filter = "Excel File (.xlsx)|*.xlsx";
openFileDialog1.FilterIndex = 1;
openFileDialog1.Multiselect = false;
// Call the ShowDialog method to show the dialog box.
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
var bookFileNULL = new LinqToExcel.ExcelQueryFactory(openFileDialog1.FileName);
List <AllItem> PoliciesNULL =
(from row in bookFileNULL.Worksheet("Table1")
let item = new AllItem
{
Name = row["Title"].Cast <string>()
}
// where item.Supplier == "Walmart"
select item).ToList();
MessageBox.Show("Found items for import: " + PoliciesNULL.Count());
//Add items
int i = 0;
foreach (AllItem pol in PoliciesNULL)
{
i++;
AddItems(sList.First().Title.ToString(), Views.First().Id.ToString(), pol);
progressBar1.Value = (int)(i / PoliciesNULL.Count) * 100;
int percent = (int)(((double)progressBar1.Value / (double)progressBar1.Maximum) * 100);
progressBar1.Refresh();
progressBar1.CreateGraphics().DrawString(percent.ToString() + "%",
new Font("Arial", (float)8.25, FontStyle.Regular),
Brushes.Black,
new PointF(progressBar1.Width / 2 - 10, progressBar1.Height / 2 - 7));
}
MessageBox.Show("Done!");
progressBar1.Value = 0;
}
catch (Exception ex)
{
MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
}
}
}
else if (dialogResult2 == DialogResult.No)
{
//do something else
}
}
}
