void Access_Excel(ITestCaseCollection testcases)
{
try
{
FileInfo new_file = new FileInfo(File_Name.Text);
// FileInfo template = new FileInfo(System.Windows.Forms.Application.StartupPath + "\\Resources\\Test_Case_Template.xlsx");
FileInfo template = new FileInfo(Directory.GetCurrentDirectory() + "\\Test_Case_Template.xlsx");
using (ExcelPackage xlpackage = new ExcelPackage(new_file,template))
{
ExcelWorksheet worksheet = xlpackage.Workbook.Worksheets["Test Case"];
GetTestCases(testcases, worksheet);
xlpackage.Save();
MessageBox.Show("File has been saved at " + File_Name.Text);
}
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
internal ExcelVmlDrawingCommentCollection(ExcelPackage pck, ExcelWorksheet ws, Uri uri) :
base(pck, ws,uri)
{
if (uri == null)
{
VmlDrawingXml.LoadXml(CreateVmlDrawings());
_drawings = new RangeCollection(new List<IRangeID>());
}
else
{
AddDrawingsFromXml(ws);
}
}
internal ExcelVmlDrawingPictureCollection(ExcelPackage pck, ExcelWorksheet ws, Uri uri) :
base(pck, ws, uri)
{
_pck = pck;
_ws = ws;
if (uri == null)
{
VmlDrawingXml.LoadXml(CreateVmlDrawings());
_images = new List<ExcelVmlDrawingPicture>();
}
else
{
AddDrawingsFromXml();
}
}
public ExcelDrawings(ExcelPackage xlPackage, ExcelWorksheet sheet)
{
_drawingsXml = new XmlDocument();
_drawingsXml.PreserveWhitespace = true;
_drawings = new Dictionary<int, ExcelDrawing>();
XmlNode node = sheet.WorksheetXml.SelectSingleNode("//d:drawing", sheet.NameSpaceManager);
CreateNSM();
if (node != null)
{
PackageRelationship drawingRelation = sheet.Part.GetRelationship(node.Attributes["r:id"].Value);
_uriDrawing = PackUriHelper.ResolvePartUri(sheet.WorksheetUri, drawingRelation.TargetUri);
_part = xlPackage.Package.GetPart(_uriDrawing);
_drawingsXml.Load(_part.GetStream());
AddDrawings();
}
}
internal ExcelVmlDrawingBaseCollection(ExcelPackage pck, ExcelWorksheet ws, Uri uri)
{
VmlDrawingXml = new XmlDocument();
VmlDrawingXml.PreserveWhitespace = false;
NameTable nt=new NameTable();
NameSpaceManager = new XmlNamespaceManager(nt);
NameSpaceManager.AddNamespace("v", ExcelPackage.schemaMicrosoftVml);
NameSpaceManager.AddNamespace("o", ExcelPackage.schemaMicrosoftOffice);
NameSpaceManager.AddNamespace("x", ExcelPackage.schemaMicrosoftExcel);
Uri = uri;
if (uri == null)
{
Part = null;
}
else
{
Part=pck.Package.GetPart(uri);
XmlHelper.LoadXmlSafe(VmlDrawingXml, Part.GetStream());
}
}
internal ExcelVmlDrawings(ExcelPackage pck, ExcelWorksheet ws, Uri uri)
{
VmlDrawingXml = new XmlDocument();
VmlDrawingXml.PreserveWhitespace = false;
NameTable nt=new NameTable();
NameSpaceManager = new XmlNamespaceManager(nt);
NameSpaceManager.AddNamespace("v", ExcelPackage.schemaMicrosoftVml);
NameSpaceManager.AddNamespace("o", ExcelPackage.schemaMicrosoftOffice);
NameSpaceManager.AddNamespace("x", ExcelPackage.schemaMicrosoftExcel);
Uri = uri;
if (uri == null)
{
VmlDrawingXml.LoadXml(CreateVmlDrawings());
_drawings = new RangeCollection(new List<IRangeID>());
Part = null;
}
else
{
Part=pck.Package.GetPart(uri);
VmlDrawingXml.Load(Part.GetStream());
AddDrawingsFromXml(ws);
}
}
public IActionResult ExportExcel(int billId)
{
string sWebRootFolder = _hostingEnvironment.WebRootPath;
string sFileName = $"Bill_{billId}.xlsx";
// Template File
string templateDocument = Path.Combine(sWebRootFolder, "templates", "BillTemplate.xlsx");
string url = $"{Request.Scheme}://{Request.Host}/{"export-files"}/{sFileName}";
FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, "export-files", sFileName));
if (file.Exists)
{
file.Delete();
file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
}
using (FileStream templateDocumentStream = System.IO.File.OpenRead(templateDocument))
{
using (ExcelPackage package = new ExcelPackage(templateDocumentStream))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets["TEDUOrder"];
// Data Acces, load order header data.
var billDetail = _billService.GetDetail(billId);
// Insert customer data into template
worksheet.Cells[4, 1].Value = "Customer Name: " + billDetail.CustomerName;
worksheet.Cells[5, 1].Value = "Address: " + billDetail.CustomerAddress;
worksheet.Cells[6, 1].Value = "Phone: " + billDetail.CustomerMobile;
// Start Row for Detail Rows
int rowIndex = 9;
// load order details
var orderDetails = _billService.GetBillDetails(billId);
int count = 1;
foreach (var orderDetail in orderDetails)
{
// Cell 1, Carton Count
worksheet.Cells[rowIndex, 1].Value = count.ToString();
// Cell 2, Order Number (Outline around columns 2-7 make it look like 1 column)
worksheet.Cells[rowIndex, 2].Value = orderDetail.Product.Name;
// Cell 8, Weight in LBS (convert KG to LBS, and rounding to whole number)
worksheet.Cells[rowIndex, 3].Value = orderDetail.Quantity.ToString();
worksheet.Cells[rowIndex, 4].Value = orderDetail.Price.ToString("N0");
worksheet.Cells[rowIndex, 5].Value = (orderDetail.Price * orderDetail.Quantity).ToString("N0");
// Increment Row Counter
rowIndex++;
count++;
}
decimal total = (decimal)(orderDetails.Sum(x => x.Quantity * x.Price));
worksheet.Cells[24, 5].Value = total.ToString("N0");
var numberWord = "Total amount (by word): " + TextHelper.ToString(total);
worksheet.Cells[26, 1].Value = numberWord;
var billDate = billDetail.DateCreated;
worksheet.Cells[28, 3].Value = billDate.Day + ", " + billDate.Month + ", " + billDate.Year;
package.SaveAs(file); //Save the workbook.
}
}
return(new OkObjectResult(url));
}
// =========================================== Export Import ===================================================================
public FileResult ExportCustomerInfo([DataSourceRequest] DataSourceRequest request)
{
ExcelPackage pck = new ExcelPackage(new FileInfo(Server.MapPath("~/ExportExcelFile/24HCRM_CUSTOMER_INFO.xlsx")));
ExcelWorksheet ws = pck.Workbook.Worksheets["Data"];
if (asset.Export)
{
using (var dbConn = OrmliteConnection.openConn())
{
string whereCondition = "";
if (request.Filters.Count > 0)
{
whereCondition = " AND " + KendoApplyFilter.ApplyFilter(request.Filters[0], "");
}
string strQuery = @"SELECT cus.*
,ISNULL(ps1.Value,'N/A') AS StatusName
,ISNULL(source.Value,'N/A') AS SourceName
,ISNULL(ps2.Value,'N/A') AS TypeName
,ISNULL(emp.Name,'N/A') AS EmployeeName
,ISNULL(team.TeamName,'N/A') AS TeamName
,ISNULL(unit.Value,'N/A') AS RegionName
FROM ERPAPD_Customer cus
LEFT JOIN [Parameters] ps1 ON ps1.ParamID = cus.Status AND ps1.Type = 'CustomerStatus'
LEFT JOIN [Parameters] source ON source.ParamID = cus.Source AND source.Type = 'CustomerSource'
LEFT JOIN [ERPAPD_MasterData_Customer] ps2 ON ps2.Code = cus.CustomerType AND ps2.Type = 'CustomerType'
LEFT JOIN [ERPAPD_Employee] emp ON cus.StaffId = emp.RefEmployeeID
LEFT JOIN [CRM_Team] team ON team.TeamID = cus.GroupId
LEFT JOIN [CRM_Hierarchy] unit ON unit.HierarchyID = cus.UnitId
WHERE 1=1
";
var data = dbConn.Select <ERPAPD_Customer>(strQuery + whereCondition);
int rowData = 2;
foreach (var row in data)
{
int i = 2;
rowData++;
row.Phone = row.Phone.Replace(@"[", String.Empty);
row.Phone = row.Phone.Replace(@"]", String.Empty);
row.Website = row.Website.Replace(@"[", String.Empty);
row.Website = row.Website.Replace(@"]", String.Empty);
ws.Cells[rowData, i++].Value = "- " + row.CustomerName + "\n- Mã: " + row.CustomerID + "\n- Loại: " + row.TypeName;
ws.Cells[rowData, i++].Value = (!String.IsNullOrEmpty(row.Address) ? "- Địa chỉ: " + row.Address : "") + (!String.IsNullOrEmpty(row.Phone) ? "\n- Phone: " + row.Phone : "") + (!String.IsNullOrEmpty(row.Website) ? "\n- Website: " + row.Website : "");
string trangthai = String.Empty;
if (row.Status == "MOI")
{
trangthai = "Chưa gửi";
}
if (row.Status == "CHUA_DUYET")
{
trangthai = "Chờ duyệt";
}
if (row.Status == "HOAT_DONG")
{
trangthai = "Đã duyệt";
}
if (row.Status == "DUNG_HOAT_DONG")
{
trangthai = "Từ chối";
}
ws.Cells[rowData, i++].Value = ("\n- Tạo lúc: " + row.RowCreatedAt) + " - " + (row.RowCreatedUser) + "\n" + "- " + trangthai + ("\n- Cập nhật lúc: " + row.RowUpdatedAt) + " - " + (row.RowUpdatedUser);
ws.Cells[rowData, i++].Value = "- Nhân Viên: " + row.EmployeeName + "\n- " + row.TeamName + "\n- " + row.RegionName;
}
}
}
else
{
ws.Cells["A2:E2"].Merge = true;
ws.Cells["A2"].Value = "You don't have permission to export data.";
}
MemoryStream output = new MemoryStream();
pck.SaveAs(output);
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"24HCRM_CUSTOMER_INFO_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
protected void Save(ExcelPackage excelPackage, FileDto file)
{
var filePath = Path.Combine(AppFolders.TempFileDownloadFolder, file.FileToken);
excelPackage.SaveAs(new FileInfo(filePath));
}
public static GlobalSettings ReadExcel(GlobalSettings setting, string configFile)
{
if (File.Exists(configFile + ".tmp"))
{
//need rename the config.xlsm by the .tmp file
try
{
File.Delete(configFile); //remove existing config file
File.Move(configFile + ".tmp", configFile);
//File.Delete(configFile + ".tmp");
}
catch (Exception)
{
Logger.Write(
"Seem that you are opening config.xlsm, You need to close it for migration new config."
);
}
}
bool needSave = false;
using (FileStream stream = File.Open(configFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var package = new ExcelPackage(stream))
{
foreach (var item in setting.GetType().GetFields())
{
var att = item.GetCustomAttributes(typeof(NecrobotConfigAttribute), true)
.Cast <NecrobotConfigAttribute>()
.FirstOrDefault();
if (att != null)
{
var ws = package.Workbook.Worksheets[att.SheetName];
var configProp = item.GetValue(setting);
if (item.FieldType.IsGenericType &&
item.FieldType.GetGenericTypeDefinition() == typeof(Dictionary <,>))
{
var type = item.FieldType;
Type keyType = type.GetGenericArguments()[0];
Type valueType = type.GetGenericArguments()[1];
MethodInfo method = typeof(ExcelConfigHelper).GetMethod("ReadSheetAsDictionary",
BindingFlags.Public | BindingFlags.Static | BindingFlags.NonPublic);
MethodInfo genericMethod = method.MakeGenericMethod(valueType);
configProp = genericMethod.Invoke(null, new object[] { ws });
//configProp = ReadSheetAsDictionary<TransferFilter>(ws);
}
else
{
foreach (var cfg in configProp.GetType().GetFields())
{
var peAtt = cfg.GetCustomAttributes(typeof(NecrobotConfigAttribute), true)
.Cast <NecrobotConfigAttribute>()
.FirstOrDefault();
if (peAtt != null)
{
string key = string.IsNullOrEmpty(peAtt.Key) ? cfg.Name : peAtt.Key;
string keyFromExcel = ws.Cells[$"A{peAtt.Position + OFFSET_START}"]
.GetValue <string>();
if (keyFromExcel == key)
{
var value = ws.Cells[$"B{peAtt.Position + OFFSET_START}"].Value;
var convertedValue = Convert.ChangeType(value, cfg.FieldType);
cfg.SetValue(configProp, convertedValue);
}
else
{
//migrate config
needSave = true;
WriteOnePropertyToSheet(ws, configProp, cfg);
}
}
}
}
item.SetValue(setting, configProp);
//set to original object
}
}
var pkmSheet = package.Workbook.Worksheets["Pokemons"];
setting.ItemRecycleFilter = ReadItemRecycleFilter(package);
if (needSave || hasUpdate)
{
package.SaveAs(new FileInfo(configFile + ".tmp")); //use to migrate new config, hack hack hack
}
}
return(ConvertToBackwardCompitable(setting));
}
public async Task <ActionResult> ProcessFile(string id, string permohonanId)
{
if (string.IsNullOrEmpty(id))
{
return(Json(new { success = false, messge = "Store Id is not provided for the excel file" }));
}
var context = new SphDataContext();
var store = ObjectBuilder.GetObject <IBinaryStore>();
var doc = await store.GetContentAsync(id);
var temp = Path.GetTempFileName() + ".xlsx";
System.IO.File.WriteAllBytes(temp, doc.Content);
var file = new FileInfo(temp);
var excel = new ExcelPackage(file);
var ws = excel.Workbook.Worksheets["Pelajar"];
if (null == ws)
{
throw new ArgumentException("Cannot open Worksheet Pelajar in " + doc.FileName);
}
var permohonan = await context.LoadOneAsync <Permohonan>(x => x.Id == permohonanId);
if (null == permohonan)
{
return(HttpNotFound("Cannot find any Permohonan with Id=" + permohonanId));
}
var senaraiDaftar = new List <PendaftaranProgram>();
var row = 2;
var name = ws.Cells["A" + row].GetValue <string>();
var mykad = ws.Cells["B" + row].GetValue <string>();
var hasRow = !string.IsNullOrEmpty(name) && !string.IsNullOrEmpty(mykad);
var duplicateEmails = new List <string>();
string warning = "";
while (hasRow)
{
var student = new Pengguna
{
Id = mykad,
Nama = name,
MyKad = mykad,
Jantina = ws.Cells["C" + row].GetValue <string>(),
Warganegara = "Malaysia",
Umur = 0,
Emel = ws.Cells["G" + row].GetValue <string>(),
Emel2 = null,
Pekerjaan = "Pelajar",
Pelajar = new Pelajar
{
BidangPengajian = ws.Cells["D" + row].GetValue <string>(),
Tempat = ws.Cells["F" + row].GetValue <string>(),
TahapPendidikan = ws.Cells["E" + row].GetValue <string>()
},
IsResponden = true
};
var emailExist = await context.GetAnyAsync <Pengguna>(x => x.MyKad == student.MyKad);
if (!emailExist)
{
var email = Membership.FindUsersByEmail(student.Emel);
if (email.Count > 0)
{
duplicateEmails.Add(student.Emel);
}
}
var did = Guid.NewGuid().ToString();
var daftar = new PendaftaranProgram
{
NamaProgram = permohonan.NamaProgram,
MyKad = student.MyKad,
TarikhDaftar = DateTime.Now,
NamaPengguna = student.Nama,
NoPermohonan = permohonan.PermohonanNo,
PendaftaranNo = did,
PermohonanId = permohonanId,
Id = did,
WebId = did
};
//increment
row++;
name = ws.Cells["A" + row].GetValue <string>();
mykad = ws.Cells["B" + row].GetValue <string>();
hasRow = !string.IsNullOrEmpty(name) && !string.IsNullOrEmpty(mykad);
if (row - 2 > permohonan.BilRespondan)
{
warning = $"Fail Excel ini mengandungi lebih dari bilangan responden yang dibenarkan : {permohonan.BilRespondan}";
break;
}
var daftarExist = await context.GetAnyAsync <PendaftaranProgram>(x => x.MyKad == student.MyKad && x.NoPermohonan == permohonan.PermohonanNo);
if (!daftarExist)
{
senaraiDaftar.Add(daftar);
}
using (var session = context.OpenSession())
{
if (!emailExist)
{
session.Attach(student);
}
if (!daftarExist)
{
session.Attach(daftar);
}
if (!daftarExist || !emailExist)
{
await session.SubmitChanges("TambahResponden");
}
}
}
// TODO : verify the user name and email for each record
return(Json(new { success = true, warning, list = senaraiDaftar, duplicateEmails, status = "OK" }));
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(programOptions, jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_APM) == 0)
{
logger.Warn("No {0} targets to process", APPLICATION_TYPE_APM);
loggerConsole.Warn("No {0} targets to process", APPLICATION_TYPE_APM);
return(true);
}
try
{
loggerConsole.Info("Prepare Snapshots Method Calls Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Snapshots Method Call Lines Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Snapshots Method Call Lines Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivot
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Type";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.Cells[3, 1].Value = "See Location";
sheet.Cells[3, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT);
sheet.Cells[3, 2].StyleName = "HyperLinkStyle";
sheet.Cells[4, 1].Value = "See Timeline";
sheet.Cells[4, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT);
sheet.Cells[4, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 5, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_METHOD_CALL_LINES);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 9, 1);
#endregion
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
loggerConsole.Info("Fill Snapshots Method Call Lines Report File");
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, typeof(ControllerSummary), sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ApplicationSnapshotsReportFilePath(), 0, typeof(APMApplication), sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Method Call Lines
loggerConsole.Info("List of Method Call Lines");
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.SnapshotsMethodCallLinesReportFilePath(), 0, typeof(MethodCallLine), sheet, LIST_SHEET_START_TABLE_AT + 1, 1);
#endregion
loggerConsole.Info("Finalize Snapshots Method Call Lines Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
adjustColumnsOfEntityRowTableInMetricReport(APMApplication.ENTITY_TYPE, sheet, table);
ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshots"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshots"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsNormal"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsNormal"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsVerySlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsVerySlow"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsStall"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsStall"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsSlow"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsSlow"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumSnapshotsError"].Position + 1, sheet.Dimension.Rows, table.Columns["NumSnapshotsError"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Method Call Lines
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT + 1)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT + 1, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_METHOD_CALL_LINES);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
try
{
sheet.Column(table.Columns["Controller"].Position + 1).Width = 20;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["NodeName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["SegmentUserExperience"].Position + 1).Width = 10;
sheet.Column(table.Columns["SnapshotUserExperience"].Position + 1).Width = 10;
sheet.Column(table.Columns["RequestID"].Position + 1).Width = 20;
sheet.Column(table.Columns["SegmentID"].Position + 1).Width = 10;
sheet.Column(table.Columns["Type"].Position + 1).Width = 10;
sheet.Column(table.Columns["Framework"].Position + 1).Width = 15;
sheet.Column(table.Columns["FullNameIndent"].Position + 1).Width = 45;
sheet.Column(table.Columns["ExitCalls"].Position + 1).Width = 15;
sheet.Column(table.Columns["Occurred"].Position + 1).Width = 20;
sheet.Column(table.Columns["OccurredUtc"].Position + 1).Width = 20;
}
catch (OutOfMemoryException ex)
{
// Do nothing, we must have a lot of cells
logger.Warn("Ran out of memory due to too many rows/cells");
logger.Warn(ex);
}
ExcelAddress cfAddressUserExperience = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["SegmentUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SegmentUserExperience"].Position + 1);
addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience);
cfAddressUserExperience = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["SnapshotUserExperience"].Position + 1, sheet.Dimension.Rows, table.Columns["SnapshotUserExperience"].Position + 1);
addUserExperienceConditionalFormatting(sheet, cfAddressUserExperience);
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1], range, PIVOT_METHOD_CALL_LINES_TYPE_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending, true);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addRowFieldToPivot(pivot, "FullName");
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average);
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESTYPE_EXEC_AVERAGE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_METHOD_CALL_LINES_LOCATION_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addRowFieldToPivot(pivot, "Type");
addRowFieldToPivot(pivot, "Framework");
addRowFieldToPivot(pivot, "FullName");
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addColumnFieldToPivot(pivot, "ExecRange", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Count);
chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESLOCATION_EXEC_AVERAGE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
sheet.Column(7).Width = 20;
sheet = excelReport.Workbook.Worksheets[SHEET_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE_PIVOT];
pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 6, 1], range, PIVOT_METHOD_CALL_LINES_TIMELINE_EXEC_AVERAGE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "ElementType");
addFilterFieldToPivot(pivot, "NumChildren", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumExits", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Depth", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Class", eSortType.Ascending);
addFilterFieldToPivot(pivot, "Method", eSortType.Ascending);
addFilterFieldToPivot(pivot, "FullName", eSortType.Ascending);
addFilterFieldToPivot(pivot, "BTName", eSortType.Ascending);
addFilterFieldToPivot(pivot, "ExecRange", eSortType.Ascending, true);
ExcelPivotTableField fieldR = pivot.RowFields.Add(pivot.Fields["Occurred"]);
fieldR.AddDateGrouping(eDateGroupBy.Days | eDateGroupBy.Hours | eDateGroupBy.Minutes);
fieldR.Compact = false;
fieldR.Outline = false;
addColumnFieldToPivot(pivot, "Type", eSortType.Ascending);
addColumnFieldToPivot(pivot, "Framework", eSortType.Ascending);
addDataFieldToPivot(pivot, "Exec", DataFieldFunctions.Average);
chart = sheet.Drawings.AddChart(GRAPH_METHOD_CALL_LINESTIMELINE_EXEC_AVERAGE, eChartType.Line, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
FileIOHelper.CreateFolder(FilePathMap.ReportFolderPath());
string reportFilePath = FilePathMap.SnapshotMethodCallsExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
//Exportar Reporte pagos pendientes.
public void ExportExcel()
{
if (sesion == null)
{
sesion = SessionDB.start(Request, Response, false, db);
}
var sql = string.Empty;
try
{
System.Data.DataTable tbl = new System.Data.DataTable();
tbl.Columns.Add("ID Docente", typeof(string));
tbl.Columns.Add("Nombre", typeof(string));
tbl.Columns.Add("Tipo de pago", typeof(string));
tbl.Columns.Add("Año", typeof(string));
tbl.Columns.Add("Periodo", typeof(string));
tbl.Columns.Add("Esquema de pago", typeof(string));
tbl.Columns.Add("Concepto de pago", typeof(string));
tbl.Columns.Add("Monto", typeof(string));
tbl.Columns.Add("Fecha de pago", typeof(string));
tbl.Columns.Add("Centro de costos", typeof(string));
tbl.Columns.Add("Campus", typeof(string));
tbl.Columns.Add("Fecha actual", typeof(string));
tbl.Columns.Add("Situación", typeof(string));
sql = "SELECT * FROM v_pagospendientes";
if (Request.Params.Count > 0)
{
if (Request.Params["sede"] != null && Request.Params["sede"] != "")
{
sql += " where sede = '" + Request.Params["sede"] + "'";
}
if (Request.Params["ciclo"] != null && Request.Params["ciclo"] != "")
{
sql += " and ANIO = '" + Request.Params["ciclo"] + "'";
}
if (Request.Params["periodo"] != null && Request.Params["periodo"] != "")
{
sql += " and PERIODO = '" + Request.Params["periodo"] + "'";
}
if (Request.Params["situacion"] != null && Request.Params["situacion"] != "")
{
sql += " and ESTADO = '" + Request.Params["situacion"] + "'";
}
}
ResultSet res = db.getTable(sql);
while (res.Next())
{
// Here we add five DataRows.
tbl.Rows.Add(res.Get("IDDOCENTE"), res.Get("NOMBRE"), res.Get("TIPOPAGO"), res.Get("ANIO"), res.Get("PERIODO"), res.Get("ESQUEMA"), res.Get("CONCEPTO"), res.Get("BANCOS"), res.Get("FECHAPAGO"), res.Get("CENTROCOSTOS"), res.Get("SEDE"), res.Get("FECHAACTUAL"), res.Get("ESTADO"));
}
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Pagos pendientes");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
ws.Cells["A1:M1"].AutoFitColumns();
//ws.Column(1).Width = 20;
//ws.Column(2).Width = 80;
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells["A1:M1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ReportePagosPendientes.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
Log.write(this, "Start", LOG.CONSULTA, "Exporta Excel Reportes pendientes", sesion);
}
catch (Exception e)
{
ViewBag.Notification = Notification.Error(e.Message);
Log.write(this, "Start", LOG.ERROR, "Exporta Excel Reportes pendientes" + e.Message, sesion);
}
}
public static bool CargarDesdeWS()
{
//System.Console.WriteLine("CargarDesdeWS->STAR");
int server = 2;
string cKeyValidate = "W~(4n-Xp@fcJRVV3gepmYeU3=8Rrg3C,{RUvmXH6shWT48;P";
WSPaCr.WSBDSoapClient WSD = new WSPaCr.WSBDSoapClient();
//System.Console.WriteLine("CargarDesdeWS->Invocando el WS");
var miJson = WSD.GetDaPI(server, cKeyValidate);
//System.Console.WriteLine("CargarDesdeWS->Data en miJson");
System.Console.WriteLine("CargarDesdeWS->Los primeros 30 Chars recuperados->" + miJson.Substring(1, 30));
DataTable dtExcelData = (DataTable)JsonConvert.DeserializeObject(miJson, typeof(DataTable));
System.Console.WriteLine("CargarDesdeWS->Data a DataTable");
try
{
var consString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
//System.Console.WriteLine("CargarDesdeWS->Data a dbo.ImportLab START");
using (var con = new SqlConnection(consString))
{
using (var sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.ImportLab";
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
//System.Console.WriteLine("CargarDesdeWS->Data a dbo.ImportLab END");
//Archivo de exportacion de resultados y ejecucion de StoreProcedure
var ms = new MemoryStream();
using (var fs = System.IO.File.OpenRead(ConfigurationManager.AppSettings["ImportTemplate"]
.Replace("{countryId}", "9")
))
using (var excelPackage = new ExcelPackage(fs))
{
//System.Console.WriteLine("->111");
var excelWorkBook = excelPackage.Workbook;
int startColumn = 1;
int startRow = 3;
bool insertRow = true;
string Report = "ImportLab_CR";
string user = "******";
System.Console.WriteLine("->222");
AppendDataToExcel(9, excelWorkBook, Report, startRow, startColumn, 1, insertRow);
System.Console.WriteLine("->333");
excelPackage.SaveAs(ms);
System.Console.WriteLine("->444");
//implementación para el guardado del archivo
//FileInfo notImportedFile = new FileInfo(ConfigurationManager.AppSettings["ImportFailedFolder"] + User.Identity.Name + "_" + DateTime.Now.ToString("yyyyMMddhhmmsst") + "_" + Request.Files["file"]?.FileName + ".XLSX");
FileInfo notImportedFile = new FileInfo(ConfigurationManager.AppSettings["ImportFailedFolder"] + user + "_" + DateTime.Now.ToString("yyyyMMddhhmmsst") + ".XLSX");
System.Console.WriteLine("->555");
FileStream aFile = new FileStream(notImportedFile.FullName, FileMode.Create);
aFile.Seek(0, SeekOrigin.Begin);
System.Console.WriteLine("->666");
ExcelPackage excelNotImported = new ExcelPackage();
excelNotImported.Load(ms);
System.Console.WriteLine("->777");
excelNotImported.SaveAs(aFile);
System.Console.WriteLine("->888");
aFile.Close();
System.Console.WriteLine("->999");
//hacer inserción en la base de datos, bitácora de subidas
var consStringLogImport = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (var conImportLog = new SqlConnection(consStringLogImport))
{
using (var commandImportLog = new SqlCommand("importLogSP", conImportLog)
{
CommandType = CommandType.StoredProcedure
})
{
//var user = UserManager.FindById(User.Identity.GetUserId());
commandImportLog.Parameters.Clear();
commandImportLog.Parameters.Add("@Fecha_Import", SqlDbType.DateTime).Value = DateTime.Now.ToString();
//commandImportLog.Parameters.Add("@User_Import", SqlDbType.NVarChar).Value = User.Identity.Name;
commandImportLog.Parameters.Add("@User_Import", SqlDbType.NVarChar).Value = user;
//commandImportLog.Parameters.Add("@Country_ID", SqlDbType.Int).Value = user.Institution.CountryID;
commandImportLog.Parameters.Add("@Country_ID", SqlDbType.Int).Value = 9;
//commandImportLog.Parameters.Add("@Filename", SqlDbType.NVarChar).Value = ConfigurationManager.AppSettings["ImportFailedFolder"] + User.Identity.Name + "_" + DateTime.UtcNow.ToString("yyyyMMddhhmmsst") + "_" + Request.Files["file"]?.FileName;
commandImportLog.Parameters.Add("@Filename", SqlDbType.NVarChar).Value = notImportedFile.FullName;
var returnParameter = commandImportLog.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
conImportLog.Open();
using (var reader2 = commandImportLog.ExecuteReader())
{
if (returnParameter.Value.ToString() == "1")
{
//ViewBag.Message = $"Archivo registrado en al bitácora.";
System.Console.WriteLine("Archivo registrado en la bitácora.");
}
}
}
}
//fin de inserción
}
ms.Position = 0;
//ViewBag.Message = $"Archivo trabajado correctamente!";
//ViewBag.Message = "Archivo procesado. En la lista inferior podrá ver la lista de los registros que no fueron importados. Lo podrá localizar por la hora de subida y por su usuario. Puede hacer clic en la flecha azul para descargarlo.";
//return View();
//return new FileStreamResult(ms, "application/xlsx")
//{
// FileDownloadName = "NoInsertados.xlsx"
//};
//{ storeProcedureMessage}
System.Console.WriteLine("CargarDesdeWS->END");
}
catch (Exception e)
{
//ViewBag.Message = "El archivo no se pudo cargar, por favor, compruebe el archivo - " + e.Message;
System.Console.WriteLine("El archivo no se pudo cargar, por favor verifique - " + e.Message);
}
return(true);
}
public static void Init(TestContext context)
{
_pck = OpenPackage("DrawingEffect.xlsx", true);
}
public ActionResult Upload(HttpPostedFileBase file)
{
if ((file != null) && (file.ContentLength > 0) && !string.IsNullOrEmpty(file.FileName))
{
string fileName = file.FileName;
string fileContentType = file.ContentType;
byte[] fileBytes = new byte[file.ContentLength];
var data = file.InputStream.Read(fileBytes, 0, Convert.ToInt32(file.ContentLength));
//var aliasWorkCenter = db.Alias.Include(w => w.WorkCenters).ToList();
var wcs = db.WorkCenters.ToList();
var marcas = new List <Marca>();
using (var package = new ExcelPackage(file.InputStream))
{
PersonaServicio personaServicio = new PersonaServicio();
IRespuestaServicio <Persona> persona = personaServicio.GetPersona(User.Identity.GetUserId());
var currentSheet = package.Workbook.Worksheets;
foreach (var workSheet in currentSheet)
{
var noOfCol = workSheet.Dimension.End.Column;
var noOfRow = workSheet.Dimension.End.Row;
for (int rowIterator = 4; rowIterator <= noOfRow; rowIterator++)
{
if (workSheet.Cells[rowIterator, 3].Value != null)
{
string code_FA = workSheet.Cells[rowIterator, 3].Value.ToString().Trim();
code_FA = code_FA.Replace(" ", "");
if (marcas.Where(m => m.Code_FA == code_FA).Count() == 0)
{
var marca = new Marca();
marca.IdPersonaQueDioDeAlta = persona.Respuesta.Id;
marca.FechaDeAlta = DateTime.Now;
marca.Code_FA = code_FA;
marca.Descripcion = workSheet.Cells[rowIterator, 4].Value.ToString().Trim();
marca.Codigo_Cigarrillo = workSheet.Cells[rowIterator, 6].Value.ToString().Trim();
marca.Activo = false;
if (workSheet.Cells[rowIterator, 9].Value != null)
{
marca.PesoPorCigarrillo = Convert.ToDouble(workSheet.Cells[rowIterator, 9].Value.ToString().Trim());
marca.Activo = true;
}
if (workSheet.Cells[rowIterator, 12].Value != null)
{
marca.PesoTabacco = Convert.ToDouble(workSheet.Cells[rowIterator, 12].Value.ToString().Trim());
}
marcas.Add(marca);
}
}
}
}
db.Marcas.AddRange(marcas);
db.SaveChanges();
return(RedirectToAction("Index"));
}
}
return(View("Index"));
}
/// <summary>Writing <see cref="GroupSessionResultTableView"/> objects to an excel file</summary>
/// <param name="dataToWrite"><see cref="IEnumerable{GroupSessionResultTableView}"/> objects to write</param>
/// <param name="excel"><see cref="ExcelPackage"/> object</param>
/// <param name="workSheet"><see cref="ExcelWorksheet"/> object</param>
private static void WriteGroupSessionResultTable(IEnumerable <GroupSessionResultTableView> dataToWrite, ExcelPackage excel, ExcelWorksheet workSheet)
{
foreach (var table in dataToWrite)
{
int currentRow = 1;
workSheet = excel.Workbook.Worksheets.Add(table.AcademicYear);
SetWorkSheetStyle(workSheet);
SetRowStyle(workSheet.Row(currentRow));
workSheet.Cells[currentRow, currentRow].Value = table.SessionName;
workSheet.Cells[currentRow, currentRow, currentRow, table.Headers.Length].Merge = true;
SetRowStyle(workSheet.Row(++currentRow));
for (int j = 0; j < table.Headers.Length; j++)
{
workSheet.Cells[currentRow, ++j].Value = table.Headers[--j];
}
for (int k = ++currentRow, j = 0; j < table.TableRowViews.Count(); k++, j++)
{
workSheet.Cells[k, 1].Value = table.TableRowViews.ToList()[j].GroupName;
workSheet.Cells[k, 2].Value = table.TableRowViews.ToList()[j].MaxAssessment;
workSheet.Cells[k, 3].Value = table.TableRowViews.ToList()[j].MinAssessment;
workSheet.Cells[k, 4].Value = table.TableRowViews.ToList()[j].AvgAssessment;
}
SetBorder(excel, workSheet, table.AcademicYear);
}
}
/// <summary>Writing <see cref="GroupTableView"/> objects to an excel file</summary>
/// <param name="dataToWrite"><see cref="IEnumerable{GroupTableView}"/> objects to write</param>
/// <param name="excel"><see cref="ExcelPackage"/> object</param>
/// <param name="workSheet"><see cref="ExcelWorksheet"/> object</param>
private static void WriteGroupTable(IEnumerable <GroupTableView> dataToWrite, ExcelPackage excel, ExcelWorksheet workSheet)
{
foreach (var groupTableView in dataToWrite)
{
int currentRow = 1;
workSheet = excel.Workbook.Worksheets.Add(groupTableView.GroupName);
SetWorkSheetStyle(workSheet);
SetRowStyle(workSheet.Row(currentRow));
workSheet.Cells[currentRow, currentRow].Value = groupTableView.SessionName;
workSheet.Cells[currentRow, currentRow, currentRow, groupTableView.Headers.Length].Merge = true;
SetRowStyle(workSheet.Row(++currentRow));
workSheet.Cells[currentRow, currentRow].Value = $"Group: {groupTableView.GroupName}";
workSheet.Cells[currentRow, currentRow, currentRow, groupTableView.Headers.Length].Merge = true;
SetRowStyle(workSheet.Row(++currentRow));
for (int i = 0; i < groupTableView.Headers.Length; i++)
{
workSheet.Cells[currentRow, ++i].Value = groupTableView.Headers[--i];
}
for (int i = ++currentRow, j = 0; j < groupTableView.TableRawViews.Count(); i++, j++)
{
workSheet.Cells[i, 1].Value = groupTableView.TableRawViews.ToList()[j].StudentSurname;
workSheet.Cells[i, 2].Value = groupTableView.TableRawViews.ToList()[j].StudentName;
workSheet.Cells[i, 3].Value = groupTableView.TableRawViews.ToList()[j].StudentPatronymic;
workSheet.Cells[i, 4].Value = groupTableView.TableRawViews.ToList()[j].Subject;
workSheet.Cells[i, 5].Value = groupTableView.TableRawViews.ToList()[j].AssessmentForm;
workSheet.Cells[i, 6].Value = groupTableView.TableRawViews.ToList()[j].Date;
workSheet.Cells[i, 7].Value = groupTableView.TableRawViews.ToList()[j].Assessment;
}
SetBorder(excel, workSheet, groupTableView.GroupName);
}
}
/// <summary>Writing <see cref="ExpelledStudentsReportView"/> object to an excel file</summary>
/// <param name="dataToWrite"></param>
/// <param name="excel"></param>
/// <param name="workSheet"></param>
private static void WriteExpelledStudentsTable(IEnumerable <ExpelledStudentsTableView> dataToWrite, ExcelPackage excel, ExcelWorksheet workSheet)
{
foreach (var data in dataToWrite)
{
int currentRow = 1;
workSheet = excel.Workbook.Worksheets.Add(data.GroupName);
SetWorkSheetStyle(workSheet);
SetRowStyle(workSheet.Row(currentRow));
workSheet.Cells[currentRow, currentRow].Value = $"{data.GroupName} students to be expelled";
workSheet.Cells[currentRow, currentRow, currentRow, data.Headers.Length].Merge = true;
SetRowStyle(workSheet.Row(++currentRow));
for (int i = 0; i < data.Headers.Length; i++)
{
workSheet.Cells[currentRow, ++i].Value = data.Headers[--i];
}
for (int i = ++currentRow, j = 0; j < data.TableRowViews.Count(); i++, j++)
{
workSheet.Cells[i, 1].Value = data.TableRowViews.ToList()[j].StudentSurname;
workSheet.Cells[i, 2].Value = data.TableRowViews.ToList()[j].StudentName;
workSheet.Cells[i, 3].Value = data.TableRowViews.ToList()[j].StudentPatronymic;
}
SetBorder(excel, workSheet, data.GroupName);
}
}
public ActionResult ImportFromExcel()
{
try
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xlsx")
{
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "]" + Request.Files["FileUpload"].FileName);
string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "-Error]" + Request.Files["FileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
var rownumber = 2;
var total = 0;
FileInfo fileInfo = new FileInfo(fileLocation);
var excelPkg = new ExcelPackage(fileInfo);
FileInfo template = new FileInfo(Server.MapPath(@"~\ExportExcelFile\TW_Goods_Category.xlsx"));
template.CopyTo(errorFileLocation);
FileInfo _fileInfo = new FileInfo(errorFileLocation);
var _excelPkg = new ExcelPackage(_fileInfo);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["TW_Goods_Category"];
ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["TW_Goods_Category"];
//remove row
int totalRows = oSheet.Dimension.End.Row;
for (int i = 2; i <= totalRows; i++)
{
string CatID = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString() : "0";
try
{
var write = new ERPAPD_Customer();
var checkCatNumber = dbConn.FirstOrDefault <ERPAPD_Customer>("CatId={0}", CatID);
if (checkCatNumber != null)
{
//write.Note = Note;
dbConn.Update(write);
total++;
}
else
{
//write.Note = Note;
dbConn.Insert(write);
total++;
}
}
catch (Exception e)
{
//eSheet.Cells[rownumber, 2].Value = CatName;
//eSheet.Cells[rownumber, 3].Value = Status;
//eSheet.Cells[rownumber, 5].Value = CatNumber;
eSheet.Cells[rownumber, 10].Value = e.Message;
rownumber++;
continue;
}
}
_excelPkg.Save();
return(Json(new { success = true, total = total, totalError = rownumber - 2, link = errorFileLocation }));
}
else
{
return(Json(new { success = false, error = "File extension is not valid. *.xlsx please." }));
}
}
else
{
return(Json(new { success = false, error = "File upload null" }));
}
}
}
catch (Exception ex)
{
return(Json(new { success = false, error = ex.Message }));
}
}
public Stream CreateExcelFile(List <T> data, Func <ExcelWorksheet, string, ExcelWorksheet> formatWorkSheet)
{
Type objectType = typeof(T);
using (var excelPackage = new ExcelPackage(new MemoryStream()))
{
excelPackage.Workbook.Properties.Author = "Author";
//tạo title cho file excel
excelPackage.Workbook.Properties.Title = "Title";
//commment
excelPackage.Workbook.Properties.Comments = "Comments";
//add sheet vào fiel excel
excelPackage.Workbook.Worksheets.Add("Sheet 1");
//lấy sheet vừa mới tạo để thao tác
var workSheet = excelPackage.Workbook.Worksheets[1];
var rowCount = data.Count;
var columnCount = this.properties.Count;
for (int column = 0; column < columnCount; column++)
{
KeyValuePair <string, string> keyValue = this.properties.ElementAt(column);
workSheet.Cells[startRow, column + startColumn].Value = keyValue.Value;
}
if (objectType.Name == "HSCV_VANBANDEN_BO")
{
for (int row = 0; row < rowCount; row++)
{
var item = data[row];
for (int column = 0; column < columnCount; column++)
{
KeyValuePair <string, string> keyValue = this.properties.ElementAt(column);
string propertyName = keyValue.Key;
object cellValue = string.Empty;
if (!string.IsNullOrEmpty(propertyName))
{
PropertyInfo property = objectType.GetProperty(propertyName);
if (property != null)
{
if (property.PropertyType == typeof(DateTime?) || property.PropertyType == typeof(DateTime))
{
var dataValue = property.GetValue(data[row]);
if (propertyName == "NGAY_BANHANH")
{
property = objectType.GetProperty("NGAY_VANBAN");
var dataNgayVanBan = property.GetValue(data[row]);
property = objectType.GetProperty("NGAY_HIEULUC");
var dataCoHieuLuc = property.GetValue(data[row]);
property = objectType.GetProperty("NGAYHET_HIEULUC");
var dataHetHieuLuc = property.GetValue(data[row]);
if (dataValue != null)
{
cellValue += string.Format("- Ngày ban hành: {0}\n", string.Format("{0:dd/MM/yyyy}", dataValue));
}
if (dataNgayVanBan != null)
{
cellValue += string.Format("- Ngày văn bản: {0}\n", string.Format("{0:dd/MM/yyyy}", dataNgayVanBan));
}
if (dataCoHieuLuc != null)
{
cellValue += string.Format("- Ngày có hiệu lực: {0}\n", string.Format("{0:dd/MM/yyyy}", dataCoHieuLuc));
}
if (dataHetHieuLuc != null)
{
cellValue += string.Format("- Ngày hết hiệu lực: {0}\n", string.Format("{0:dd/MM/yyyy}", dataHetHieuLuc));
}
}
else
{
cellValue = string.Format("{0:dd/MM/yyyy}", dataValue);
}
}
else
{
cellValue = property.GetValue(data[row]) ?? string.Empty;
}
}
else if (propertyName == "STT")
{
cellValue = (row + 1);
}
}
workSheet.Cells[startRow + row + 1, column + startColumn].Value = cellValue;
workSheet.Row(startRow + row + 1).Height = 30;
}
}
}
else
{
for (int row = 0; row < rowCount; row++)
{
var item = data[row];
for (int column = 0; column < columnCount; column++)
{
KeyValuePair <string, string> keyValue = this.properties.ElementAt(column);
if (row == 0)
{
workSheet.Cells[startRow, column + startColumn].Value = keyValue.Value;
}
string propertyName = keyValue.Key;
object cellValue = string.Empty;
if (!string.IsNullOrEmpty(propertyName))
{
PropertyInfo property = objectType.GetProperty(propertyName);
if (property != null)
{
if (property.PropertyType == typeof(DateTime?) || property.PropertyType == typeof(DateTime))
{
cellValue = string.Format("{0:dd/MM/yyyy}", property.GetValue(data[row]));
}
else
{
cellValue = property.GetValue(data[row]) ?? string.Empty;
cellValue = property.GetValue(data[row]) ?? string.Empty;
}
}
else if (propertyName == "STT")
{
cellValue = (row + 1);
}
}
workSheet.Cells[startRow + row + 1, column + startColumn].Value = cellValue;
workSheet.Row(startRow + row + 1).Height = 30;
}
}
}
//định dạng biểu mẫu
workSheet = formatWorkSheet(workSheet, this.fileName);
ExcelRange dataRange = workSheet.SelectedRange[startRow, startColumn, (startRow + data.Count), (startColumn + this.properties.Count) - 1];
dataRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
dataRange.Style.WrapText = true;
dataRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
dataRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
if (leftAlignColumn != null)
{
foreach (var col in leftAlignColumn)
{
ExcelRange range = workSheet.SelectedRange[startRow, col, (startRow + data.Count), col];
range.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
range.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
}
}
if (rightAlignColumn != null)
{
foreach (var col in rightAlignColumn)
{
ExcelRange range = workSheet.SelectedRange[startRow, col, (startRow + data.Count), col];
range.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
range.Style.WrapText = true;
range.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
}
for (int row = startRow; row <= (startRow + data.Count); row++)
{
for (int column = startColumn; column <= (startColumn + this.properties.Count) - 1; column++)
{
workSheet.Cells[row, column].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, System.Drawing.Color.Black);
}
}
ExcelRange headerRange = workSheet.SelectedRange[startRow, startColumn, startRow, (startColumn + this.properties.Count) - 1];
headerRange.Style.Font.Bold = true;
headerRange.Style.Font.Size = 12;
headerRange.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
headerRange.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
excelPackage.Save();
return(excelPackage.Stream);
}
}
public static void WriteExcel()
{
var path = GetSavePath("UILabel中文分析报告", "xlsx");
var file = new FileInfo(path);
if (file.Exists)
{
file.Delete();
file = new FileInfo(path);
}
ReadJson();
//获取所有的Prefab的GUID
var excelConfigs = new Dictionary <string, ExcelConfig>();
var assets = AssetDatabase.FindAssets("t:Prefab", PrefabPath);
for (var i = 0; i < assets.Length; i++)
{
//通过GUID获取Asset路径
var guid = AssetDatabase.GUIDToAssetPath(assets[i]);
//加载Asset通过路径
var gameObject = AssetDatabase.LoadAssetAtPath <GameObject>(guid);
var labels = gameObject.GetComponentsInChildren <UILabel>();
foreach (var uiLabel in labels)
{
if (HasChinese(uiLabel.text))
{
if (excelConfigs.ContainsKey(gameObject.name))
{
excelConfigs[gameObject.name].uiLabelCompnents.Add(uiLabel);
}
else
{
var config = new ExcelConfig();
config.prefabPath = guid;
config.componentPath = GetGameObjectPath(uiLabel.gameObject).Remove(0, 1);
config.uiLabelCompnents = new List <UILabel>();
config.uiLabelCompnents.Add(uiLabel);
excelConfigs.Add(gameObject.name, config);
}
}
}
EditorUtility.DisplayProgressBar("查找中", guid, (float)i / (assets.Length - 1));
}
var row = 1;
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets.Add("中文分析报告");
//设置表头
worksheet.Cells[row, 1].Value = "Prefab路径";
worksheet.Cells[row, 2].Value = "组件层级路径";
worksheet.Cells[row, 3].Value = "组件中文值";
//设置表头为粗体
using (var range = worksheet.Cells[row, 1, row, 3])
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.AntiqueWhite);
range.Style.Font.Bold = true;
}
row++;
//row = 2
var count = 0;
foreach (var excelConfig in excelConfigs.Values)
{
worksheet.Cells[row, 1].Value =
AssetDatabase.LoadAssetAtPath <GameObject>(excelConfig.prefabPath).name;
worksheet.Cells[row, 1].Style.Font.Bold = true;
using (var range = worksheet.Cells[row, 1, row, 3])
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml("#DDEBF7"));
}
row++;
for (var i = 0; i < excelConfig.uiLabelCompnents.Count; i++)
{
var label = excelConfig.uiLabelCompnents[i];
worksheet.Cells[row, 1].Value = excelConfig.prefabPath;
worksheet.Cells[row, 2].Value = GetGameObjectPath(label.gameObject).Remove(0, 1);
worksheet.Cells[row, 3].Value = label.text;
row++;
}
count++;
row++;
EditorUtility.DisplayProgressBar("写入Excel中", excelConfig.prefabPath,
(float)count / (excelConfigs.Count - 1));
}
package.Save();
}
excelConfigs = null;
EditorUtility.ClearProgressBar();
AssetDatabase.Refresh();
}
public string ExportAttendance(List <Meeting> meetings, DateTime startDate, DateTime endDate)
{
if (meetings == null || meetings.Count == 0)
{
MessageBox.Show("Тайлан гаргах хурлаа сонгоно уу!");
return("");
}
using (var package = new ExcelPackage())
{
// Add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Ажилтнаар");
//Add the headers
worksheet.Cells[1, 1].Value = "№";
worksheet.Cells[1, 2].Value = "Нэр";
worksheet.Cells[1, 3].Value = "Хэлтэс";
worksheet.Cells[1, 4].Value = "Албан тушаал";
List <ArchivedMeeting> archivedMeetings = meetingController.archivedMeetingModel.GetByFK(meetings.First().IDName, meetings.Select(x => x is ModifiedMeeting ? (((ModifiedMeeting)x).meeting_id) : x.id).ToArray());
archivedMeetings.RemoveAll(x => x.meetingDatetime.Date <startDate || x.meetingDatetime.Date> endDate);
if (archivedMeetings.Count == 0)
{
throw new Exception("Таны сонгосон хурал " + startDate.ToString("yyyy/MM/dd") + "-с " + endDate.ToString("yyyy/MM/dd") + "-нд хийгдээгүй байна.");
}
for (int i = 0; i < archivedMeetings.Count; i++)
{
worksheet.Cells[1, i + 5].Value = archivedMeetings[i].meetingDatetime.ToString("yyyy/MM/dd HH:mm") + "\n" + archivedMeetings[i].name;
worksheet.Cells[1, i + 5].Style.TextRotation = 90;
}
List <User> users = new List <User>();
List <Attendance> attendances = meetingController.attendanceModel.GetByFK(archivedMeetings.First().IDName, archivedMeetings.Select(x => x.id).ToArray());
List <int> userids = new List <int>();
if (attendances.Count > 1900)
{
IEnumerable <List <Attendance> > splitedAttendances = SplitList <Attendance>(attendances, 1900);
foreach (List <Attendance> chunkAttendance in splitedAttendances)
{
userids.AddRange(chunkAttendance.Select(x => x.userId));
}
}
else
{
userids = attendances.Select(x => x.userId).ToList();
}
userids = userids.Distinct().ToList();
Dictionary <int, string> positions = meetingController.positionModel.GetAll().ToDictionary(x => x.id, x => x.name);
Dictionary <int, string> abbrStatuses = meetingController.statusModel.GetAll().ToDictionary(x => x.id, x => GetAbbr(x.name));
Dictionary <int, string> statuses = meetingController.statusModel.GetAll().ToDictionary(x => x.id, x => x.name);
Dictionary <int, string> departments = meetingController.departmentModel.GetAll().ToDictionary(x => x.id, x => x.name);
Dictionary <int, int[, ]> departmentAttendance = new Dictionary <int, int[, ]>();
if (userids.Count == 0)
{
throw new Exception("Таны сонгосон хуралд " + startDate.ToString("yyyy/MM/dd") + "-с " + endDate.ToString("yyyy/MM/dd") + "-нд ямар ч хүн суугаагүй байна.");
}
users = meetingController.userModel.Get(userids.ToArray()).OrderBy(x => x.fname + x.lname).OrderBy(x => x.departmentId).Distinct().ToList();
worksheet.Cells[1, archivedMeetings.Count + 5].Value = "Нийт ирсэн";
worksheet.Cells[1, archivedMeetings.Count + 6].Value = "Нийт хоцорсон";
worksheet.Cells[1, archivedMeetings.Count + 7].Value = "Нийт тасалсан";
worksheet.Cells[1, archivedMeetings.Count + 8].Value = "Ирц";
for (int i = 0; i < users.Count; i++)
{
worksheet.Cells[i + 2, 1].Value = i + 1;
worksheet.Cells[i + 2, 2].Value = users[i].fname;
worksheet.Cells[i + 2, 3].Value = departments.ContainsKey(users[i].departmentId) ? departments[users[i].departmentId] : "";
worksheet.Cells[i + 2, 4].Value = positions.ContainsKey(users[i].positionId) ? positions[users[i].positionId] : "";
string columnName1 = GetExcelColumnName(5);
string columnName2 = GetExcelColumnName(archivedMeetings.Count + 4);
string columnRange = columnName1 + (i + 2).ToString() + ":" + columnName2 + (i + 2).ToString();
worksheet.Cells[i + 2, archivedMeetings.Count + 5].Formula = "COUNTIF(" + columnRange + ", \"И\")";
worksheet.Cells[i + 2, archivedMeetings.Count + 6].Formula = "COUNTIF(" + columnRange + ", \"Х*\")";
worksheet.Cells[i + 2, archivedMeetings.Count + 7].Formula = "COUNTIF(" + columnRange + ", \"Т\")";
worksheet.Cells[i + 2, archivedMeetings.Count + 8].Formula = "(COUNTIF(" + columnRange + ", \"И\") + COUNTIF(" + columnRange + ", \"Х*\") + COUNTIF(" + columnRange + ", \"Ч*\"))/COUNTA(" + columnRange + ")";
worksheet.Cells[i + 2, archivedMeetings.Count + 8].Style.Numberformat.Format = "#0%";
if (!departmentAttendance.ContainsKey(users[i].departmentId))
{
departmentAttendance.Add(users[i].departmentId, new int[archivedMeetings.Count, 18]);
}
}
for (int i = 0; i < archivedMeetings.Count; i++)
{
for (int j = 0; j < users.Count; j++)
{
Attendance attendance = attendances.FindAll(x => x.userId == users[j].id).Find(x => x.archivedMeetingId == archivedMeetings[i].id);
if (attendance != null)
{
string status;
Color colFromHex;
switch (attendance.statusId)
{
case 1: status = "И"; colFromHex = ColorTranslator.FromHtml("#adebad"); break;
case 2: status = "Х(" + attendance.regTime.ToString() + ")"; colFromHex = ColorTranslator.FromHtml("#ffd480"); break;
case 14: status = "Т"; colFromHex = ColorTranslator.FromHtml("#ffcccc"); break;
case 15: status = "Б"; colFromHex = ColorTranslator.FromHtml("#d9d9d9"); break;
default: status = "Ч(" + abbrStatuses[attendance.statusId] + ")"; colFromHex = ColorTranslator.FromHtml("#b3d9ff"); break;
}
worksheet.Cells[j + 2, i + 5].Value = status;
worksheet.Cells[j + 2, i + 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[j + 2, i + 5].Style.Fill.BackgroundColor.SetColor(colFromHex);
departmentAttendance[users[j].departmentId][i, attendance.statusId]++;
departmentAttendance[users[j].departmentId][i, 16]++;
}
}
string columnName = GetExcelColumnName(i + 5);
string columnRange = columnName + "2:" + columnName + (users.Count + 1).ToString();
worksheet.Cells[users.Count + 2, i + 5].Formula = "COUNTA(" + columnRange + ")";
worksheet.Cells[users.Count + 3, i + 5].Formula = "COUNTIF(" + columnRange + ", \"И\")&\" \"&TEXT(COUNTIF(" + columnRange + ", \"И\")/COUNTA(" + columnRange + "),\"(##%)\")";
worksheet.Cells[users.Count + 4, i + 5].Formula = "COUNTIF(" + columnRange + ", \"Х*\")&\" \"&TEXT(COUNTIF(" + columnRange + ", \"Х*\")/COUNTA(" + columnRange + "),\"(##%)\")";
worksheet.Cells[users.Count + 5, i + 5].Formula = "COUNTIF(" + columnRange + ", \"Ч*\")&\" \"&TEXT(COUNTIF(" + columnRange + ", \"Ч*\")/COUNTA(" + columnRange + "),\"(##%)\")";
worksheet.Cells[users.Count + 6, i + 5].Formula = "COUNTIF(" + columnRange + ", \"Т\")&\" \"&TEXT(COUNTIF(" + columnRange + ", \"Т\")/COUNTA(" + columnRange + "),\"(##%)\")";
worksheet.Cells[users.Count + 2, i + 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[users.Count + 3, i + 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[users.Count + 4, i + 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[users.Count + 5, i + 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
worksheet.Cells[users.Count + 6, i + 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
worksheet.Cells[users.Count + 2, 2].Value = "Нийт";
worksheet.Cells[users.Count + 3, 2].Value = "Нийт ирсэн";
worksheet.Cells[users.Count + 4, 2].Value = "Нийт хоцорсон";
worksheet.Cells[users.Count + 5, 2].Value = "Нийт чөлөөтэй";
worksheet.Cells[users.Count + 6, 2].Value = "Нийт тасалсан";
worksheet.Cells[users.Count + 8, 2].Value = "Тайлбар";
worksheet.Cells[users.Count + 8, 2].Style.Font.Bold = true;
int statusCount = 0;
foreach (KeyValuePair <int, string> status in statuses)
{
string value;
Color colFromHex;
switch (status.Key)
{
case 1: value = "И = Ирсэн"; colFromHex = ColorTranslator.FromHtml("#adebad"); break;
case 2: value = "Х(#) = Хоцорсон(Минут)"; colFromHex = ColorTranslator.FromHtml("#ffd480"); break;
case 14: value = "Т = Тасалсан"; colFromHex = ColorTranslator.FromHtml("#ffcccc"); break;
case 15: value = "Б = Бүртгэгдээгүй"; colFromHex = ColorTranslator.FromHtml("#d9d9d9"); break;
default: value = "Ч(" + abbrStatuses[status.Key] + ") = Чөлөөтэй(" + status.Value + ")"; colFromHex = ColorTranslator.FromHtml("#b3d9ff"); break;
}
worksheet.Cells[users.Count + 9 + statusCount, 2].Value = value;
worksheet.Cells[users.Count + 9 + statusCount, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[users.Count + 9 + statusCount, 2].Style.Fill.BackgroundColor.SetColor(colFromHex);
statusCount++;
}
//Ok now format the values;
using (var range = worksheet.Cells[1, 1, 1, archivedMeetings.Count + 4])
{
range.Style.Font.Bold = true;
}
worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
if (departmentAttendance.Count > 0)
{
// Add a new worksheet to the empty workbook
ExcelWorksheet worksheet2 = package.Workbook.Worksheets.Add("Хэлтсээр");
//Add the headers
worksheet2.Cells[1, 1].Value = "№";
worksheet2.Cells[1, 2].Value = "Хэлтэс";
for (int i = 0; i < archivedMeetings.Count; i++)
{
worksheet2.Cells[1, i + 3].Value = archivedMeetings[i].meetingDatetime.ToString("yyyy/MM/dd HH:mm") + "\n" + archivedMeetings[i].name;
worksheet2.Cells[1, i + 3].Style.TextRotation = 90;
}
worksheet2.Cells[1, archivedMeetings.Count + 3].Value = "Нийт ирц";
Dictionary <int, double> departmentAttendancePercent = new Dictionary <int, double>();
for (int i = 0; i < archivedMeetings.Count; i++)
{
int j = 0;
foreach (KeyValuePair <int, int[, ]> entry in departmentAttendance)
{
worksheet2.Cells[j + 2, i + 3].Value = (entry.Value[i, 1] + entry.Value[i, 2]) + "/" + entry.Value[i, 16];
double value;
if (entry.Value[i, 16] == 0)
{
value = 0;
}
else
{
value = ((double)(entry.Value[i, 1] + entry.Value[i, 2])) / entry.Value[i, 16];
}
if (!departmentAttendancePercent.ContainsKey(entry.Key))
{
departmentAttendancePercent.Add(entry.Key, value);
}
else
{
departmentAttendancePercent[entry.Key] += value;
}
j++;
}
}
{
int i = 0;
foreach (KeyValuePair <int, int[, ]> entry in departmentAttendance)
{
worksheet2.Cells[i + 2, 1].Value = i + 1;
if (entry.Key != -1)
{
worksheet2.Cells[i + 2, 2].Value = departments[entry.Key];
}
else
{
worksheet2.Cells[i + 2, 2].Value = "Бусад";
}
worksheet2.Cells[i + 2, archivedMeetings.Count + 3].Value = departmentAttendancePercent[entry.Key] / archivedMeetings.Count;
worksheet2.Cells[i + 2, archivedMeetings.Count + 3].Style.Numberformat.Format = "#0%";
i++;
}
}
//Ok now format the values;
using (var range = worksheet2.Cells[1, 1, 1, archivedMeetings.Count + 2])
{
range.Style.Font.Bold = true;
}
worksheet2.Cells.AutoFitColumns(0); //Autofit columns for all cells
// lets set the header text
worksheet2.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Inventory";
// add the page number to the footer plus the total number of pages
worksheet2.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet2.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
worksheet2.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
package.Workbook.Worksheets.MoveToStart("Хэлтсээр");
}
// set some document properties
package.Workbook.Properties.Title = "Хурлын ирц";
package.Workbook.Properties.Author = "BORTS";
package.Workbook.Properties.Comments = "Хурлын ирцийн тайлан";
// set some extended property values
package.Workbook.Properties.Company = "BolorSoft LLC.";
var xlFile = Utils.GetFileInfo(startDate.ToString("yyyyMMdd") + "_" + endDate.ToString("yyyyMMdd") + "_report.xlsx");
// save our new workbook in the output directory and we are done!
package.SaveAs(xlFile);
MessageBox.Show(startDate.ToString("yyyy / MM / dd") + " - с " + endDate.ToString("yyyy / MM / dd") + " хүртэлх " + " тайлан " + xlFile.FullName + " файлд амжилттай гарлаа.", "Тайлан амжилттай гарлаа");
System.Diagnostics.Process.Start(xlFile.FullName);
return(xlFile.FullName);
}
}
/// <summary>
/// Sets the workbook properties and adds a default sheet.
/// </summary>
/// <param name="p">The p.</param>
/// <returns></returns>
private static void SetWorkbookProperties(ExcelPackage p)
{
//Here setting some document properties
p.Workbook.Properties.Author = "trx2Any";
p.Workbook.Properties.Title = "Trx2ExcelReports";
}
public ReportExcelBuilder(string path)
{
excel = new ExcelPackage(new FileInfo(path));
}
public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading));
int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3;
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
// add the content into the Excel file
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// autofit width of cells with small content
int columnIndex = 1;
foreach (DataColumn column in dataTable.Columns)
{
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
int maxLength = columnCells.Max(cell => (cell.Value == null ? "" : Convert.ToString(cell.Value)).Count());
if (maxLength < 150)
{
workSheet.Column(columnIndex).AutoFit();
}
columnIndex++;
}
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
{
if (i == 0 && showSrNo)
{
continue;
}
if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
{
workSheet.DeleteColumn(i + 1);
}
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 5;
}
result = package.GetAsByteArray();
}
return(result);
}
/// <summary>
/// EPPlus读取Excel(2003/2007)
/// </summary>
/// <typeparam name="T">泛型类型</typeparam>
/// <param name="fullPath">Excel路径</param>
/// <returns>泛型集合</returns>
public static List <List <T> > EPPlusReadExcel <T>(string fullPath) where T : class, new()
{
var lists = new List <List <T> >();
try
{
var dictHeader = new Dictionary <string, int>();
var file = new FileInfo(fullPath);
using (var package = new ExcelPackage(file))
{
for (var sheetIndex = 1; sheetIndex <= package.Workbook.Worksheets.Count; sheetIndex++)
{
var list = new List <T>();
using (var worksheet = package.Workbook.Worksheets[sheetIndex])
{
var colStart = worksheet.Dimension.Start.Column; //工作区开始列
var colEnd = worksheet.Dimension.End.Column; //工作区结束列
var rowStart = worksheet.Dimension.Start.Row; //工作区开始行号
var rowEnd = worksheet.Dimension.End.Row; //工作区结束行号
//将每列标题添加到字典中
for (int i = colStart; i <= colEnd; i++)
{
dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
}
var propertyInfoList = new List <PropertyInfo>(typeof(T).GetProperties());
for (int row = rowStart + 1; row <= rowEnd; row++)
{
var result = new T();
//为对象T的各属性赋值
foreach (var p in propertyInfoList)
{
//与属性名对应的单元格
var cell = worksheet.Cells[row, dictHeader[p.Name]];
if (cell.Value == null)
{
continue;
}
switch (p.PropertyType.Name.ToLower())
{
case "string":
p.SetValue(result, cell.GetValue <string>(), null);
break;
case "int16":
p.SetValue(result, cell.GetValue <short>(), null);
break;
case "int32":
p.SetValue(result, cell.GetValue <int>(), null);
break;
case "int64":
p.SetValue(result, cell.GetValue <int>(), null);
break;
case "decimal":
p.SetValue(result, cell.GetValue <decimal>(), null);
break;
case "double":
p.SetValue(result, cell.GetValue <double>(), null);
break;
case "datetime":
p.SetValue(result, cell.GetValue <DateTime>(), null);
break;
case "boolean":
p.SetValue(result, cell.GetValue <bool>(), null);
break;
case "byte":
p.SetValue(result, cell.GetValue <byte>(), null);
break;
case "char":
p.SetValue(result, cell.GetValue <char>(), null);
break;
case "single":
p.SetValue(result, cell.GetValue <float>(), null);
break;
default:
break;
}
}
list.Add(result);
}
}
lists.Add(list);
}
}
}
catch (Exception ex)
{
LogHelper.Error(ex, "EPPlus读取Excel(2003/2007)");
}
return(lists);
}
public IActionResult ImportExcel(IFormFile file)
{
int failedCount = 0;
var rootPath = webHostEnvironment.WebRootPath;
var folder = Path.Combine(rootPath, @"Excels");
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
var fileName = Guid.NewGuid().ToString().Replace("-", "").ToUpper() + file.FileName;
if (!fileName.EndsWith(".xlsx") && !fileName.EndsWith(".xls"))
{
ViewBag.ErrorMessage = "UnCorrect Excel File";
return(View("Index"));
}
var fullPath = Path.Combine(folder, fileName);
using (var fs = new FileStream(fullPath, FileMode.OpenOrCreate))
{
file.CopyTo(fs);
}
ExcelPackage.LicenseContext = LicenseContext.Commercial;
List <DatabaseModel> list = new List <DatabaseModel>();
using (ExcelPackage package = new ExcelPackage(new FileInfo(fullPath)))
{
var worksheet = package.Workbook.Worksheets.FirstOrDefault();
for (int i = 3; i <= worksheet.Dimension.Rows; i++)
{
try
{
var yataginYerleshdiyiErazi = worksheet.Cells[i, 2].Value;
var yataginKodu = worksheet.Cells[i, 3].Value;
var yataginAdi = worksheet.Cells[i, 4].Value;
var saheninKodu = worksheet.Cells[i, 5].Value;
var saheninAdi = worksheet.Cells[i, 6].Value;
var dmaaNomresi = worksheet.Cells[i, 7].Value;
var dmaaQeydiyyatTarixi = worksheet.Cells[i, 8].Value;
var dmaaBitmeTarixi = worksheet.Cells[i, 9].Value;
var senayeMenimsenilmesi = worksheet.Cells[i, 10].Value;
var faydaliQazintiNovu = worksheet.Cells[i, 11].Value;
var ehtiyyatinKategoriyasi = worksheet.Cells[i, 12].Value;
var balansEhtiyyatlari2019 = worksheet.Cells[i, 13].Value;
var hasilat = worksheet.Cells[i, 14].Value;
var hasilatItkileri = worksheet.Cells[i, 15].Value;
var kesfiyyat = worksheet.Cells[i, 16].Value;
var yenidenQiymetlendirme = worksheet.Cells[i, 17].Value;
var moteberliyiTesdiqlenmeyenEhtiyyat = worksheet.Cells[i, 18].Value;
var serhedlerinDeyishmesi = worksheet.Cells[i, 19].Value;
var qaliqEhtiyyatlari2020 = worksheet.Cells[i, 20].Value;
var ayrilanSahe = worksheet.Cells[i, 21].Value;
var voen = worksheet.Cells[i, 22].Value;
var tesdiqEdilmishBalansEhtiyyatCemi = worksheet.Cells[i, 23].Value;
var koordinat = worksheet.Cells[i, 24].Value;
var mineralXammalBazasininBerpasi = worksheet.Cells[i, 25].Value;
var tesdiqOlunmasiBaredeMelumat = worksheet.Cells[i, 26].Value;
var serh = worksheet.Cells[i, 27].Value;
// Fərid Səmədov tərəfindən dəyişdirildi
// Səbəb - Excel də copy/paste zamanı sətrlər "yaradılmış" kimi yadda saxlanılır.
// Nəticədə, həmin sətrlər də mövcud statusu ilə cədvələ əlavə edilir. Aşağıdakı yoxlama ilə bunun qarşısı alınır.
if (yataginYerleshdiyiErazi is null &&
yataginKodu is null &&
yataginAdi is null &&
saheninKodu is null &&
saheninAdi is null &&
dmaaNomresi is null &&
dmaaQeydiyyatTarixi is null &&
dmaaBitmeTarixi is null &&
senayeMenimsenilmesi is null &&
faydaliQazintiNovu is null &&
ehtiyyatinKategoriyasi is null &&
balansEhtiyyatlari2019 is null &&
hasilat is null &&
hasilatItkileri is null &&
kesfiyyat is null &&
yenidenQiymetlendirme is null &&
moteberliyiTesdiqlenmeyenEhtiyyat is null &&
serhedlerinDeyishmesi is null &&
qaliqEhtiyyatlari2020 is null &&
ayrilanSahe is null &&
voen is null &&
tesdiqEdilmishBalansEhtiyyatCemi is null &&
koordinat is null &&
mineralXammalBazasininBerpasi is null &&
tesdiqOlunmasiBaredeMelumat is null &&
serh is null)
{
continue;
}
DatabaseModel databaseModel = new DatabaseModel
{
YataginInzibatiErazisi = yataginYerleshdiyiErazi is null ? "" : yataginYerleshdiyiErazi.ToString(),
YataginAdi = Convert.ToString(yataginAdi),
YataginKodu = Convert.ToString(yataginKodu),
SaheninKodu = Convert.ToString(saheninKodu),
SaheninAdi = Convert.ToString(saheninAdi),
FaydaliQazintiNovu = faydaliQazintiNovu is null ? "" : faydaliQazintiNovu.ToString(),
DMAANomresi = Convert.ToString(dmaaNomresi),
DMAABitmeTarix = dmaaBitmeTarixi is null ? (DateTime?)null : Convert.ToDateTime(dmaaBitmeTarixi),
DMAAQeydiyyatTarixi = dmaaQeydiyyatTarixi is null ? (DateTime?)null : Convert.ToDateTime(dmaaQeydiyyatTarixi),
EhtiyyatinKategoryasi = Convert.ToString(ehtiyyatinKategoriyasi),
SenayeMenimsenilmesiSeviyyesi = Convert.ToString(senayeMenimsenilmesi),
BalansEhtiyyatlari2019 = Convert.ToString(balansEhtiyyatlari2019),
AyrilanSahe = Convert.ToString(ayrilanSahe),
VOEN = Convert.ToString(voen),
Hasilat = Convert.ToString(hasilat),
Koordinat = Convert.ToString(koordinat),
HasilatZamaniItkiler = Convert.ToString(hasilatItkileri),
Kesfiyyat = kesfiyyat is null ? "" : kesfiyyat.ToString(),
YenidenQiymetlendirme = yenidenQiymetlendirme is null ? "" : yenidenQiymetlendirme.ToString(),
MoteberliyiTesdiqlenmeyen = moteberliyiTesdiqlenmeyenEhtiyyat?.ToString(),
SerhedlerinDeyishmesiVeDiger = Convert.ToString(serhedlerinDeyishmesi),
QaliqEhtiyyatlari2020 = Convert.ToString(qaliqEhtiyyatlari2020),
TesdiqEdilmishBalansCemi = Convert.ToString(tesdiqEdilmishBalansEhtiyyatCemi),
MineralXammalBazasiBerpasi = Convert.ToString(mineralXammalBazasininBerpasi),
TesdiqOlunmaseBarede = Convert.ToString(tesdiqOlunmasiBaredeMelumat),
Serh = Convert.ToString(serh)
};
list.Add(databaseModel);
}
catch (Exception)
{
failedCount++;
continue;
}
}
dataContext.ExcelDatas.BatchDelete();
dataContext.BulkInsert(list);
}
return(RedirectToAction("Index"));
}
public static void Init(TestContext context)
{
_pck = OpenPackage("FormControl.xlsm", true);
_pck.Workbook.CreateVBAProject();
_codeModule = _pck.Workbook.VbaProject.Modules.AddModule("ControlEvents");
}
public EpplusExcelDataProvider(ExcelPackage package)
{
_package = package;
_rangeAddressFactory = new RangeAddressFactory(this);
}
private static ExcelWorksheet BuildSheetHeader(ExcelPackage package, FieldInfo item, object att)
{
NecrobotConfigAttribute excelAtt = att as NecrobotConfigAttribute;
ExcelWorksheet workSheet = package.Workbook.Worksheets[excelAtt.SheetName];
if (workSheet == null)
{
var type = item.FieldType;
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Dictionary <,>))
{
var pkmRef = package.Workbook.Worksheets["Pokemons"];
workSheet = package.Workbook.Worksheets.Add(excelAtt.SheetName, pkmRef);
Type keyType = type.GetGenericArguments()[0];
Type valueType = type.GetGenericArguments()[1];
int pos = 1;
workSheet.Cells[1, 1].Value = excelAtt.SheetName;
workSheet.Cells[2, 1].Value = excelAtt.Description;
foreach (var vtp in valueType.GetProperties())
{
var att1 = vtp.GetCustomAttributes <NecrobotConfigAttribute>(true).FirstOrDefault();
int colIndex = (att1 == null ? pos : att1.Position) + COL_OFFSET;
workSheet.Column(colIndex).AutoFit();
workSheet.Cells[4, colIndex].Value = att1 == null ? vtp.Name : att1.Key;
if (att1 != null)
{
workSheet.Cells[4, colIndex].AddComment(att1.Description, "necrobot2");
AddValidationForType(workSheet, vtp, $"{GetCol(colIndex)}5:{GetCol(colIndex)}155");
}
pos++;
}
workSheet.Cells[$"A1:{GetCol(COL_OFFSET + pos)}1"].Merge = true;
workSheet.Cells[$"A2:{GetCol(COL_OFFSET + pos)}2"].Merge = true;
workSheet.Cells[$"A1:{GetCol(COL_OFFSET + pos)}1"].Style.Font.Size = 16;
}
else
{
workSheet = package.Workbook.Worksheets.Add(excelAtt.SheetName);
workSheet.Cells[1, 1].Value = excelAtt.SheetName;
workSheet.Cells[2, 1].Value = excelAtt.Description;
workSheet.Cells[$"A1:C1"].Merge = true;
workSheet.Cells[$"A2:C2"].Merge = true;
workSheet.Cells["A1:C1"].Style.Font.Size = 16;
workSheet.Row(1).CustomHeight = true;
workSheet.Row(1).Height = 30;
workSheet.Cells["A1:C1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
workSheet.Cells["A1:C1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Justify;
workSheet.Cells[4, 1].Value = "Key";
workSheet.Cells[4, 2].Value = "Value";
workSheet.Cells[4, 3].Value = "Description";
}
workSheet.Row(4).Style.Font.Bold = true;
}
return(workSheet);
}
protected void tworzPlikExcell(object sender, EventArgs e)
{
// execel begin
string path = Server.MapPath("Template") + "\\obbc.xlsx";
FileInfo existingFile = new FileInfo(path);
if (!existingFile.Exists)
{
return;
}
string download = Server.MapPath("Template") + @"\obbc";
FileInfo fNewFile = new FileInfo(download + "_.xlsx");
using (ExcelPackage MyExcel = new ExcelPackage(existingFile))
{
ExcelWorksheet MyWorksheet1 = MyExcel.Workbook.Worksheets[1];
// pierwsza
DataTable table1 = (DataTable)Session["tabelka001"];
MyWorksheet1 = tb.tworzArkuszwExcle(MyExcel.Workbook.Worksheets[1], table1, 12, 0, 3, false, false, false, false, false);
//pod tabela
int rowik = table1.Rows.Count - 4;
tb.komorkaExcela(MyWorksheet1, rowik + 7, 1, "Zaległość z poprzedniego miesiąca", true, 0, 4);
tb.komorkaExcela(MyWorksheet1, rowik + 8, 1, "Wpływ", true, 0, 4);
tb.komorkaExcela(MyWorksheet1, rowik + 9, 1, "Załatwienia", true, 0, 4);
tb.komorkaExcela(MyWorksheet1, rowik + 10, 1, "Pozostało na następny miesiąc", true, 0, 4);
tb.komorkaExcela(MyWorksheet1, rowik + 11, 1, "3-6 miesięcy", true, 0, 4);
tb.komorkaExcela(MyWorksheet1, rowik + 12, 1, "6-12 miesięcy", true, 0, 4);
tb.komorkaExcela(MyWorksheet1, rowik + 13, 1, "ponad 12 miesięcy", true, 0, 4);
DataTable tabelka001 = (DataTable)Session["tabelka002"];
foreach (DataRow dR in tabelka001.Rows)
{
for (int i = 2; i < 9; i++)
{
try
{
MyWorksheet1.Cells[rowik + 7, i + 4].Value = double.Parse(dR[i - 1].ToString().Trim());
}
catch
{
MyWorksheet1.Cells[rowik + 7, i + 4].Value = dR[i - 1].ToString().Trim();
}
MyWorksheet1.Cells[rowik + 7, i + 4].Style.ShrinkToFit = true;
MyWorksheet1.Cells[rowik + 7, i + 4].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin, System.Drawing.Color.Black);
}
rowik++;
}
DataTable table2 = (DataTable)Session["tabelka003"];
cm.log.Info("oobc trzecia tabela ilosc wierszy " + table2.Rows.Count.ToString());
MyWorksheet1 = tb.tworzArkuszwExcleBezSedziow(MyExcel.Workbook.Worksheets[2], table2, 2, 6, 2, 4, false);
// trzecia
DataTable table3 = (DataTable)Session["tabelka004"];
MyWorksheet1 = tb.tworzArkuszwExcle(MyExcel.Workbook.Worksheets[3], table3, 4, 0, 3, false, false, false, false, false);
// czwarta
DataTable table4 = (DataTable)Session["tabelka005"];
cm.log.Info("oobc trzecia tabela ilosc wierszy " + table4.Rows.Count.ToString());
MyWorksheet1 = tb.tworzArkuszwExcle(MyExcel.Workbook.Worksheets[4], table4, 9, 0, 3, false, false, false, false, false);
try
{
MyExcel.SaveAs(fNewFile);
this.Response.Clear();
this.Response.ContentType = "application/vnd.ms-excel";
this.Response.AddHeader("Content-Disposition", "attachment;filename=" + fNewFile.Name);
this.Response.WriteFile(fNewFile.FullName);
this.Response.End();
}
catch (Exception ex)
{
cm.log.Error(tenPlik + " " + ex.Message);
}
}//end of using
}
private void btnProcurarArquivo_Click(object sender, EventArgs e)
{
if (ofdArquivo.ShowDialog() != DialogResult.OK)
{
return;
}
edtArquivo.Text = ofdArquivo.FileName;
items.Clear();
using (var arquivo = new ExcelPackage(new FileInfo(ofdArquivo.FileName)))
using (var worksheet = arquivo.Workbook.Worksheets[1])
using (var conexao = new Connection(Database.Local))
{
int colCount = worksheet.Dimension.End.Column;
int rowCount = worksheet.Dimension.End.Row;
for (int row = 2; row <= rowCount; row++)
{
DateTime dataNascimento;
DateTime.TryParse(worksheet.Cells[row, 6].Value?.ToString().Trim(), out dataNascimento);
int idCidade = 0;
var cidade = new Cidade();
if (cidade.GetByCidadeAndUf(worksheet.Cells[row, 19].Value?.ToString().Trim(), worksheet.Cells[row, 20].Value?.ToString().Trim(), null))
{
idCidade = cidade.Id;
}
var item = new ImportacaoClienteTO();
item.Cliente.Nome = worksheet.Cells[row, 4].Value?.ToString().Trim() + " " + worksheet.Cells[row, 5].Value?.ToString().Trim();
item.Cliente.DataNascimento = dataNascimento;
item.Cliente.Cpf = worksheet.Cells[row, 7].Value?.ToString().Trim().Replace(".", String.Empty).Replace("-", String.Empty);
item.Cliente.Email = worksheet.Cells[row, 8].Value?.ToString().Trim();
item.Cliente.Telefone = worksheet.Cells[row, 9].Value?.ToString().Trim() + worksheet.Cells[row, 10].Value?.ToString().Trim();
item.Cliente.Celular = worksheet.Cells[row, 11].Value?.ToString().Trim() + worksheet.Cells[row, 12].Value?.ToString().Trim();
item.Cliente.Cep = worksheet.Cells[row, 13].Value?.ToString().Trim();
item.Cliente.TipoLogradouro = worksheet.Cells[row, 14].Value?.ToString().Trim();
item.Cliente.Logradouro = worksheet.Cells[row, 15].Value?.ToString().Trim();
item.Cliente.Numero = worksheet.Cells[row, 16].Value?.ToString().Trim();
item.Cliente.Complemento = worksheet.Cells[row, 17].Value?.ToString().Trim();
item.Cliente.Bairro = worksheet.Cells[row, 18].Value?.ToString().Trim();
item.Cliente.IdCidade = idCidade;
if (idCidade != 0)
{
item.Cidade = cidade;
}
item.Tatuagem.Local = worksheet.Cells[row, 21].Value?.ToString();
DateTime dataUltimaSessao;
DateTime.TryParse(string.IsNullOrEmpty(worksheet.Cells[row, 3].Value?.ToString()) ? worksheet.Cells[row, 2].Value?.ToString().Trim() : worksheet.Cells[row, 3].Value?.ToString().Trim(), out dataUltimaSessao);
Decimal valorSessao;
Decimal.TryParse(worksheet.Cells[row, 1].Value?.ToString(), out valorSessao);
item.Sessao.DataSessao = dataUltimaSessao;
item.Sessao.Valor = valorSessao;
item.Sessao.Parametros = worksheet.Cells[row, 22].Value?.ToString();
item.Sessao.Disparos = worksheet.Cells[row, 23].Value?.ToString();
item.Sessao.Pago = true;
for (int col = 24; col <= colCount; col++)
{
Pergunta pergunta = Pergunta.GetAtivaByCodigoImportacao(worksheet.Cells[1, col].Value?.ToString().Trim(), conexao, null);
if (pergunta == null)
{
continue;
}
var resposta = new Resposta()
{
IdPergunta = pergunta.Id
};
if (pergunta.Dissertativa)
{
resposta.RespostaDissertativa = worksheet.Cells[row, col].Value?.ToString().Trim();
}
else
{
Alternativa alternativa = Alternativa.GetAtivaByIdPerguntaAndDescricao(pergunta.Id, worksheet.Cells[row, col].Value?.ToString().Trim(), conexao, null);
if (alternativa == null)
{
continue;
}
resposta.IdAlternativa = alternativa.Id;
}
item.Respostas.Add(resposta);
}
items.Add(item);
}
}
lblQtdeClientesCarregados.Text = items.Count.ToString();
lblClientesCarregados.Visible = true;
lblQtdeClientesCarregados.Visible = true;
}
private void DownloadExcel(string PoNo, string VendorName, DateTime Date, List <CalculationQuotation> QuotationList)
{
//Step 1. 寫入EXCEL
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
var ExcelPath = ConfigurationManager.AppSettings["DowloadDirectory"];
var DownloadPath = new FileInfo(ExcelPath);
using (var Excel = new ExcelPackage(DownloadPath))
{
ExcelWorksheet WorkSheet = Excel.Workbook.Worksheets["PO報價"];
//Step 1-1.把數量寫入計價單
foreach (var QuotationItem in QuotationList)
{
if (QuotationItem.Count != 0)
{
switch (QuotationItem.PoClassID)
{
#region 把數量寫入計價單
case "10-1":
WorkSheet.Cells[9, 7].Value = QuotationItem.Count;
break;
case "10-2":
WorkSheet.Cells[10, 7].Value = QuotationItem.Count;
break;
case "10-3":
WorkSheet.Cells[11, 7].Value = QuotationItem.Count;
break;
case "10-4":
WorkSheet.Cells[12, 7].Value = QuotationItem.Count;
break;
case "10-5":
WorkSheet.Cells[13, 7].Value = QuotationItem.Count;
break;
case "10-6":
WorkSheet.Cells[14, 7].Value = QuotationItem.Count;
break;
case "10-7":
WorkSheet.Cells[15, 7].Value = QuotationItem.Count;
break;
case "10-8":
WorkSheet.Cells[16, 7].Value = QuotationItem.Count;
break;
case "10-9":
WorkSheet.Cells[17, 7].Value = QuotationItem.Count;
break;
case "10-10":
WorkSheet.Cells[18, 7].Value = QuotationItem.Count;
break;
case "10-11":
WorkSheet.Cells[19, 7].Value = QuotationItem.Count;
break;
case "10-12":
WorkSheet.Cells[20, 7].Value = QuotationItem.Count;
break;
case "10-13":
WorkSheet.Cells[21, 7].Value = QuotationItem.Count;
break;
case "10-14":
WorkSheet.Cells[22, 7].Value = QuotationItem.Count;
break;
case "10-15":
WorkSheet.Cells[23, 7].Value = QuotationItem.Count;
break;
case "10-16":
WorkSheet.Cells[24, 7].Value = QuotationItem.Count;
break;
case "10-17":
WorkSheet.Cells[25, 7].Value = QuotationItem.Count;
break;
case "10-18":
WorkSheet.Cells[26, 7].Value = QuotationItem.Count;
break;
case "10-19":
WorkSheet.Cells[27, 7].Value = QuotationItem.Count;
break;
case "10-20":
WorkSheet.Cells[28, 7].Value = QuotationItem.Count;
break;
case "10-21":
WorkSheet.Cells[29, 7].Value = QuotationItem.Count;
break;
case "10-22":
WorkSheet.Cells[30, 7].Value = QuotationItem.Count;
break;
case "10-23":
WorkSheet.Cells[31, 7].Value = QuotationItem.Count;
break;
case "10-24":
WorkSheet.Cells[32, 7].Value = QuotationItem.Count;
break;
case "10-25":
WorkSheet.Cells[33, 7].Value = QuotationItem.Count;
break;
default:
break;
#endregion
}
}
}
//Vendor
WorkSheet.Cells[5, 8].Value = VendorName;
//PO
WorkSheet.Cells[7, 8].Value = PoNo;
//申請月份
//WorkSheet.Cells[9, 1].Value = Date.ToString("yyyy/MM");
Excel.Save();
}
}
public async Task <IActionResult> ImportEventParticipants([FromForm] EventParticipant eventParticipant)
{
BaseResult <UserModel> baseResult = new BaseResult <UserModel>();
bool isSuccess = false;
List <User> users = _SUser.GetAllUser();
List <User> excelFileUsers = new List <User>();
List <EventParticipant> rels = new List <EventParticipant>();
Dictionary <string, string> hashedValue = new Dictionary <string, string>();
int id = Convert.ToInt32(HttpContext.User.Identity.Name);
using (MemoryStream ms = new MemoryStream())
{
IFormFile formFile = eventParticipant.importUserFile[0];
await formFile.CopyToAsync(ms);
using (ExcelPackage excelPackage = new ExcelPackage(ms))
{
var workSheet = excelPackage.Workbook.Worksheets.First();
int rowCount = workSheet.Dimension.End.Row;
int userTypeId = Convert.ToInt32(_SMethod.GetEnumValue(enumUserType.user));
for (int row = 1; row <= rowCount; row++)
{
var emailAddress = workSheet.Cells[row, 1].Value;
if (emailAddress != null)
{
bool emailVerification = _SMethod.ValidateEmail(emailAddress.ToString());
if (emailVerification)
{
User _user = new User();
string guid = Guid.NewGuid().ToString();
string _password = "";
for (int i = 0; i < 8; i++)
{
_password += guid[i];
}
_user.email = emailAddress.ToString();
_user.name = _user.surname = "";
_user.avatarPath = "";
_user.phoneNr = _user.taxNr = "";
_user.userTypeId = userTypeId;
_user.userGuid = Guid.NewGuid().ToString();
_user.identityNr = "";
_user.password = _SMethod.StringToMd5(_password);
_user.emailVerification = 1;
_user.profileStatus = 1;
_user.notificationStatus = 2;
_user.statusId = 2;
_user.creationDate = _user.birthDate = DateTime.Now;
_user.gender = Convert.ToInt32(_SMethod.GetEnumValue(enumGenderType.Belirtilmemiş));
if (excelFileUsers.FirstOrDefault(x => x.email.Equals(emailAddress)) == null)
{
excelFileUsers.Add(_user);
hashedValue.TryAdd(emailAddress.ToString(), _password);
}
}
}
}
List <User> usersNotIn = excelFileUsers.Where(x => !users.Any(y => y.email.Equals(x.email))).ToList();
foreach (var item in usersNotIn)
{
int userId = _SUser.InsertUser(item);
rels.Add(new EventParticipant
{
eventId = eventParticipant.eventId,
userId = userId,
creatorId = id,
creationDate = DateTime.Now,
statusId = 2
});
string _password = "";
hashedValue.TryGetValue(item.email, out _password);
if (!String.IsNullOrEmpty(_password))
{
EmailVerificationQueueModel emailVerificationQueueModel = new EmailVerificationQueueModel()
{
email = item.email,
userGuid = item.userGuid
};
PasswordQueueModel passwordQueue = new PasswordQueueModel()
{
email = item.email,
password = _password
};
await notificationDispatcher.SendPassword(passwordQueue);
await notificationDispatcher.SendEmailVerification(emailVerificationQueueModel);
}
}
List <User> usersIn = excelFileUsers.Where(x => users.Any(y => y.email.Equals(x.email))).ToList();
foreach (var item in usersIn)
{
rels.Add(new EventParticipant
{
eventId = eventParticipant.eventId,
userId = item.id,
creatorId = id,
creationDate = DateTime.Now,
statusId = 2
});
}
List <User> participants = new List <User>();
participants.AddRange(usersNotIn);
participants.AddRange(usersIn);
isSuccess = _SEventParticipant.BulkInsertParticipants(rels);
baseResult.data.users = participants;
}
}
if (isSuccess)
{
return(Json(baseResult));
}
else
{
baseResult.statusCode = HttpStatusCode.NotFound;
return(new NotFoundObjectResult(baseResult));
}
}
private void btnModeloImportacao_Click(object sender, EventArgs e)
{
string fileName = String.Empty;
using (var saveFileDialog = new SaveFileDialog())
{
saveFileDialog.Filter = "Arquivo Ecxel|*.xls";
saveFileDialog.DefaultExt = "xls";
if (saveFileDialog.ShowDialog() != DialogResult.OK)
{
return;
}
fileName = saveFileDialog.FileName;
}
using (var file = new ExcelPackage())
using (var sheet = file.Workbook.Worksheets.Add("Modelo"))
{
sheet.Cells[1, 1].Value = "valor_pago";
sheet.Cells[1, 2].Value = "data_primeira_visita";
sheet.Cells[1, 3].Value = "data_ultima_visita";
sheet.Cells[1, 4].Value = "nome";
sheet.Cells[1, 5].Value = "sobrenome";
sheet.Cells[1, 6].Value = "data_nascimento";
sheet.Cells[1, 7].Value = "cpf";
sheet.Cells[1, 8].Value = "email";
sheet.Cells[1, 9].Value = "ddd_telefone";
sheet.Cells[1, 10].Value = "telefone";
sheet.Cells[1, 11].Value = "ddd_celular";
sheet.Cells[1, 12].Value = "celular";
sheet.Cells[1, 13].Value = "cep";
sheet.Cells[1, 14].Value = "tipo_logradouro";
sheet.Cells[1, 15].Value = "logradouro";
sheet.Cells[1, 16].Value = "numero";
sheet.Cells[1, 17].Value = "complemento";
sheet.Cells[1, 18].Value = "bairro";
sheet.Cells[1, 19].Value = "cidade";
sheet.Cells[1, 20].Value = "estado";
sheet.Cells[1, 21].Value = "area_corpo";
sheet.Cells[1, 22].Value = "parametros";
sheet.Cells[1, 23].Value = "quantidade_disparos";
using (var conexao = new Connection(Database.Local))
{
List <Pergunta> perguntas = Pergunta.GetAllAtivas(conexao, null);
int i = 0;
foreach (Pergunta pergunta in perguntas)
{
sheet.Cells[1, 24 + i].Value = pergunta.CodigoImportacao;
i++;
}
}
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
sheet.Cells[sheet.Dimension.Address].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
file.SaveAs(new FileInfo(fileName));
}
MessageBox.Show("Arquivo salvo com sucesso no local selecionado", "Sucesso", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
static void Main(string[] args)
{
// Test paths
//string excelFilePath = "C:\\Users\\kflor\\OneDrive\\Desktop\\Example.xlsx";
//string txtFilePath = "C:\\Users\\kflor\\OneDrive\\Desktop\\Test.txt";
//_startLineOffset = 15000;
// Command line argument section
#region Command line
_log.Info($"Number of arguments passed for this execution: {args.Length}");
for (int i = 0; i < args.Length; ++i)
{
_log.Info($"Arg #{i + 1}: {args[i]}");
}
if (args.Length < 1)
{
_log.Error("No arguments passed.");
return;
}
if (args.Length < 2)
{
_log.Error("Only one argument passed.");
return;
}
// Should be at least 2 arguments which will be the paths to the two files
string excelFilePath = args[0];
string txtFilePath = args[1];
// This is to capture the optional third parameter, Work order number
if (args.Length > 2)
{
WO = args[2];
}
if (!File.Exists(excelFilePath))
{
_log.Error($"File does not exist or path invalid: {args[0]}");
return;
}
if (!File.Exists(txtFilePath))
{
_log.Error($"File does not exist or path invalid: {args[1]}");
return;
}
#endregion Command line
// Read in text file section
#region Text File
// Open text file and read all lines
string[] lines;
try
{
lines = File.ReadAllLines(txtFilePath);
}
catch (Exception ex)
{
_log.Error($"Exception thrown when reading text file: {ex.Message.ToString()}");
return;
}
#endregion
//create a fileinfo object of an excel file on the disk (file must exist)
FileInfo file;
try
{
file = new FileInfo(excelFilePath);
}
catch (Exception ex)
{
_log.Error($"Exception thrown when creating file object of excel file. {ex.Message.ToString()}");
return;
}
#region Create Excel library object
// Create a new Excel package from the file
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
if (excelPackage.Workbook.Worksheets[1] == null)
{
_log.Error("Worksheet does not exist..");
return;
}
// Get a WorkSheet by index. Note that EPPlus indexes start at 1
ExcelWorksheet firstWorksheet = excelPackage.Workbook.Worksheets["DataEntry"];
ExcelWorksheet secondWorksheet = excelPackage.Workbook.Worksheets["WoStat"];
// Find first unused row with no data entered
while (firstWorksheet.Cells[_startLineOffset, 2].Value != null)
{
++_startLineOffset;
}
_log.Info($"Writing to worksheet, starting at row: {_startLineOffset}");
// Parse Strings and input data
#region
for (int textFileLine = 0; textFileLine < lines.Length; ++textFileLine)
{
string[] words = lines[textFileLine].Split(',');
if (words.Length > 2 && WO == "")
{
WO = words[2];
}
for (int wordsIndex = 0, column = 1; wordsIndex < words.Length && column <= _maxColumn; ++wordsIndex, ++column)
{
while (_skipPositions.Contains(column))
{
++column;
}
if (_doublePositions.Contains(column))
{
// If it is a position that is suppose to be a double
if (Double.TryParse(words[wordsIndex], out double number))
{
firstWorksheet.Cells[_startLineOffset + textFileLine, column].Value = number;
continue;
}
else
{
_log.Error($"Index: {wordsIndex} was expected to be a double value, inserted instead as a text value");
}
}
// Default the rest to text values
firstWorksheet.Cells[_startLineOffset + textFileLine, column].Value = words[wordsIndex];
}
}
#endregion
if (WO != "")
{
int lineNum = 1;
// Find line that has work order number on it
try
{
// Find line that has work order number on it
while (secondWorksheet.Cells[lineNum, 1].Value.ToString() != WO)
{
++lineNum;
}
// Read data from that row
string dueDate = secondWorksheet.Cells["G" + lineNum.ToString()].Value.ToString();
string customer = secondWorksheet.Cells["CS" + lineNum.ToString()].Value.ToString();
string heat = secondWorksheet.Cells["CT" + lineNum.ToString()].Value.ToString();
string alloyGrade = secondWorksheet.Cells["EG" + lineNum.ToString()].Value.ToString();
string alloyHeatTreat = secondWorksheet.Cells["EH" + lineNum.ToString()].Value.ToString();
string finalST = secondWorksheet.Cells["EI" + lineNum.ToString()].Value.ToString();
string stTolerance = secondWorksheet.Cells["EJ" + lineNum.ToString()].Value.ToString();
string finalLT = secondWorksheet.Cells["EK" + lineNum.ToString()].Value.ToString();
string ltTolerance = secondWorksheet.Cells["EL" + lineNum.ToString()].Value.ToString();
string finalLength = secondWorksheet.Cells["EM" + lineNum.ToString()].Value.ToString();
string lengthTolerance = secondWorksheet.Cells["EN" + lineNum.ToString()].Value.ToString();
// Write data to text file
using (StreamWriter sw = new StreamWriter(Path.GetDirectoryName(txtFilePath) + "\\" + dataFile))
{
// Enter required data to textfile
sw.WriteLine($"DueDate={dueDate}");
sw.WriteLine($"Customer={customer}");
sw.WriteLine($"Heat={heat}");
sw.WriteLine($"AlloyGrade={alloyGrade}");
sw.WriteLine($"AllowHeatTreatCondition={alloyHeatTreat}");
sw.WriteLine($"FinalSTDimension={finalST}");
sw.WriteLine($"STTolerance={stTolerance}");
sw.WriteLine($"FinalLTDimension={finalLT}");
sw.WriteLine($"LTTolerance={ltTolerance}");
sw.WriteLine($"FinalLength={finalLength}");
sw.WriteLine($"LengthTolerance={lengthTolerance}");
}
}
catch (Exception ex)
{
_log.Error($"Exception: {ex.Message.ToString()}");
}
}
try
{
//save the changes
excelPackage.Save();
}
catch (Exception ex)
{
_log.Error($"Exception when saving excel document: {ex.Message.ToString()}");
}
}
#endregion
}
