public List<DeveloperModel> GetDevelopersFromExcel(IFormFile file) { ExcelUpload<DeveloperModel> excel = new ExcelUpload<DeveloperModel>(); string folderName = "Upload"; string webRootPath = environment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); string[] allowedColumns = { "name", "skills", "keywords", "previousworks", "profilelink", "previouswork", "product", "component", "hardware" }; return excel.ImportFromExcel(file, newPath, allowedColumns, (string colName, string colValue, DeveloperModel developer) => { if (colName == "name") { developer.DeveloperName = colValue; } if (colName == "product") { developer.ProductName = colValue; } if (colName == "component") { developer.ComponentName = colValue; } if (colName == "hardware") { developer.HardwareName = colValue; } if (colName == "previousworks" || colName == "previouswork" || colName == "profilelink") { developer.PreviousWorks = colValue; } if (colName == "keywords" || colName == "skills") { string[] skills = colValue.Split(','); string processedSkill = ""; foreach (var skill in skills) { var keyword = TextFormaterTool.DataPreprocess(skill); if (keyword == null || keyword == "") continue; processedSkill += keyword + ","; } developer.Skills = processedSkill.TrimEnd(','); } }); }
public void TestParseColumnsHeaders() { MockObjectRepository repo = new MockObjectRepository(); BulkCopyController controller = new BulkCopyController(repo); ExcelUpload uploadModel = new ExcelUpload() { ExcelFile = this.exampleContent, FirstRowIsHeader = true }; List <ExcelSheet> sheets = controller.Parse(uploadModel); Assert.IsNotNull(sheets); Assert.AreEqual(2, sheets.Count); var firstSheet = sheets[0]; var secondSheet = sheets[1]; Assert.IsNotNull(firstSheet.Columns); Assert.AreEqual(4, firstSheet.Columns.Count); Assert.AreEqual("A", firstSheet.Columns[0].Letter); Assert.AreEqual("Name", firstSheet.Columns[0].Name); Assert.AreEqual("B", firstSheet.Columns[1].Letter); Assert.AreEqual("OID", firstSheet.Columns[1].Name); Assert.IsNotNull(secondSheet.Columns); Assert.AreEqual(15, secondSheet.Columns.Count); Assert.AreEqual("A", secondSheet.Columns[0].Letter); Assert.AreEqual("Template", secondSheet.Columns[0].Name); Assert.AreEqual("B", secondSheet.Columns[1].Letter); Assert.AreEqual("Number", secondSheet.Columns[1].Name); }
public async Task <IActionResult> DeleteExcelData([FromBody] ExcelUpload excelupload) { ExcelUpload tableUploadExcel = _context.ExcelUploads.Find(excelupload.Id); _context.ExcelUploads.Remove(tableUploadExcel); await _context.SaveChangesAsync(); return(RedirectToAction("Index")); }
/// <summary> /// 로그인 버튼 이미지 클릭 이벤트 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void ImgLoginBtn_PreviewMouseLeftButtonUp(object sender, MouseButtonEventArgs e) { this.imgLoginBtn.Source = new BitmapImage(new Uri(CLICK_IMAGE_PATH)); try { using (ExcelUpload popup = new ExcelUpload(EnumClass.ExcelUploadKind.ORD_INFO)) { popup.ShowDialog(); } var strUserID = this.txtUserID.Text.Trim(); // 사용자 ID var strPwd = this.txtPwd.Text.Trim(); // 비밀번호 if (strUserID.Length == 0) { this.BaseClass.MsgError("ERR_EMPTY_USER_ID"); this.txtUserID.Focus(); return; } if (strPwd.Length == 0) { this.BaseClass.MsgError("ERR_EMPTY_PWD_NM"); this.txtPwd.Focus(); return; } var query = this.g_dtDatabaseConnectionInfo.AsEnumerable().Where(p => p.Field <string>("DB_CONN_TYPE").Equals(this.g_strConfigDBConnectType)).FirstOrDefault(); if (query == null) { // ERR_NOT_EXIST_DATABASE_CONNECT_STRING - 데이터베이스 연결 문자열이 존재하지 않습니다. this.BaseClass.MsgError("ERR_NOT_EXIST_DATABASE_CONNECT_STRING"); return; } else { this.BaseClass.DatabaseConnectionString_ORACLE = query.Field <string>("ORCL_CONN_STR"); // 오라클 연결 문자열 this.BaseClass.DatabaseConnectionString_MSSQL = query.Field <string>("MS_CONN_STR"); // MS-SQL 연결 문자열 this.BaseClass.DatabaseConnectionString_MariaDB = query.Field <string>("MR_CONN_STR"); // MariaDB 연결 문자열 } // 로그인 처리 var iResult = this.GetSP_LOGIN_LIST_INQ(strUserID, strPwd); if (iResult == 0) { this.LoginProcess(strUserID); } } catch (Exception err) { this.BaseClass.Error(err); } }
public async Task <IActionResult> PostExcelUpdatedData([FromBody] ExcelUpload excelupload) { ExcelUpload tableUploadExcel = _context.ExcelUploads.Find(excelupload.Id); tableUploadExcel.ApplicationUserId = HttpContext.User.FindFirstValue(ClaimTypes.NameIdentifier); tableUploadExcel.Edited = true; tableUploadExcel.ModifiedDate = DateTime.Now; tableUploadExcel.ExcelData = excelupload.ExcelData; await _context.SaveChangesAsync(); return(RedirectToAction("Index")); }
protected void mainPanel_Callback(object source, DevExpress.Web.CallbackEventArgsBase e) { SortedList uploadagency = new SortedList(); ExcelUpload oExcelUpload = new ExcelUpload(conn); object[] param; NameValueCollection nvcAuto = new NameValueCollection(); NameValueCollection nvcAutoField = new NameValueCollection(); int idxAuto = 0; string stage = Request.QueryString["atype"]; string assigntrack = "1.1"; string assigntrackfrom = "1.0"; string assigntype = "1"; string stagequota = stage; string nexttrack = ""; regno = grid.GetSelectedFieldValues("__KeyField"); if (e.Parameter == "confirm") { try { generate("NONPV"); } catch { } } //for (int i = 0; i < selList.Items.Count; i++) for (int i = 0; i < regno.Count; i++) { string sendto = SendTo.SelectedValue; switch (e.Parameter) { case "confirm": grid.Selection.UnselectAll(); param = new object[] { regno[i].ToString(), "NONPV", USERID }; conn.ExecuteNonQuery("EXEC SP_SENDTOAGENCY @1, @2, @3", param, dbtimeout); break; case "send": param = new object[] { regno[i].ToString(), sendto, USERID }; conn.ExecuteNonQuery("EXEC SP_FLAG_SENDTOAGENCY @1, @2, @3", param, dbtimeout); break; } } ListSys.gridBind(grid, (string)ViewState["strSQL"], UC_ListFilter1.paramFilter, UC_ListFilter1.strFilter, conn); }
public async Task <IActionResult> PostExcelData([FromBody] ExcelUpload excelupload) { var excelNmArr = excelupload.ExcelName.Split('.'); var excelNm = excelNmArr[0]; var excelNmExt = excelNmArr[excelNmArr.Length - 1]; excelupload.ExcelName = excelNm + DateTime.Now.ToString("yyyyMMddHHmmss") + '.' + excelNmExt; excelupload.ApplicationUserId = HttpContext.User.FindFirstValue(ClaimTypes.NameIdentifier); excelupload.CreatedDate = DateTime.Now; excelupload.Edited = false; excelupload.ModifiedDate = DateTime.Now; _context.ExcelUploads.Add(excelupload); await _context.SaveChangesAsync(); return(RedirectToAction("Index")); }
public void TestParseRowsWithHeaders() { MockObjectRepository repo = new MockObjectRepository(); BulkCopyController controller = new BulkCopyController(repo); ExcelUpload uploadModel = new ExcelUpload() { ExcelFile = this.exampleContent, FirstRowIsHeader = true }; List <ExcelSheet> sheets = controller.Parse(uploadModel); Assert.IsNotNull(sheets); Assert.AreEqual(2, sheets.Count); var firstSheet = sheets[0]; Assert.IsNotNull(firstSheet.Rows); Assert.AreEqual(3, firstSheet.Rows.Count); var firstSheetFirstRow = firstSheet.Rows[0]; Assert.IsNotNull(firstSheetFirstRow.Cells); Assert.AreEqual(4, firstSheetFirstRow.Cells.Count); Assert.AreEqual("A", firstSheetFirstRow.Cells[0].Letter); Assert.AreEqual("Template 1", firstSheetFirstRow.Cells[0].Value); Assert.AreEqual("B", firstSheetFirstRow.Cells[1].Letter); Assert.AreEqual("1.2.3.4", firstSheetFirstRow.Cells[1].Value); Assert.AreEqual("B", firstSheetFirstRow.Cells[1].Letter); Assert.AreEqual("1.2.3.4", firstSheetFirstRow.Cells[1].Value); var secondSheet = sheets[1]; }
public List <ExcelSheet> Parse(ExcelUpload uploadModel) { List <ExcelSheet> returnSheets = new List <ExcelSheet>(); using (MemoryStream ms = new MemoryStream(uploadModel.ExcelFile)) { SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(ms, false); WorkbookPart wbPart = spreadsheet.WorkbookPart; var sheets = wbPart.Workbook.Descendants <Sheet>(); foreach (Sheet sheet in sheets) { ExcelSheet newReturnSheet = new ExcelSheet(); WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id)); Worksheet worksheet = wsPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); newReturnSheet.SheetName = sheet.Name; var firstRowCells = worksheet.Descendants <Cell>().Where(y => GetCellRow(y.CellReference.Value) == 1); foreach (var firstRowCell in firstRowCells) { ExcelColumn newReturnColumn = new ExcelColumn() { Letter = GetCellLetter(firstRowCell.CellReference.Value), Name = GetCellLetter(firstRowCell.CellReference.Value) }; if (uploadModel.FirstRowIsHeader) { newReturnColumn.Name = GetCellValue(firstRowCell, wbPart); } newReturnSheet.Columns.Add(newReturnColumn); } var rows = sheetData.Descendants <Row>(); foreach (var row in rows) { ExcelRow newReturnRow = new ExcelRow() { RowNumber = (int)row.RowIndex.Value }; if (uploadModel.FirstRowIsHeader && newReturnRow.RowNumber == 1) { continue; } foreach (ExcelColumn column in newReturnSheet.Columns) { var cellReference = string.Format("{0}{1}", column.Letter, newReturnRow.RowNumber); Cell cell = worksheet.Descendants <Cell>().SingleOrDefault(y => y.CellReference.Value == cellReference); ExcelCell newReturnCell = new ExcelCell() { Letter = column.Letter, Value = GetCellValue(cell, wbPart) }; newReturnRow.Cells.Add(newReturnCell); } newReturnSheet.Rows.Add(newReturnRow); } returnSheets.Add(newReturnSheet); } } return(returnSheets); }
public async Task <IActionResult> GetExcelDetails(string excelid) { ExcelUpload excelUpload = await _context.ExcelUploads.FindAsync(int.Parse(excelid)); return(Json(excelUpload)); }
public List<BugModel> GetBugsFromExcel(IFormFile file) { ExcelUpload<BugModel> excel = new ExcelUpload<BugModel>(); string folderName = "Upload"; string webRootPath = environment.WebRootPath; string newPath = Path.Combine(webRootPath, folderName); string[] allowedColumns = { "product", "component", "severity", "priority", "summary", "description", "hardware", "assignee", "status", "resolution", "id", "changed" }; return excel.ImportFromExcel(file, newPath ,allowedColumns,(string coulumnName, string columnValue,BugModel bug) => { if (coulumnName == "assignee") { bug.AssigneeName = columnValue; } if ( coulumnName == "product") { bug.ProductName = columnValue; } if ( coulumnName == "component") { bug.ComponentName = columnValue; } if (coulumnName == "hardware") { bug.HardwareName = columnValue; } if (coulumnName == "severity") { string severityFromExcel = columnValue.Trim().ToLower(); bug.SeverityTitle = severityFromExcel; } if (coulumnName == "priority") { string priorityFromExcel =columnValue.Trim().ToLower(); bug.PriorityTitle = priorityFromExcel; } if (coulumnName == "summary" ||coulumnName == "description") { string colValue = columnValue.Replace("\'", String.Empty) .Replace("{", string.Empty) .Replace("}", string.Empty) .Replace("\"", String.Empty); var keyword = TextFormaterTool.DataPreprocess(colValue); bug.KeyWords = bug.KeyWords + keyword; } }); }