MS OpenXML SDK 2.0在Excel中指定位置填入数据和插入图片

简介

这是公司另外一个项目组遇到的技术问题。即如何用OpenXML在Excel指定位置填入数据和插入图片。笔者知道此事之后，帮他们想了一下办法。

用户的需求是这样的：有一个Excel的文档模版，里面已经有了一个表格框架。需要在某些单元格填入数字或者文字。并在某些单元格插入图片。并且不破坏原有的表格内容。这些功能必须在asp.net应用程序中实现，并且不用Office interop的组件。他们之前已经用了Office interop对象来操作Excel，这样就要求服务器上安装Office，因为之前的方法占用内存大，不稳定，所以用户希望他们另外想办法。用户和他们都知道OpenXML可以读写Excel文件，而且很轻量的，占用资源小，但是他们不知道如何用OpenXML完成这个任务，卡壳好多天了。

需求的理解可以见上图，我们需要做的是：

1. 在黄色背景的单元格填入数据

2. 在指定位置插入图片(如上图中的折线图)

上图左图是未填入数据之前的样子。上图右图是完成后的最终结果图。

实现

他们在网路上找了一些参考资料和参考代码，但是其中的对象模型比较复杂，一时半会没有弄明白。笔者聆听他们的需求后，再看了他们的已有代码。调试了一阵，发现这些参考代码对实现他们的需求没有什么用。需要针对他们的需求来改代码才行。

此工程必须引用C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll，C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\WindowsBase.dll

以下是核心代码：

?

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using System.Drawing;

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml.Drawing.Spreadsheet;

using System.Text.RegularExpressions;

using System.Data;

namespace ExcelWriter

{

    public class OpenXMLExcel : IDisposable

    {

        #region Constrution and dispose

        

        SpreadsheetDocument spreadSheet;

        public WorksheetPart CurrentWorksheetPart { get; set; }

        SharedStringTablePart shareStringPart;

        public OpenXMLExcel(Stream stream, bool bCreate)

        {

            if (bCreate == true)

            {

                spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);

                WorkbookPart workbookPart = spreadSheet.AddWorkbookPart();

                workbookPart.Workbook = new Workbook();

 

                WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);

 

                if (spreadSheet.WorkbookPart.GetPartsOfType<sharedstringtablepart>().Count() > 0)

                {

                    shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<sharedstringtablepart>().First();

                }

                else

                {

                    shareStringPart = spreadSheet.WorkbookPart.AddNewPart<sharedstringtablepart>();

                }

 

                shareStringPart.SharedStringTable = new SharedStringTable();

                shareStringPart.SharedStringTable.Count = 1;

                shareStringPart.SharedStringTable.UniqueCount = 1;

 

                CurrentWorksheetPart = worksheetPart;

            }

            else

            {

                spreadSheet = SpreadsheetDocument.Open(stream, true);

                shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<sharedstringtablepart>().First();

                CurrentWorksheetPart = spreadSheet.WorkbookPart.WorksheetParts.First();

            }

        }

        public void Dispose()

        {

            spreadSheet.Close();

            spreadSheet.Dispose();

        }

         

        #endregion

        #region Public interface

        public void WriteData(int x, int y, string strContent)

        {

            DataTable dt = new DataTable();

            dt.Columns.Add("Column1");

            dt.Rows.Add(strContent);

            WriteDataIntoWorkSheet(x, y, dt);

        }

 

        public void WriteDataIntoWorkSheet(int startx, int starty, DataTable dt)

        {

            //if (startx < 1)

            //    startx = 1;

            //if (starty < 1)

            //    starty = 1;

 

            WorksheetPart worksheetPart = CurrentWorksheetPart;

            //starty -= 1;

            int j = 0;

            foreach (DataRow dr in dt.Rows)

            {

                j++;

                for (int i = 0; i < dt.Columns.Count; i++)

                {

                    string name = GetColumnName(i + starty);

                    string text = Convert.IsDBNull(dr[i]) ? "" : dr[i].ToString();

                    int index = InsertSharedStringItem(text, shareStringPart);

                    Cell cell = InsertCellInWorksheet(name, Convert.ToUInt32(j + startx), worksheetPart);

 

                    cell.CellValue = new CellValue(index.ToString());

                    cell.DataType = new EnumValue<cellvalues>(CellValues.SharedString);

                    worksheetPart.Worksheet.Save();

                }

            }

        }

        public void WriteDataIntoWorkSheet(int startx, int starty, string[][] data)

        {

            WorksheetPart worksheetPart = CurrentWorksheetPart;

            starty -= 1;

            int i = 0;

            foreach (string[] row in data)

            {

                int j = 0;

                foreach (string text in row)

                {

                    string name = GetColumnName(j + starty);

                    int index = InsertSharedStringItem(text, shareStringPart);

                    Cell cell = InsertCellInWorksheet(name, Convert.ToUInt32(i + startx), worksheetPart);

 

                    cell.CellValue = new CellValue(index.ToString());

                    cell.DataType = new EnumValue<cellvalues>(CellValues.SharedString);

                    worksheetPart.Worksheet.Save();

                    j++;

                }

                i++;

            }

        }

        public void AddNewWorksheet()

        {

            WorkbookPart workbookPart = spreadSheet.WorkbookPart;

 

            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<worksheetpart>();

            newWorksheetPart.Worksheet = new Worksheet(new SheetData());

            newWorksheetPart.Worksheet.Save();

            CurrentWorksheetPart = newWorksheetPart;

            //workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1;

            //workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1;

            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            Sheets sheets = workbookPart.Workbook.GetFirstChild<sheets>();

            uint sheetId = 1;

            if (sheets.Elements<sheet>().Count() > 0)

            {

                sheetId = sheets.Elements<sheet>().Select(s => s.SheetId.Value).Max() + 1;

            }

 

            string sheetName = "Sheet" + sheetId;

 

            // Append the new worksheet and associate it with the workbook.

            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };

            sheets.Append(sheet);

            workbookPart.Workbook.Save();

        }

 

        public void InsertImage(long x, long y, long? width, long? height, string sImagePath)

        {

            try

            {

                WorksheetPart wsp = CurrentWorksheetPart;

                DrawingsPart dp;

                ImagePart imgp;

                WorksheetDrawing wsd;

 

                ImagePartType ipt;

                switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())

                {

                    case "png":

                        ipt = ImagePartType.Png;

                        break;

                    case "jpg":

                    case "jpeg":

                        ipt = ImagePartType.Jpeg;

                        break;

                    case "gif":

                        ipt = ImagePartType.Gif;

                        break;

                    default:

                        return;

                }

 

                if (wsp.DrawingsPart == null)

                {

                    //----- no drawing part exists, add a new one

 

                    dp = wsp.AddNewPart<drawingspart>();

                    imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));

                    wsd = new WorksheetDrawing();

                }

                else

                {

                    //----- use existing drawing part

 

                    dp = wsp.DrawingsPart;

                    imgp = dp.AddImagePart(ipt);

                    dp.CreateRelationshipToPart(imgp);

                    wsd = dp.WorksheetDrawing;

                }

 

                using (FileStream fs = new FileStream(sImagePath, FileMode.Open))

                {

                    imgp.FeedData(fs);

                }

 

                int imageNumber = dp.ImageParts.Count<imagepart>();

                if (imageNumber == 1)

                {

                    Drawing drawing = new Drawing();

                    drawing.Id = dp.GetIdOfPart(imgp);

                    CurrentWorksheetPart.Worksheet.Append(drawing);

                }

 

                NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();

                nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));

                nvdp.Name = "Picture " + imageNumber.ToString();

                nvdp.Description = "";

                DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();

                picLocks.NoChangeAspect = true;

                picLocks.NoChangeArrowheads = true;

                NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();

                nvpdp.PictureLocks = picLocks;

                NonVisualPictureProperties nvpp = new NonVisualPictureProperties();

                nvpp.NonVisualDrawingProperties = nvdp;

                nvpp.NonVisualPictureDrawingProperties = nvpdp;

 

                DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();

                stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

 

                BlipFill blipFill = new BlipFill();

                DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();

                blip.Embed = dp.GetIdOfPart(imgp);

                blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;

                blipFill.Blip = blip;

                blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();

                blipFill.Append(stretch);

 

                DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();

                DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();

                offset.X = 0;

                offset.Y = 0;

                t2d.Offset = offset;

                Bitmap bm = new Bitmap(sImagePath);

 

                DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();

 

                if (width == null)

                    extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);

                else

                    extents.Cx = width * (long)((float)914400 / bm.HorizontalResolution);

 

                if (height == null)

                    extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);

                else

                    extents.Cy = height * (long)((float)914400 / bm.VerticalResolution);

 

                bm.Dispose();

                t2d.Extents = extents;

                ShapeProperties sp = new ShapeProperties();

                sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;

                sp.Transform2D = t2d;

                DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();

                prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;

                prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();

                sp.Append(prstGeom);

                sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

 

                DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();

                picture.NonVisualPictureProperties = nvpp;

                picture.BlipFill = blipFill;

                picture.ShapeProperties = sp;

 

                Position pos = new Position();

                pos.X = x * 914400 / 72;

                pos.Y = y * 914400 / 72;

                Extent ext = new Extent();

                ext.Cx = extents.Cx;

                ext.Cy = extents.Cy;

                AbsoluteAnchor anchor = new AbsoluteAnchor();

                anchor.Position = pos;

                anchor.Extent = ext;

                anchor.Append(picture);

                anchor.Append(new ClientData());

                wsd.Append(anchor);

                wsd.Save(dp);

            }

            catch (Exception ex)

            {

                throw ex; // or do something more interesting if you want

            }

        }

 

        public void InsertImage(long x, long y, string sImagePath)

        {

            InsertImage(x, y, null, null, sImagePath);

        }

         

        #endregion

        #region private static OpenXml methods

 

        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)

        {

            // If the part does not contain a SharedStringTable, create one.

            if (shareStringPart.SharedStringTable == null)

            {

                shareStringPart.SharedStringTable = new SharedStringTable();

                shareStringPart.SharedStringTable.Count = 1;

                shareStringPart.SharedStringTable.UniqueCount = 1;

            }

            int i = 0;

            // Iterate through all the items in the SharedStringTable. If the text already exists, return its index.

            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<sharedstringitem>())

            {

                if (item.InnerText == text)

                {

                    return i;

                }

                i++;

            }

 

            // The text does not exist in the part. Create the SharedStringItem and return its index.

            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));

            shareStringPart.SharedStringTable.Save();

 

            return i;

        }

 

        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)

        {

            Worksheet worksheet = worksheetPart.Worksheet;

            SheetData sheetData = worksheet.GetFirstChild<sheetdata>();

            string cellReference = columnName + rowIndex;

 

            // If the worksheet does not contain a row with the specified row index, insert one.

            Row row;

            if (sheetData.Elements<row>().Where(r => r.RowIndex == rowIndex).Count() != 0)

            {

                row = sheetData.Elements<row>().Where(r => r.RowIndex == rowIndex).First();

            }

            else

            {

                row = new Row() { RowIndex = rowIndex };

                sheetData.Append(row);

            }

 

            // If there is not a cell with the specified column name, insert one. 

            if (row.Elements<cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)

            {

                return row.Elements<cell>().Where(c => c.CellReference.Value == cellReference).First();

            }

            else

            {

                // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.

                Cell refCell = null;

                foreach (Cell cell in row.Elements<cell>())

                {

                    if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)

                    {

                        refCell = cell;

                        break;

                    }

                }

 

                Cell newCell = new Cell() { CellReference = cellReference };

                row.InsertBefore(newCell, refCell);

 

                worksheet.Save();

                return newCell;

            }

        }

 

        private static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)

        {

            // Add a new worksheet part to the workbook.

            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<worksheetpart>();

            newWorksheetPart.Worksheet = new Worksheet(new SheetData());

            newWorksheetPart.Worksheet.Save();

 

            workbookPart.Workbook.AppendChild<sheets>(new Sheets());

 

            Sheets sheets = workbookPart.Workbook.GetFirstChild<sheets>();

            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

 

            // Get a unique ID for the new sheet.

            uint sheetId = 1;

            if (sheets.Elements<sheet>().Count() > 0)

            {

                sheetId = sheets.Elements<sheet>().Select(s => s.SheetId.Value).Max() + 1;

            }

 

            string sheetName = "Sheet" + sheetId;

 

            // Append the new worksheet and associate it with the workbook.

            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };

            sheets.Append(sheet);

 

            workbookPart.Workbook.Save();

            return newWorksheetPart;

        }

 

        // Given a Worksheet and a cell name, verifies that the specified cell exists.

        // If it does not exist, creates a new cell.

        private static void CreateSpreadsheetCellIfNotExist(Worksheet worksheet, string cellName)

        {

            string columnName = GetColumnName(cellName);

            uint rowIndex = GetRowIndex(cellName);

 

            IEnumerable<row> rows = worksheet.Descendants<row>().Where(r => r.RowIndex.Value == rowIndex);

 

            // If the Worksheet does not contain the specified row, create the specified row.

            // Create the specified cell in that row, and insert the row into the Worksheet.

            if (rows.Count() == 0)

            {

                Row row = new Row() { RowIndex = new UInt32Value(rowIndex) };

                Cell cell = new Cell() { CellReference = new StringValue(cellName) };

                row.Append(cell);

                worksheet.Descendants<sheetdata>().First().Append(row);

                worksheet.Save();

            }

            else

            {

                Row row = rows.First();

 

                IEnumerable<cell> cells = row.Elements<cell>().Where(c => c.CellReference.Value == cellName);

 

                // If the row does not contain the specified cell, create the specified cell.

                if (cells.Count() == 0)

                {

                    Cell cell = new Cell() { CellReference = new StringValue(cellName) };

                    row.Append(cell);

                    worksheet.Save();

                }

            }

 

        }

        // Given a cell name, parses the specified cell to get the column name.

        private static string GetColumnName(string cellName)

        {

            // Create a regular expression to match the column name portion of the cell name.

            Regex regex = new Regex("[A-Za-z]+");

            Match match = regex.Match(cellName);

 

            return match.Value;

        }

 

        // Given a cell name, parses the specified cell to get the row index.

        private static uint GetRowIndex(string cellName)

        {

            // Create a regular expression to match the row index portion the cell name.

            Regex regex = new Regex(@"\d+");

            Match match = regex.Match(cellName);

 

            return uint.Parse(match.Value);

        }

         

        #endregion

        #region Utility methods

        private static string GetColumnName(int index)

        {

            string name = "";

            char[] columnNames = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();

            int num = index;

            do

            {

                int i = num % 26;

                name = columnNames[i] + name;

                num = num / 26 - 1;

            } while (num > -1);

            if (string.IsNullOrEmpty(name))

                name = "A";

            return name;

        }

        #endregion

    }

 

}</cell></cell></sheetdata></row></row></sheet></sheet></sheets></sheets></worksheetpart></cell></cell></cell></row></row></sheetdata></sharedstringitem></imagepart></drawingspart></sheet></sheet></sheets></worksheetpart></cellvalues></cellvalues></sharedstringtablepart></sharedstringtablepart></sharedstringtablepart></sharedstringtablepart>

 

要使用上述代码，就可以用如下的一段代码：

?

FileStream file = null;

            OpenXMLExcel openXMLExcel = null;

            try

            {

                file = new FileStream(@"C:\book1.xlsx", FileMode.Open);

                openXMLExcel = new OpenXMLExcel(file, false);

                openXMLExcel.WriteData(1, 1, "数学");

                openXMLExcel.WriteData(3, 1, "物理");

                openXMLExcel.InsertImage(1, 70, @"c:\TempPic.jpg");

                openXMLExcel.Dispose();

                file.Close();

            }

            catch

            {

                if (openXMLExcel != null)

                {

                    openXMLExcel.Dispose();

                }

                if (file != null)

                {

                    file.Close();

                }

            }

            finally

            {

            }

 

FileMode.Open是为了读入已有的Excel文件的字节流。

new OpenXMLExcel(file, false)中的false是为了进行读取操作, 此参数如果为true的话，就会建立一个新的Excel Sheet。

openXMLExcel.WriteData(1, 1, "数学"); 是为了将“数学”两字写入黄色背景的单元格，前一个1是横向坐标参数，表示第1行，注意这是以单元格为单位的坐标，这个行号从0开始。后一个1是纵向组表参数，表示第一列，注意这个列号也是从0开始。即最左上的单元格的坐标是(0,0)。

openXMLExcel.InsertImage(1, 70, @"c:\TempPic.jpg"); 是为了将c:\TempPic.jpg插入到坐标为1, 70的坐标处。注意这是以象素Pixel为单位的坐标。坐标原点在左上角。

OpenXML局限性

此OpenXML不支持Excel 2003或者更早的文件格式。只支持Excel 2007及Excel 2010的文件格式。可能是因为从Excel 2007起，这些文件都用xml表示了。xlsx其实是一个zip文件，解开此zip文件，我们可以看到很多描述excel内容的xml文件。

Enjoy coding life!