private void RenderType(OrganizationType type, IWorksheet sheet, ref Int32 row_index)
{
Int32 first_row = row_index;
Int32 last_column = 5;
sheet.Cells[row_index, 0].Value = type.Caption.ToUpper();
var range = sheet.Cells[row_index, 0, row_index, 2];
range.Merge();
range.Font.Color = Color.Red;
range.Font.Italic = true;
row_index++;
foreach (var row in ReportSumModel.GetRows(type))
{
RenderRow(row, sheet, row_index);
row_index++;
}
if (row_index == first_row)
{
++row_index;
}
var formula = string.Format("=SUM(R[{0}]C:R[-1]C)", first_row - row_index);
sheet.Cells[row_index, 2].FormulaR1C1 = formula;
sheet.Cells[row_index, 3].FormulaR1C1 = formula;
sheet.Cells[row_index, 4].FormulaR1C1 = formula;
sheet.Cells[row_index, 5].FormulaR1C1 = formula;
range = sheet.Cells[row_index, 0, row_index, last_column];
range.Font.Color = Color.Red;
range.Font.Size = 14;
sheet.Cells[row_index, 0].Value = string.Format("ИТОГО: {0}", type.Caption.ToUpper());
range = sheet.Cells[row_index, 0, row_index, 1];
range.Merge();
range.Font.Italic = true;
range.Font.Size = 11;
row_index++;
}
public void MakeReportSum(DateTime dateBegin, DateTime dateEnd, Int64 customerTypeId, Stream stream)
{
int last_column = 5;
int first_row = 3;
IWorkbook book = Factory.GetWorkbook();
var sheet = book.Worksheets[0];
var customerType = CustomerTypeService.GetTypeById(customerTypeId);
if (customerType == CustomerType.Empty)
{
customerType = null;
}
PrepareTemplateSum(sheet, dateBegin, dateEnd, customerType);
ReportSumModel.Refresh(dateBegin, dateEnd, customerType);
Int32 row_index = first_row;
List <Int32> sum_rows = new List <int>();
foreach (var type in ReportSumModel.GetTypes())
{
RenderType(type, sheet, ref row_index);
sum_rows.Add(row_index - 1);
}
string formula = "";
foreach (int index in sum_rows)
{
formula += string.Format("R[{0}]C+", index - row_index);
}
if (!string.IsNullOrEmpty(formula))
{
formula = string.Format("=SUM({0})", formula.TrimEnd('+'));
sheet.Cells[row_index, 2].FormulaR1C1 = formula;
sheet.Cells[row_index, 3].FormulaR1C1 = formula;
sheet.Cells[row_index, 4].FormulaR1C1 = formula;
sheet.Cells[row_index, 5].FormulaR1C1 = formula;
}
var range = sheet.Cells[row_index, 0];
range.Value = "ИТОГО";
range.Font.Italic = true;
range = sheet.Cells[row_index, 0, row_index, last_column];
range.Font.Size = 16;
sheet.Cells[first_row - 2, 0, row_index, last_column].Borders.LineStyle = LineStyle.Continuous;
sheet.PageSetup.Orientation = PageOrientation.Landscape;
sheet.PageSetup.FitToPagesTall = 999;
book.SaveToStream(stream, FileFormat.Excel8);
}
public void MakeReportOper(DateTime dateBegin, DateTime dateEnd, Int64 customerTypeId, Stream stream)
{
int last_column = 8;
int first_row = 2;
IWorkbook book = Factory.GetWorkbook();
var sheet = book.Worksheets[0];
var customerType = CustomerTypeService.GetTypeById(customerTypeId);
if (customerType == CustomerType.Empty)
{
customerType = null;
}
PrepareTemplateOper(sheet, dateBegin, dateEnd, customerType);
ReportSumModel.Refresh(dateBegin, dateEnd, customerType);
Int32 row_index = first_row;
Int32 num = 0;
IEnumerable <ServiceAction> actions = null;
var user = UserService.GetCurrentUser();
if (user.IsAdmin)
{
actions = ActionService.GetActions(dateBegin, dateEnd, customerType);
}
else
{
actions = ActionService.GetActions(user, dateBegin, dateEnd, customerType);
}
foreach (var action in actions)
{
if (action.Service == null || action.Service.Organization == null || action.User == null || action.Type == null)
{
continue;
}
sheet.Cells[row_index, 0].Value = ++num;
sheet.Cells[row_index, 1].Value = action.Date.ToString("dd.MM.yyyy");
sheet.Cells[row_index, 2].Value = action.Customer;
sheet.Cells[row_index, 3].Value = action.IsNonresident ? "Да" : "Нет";
sheet.Cells[row_index, 4].Value = action.FreeVisit ? "Да" : "Нет";
sheet.Cells[row_index, 5].Value = action.Service.Caption;
sheet.Cells[row_index, 6].Value = action.Service.Organization.Caption;
sheet.Cells[row_index, 7].Value = action.Type.Caption;
sheet.Cells[row_index, 8].Value = action.User.Name;
row_index++;
}
sheet.Cells[first_row - 2, 0, row_index, last_column].Borders.LineStyle = LineStyle.Continuous;
sheet.PageSetup.Orientation = PageOrientation.Landscape;
sheet.PageSetup.FitToPagesTall = 999;
book.SaveToStream(stream, FileFormat.Excel8);
}