Simple Library to Create Excel Worksheets and Convert to PDF in C# And ASP.Net

Introduction

After I knew NPOI from friends the last time, I also tried to find some other free open-source libraries to operate Excel worksheets in C#. During the summer holidays, I spent some time to get experience with these libraries a bit. Generally speaking, the functions are very well for operating Excel files in C# and to support operation of multi-workbook and support most of the Microsoft Excel elements, such as create Excel worksheets with dropdown list, formula, hyperlinks, Image and others. This article I will post a project used NPOI and Spire.XLS DLLs. With these DLLs, you can quickly write and manipulate Excel files and there is no need to install Microsoft Office on either of the development nor target systems, but it still needs .NET Framework 2.0 or above.

This project is for .NET framework 4.0.

Application Overview

First, I use NPOI to create an empty Excel worksheet and then insert an image and some hyperlinks into the sheet, then save the Excel file. Secondly, I use Spire.XLS to convert the Excel into PDF file.

Step 1: Use NPOI to create an Excel worksheet with image and hyperlinks. Here are the codes. NPOI (https://npoi.codeplex.com/) is an open source project that can help you read/write xls files, documents and PPT files. You can get the samples easily once you download the examples.

HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//create a new instance of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
hssfworkbook.DocumentSummaryInformation = dsi;
// create a new instance of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "Excel Example";
hssfworkbook.SummaryInformation = si;
//here, we must insert at least one sheet to the workbook. Otherwise, Excel will say 'data lost in file'
ISheet sheet1 = hssfworkbook.CreateSheet("sheet1");
 ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
 ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;
HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(600, 255, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 0;

//load an picture and insert it to the Excel sheet
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage("image.jpg", hssfworkbook));

//insert a hyperlink
ICellStyle hlink_style = hssfworkbook.CreateCellStyle();
IFont hlink_font = hssfworkbook.CreateFont();
hlink_font.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
hlink_font.Color = HSSFColor.Blue.Index;
hlink_style.SetFont(hlink_font);
ICell cell;
//URL
cell = sheet1.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = ("http://google.com/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet1.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new HSSFHyperlink(HyperlinkType.File);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet1.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new HSSFHyperlink(HyperlinkType.Email);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//Write the stream data of workbook to the root directory
string filename = "test.xls";
FileStream file = new FileStream(filename, FileMode.Create);
hssfworkbook.Write(file);
file.Close();

Screenshot of the created Excel file:



Step 2: Since I need to convert an Excel file to PDF and NPOI only supports saving Excel as HTML, I searched and finally found the free Spire.XLS (https://freenetexcel.codeplex.com/). Besides converting an Excel file into PDF, it also supports converting an Excel worksheet to an Image, XML, CSV, TEXT and others. It is easily integrated with other third-party components. It is easy and only three lines of codes are needed.

Workbook workbook = new Workbook();
workbook.LoadFromFile(filename);
workbook.SaveToFile("result.pdf",FileFormat.PDF);

It says Spire.XLS supports many elements in an Excel file that can be converted to PDF successfully, such as chart, shape, smart art and and so on. Check the effected Screenshot of the resulting PDF file.



Summary

NPOI is open-source to easily read and write Excel 97-0, but it does not support Excel 2007, Excel 2010 and Excel 2013. Free Spire.XLS supports these versions of Excel files, but it is limited to 5 sheets per workbook and 150 rows per sheet. For personal use, both of them are enough. If this article helps, you can share it with your friends.