使用OpenXml SDK导出excel中的图像
本文关键字：图像 excel 导出 OpenXml SDK 使用 | 更新日期: 2023-09-27 18:26:41
在Excel Cell中导出多个图像时遇到问题。我在一个页面上点击一个简单的按钮就可以做到这一点。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
using System.IO;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
namespace OpenXMLExport
{
public partial class _Default : System.Web.UI.Page
{
public static string ImageFile = HttpContext.Current.Server.MapPath(@"~'Data'Sunset.jpg");
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
DataTable table = GetTable();
DataSet ds = new DataSet();
ds.Tables.Add(table);
ExportDataSet(ds, HttpContext.Current.Server.MapPath(@"~'Data'ImageExport.xlsx"));
}
/// <summary>
/// This example method generates a DataTable.
/// </summary>
static DataTable GetTable()
{
//
// Here we create a DataTable with four columns.
//
DataTable table = new DataTable();
table.Columns.Add("Dosage", typeof(int));
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Image", typeof(string));
//
// Here we add five DataRows.
//
table.Rows.Add(25, "Indocin", "David");
table.Rows.Add(50, "Enebrel", "Sam");
//table.Rows.Add(10, "Hydralazine", "Christoff");
return table;
}
private void ExportDataSet(DataSet ds, string destination)
{
using (var workbook = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Create(
destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
foreach (System.Data.DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart<DocumentFormat.OpenXml.Packaging.WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
//DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties sheetFormatProperties2 = new DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties() { DefaultRowHeight = 15D };
//sheetPart.Worksheet.Append(sheetFormatProperties2);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets =
workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{ Id = relationshipId, SheetId = sheetId, Name = table.TableName };
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List<String> columns = new List<string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
if (col.ToString() != "Image")
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
else
{
DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1 = GetSheetByName(workbookPart, "sheet");
InsertImage(sheet1, 1, 3, 3, 6, new FileStream(ImageFile, FileMode.Open,FileAccess.ReadWrite));
workbook.WorkbookPart.Workbook.Save();
}
}
sheetData.AppendChild(newRow);
}
// Close the document handle.
workbook.Close();
DownloadFile(HttpContext.Current.Server.MapPath(@"~'Data'ImageExport.xlsx"));
//System.Diagnostics.Process.Start(HttpContext.Current.Server.MapPath(@"'ImageExport.xlsx"));
}
}
}
public static void DownloadFile(string filePath)
{
string path = filePath;// HttpContext.Current.Server.MapPath(filePath);
System.IO.FileInfo file = new System.IO.FileInfo(path);
if (file.Exists)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.WriteFile(file.FullName);
HttpContext.Current.Response.End();
}
}
/// <summary>
/// Returns the WorksheetPart for the specified sheet name
/// </summary>
/// <param name="workbookpart">The WorkbookPart</param>
/// <param name="sheetName">The name of the worksheet</param>
/// <returns>Returns the WorksheetPart for the specified sheet name</returns>
private static DocumentFormat.OpenXml.Packaging.WorksheetPart GetSheetByName(DocumentFormat.OpenXml.Packaging.WorkbookPart workbookpart, string sheetName)
{
foreach (DocumentFormat.OpenXml.Packaging.WorksheetPart sheetPart in workbookpart.WorksheetParts)
{
string uri = sheetPart.Uri.ToString();
if (uri.EndsWith(sheetName + ".xml"))
return sheetPart;
}
return null;
}
/// <summary>
/// Inserts the image at the specified location
/// </summary>
/// <param name="sheet1">The WorksheetPart where image to be inserted</param>
/// <param name="startRowIndex">The starting Row Index</param>
/// <param name="startColumnIndex">The starting column index</param>
/// <param name="endRowIndex">The ending row index</param>
/// <param name="endColumnIndex">The ending column index</param>
/// <param name="imageStream">Stream which contains the image data</param>
private static void InsertImage(DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1,
int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
{
//Inserting a drawing element in worksheet
//Make sure that the relationship id is same for drawing element in worksheet and its relationship part
int drawingPartId = GetNextRelationShipID(sheet1);
DocumentFormat.OpenXml.Spreadsheet.Drawing drawing1 = new DocumentFormat.OpenXml.Spreadsheet.Drawing()
{ Id = "rId" + drawingPartId.ToString() };
//Check whether the WorksheetPart contains VmlDrawingParts (LegacyDrawing element)
if (sheet1.VmlDrawingParts == null)
{
//if there is no VMLDrawing part (LegacyDrawing element) exists, just append the drawing part to the sheet
sheet1.Worksheet.Append(drawing1);
}
else
{
//if VmlDrawingPart (LegacyDrawing element) exists, then find the index of legacy drawing in the sheet and inserts the new drawing element before VMLDrawing part
int legacyDrawingIndex = GetIndexofLegacyDrawing(sheet1);
if (legacyDrawingIndex != -1)
sheet1.Worksheet.InsertAt<DocumentFormat.OpenXml.OpenXmlElement>(drawing1, legacyDrawingIndex);
else
sheet1.Worksheet.Append(drawing1);
}
//Adding the drawings.xml part
DocumentFormat.OpenXml.Packaging.DrawingsPart drawingsPart1
= sheet1.AddNewPart<DocumentFormat.OpenXml.Packaging.DrawingsPart>("rId" + drawingPartId.ToString());
GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
//Adding the image
DocumentFormat.OpenXml.Packaging.ImagePart imagePart1 = drawingsPart1.AddNewPart<DocumentFormat.OpenXml.Packaging.ImagePart>("image/jpeg", "rId1");
imagePart1.FeedData(imageStream);
}
// Generates content of drawingsPart1.
private static void GenerateDrawingsPart1Content(DocumentFormat.OpenXml.Packaging.DrawingsPart drawingsPart1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
{
Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();
worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };
Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
Xdr.ColumnId columnId1 = new Xdr.ColumnId();
columnId1.Text = startColumnIndex.ToString();
Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
columnOffset1.Text = "38100";
Xdr.RowId rowId1 = new Xdr.RowId();
rowId1.Text = startRowIndex.ToString();
Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
rowOffset1.Text = "0";
fromMarker1.Append(columnId1);
fromMarker1.Append(columnOffset1);
fromMarker1.Append(rowId1);
fromMarker1.Append(rowOffset1);
Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
Xdr.ColumnId columnId2 = new Xdr.ColumnId();
columnId2.Text = endColumnIndex.ToString();
Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
columnOffset2.Text = "542925";
Xdr.RowId rowId2 = new Xdr.RowId();
rowId2.Text = endRowIndex.ToString();
Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
rowOffset2.Text = "161925";
toMarker1.Append(columnId2);
toMarker1.Append(columnOffset2);
toMarker1.Append(rowId2);
toMarker1.Append(rowOffset2);
Xdr.Picture picture1 = new Xdr.Picture();
Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (DocumentFormat.OpenXml.UInt32Value)2U, Name = "Picture 1" };
//DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties sheetFormatProperties3
// = new DocumentFormat.OpenXml.Spreadsheet.SheetFormatProperties() { DefaultRowHeight = 15D ,DefaultColumnWidth = 25D};
Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };
nonVisualPictureDrawingProperties1.Append(pictureLocks1);
nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);
Xdr.BlipFill blipFill1 = new Xdr.BlipFill();
A.Blip blip1 = new A.Blip() { Embed = "rId1", CompressionState = A.BlipCompressionValues.Print };
blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();
A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };
A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
blipExtension1.Append(useLocalDpi1);
blipExtensionList1.Append(blipExtension1);
blip1.Append(blipExtensionList1);
A.Stretch stretch1 = new A.Stretch();
A.FillRectangle fillRectangle1 = new A.FillRectangle();
stretch1.Append(fillRectangle1);
blipFill1.Append(blip1);
blipFill1.Append(stretch1);
Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();
A.Transform2D transform2D1 = new A.Transform2D();
A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };
transform2D1.Append(offset1);
transform2D1.Append(extents1);
A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
A.AdjustValueList adjustValueList1 = new A.AdjustValueList();
presetGeometry1.Append(adjustValueList1);
shapeProperties1.Append(transform2D1);
shapeProperties1.Append(presetGeometry1);
picture1.Append(nonVisualPictureProperties1);
picture1.Append(blipFill1);
picture1.Append(shapeProperties1);
Xdr.ClientData clientData1 = new Xdr.ClientData();
//CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
//CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
//cellStyleFormats1.Append(cellFormat1);
//CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
//CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
//CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
//CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
//CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
//cellFormats1.Append(cellFormat2);
//cellFormats1.Append(cellFormat3);
//cellFormats1.Append(cellFormat4);
//cellFormats1.Append(cellFormat5);
//CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
//CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
//cellStyles1.Append(cellStyle1);
//twoCellAnchor1.Append(cellStyles1);
//twoCellAnchor1.Append(cellFormats1);
twoCellAnchor1.Append(fromMarker1);
twoCellAnchor1.Append(toMarker1);
twoCellAnchor1.Append(picture1);
twoCellAnchor1.Append(clientData1);
//twoCellAnchor1.Append(sheetFormatProperties3);
worksheetDrawing1.Append(twoCellAnchor1);
drawingsPart1.WorksheetDrawing = worksheetDrawing1;
}
/// <summary>
/// Get the index of legacy drawing element in the specified WorksheetPart
/// </summary>
/// <param name="sheet1">The worksheetPart</param>
/// <returns>Index of legacy drawing</returns>
private static int GetIndexofLegacyDrawing(DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1)
{
for (int i = 0; i < sheet1.Worksheet.ChildElements.Count; i++)
{
DocumentFormat.OpenXml.OpenXmlElement element = sheet1.Worksheet.ChildElements[i];
if (element is DocumentFormat.OpenXml.Spreadsheet.LegacyDrawing)
return i;
}
return -1;
}
/// <summary>
/// Returns the next relationship id for the specified WorksheetPart
/// </summary>
/// <param name="sheet1">The worksheetPart</param>
/// <returns>Returns the next relationship id </returns>
private static int GetNextRelationShipID(DocumentFormat.OpenXml.Packaging.WorksheetPart sheet1)
{
int nextId = 0;
List<int> ids = new List<int>();
foreach (DocumentFormat.OpenXml.Packaging.IdPartPair part in sheet1.Parts)
{
ids.Add(int.Parse(part.RelationshipId.Replace("rId", string.Empty)));
}
if (ids.Count > 0)
nextId = ids.Max() + 1;
else
nextId = 1;
return nextId;
}
}
}
如果我试图导出一个有一行的表，它可以正常工作。但我遇到了多行的问题
DocumentFormat.OpenXml.Packaging.DrawingsPart drawingsPart1
= sheet1.AddNewPart<DocumentFormat.OpenXml.Packaging.DrawingsPart>("rId" + drawingPartId.ToString());
在添加第二行的绘图部分时，我收到错误"此父项只允许使用该类型的一个实例。"
与我发现的错误相同在这里http://social.msdn.microsoft.com/Forums/office/en-US/8ac6040f-8599-4e20-84fb-4b2390847373/excel-style-part-using-openxml-in-c
但在我的案件中仍然无法解决。。。我只需要使用OpenXMl
在这段代码中，您一直试图为工作表中的每个图像添加一个新的绘图部分。根据Excel包的开放xml文件格式规范，工作表和图表表只能有一个绘图部分。因此，更改代码以将图像（多个两个CellAnchor标记）附加到工作表的单个绘图部分中。通过这种方式，您可以避免此异常。
请找到使用openxml-sdf将简单DataTable导入Excel工作簿的代码片段。我希望这将有助于实现你的要求。
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
ExcelBuilder obj = new ExcelBuilder();
obj.SetDataSource(GetTable());
obj.CreatePackage(@"output.xlsx");
System.Diagnostics.Process.Start("output.xlsx");
}
/// <summary>
/// Get the DataTable instance
/// </summary>
/// <returns>Returns the DataTable instance</returns>
private DataTable GetTable()
{
DataTable table = new DataTable();
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Image", typeof(Image));
table.Rows.Add("AAAA", GetImageFrom64("iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAFqSURBVDhPY/hPAph45sP/xs0XoDwIIMqA2v0v/kfu/PA//tTP/ylLTkBFIQCvARk7X/0P2/3pv8/O7/9ddv4E47jV56CyEIDVgL6jL/4nHP3533f/z/+OQE12G96DbS5fe4I4AyYdfwHW7Lz8wf+OragagpachrIgAKsBzUfe/k9dchzKQwUesw5BWRCA1YDKw+//N607DOWhAqIMqAG6oHz5ASgPFXhOJ8KA8kPv/5cu3gXl/f8/9eTz/26rHvxXmffkv8/kvVBRCMDpgry5m6G8//+1lz7+L9x/7T9f553/rj0Ig0EAqwFh24FeWLAVzC49+uk/b9eV/7xNZ8EGlM3bCRaHARQDbDa+/q+24uV/2RUf/rcs3wMV/f+fr/rIf5G6o/8TJ26BiiAA3ADP9U//S8979F9s4rX/wjOe/W9fdRAqgx/ADbCfexmsUWjCQ7BtxAIULyTP2Pk/eyaqHwkBrIFIPPj/HwAXQanDAoJm4wAAAABJRU5ErkJggg=="));
table.Rows.Add("BBBB", GetImageFrom64("iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAEoSURBVDhPjZM9S8NQFIb9VcUf4NRNRHCxW4sOji4GK8UspRAIghAEBR0yBSczlSKiEiS0XRR0iODg4H+Q17zn3AZDc68+cLlfnOfkHG5W4CBNU7OyYxUkSQLP82R2YRVEUYSbu1zmIAjM6TJWQetojNXBRIbv++Z0Gatg7fILZy9A7/pTSrHhFIxmQCcu6oL3E+BtZDZ/CI5zYOPitfkL7rvAw55bcJBpL8IwNKe/KIMx2fyfoBEK0naD4Glf6qwJWDPHtA/ku2XmLclOKsH380Av8g4w36kLHrf1jllnQxOhqKC4EoFI2JwyEwVsoLUEgwrKIAmeH8qW8AnzX3C9QqKC2/WqpgVZllUSFyr4OFdJcSrbBXEcm5WdqokCO80us2kUcjgBfgCofKZ+pmmmjQAAAABJRU5ErkJggg=="));
table.Rows.Add("CCCC", GetImageFrom64("iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAEUSURBVDhPpZIhc4QwFIQzESdO9gdW9gecrEAgEAgEAoFEIJAIJALJD6hAIBAIBAKBQNw2SydMgNzN3FV8mUne7ibvgSiKAmVZoqoq1HW9wT3PWc/zHFmWIUkSxHGMKIoQBAF838caME0T5nm2wppmHMeVYRhWtgAKl2U58ShkF8DnHQNCeVnRIXp/DGAbgr2ZZk8J79cPK6ydAtI03QV88yZ5tcLaqQVO1mzhpkQ/D2DtFMDPwt70wL6kRKmENljT5i0gDMPdlD+V6Bna3Pc9PM+DYAqfZYZoeK4xb6a56zq4rgvBxRSamKajuW1bOI4DwcUmPEKjaW6a5i+A023ehF5hm/YriEwt/0Hw9+QP8g6uvOAX9G/HbwDThCwAAAAASUVORK5CYII="));
return table;
}
/// <summary>
/// Converts the From64 data into Image instance
/// </summary>
/// <param name="base64String">Image stream in Base64 encoding</param>
/// <returns>Returns the Image instance</returns>
private Image GetImageFrom64(string base64String)
{
MemoryStream stream = new MemoryStream(System.Convert.FromBase64String(base64String));
return Image.FromStream(stream);
}
}
public class ExcelBuilder
{
/// <summary>
/// static field to maintain the track the relationship id
/// </summary>
private static int s_rId;
/// <summary>
/// Field for Data source
/// </summary>
private DataTable m_table;
/// <summary>
/// Collection to maintain string value of cell and to serialize the content in SharedString xml part
/// </summary>
private List<string> sharedStrings = new List<string>();
/// <summary>
/// Collection to maintain the image collection added into the excel workbook
/// </summary>
private Dictionary<string, Image> ImageCollection = new Dictionary<string, Image>();
/// <summary>
/// Set the DataSource of the Excel builder
/// </summary>
/// <param name="table"></param>
public void SetDataSource(DataTable table)
{
m_table = table;
}
/// <summary>
/// Create a new Excel file
/// </summary>
/// <param name="filePath">Path of the output file</param>
public void CreatePackage(string filePath)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
CreateParts(package);
}
}
// Adds child parts and generates content of the specified part.
private void CreateParts(SpreadsheetDocument workbook)
{
WorkbookPart workbookPart1 = workbook.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>(GetNextRelationShipId());
GenerateWorksheetPart1Content(worksheetPart1);
SharedStringTablePart sharedStringTablePart1 = workbookPart1.AddNewPart<SharedStringTablePart>(GetNextRelationShipId());
GenerateSharedStringTablePart1Content(sharedStringTablePart1);
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
// Generates content of worksheetPart1.
private void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1)
{
Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetData sheetData1 = new SheetData();
string drawingrID = GetNextRelationShipId();
AppendSheetData(sheetData1, worksheetPart1, drawingrID);
worksheet1.Append(sheetData1);
if (worksheetPart1.DrawingsPart != null && worksheetPart1.DrawingsPart.WorksheetDrawing != null)
{
Drawing drawing1 = new Drawing() { Id = drawingrID };
worksheet1.Append(drawing1);
}
worksheetPart1.Worksheet = worksheet1;
}
private void AppendSheetData(SheetData sheetData1, WorksheetPart worksheetPart, string drawingrID)
{
for (int rowIndex = 0; rowIndex < m_table.Rows.Count; rowIndex++)
{
Row row = new Row() { RowIndex = (UInt32Value)(rowIndex + 1U) };
DataRow tableRow = m_table.Rows[rowIndex];
for (int colIndex = 0; colIndex < tableRow.ItemArray.Length; colIndex++)
{
Cell cell = new Cell();
CellValue cellValue = new CellValue();
object data = tableRow.ItemArray[colIndex];
if (data is int || data is float || data is double)
{
cellValue.Text = data.ToString();
cell.Append(cellValue);
}
else if (data is string)
{
cell.DataType = CellValues.SharedString;
string text = data.ToString();
if (!sharedStrings.Contains(text))
sharedStrings.Add(text);
cellValue.Text = sharedStrings.IndexOf(text).ToString();
cell.Append(cellValue);
}
else if (data is Image)
{
DrawingsPart drawingsPart = null;
Xdr.WorksheetDrawing worksheetDrawing = null;
if (worksheetPart.DrawingsPart == null)
{
drawingsPart = worksheetPart.AddNewPart<DrawingsPart>(drawingrID);
worksheetDrawing = new Xdr.WorksheetDrawing();
worksheetDrawing.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
worksheetDrawing.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
drawingsPart.WorksheetDrawing = worksheetDrawing;
}
else if (worksheetPart.DrawingsPart != null && worksheetPart.DrawingsPart.WorksheetDrawing != null)
{
drawingsPart = worksheetPart.DrawingsPart;
worksheetDrawing = worksheetPart.DrawingsPart.WorksheetDrawing;
}
string imagerId = GetNextRelationShipId();
Xdr.TwoCellAnchor cellAnchor = AddTwoCellAnchor(rowIndex, 1, rowIndex, 1, imagerId);
worksheetDrawing.Append(cellAnchor);
ImagePart imagePart = drawingsPart.AddNewPart<ImagePart>("image/png", imagerId);
GenerateImagePartContent(imagePart, data as Image);
}
row.Append(cell);
}
sheetData1.Append(row);
}
}
// Generates content of imagePart1.
private void GenerateImagePartContent(ImagePart imagePart, Image image)
{
MemoryStream memStream = new MemoryStream();
image.Save(memStream, ImageFormat.Png);
memStream.Position = 0;
imagePart.FeedData(memStream);
memStream.Close();
}
private Xdr.TwoCellAnchor AddTwoCellAnchor(int startRow, int startColumn, int endRow, int endColumn, string imagerId)
{
Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };
Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
Xdr.ColumnId columnId1 = new Xdr.ColumnId();
columnId1.Text = startColumn.ToString();
Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
columnOffset1.Text = "0";
Xdr.RowId rowId1 = new Xdr.RowId();
rowId1.Text = startRow.ToString();
Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
rowOffset1.Text = "0";
fromMarker1.Append(columnId1);
fromMarker1.Append(columnOffset1);
fromMarker1.Append(rowId1);
fromMarker1.Append(rowOffset1);
Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
Xdr.ColumnId columnId2 = new Xdr.ColumnId();
columnId2.Text = endColumn.ToString();
Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
columnOffset2.Text = "152381";
Xdr.RowId rowId2 = new Xdr.RowId();
rowId2.Text = endRow.ToString();
Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
rowOffset2.Text = "152381";
toMarker1.Append(columnId2);
toMarker1.Append(columnOffset2);
toMarker1.Append(rowId2);
toMarker1.Append(rowOffset2);
Xdr.Picture picture1 = new Xdr.Picture();
Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Picture 1" };
Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };
nonVisualPictureDrawingProperties1.Append(pictureLocks1);
nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);
Xdr.BlipFill blipFill1 = new Xdr.BlipFill();
A.Blip blip1 = new A.Blip() { Embed = imagerId };
blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();
A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };
A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");
blipExtension1.Append(useLocalDpi1);
blipExtensionList1.Append(blipExtension1);
blip1.Append(blipExtensionList1);
A.Stretch stretch1 = new A.Stretch();
A.FillRectangle fillRectangle1 = new A.FillRectangle();
stretch1.Append(fillRectangle1);
blipFill1.Append(blip1);
blipFill1.Append(stretch1);
Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();
A.Transform2D transform2D1 = new A.Transform2D();
A.Offset offset1 = new A.Offset() { X = 0L, Y = 0L };
A.Extents extents1 = new A.Extents() { Cx = 152381L, Cy = 152381L };
transform2D1.Append(offset1);
transform2D1.Append(extents1);
A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
A.AdjustValueList adjustValueList1 = new A.AdjustValueList();
presetGeometry1.Append(adjustValueList1);
shapeProperties1.Append(transform2D1);
shapeProperties1.Append(presetGeometry1);
picture1.Append(nonVisualPictureProperties1);
picture1.Append(blipFill1);
picture1.Append(shapeProperties1);
Xdr.ClientData clientData1 = new Xdr.ClientData();
twoCellAnchor1.Append(fromMarker1);
twoCellAnchor1.Append(toMarker1);
twoCellAnchor1.Append(picture1);
twoCellAnchor1.Append(clientData1);
return twoCellAnchor1;
}
/// <summary>
/// Generates the SharedString xml part using the string collection in SharedStrings (List<string>)
/// </summary>
/// <param name="part"></param>
private void GenerateSharedStringTablePart1Content(SharedStringTablePart part)
{
SharedStringTable sharedStringTable1 = new SharedStringTable();
sharedStringTable1.Count = new UInt32Value((uint)sharedStrings.Count);
sharedStringTable1.UniqueCount = new UInt32Value((uint)sharedStrings.Count);
foreach (string item in sharedStrings)
{
SharedStringItem sharedStringItem = new SharedStringItem();
Text text = new Text();
text.Text = item;
sharedStringItem.Append(text);
sharedStringTable1.Append(sharedStringItem);
}
part.SharedStringTable = sharedStringTable1;
}
/// <summary>
/// Gets the next relationship id
/// </summary>
/// <returns></returns>
private string GetNextRelationShipId()
{
s_rId++;
return "rId" + s_rId.ToString();
}
}