C# Net 使用 openxml 写入 对象 到 Excel 中

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 ycbx.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(string path, object objs, string sheetname = "", uint gorow = 1, int gocol = 1)
        {
            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>();
                    //表头
                    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;
            }
        }

    }
}