public void GetMenuExcellTestApi()
{
string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
@"ACSDining.Web\ExcelFiles\Menu.xls";
//GetExcelController excelApi = new GetExcelController(_unitOfWork);
GetExcelService excelService = new GetExcelService(_unitOfWork.RepositoryAsync<WeekOrderMenu>());
WeekYearDto wyDto = new WeekYearDto
{
Week = 22,
Year = 2016
};
ForMenuExcelDto feDto = new ForMenuExcelDto
{
WeekYear = wyDto,
MenuTitle = "test string"
};
string result = excelService.GetMenuExcel(feDto);
Assert.IsNotNull(result);
Process.Start(_path);
}
public static string GetMenuExcelFile(this IRepositoryAsync<MenuForWeek> repository, ForMenuExcelDto dto)
{
WeekMenuDto weekMenuDto = repository.MapWeekMenuDto(dto.WeekYear);
WorkingWeek wweek = repository.WorkWeekByWeekYear(dto.WeekYear);
string[] daynames = wweek.WorkingDays.Where(wd => wd.IsWorking).OrderBy(wd=>wd.DayOfWeek.Id).Select(wd => wd.DayOfWeek.Name).ToArray();
string[] dishCategories = MapHelper.GetCategoriesStrings(repository.Context);
int daycount = weekMenuDto.WorkWeekDays.Count(d => d);
int catLength = dishCategories.Length;
List<MenuForDayDto> mfdays = new List<MenuForDayDto>();
for (int i = 0; i < weekMenuDto.WorkWeekDays.Length; i++)
{
if (weekMenuDto.WorkWeekDays[i])
{
mfdays.Add(weekMenuDto.MfdModels[i]);
}
}
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.Add("Меню");
string endcolname = GetExcelColumnName(4);
string allstr = string.Format("A{0}:D{1}", 1, mfdays.Count*(dishCategories.Length + 1) + 3);
string titlerang = String.Format("A1:{0}1", GetExcelColumnName(4));
Range range = worksheet.Range(titlerang);
range.Merge();
worksheet.Cell("A1").MergedWithCell.Value = "Меню " + dto.MenuTitle;
range.AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Columns[0].Width = 180;
worksheet.Cell(1, 1).Value = "Наименование блюд";
worksheet.Columns[1].Width = 450;
worksheet.Columns[2].Width = 90;
worksheet.Columns[3].Width = 120;
worksheet.Rows[0].Height = 50;
worksheet.Rows[0].AlignmentVertical=AlignmentVertical.Centered;
worksheet.Rows[1].Height = 50;
worksheet.Rows[1].AlignmentVertical = AlignmentVertical.Centered;
worksheet.Range("A2:D2").FillPattern = PatternStyle.Solid;
worksheet.Range("A2:D2").FillPatternForeColor = Color.FromArgb(48, 127, 217);
worksheet.Range("C2:D2").Merge();
worksheet.Cell(1, 2).MergedWithCell.Value = "Цена, грн";
worksheet.Range("A2:D2").AlignmentHorizontal = AlignmentHorizontal.Centered;
for (int i = 0; i < daycount; i++)
{
MenuForDayDto mfd = mfdays[i];
int strcount = i*catLength + 2;
string colname = string.Format("A{0}:D{1}", strcount + 1 + i, strcount + 1 + i);
worksheet.Range(colname).Merge();
worksheet.Cell(strcount + i, 0).MergedWithCell.Value = daynames[i];
worksheet.Range(colname).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Range(colname).FillPattern = PatternStyle.Solid;
worksheet.Range(colname).FillPatternForeColor = Color.FromArgb(144, 164, 187);
for (int j = 0; j < mfd.Dishes.Count; j++)
{
worksheet.Cell(strcount + j + 1 + i, 0).Value = mfd.Dishes[j].Category;
worksheet.Cell(strcount + j + 1 + i, 0).Indent = 2;
worksheet.Cell(strcount + j + 1 + i, 1).Value = mfd.Dishes[j].Title;
worksheet.Cell(strcount + j + 1 + i, 1).Indent = 2;
bool deskexists = !string.IsNullOrEmpty(mfd.Dishes[j].Description);
if (deskexists)
{
worksheet.Cell(strcount + j + 1 + i, 1).Value = mfd.Dishes[j].Title + ":" +
mfd.Dishes[j].Description;
worksheet.Cell(strcount + j + 1 + i, 1).Font = new Font("Arial", 12, FontStyle.Bold);
}
worksheet.Cell(strcount + j + 1 + i, 1).Wrap = true;
worksheet.Cell(strcount + j + 1 + i, 2).Value = mfd.Dishes[j].Price;
worksheet.Cell(strcount + j + 1 + i, 2).NumberFormatString = "#,##0.00";
worksheet.Rows[strcount + j + 1 + i].Height = (uint) (!deskexists ? 70 : 90);
worksheet.Rows[strcount + j + 1 + i].AlignmentVertical = AlignmentVertical.Centered;
}
string sumdaytotal = string.Format("D{0}:D{1}", strcount + 2 + i, strcount + 1 + i + catLength);
worksheet.Range(sumdaytotal).Merge();
worksheet.Range(sumdaytotal).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Range(sumdaytotal).AlignmentVertical = AlignmentVertical.Centered;
worksheet.Cell(strcount + 1 + i, 3).Value = mfd.TotalPrice;
worksheet.Range(sumdaytotal).NumberFormatString = "#,##0.00";
}
string totalstr = string.Format("A{0}:C{0}", daycount*(catLength+1) + 3);
worksheet.Range(totalstr).Merge();
worksheet.Cell(daycount*(catLength + 1) + 2, 0).Value = "Всего ";
worksheet.Range(totalstr).AlignmentHorizontal = AlignmentHorizontal.Right;
worksheet.Cell(daycount * (catLength + 1) + 2, 3).Value = weekMenuDto.SummaryPrice;
worksheet.Cell(daycount * (catLength + 1) + 2, 3).AlignmentHorizontal = AlignmentHorizontal.Centered;
worksheet.Cell(daycount * (catLength + 1) + 2, 3).NumberFormatString = "#,##0.00";
worksheet.Rows[daycount * (catLength + 1) + 2].Height = 60;
worksheet.Rows[daycount * (catLength + 1) + 2].AlignmentVertical = AlignmentVertical.Centered;
worksheet.Range(allstr).OuterBorderStyle = LineStyle.Medium;
worksheet.Range(allstr).InnerBorderStyle = LineStyle.Medium;
worksheet.Range(allstr).Font = new Font("Arial", 14, FontStyle.Bold);
//string _path = AppDomain.CurrentDomain.BaseDirectory.Replace(@"UnitTestProject1\bin\Debug", "") +
// @"ACSDining.Web\ExcelFiles\Menu.xls";
string pathstr = string.Format("~/ExcelFiles/Меню_{0}.xls", YearWeekHelp.GetWeekTitle(repository, dto.WeekYear));
string _path = HostingEnvironment.MapPath(pathstr);
if (File.Exists(_path))
{
File.Delete(_path);
}
try
{
document.SaveAs(_path);
}
catch (Exception)
{
throw;
}
// Close document
document.Close();
return _path;
}
