Export excel as stream
Hello, I trying to export the blazor pivot table to excel stream file then download it. I found out the below code does not return a stream :
await PivotView.ExportToExcelAsync(ExportProperties, false, null,true);
Any Suggestions.
Thank you.
Hi Ali,
We regret to inform you that we currently only allow PDF exporting for saving pivot table data as a stream. Therefore, our Blazor Pivot table currently doesn't have support for exporting Excel data as stream/blobs.
Please refer the below document to know saving the PDF document as stream.
Document: https://blazor.syncfusion.com/documentation/pivot-table/pdf-export#saving-pdf-document-to-stream
Please let us know if you have any concerns.
Regards,
Angelin Faith Sheeba.
Hello, Thanks for the reply.
Hello, I Believe this method can be extended to allow export to stream to make the control stand out of its competitors. However I found out the below code when I decompile the Exporttoexcelasync function. Can we extend the method or create another method to return the stream. I tried to mimic the code but found to be difficult. Please Review. Thank You and syncfusion is the best.
internal async Task ExportToExcel(string type, ExcelExportProperties excelExportProperties = null)
{
using Workbook book = new Workbook();
Worksheet workSheet = book.Worksheets[0];
if (Parent.DataType.Equals(DataMode.OLAP))
{
olapEngine = Parent.OlapEngine;
}
else
{
pivotEngine = Parent.PivotEngine;
}
currentPivotValues = PivotUtils.ClonePivotValues(Parent.DataType.Equals(DataMode.Pivot) ? pivotEngine.PivotValues : olapEngine.PivotValues, skipMembers: true, skipIndexInfo: false);
if (Parent.ExportAllPages && (Parent.EnablePaging || Parent.EnableVirtualization) && !Parent.DataSourceSettings.EnableServerSideAggregation)
{
pageSettings = (Parent.DataType.Equals(DataMode.Pivot) ? pivotEngine.PageSettings : olapEngine.PageSettings);
if (Parent.DataType.Equals(DataMode.Pivot))
{
pivotEngine.PageSettings = null;
await pivotEngine.GenerateTableInfo();
clonedValues = PivotUtils.ClonePivotValues(pivotEngine.PivotValues);
SfPivotView<TValue> parent = Parent;
Matrix<Matrix<AxisSet>> matrix2 = (pivotEngine.PivotValues = currentPivotValues);
parent.PivotValues = matrix2;
pivotEngine.PageSettings = pageSettings;
}
else
{
_ = string.Empty;
UpdateOlapSettings(isUpdate: true);
string mdxQuery = Parent.OlapEngine.MdxQuery;
await olapEngine.GenerateTableInfo();
clonedValues = PivotUtils.ClonePivotValues(olapEngine.PivotValues);
SfPivotView<TValue> parent2 = Parent;
Matrix<Matrix<AxisSet>> matrix2 = (olapEngine.PivotValues = currentPivotValues);
parent2.PivotValues = matrix2;
olapEngine.PageSettings = pageSettings;
UpdateOlapSettings(isUpdate: false);
Parent.OlapEngine.MdxQuery = mdxQuery;
}
Parent.ApplyFormatting(Parent.Engine.PivotValues);
if (Parent.PivotViewEvents?.EnginePopulated.HasDelegate ?? false)
{
EnginePopulatedEventArgs args = new EnginePopulatedEventArgs
{
PivotValues = clonedValues
};
await SfBaseUtils.InvokeEvent(Parent.PivotViewEvents.EnginePopulated, args);
clonedValues = args.PivotValues;
}
}
else
{
clonedValues = currentPivotValues;
}
pivotValues = clonedValues;
string fileName = ((!string.IsNullOrEmpty(excelExportProperties?.FileName)) ? excelExportProperties.FileName : "default");
if (Parent.PivotViewEvents?.BeforeExport.HasDelegate ?? false)
{
BeforeExportEventArgs args2 = new BeforeExportEventArgs
{
ExcelExportProperties = excelExportProperties,
FileName = fileName,
Header = string.Empty,
Footer = string.Empty,
DataCollections = clonedValues
};
await SfBaseUtils.InvokeEvent(Parent.PivotViewEvents.BeforeExport, args2);
pivotValues = args2.DataCollections;
fileName = args2.FileName;
excelExportProperties = args2.ExcelExportProperties;
}
List<Cell> cells = new List<Cell>();
int colLen = 0;
int actualrCnt = 0;
formatList = Parent.RenderModule.GetFormatList();
List<string> advanceAggTypes = new List<string> { "PercentageOfParentRowTotal", "PercentageOfParentColumnTotal", "PercentageOfParentTotal", "PercentageOfDifferenceFrom", "DifferenceFrom", "RunningTotals" };
int maxLevel = 0;
int rowlength = pivotValues.Count();
char[] formats = new char[6] { 'N', 'P', 'C', 'n', 'p', 'c' };
Regex rexExpression = new Regex("\\D+", RegexOptions.Compiled);
if (excelExportProperties?.Header != null)
{
int headerRows = excelExportProperties.Header.HeaderRows;
for (int i = 0; i < headerRows; i++)
{
actualrCnt++;
row = workSheet.Rows.Add();
row.Index = actualrCnt;
int num = 0;
if (excelExportProperties.Header.Rows.Count != i)
{
num = ((excelExportProperties.Header.Rows[i].Cells != null) ? excelExportProperties.Header.Rows[i].Cells.Count : 0);
}
if (num > 0)
{
for (int j = 0; j < num; j++)
{
this.cell = row.Cells.Add();
this.cell.Index = j + 1;
SetRowCellValues(row.Cells[j], excelExportProperties.Header.Rows[i].Cells[j]);
}
}
}
}
for (int rowCnt = 0; rowCnt < rowlength; rowCnt++)
{
if (pivotValues[rowCnt] == null)
{
continue;
}
actualrCnt++;
row = workSheet.Rows.Add();
row.Index = actualrCnt;
colLen = pivotValues[rowCnt].Count();
for (int colCnt = 0; colCnt < colLen; colCnt++)
{
if (pivotValues[rowCnt][colCnt] != null)
{
bool flag = false;
AxisSet pivotCell = pivotValues[rowCnt][colCnt];
bool flag2 = string.IsNullOrEmpty(pivotCell.FormattedText);
if ((pivotCell.Level != -1 || pivotCell.RowSpan != -1) && (pivotCell.Level != -1 || pivotCell.RowSpan != 0))
{
object value = pivotCell.FormattedText;
if (pivotCell.Type != null)
{
if (pivotCell.Type.Equals("grand sum", StringComparison.Ordinal))
{
value = Parent.Localizer.GetText(LocaleKeys.GrandTotal);
}
else if (pivotCell.Type.Equals("sum", StringComparison.Ordinal) && ((pivotCell.Axis == "row" && pivotCell.MemberType != 3) || pivotCell.Axis == "column"))
{
value = pivotCell.FormattedText.Replace("Total", Parent.Localizer.GetText(LocaleKeys.Total), StringComparison.Ordinal);
}
}
else if (pivotCell.Axis.Equals("value", StringComparison.Ordinal))
{
if ((!pivotCell.Value.HasValue && flag2) || Parent.ExportType == "csv" || (!pivotCell.Value.HasValue && !flag2) || (pivotCell.Value.GetValueOrDefault() == 0.0 && pivotCell.FormattedText != pivotCell.Value.GetValueOrDefault().ConvertToString()))
{
flag = true;
value = ((!pivotCell.Value.HasValue && flag2) ? string.Empty : pivotCell.FormattedText);
}
else
{
flag = false;
string text = pivotCell.ActualText?.ConvertToString();
Field field = ((!string.IsNullOrEmpty(text) && Parent.DataType.Equals(DataMode.Pivot)) ? Parent.Engine.FieldList[text] : null);
if (field != null && advanceAggTypes.Contains(field.AggregateType))
{
PivotEngine<TValue> obj = Parent.PivotEngine;
double? obj2;
if (obj == null)
{
obj2 = null;
}
else
{
double[] obj3 = obj.AggregateValueMatrix[pivotCell.RowIndex];
obj2 = ((obj3 != null) ? new double?(obj3[pivotCell.ColIndex]) : null);
}
double? num2 = obj2;
if (num2.HasValue)
{
value = num2;
}
}
else
{
value = pivotCell.Value;
}
}
}
if (pivotCell.Level != -1 || pivotCell.RowSpan != 0)
{
this.cell = row.Cells.Add();
this.cell.Index = colCnt + 1;
this.cell.Value = value;
this.cell.ColumnSpan = pivotCell.ColSpan;
this.cell.RowSpan = ((pivotCell.RowSpan == -1) ? 1 : pivotCell.RowSpan.ConvertToInt32());
cells.Add(this.cell);
int count = cells.Count;
Cell cell = cells[count - 1];
if (pivotCell.Style != null)
{
cell.CellStyle.BackColor = pivotCell.Style.BackgroundColor;
cell.CellStyle.FontColor = pivotCell.Style.Color;
cell.CellStyle.FontName = pivotCell.Style.FontFamily;
cell.CellStyle.FontSize = pivotCell.Style.FontSize.Split("px")[0].ConvertToInt32();
}
else
{
cell.CellStyle.FontColor = "#000000";
cell.CellStyle.FontName = "Calibri";
cell.CellStyle.FontSize = 10;
}
if (pivotCell.Axis.Equals("value", StringComparison.Ordinal))
{
string key = ((Parent.DataSourceSettings.ValueAxis.Equals("row", StringComparison.Ordinal) && Parent.DataType.Equals(DataMode.OLAP) && pivotCell.RowOrdinal != 0 && olapEngine.TupleRowInfo[pivotCell.RowOrdinal] != null) ? olapEngine.TupleRowInfo[pivotCell.RowOrdinal].MeasureName : (pivotCell.ActualText as string));
string value2;
bool flag3 = formatList.TryGetValue(key, out value2);
if (pivotCell.Value == 0.0 && flag3 && !flag2 && Parent.ExportType != "csv")
{
if (Intl.GetNumericFormat(Convert.ToDouble(pivotCell.Value, CultureInfo.InvariantCulture), value2) == pivotCell.FormattedText)
{
flag = false;
cell.Value = pivotCell.Value;
}
else
{
flag = true;
cell.Value = pivotCell.FormattedText;
}
}
if (flag2)
{
flag = true;
cell.Value = string.Empty;
}
cell.CellStyle.HAlign = HAlignType.Right;
if (flag3 && !flag)
{
cell.CellStyle.NumberFormat = ((value2.IndexOfAny(formats) == -1) ? value2 : PivotUtils.GetNumberFormat(value2, cell, Intl.GetCulture(), rexExpression));
}
cell.CellStyle.Bold = false;
cell.CellStyle.WrapText = true;
}
else
{
cell.CellStyle.Bold = true;
cell.CellStyle.VAlign = VAlignType.Center;
cell.CellStyle.WrapText = true;
cell.CellStyle.Indent = ((colCnt == 0) ? (pivotCell.Level * 10) : 0);
if (pivotCell.Axis.Equals("row", StringComparison.Ordinal) && colCnt == 0)
{
cell.CellStyle.HAlign = HAlignType.Left;
if (Parent.DataType.Equals(DataMode.OLAP))
{
int num3 = Parent.RenderModule.IndentCollection[rowCnt];
cell.CellStyle.Indent = num3 * 2;
maxLevel = ((maxLevel > num3) ? maxLevel : num3);
}
else
{
cell.CellStyle.Indent = pivotCell.Level * 2;
maxLevel = ((pivotCell.Level > maxLevel) ? pivotCell.Level : maxLevel);
}
}
else if (pivotCell.Axis.Equals("value", StringComparison.Ordinal))
{
cell.CellStyle.HAlign = HAlignType.Right;
}
}
cell.CellStyle.Borders.All.Color = "#000000";
cell.CellStyle.Borders.All.LineStyle = LineStyle.Thin;
if (pivotCell.Axis.Equals("column", StringComparison.Ordinal))
{
ExcelHeaderQueryCellInfoEventArgs obj4 = new ExcelHeaderQueryCellInfoEventArgs
{
Style = cell.CellStyle,
Cell = cell,
RowIndex = rowCnt,
ColumnIndex = colCnt
};
(Parent.PivotViewEvents?.ExcelHeaderQueryCellInfo)?.Invoke(obj4);
}
else if (Parent.PivotViewEvents?.ExcelQueryCellInfo.HasDelegate ?? false)
{
ExcelQueryCellInfoEventArgs<TValue> eventArgs = new ExcelQueryCellInfoEventArgs<TValue>
{
Style = cell.CellStyle,
ColumnIndex = colCnt,
RowIndex = rowCnt,
Value = cell.Value,
Cell = cell
};
await SfBaseUtils.InvokeEvent(Parent.PivotViewEvents.ExcelQueryCellInfo, eventArgs);
}
}
}
colCnt += ((pivotCell.ColSpan != 0) ? (pivotCell.ColSpan - 1) : 0);
}
else
{
this.cell = row.Cells.Add();
this.cell.Index = colCnt + 1;
this.cell.Value = string.Empty;
this.cell.ColumnSpan = 1;
this.cell.RowSpan = 1;
cells.Add(this.cell);
}
}
}
List<Column> list = new List<Column>();
for (int k = 0; k < colLen; k++)
{
column = workSheet.Columns.Add();
column.Index = k + 1;
column.Width = 100;
list.Add(column);
}
if (excelExportProperties?.Footer != null)
{
int footerRows = excelExportProperties.Footer.FooterRows;
int num4 = footerRows - excelExportProperties.Footer.Rows.Count;
for (int l = 1; l <= footerRows; l++)
{
actualrCnt++;
row = workSheet.Rows.Add();
row.Index = actualrCnt;
int num5 = 0;
int num6 = ((num4 > 0) ? num4 : 0);
if (l > num6)
{
num5 = ((excelExportProperties.Footer.Rows[l - num6 - 1].Cells != null) ? excelExportProperties.Footer.Rows[l - num6 - 1].Cells.Count : 0);
}
if (num5 > 0)
{
for (int m = 0; m < num5; m++)
{
this.cell = row.Cells.Add();
this.cell.Index = m + 1;
SetRowCellValues(row.Cells[m], excelExportProperties.Footer.Rows[l - num6 - 1].Cells[m]);
}
}
}
}
if (maxLevel > 0)
{
list[0].Width = 100 + maxLevel * 20;
}
if (type.Equals("CSV", StringComparison.Ordinal))
{
isCsvExport = true;
fileName += ".csv";
}
else
{
fileName += ".xlsx";
}
using MemoryStream outputStream = new MemoryStream();
book.Save(outputStream, isCsvExport);
await Parent.InvokeMethod("sfBlazor.PivotView.exportDocument", Parent.dataId, fileName, Convert.ToBase64String(outputStream.ToArray()));
}
Hi Ali,
We would like to inform you that we used internal and protected functions for formatting and generating pivot values (cell collection) in order to frame cells in an Excel sheet during export. Implementing Excel exporting at the sample level because it involves adding the complete codebase to the sample. Furthermore, setting the virtualization property make it impossible to export all the pages of the pivot table data. Therefore, meeting your sample-level requirements within our pivot table is not achievable. However, we are currently looking into the possibility of implementing the request to save an excel document as a stream at the source level. We will update further details within two business days (May 8, 2024).
Please let us know if you have any concerns.
Regards,
Angelin Faith Sheeba.
Hi Ali,
Thanks for the patience. We have logged your requirement as a feature request “Support to save the excel document as memory stream” and this will be included in any one of our upcoming releases. You can track the status with the feedback link below.
https://www.syncfusion.com/feedback/56938/support-to-save-the-excel-document-as-memory-stream
We appreciate your patience until then.
Regards,
Angelin Faith Sheeba.
Hell Angelin,
Thanks for the update. I am looking forward to it. In the meantime, I found an alternative solution for it.
Thank You.
- 6 Replies
- 2 Participants
-
AL Ali
- May 2, 2024 05:27 AM UTC
- May 9, 2024 09:31 AM UTC
