Ошибка сохранения в 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();
}
}