I have a excelfile containing a chart.
excel_file.png (172.0 KB)
My c# code for creating a PDF from the excelfile looks like:
public void CreatePDF(Worksheet worksheet)
{
worksheet.PageSetup.BottomMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.LeftMargin = 0;
var pdfSaveOptions = new PdfSaveOptions();
//Only export print area
pdfSaveOptions.OnePagePerSheet = true;
worksheet.Workbook.Save(_pdfTargetPath, pdfSaveOptions);
}
And the output result looks like this:
pdf_file.png (14.1 KB)
@Developer0815
Would you like to provide your sample file? We will check it soon.
@Developer0815
Thank you for your feedback. Unfortunately, there is no sample file in the compressed file. Please upload the sample file again.
You can use this void to reproduce the steps that my code does:
public void CreatePdf()
{
var pathToExcelFile = @"C:\Users\MyUser\Downloads\146.xlsx";
var outputPath = @"C:\Users\MyUser\Downloads\146.pdf";
using var fileStream = new FileStream(pathToExcelFile, FileMode.Open, FileAccess.Read);
var workbook = new Workbook(fileStream, new LoadOptions(LoadFormat.Xlsx));
var layoutSheet = workbook.Worksheets.Single(s => s.Name == "Layout");
//Hide all other worksheets so that only the given worksheet is exported to PDF
foreach (var worksheet in workbook.Worksheets)
{
if (worksheet != layoutSheet)
worksheet.IsVisible = false;
}
layoutSheet.PageSetup.BottomMargin = 0;
layoutSheet.PageSetup.RightMargin = 0;
layoutSheet.PageSetup.TopMargin = 0;
layoutSheet.PageSetup.LeftMargin = 0;
var pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.OnePagePerSheet = true;
workbook.Save(outputPath, pdfSaveOptions);
}
@Developer0815
PdfSaveOptions.OnePagePerSheet is a property of placing a worksheet onto a single page. If you only want to export the print area, you can set the Worksheet.PageSetup.PrintArea. Please refer to the following example code. After setting print area, you will find that the top interval of the first page is 0. Please refer to the attachment. out_net.pdf (84.8 KB)
Workbook wb = new Workbook(filePath + "146.xlsx");
Worksheet worksheet = wb.Worksheets[0];
worksheet.PageSetup.BottomMargin = 0;
worksheet.PageSetup.RightMargin = 0;
worksheet.PageSetup.TopMargin = 0;
worksheet.PageSetup.LeftMargin = 0;
//Only export print area
worksheet.PageSetup.PrintArea = "A3:G16";
Console.WriteLine(worksheet.PageSetup.PrintArea);
var pdfSaveOptions = new PdfSaveOptions();
pdfSaveOptions.OnePagePerSheet = true;
worksheet.Workbook.Save(filePath + "out_net.pdf", pdfSaveOptions);
Ok that removes the top whitespace but the leading whitespace is still there…
And in my code I cant rely on that the chart always has the same size and starts at “A3”…
Isnt there a dynamic solution?
I found a way to do it like so:
var chart = layoutSheet.Charts[0];
chart.ToPdf(outputPath);
But sometimes my Excel file does not contain charts but normal tables. Or sometimes even cells are filled and then there is a chart underneath. These cells would then be lost in this way…
@Developer0815
Due to the image being suspended on a cell, the position of the upper left corner may not necessarily coincide with the upper left corner of the cell, resulting in a blank space. If it is a regular table, the data in the cells is within the range of the cells and there will be no large amount of blank space.
Aspose.Cells follow Excel standards, and the output control of the printing area is the ultimate solution. I don’t think there will be any more subtle range output control.
Ok i did a implementation that should get the work done… maybe you can check for errors?
var firstCell = layoutSheet.Cells.FirstCell;
var lastCell = layoutSheet.Cells.LastCell ?? firstCell;
var chart = layoutSheet.Charts.SingleOrDefault();
// if there are manually typed cells
if (firstCell != null)
{
var firstCellName = firstCell.Name;
var lastCellName = lastCell.Name;
// if there is a chart and filled cells calculate the top left and the bottom right print area
if (chart != null)
{
// get top left row and column index of the chart
var chartUpperLeftRow = chart.ChartObject.UpperLeftRow;
var chartUpperLeftColumn = chart.ChartObject.UpperLeftColumn;
// get bottom right row and column index of the chart
var chartLowerRightRow = chart.ChartObject.LowerRightRow;
var chartLowerRightColumn = chart.ChartObject.LowerRightColumn;
// get the row and column index of the firstCell
CellsHelper.CellNameToIndex(firstCell.Name, out var cellsUpperLeftRow, out var cellsUpperLeftColumn);
// get the row and column index of the lastCell
CellsHelper.CellNameToIndex(lastCell.Name, out var cellsLowerRightRow, out var cellsLowerRightColumn);
// get the lower row index from the chart or the cell
var minUpperLeftRow = Math.Min(chartUpperLeftRow, cellsUpperLeftRow);
// get the lower column index from the chart or the cell
var minUpperLeftColumn = Math.Min(chartUpperLeftColumn, cellsUpperLeftColumn);
// get the higher row index from the chart or the cell
var maxLowerRightRow = Math.Max(chartLowerRightRow, cellsLowerRightRow);
// get the higher column index from the chart or the cell
var maxLowerRightColumn = Math.Max(chartLowerRightColumn, cellsLowerRightColumn);
// convert the indexes back to names
firstCellName = CellsHelper.CellIndexToName(minUpperLeftRow, minUpperLeftColumn);
lastCellName = CellsHelper.CellIndexToName(maxLowerRightRow, maxLowerRightColumn);
}
layoutSheet.PageSetup.PrintArea = $"{firstCellName}:{lastCellName}";
layoutSheet.PageSetup.BottomMargin = 0;
layoutSheet.PageSetup.RightMargin = 0;
layoutSheet.PageSetup.TopMargin = 0;
layoutSheet.PageSetup.LeftMargin = 0;
var pdfSaveOptions = new PdfSaveOptions();
//Only export print area
pdfSaveOptions.OnePagePerSheet = true;
workbook.Save(outputPath, pdfSaveOptions);
}
@Developer0815,
It seems that you have implemented your requirements using your code. Apparently the code segment looks OK. Please feel free to contact us at any time if you have any further questions or comments.
@Developer0815
Maybe you can try MaxDisplayRange instead of checking cells and other objects by yourself:
...
layoutSheet.PageSetup.PrintArea = layoutSheet.Cells.MaxDisplayRange.Address;
...
The DiplayRange is just a mess unfortunately… for my exelfile it shows A1:S16
Thats just not right…
I wrote some final code now that does the job (the previous one had some errors i found on testing)
public void CreatePdf()
{
var pathToExcelFile = @"C:\Users\MyUser\Downloads\146.xlsx";
var outputPath = @"C:\Users\MyUser\Downloads\146.pdf";
using var fileStream = new FileStream(pathToExcelFile, FileMode.Open, FileAccess.Read);
var workbook = new Workbook(fileStream, new LoadOptions(LoadFormat.Xlsx));
var layoutSheet = workbook.Worksheets.Single(s => s.Name == "Layout");
//Hide all other worksheets so that only the given worksheet is exported to PDF
foreach (var worksheet in workbook.Worksheets)
{
if (worksheet != layoutSheet)
worksheet.IsVisible = false;
}
// get the charts
var charts = layoutSheet.Charts.ToList();
// collect all cells that have a value
var filledCells = layoutSheet.Cells.OfType<Cell>().Where(c => c.Value != null).ToList();
// if there are cells with a value
if (!filledCells.IsNullOrEmpty())
{
// get min/max row/cell indexes
var minUpperLeftRow = filledCells.Min(c => c.Row);
var minUpperLeftColumn = filledCells.Min(c => c.Column);
var maxLowerRightRow = filledCells.Max(c => c.Row);
var maxLowerRightColumn = filledCells.Max(c => c.Column);
// if there are charts and filled cells calculate the top left and the bottom right print area
if (!charts.IsNullOrEmpty())
{
// get top left row and column index of the chart
var chartUpperLeftRow = charts.Min(c => c.ChartObject.UpperLeftRow);
var chartUpperLeftColumn = charts.Min(c => c.ChartObject.UpperLeftColumn);
// get bottom right row and column index of the chart
var chartLowerRightRow = charts.Max(c => c.ChartObject.LowerRightRow);
var chartLowerRightColumn = charts.Max(c => c.ChartObject.LowerRightColumn);
// get the lower row index from the chart or the cell
minUpperLeftRow = Math.Min(chartUpperLeftRow, minUpperLeftRow);
// get the lower column index from the chart or the cell
minUpperLeftColumn = Math.Min(chartUpperLeftColumn, minUpperLeftColumn);
// get the higher row index from the chart or the cell
maxLowerRightRow = Math.Max(chartLowerRightRow, maxLowerRightRow);
// get the higher column index from the chart or the cell
maxLowerRightColumn = Math.Max(chartLowerRightColumn, maxLowerRightColumn);
}
// convert the indexes to names
var firstCellName = CellsHelper.CellIndexToName(minUpperLeftRow, minUpperLeftColumn);
var lastCellName = CellsHelper.CellIndexToName(maxLowerRightRow, maxLowerRightColumn);
layoutSheet.PageSetup.PrintArea = $"{firstCellName}:{lastCellName}";
}
// only a single chart - no cells filled
else if (charts.Count == 1)
{
charts.Single().ToPdf(outputPath);
return;
}
// default code - will be executed no matter if there are filled cells or tables
layoutSheet.PageSetup.BottomMargin = 0;
layoutSheet.PageSetup.RightMargin = 0;
layoutSheet.PageSetup.TopMargin = 0;
layoutSheet.PageSetup.LeftMargin = 0;
var pdfSaveOptions = new PdfSaveOptions
{
//Only export print area
OnePagePerSheet = true
};
workbook.Save(outputPath, pdfSaveOptions);
}
@Developer0815
It’s great to see that you’ve completed the required functionality. If you have any other questions, feel free to ask.