How to Export the pdf file with fit all columns in single page

3 mins read

Description

This Knowledge Base explains how to export spreadsheet to Pdf file with fit all columns in the single page.

Solution

You can use the LayoutOptions property in ExcelToPdfConverter(MVC) and XlsIORendererSettings(Core) to set the layout option in 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:

spreadsheet