using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
/// <summary>
/// 导出Excel
/// </summary>
public class ExcelExport : IDisposable
{
int rowCount = 0;
string sheetName = "sheet1";
SpreadsheetDocument xl;
OpenXmlWriter oxw;
WorksheetPart wsp;
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="path">Excel文件名称(全路径)</param>
/// <param name="rowCount">表格列数量</param>
/// <param name="sheetName">表格名称</param>
public ExcelExport(string path, int rowCount, string sheetName = "sheet1")
{
this.rowCount = rowCount;
this.sheetName = sheetName ?? "sheet1";
xl = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook);
xl.AddWorkbookPart();
wsp = xl.WorkbookPart.AddNewPart<WorksheetPart>();
oxw = OpenXmlWriter.Create(wsp);
oxw.WriteStartElement(new Worksheet());
oxw.WriteStartElement(new SheetData());
}
/// <summary>
/// 写入表格数据
/// </summary>
/// <param name="datas"></param>
public void Write(object[] datas)
{
if (datas == null || datas.Length == 0) return;
int colNum = datas.Length;
//oxa = new List<OpenXmlAttribute>();
// this is the row index
//oxa.Add(new OpenXmlAttribute("r", null, i.ToString()));
//oxw.WriteStartElement(new Row(), oxa);
oxw.WriteStartElement(new Row());
for (int j = 0; j < colNum; ++j)
{
var oxa = new List<OpenXmlAttribute>();
// this is the data type ("t"), with CellValues.String ("str")
oxa.Add(new OpenXmlAttribute("t", null, "str"));
// it's suggested you also have the cell reference, but
// you'll have to calculate the correct cell reference yourself.
// Here's an example:
//oxa.Add(new OpenXmlAttribute("r", null, "A1"));
//c.Append(f);
oxw.WriteStartElement(new Cell() { DataType = CellValues.InlineString }, oxa);
//oxw.WriteStartElement(new Cell());
oxw.WriteElement(new CellValue($"{datas[j]}"));
// this is for Cell
oxw.WriteEndElement();
}
// this is for Row
oxw.WriteEndElement();
}
/// <summary>
/// 写入表格数据
/// </summary>
/// <param name="datas"></param>
public void Write(List<object> datas)
{
if (datas == null || datas.Count == 0) return;
Write(datas.ToArray());
}
void Close()
{
// this is for SheetData
oxw.WriteEndElement();
// this is for Worksheet
oxw.WriteEndElement();
oxw.Close();
oxw = OpenXmlWriter.Create(xl.WorkbookPart);
oxw.WriteStartElement(new Workbook());
oxw.WriteStartElement(new Sheets());
// you can use object initialisers like this only when the properties
// are actual properties. SDK classes sometimes have property-like properties
// but are actually classes. For example, the Cell class has the CellValue
// "property" but is actually a child class internally.
// If the properties correspond to actual XML attributes, then you're fine.
oxw.WriteElement(new Sheet()
{
Name = sheetName,
SheetId = 1,
Id = xl.WorkbookPart.GetIdOfPart(wsp)
});
// this is for Sheets
oxw.WriteEndElement();
// this is for Workbook
oxw.WriteEndElement();
oxw.Close();
xl.Close();
}
#region IDisposable Support
private bool disposedValue = false; // 要检测冗余调用
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// TODO: 释放托管状态(托管对象)。
Close();
}
// TODO: 释放未托管的资源(未托管的对象)并在以下内容中替代终结器。
// TODO: 将大型字段设置为 null。
disposedValue = true;
}
}
// TODO: 仅当以上 Dispose(bool disposing) 拥有用于释放未托管资源的代码时才替代终结器。
// ~OpenXmlExt() {
// // 请勿更改此代码。将清理代码放入以上 Dispose(bool disposing) 中。
// Dispose(false);
// }
// 添加此代码以正确实现可处置模式。
void IDisposable.Dispose()
{
// 请勿更改此代码。将清理代码放入以上 Dispose(bool disposing) 中。
Dispose(true);
// TODO: 如果在以上内容中替代了终结器，则取消注释以下行。
// GC.SuppressFinalize(this);
}
#endregion
}
使用：
object[] objTitle = new object[] { "SIM", "ICCID"};
string descFile="d:\test.xlsx";
using (var oxExt = new ExcelUtil.ExcelExport(descFile, objTitle.Length, "清单"))
{
oxExt.Write(objTitle);
}