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 ?? "");
}