欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-02 11:53:33
C# Net 使用 openxml 写入 对象 到 Excel 中 C# Net 使用openxml写入对象到Excel中 文尾看效果 效果一(模板文件写入集合对象) 效果二(新建文件写入集合对象) 加入包:OpenXml 创建文件:ExcelWrite.cs 复制下面全部代码到文件 ExcelWr ......

 

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. 准备模板文件

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

 

 

 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.效果

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

 

 

 

--------------------------------------------------------------------------------------------

-------------调用方式二(新建文件写入集合对象)----------------------------

--------------------------------------------------------------------------------------------

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.效果

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