private static ProjectSheet CreateProjectSheet(string userId, string[] columnNames, ExcelWorksheet sheet)
{
var projectSheet = new ProjectSheet
{
ProjectSheetId = Guid.NewGuid().ToString(),
UserLogin = userId,
ProjectName = sheet.Name,
Tasks = new List <ProjectTask>(),
Holidays = new List <Holiday>()
};
int rows = sheet.Dimension.Rows;
int columns = sheet.Dimension.Columns;
for (int i = 1; i <= rows; i++)
{
if (columns <= 5 && Utilities.CheckForHeaderRows(sheet, i, columnNames))
{
ProjectTask task = CreateProjectTask(sheet, projectSheet, columns, i);
projectSheet.Tasks.Add(task);
}
else if (Utilities.CheckForHeaderRows(sheet, i, columnNames))
{
Holiday holiday = CreateHoliday(sheet, projectSheet, columns, i);
projectSheet.Holidays.Add(holiday);
}
}
return(projectSheet);
}
public void GroupProjectTypeColumn()
{
if (ProjectSheetDataRowIndex > ProjectSheetDataRowStartIndex)
{
var cellIndex = 1;
var tempIndex = ProjectSheetDataRowStartIndex;
var tempValue = ProjectSheet.GetRow(ProjectSheetDataRowStartIndex).GetCell(cellIndex).StringCellValue;
var endIndex = ProjectSheetDataRowIndex - 1;
for (int i = ProjectSheetDataRowStartIndex + 1; i <= endIndex; i++)
{
var cellValue = ProjectSheet.GetRow(i).GetCell(cellIndex).StringCellValue;
if (cellValue != tempValue)
{
if (i - tempIndex > 1)
{
ProjectSheet.AddMergedRegion(new CellRangeAddress(tempIndex, i - 1, cellIndex, cellIndex));
}
tempValue = cellValue;
tempIndex = i;
}
else if (i == endIndex)
{
ProjectSheet.AddMergedRegion(new CellRangeAddress(tempIndex, i, cellIndex, cellIndex));
}
}
}
}
//public void WriteProjects(IEnumerable<Project> projects)
//{
// foreach (var project in projects)
// {
// WriteProject(project);
// }
//}
public void WriteProject(Project project, IEnumerable <ExpenditureStatisticItem> items)
{
var row = ProjectSheet.CreateRow(ProjectSheetDataRowIndex);
row.CreateCell(0).SetCellValue(ProjectSheetDataRowIndex - ProjectSheetDataRowStartIndex + 1);
row.CreateCell(1).SetCellValue(project.TypeString);
row.CreateCell(2).SetCellValue(project.Code);
row.CreateCell(3).SetCellValue(project.Name);
row.CreateCell(4).SetCellValue(project.Consignor);
row.CreateCell(5).SetCellValue(project.NeedFinish);
row.CreateCell(6).SetCellValue(project.CurrentProgress);
row.CreateCell(7).SetCellValue(project.ManagerNames);
row.CreateCell(8).SetCellValue(project.ParticipantNames);
row.CreateCell(9).SetCellValue(project.Deadline.ToShortDateString());
row.CreateCell(10).SetCellValue(project.NeedSupport);
row.CreateCell(11).SetCellValue(project.AdvancePlan);
row.CreateCell(12).SetCellValue(project.StatusString);
row.CreateCell(13).SetCellValue(project.StartTime.Year.ToString());
row.CreateCell(14).SetCellValue(project.Deadline.Year.ToString());
ProjectSheetDataRowIndex++;
if (WithFinance)
{
WirteFinance(project, items);
}
}
public EplProjectProperties GetProjectProperties()
{
EplProjectProperties EplProjectProperties = new EplProjectProperties();
try
{
EplProjectProperties.ProjectName = ProjectSheet.GetCellText("C2");
EplProjectProperties.ProjectTitle = ProjectSheet.GetCellText("C3");
EplProjectProperties.OrderNumber = ProjectSheet.GetCellText("C4");
EplProjectProperties.ProductNumber = ProjectSheet.GetCellText("C5");
EplProjectProperties.Date = ProjectSheet.GetCellText("C6");
EplProjectProperties.Descriptions = ProjectSheet.GetCellText("C7");
EplProjectProperties.CheckPersion = ProjectSheet.GetCellText("C8");
EplProjectProperties.ApprovedPersion = ProjectSheet.GetCellText("C9");
EplProjectProperties.Creator = ProjectSheet.GetCellText("C10");
for (int i = 0; i < 10; i++)
{
int addr = i * 3 + 13;
EplProjectProperties.Revisions[i].Name = ProjectSheet.GetCellText("C", addr);
EplProjectProperties.Revisions[i].Description = ProjectSheet.GetCellText("C", addr + 1);
EplProjectProperties.Revisions[i].Date = ProjectSheet.GetCellText("C", addr + 2);
}
EplProjectProperties.PageLayout.Width = ProjectSheet.GetCellValue <double>("F2");
EplProjectProperties.PageLayout.Heigth = ProjectSheet.GetCellValue <double>("F3");
return(EplProjectProperties);
}
catch (Exception ex)
{
Logger.WriteLine("Get project properties failed.", ex);
return(null);
}
}
public IEnumerable <ProjectSheet> GetReportDataFromFile(ExcelPackage sentFile, string domainLogin)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var sourceExcel = sentFile;
var projectSheets = sourceExcel.Workbook.Worksheets;
var userId = domainLogin;
var columnNames = Utilities.ReportColumnNames;
var sheetList = new List <ProjectSheet>();
foreach (var sheet in projectSheets)
{
ProjectSheet projectSheet = CreateProjectSheet(userId, columnNames, sheet);
sheetList.Add(projectSheet);
}
return(sheetList);
}
private static ProjectTask CreateProjectTask(ExcelWorksheet sheet, ProjectSheet projectSheet, int columns, int i)
{
var task = new ProjectTask
{
Id = Guid.NewGuid().ToString(),
ProjectSheetId = projectSheet.ProjectSheetId,
ProjectSheet = projectSheet,
UserLogin = projectSheet.UserLogin,
Name = sheet.Cells[i, 2].Value.ToString(),
DateStarted = sheet.Cells[i, 4].Value.ToString(),
DateEnded = sheet.Cells[i, 5].Value.ToString()
};
var idVal = sheet.Cells[i, 1].Value;
if (idVal != null)
{
task.TaskId = idVal.ToString();
}
var descriptionVal = sheet.Cells[i, 3].Value;
if (descriptionVal != null)
{
task.Description = descriptionVal.ToString();
}
if (columns < 5)
{
for (int k = 5; k <= columns; k++)
{
var content = sheet.Cells[i, k].Value;
if (content != null)
{
task.Others += content.ToString() + "; ";
}
}
}
return(task);
}
private static Holiday CreateHoliday(ExcelWorksheet sheet, ProjectSheet projectSheet, int columns, int i)
{
var holiday = new Holiday
{
Id = Guid.NewGuid().ToString(),
ProjectSheetId = projectSheet.ProjectSheetId,
ProjectSheet = projectSheet,
UserLogin = projectSheet.UserLogin
};
var holidayName = "";
for (int j = 1; j <= columns; j++)
{
var content = sheet.Cells[i, j].Value;
if (content != null)
{
holidayName += content.ToString() + "; ";
}
}
holiday.Name = holidayName;
return(holiday);
}
