コード例 #1
0
        private void excel_WriteExcelEvent(object sender, ExcelHelper.WriteExcelEventArgs e)
        {
            IList <WarehouseIn> warehouseIns = e.UserData as IList <WarehouseIn>;
            Workbook            workBook     = e.ExceWorkbook;

            foreach (var warehouseIn in warehouseIns)
            {
                var worksheet = (Worksheet)workBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                worksheet.Activate();
                worksheet.Name = "签收表" + workBook.Worksheets.Count;

                worksheet.Cells[1, 1] = ("维修材料签收表");
                worksheet.Cells[2, 6] = ("日期");
                worksheet.Cells[2, 7] = (warehouseIn.BillDate.ToShortDateString());

                worksheet.Cells[3, 1] = ("序号");
                worksheet.Cells[3, 2] = ("物品名称");
                worksheet.Cells[3, 3] = ("牌子");
                worksheet.Cells[3, 4] = ("规格型号");
                worksheet.Cells[3, 5] = ("数量");
                worksheet.Cells[3, 6] = ("单位");
                worksheet.Cells[3, 7] = ("备注");

                int rowIndex = 4;
                foreach (var item in warehouseIn.Items)
                {
                    worksheet.Cells[rowIndex, 1] = (rowIndex - 3);
                    worksheet.Cells[rowIndex, 2] = (item.ProductName);
                    worksheet.Cells[rowIndex, 3] = (item.Brand);
                    worksheet.Cells[rowIndex, 4] = (item.Specification);
                    worksheet.Cells[rowIndex, 5] = (item.Quantity);
                    worksheet.Cells[rowIndex, 6] = (item.Unit);
                    worksheet.Cells[rowIndex, 7] = (item.Memo);
                    rowIndex++;
                }
                //var rowCount = rowIndex - 4 + 18;
                for (; rowIndex < 22; rowIndex++)
                {
                    worksheet.Cells[rowIndex, 1] = (rowIndex - 3);
                    worksheet.Cells[rowIndex, 2] = "";
                    worksheet.Cells[rowIndex, 3] = "";
                    worksheet.Cells[rowIndex, 4] = "";
                    worksheet.Cells[rowIndex, 5] = "";
                    worksheet.Cells[rowIndex, 6] = "";
                    worksheet.Cells[rowIndex, 7] = "";
                }
                worksheet.Cells[rowIndex + 1, 1] =
                    (string.Format("送货单位:{0,-17}  收货人:{1,-10}  复核:{2,-10}  审批:{3,-10}", warehouseIn.Vender, warehouseIn.ReceivedBy, warehouseIn.ReviewedBy, warehouseIn.AuditBy));


                ((Range)worksheet.Cells[1, 1]).ColumnWidth = 5;
                ((Range)worksheet.Cells[1, 2]).ColumnWidth = 20;
                ((Range)worksheet.Cells[1, 3]).ColumnWidth = 13;
                ((Range)worksheet.Cells[1, 4]).ColumnWidth = 13;
                ((Range)worksheet.Cells[1, 5]).ColumnWidth = 8;
                ((Range)worksheet.Cells[1, 6]).ColumnWidth = 8;
                ((Range)worksheet.Cells[1, 7]).ColumnWidth = 14;

                Range rangeTitle = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 7]);
                rangeTitle.Merge();
                rangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rangeTitle.VerticalAlignment   = XlVAlign.xlVAlignCenter;
                rangeTitle.Font.Bold           = true;
                rangeTitle.Font.Size           = 24;
                rangeTitle.RowHeight           = 35;

                Range rangeDate = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 7]);
                rangeDate.RowHeight = 25;

                Range rangeHeader = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 7]);
                rangeHeader.Font.Bold           = true;
                rangeHeader.Font.Size           = 12;
                rangeHeader.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rangeHeader.VerticalAlignment   = XlVAlign.xlVAlignCenter;
                rangeHeader.RowHeight           = 27;

                Range rangeData = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowIndex - 1, 7]);
                rangeData.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                rangeData.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
                rangeData.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
                rangeData.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle        = XlLineStyle.xlContinuous;
                rangeData.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
                rangeData.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
                rangeData.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                rangeData.VerticalAlignment   = XlVAlign.xlVAlignCenter;
                rangeData.RowHeight           = 33;

                worksheet.get_Range(worksheet.Cells[rowIndex, 1], worksheet.Cells[rowIndex, 7]).RowHeight = 24;

                Range rangeFooter = worksheet.get_Range(worksheet.Cells[rowIndex + 1, 1], worksheet.Cells[rowIndex + 1, 7]);
                rangeFooter.RowHeight = 24;
                rangeFooter.Merge();

                worksheet.PageSetup.TopMargin    = e.ExcelApp.CentimetersToPoints(1.5);
                worksheet.PageSetup.BottomMargin = e.ExcelApp.CentimetersToPoints(1.5);
                worksheet.PageSetup.LeftMargin   = e.ExcelApp.CentimetersToPoints(1.4);
                worksheet.PageSetup.RightMargin  = e.ExcelApp.CentimetersToPoints(1.4);
                worksheet.PageSetup.HeaderMargin = e.ExcelApp.CentimetersToPoints(1);
                worksheet.PageSetup.FooterMargin = e.ExcelApp.CentimetersToPoints(1);
            }
        }
コード例 #2
0
        private void excel_WriteExcelEvent(object sender, ExcelHelper.WriteExcelEventArgs e)
        {
            IList <WarehouseInItem> items = e.UserData as IList <WarehouseInItem>;
            Workbook  workBook            = e.ExceWorkbook;;
            Worksheet worksheet           = (Worksheet)workBook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            worksheet.Name        = "维修材料清单";
            worksheet.Cells[1, 1] = ("惠东文化广场管理所维修材料清单");
            worksheet.Cells[2, 7] = ("日期");
            worksheet.Cells[2, 8] = (DateTime.Now.ToShortDateString());

            worksheet.Cells[3, 1] = ("序号");
            worksheet.Cells[3, 2] = ("物品名称");
            worksheet.Cells[3, 3] = ("牌子");
            worksheet.Cells[3, 4] = ("规格型号");
            worksheet.Cells[3, 5] = ("数量");
            worksheet.Cells[3, 6] = ("单位");
            worksheet.Cells[3, 7] = ("单价(元)");
            worksheet.Cells[3, 8] = ("总价(元)");

            int rowIndex = 4;

            foreach (var item in items)
            {
                worksheet.Cells[rowIndex, 1] = (rowIndex - 3);
                worksheet.Cells[rowIndex, 2] = (item.ProductName);
                worksheet.Cells[rowIndex, 3] = (item.Brand);
                worksheet.Cells[rowIndex, 4] = (item.Specification);
                worksheet.Cells[rowIndex, 5] = (item.Quantity);
                worksheet.Cells[rowIndex, 6] = (item.Unit);
                worksheet.Cells[rowIndex, 7] = (item.UnitPrice);
                worksheet.Cells[rowIndex, 8] = (string.Format("=E{0}*G{0}", rowIndex));
                rowIndex++;
            }
            //var rowCount = rowIndex - 4 + 27;
            for (; rowIndex < 31; rowIndex++)
            {
                worksheet.Cells[rowIndex, 1] = (rowIndex - 3);
                worksheet.Cells[rowIndex, 2] = "";
                worksheet.Cells[rowIndex, 3] = "";
                worksheet.Cells[rowIndex, 4] = "";
                worksheet.Cells[rowIndex, 5] = "";
                worksheet.Cells[rowIndex, 6] = "";
                worksheet.Cells[rowIndex, 7] = "";
            }
            worksheet.Cells[rowIndex, 6] = ("综合费用");
            worksheet.Cells[rowIndex, 8] = (string.Format("=sum(H2:H{0})", rowIndex - 1));

            ((Range)worksheet.Cells[1, 1]).ColumnWidth = 5;
            ((Range)worksheet.Cells[1, 2]).ColumnWidth = 15;
            ((Range)worksheet.Cells[1, 3]).ColumnWidth = 11;
            ((Range)worksheet.Cells[1, 4]).ColumnWidth = 12;
            ((Range)worksheet.Cells[1, 5]).ColumnWidth = 6.5;
            ((Range)worksheet.Cells[1, 6]).ColumnWidth = 6.5;
            ((Range)worksheet.Cells[1, 7]).ColumnWidth = 11;
            ((Range)worksheet.Cells[1, 8]).ColumnWidth = 12;

            Range rangeTitle = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 8]);

            rangeTitle.Merge();
            rangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rangeTitle.VerticalAlignment   = XlVAlign.xlVAlignCenter;
            rangeTitle.Font.Bold           = true;
            rangeTitle.Font.Size           = 24;
            rangeTitle.RowHeight           = 28.5;

            Range rangeSpace = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 8]);

            rangeSpace.RowHeight = 25;

            Range rangeHeader = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, 8]);

            rangeHeader.Font.Bold           = true;
            rangeHeader.Font.Size           = 12;
            rangeHeader.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rangeHeader.VerticalAlignment   = XlVAlign.xlVAlignCenter;
            rangeHeader.RowHeight           = 21;

            Range rangeData = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowIndex, 8]);

            rangeData.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
            rangeData.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle   = XlLineStyle.xlContinuous;
            rangeData.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle         = XlLineStyle.xlContinuous;
            rangeData.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle        = XlLineStyle.xlContinuous;
            rangeData.Borders.get_Item(XlBordersIndex.xlEdgeTop).LineStyle          = XlLineStyle.xlContinuous;
            rangeData.Borders.get_Item(XlBordersIndex.xlEdgeBottom).LineStyle       = XlLineStyle.xlContinuous;
            rangeData.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rangeData.VerticalAlignment   = XlVAlign.xlVAlignCenter;
            rangeData.RowHeight           = 21;

            Range rangeTotal = worksheet.get_Range(worksheet.Cells[rowIndex, 6], worksheet.Cells[rowIndex, 7]);

            rangeTotal.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rangeTotal.VerticalAlignment   = XlVAlign.xlVAlignCenter;
            rangeTotal.Font.Bold           = true;
            rangeTotal.Font.Size           = 12;
            rangeTotal.RowHeight           = 21;
            rangeTotal.Merge();

            Range rangeUnitPrice = worksheet.get_Range(worksheet.Cells[3, 7], worksheet.Cells[rowIndex - 1, 7]);

            rangeUnitPrice.NumberFormat = "0.00";
            Range rangeAmount = worksheet.get_Range(worksheet.Cells[3, 8], worksheet.Cells[rowIndex, 8]);

            rangeAmount.NumberFormat        = "0.00";
            rangeAmount.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            rangeAmount.VerticalAlignment   = XlVAlign.xlVAlignCenter;

            worksheet.PageSetup.TopMargin    = e.ExcelApp.CentimetersToPoints(2.5);
            worksheet.PageSetup.BottomMargin = e.ExcelApp.CentimetersToPoints(2.5);
            worksheet.PageSetup.LeftMargin   = e.ExcelApp.CentimetersToPoints(1.4);
            worksheet.PageSetup.RightMargin  = e.ExcelApp.CentimetersToPoints(1.4);
            worksheet.PageSetup.HeaderMargin = e.ExcelApp.CentimetersToPoints(1.3);
            worksheet.PageSetup.FooterMargin = e.ExcelApp.CentimetersToPoints(1.3);
        }