Ошибка сохранения в Excel с помощью сводной таблицы, содержащей столбцы с формулой (невозможно преобразовать объект типа double в строку). Есть ли решение?

Сейчас я работаю над отчетом, который так сложно сделать вручную, поэтому я пишу код, который его создает. Код сейчас в порядке, но есть ошибка, которая так раздражает, и я понятия не имею, как ее решить. Так что проблема в том, что когда я пытаюсь сохранить свой файл Excel с помощью pivot, каждый раз выдает это исключение:

Ошибка сохранения

Я пытаюсь сделать файл Excel из кода полностью, а также пытаюсь записать данные в подготовленный шаблон Excel, но проблема в обоих случаях возникла. Когда я тестировал, чтобы сохранить файл с другими сводными данными, я узнал об этом, источник проблемы лежит в тех столбцах, которые содержат формулы. Если я добавлю столбцы в сводную таблицу, которая содержит только цифры, сохранение прошло успешно, в противном случае - нет. Есть ли решение, чтобы сохранить файл Excel с помощью сводки, которая содержит формулы? Для работы с Excel я использую ClosedXML.

Конец моего кода выглядит так:

var agentstatlist = (from a in avayaandsaxuserlist
                              join b in agentnamelist
                              on a.AvayaID equals b.AvayaID
                              join c in loggedintimelist
                              on a.AvayaID equals c.AvayaID into leftjoinedlist2
                              from c in leftjoinedlist2.DefaultIfEmpty()
                              join d in incomingcountlist
                              on a.AvayaID equals d.AvayaID into leftjoinedlist3
                              from d in leftjoinedlist3.DefaultIfEmpty()
                              join e in incomingavglist
                              on a.AvayaID equals e.AvayaID into leftjoinedlist4
                              from e in leftjoinedlist4.DefaultIfEmpty()
                              join f in outgoingcountlist
                              on a.AvayaID equals f.AvayaID into leftjoinedlist5
                              from f in leftjoinedlist5.DefaultIfEmpty()
                              join g in outgoingavglist
                              on a.AvayaID equals g.AvayaID into leftjoinedlist6
                              from g in leftjoinedlist6.DefaultIfEmpty()
                              join h in notreadylist
                              on a.AvayaID equals h.AvayaID into leftjoinedlist7
                              from h in leftjoinedlist7.DefaultIfEmpty()
                              join i in callnothandledlist
                              on a.AvayaID equals i.AvayaID into leftjoinedlist8
                              from i in leftjoinedlist8.DefaultIfEmpty()
                              join j in saxtimelist
                              on a.Sax equals j.Sax into leftjoinedlist9
                              from j in leftjoinedlist9.DefaultIfEmpty()
                              join k in loglist
                              on a.Sax equals k.Sax
                              join l in claimcreatedlist
                              on a.Sax equals l.Sax into leftjoinedlist11
                              from l in leftjoinedlist11.DefaultIfEmpty()
                              join m in servicecreatedlist
                              on a.Sax equals m.Sax into leftjoinedlist12
                              from m in leftjoinedlist12.DefaultIfEmpty()
                              join n in flyercreatedlist
                              on a.Sax equals n.Sax into leftjoinedlist13
                              from n in leftjoinedlist13.DefaultIfEmpty()
                              join o in flyercompletedlist
                              on a.Sax equals o.Sax into leftjoinedlist14
                              from o in leftjoinedlist14.DefaultIfEmpty()
                              join p in claimstatuschangedlist
                              on a.Sax equals p.Sax into leftjoinedlist15
                              from p in leftjoinedlist15.DefaultIfEmpty()
                              join q in servicestatuschangedlist
                              on a.Sax equals q.Sax into leftjoinedlist16
                              from q in leftjoinedlist16.DefaultIfEmpty()
                              join r in agentdelaylist
                              on a.AvayaID equals r.AvayaID into leftjoinedlist17
                              from r in leftjoinedlist17.DefaultIfEmpty()
                              join s in agentteamlist
                              on a.AvayaID equals s.AvayaID
                              join t in loggedintimesecundumlist
                              on a.AvayaID equals t.AvayaID into leftjoinedlist18
                              from t in leftjoinedlist18.DefaultIfEmpty()
                              where int.Parse(k.StringData) >= 1
                              where a.AvayaID != "xt. -"
                              select new
                              {
                                  a.Sax,
                                  a.AvayaID,
                                  Team = s.StringData,
                                  Name = b.StringData,
                                  LoggedInTimeSec = t == null ? null : t.StringData,
                                  LoggedInTime = c == null ? null : c.StringData,
                                  IncomingDB = d == null ? null : d.StringData,
                                  IncomingAverage = e == null ? null : e.StringData,
                                  OutgoingDB = f == null ? null : f.StringData,
                                  OutgoingAverage = g == null ? null : g.StringData,
                                  Notready = h == null ? null : h.StringData,
                                  CallNotHandle = i == null ? null : i.StringData,
                                  SaxTime = j == null ? null : j.StringData,
                                  LogCr = k.StringData,
                                  ClaimCr = l == null ? null : l.StringData,
                                  ServiceCr = m == null ? null : m.StringData,
                                  FlyerCr = n == null ? null : n.StringData,
                                  FlyerComp = o == null ? null : o.StringData,
                                  ClaimStatusCh = p == null ? null : p.StringData,
                                  ServiceStatusCh = q== null ? null : q.StringData,
                                  AgentDelay = r == null ? null : r.StringData
                              }).ToList();

        Logger.Info("Joining lists with linq query succesfully finished");

        Logger.Info("Creating datatable from joined list started");
        DataTable agentsDataTable = new DataTable();

        using (var reader = ObjectReader.Create(agentstatlist, "Sax", "AvayaID", "Name", "Team", "LoggedInTimeSec", "LoggedInTime", "IncomingDB", "IncomingAverage", "OutgoingDB", "OutgoingAverage", "Notready", "CallNotHandle", "SaxTime",
                   "LogCr", "ClaimCr", "ServiceCr", "FlyerCr", "FlyerComp", "ClaimStatusCh", "ServiceStatusCh", "AgentDelay"))
        {
            agentsDataTable.Load(reader);
        }

        string filename = @"OutputFile\AgentSummary"+DateTime.Now.ToString("yyyyMMddhhmmss")+".xlsx";

        Logger.Info("Creating datatable from joined list was succesfully finished");
        Logger.Info("Writing data into excel file started");

        DataHandler.SaveDataAsExcel(agentsDataTable, filename, "AgentEfficiency");


        //
        Logger.Info("Creating excel formulas started");

        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "SaxTime%", @"=IF(E? = """","""",(M?/E?))");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "In/h", @"=IF(E? = """","""",(G?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "Out/h", @"=IF(E? = """","""",(H?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "Log/h", @"=IF(E? = """","""",(N?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "ClaimCr/h", @"=IF(E? = """","""",(O?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "ServiceCr/h", @"=IF(E? = """","""",(P?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "FlyerCr/h", @"=IF(E? = """","""",(Q?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "FlyerComp/h", @"=IF(E? = """","""",(R?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "ClaimStatusCh/h", @"=IF(E? = """","""",(S?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "ServiceStatusCh/h", @"=IF(E? = """","""",(T?/E?)*3600)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "Summary/h", "=SUM(V?:AE?)");
        DataHandler.AddNewColumnWithFormula(filename, "AgentEfficiency", "Efficiency", @"=IF(V? = """","""",(AF?/V?))");

        XLWorkbook xl = new XLWorkbook(filename);

        xl.Cells("E:AF").Style.NumberFormat.Format = "#,##0.00";
        xl.Save();

        Logger.Info("Creating Pivot started");
        // add new pivot table. Update lastRow and lastColumn if necessary
        DataHandler.AddPivotToExcel(filename, "TeamEfficiency", "AgentEfficiency", "Pivot1",
            agentsDataTable.Rows.Count + 1, agentsDataTable.Columns.Count+12,
            new List<string> { "Team" }, new List<string>(),
            new List<Tuple<string, string, XLPivotSummary>> {
                new Tuple<string, string, XLPivotSummary>("LoggedInTime", "Sum of LoggedInTime", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("IncomingDB", "Sum of IncomingDB", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("IncomingAverage", "Average of IncomingAverage", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>("OutgoingDB", "Sum of OutgoingDB", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("OutgoingAverage", "Average of OutgoingAverage", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>("Notready", "Average of Notready", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>("CallNotHandle", "Sum of CallnotHandle", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("SaxTime", "Sum of SaxTime", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("LogCr", "Sum of Log", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("ClaimCr", "Sum of ClaimCr", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("ServiceCr", "Sum of ServiceCr", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("FlyerCr", "Sum of FlyerCr", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("FlyerComp", "Sum of FlyerCompleted", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("ClaimStatusCh", "Sum of ClaimStatusCh", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("ServiceStatusCh", "Sum of ServiceStatusCh", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>("AgentDelay", "Sum of AgentDelay", XLPivotSummary.Sum),
                new Tuple<string, string, XLPivotSummary>(@"SaxTime%", @"Average of SaxTime%", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"In/h", @"Average of In/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"Out/h", @"Average of Out/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"Log/h", @"Average of Log/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"ClaimCr/h", @"Average of ClaimCr/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"ServiceCr/h", @"Average of ServiceCr/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"FlyerCr/h", @"Average of FlyerCr/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"FlyerComp/h", @"Average of FlyerComp/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"ClaimStatusCh/h", @"Average of ClaimStatusCh/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"ServiceStatusCh/h", @"Average of ServiceStatusCh/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>(@"Summary/h", @"Average of Summary/h", XLPivotSummary.Average),
                new Tuple<string, string, XLPivotSummary>("Efficiency", "Average of Efficiency", XLPivotSummary.Average)
            });

        Logger.Info("Writing data to excel successfully finished");
    }

И мой метод создателя сводок, как это:

 public static void AddPivotToExcel(string fileName, string pivotSheetName, string sourceSheetName, string pivotName,
        int lastRow, int lastColumn, List<string> rowLabels, List<string> columnLabels, List<Tuple<string, string, XLPivotSummary>> values)
    {
        using (var workBook = new XLWorkbook(fileName))
        {
            var pivotSheet = workBook.Worksheets.Add(pivotSheetName);

            IXLWorksheet sourceSheet;
            if (!workBook.TryGetWorksheet(sourceSheetName, out sourceSheet))
            {
                throw new InvalidOperationException("Sheet " + sourceSheetName + " does not exist in " + fileName);
            }

            var dataRange = sourceSheet.Range(1, 1, lastRow, lastColumn);
            //var dataRange = sourceSheet.Range(sourceSheet.FirstCell(), sourceSheet.LastCell());

            var pivot = pivotSheet.PivotTables.Add(pivotName, pivotSheet.Cell(1, 1), dataRange);

            foreach (var item in rowLabels)
            {
                pivot.RowLabels.Add(item);
            }

            foreach (var item in columnLabels)
            {
                pivot.ColumnLabels.Add(item);
            }

            foreach (var item in values)
            {
                // The default calculation setting is a total of each row/column
                var addedValue = pivot.Values.Add(item.Item1, item.Item2);
                addedValue.SetSummaryFormula(item.Item3);
            }        

            pivotSheet.Columns().AdjustToContents();

            workBook.Save();
        }
    }

0 ответов

Другие вопросы по тегам