private ReadExcelFileModel _ReadExcelFile(string filename)
{
var listPrev = new List<UpdateKpiAchievementsViewModel.KpiAchievementItem>();
var response = new ReadExcelFileModel();
var file = Server.MapPath(filename);
Workbook workbook = new Workbook();
using (FileStream stream = new FileStream(file, FileMode.Open))
{
workbook.LoadDocument(stream, DocumentFormat.OpenXml);
foreach (var worksheet in workbook.Worksheets)
{
string[] name = worksheet.Name.Split('_');
//if (name.Count() > 0 && name[0] != "Sheet1" && (name[0] == PeriodeType.Daily.ToString() || name[0] == PeriodeType.Hourly.ToString() || name[0] == PeriodeType.Monthly.ToString() || name[0] == PeriodeType.Weekly.ToString() || name[0] == PeriodeType.Yearly.ToString()))
if(name[0] == "Daily" || name[0] == "Monthly" || name[0]== "Yearly")
{
string periodType = name[0];
PeriodeType pType = string.IsNullOrEmpty(periodType)
? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), periodType);
string period = name[name.Count() - 1];
string[] periodes = null;
int tahun, bulan;
//validate and switch value by periodType
if (periodType != period && !string.IsNullOrEmpty(period))
{
switch (periodType)
{
case "Daily":
periodes = period.Split('-');
tahun = int.Parse(periodes[0]);
bulan = int.Parse(periodes[periodes.Count() - 1]);
break;
case "Monthly":
tahun = int.Parse(period);
break;
case "Yearly":
default:
break;
}
}
//coba baca value
workbook.Worksheets.ActiveWorksheet = worksheet;
//get row
Range range = worksheet.GetUsedRange();
int rows = range.RowCount;
int column = range.ColumnCount - 2;
int Kpi_Id = 0;
DateTime periodData = new DateTime();
double? nilai = null;
for (int i = 1; i < rows; i++)
{
//get rows
for (int j = 0; j < column; j++)
{
var prepareDataContainer = new UpdateKpiAchievementsViewModel.KpiAchievementItem();
//get rows header and period
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
Kpi_Id = int.Parse(worksheet.Cells[i, j].Value.ToString());
}
}
else if (j > 1)
{
if (worksheet.Cells[0, j].Value.Type == CellValueType.DateTime)
{
periodData = DateTime.Parse(worksheet.Cells[0, j].Value.ToString());
}
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
nilai = double.Parse(worksheet.Cells[i, j].Value.ToString());
}
else
{
nilai = null;
}
if (nilai != null)
{
prepareDataContainer.Value = nilai;
prepareDataContainer.KpiId = Kpi_Id;
prepareDataContainer.Periode = periodData;
prepareDataContainer.PeriodeType = pType;
var oldKpiAchievement = _kpiAchievementService.GetKpiAchievementByValue(new GetKpiAchievementRequestByValue { Kpi_Id = Kpi_Id, periode = periodData, PeriodeType = periodType });
if (oldKpiAchievement.IsSuccess)
{
prepareDataContainer.Id = oldKpiAchievement.Id;
}
var request = prepareDataContainer.MapTo<UpdateKpiAchievementItemRequest>();
_kpiAchievementService.UpdateKpiAchievementItem(request);
}
//listPrev.Add(prepareDataContainer);
}
}
}
//DataTable dataTable = worksheet.CreateDataTable(range, true);
//for (int col = 0; col < range.ColumnCount; col++)
//{
// //CellValueType cellType = range[0, col].Value.Type;
// for (int r = 1; r < range.RowCount; r++)
// {
// //if (cellType != range[r, col].Value.Type)
// //{
// // dataTable.Columns[col].DataType = typeof(string);
// // break;
// //}
// }
//}
response.isSuccess = true;
}
else
{
response.isSuccess = false;
response.Message = "File Not Valid";
break;
}
}
}
return response;
}
private ReadExcelFileModel _ReadExcelFile(string filename)
{
var response = new ReadExcelFileModel();
try
{
int inserted = 0;
int skipped = 0;
int rejected = 0;
var listPrev = new List<UpdateKpiAchievementsViewModel.KpiAchievementItem>();
var file = Server.MapPath(filename);
var userId = UserProfile().UserId;
Workbook workbook = new Workbook();
using (FileStream stream = new FileStream(file, FileMode.Open))
{
workbook.LoadDocument(stream, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
foreach (var worksheet in workbook.Worksheets)
{
string[] name = worksheet.Name.Split('_');
//if (name.Count() > 0 && name[0] != "Sheet1" && (name[0] == PeriodeType.Daily.ToString() || name[0] == PeriodeType.Hourly.ToString() || name[0] == PeriodeType.Monthly.ToString() || name[0] == PeriodeType.Weekly.ToString() || name[0] == PeriodeType.Yearly.ToString()))
if (name[0] == "Daily" || name[0] == "Monthly" || name[0] == "Yearly")
{
string periodType = name[0];
PeriodeType pType = string.IsNullOrEmpty(periodType)
? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), periodType);
string period = name[name.Count() - 1];
string[] periodes = null;
int tahun, bulan;
//validate and switch value by periodType
if (periodType != period && !string.IsNullOrEmpty(period))
{
switch (periodType)
{
case "Daily":
periodes = period.Split('-');
tahun = int.Parse(periodes[0]);
bulan = int.Parse(periodes[periodes.Count() - 1]);
break;
case "Monthly":
tahun = int.Parse(period);
break;
case "Yearly":
default:
break;
}
}
//coba baca value
workbook.Worksheets.ActiveWorksheet = worksheet;
//get row
Range range = worksheet.GetUsedRange();
int rows = range.RowCount;
int column = range.ColumnCount - 2;
int kpiId = 0;
DateTime periodData = new DateTime();
double? nilai = null;
for (int i = 1; i < rows; i++)
{
//get rows
for (int j = 0; j < column; j++)
{
bool fromExistedToNull = false;
var prepareDataContainer = new UpdateKpiAchievementsViewModel.KpiAchievementItem();
//get rows header and period
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
kpiId = int.Parse(worksheet.Cells[i, j].Value.ToString());
}
}
else if (j > 1)
{
if (worksheet.Cells[0, j].Value.Type == CellValueType.DateTime)
{
periodData = DateTime.Parse(worksheet.Cells[0, j].Value.ToString());
}
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
nilai = double.Parse(worksheet.Cells[i, j].Value.ToString());
}
else if (worksheet.Cells[i, j].Value.Type == CellValueType.Text)
{
fromExistedToNull = true;
nilai = null;
}
else
{
nilai = null;
}
bool isValidKpi = false;
if (!this.UserProfile().IsSuperAdmin)
{
skipped++;
isValidKpi =
_kpiService.IsValidKpi(new Services.Requests.Kpi.GetKpiByRole
{
RoleId = this.UserProfile().RoleId
});
}
else
{
isValidKpi = true;
}
if (isValidKpi && (nilai != null || fromExistedToNull))
{
prepareDataContainer.Value = nilai.ToString();
prepareDataContainer.KpiId = kpiId;
prepareDataContainer.Periode = periodData;
prepareDataContainer.PeriodeType = pType;
var oldKpiAchievement =
_kpiAchievementService.GetKpiAchievementByValue(
new GetKpiAchievementRequestByValue
{
KpiId = kpiId,
Periode = periodData,
PeriodeType = periodType
});
if (oldKpiAchievement.IsSuccess)
{
prepareDataContainer.Id = oldKpiAchievement.Id;
}
else
{
var fixedError = false;
if (oldKpiAchievement.ExceptionType != null &&
oldKpiAchievement.ExceptionType ==
typeof(InvalidOperationException))
{
var delete = _kpiAchievementService.DeleteKpiAchievement(kpiId, periodData, pType);
fixedError = delete.IsSuccess;
}
if (!fixedError)
{
throw new Exception(string.Format(@"KPI with id {0} and periode {1} and periode type {2} can't be inserted", kpiId, periodData.ToShortDateString(), pType.ToString()));
}
}
var request = prepareDataContainer.MapTo<UpdateKpiAchievementItemRequest>();
request.UserId = userId;
var insert = _kpiAchievementService.UpdateKpiAchievementItem(request);
if (insert.IsSuccess)
{
inserted++;
}
else
{
rejected++;
}
}
}
}
}
response.isSuccess = true;
response.Message = "Success :" + inserted + "\r\n";
response.Message += "Skipped :" + skipped + "\r\n";
response.Message += "Rejected :" + rejected + "\r\n";
}
else
{
response.isSuccess = false;
response.Message = "File Not Valid";
break;
}
}
}
}
catch (Exception exception)
{
response.Message = exception.Message;
response.isSuccess = false;
}
return response;
}