Modifying Microsoft Excel sheet using OpenXML in SharePoint 2010 webpart or Asp.net pages

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. :)

Merge two or More PDF into a single PDF using PDFSharp Library in SharePoint 2010

Problem:

Need  to merge two or more PDFs into a single PDF in SharePoint 2010


Solution:

I have searched around web for the solution and it seems many third Party dlls like itextsharp,selectPDF, PDFSharp etc are available to achieve this functionality. I go with PDFsharp free Licence.

Download the PDFsharp  dll

Below is the code :

arrFilesToZip - This is array list to store all the PDF file location which you want to merge.
CombinedFilePath - This  is the Merge PDF file full path URL


                PdfDocument outputDocument = new PdfDocument();

                // Iterate files
                foreach (object tmpfile in arrFilesToZip)
                {
                    string file = (string)tmpfile;
                    // Open the document to import pages from it.

                    PdfDocument inputDocument = PdfReader.Open(file, PdfDocumentOpenMode.Import);

                    // Iterate pages

                    int count = inputDocument.PageCount;
                    for (int idx = 0; idx < count; idx++)
                    {
                        // Get the page from the external document...
                        PdfPage page = inputDocument.Pages[idx];

                        // ...and add it to the output document.
                        outputDocument.AddPage(page);

                    }
                }

                // Save the document...

   
                string combinedFilesPdfName = "CombinedFilePath"+".pdf";
                //  if (File.Exists(combinedFilesPdfName))
                //      File.Delete(combinedFilesPdfName);



                outputDocument.Save(combinedFilesPdfName);

Please let me know if you face any issue in merge of the files using this code.

cheers.

Create a ZIP file using ICSharpCode.SharpZipLib in SharePoint 2010 webpart

Problem :    

We have to generate a zip file in SharePoint  webpart from a no of files present in a Physical path

Example : 

Files :   ABC.pdf, DEF.pdf, GHI.pdf

Zip file : xyz.zip 

Solution :

I have found the solution on web to use ICSharpCode.SharpZipLib(Free)  and convert the number of files into a single zip file.

Download  ICSharpCode.SharpZipLib(Free) .

Below is the code i have attached.

Parameter for below function:

arrFilesToZip - ArrayList with all the Full file Path name which you want to zip

FolderName - Full path of the folder where you want to save your zip file

sZipFileName- Zip file name

public string CreateZipfile(ArrayList arrFilesToZip, string folderName, string sZipFileName)

        {

            try

            {

                using (ZipOutputStream s = new ZipOutputStream(File.Create(folderName + "\\" + sZipFileName + ".zip")))

                {

                    s.UseZip64 = UseZip64.Off;

                    //s.IsStreamOwner = false;

                    ICSharpCode.SharpZipLib.Checksums.Crc32 crc = new ICSharpCode.SharpZipLib.Checksums.Crc32();

                    foreach (string pathname in arrFilesToZip)

                    {

                        byte[] buffer = File.ReadAllBytes(pathname);

                        ZipEntry entry = new ZipEntry(Path.GetFileName(pathname));

                        entry.DateTime = DateTime.Now;

                        entry.Size = buffer.Length;

                        entry.CompressionMethod = CompressionMethod.Stored;

                        crc.Reset();

                        crc.Update(buffer);

                        entry.Crc = crc.Value;

                        s.PutNextEntry(entry);

                        s.Write(buffer, 0, buffer.Length);

                    }

                    s.Finish();

                    s.Flush();

                    s.Close();

                }

                return folderName + "\\" + sZipFileName + ".zip";

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

Please let me know if you have any issue in understanding of code. 

Appreciate of any suggestion.:)