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;
}
});
}
