private static void setValue(NPOI.SS.UserModel.ISheet sheet, int rowIndex, int colIndex, double value) { IRow row = sheet.GetRow(rowIndex); if (row == null) { row = sheet.CreateRow(rowIndex); } row.CreateCell(colIndex).SetCellValue(value); }
public static void ExportReportHeader(string title, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber) { var row = sheet.CreateRow(rowNumber++); row.HeightInPoints = 27; var titleCell = row.CreateCell(0); titleCell.SetCellValue(title); titleCell.CellStyle = allStyles.HeaderStyle; titleCell.CellStyle.WrapText = true; var titleMerge = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 7); sheet.AddMergedRegion(titleMerge); row = sheet.CreateRow(rowNumber++); }
private static void ExecuteResultExtracted(NPOI.SS.UserModel.ISheet sheet, int r, MucketyItem p) { var row = sheet.CreateRow(r); row.CreateCell(0).SetCellValue(p.actor1); row.CreateCell(1).SetCellValue(p.actor1type); row.CreateCell(2).SetCellValue(p.actor1deceased); row.CreateCell(3).SetCellValue(p.actor2); row.CreateCell(4).SetCellValue(p.actor2type); row.CreateCell(5).SetCellValue(p.actor2deceased); row.CreateCell(6).SetCellValue(p.relation); row.CreateCell(7).SetCellValue(p.former); }
protected static void CreateHeader(NPOI.SS.UserModel.ISheet excelSheet, List<string> columList) { int rownum = MaxLevel_ - 1 > 0 ? MaxLevel_ - 2 : 0; NPOI.SS.UserModel.IRow newRow = excelSheet.CreateRow(rownum); for (int k = 0; k < columList.Count; k++) { NPOI.SS.UserModel.ICell newCell = newRow.CreateCell(k); newCell.SetCellValue(columList[k]); } }
/// <summary> /// 创建excel表头 /// </summary> /// <param name="dgv"></param> /// <param name="excelSheet"></param> protected static void CreateHeader(NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkbook) { int cellIndex = 0; NPOI.SS.UserModel.IRow row1 = excelSheet.CreateRow(0); NPOI.SS.UserModel.ICellStyle cellStyle = excelWorkbook.CreateCellStyle(); cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; //cellStyle.FillPattern = HSSFCellStyle.SQUARES; cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; //循环导出列 foreach (System.Collections.DictionaryEntry de in _listColumnsName) { NPOI.SS.UserModel.ICell cell = row1.CreateCell(cellIndex); cell.CellStyle = cellStyle; string cellName = de.Value.ToString().Trim(); cell.SetCellValue(cellName); excelSheet.SetColumnWidth(cellIndex, 15 * 256); cellIndex++; } }
private void ExportReportSubheader(NPOI.SS.UserModel.ISheet sheet, vmAdmin_PerformanceFilter filter, StyleContainer allStyles, ref int rowNumber) { var row = sheet.CreateRow(rowNumber++); if (filter.EventId.HasValue && filter.EventId.Value > 0) { var thisEvent = _service.GetEventById(filter.EventId.Value); ReportUtilities.CreateCell(row, 0, string.Format("{0} - {1}", thisEvent.GeneralLocality, thisEvent.EventDates.Min().DateOfEvent.ToShortDateString()), allStyles.Header2Style); row = sheet.CreateRow(rowNumber++); } else { if (filter.startDate.HasValue) { var value = string.Format("Events from {0} to {1}", filter.startDate.Value.ToShortDateString(), filter.endDate.Value.ToShortDateString()); ReportUtilities.CreateCell(row, 0, value, allStyles.Header2Style); row = sheet.CreateRow(rowNumber++); } } }
private static void ExportReportValues(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber) { var row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Event Revenue", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 1, report.TotalRevenue, allStyles.Currency); ReportUtilities.CreateCell(row, 2, "Available Spots", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 3, report.TotalSpots, allStyles.RightAligned); ReportUtilities.CreateCell(row, 4, "Fee Total", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 5, report.FeeValue, allStyles.Currency); ReportUtilities.CreateCell(row, 6, "Charge Total", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 7, report.ChargeValue, allStyles.Currency); row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Days count", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 1, report.DayCount, allStyles.RightAligned); ReportUtilities.CreateCell(row, 2, "Active Registrations", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 3, report.SpotsTaken, allStyles.RightAligned); ReportUtilities.CreateCell(row, 4, "Discount value", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 5, report.DiscountValue, allStyles.Currency); ReportUtilities.CreateCell(row, 6, "Local Tax", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 7, report.ChargeLocalTaxValue, allStyles.Currency); row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Event count", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 1, report.EventCount, allStyles.RightAligned); ReportUtilities.CreateCell(row, 2, "Spots Available", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 3, report.SpotsLeft, allStyles.RightAligned); ReportUtilities.CreateCell(row, 4, "Local Tax", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 5, report.LocalTaxValue, allStyles.Currency); ReportUtilities.CreateCell(row, 6, "State Tax", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 7, report.ChargeStateTaxValue, allStyles.Currency); row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Revenue Per Day", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 1, report.RevenuePerDay, allStyles.Currency); ReportUtilities.CreateCell(row, 2, "Registrations Per Day", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 3, report.RegPerDay, allStyles.RightAligned); ReportUtilities.CreateCell(row, 4, "State Tax", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 5, report.StateTaxValue, allStyles.Currency); ReportUtilities.CreateCell(row, 6, "Total Revenue", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 7, report.ChargeActualRevenue, allStyles.Currency); row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Revenue Per Event", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 1, report.RevenuePerEvent, allStyles.Currency); row.CreateCell(2); row.CreateCell(3); ReportUtilities.CreateCell(row, 4, "Total Revenue", allStyles.LeftAligned); ReportUtilities.CreateCell(row, 5, report.FeeActualRevenue, allStyles.Currency); row.CreateCell(6); row.CreateCell(7); row = sheet.CreateRow(rowNumber++); }
private static void ExportReportTshirts(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber) { if (report.TShirtSizes != null && report.TShirtSizes.Count > 0) { var row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "T-Shirts", allStyles.Header3Style); foreach (var size in report.TShirtSizes) { row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, size.Keys.ElementAt(0).ToString(), allStyles.LeftAligned); ReportUtilities.CreateCell(row, 1, size.Values.ElementAt(0), allStyles.RightAligned); } row = sheet.CreateRow(rowNumber++); } }
private static void ExportReportFees(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber) { if (report.FeeReport.Count > 0) { var row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Event Fees", allStyles.Header2Style); foreach (var feeType in report.FeeReport.GroupBy(x => x.FeeType).Select(x => x.Key)) { row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, feeType.ToString(), allStyles.Header3Style); row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Cost", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 1, "Use Count", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 2, "Cost Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 3, "Discount Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 4, "Local Tax Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 5, "State Tax Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 6, "Actual Total", allStyles.TitleStyle); foreach (var fee in report.FeeReport.Where(x => x.FeeType == feeType).OrderBy(x => x.Cost)) { row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, fee.Cost, allStyles.Currency); ReportUtilities.CreateCell(row, 1, fee.UseCount, allStyles.RightAligned); ReportUtilities.CreateCell(row, 2, fee.CostTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 3, fee.DiscountTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 4, fee.LocalTaxTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 5, fee.StateTaxTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 6, fee.ActualTotal, allStyles.Currency); } row = sheet.CreateRow(rowNumber++); } } }
private static void ExportReportCharges(vmAdmin_PerformanceReport report, NPOI.SS.UserModel.ISheet sheet, StyleContainer allStyles, ref int rowNumber) { if (report.ChargeReport.Count > 0) { var row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Event Charges", allStyles.Header2Style); row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, "Name", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 1, "Cost Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 2, "Discount Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 3, "Local Tax Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 4, "State Tax Total", allStyles.TitleStyle); ReportUtilities.CreateCell(row, 5, "Actual Total", allStyles.TitleStyle); foreach (var charge in report.ChargeReport.OrderBy(x => x.Name)) { row = sheet.CreateRow(rowNumber++); ReportUtilities.CreateCell(row, 0, charge.Name, allStyles.LeftAligned); ReportUtilities.CreateCell(row, 1, charge.CostTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 2, charge.DiscountTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 3, charge.LocalTaxTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 4, charge.StateTaxTotal, allStyles.Currency); ReportUtilities.CreateCell(row, 5, charge.ActualTotal, allStyles.Currency); } } }