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 = https://www.cnblogs.com/ping9719/p/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 = https://www.cnblogs.com/ping9719/p/worksheet.GetFirstChild();

            string columnName = ExcelAlphabet.ColumnToABC(column);
            uint rowIndex = row;

            string cellReference = columnName + rowIndex;

            //如果作業表不包含具有指定行索引的行，則插入一行
            Row rowobj;
            if (sheetData.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                rowobj = sheetData.Elements().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                rowobj = new Row() { RowIndex = rowIndex };
                sheetData.Append(rowobj);
            }

            Cell newCell2;
            //如果沒有具有指定列名的單元格，則插入一個， 
            if (rowobj.Elements().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                newCell2 = rowobj.Elements().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                //細胞必須按照細胞參考的順序排列，確定在何處插入新單元格，
                Cell refCell = null;
                foreach (Cell item in rowobj.Elements())
                {
                    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.SharedString);
        }

        /// 
        /// 寫入文本
        /// 
        /// (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())
            {
                if (item.InnerText == text)
                    return i;

                i++;
            }

            //這部分沒有正文，創建SharedStringItem并回傳它的索引，
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }

        /// 
        /// 插入一個新的作業表（如Sheet2）
        /// 
        /// ();
            string cellReference = columnName + rowIndex;

            //如果作業表不包含具有指定行索引的行，則插入一行
            Row row;
            if (sheetData.Elements().Where(r => r.RowIndex == rowIndex).Count() != 0)
            {
                row = sheetData.Elements().Where(r => r.RowIndex == rowIndex).First();
            }
            else
            {
                row = new Row() { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            //如果沒有具有指定列名的單元格，則插入一個， 
            if (row.Elements().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
            {
                return row.Elements().Where(c => c.CellReference.Value == cellReference).First();
            }
            else
            {
                //細胞必須按照細胞參考的順序排列，確定在何處插入新單元格，
                Cell refCell = null;
                foreach (Cell cell in row.Elements())
                {
                    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 = https://www.cnblogs.com/ping9719/p/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 = https://www.cnblogs.com/ping9719/p/StudentDB.StudentList(studentList).PageData;

                //寫入到excel
                var path = ExcelCreate.NewCreate();
                ExcelWrite.WriteObj(path, data);

3.效果

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/59609.html

標籤：C#

上一篇：C# IoC學習筆記

下一篇：C# 面向物件

C# Net 使用 openxml 寫入物件 到 Excel

C# Net 使用 openxml 寫入物件到 Excel