Issue Description
Need to update the Microsoft Excel sheet by adding the color to cell, updating cell value and Replacing the images in SharePoint web partusing DocumentFormat.OpenXml.dll.
Solution
I had to achieved the above requirement using OpenXML ( 3.5 Net Version and above) only since no other third part client has agreed for, so i started searching to web and found some bits and pieces result from MSDN and Stackoverflow.com. but here i collate all the Information with the
sample code so you can use in your project if you have similar kind of requirement.
Click on the
link to download the code.
Some explanation to understand the code
- Sample code Folder name is GeneratePDF.zipa - dont go with the name i was using this project initially for GeneratePDF and later used for this Excel update requirement.
- Rename the GeneratePDF.zipa to .zip file so you can extract the file and get the working solution with you.
- In my case, i have an excel template is ready and stored in a SharePoint Document Library.
- Next step i read this Excel template and get the document to Memory stream.
- Load the memory stream to the Spreadsheet object of OpenXMLLibrary
-I have created some methods to work for you easily so you no need to understand all the codes
Methods to Replace the Image in Excel using OpenXML
///
/// Replaced the Image with specific Image from File Location
///
/// <param name="document" - Spreadsheet Document></param>
/// <param name="wbPart" Workbook part></param>
/// <param name="replacement" Replacement Image Path></param>
/// <param name="row" which row you want to replace the image></param>
/// <param name="col" which Column you want to replace the Image ></param>
private static void ReplaceImageFromSpecificLocation(SpreadsheetDocument document, WorkbookPart wbPart, string replacement, string row, string col)
{
var workSheet = wbPart.WorksheetParts.FirstOrDefault();
TwoCellAnchor cellHoldingPicture = workSheet.DrawingsPart.WorksheetDrawing.OfType<TwoCellAnchor>()
.Where(c => c.FromMarker.RowId.Text == row && c.FromMarker.ColumnId.Text == col).FirstOrDefault();
OneCellAnchor cellHoldingPicture1 = workSheet.DrawingsPart.WorksheetDrawing.OfType<OneCellAnchor>()
.Where(c => c.FromMarker.RowId.Text == row && c.FromMarker.ColumnId.Text == col).FirstOrDefault();
string rIdofPicture = string.Empty;
if (cellHoldingPicture != null)
{
var picture = cellHoldingPicture.OfType<DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture>().FirstOrDefault();
string PictureName = picture.NonVisualPictureProperties.LocalName;
rIdofPicture = picture.BlipFill.Blip.Embed;
}
if (cellHoldingPicture1 != null)
{
var picture = cellHoldingPicture1.OfType<DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture>().FirstOrDefault();
rIdofPicture = picture.BlipFill.Blip.Embed;
}
ImagePart imageInThisCell = (ImagePart)workSheet.DrawingsPart.GetPartById(rIdofPicture);
foreach (var part in document.WorkbookPart.GetPartsOfType<WorksheetPart>())
{
// get drawingpart of worksheetpart
var drawingPart = part.DrawingsPart;
if (drawingPart != null)
{
// load drawing part to XmlDocument for processing
XmlDocument doc = new XmlDocument();
doc.Load(drawingPart.GetStream());
drawingPart.DeletePart(imageInThisCell);
// add the ImagePart
ImagePart newPart = drawingPart.AddImagePart(ImagePartType.Jpeg, rIdofPicture);
// replace image
using (FileStream stream = new FileStream(replacement, FileMode.Open))
{
// feed data to the newPart
newPart.FeedData(stream);
}
doc.Save(drawingPart.GetStream(FileMode.Create));
}
}
}
--
Method to Update the Cell Value
/// <param name="spreadSheet" - Spreadsheet Object></param>
/// <param name="text" The Text to be replaced></param>
/// <param name="rowIndex" Row Index where the value need to repalced></param>
/// <param name="columnName" ColumnName where the value need to be replaced></param>
public static void UpdateCell(SpreadsheetDocument spreadSheet, string text, uint rowIndex, string columnName)
{
WorksheetPart worksheetPart =
GetWorksheetPartByName(spreadSheet, "Sample PDF Design");// Sample PDF Design is your sheet Name
// InsertImage(worksheetPart, 1, 1, 3, 3, new FileStream(ImageFile, FileMode.Open));
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet,
columnName, rowIndex);
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
cell.CellValue = new CellValue(text);
worksheetPart.Worksheet.Save();
}
}
Method to Update the the Cell Value with Cell Color
public static void UpdateCellWithBackColor(SpreadsheetDocument spreadSheet, System.Drawing.Color Backcolor, uint rowIndex, string columnName)
{
WorksheetPart worksheetPart =
GetWorksheetPartByName(spreadSheet, "Sample PDF Design"); // Sample PDF Design is your sheet Name
// InsertImage(worksheetPart, 1, 1, 3, 3, new FileStream(ImageFile, FileMode.Open));
if (worksheetPart != null)
{
Cell cell = GetCell(worksheetPart.Worksheet,
columnName, rowIndex);
Stylesheet styleSheet = spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
// cell.CellValue = new CellValue(text);
UInt32Value headerFillIndex =
createFill(
styleSheet,
Backcolor);
//cell.DataType =
// new EnumValue<CellValues>(CellValues.Number);
UInt32Value StyleIndex =
createCellFormat(
styleSheet,
null,
headerFillIndex,
null);
cell.StyleIndex = StyleIndex;// GetStyleIndex(spreadSheet.WorkbookPart, cell);
// Save the worksheet.
worksheetPart.Worksheet.Save();
}
}
There are some supporting method might be used in above method, so please Download whole code and find the appropriate method. After changing the Excel sheet again i am saving to a Temp folder in Documents Library of SharePoint.
Let me know if you face any issue with above solution. i will try to help you.
Thanks cheers. :)
