C# 基于DocumentFormat.OpenXml的数据导出到Excel-白红宇

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);

}

本文转自94cool博客园博客，原文链接：http://www.cnblogs.com/94cool/p/7527292.html，如需转载请自行联系原作者