简介
这是公司另外一个项目组遇到的技术问题。即如何用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().Count() > 0)
{
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().First();
}
else
{
shareStringPart = spreadSheet.WorkbookPart.AddNewPart();
}
shareStringPart.SharedStringTable = new SharedStringTable();
shareStringPart.SharedStringTable.Count = 1;
shareStringPart.SharedStringTable.UniqueCount = 1;
CurrentWorksheetPart = worksheetPart;
}
else
{
spreadSheet = SpreadsheetDocument.Open(stream, true);
shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType().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.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.SharedString);
worksheetPart.Worksheet.Save();
j++;
}
i++;
}
}
public void AddNewWorksheet()
{
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
WorksheetPart newWorksheetPart = workbookPart.AddNewPart();
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();
uint sheetId = 1;
if (sheets.Elements().Count() > 0)
{
sheetId = sheets.Elements().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();
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();
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())
{
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();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (sheetData.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements().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().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements().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())
{
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();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
workbookPart.Workbook.AppendChild(new Sheets());
Sheets sheets = workbookPart.Workbook.GetFirstChild();
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new sheet.
uint sheetId = 1;
if (sheets.Elements().Count() > 0)
{
sheetId = sheets.Elements().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 rows = worksheet.Descendants().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().First().Append(row);
worksheet.Save();
}
else
{
Row row = rows.First();
IEnumerable cells = row.Elements().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
}
}
要使用上述代码，就可以用如下的一段代码：
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!
