When converting an Excel file to PDF, the columns are displayed incorrectly.
We use the following code:
- Code: Select all
...
var workbook = FitWorkbook(content);
if (customFontFilePaths.Length > 0)
workbook.CustomFontFilePaths = customFontFilePaths.Split(",");
else
workbook.CustomFontFilePaths = new string[]
{
"/usr/share/fonts/truetype/msttcorefonts/times.ttf",
"/usr/share/fonts/truetype/msttcorefonts/Arial_Black.ttf",
"/usr/share/fonts/truetype/msttcorefonts/Times_New_Roman.ttf",
"/usr/share/fonts/truetype/msttcorefonts/georgiab.ttf"
};
using (var stream = new MemoryStream())
{
workbook.SaveToStream(stream, Spire.Xls.FileFormat.PDF);
return File(stream.ToArray(), "application/pdf", "convert.pdf");
}
...
private Workbook FitWorkbook(byte[] content)
{
Workbook workbook = new();
using (var stream = new MemoryStream(content))
{
workbook.LoadFromStream(stream, true);
// fitting line heights
FitMergedRows(workbook);
return workbook;
}
}
private void FitMergedRows(Workbook workbook)
{
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
var sheet = workbook.Worksheets[i];
var ranges = sheet.MergedCells;
if (sheet.AllocatedRange == null)
continue;
var startedColumnIndex = sheet.Columns.Length;
var fitColumnIndex = startedColumnIndex;
for (int j = 0; j < ranges.Count(); j++)
sheet[1, sheet.Columns.Length + 1].Value = j.ToString();
foreach (var range in ranges)
{
if (range.RowCount > 1 || range.DisplayedText.Length < 1)
continue;
fitColumnIndex++;
var columnWidth = 0.0;
foreach (var cell in range.CellList)
columnWidth += cell.ColumnWidth;
sheet.SetColumnWidth(fitColumnIndex, columnWidth);
sheet.Rows[range.Row - 1].CellList[fitColumnIndex - 1].Value = range.DisplayedText.Replace(System.Environment.NewLine, " ");
if (range.CellsCount > 0)
sheet.Rows[range.Row - 1].CellList[fitColumnIndex - 1].Style = range.Cells[0].Style;
sheet.Rows[range.Row - 1].CellList[fitColumnIndex - 1].Style.WrapText = true;
}
try
{
sheet.AllocatedRange.AutoFitRows();
}
catch (Exception e)
{
throw new Exception($"error AutoFitRows: {e.Message}", e);
}
sheet.DeleteColumn(startedColumnIndex, sheet.Columns.Count() - 1);
}
}
use Asp.net Core Web App Net 5.0,
OS: Windows 10 (local testing), docker linux
Spire Library: Spire.Office (6.7.0) & Spire.Xls (11.7.0)
In the attached archive sample files.
Thank you