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

    }
}

 

 

创建文件：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.效果