How to hide the summary rows and columns using XlsIO?
24 Jan 20235 minutes to read
You can hide the summary rows and columns by using the IsSummaryRowBelow and IsSummaryColumnRight properties. The following code snippet illustrates this.
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Hide the summary rows at the bottom.
worksheet.PageSetup.IsSummaryRowBelow = false;
//Hide the summary columns to the right.
worksheet.PageSetup.IsSummaryColumnRight = false;
workbook.SaveAs("SuppressRowsColumns.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
application.DefaultVersion = ExcelVersion.Excel2013
Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic)
Dim worksheet As IWorksheet = workbook.Worksheets(0)
'Hide the summary rows at the bottom.
worksheet.PageSetup.IsSummaryRowBelow = False
'Suppress the summary columns to the right.
worksheet.PageSetup.IsSummaryColumnRight = False
workbook.SaveAs("SuppressRowsColumns.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
Stream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = await application.Workbooks.OpenAsync(inputStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Hide the summary rows at the bottom
worksheet.PageSetup.IsSummaryRowBelow = false;
//Hide the summary columns to the right
worksheet.PageSetup.IsSummaryColumnRight = false;
//Initializes FileSavePicker
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "SuppressRowsColumns";
savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });
//Creates a storage file from FileSavePicker
StorageFile storageFile = await savePicker.PickSaveFileAsync();
//Saves changes to the specified storage file
await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
IWorksheet worksheet = workbook.Worksheets[0];
//Hide the summary rows at the bottom
worksheet.PageSetup.IsSummaryRowBelow = false;
//Hide the summary columns to the right
worksheet.PageSetup.IsSummaryColumnRight = false;
FileStream stream = new FileStream("SuppressRowsColumns.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
IApplication application = excelEngine.Excel;
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = assembly.GetManifestResourceStream("App.Sample.xlsx");
IWorkbook workbook = application.Workbooks.Open(fileStream);
IWorksheet worksheet = workbook.Worksheets[0];
//Hide the summary rows at the bottom
worksheet.PageSetup.IsSummaryRowBelow = false;
//Hide the summary columns to the right
worksheet.PageSetup.IsSummaryColumnRight = false;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
stream.Position = 0;
//Save the stream as a file in the device and invoke it for viewing
Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("SuppressRowsColumns.xlsx", "application/msexcel", stream);
}