Convert World / Excel To PDF

There are several Frameworks for this solution, the one I used the most was EEPLus a DLL and does not need to be installed), but there are many others like:

• CloseXml
• Open XML SDK ( see article )
• NPOI

As I said what I used the most and had a better experience was EEPLus .

See how easy it is to use it:

Create Tab

private static ExcelWorksheet CreateSheet(ExcelPackage p, string sheetName)
{
    p.Workbook.Worksheets.Add(sheetName);
    ExcelWorksheet ws = p.Workbook.Worksheets[1];
    ws.Name = sheetName; //Setting Sheet's name
    ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet
    ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet

    return ws;
}

Join columns

//Merging cells and create a center heading for out table
ws.Cells[1, 1].Value = "Sample DataTable Export"; // Heading Name
ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true; //Merge columns start and end range
ws.Cells[1, 1, 1, dt.Columns.Count].Style.Font.Bold = true; //Font should be bold
ws.Cells[1, 1, 1, dt.Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

Add stilo in the cell

//Setting the background color of header cells to Gray
var fill = cell.Style.Fill;
fill.PatternType = ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(Color.Gray);

Add border style

//Setting Top/left,right/bottom borders.
var border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;

Add formula

//Setting Sum Formula
cell.Formula = "Sum(" + ws.Cells[3, colIndex].Address + ":" + ws.Cells[rowIndex - 1, colIndex].Address + ")";

Add image

private static void AddImage(ExcelWorksheet ws, int columnIndex, int rowIndex, string filePath)
{
    //How to Add a Image using EP Plus
    Bitmap image = new Bitmap(filePath);
    ExcelPicture picture = null;
    if (image != null)
    {
        picture = ws.Drawings.AddPicture("pic" + rowIndex.ToString() + columnIndex.ToString(), image);
        picture.From.Column = columnIndex;
        picture.From.Row = rowIndex;
        picture.From.ColumnOff = Pixel2MTU(2); //Two pixel space for better alignment
        picture.From.RowOff = Pixel2MTU(2);//Two pixel space for better alignment
        picture.SetSize(100, 100);
    }
}

Convert to PDF

Workbook workbook = new Workbook();  
//Load excel file  
workbook.LoadFromFile(info.Name);  
//Save excel file to pdf file.  
workbook.SaveToFile("result.pdf", Spire.Xls.FileFormat.PDF); 

Tutorial links:

link

link

link

Try using EPPlus and Spire.Xls. I did not use it, but from what I researched it is not necessary to install Office on the server.

I use the EEPLus library. The EPPlus library can also be downloaded by NuGet .

Here is an example of a code that generates an Excel file from a DataTable :

Private Sub CreateAndFormatExcel(ByVal dt As Data.DataTable)
    Dim fileName As String = "exemplo.xlsx"

    Using pck As New OfficeOpenXml.ExcelPackage()
        'Excel Properties
        pck.Workbook.Properties.Author = "Nome do Autor"
        pck.Workbook.Properties.Title = "Titulo do Excel"
        pck.Workbook.Properties.Company = "Nome da Empresa"
        pck.Workbook.Properties.Subject = "Subject"
        pck.Workbook.Properties.Comments = "Comentarios"
        pck.Workbook.Properties.Manager = "Manager"
        pck.Workbook.Properties.HyperlinkBase = New Uri("http://google.com")

        'Create the worksheet
        Dim ws As OfficeOpenXml.ExcelWorksheet = pck.Workbook.Worksheets.Add(Resources.Standard.ListaFaltas)

        'Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
        ws.Cells("A1").LoadFromDataTable(dt, True)

        Dim rangeCell As String = "A1:F1"

        Using rng As OfficeOpenXml.ExcelRange = ws.Cells(rangeCell)
            rng.Style.Font.Bold = True
            rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid
            'Set Pattern for the background to Solid
            rng.Style.Fill.BackgroundColor.SetColor(Drawing.Color.Red)
            'Set color to dark blue
            rng.Style.Font.Color.SetColor(Drawing.Color.White)
        End Using

        ws.Cells(rangeCell).AutoFilter = True

        'Congela painel
        ws.View.FreezePanes(2, 2)

        'Write it back to the client
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;  filename=" & fileName & "")
        Response.BinaryWrite(pck.GetAsByteArray())
        Response.End()
    End Using

End Sub