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