예제 #1
0
 /// <summary>
 /// 为行添加上边框
 /// </summary>
 /// <param name="sheet"></param>
 /// <param name="startCell"></param>
 /// <param name="endCell"></param>
 private static void SetTopBorder(ref NPOIHelper2 sheet, int startCell = 0, int endCell = 0, BorderStyle borderStyle = BorderStyle.Thin, IFont font = null)
 {
     for (int i = startCell; i <= endCell; i++)
     {
         sheet.CreateCell(i, "", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, font: font));
     }
 }
예제 #2
0
 private static int RepeatSetCell(ref NPOIHelper2 sheet, int startCellIndex, ICellStyle cellStyle, string[] cellNames)
 {
     for (int i = startCellIndex, j = 0; j < cellNames.Length; i++, j++)
     {
         sheet.MergeCells(2, 4, i, i);
         sheet.CreateCell(i, cellNames[j], cellstyle: cellStyle);
         startCellIndex = i;
     }
     return(startCellIndex);
 }
예제 #3
0
        private static void AddData(ref NPOIHelper2 sheet, string dataIndex, int rowIndex, Finance_EveryDaySaleLog item, ICellStyle cellStyle, ICellStyle cellStyle2)
        {
            sheet.CreateRow(rowIndex);
            int i = 0;

            //序号
            sheet.CreateCell(i++, dataIndex, cellStyle);
            //年
            sheet.CreateCell(i++, item.Year.ToString(), cellStyle);
            //月
            sheet.CreateCell(i++, item.Month.ToString(), cellStyle);
            //日
            sheet.CreateCell(i++, item.Day.ToString(), cellStyle);
            //部门
            sheet.CreateCell(i++, item.DepartmentName, cellStyle);
            //销售人员
            sheet.CreateCell(i++, item.SaleName, cellStyle);
            //客户/店铺
            sheet.CreateCell(i++, item.Customer, cellStyle);
            //合同号
            sheet.CreateCell(i++, item.ContractNumber, cellStyle);
            //供应商
            sheet.CreateCell(i++, item.Supplier, cellStyle);
            //产品名称
            sheet.CreateCell(i++, item.ProductName, cellStyle);
            //SPU
            sheet.CreateCell(i++, item.SPU, cellStyle);
            //产品SKU
            sheet.CreateCell(i++, item.ProductSKU, cellStyle);
            //产品规格
            sheet.CreateCell(i++, item.ProductSpecification, cellStyle);
            //销售数量
            sheet.CreateCell(i++, item.SaleCount?.ToString("f2") ?? "0", cellStyle);
            //销售单价
            sheet.CreateCell(i++, item.SalePrice?.ToString("f2") ?? "0", cellStyle);
            //货币
            sheet.CreateCell(i++, item.Currency, cellStyle);
            //汇率
            sheet.CreateCell(i++, item.ExchangeRate?.ToString("f2") ?? "0", cellStyle);
            //销售收入
            sheet.CreateCell(i++, item.SaleIncome?.ToString("f2") ?? "0", cellStyle);
            //单价料
            sheet.CreateCell(i++, item.MaterialUnitPrice?.ToString("f2") ?? "0", cellStyle);
            //单价工
            sheet.CreateCell(i++, item.ProcessUnitPrice?.ToString("f2") ?? "0", cellStyle);
            //合计料
            sheet.CreateCell(i++, item.MaterialTotalPrice?.ToString("f2") ?? "0", cellStyle);
            //合计工
            sheet.CreateCell(i++, item.ProcessTotalPrice?.ToString("f2") ?? "0", cellStyle);
            //毛益额
            sheet.CreateCell(i++, item.GrossProfit?.ToString("f2") ?? "0", cellStyle);
            //毛益率
            sheet.CreateCell(i++, item.GrossProfitRate?.ToString("f2") + "%" ?? "", cellStyle);
            //量
            sheet.CreateCell(i++, item.ChangeCostNumber?.ToString("f2") ?? "0", cellStyle);
            //事
            sheet.CreateCell(i++, item.ChangeCostMatter?.ToString("f2") ?? "0", cellStyle);
            //金额
            sheet.CreateCell(i++, item.ContributionMoney?.ToString("f2") ?? "0", cellStyle);
            //比例
            sheet.CreateCell(i++, item.ContributionRatio?.ToString("f2") ?? "0", cellStyle);
            //未到期
            sheet.CreateCell(i++, item.AvgCoatUndue?.ToString("f2") ?? "0", cellStyle);
            //当期
            sheet.CreateCell(i++, item.AvgCoatCurrentdue?.ToString("f2") ?? "0", cellStyle);
            //逾期
            sheet.CreateCell(i++, item.AvgCoatOverdue?.ToString("f2") ?? "0", cellStyle);
            //金额
            sheet.CreateCell(i++, item.CustomerContributionMoney?.ToString("f2") ?? "0", cellStyle);
            //比例
            sheet.CreateCell(i++, item.ContributionRatio?.ToString("f2") ?? "0", cellStyle);
            //是否关注
            sheet.CreateCell(i++, (item.Follow != null ? (item.Follow == true ? "是" : "否") : "否"), cellStyle2);
        }
예제 #4
0
        public static XSSFWorkbook CustomEveryDaySaleLogExport(IEnumerable <Finance_EveryDaySaleLog> statisticsList, string department)
        {
            int         rowIndex = 0;
            NPOIHelper2 sheet    = new NPOIHelper2(defaultFontSize: 12);

            //将Excel背景色改为白色
            for (int i = 0; i <= ColTotalIndex; i++)
            {
                sheet.SetDefaultColumnStyle(i, sheet.CellStyle(new NPOI.HSSF.Util.HSSFColor.White()));
            }

            var cellStyleDefault1 = sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12));

            var cellStyleDefault2 = sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12));

            var cellStyleDefault3 = sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, left: BorderStyle.Thin, right: BorderStyle.Medium, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12));

            var cellStyleDefault4 = sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, left: BorderStyle.Thin, right: BorderStyle.Medium, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12));

            //设置列宽
            for (int i = 3; i <= ColTotalIndex; i++)
            {
                sheet.SetColumnWidth(i, 15 * 256);
            }

            #region row0
            sheet.CreateRow(rowIndex);
            sheet.MergeCells(0, 0, 0, ColTotalIndex);
            sheet.CreateCell(0, (department == "0" ? "" : department) + "价值分析表", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, font: sheet.FontStyle(fontsize: 18)));
            sheet.SetHeight(25 * 25);
            #endregion

            #region row1
            sheet.CreateRow(++rowIndex);
            for (int i = 0; i <= ColTotalIndex; i++)
            {
                sheet.CreateCell(i, "", sheet.CellStyle(top: BorderStyle.Medium, right: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            }
            sheet.MergeCells(1, 1, 0, 22);
            sheet.CreateCell(0, "每日", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Medium, font: sheet.FontStyle(fontsize: 18)));

            sheet.MergeCells(1, 1, 23, ColTotalIndex);
            sheet.CreateCell(23, "每月", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Medium, font: sheet.FontStyle(fontsize: 18)));

            sheet.CreateCell(ColTotalIndex, "", sheet.CellStyle(top: BorderStyle.Medium, right: BorderStyle.Medium, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            #endregion

            #region row2
            sheet.CreateRow(++rowIndex);
            for (int i = 0; i <= ColTotalIndex; i++)
            {
                sheet.CreateCell(i, "", sheet.CellStyle(top: BorderStyle.Thin, right: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            }

            sheet.CreateCell(0, "内容", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));

            sheet.MergeCells(2, 2, 1, 3);
            sheet.CreateCell(1, "日期", cellStyleDefault1);

            string[] arr      = new string[] { "部门", "销售人员", "客户/店铺", "合同号", "供应商", "产品名称", "SPU", "产品SKU", "产品规格", "销售数量", "销售单价", "币种", "汇率", "销售收入" };
            int      colIndex = RepeatSetCell(ref sheet, 4, cellStyleDefault1, arr);

            colIndex += 4;
            sheet.MergeCells(2, 2, colIndex - 3, colIndex);
            sheet.CreateCell(colIndex - 3, "直接成本", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(2, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "毛益额", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(2, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "毛益率", cellStyleDefault1);

            colIndex += 2;
            sheet.MergeCells(2, 2, colIndex - 1, colIndex);
            sheet.CreateCell(colIndex - 1, "变动费用", cellStyleDefault1);

            colIndex += 2;
            sheet.MergeCells(2, 2, colIndex - 1, colIndex);
            sheet.CreateCell(colIndex - 1, "经营贡献", cellStyleDefault1);

            colIndex += 3;
            sheet.MergeCells(2, 2, colIndex - 2, colIndex);
            sheet.CreateCell(colIndex - 2, "应收账款平均资金成本", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));

            colIndex += 2;
            sheet.MergeCells(2, 2, colIndex - 1, colIndex);
            sheet.CreateCell(colIndex - 1, "应收账款平均资金成本", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));

            colIndex++;
            sheet.MergeCells(2, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "是否关注", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, right: BorderStyle.Medium, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));
            #endregion

            #region row3
            sheet.CreateRow(++rowIndex);

            sheet.MergeCells(3, 4, 0, 0);
            sheet.CreateCell(0, "序号", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));

            sheet.MergeCells(3, 4, 1, 1);
            sheet.CreateCell(1, "年", cellStyleDefault1);

            sheet.MergeCells(3, 4, 2, 2);
            sheet.CreateCell(2, "月", cellStyleDefault1);

            sheet.MergeCells(3, 4, 3, 3);
            sheet.CreateCell(3, "日", cellStyleDefault1);

            for (int i = 4, j = 0; j < arr.Length; i++, j++)
            {
                sheet.CreateCell(i, "", sheet.CellStyle(left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            }

            colIndex  = 4 + arr.Length;
            colIndex += 1;
            sheet.MergeCells(3, 3, colIndex - 1, colIndex);
            sheet.CreateCell(colIndex - 1, "单件价格", cellStyleDefault1);
            sheet.CreateCell(colIndex, "", sheet.CellStyle(top: BorderStyle.Thin, left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));

            colIndex += 2;
            sheet.MergeCells(3, 3, colIndex - 1, colIndex);
            sheet.CreateCell(colIndex - 1, "合计金额", cellStyleDefault1);
            sheet.CreateCell(colIndex, "", sheet.CellStyle(top: BorderStyle.Thin, left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            sheet.CreateCell(colIndex + 1, "", sheet.CellStyle(left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            sheet.CreateCell(colIndex + 2, "", sheet.CellStyle(left: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            colIndex += 2;

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "量", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "事", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "金额", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "比例", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "未到期", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "当期", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "逾期", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "金额", cellStyleDefault1);

            colIndex++;
            sheet.MergeCells(3, 4, colIndex, colIndex);
            sheet.CreateCell(colIndex, "比例", cellStyleDefault1);

            sheet.CreateCell(ColTotalIndex, "", sheet.CellStyle(top: BorderStyle.Thin, left: BorderStyle.Thin, right: BorderStyle.Medium, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));
            #endregion

            #region row4
            sheet.CreateRow(++rowIndex);

            colIndex = 4 + arr.Length;
            for (int i = 0; i < colIndex; i++)
            {
                sheet.CreateCell(i, "", sheet.CellStyle(right: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            }

            sheet.CreateCell(++colIndex, "料", cellStyleDefault1);
            sheet.CreateCell(++colIndex, "工", cellStyleDefault1);
            sheet.CreateCell(++colIndex, "料", cellStyleDefault1);
            sheet.CreateCell(++colIndex, "工", sheet.CellStyle(ha: HorizontalAlignment.Center, va: VerticalAlignment.Center, top: BorderStyle.Thin, left: BorderStyle.Thin, right: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));

            colIndex++;
            for (int i = colIndex; i < ColTotalIndex; i++)
            {
                sheet.CreateCell(i, "", sheet.CellStyle(right: BorderStyle.Thin, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            }

            sheet.CreateCell(ColTotalIndex, "", sheet.CellStyle(right: BorderStyle.Medium, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Bold, fontsize: 12)));
            #endregion

            #region 循环其余行

            int month     = 0;
            int month2    = 0;
            int year      = 0;
            int year2     = 0;
            int dataIndex = 0;
            int lastId    = statisticsList.Select(o => o.Id).Last();
            List <Finance_EveryDaySaleLog> list = null;
            foreach (var item in statisticsList)
            {
                dataIndex++;
                //if (dataIndex > 200000)
                //    break;

                #region 普通记录 在最后一条数据时显示
                if (item.Id == lastId)
                {
                    AddData(ref sheet, dataIndex.ToString(), ++rowIndex, item, cellStyleDefault1, cellStyleDefault3);
                }
                #endregion

                year2  = year;
                year   = item.Year.GetValueOrDefault();
                month2 = month;
                month  = item.Month.GetValueOrDefault();

                #region 月统计记录
                if ((month != month2 && month2 != 0) || lastId == item.Id)
                {
                    list = (from o in statisticsList where o.Year == year2 && o.Month == month2 select o).ToList();
                    AddData(ref sheet, $"{month2}月合计", ++rowIndex, CountData(list, year2, month2), cellStyleDefault2, cellStyleDefault4);
                }
                #endregion

                #region 年统计记录
                if ((year != year2 && year2 != 0) || lastId == item.Id)
                {
                    list = (from o in statisticsList where o.Year == year2 select o).ToList();
                    AddData(ref sheet, $"{year2}年合计", ++rowIndex, CountData(list, year2, month2), cellStyleDefault2, cellStyleDefault4);
                }
                #endregion

                #region 合计统计记录
                if (lastId == item.Id)
                {
                    AddData(ref sheet, "合计", ++rowIndex, CountData(list, year2, month2), cellStyleDefault2, cellStyleDefault4);
                }

                if (item.Id != lastId)
                {
                    AddData(ref sheet, dataIndex.ToString(), ++rowIndex, item, cellStyleDefault1, cellStyleDefault3);
                }
                #endregion
            }

            #endregion

            #region 收尾
            sheet.CreateRow(++rowIndex);
            for (int i = 0; i <= ColTotalIndex; i++)
            {
                sheet.CreateCell(i, "", sheet.CellStyle(top: BorderStyle.Medium, font: sheet.FontStyle(boldweight: (short)FontBoldWeight.Normal, fontsize: 12)));
            }
            #endregion
            return(sheet.GetWorkbook());
        }