How to Add Images to Excel Files using NPOI
In our last post, we saw the usage of NPOI to work with Excel and perform basic read and Write operations on Excel files.
Today in this article, we shall try to insert the image into an Excel file at a particular location using the NPOI library.
I shall be using the same logic which we learned in our previous article.
Today in this article, we will cover below aspects,
Please refer to the article below article on Read/Write Excel files using NPOI
Kindly add the below logic to your writing method,
Adding Image(JPEG, PNG) in the Excel File
I already have a sample jpeg file – Read-write-excel-npoi.jpg which we shall be trying to insert at Index (5,5) i.e @ row 5 and column 5
At row 5 and column 5, the above image will be inserted programmatically.
byte[] data = File.ReadAllBytes("Read-write-excel-npoi.jpg");
int pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
ICreationHelper helper = workbook.GetCreationHelper();
IDrawing drawing = excelSheet.CreateDrawingPatriarch();
IClientAnchor anchor = helper.CreateClientAnchor();
anchor.Col1 = 5;
anchor.Row1 = 5;
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize();
Write EXCEL with Image
Here below is a sample POC complete Code as below,
static void WriteExcel()
{
List<UserDetails> persons = new List<UserDetails>()
{
new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country="USA"},
new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
};
// Lets converts our object data to Datatable for a simplified logic.
// Datatable is most easy way to deal with complex datatypes for easy reading and formatting.
DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons), (typeof(DataTable)));
var memoryStream = new MemoryStream();
using (var fs = new FileStream("Result.xlsx", FileMode.OpenOrCreate, FileAccess.Write))
{
IWorkbook workbook = new XSSFWorkbook();
ISheet excelSheet = workbook.CreateSheet("TestSheet1");
List<String> columns = new List<string>();
IRow row = excelSheet.CreateRow(0);
int columnIndex = 0;
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
columnIndex++;
}
int rowIndex = 1;
foreach (DataRow dsrow in table.Rows)
{
row = excelSheet.CreateRow(rowIndex);
int cellIndex = 0;
foreach (String col in columns)
{
row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
cellIndex++;
}
rowIndex++;
}
byte[] data = File.ReadAllBytes("Read-write-excel-npoi.jpg");
int pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
ICreationHelper helper = workbook.GetCreationHelper();
IDrawing drawing = excelSheet.CreateDrawingPatriarch();
IClientAnchor anchor = helper.CreateClientAnchor();
anchor.Col1 = 5;
anchor.Row1 = 5;
IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
picture.Resize();
workbook.Write(fs);
}
}
I have kept the image file in the same project directory so that it can be used by Excel API and loaded in the proper location in Excel.
Finally Image will be entered at the required location successfully,
Do you have any comments or ideas or any better suggestions to share?
Please sound off your comments below.
Happy Coding !!
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.
How do you prevent the image from Stretching
How do you prevent the image from Stretching?
TKS a lot !