C# Net 使用 openxml 写入对象 到 Excel
C# Net 使用 openxml 写入 对象 到 Excel 中
C# Net 使用openxml写入对象到Excel中
------------------------------------------------------------
-------------------------文尾看效果---------------------
----------效果一（模板文件写入集合对象）------
----------效果二（新建文件写入集合对象）------
-------------------------------------------------------------
-------------------------------------------------------------
加入包：OpenXml
创建文件：ExcelWrite.cs
复制下面全部代码到文件 ExcelWrite.cs
using System;using System.Collections.Generic;using System.Text;using System.Linq;using DocumentFormat.OpenXml;using DocumentFormat.OpenXml.Packaging;using DocumentFormat.OpenXml.Spreadsheet;using System.IO;using System.Reflection;using System.ComponentModel;namespace YGNT.Office.ExcelXml{ /// <summary> /// 写入Excel /// </summary> public class ExcelWrite { /// <summary> /// 写入文本 /// </summary> /// <param name="path">文件</param> /// <param name="objs">List<T>对象，他的默认值为第一行</param> /// <param name="sheetName">把数据加入到工作薄的工作薄名</param> /// <param name="goRow">开始行（从1开始）</param> /// <param name="goCol">开始列（从1开始）</param> public static void WriteObj<T>(string path, List<T> objs, string sheetName = "", uint goRow = 1, int goCol = 1) where T : new() { using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true)) { var type = objs.GetType(); var enumer = type.GetInterface("IEnumerable", false); if (type.IsGenericType && enumer != null) { WorkbookPart workbookPart = spreadSheet.WorkbookPart; //获取第一个工作表 Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName); WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet); //如果SharedStringTablePart不存在创建一个新的 SharedStringTablePart shareStringPart; if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0) shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First(); else shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); //如果部分不包含SharedStringTable，则创建一个。 if (shareStringPart.SharedStringTable == null) shareStringPart.SharedStringTable = new SharedStringTable(); uint row = goRow; int col = goCol; List<string> paiChu = new List<string>(); T t = new T(); //表头 //取类上的自定义特性 bool isPaiChuClass = false; var newType = t.GetType(); var exc = (IEnumerable<ExcelColumnAttribute>)newType.GetCustomAttributes(typeof(ExcelColumnAttribute)); if (exc.Any() && !exc.First().IsShow) isPaiChuClass = true; //取属性上的自定义特性 foreach (var property in newType.GetRuntimeProperties()) { ExcelColumnAttribute att = null; var atts = (IEnumerable<ExcelColumnAttribute>)property.GetCustomAttributes(typeof(ExcelColumnAttribute)); if (atts.Any()) att = atts.First(); if (att != null && !att.IsShow) { paiChu.Add(property.Name); continue; } //排除了类的列后不允许添加默认行 else if (isPaiChuClass) continue; else if (att == null || string.IsNullOrEmpty(att.ColumnName)) NewMethod(row, col, property.Name, shareStringPart, worksheetPart); else NewMethod(row, col, att.ColumnName, shareStringPart, worksheetPart); col++; } if (!isPaiChuClass) row++; ////表头 //foreach (object obj in objs as dynamic) //{ // //取类上的自定义特性 // bool isPaiChuClass = false; // var newType = obj.GetType(); // var exc = (IEnumerable<ExcelColumnAttribute>)newType.GetCustomAttributes(typeof(ExcelColumnAttribute)); // if (exc.Any() && !exc.First().IsShow) // isPaiChuClass = true; // //取属性上的自定义特性 // foreach (var property in newType.GetRuntimeProperties()) // { // ExcelColumnAttribute att = null; // var atts = (IEnumerable<ExcelColumnAttribute>)property.GetCustomAttributes(typeof(ExcelColumnAttribute)); // if (atts.Any()) // att = atts.First(); // if (att != null && !att.IsShow) // { // paiChu.Add(property.Name); // continue; // } // //排除了类的列后不允许添加默认行 // else if (isPaiChuClass) // continue; // else if (att == null || string.IsNullOrEmpty(att.ColumnName)) // NewMethod(row, col, property.Name, shareStringPart, worksheetPart); // else // NewMethod(row, col, att.ColumnName, shareStringPart, worksheetPart); // col++; // } // if (!isPaiChuClass) // row++; // break; //} //正文 foreach (object obj in objs as dynamic) { col = goCol; foreach (var property in obj.GetType().GetRuntimeProperties()) { if (paiChu.Contains(property.Name)) continue; //var aaa = property.PropertyType.Name; var value = property.GetValue(obj)?.ToString() ?? ""; NewMethod(row, col, value, shareStringPart, worksheetPart); col++; } row++; } //保存新工作表 worksheetPart.Worksheet.Save(); } else { throw new Exception("需要是一个泛型集合"); } } } private static void NewMethod(uint row, int column, string text, SharedStringTablePart shareStringPart, WorksheetPart worksheetPart) { #region 将文本插入到SharedStringTablePart中 int index = 0; //遍历SharedStringTable中的所有项。如果文本已经存在，则返回其索引。 foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>()) { if (item.InnerText == text) break; index++; } //这部分没有正文。创建SharedStringItem并返回它的索引。 shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text))); #endregion #region 将单元格A1插入工作表 Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild<SheetData>(); string columnName = ExcelAlphabet.ColumnToABC(column); uint rowIndex = row; string cellReference = columnName + rowIndex; //如果工作表不包含具有指定行索引的行，则插入一行 Row rowobj; if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { rowobj = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First(); } else { rowobj = new Row() { RowIndex = rowIndex }; sheetData.Append(rowobj); } Cell newCell2; //如果没有具有指定列名的单元格，则插入一个。 if (rowobj.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { newCell2 = rowobj.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First(); } else { //细胞必须按照细胞参考的顺序排列。确定在何处插入新单元格。 Cell refCell = null; foreach (Cell item in rowobj.Elements<Cell>()) { if (item.CellReference.Value.Length == cellReference.Length) { if (string.Compare(item.CellReference.Value, cellReference, true) > 0) { refCell = item; break; } } } Cell newCell = new Cell() { CellReference = cellReference }; rowobj.InsertBefore(newCell, refCell); newCell2 = newCell; } #endregion //设置单元格A1的值 newCell2.CellValue = new CellValue(index.ToString()); newCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString); } /// <summary> /// 写入文本 /// </summary> /// <param name="path"></param> /// <param name="row">行</param> /// <param name="column">列</param> /// <param name="text">文本</param> /// <param name="sheetName">工作薄</param> public static void WriteText(string path, uint row, int column, string text, string sheetName = "") { using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(path, true)) { WorkbookPart workbookPart = spreadSheet.WorkbookPart; //如果SharedStringTablePart不存在创建一个新的 SharedStringTablePart shareStringPart; if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0) shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First(); else shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>(); //将文本插入到SharedStringTablePart中 int index = InsertSharedStringItem(text, shareStringPart); //获取第一个工作表 Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName); WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet); //将单元格A1插入新工作表 Cell cell = InsertCellInWorksheet(ExcelAlphabet.ColumnToABC(column), row, worksheetPart); //设置单元格A1的值 cell.CellValue = new CellValue(index.ToString()); cell.DataType = new EnumValue<CellValues>(CellValues.SharedString); //保存新工作表 worksheetPart.Worksheet.Save(); } } //验证指定的文本是否存在于 SharedStringTablePart 对象中，并在不存在时添加文本 private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart) { //如果部分不包含SharedStringTable，则创建一个。 if (shareStringPart.SharedStringTable == null) shareStringPart.SharedStringTable = new SharedStringTable(); int i = 0; //遍历SharedStringTable中的所有项。如果文本已经存在，则返回其索引。 foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>()) { if (item.InnerText == text) return i; i++; } //这部分没有正文。创建SharedStringItem并返回它的索引。 shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text))); shareStringPart.SharedStringTable.Save(); return i; } /// <summary> /// 插入一个新的工作表（如Sheet2） /// </summary> /// <param name="workbookPart">工作簿</param> /// <returns></returns> public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart) { //向工作簿添加新工作表部件。 WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>(); newWorksheetPart.Worksheet = new Worksheet(new SheetData()); newWorksheetPart.Worksheet.Save(); Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>(); string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart); //为新工作表获取唯一的ID uint sheetId = 1; if (sheets.Elements<Sheet>().Count() > 0) { sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1; } string sheetName = "Sheet" + sheetId; //附加新工作表并将其与工作簿关联。 Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); return newWorksheetPart; } // 将新的 Cell 对象插入到 Worksheet 对象中 private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart) { Worksheet worksheet = worksheetPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild<SheetData>(); string cellReference = columnName + rowIndex; //如果工作表不包含具有指定行索引的行，则插入一行 Row row; if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) { row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First(); } else { row = new Row() { RowIndex = rowIndex }; sheetData.Append(row); } //如果没有具有指定列名的单元格，则插入一个。 if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) { return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First(); } else { //细胞必须按照细胞参考的顺序排列。确定在何处插入新单元格。 Cell refCell = null; foreach (Cell cell in row.Elements<Cell>()) { if (cell.CellReference.Value.Length == cellReference.Length) { 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; } } }}
创建文件：ExcelSeek.cs
复制下面全部代码到文件 ExcelSeek.cs
using DocumentFormat.OpenXml.Packaging;using DocumentFormat.OpenXml.Spreadsheet;using System;using System.Collections.Generic;using System.Linq;using System.Text;namespace YCBX.Office.ExcelXml{ public class ExcelSeek { /// <summary> /// 在工作薄中查找工作表 /// </summary> public static Sheet SeekSheet(WorkbookPart workbookPart, string sheetName = "") { //获取所有工作薄 IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>(); Sheet sheet = null; if (!sheets.Any()) throw new ArgumentException("空的Excel文档"); if (string.IsNullOrEmpty(sheetName)) sheet = sheets.First(); else { if (sheets.Count(o => o.Name == sheetName) <= 0) throw new ArgumentException($"没有找到工作薄“{sheetName}”"); sheet = sheets.First(o => o.Name == sheetName); } return sheet; } /// <summary> /// 根据工作表获取工作页 /// </summary> /// <param name="sheet">工作表</param> /// <returns>工作页</returns> public static WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, Sheet sheet) { return (WorksheetPart)workbookPart.GetPartById(sheet.Id); } }}
创建文件：ExcelCreate.cs
复制下面全部代码到文件 ExcelCreate.cs
using System;using System.Collections.Generic;using System.IO;using System.Text;using DocumentFormat.OpenXml;using DocumentFormat.OpenXml.Packaging;using DocumentFormat.OpenXml.Spreadsheet;namespace YCBX.Office.ExcelXml{ /// <summary> /// 创建Excel /// </summary> public class ExcelCreate { /// <summary> /// 新的空白Excel文档 /// </summary> /// <returns></returns> public static void NewCreate(string path) { //创建 xlsx SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook); //将工作簿部件添加到文档中 WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); //将工作表部分添加到工作簿部分 WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); //将工作表添加到工作簿 Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild<Sheets>(new Sheets()); //附加新工作表并将其与工作簿关联 Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart. GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); workbookpart.Workbook.Save(); spreadsheetDocument.Close(); } /// <summary> /// 新的空白Excel文档 /// </summary> /// <returns>临时的文件</returns> public static string NewCreate() { var file = Path.ChangeExtension(Path.GetRandomFileName(), ".xlsx"); NewCreate(file); //var memoryStream = new MemoryStream(File.ReadAllBytes(tempFileName)); return file; } }}
创建文件：ExcelColumnAttribute.cs
复制下面全部代码到文件 ExcelColumnAttribute.cs
using System;using System.Collections.Generic;using System.ComponentModel;using System.Text;namespace YCBX.Office.ExcelXml{ /// <summary> /// Excel列特性 /// </summary> public class ExcelColumnAttribute : Attribute //: DescriptionAttribute { /// <summary> /// 建议列名 /// </summary> public virtual string ColumnName { get; } /// <summary> /// 是否显示列 /// </summary> public virtual bool IsShow { get; } /// <summary> /// 初始化Excel列名的特性 /// </summary> /// <param name="isShow">是否显示列（在类上为false时不解析默认第一行，在属性上为false时不显示属性的值）</param> public ExcelColumnAttribute(bool isShow = true) { IsShow = isShow; } /// <summary> /// 初始化Excel列名的特性 /// </summary> /// <param name="description">建议列名（在属性上为Excel中的第一行的头值）</param> /// <param name="isShow">是否显示列（在类上为false时不解析默认第一行，在属性上为false时不显示属性的值）</param> public ExcelColumnAttribute(string description, bool isShow = true) { ColumnName = description; IsShow = isShow; } }}
创建文件：ExcelAlphabet.cs
复制下面全部代码到文件 ExcelAlphabet.cs
using DocumentFormat.OpenXml.Spreadsheet;using System;using System.Collections.Generic;using System.Text;namespace YCBX.Office.ExcelXml{ /// <summary> /// Excel字母码帮助（26进制转换） /// </summary> public class ExcelAlphabet { //备注 A 对应char为65，Z 对应char为90 /// <summary> /// 26个字母 /// </summary> public static uint AlphabetCount = 26; /// <summary> /// 数字转字符 /// </summary> /// <param name="iNumber"></param> /// <returns></returns> public static string ColumnToABC(int iNumber) { if (iNumber < 1 || iNumber > 702) throw new Exception("转为26进制可用10进制范围为1-702"); string sLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; int iUnits = 26; int iDivisor = (int)(iNumber / iUnits); int iResidue = iNumber % iUnits; if (iDivisor == 1 && iResidue == 0) { iDivisor = 0; iResidue = iResidue + iUnits; } else { if (iResidue == 0) { iDivisor -= 1; iResidue += iUnits; } } if (iDivisor == 0) { return sLetters.Substring(iResidue - 1, 1); } else { return sLetters.Substring(iDivisor - 1, 1) + sLetters.Substring(iResidue - 1, 1); } } /// <summary> /// 字符转数字 /// </summary> /// <param name="sString"></param> /// <returns></returns> public static int ABCToColumn(string sString) { if (string.Compare(sString, "A") == -1 || string.Compare(sString, "ZZ") == 1) return 0; string sLetters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; int iUnits = 26; int sFirst = -1; int sSecond = 0; if (sString.Length == 1) { sSecond = sLetters.IndexOf(sString); } else { sFirst = sLetters.IndexOf(sString.Substring(0, 1)); sSecond = sLetters.IndexOf(sString.Substring(1, 1)); } return (sFirst + 1) * iUnits + (sSecond + 1); } }}
--------------------------------------------------------------------------------------------
-------------调用方式一（模板文件写入集合对象）----------------------------
--------------------------------------------------------------------------------------------
1. 准备模板文件
2.准备集合model
[ExcelColumn(false)] public class StatisticalLearningModel { /// <summary> /// 机构 /// </summary> [ExcelColumn(false)] public string Organization { get; set; } /// <summary> /// 班级编号 /// </summary> [ExcelColumn(false)] public string ClassId { get; set; } /// <summary> /// 班级 /// </summary> public string Class { get; set; } /// <summary> /// 用户id /// </summary> [ExcelColumn(false)] public string StuId { get; set; } /// <summary> /// 姓名 /// </summary> public string StuName { get; set; } //以下省略手机，身份证等属性.... }
3.调用
List<StatisticalLearningModel> data = StudentDB.StatisticalLearning(dto).PageData; //写入到excel var path = Path.ChangeExtension(Path.GetRandomFileName(), ".xlsx"); System.IO.File.Copy(@"OfficeFile\学员学习统计模板.xlsx", path, true); ExcelWrite.WriteObj(path, data, string.Empty, 3);
4.效果
--------------------------------------------------------------------------------------------
-------------调用方式二（新建文件写入集合对象）----------------------------
--------------------------------------------------------------------------------------------
1.准备集合model
public class StudentListModel { /// <summary> /// 机构 /// </summary> [ExcelColumn(false)] public string Organization { get; set; } /// <summary> /// 班级 /// </summary> [ExcelColumn("班级名")] public string Class { get; set; } /// <summary> /// 用户id /// </summary> [ExcelColumn(false)] public string StuId { get; set; } /// <summary> /// 姓名 /// </summary> [ExcelColumn("姓名")] public string StuName { get; set; } //以下省略身份证手机等属性.... }
2.调用
var data = StudentDB.StudentList(studentList).PageData; //写入到excel var path = ExcelCreate.NewCreate(); ExcelWrite.WriteObj(path, data);
3.效果
赞 (0)