C ා net uses openxml to write objects to Excel

C ා net uses openxml to write objects to Excel

C ා net uses openxml to write objects to Excel

 

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

-------------------------The end of the article---------------------

----------Effect 1 (template file writes to collection object)------

----------Effect 2 (new file written to collection object)------

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

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

 

Add package: OpenXml

Create file: ExcelWrite.cs

Copy all the following code to the file 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>
    /// Write in Excel
    /// </summary>
    public class ExcelWrite
    {
        /// <summary>
        /// Write text
        /// </summary>
        /// <param name="path">file</param>
        /// <param name="objs">List<T>Object whose default value is the first line</param>
        /// <param name="sheetName">Add data to workbook name</param>
        /// <param name="goRow">Start line (from 1)</param>
        /// <param name="goCol">Start column (from 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;

                    //Get first sheet
                    Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
                    WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet);

                    //If SharedStringTablePart No create a new
                    SharedStringTablePart shareStringPart;
                    if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                        shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                    else
                        shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();

                    //If part does not contain SharedStringTable,Then create one.
                    if (shareStringPart.SharedStringTable == null)
                        shareStringPart.SharedStringTable = new SharedStringTable();

                    uint row = goRow;
                    int col = goCol;
                    List<string> paiChu = new List<string>();
                    //Header
                    foreach (object obj in objs as dynamic)
                    {
                        //Take the custom property on the class
                        bool isPaiChuClass = false;
                        var newType = obj.GetType();
                        var exc = (IEnumerable<ExcelColumnAttribute>)newType.GetCustomAttributes(typeof(ExcelColumnAttribute));
                        if (exc.Any() && !exc.First().IsShow)
                            isPaiChuClass = true;

                        //Take the custom attribute on the attribute
                        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;
                            }
                            //Adding default row is not allowed after excluding column of class
                            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;
                    }

                    //text
                    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++;
                    }

                    //Save new sheet
                    worksheetPart.Worksheet.Save();
                }
                else
                {
                    throw new Exception("Need to be a generic collection");
                }
            }
        }

        private static void NewMethod(uint row, int column, string text, SharedStringTablePart shareStringPart, WorksheetPart worksheetPart)
        {
            #region Insert text into SharedStringTablePart in

            int index = 0;

            //ergodic SharedStringTable All items in. If the text already exists, its index is returned.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                    break;
                index++;
            }

            //There is no text for this part. Establish SharedStringItem And return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));

            #endregion

            #region Cell A1 Insert sheet

            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();

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

            string cellReference = columnName + rowIndex;

            //Inserts a row if the worksheet does not contain a row with the specified row index
            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 there is no cell with the specified column name, insert one. 
            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
            {
                //Cells must be arranged in the order of cell reference. Determines where to insert new cells.
                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

            //set cell A1 Value
            newCell2.CellValue = new CellValue(index.ToString());
            newCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);
        }

        /// <summary>
        /// Write text
        /// </summary>
        /// <param name="path"></param>
        /// <param name="row">That's ok</param>
        /// <param name="column">column</param>
        /// <param name="text">text</param>
        /// <param name="sheetName">Job shop</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;

                //If SharedStringTablePart No create a new
                SharedStringTablePart shareStringPart;
                if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
                    shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
                else
                    shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();

                //Insert text into SharedStringTablePart in
                int index = InsertSharedStringItem(text, shareStringPart);

                //Get first sheet
                Sheet sheet = ExcelSeek.SeekSheet(workbookPart, sheetName);
                WorksheetPart worksheetPart = ExcelSeek.GetWorksheetPart(workbookPart, sheet);

                //Cell A1 Insert new sheet
                Cell cell = InsertCellInWorksheet(ExcelAlphabet.ColumnToABC(column), row, worksheetPart);

                //set cell A1 Value
                cell.CellValue = new CellValue(index.ToString());
                cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);

                //Save new sheet
                worksheetPart.Worksheet.Save();
            }
        }

        //Verify that the specified text exists in SharedStringTablePart Object and add text when it does not exist
        private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
        {
            //If part does not contain SharedStringTable,Then create one.
            if (shareStringPart.SharedStringTable == null)
                shareStringPart.SharedStringTable = new SharedStringTable();

            int i = 0;

            //ergodic SharedStringTable All items in. If the text already exists, its index is returned.
            foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
            {
                if (item.InnerText == text)
                    return i;

                i++;
            }

            //There is no text for this part. Establish SharedStringItem And return its index.
            shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
            shareStringPart.SharedStringTable.Save();

            return i;
        }

        /// <summary>
        /// Insert a new worksheet (such as Sheet2)
        /// </summary>
        /// <param name="workbookPart">workbook</param>
        /// <returns></returns>
        public static WorksheetPart InsertWorksheet(WorkbookPart workbookPart)
        {
            //Add a new sheet part to the workbook.
            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);

            //Get unique for new sheet 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;

            //Attach a new sheet and associate it with the workbook.
            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }

        // New Cell Objects inserting into Worksheet In object
        private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            Worksheet worksheet = worksheetPart.Worksheet;
            SheetData sheetData = worksheet.GetFirstChild<SheetData>();
            string cellReference = columnName + rowIndex;

            //Inserts a row if the worksheet does not contain a row with the specified row index
            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 there is no cell with the specified column name, insert one. 
            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
            {
                //Cells must be arranged in the order of cell reference. Determines where to insert new cells.
                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;
            }
        }

    }
}

 

 

Create file: ExcelSeek.cs

Copy all the following code to the file 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>
        ///Find sheet in Workbook
        /// </summary>
        public static Sheet SeekSheet(WorkbookPart workbookPart, string sheetName = "")
        {
            //Get all workbooks
            IEnumerable<Sheet> sheets = workbookPart.Workbook.Descendants<Sheet>();
            Sheet sheet = null;

            if (!sheets.Any())
                throw new ArgumentException("Empty Excel File");

            if (string.IsNullOrEmpty(sheetName))
                sheet = sheets.First();
            else
            {
                if (sheets.Count(o => o.Name == sheetName) <= 0)
                    throw new ArgumentException($"No workbooks found“{sheetName}"");
                sheet = sheets.First(o => o.Name == sheetName);
            }
            return sheet;
        }

        /// <summary>
        ///Get sheet from sheet
        /// </summary>
        ///< param name = "sheet" > worksheet < / param >
        ///< returns > working page < / returns >
        public static WorksheetPart GetWorksheetPart(WorkbookPart workbookPart, Sheet sheet)
        {
            return (WorksheetPart)workbookPart.GetPartById(sheet.Id);
        }

    }
}

  

Create file: ExcelCreate.cs

Copy all the following code to the file 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>
    ///Create Excel
    /// </summary>
    public class ExcelCreate
    {

        /// <summary>
        ///New blank Excel document
        /// </summary>
        /// <returns></returns>
        public static void NewCreate(string path)
        {
            //Create xlsx
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook);

            //Add workbook parts to the document
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            //Add sheet section to workbook section
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            //Add sheet to Workbook
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
                AppendChild<Sheets>(new Sheets());

            //Attach a new sheet and associate it with the workbook
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.
                GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Sheet1"
            };
            sheets.Append(sheet);

            workbookpart.Workbook.Save();
            spreadsheetDocument.Close();
        }

        /// <summary>
        ///New blank Excel document
        /// </summary>
        ///< returns > temporary files < / returns >
        public static string NewCreate()
        {
            var file = Path.ChangeExtension(Path.GetRandomFileName(), ".xlsx");

            NewCreate(file);

            //var memoryStream = new MemoryStream(File.ReadAllBytes(tempFileName));

            return file;
        }
    }
}

  

Create file: ExcelColumnAttribute.cs

Copy all the following code to the file ExcelColumnAttribute.cs

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;

namespace YCBX.Office.ExcelXml
{
    /// <summary>
    ///Excel column properties
    /// </summary>
    public class ExcelColumnAttribute : Attribute
    //: DescriptionAttribute
    {
        /// <summary>
        ///Suggested column name
        /// </summary>
        public virtual string ColumnName { get; }

        /// <summary>
        ///Show columns or not
        /// </summary>
        public virtual bool IsShow { get; }

        /// <summary>
        ///Initialize properties for Excel column names
        /// </summary>
        ///< param name = "isshow" > display column (if it is false on the class, the first row will not be resolved by default, and if it is false on the property, the value of the property will not be displayed) < / param >
        public ExcelColumnAttribute(bool isShow = true)
        {
            IsShow = isShow;
        }

        /// <summary>
        ///Initialize properties for Excel column names
        /// </summary>
        ///< param name = "description" > recommended column name (the header value of the first row in Excel on the attribute) < / param >
        ///< param name = "isshow" > display column (if it is false on the class, the first row will not be resolved by default, and if it is false on the property, the value of the property will not be displayed) < / param >
        public ExcelColumnAttribute(string description, bool isShow = true)
        {
            ColumnName = description;
            IsShow = isShow;
        }

    }
}

  

Create file: ExcelAlphabet.cs

Copy all the following code to the file ExcelAlphabet.cs

 

using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Text;

namespace YCBX.Office.ExcelXml
{
    /// <summary>
    ///Excel alphabet code help (26 base conversion)
    /// </summary>
    public class ExcelAlphabet
    {
        //Note A corresponds to char 65, Z corresponds to char 90

        /// <summary>
        ///26 letters
        /// </summary>
        public static uint AlphabetCount = 26;

        /// <summary>
        ///Digit to character
        /// </summary>
        /// <param name="iNumber"></param>
        /// <returns></returns>
        public static string ColumnToABC(int iNumber)
        {
            if (iNumber < 1 || iNumber > 702)
                throw new Exception("Conversion to 26 base can be done in 10 base range of 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>
        ///Character to number
        /// </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);
        }
    }
}

  

 

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

-------------Call mode 1 (template file writes to collection object)----------------------------

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

1. Prepare template file

 

 

2. Prepare the collection model

    [ExcelColumn(false)]
    public class StatisticalLearningModel
    {
        /// <summary>
        //Organization
        /// </summary>
        [ExcelColumn(false)]
        public string Organization { get; set; }
        /// <summary>
        ///Class number
        /// </summary>
        [ExcelColumn(false)]
        public string ClassId { get; set; }
        /// <summary>
        //Class
        /// </summary>
        public string Class { get; set; }
        /// <summary>
        //User id
        /// </summary>
        [ExcelColumn(false)]
        public string StuId { get; set; }
        /// <summary>
        /// name
        /// </summary>
        public string StuName { get; set; }
//Omit the following attributes: mobile phone, ID card, etc }

3. call

                List<StatisticalLearningModel> data = StudentDB.StatisticalLearning(dto).PageData;

                //Write to excel
                var path = Path.ChangeExtension(Path.GetRandomFileName(), ".xlsx");
                System.IO.File.Copy(@"OfficeFile\Student learning statistics template.xlsx", path, true);
                ExcelWrite.WriteObj(path, data, string.Empty, 3);

  

4. effect

 

 

 

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

-------------Call mode 2 (new file written to collection object)----------------------------

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

1. Prepare the collection model

    public class StudentListModel
    {
        /// <summary>
        //Organization
        /// </summary>
        [ExcelColumn(false)]
        public string Organization { get; set; }
        /// <summary>
        //Class
        /// </summary>
        [ExcelColumn("Class name")]
        public string Class { get; set; }
        /// <summary>
        //User id
        /// </summary>
        [ExcelColumn(false)]
        public string StuId { get; set; }
        /// <summary>
        /// name
        /// </summary>
        [ExcelColumn("Full name")]
        public string StuName { get; set; }
//The following properties such as ID card mobile phone are omitted }

  

2. call

                var data = StudentDB.StudentList(studentList).PageData;

                //Write to excel
                var path = ExcelCreate.NewCreate();
                ExcelWrite.WriteObj(path, data);

  

3. effect

Tags: C# Excel Attribute Mobile

Posted on Sat, 21 Mar 2020 07:53:24 -0700 by plutoplanet