如何使用 Excel 对象将 DataGridView 数据导出到 Excel
2024-04-15 01:48:22
转载出处：https://code.msdn.microsoft.com/How-to-insert-image-into-93964561
本项目阐述如何使用 Open XML SDK 将图像插入到 Excel 中
简介
本示例演示如何使用 Open XML SDK 将图像自动插入到电子表格中。一些客户经常在 MSDN 论坛上咨询此问题，但是 MSDN 上并不存在示例。因此，如果 MSDN 中存在此示例，客户将可以从此示例中获取帮助。
客户证明：
http://social.msdn.microsoft.com/Forums/zh-CN/oxmlsdk/thread/4c369c2f-72ed-4e86-9c62-ff606d29ace2
http://social.msdn.microsoft.com/Forums/zh-CN/oxmlsdk/thread/5c60076e-9884-4298-a443-c97d941cf09d
http://stackoverflow.com/questions/5793950/c-sharp-openxml-insert-an-image-into-an-excel-document
生成项目
在 Visual Studio 2013 中打开项目 (InsertImageIntoExcel.csproj) 并生成项目。
class Utility
{
public static void CreatePackage(string sFile, string imageFileName)
{
try
{
// Create a spreadsheet document by supplying the filepath.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = ,
Name = "mySheet"
};
sheets.Append(sheet); var drawingsPart = worksheetPart.AddNewPart<DrawingsPart>(); if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
{
worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
} if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
} var worksheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg); using (var stream = new FileStream(imageFileName, FileMode.Open))
{
imagePart.FeedData(stream);
} Bitmap bm = new Bitmap(imageFileName);
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
var extentsCx = (long)bm.Width * (long)((float) / bm.HorizontalResolution);
var extentsCy = (long)bm.Height * (long)((float) / bm.VerticalResolution);
bm.Dispose(); var colOffset = ;
var rowOffset = ;
int colNumber = ;
int rowNumber = ; var nvps = worksheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>();
var nvpId = nvps.Count() > ?
(UInt32Value)worksheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + :
1U; var oneCellAnchor = new Xdr.OneCellAnchor(
new Xdr.FromMarker
{
ColumnId = new Xdr.ColumnId((colNumber - ).ToString()),
RowId = new Xdr.RowId((rowNumber - ).ToString()),
ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()),
RowOffset = new Xdr.RowOffset(rowOffset.ToString())
},
new Xdr.Extent { Cx = extentsCx, Cy = extentsCy },
new Xdr.Picture(
new Xdr.NonVisualPictureProperties(
new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imageFileName },
new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true })
),
new Xdr.BlipFill(
new A.Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print },
new A.Stretch(new A.FillRectangle())
),
new Xdr.ShapeProperties(
new A.Transform2D(
new A.Offset { X = , Y = },
new A.Extents { Cx = extentsCx, Cy = extentsCy }
),
new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle }
)
),
new Xdr.ClientData()
); worksheetDrawing.Append(oneCellAnchor); workbookpart.Workbook.Save(); // Close the document.
spreadsheetDocument.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
运行示例
- 本示例使用 Open XML SDK 将图像插入到 Excel 文件中。
- 将图像文件 SampleImage.jpg 从项目位置复制到可执行文件位置。
- 运行可执行文件‘InsertImageIntoExcel.exe’。此操作将创建一个名为‘InsertImage .xlsx’的 Excel 文件并将指定的图像插入到新工作表中。
- 打开 Excel 文件 InsertImage.xlsx 并验证内容
从项目中复制出来的代码：
Program.cs文件中的代码
//****************************** Module Header ******************************\
//Module Name: Program.cs
//Project: InsertImageIntoExcel
//Copyright (c) Microsoft Corporation //The project illustrates how to insert image into Excel using Open XML SDK //This source is subject to the Microsoft Public License.
//See http://www.microsoft.com/en-us/openness/resources/licenses.aspx#MPL.
//All other rights reserved. //*****************************************************************************/
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Drawing; namespace InsertImageIntoExcel
{
class Program
{
static void Main(string[] args)
{
try
{
string appPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location); string sFile = appPath + "\\InsertImage.xlsx"; string imageFile = appPath + "\\SampleImage.jpg"; // If the file exists, delete it
if (File.Exists(sFile))
{
File.Delete(sFile);
} Utility.CreatePackage(sFile, imageFile);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
Utility.cs中的代码
//****************************** Module Header ******************************\
//Module Name: Utility.cs
//Project: InsertImageIntoExcel
//Copyright (c) Microsoft Corporation //The project illustrates how to insert image into Excel using Open XML SDK //This source is subject to the Microsoft Public License.
//See http://www.microsoft.com/en-us/openness/resources/licenses.aspx#MPL.
//All other rights reserved. //*****************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using System.IO;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using System.Drawing; namespace InsertImageIntoExcel
{
class Utility
{
public static void CreatePackage(string sFile, string imageFileName)
{
try
{
// Create a spreadsheet document by supplying the filepath.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = ,
Name = "mySheet"
};
sheets.Append(sheet); var drawingsPart = worksheetPart.AddNewPart<DrawingsPart>(); if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
{
worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
} if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
} var worksheetDrawing = drawingsPart.WorksheetDrawing; var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg); using (var stream = new FileStream(imageFileName, FileMode.Open))
{
imagePart.FeedData(stream);
} Bitmap bm = new Bitmap(imageFileName);
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
var extentsCx = (long)bm.Width * (long)((float) / bm.HorizontalResolution);
var extentsCy = (long)bm.Height * (long)((float) / bm.VerticalResolution);
bm.Dispose(); var colOffset = ;
var rowOffset = ;
int colNumber = ;
int rowNumber = ; var nvps = worksheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>();
var nvpId = nvps.Count() > ?
(UInt32Value)worksheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + :
1U; var oneCellAnchor = new Xdr.OneCellAnchor(
new Xdr.FromMarker
{
ColumnId = new Xdr.ColumnId((colNumber - ).ToString()),
RowId = new Xdr.RowId((rowNumber - ).ToString()),
ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()),
RowOffset = new Xdr.RowOffset(rowOffset.ToString())
},
new Xdr.Extent { Cx = extentsCx, Cy = extentsCy },
new Xdr.Picture(
new Xdr.NonVisualPictureProperties(
new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imageFileName },
new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true })
),
new Xdr.BlipFill(
new A.Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print },
new A.Stretch(new A.FillRectangle())
),
new Xdr.ShapeProperties(
new A.Transform2D(
new A.Offset { X = , Y = },
new A.Extents { Cx = extentsCx, Cy = extentsCy }
),
new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle }
)
),
new Xdr.ClientData()
); worksheetDrawing.Append(oneCellAnchor); workbookpart.Workbook.Save(); // Close the document.
spreadsheetDocument.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
//****************************** Module Header ******************************\
//Module Name: Utility.cs
//Project: InsertImageIntoExcel
//Copyright (c) Microsoft Corporation //The project illustrates how to insert image into Excel using Open XML SDK //This source is subject to the Microsoft Public License.
//See http://www.microsoft.com/en-us/openness/resources/licenses.aspx#MPL.
//All other rights reserved. //*****************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using System.IO;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using A = DocumentFormat.OpenXml.Drawing;
using System.Drawing; namespace InsertImageIntoExcel
{
class Utility
{
public static void CreatePackage(string sFile, string imageFileName) //sFile指的是InsertImage.xlsx的路径；imageFileName指的是SampleImage.jpg路径
{
try
{
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(sFile, SpreadsheetDocumentType.Workbook);//从指定的文件创建SpreadsheetDocument 类的一个新实例
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); //为SpreadsheetDocument添加Workbookpart
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();//为WookBookPart添加WorksheetPart
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());//为WorkBook添加Sheets
//追加一个worksheet新实例并连接到workbook
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = ,
Name = "mySheet"
};
sheets.Append(sheet);
var drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
if (!worksheetPart.Worksheet.ChildElements.OfType<Drawing>().Any())
{
worksheetPart.Worksheet.Append(new Drawing { Id = worksheetPart.GetIdOfPart(drawingsPart) });
} if (drawingsPart.WorksheetDrawing == null)
{
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
} var worksheetDrawing = drawingsPart.WorksheetDrawing;
var imagePart = drawingsPart.AddImagePart(ImagePartType.Jpeg);
using (var stream = new FileStream(imageFileName, FileMode.Open))
{
imagePart.FeedData(stream);
} Bitmap bm = new Bitmap(imageFileName);
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
var extentsCx = (long)bm.Width * (long)((float) / bm.HorizontalResolution);
var extentsCy = (long)bm.Height * (long)((float) / bm.VerticalResolution);
bm.Dispose(); var colOffset = ;
var rowOffset = ;
int colNumber = ;
int rowNumber = ; var nvps = worksheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>();
var nvpId = nvps.Count() > ?
(UInt32Value)worksheetDrawing.Descendants<Xdr.NonVisualDrawingProperties>().Max(p => p.Id.Value) + :
1U; var oneCellAnchor = new Xdr.OneCellAnchor(
new Xdr.FromMarker
{
ColumnId = new Xdr.ColumnId((colNumber - ).ToString()),
RowId = new Xdr.RowId((rowNumber - ).ToString()),
ColumnOffset = new Xdr.ColumnOffset(colOffset.ToString()),
RowOffset = new Xdr.RowOffset(rowOffset.ToString())
},
new Xdr.Extent { Cx = extentsCx, Cy = extentsCy },
new Xdr.Picture(
new Xdr.NonVisualPictureProperties(
new Xdr.NonVisualDrawingProperties { Id = nvpId, Name = "Picture " + nvpId, Description = imageFileName },
new Xdr.NonVisualPictureDrawingProperties(new A.PictureLocks { NoChangeAspect = true })
),
new Xdr.BlipFill(
new A.Blip { Embed = drawingsPart.GetIdOfPart(imagePart), CompressionState = A.BlipCompressionValues.Print },
new A.Stretch(new A.FillRectangle())
),
new Xdr.ShapeProperties(
new A.Transform2D(
new A.Offset { X = , Y = },
new A.Extents { Cx = extentsCx, Cy = extentsCy }
),
new A.PresetGeometry { Preset = A.ShapeTypeValues.Rectangle }
)
),
new Xdr.ClientData()
); worksheetDrawing.Append(oneCellAnchor);
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
转载地址：http://www.cnblogs.com/sshoub/archive/2012/08/28/2660152.html
private static void CreateSpreadSheet()
{
string fileName = "E:\\01.xlsx";
string sheetName = "test"; using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create the workbook
spreadSheet.AddWorkbookPart();
spreadSheet.WorkbookPart.Workbook = new Workbook(); // create the worksheet
spreadSheet.WorkbookPart.AddNewPart<WorksheetPart>();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet();
SharedStringTablePart m_SharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>(); // create sheet data
SheetData m_SheetData = new SheetData();
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(m_SheetData); // create row
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(new Row());
//// create cell with data
//spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().First().AppendChild(new Cell() { CellValue = new CellValue("100") }); //Add Data
Row row1 = new Row() { RowIndex = }; Cell cell1 = new Cell() { CellReference = "A1" };
int index = InsertSharedStringItem("Sun", m_SharedStringTablePart);
cell1.CellValue = new CellValue(index.ToString());
cell1.DataType = new EnumValue<CellValues>(CellValues.SharedString); row1.Append(cell1);
m_SheetData.Append(row1); // save worksheet
spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save(); // create the worksheet to workbook relation
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First()),
SheetId = ,
Name = sheetName
}); spreadSheet.WorkbookPart.Workbook.Save();
}
} 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 = ;
shareStringPart.SharedStringTable.UniqueCount = ;
} int i = ;
// 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;
}
转载地址：http://www.cnblogs.com/tianjinquan/p/3972346.html
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.IO;
using System.Xml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml; namespace ECLink.Common
{
/// <summary>
/// 采用openxml方式把excel转换成DataSet
/// </summary>
public class ExcelHelper
{
public ExcelHelper()
{ }
/// <summary>
/// 将Excel多单一表转化为DataSet数据集对象
/// </summary>
/// <param name="filePath">Excel文件路径</param>
/// <returns>转化的数据集</returns>
public DataSet ExcelToDataSet(string filePath)
{
DataSet dataSet = new DataSet();
try
{
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))
{
//指定WorkbookPart对象
WorkbookPart workBookPart = spreadDocument.WorkbookPart;
//获取Excel中SheetName集合
List<string> sheetNames = GetSheetNames(workBookPart); foreach (string sheetName in sheetNames)
{
DataTable dataTable = WorkSheetToTable(workBookPart, sheetName);
if (dataTable != null)
{
dataSet.Tables.Add(dataTable);//将表添加到数据集
}
}
}
}
catch (Exception exp)
{
throw new Exception("可能Excel正在打开中,请关闭重新操作！");
}
return dataSet;
} /// <summary>
/// 根据WorkbookPart和表名创建DataTable对象
/// </summary>
/// <param name="workBookPart">WorkbookPart对象</param>
/// <param name="tableName">表名</param>
/// <returns>转化后的DataTable</returns>
public DataTable WorkSheetToTable(WorkbookPart workBookPart, string sheetName)
{
//创建Table
DataTable dataTable = new DataTable(sheetName); //根据WorkbookPart和sheetName获取该Sheet下所有行数据
IEnumerable<Row> sheetRows = GetWorkBookPartRows(workBookPart, sheetName);
IEnumerable<Column> sheetColumns = GetWorkBookPartColumns(workBookPart, sheetName); if (sheetRows == null || sheetRows.Count() <= )
{
return null;
} SharedStringTable stringTable = workBookPart.SharedStringTablePart.SharedStringTable;
//将数据导入DataTable,假定第一行为列名,第二行以后为数据
foreach (Row row in sheetRows)
{
//获取Excel中的列头
if (row.RowIndex == )
{
GetDataColumn(row, stringTable, ref dataTable);
}
else
{
GetDataRow(row, stringTable, ref dataTable);
}
}
return dataTable;
} /// <summary>
/// 根据WorkbookPart获取所有SheetName
/// </summary>
/// <param name="workBookPart"></param>
/// <returns>SheetName集合</returns>
private List<string> GetSheetNames(WorkbookPart workBookPart)
{
List<string> sheetNames = new List<string>();
Sheets sheets = workBookPart.Workbook.Sheets;
foreach (Sheet sheet in sheets)
{
string sheetName = sheet.Name;
if (!string.IsNullOrEmpty(sheetName))
{
sheetNames.Add(sheetName);
}
}
return sheetNames;
} /// <summary>
/// 根据WorkbookPart和sheetName获取该Sheet下所有Row数据
/// </summary>
/// <param name="workBookPart">WorkbookPart对象</param>
/// <param name="sheetName">SheetName</param>
/// <returns>该SheetName下的所有Row数据</returns>
public IEnumerable<Row> GetWorkBookPartRows(WorkbookPart workBookPart, string sheetName)
{
IEnumerable<Row> sheetRows = null;
//根据表名在WorkbookPart中获取Sheet集合
IEnumerable<Sheet> sheets = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == )
{
return null;//没有数据
} WorksheetPart workSheetPart = workBookPart.GetPartById(sheets.First().Id) as WorksheetPart;
//获取Excel中得到的行
sheetRows = workSheetPart.Worksheet.Descendants<Row>();
return sheetRows;
} /// <summary>
/// 根据WorkbookPart和sheetName获取该Sheet下所有Row数据
/// </summary>
/// <param name="workBookPart">WorkbookPart对象</param>
/// <param name="sheetName">SheetName</param>
/// <returns>该SheetName下的所有Row数据</returns>
public IEnumerable<Column> GetWorkBookPartColumns(WorkbookPart workBookPart, string sheetName)
{
IEnumerable<Column> sheetColumns = null;
//根据表名在WorkbookPart中获取Sheet集合
IEnumerable<Sheet> sheets = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == )
{
return null;//没有数据
} WorksheetPart workSheetPart = workBookPart.GetPartById(sheets.First().Id) as WorksheetPart; sheetColumns = workSheetPart.Worksheet.Descendants<Column>(); //获取Excel中得到的行 return sheetColumns;
} /// <summary>
/// 获取Excel中多表的表名
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private List<string> GetExcelSheetNames(string filePath)
{
string sheetName = string.Empty;
List<string> sheetNames = new List<string>();//所有Sheet表名
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))
{
WorkbookPart workBook = spreadDocument.WorkbookPart;
Stream stream = workBook.GetStream(FileMode.Open);
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(stream); XmlNamespaceManager xmlNSManager = new XmlNamespaceManager(xmlDocument.NameTable);
xmlNSManager.AddNamespace("default", xmlDocument.DocumentElement.NamespaceURI);
XmlNodeList nodeList = xmlDocument.SelectNodes("//default:sheets/default:sheet", xmlNSManager); foreach (XmlNode node in nodeList)
{
sheetName = node.Attributes["name"].Value;
sheetNames.Add(sheetName);
}
}
return sheetNames;
} #region 采用openxml方式把excel转换成dataTable /// <summary>
/// 构建DataTable的列
/// </summary>
/// <param name="row">OpenXML定义的Row对象</param>
/// <param name="stringTablePart"></param>
/// <param name="dt">须要返回的DataTable对象</param>
/// <returns></returns>
public void GetDataColumn(Row row, SharedStringTable stringTable, ref DataTable dt)
{
DataColumn col = new DataColumn();
Dictionary<string, int> columnCount = new Dictionary<string, int>();
foreach (Cell cell in row)
{
string cellVal = GetValue(cell, stringTable);
col = new DataColumn(cellVal);
if (IsContainsColumn(dt, col.ColumnName))
{
if (!columnCount.ContainsKey(col.ColumnName))
columnCount.Add(col.ColumnName, );
col.ColumnName = col.ColumnName + (columnCount[col.ColumnName]++);
}
dt.Columns.Add(col);
}
}
/// <summary>
/// 构建DataTable的每一行数据,并返回该Datatable
/// </summary>
/// <param name="row">OpenXML的行</param>
/// <param name="stringTablePart"></param>
/// <param name="dt">DataTable</param>
private void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt)
{
// 读取算法：按行一一读取单位格,若是整行均是空数据
DataRow dr = dt.NewRow();
int i = ;
int nullRowCount = i;
foreach (Cell cell in row)
{
string cellVal = GetValue(cell, stringTable);
if (cellVal == string.Empty)
{
nullRowCount++;
}
dr[i] = cellVal;
i++;
}
if (nullRowCount != i)
{
dt.Rows.Add(dr);
}
}
/// <summary>
/// 获取单位格的值
/// </summary>
/// <param name="cell"></param>
/// <param name="stringTablePart"></param>
/// <returns></returns>
private string GetValue(Cell cell, SharedStringTable stringTable)
{
//因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引
string value = string.Empty;
try
{
if (cell.ChildElements.Count == )
return value;
value = double.Parse(cell.CellValue.InnerText).ToString();
if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
{
value = stringTable.ChildElements[Int32.Parse(value)].InnerText;
}
}
catch (Exception)
{
value = "N/A";
}
return value;
}
/// <summary>
/// 判断网格是否存在列
/// </summary>
/// <param name="dt">网格</param>
/// <param name="columnName">列名</param>
/// <returns></returns>
public bool IsContainsColumn(DataTable dt, string columnName)
{
if (dt == null || columnName == null)
{
return false;
}
return dt.Columns.Contains(columnName);
} #endregion #region SaveCell
private void InsertTextCellValue(Worksheet worksheet, string column, uint row, string value)
{
Cell cell = ReturnCell(worksheet, column, row);
CellValue v = new CellValue();
v.Text = value;
cell.AppendChild(v);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
worksheet.Save();
}
private void InsertNumberCellValue(Worksheet worksheet, string column, uint row, string value)
{
Cell cell = ReturnCell(worksheet, column, row);
CellValue v = new CellValue();
v.Text = value;
cell.AppendChild(v);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
worksheet.Save();
}
private static Cell ReturnCell(Worksheet worksheet, string columnName, uint row)
{
Row targetRow = ReturnRow(worksheet, row); if (targetRow == null)
return null; return targetRow.Elements<Cell>().Where(c =>
string.Compare(c.CellReference.Value, columnName + row,
true) == ).First();
}
private static Row ReturnRow(Worksheet worksheet, uint row)
{
return worksheet.GetFirstChild<SheetData>().
Elements<Row>().Where(r => r.RowIndex == row).First();
}
#endregion
}
}
此代码示例使用 Open XML SDK 2.5 中的类来创建最小空白工作簿
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Spreadsheet
Module Module1 Sub Main()
CreateSpreadsheetWorkbook("D:\000.xlsx")
End Sub
Public Sub CreateSpreadsheetWorkbook(ByVal filepath As String)
' Create a spreadsheet document by supplying the filepath.
' By default, AutoSave = true, Editable = true, and Type = xlsx.
Dim spreadsheetDocument As SpreadsheetDocument = spreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook) ' Add a WorkbookPart to the document.
Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
workbookpart.Workbook = New Workbook ' Add a WorksheetPart to the WorkbookPart.
Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
worksheetPart.Worksheet = New Worksheet(New SheetData()) ' Add Sheets to the Workbook.
Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets()) ' Append a new worksheet and associate it with the workbook.
Dim sheet As Sheet = New Sheet
sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
sheet.SheetId =
sheet.Name = "mySheet"
sheets.Append(sheet)
workbookpart.Workbook.Save() ' Close the document.
spreadsheetDocument.Close()
End Sub End Module
重要电子表格部分
通过使用 Open XML SDK 2.5 for Office，可以使用 SpreadsheetML 元素所对应的强类型类创建文档结构和内容。可以在 DocumentFormat.OpenXML.Spreadsheet 命名空间中找到这些类。下表列出了一些重要电子表格元素所对应类的类名称。
|
包部分 |
顶级 SpreadsheetML 元素 |
Open XML SDK 2.5 类 |
说明 |
|---|---|---|---|
|
工作簿 |
workbook |
主文档部件的根元素。 |
|
|
工作表 |
worksheet |
表示包含文本、数字、日期或公式的单元格网格的工作表类型。有关详细信息，请参阅使用工作表。 |
|
|
图表工作表 |
chartsheet |
表示存储为自己的工作表的图表的工作表。有关详细信息，请参阅使用工作表。 |
|
|
表 |
table |
指定属于单个数据集的一系列数据的逻辑构造。有关详细信息，请参阅使用表格 (SpreadsheetML)。 |
|
|
数据透视表 |
pivotTableDefinition |
显示可理解布局中数据的聚合视图的逻辑构造。有关详细信息，请参阅使用数据透视表 (Open XML SDK)。 |
|
|
透视缓存 |
pivotCacheDefinition |
定义数据透视表中的数据源的构造。有关详细信息，请参阅使用数据透视表 (Open XML SDK)。 |
|
|
透视缓存记录 |
pivotCacheRecords |
数据透视表的源数据的缓存。有关详细信息，请参阅使用数据透视表 (Open XML SDK)。 |
|
|
计算链 |
calcChain |
指定上次计算工作簿中单元格的顺序的构造。有关详细信息，请参阅使用计算链 (Open XML SDK)。 |
|
|
共享字符串表 |
sst |
包含每个唯一字符串在工作簿中的所有工作表上的出现次数的构造。有关详细信息，请参阅使用共享字符串表 (Open XML SDK)。 |
|
|
条件格式 |
conditionalFormatting |
定义应用于一个单元格或一系列单元格的格式的构造。有关详细信息，请参阅使用条件格式设置 (Open XML SDK)。 |
|
|
公式 |
f |
定义包含公式的单元格的公式文本的构造。有关详细信息，请参阅使用公式 (Open XML SDK)。 |
最新文章
- php 搜索（查询）功能
- spring-data-redis注册fastjson序列化工具
- Beta版本冲刺———第三天
- 移除\禁用 jquery mobile 元素样式渲染
- oracle字符函数
- 配置hibernate
- 十个JavaScript中易犯的小错误，你中了几枪？
- Python 绝对简明手册
- 关于我的PP0.1聊天软件（客户端）
- Caused by: Unable to locate parent package [json-default] for [class com.you.user.action.StudentActi
- zabbix编译安装
- Linux 踩过的坑系列-01
- 重构现有代码：Refactoring
- 【数据结构】B-Tree, B+Tree, B*树介绍 转
- 常用 Linux 命令使用说明
- 【CF587F】Duff is Mad AC自动机+分块
- liunx进程管理之进程介绍
- 学习笔记之Data Science
- 【java规则引擎】《Drools7.0.0.Final规则引擎教程》第3章 3.2 KIE概念&FACT对象
- monkey测试入门1