How to Export the pdf file with fit all columns in single page
Description
This article explains how to export a spreadsheet to a PDF file with all columns fitting on a single page.
Solution
You can use the LayoutOptions property in ExcelToPdfConverter(MVC) and XlsIORendererSettings(Core) to set the layout option in the PDF file. This property helps to select the layout option for the Excel document in Excel-to-PDF conversion. You can use FitAllColumnsOnOnePage in LayoutOptions to achieve this.
MVC Solution
[CSHTML]
@Html.EJS().Spreadsheet("spreadsheet").SaveUrl("api/Spreadsheet/Save").Created("createdHandler").Sheets(sheet =>
{
sheet.Name("Car Sales Report").Ranges(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
}).Rows(row =>
{
row.Cells(cell =>
{
cell.Value("Customer Name").Add();
cell.Index(3).Value("Payment Mode").Add();
cell.Value("Delivery Date").Add();
}).Add();
row.Index(30).Cells(cell =>
{
cell.Index(4).Value("Total Amount:").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Right }).Add();
cell.Formula("=SUM(F2:F30)").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold }).Add();
}).Add();
}).Columns(column =>
{
column.Width(180).Add();
column.Width(130).Add();
column.Width(130).Add();
column.Width(180).Add();
column.Width(130).Add();
column.Width(120).Add();
}).Add();
}).Render()
<script>
// Triggers once the control is loaded
function createdHandler() {
// Applies cell and number formatting to specified range of the active sheet
this.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
this.numberFormat('$#,##0.00', 'F2:F31');
}
</script>
[Controller]
[System.Web.Http.Route("api/Spreadsheet/Save")]
[System.Web.Http.HttpPost, ValidateInput(false)]
public void Save(SaveSettings saveSettings)
{
if (saveSettings.SaveType.ToString() == "Pdf")
{
saveSettings.SaveType = SaveType.Xlsx;
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
Stream fileStream = Workbook.Save<Stream>(saveSettings);
IWorkbook workbook = application.Workbooks.Open(fileStream);
saveSettings.SaveType = SaveType.Pdf;
//Convert the workbook
ExcelToPdfConverter converter = new ExcelToPdfConverter(workbook);
//Initializes the Excel To PDF converter setting class
ExcelToPdfConverterSettings converterSettings = new ExcelToPdfConverterSettings();
converterSettings.DisplayGridLines = GridLinesDisplayStyle.Visible;
//Layout the page using FitAllColumnsOnOnePage options
converterSettings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
//Initialize PDF document
PdfDocument pdfDocument = new PdfDocument();
// Convert Excel document into PDF document
pdfDocument = converter.Convert(converterSettings);
saveSettings.SaveType = SaveType.Pdf;
//Save the PDF file
pdfDocument.Save(saveSettings.FileName + "." + saveSettings.SaveType.ToString().ToLower(), HttpContext.Current.Response, HttpReadType.Save);
}
}
else Workbook.Save(saveSettings);
}
Core Solution
[CSHTML]
<ejs-spreadsheet id="spreadsheet" saveUrl="Home/Save" created="createdHandler">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Car Sales Report">
<e-spreadsheet-ranges>
<e-spreadsheet-range dataSource="ViewBag.DefaultData"></e-spreadsheet-range>
</e-spreadsheet-ranges>
<e-spreadsheet-rows>
<e-spreadsheet-row>
<e-spreadsheet-cells>
<e-spreadsheet-cell value="Customer Name"></e-spreadsheet-cell>
<e-spreadsheet-cell index=3 value="Payment Mode"></e-spreadsheet-cell>
<e-spreadsheet-cell value="Delivery Date"></e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
<e-spreadsheet-row index=30>
<e-spreadsheet-cells>
<e-spreadsheet-cell index=4 value="Total Amount:">
<e-spreadsheet-cellstyle fontWeight="Bold" textAlign="Right"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
<e-spreadsheet-cell formula="=SUM(F2:F30)">
<e-spreadsheet-cellstyle fontWeight="Bold"></e-spreadsheet-cellstyle>
</e-spreadsheet-cell>
</e-spreadsheet-cells>
</e-spreadsheet-row>
</e-spreadsheet-rows>
<e-spreadsheet-columns>
<e-spreadsheet-column width="180"></e-spreadsheet-column>
<e-spreadsheet-column width="130"></e-spreadsheet-column>
<e-spreadsheet-column width="130"></e-spreadsheet-column>
<e-spreadsheet-column width="180"></e-spreadsheet-column>
<e-spreadsheet-column width="130"></e-spreadsheet-column>
<e-spreadsheet-column width="120"></e-spreadsheet-column>
</e-spreadsheet-columns>
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
function createdHandler() {
//Applied style and number formatting to range of the active sheet
this.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
this.numberFormat('$#,##0.00', 'F2:F31');
}
</script>
[Controller]
public IActionResult Save(SaveSettings saveSettings)
{
if (saveSettings.SaveType.ToString() == "Pdf")
{
saveSettings.SaveType = SaveType.Xlsx;
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
Stream fileStream = Workbook.Save<Stream>(saveSettings);
IWorkbook workbook = application.Workbooks.Open(fileStream);
saveSettings.SaveType = SaveType.Pdf;
//Initialize XlsIORendererSettings
XlsIORendererSettings settings = new XlsIORendererSettings();
//Set layout option as FitAllColumnsOnOnePage
settings.LayoutOptions = LayoutOptions.FitAllColumnsOnOnePage;
//Initialize XlsIORenderer
XlsIORenderer renderer = new XlsIORenderer();
int i, sheetCnt = workbook.Worksheets.Count;
for (i = 0; i < sheetCnt; i++)
{
workbook.Worksheets[i].PageSetup.PrintGridlines = true;
settings.DisplayGridLines = GridLinesDisplayStyle.Auto;
}
//Convert the Excel document to PDF with renderer settings
PdfDocument document = renderer.ConvertToPDF(workbook, settings);
//Saving the Excel to the MemoryStream
MemoryStream stream = new MemoryStream();
document.Save(stream);
//Set the position as '0'
stream.Position = 0;
//Download the PDF file in the browser
FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/pdf");
fileStreamResult.FileDownloadName = "Output.pdf";
return fileStreamResult;
}
else
{
return Workbook.Save(saveSettings);
}
}
Screenshot:

Also, please refer to the UG Documentation link below for more details:
https://help.syncfusion.com/document-processing/excel/spreadsheet/javascript-es6/open-save
https://help.syncfusion.com/document-processing/excel/spreadsheet/javascript-es6/open-save