public override void FillExcelData(ExcelWorksheet sheet) { FillData(); if (Data == null || Data.Count == 0) return; int startRow = DataRowIndex; int rowIndex = startRow; ExcelRange cell = null; RmsAuto.Store.Acctg.ClientProfile profile = SiteContext.Current.CurrentClient.Profile; SellerInfo seller = GetSellerInfo(); sheet.InsertRow(rowIndex, Data.Count(), rowIndex); int i = 1; foreach (var item in Data) { cell = sheet.Cells[rowIndex, 1]; cell.Value = i; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; var border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 2]; cell.Value = item.Manufacturer; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 3]; cell.Value = item.OrderNumber; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 4]; cell.Value = item.PartNumber; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 5]; cell.Value = item.PartName; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 6]; cell.Value = item.Qty; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 7]; cell.Value = item.Price; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 8]; cell.Value = item.Price * item.Qty; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; i++; rowIndex++; } cell = sheet.Cells[rowIndex + 1, 8]; cell.Value = Data.Sum(x => x.Total); cell = sheet.Cells[1, 1]; cell.Value = String.Format(ConfigurationManager.AppSettings["ExcelTemplate.InvoiceTitle"], profile.AcctgId + "/" + DateTime.Now.ToString("hhmmss"), DateTime.Now.ToShortDateString()); cell = sheet.Cells[rowIndex + 3, 1]; cell.Value = String.Format(ConfigurationManager.AppSettings["ExcelTemplate.InvoiceTotal"], Data.Sum(x => x.Qty), Data.Sum(x => x.Total)); cell = sheet.Cells[4, 1]; cell.Value = cell.Value.ToString() + seller.CompanyName + ", " + seller.Address + ", " + seller.Phone; cell = sheet.Cells[5, 1]; cell.Value = cell.Value.ToString() + profile.ClientName + ", " + profile.ShippingAddress; }
private void InsertWeek(ref ExcelWorksheet ws, WeekForExcel week) { var nameTable = ws.Names[string.Format("Tabla{0}", week.WeekNumber)]; ws.InsertRow(nameTable.Start.Row, week.WeekTable.Rows.Count); var nameTableNew = ws.Cells[nameTable.Address].LoadFromDataTable(week.WeekTable, false); ws.Cells[nameTableNew.Address].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; ws.Cells[nameTableNew.Address].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FFFF99")); //estilo para el footer ExcelRange rangeFooter = ws.Cells[nameTableNew.End.Row, 1, nameTableNew.End.Row, nameTableNew.End.Column]; rangeFooter.Style.Numberformat.Format = (this.IsPesosMoney) ? _format_pesos : _format_dollars; rangeFooter.Style.Font.Bold = true; foreach (var cell in rangeFooter) if (cell.Value != null && cell.Value.ToString() != "Total Día:") cell.Value = decimal.Parse(cell.Value.ToString()); this.SetBorders(ws.Cells[nameTableNew.Address]); this.PaintWeek(ref ws, nameTableNew.Address); }
public override void FillExcelData(ExcelWorksheet sheet) { FillData(); if (Data == null || Data.Count == 0) return; int startRow = DataRowIndex; int rowIndex = startRow; ExcelRange cell = null; RmsAuto.Store.Acctg.ClientProfile profile = RmsAuto.Store.Acctg.ClientProfile.Load((string)Context.Session["ClientID"]); SellerInfo seller = GetSellerInfo(); sheet.InsertRow(rowIndex, Data.Count(), rowIndex); int i = 1; foreach (var item in Data) { cell = sheet.Cells[rowIndex, 1]; cell.Value = i; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; var border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 2]; cell.Value = item.Manufacturer; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 3]; cell.Value = item.PartNumber; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 4]; cell.Value = item.PartName; cell.Style.WrapText = true; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 5]; cell.Value = item.Qty; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 6]; cell.Value = item.Price; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; cell = sheet.Cells[rowIndex, 7]; cell.Value = item.Price * item.Qty; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top; border = cell.Style.Border; border.Left.Style = border.Right.Style = border.Bottom.Style = border.Top.Style = ExcelBorderStyle.Thin; i++; rowIndex++; } cell = sheet.Cells[rowIndex + 1, 7]; cell.Value = Data.Sum(l => l.Total); cell = sheet.Cells[13, 6]; cell.Value = DateTime.Now.ToShortDateString(); cell = sheet.Cells[14, 7]; cell.Value = profile.AcctgId + "/" + DateTime.Now.ToString("hhmmss"); cell = sheet.Cells[14, 1]; cell.Value = profile.ClientName; cell = sheet.Cells[15, 6]; cell.Value = String.Join(", ", Data.Select(x => x.OrderNumber.ToString()).Distinct().ToArray()); cell = sheet.Cells[18, 1]; cell.Value = profile.DeliveryAddress; cell = sheet.Cells[21, 1]; cell.Value = cell.Value.ToString() + HttpUtility.UrlDecode(Context.Request["descr"] ?? ""); cell = sheet.Cells[22, 1]; cell.Value = cell.Value.ToString() + HttpUtility.UrlDecode(Context.Request["country"] ?? ""); cell = sheet.Cells[7, 1]; cell.Value = String.Format(ConfigurationManager.AppSettings["ExcelTemplate.SellerInfo"], seller.License ?? "", seller.Issued.HasValue ? seller.Issued.Value.ToShortDateString() : "-", seller.Address ?? ""); }