Open Xml SDK (Gerar excel) .net c#

alfinete · 24 de Outubro de 2023

Bom dia

estou a gerar um excel em .net atraves do Open Xml Sdk através do Open-XML-SDK NugetPackage

quanto á geração do mesmo está tdo a funcionar Ok

ma agora quero alterar o tamanho das colunas dinamticamente e as cores do header das mesmas, e não estou a conseguir

isto tudo através da métodp que devolve stylesheet

Agradecia uma ajuda de vossa parte

Aguardo

Class ExcelFields
_____________________________________________________________________________________________________________

Código:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace openxmlexcel.classes
{
    public class ExcelFields
    {
        public string ColumnName {get;set;}
        public string ColumnProperty { get; set; }
        public string   ColumnWidth { get; set; }
        public string ColumnBckcolor { get; set; }
        public int Order { get; set; }
    }
}

Class Person
_____________________________________________________________________________________________________________

Código:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace openxmlexcel.classes
{
    public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Age { get; set; }
    }
}

file.aspx.cs
________________________________________________

Código:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using openxmlexcel.classes;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace openxmlexcel
{
    public partial class Teste : System.Web.UI.Page
    {
        private DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet1;
        //public enum exportTypes {
        //    tranches
         
        //}
 
        protected void Page_Load(object sender, EventArgs e)
        {
            CreateExcel("FIleTeste", "FIleTestesh", GetPersonsList(),GetListCOlumns());

           // string indexname = translateColumnIndexToName(0);
        }


        private Stylesheet GetStylesheet()
        {

            var stylesheet = new Stylesheet();

            // Default Font
            var fonts = new Fonts() { Count = 1, KnownFonts = BooleanValue.FromBoolean(true) };
            var font = new Font
            {
                FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 },
                FontName = new FontName() { Val = "Calibri" },
                FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
                FontScheme = new FontScheme() { Val = new EnumValue<FontSchemeValues>(FontSchemeValues.Minor) }
            };
            fonts.Append(font);
            stylesheet.Append(fonts);

            // Default Fill
            var fills = new Fills() { Count = 1 };
            var fill = new Fill();
            fill.PatternFill = new PatternFill() { ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("255,0,0") }, PatternType = PatternValues.Solid };
            fills.Append(fill);
            stylesheet.Append(fills);

            // Default Border
            var borders = new Borders() { Count = 1 };
            var border = new Border
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder(),
                TopBorder = new TopBorder(),
                BottomBorder = new BottomBorder(),
                DiagonalBorder = new DiagonalBorder()
            };
            borders.Append(border);
            stylesheet.Append(borders);

       
            return stylesheet;

        }

        public void CreateExcel(string FileName, string SheetName, dynamic ListData, List<ExcelFields> excelFields)
        {
            string directory = Path.Combine(@"c:\Uploads");

            string fileName = Path.Combine(directory, string.Format(FileName + "{0:yyyy-MM-dd HH.mm.ss}.xlsx", DateTime.Now));

      

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
             Create(fileName, SpreadsheetDocumentType.Workbook))
            {


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

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());



                WorkbookStylesPart stylePart = workbookpart.AddNewPart
                       <WorkbookStylesPart>();
                stylePart.Stylesheet = GetStylesheet();
                stylePart.Stylesheet.Save();


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

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

                //add the sheet to the workbook sheet aray
                sheets.Append(sheet);
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

             
                UInt32 rowIndex = 1;
                Int32 ColIndex = 1;
                string AlphaColumn = string.Empty;
           
                #region Header

                var row = new Row() { RowIndex = rowIndex };
                    //Columns Lstcolumns = new Columns() ;
               
                    foreach (var Col in excelFields)
                    {


                    //Descrição da coluna de excel referente ao index da mesma
                    AlphaColumn = translateColumnIndexToName(ColIndex - 1);
                    var MyCell = new Cell() { CellReference = AlphaColumn + (rowIndex),StyleIndex = (UInt32Value)0 };
                    MyCell.CellValue = new CellValue(Col.ColumnName);
                    MyCell.DataType = CellValues.String;
                 

                    row.AppendChild(MyCell);

             

                    ColIndex++;
                    }

                //worksheetPart.Worksheet.Append(Lstcolumns);
            
                sheetData.AppendChild(row);

                    rowIndex++;

                #endregion


                /// a sequencia de Criação das propriedades do objecto referente á lista tem de estar pela mesma oudem  dos headers da tabela
                ///
                #region Dados

          
                rowIndex = 2;

                //Por Cada Item da lista Uma Nova Linha
                for (int i = 0; i < ListData.Count; i++)
                {

                    row = new Row() { RowIndex = rowIndex };
                    // Lista de propertyInfo do Nosso Objecto
                    // List<System.Reflection.PropertyInfo> lstPrpByObj = ListData[i].GetType().GetProperties().ToList();

                    List<ExcelFields> lstPrpByObj = excelFields.OrderBy(p => p.Order).ToList();

                    Int32 ColumnValueIndex = 0;
                    //Por cada propriedade do objecto uma Coluna
                    foreach (var prop in lstPrpByObj)
                    {
                        // Trazemos a Propriedade por Nopme referente ao Nosso Objecto
                        PropertyInfo Prop = ListData[i].GetType().GetProperty(prop.ColumnProperty);
                        // Trazemos os Valor da propriedade
                        string PropValue = Prop.GetValue(ListData[i], null).ToString();

                        // Designação da COluna no excel A,B,C,D
                        AlphaColumn = translateColumnIndexToName(ColumnValueIndex);
                        string RowColumn = AlphaColumn + rowIndex;
                        var Cell = new Cell() { CellReference = RowColumn };
                        Cell.CellValue = new CellValue(PropValue);
                        Cell.DataType = CellValues.String;
                        row.AppendChild(Cell);
                        ColumnValueIndex++;
                    }



                    sheetData.AppendChild(row);
                    rowIndex++;

            
                }




                #endregion




 

                //}

                //foreach (var person in ListData)
                //    {
                //        row = new Row() { RowIndex = rowIndex };

                //        var firstNameCell = new Cell() { CellReference = "A" + (rowIndex) };
                //        firstNameCell.CellValue = new CellValue(person.FirstName);
                //        firstNameCell.DataType = CellValues.String;

                //        row.AppendChild(firstNameCell);

                //        Cell lastNameCell = new Cell() { CellReference = "B" + (rowIndex) };
                //        lastNameCell.CellValue = new CellValue(person.LastName);
                //        lastNameCell.DataType = new EnumValue<CellValues>(CellValues.String);

                //        row.AppendChild(lastNameCell);

                //        Cell ageCell = new Cell() { CellReference = "C" + (rowIndex) };
                //        ageCell.CellValue = new CellValue(person.Age.ToString());
                //        ageCell.DataType = new EnumValue<CellValues>(CellValues.String);

                //        row.AppendChild(ageCell);

                //        sheetData.AppendChild(row);

                //        rowIndex++;
                //    }

                workbookpart.Workbook.Save();
            }
        }


       /// <summary>
       /// Devolve a designação de uma coluna excel baseado num index da mesma
       /// 0-A,1-B........
       /// </summary>
       /// <param name="index"></param>
       /// <returns></returns>
        public static String translateColumnIndexToName(int index)
        {
            //assert (index >= 0);

            int quotient = (index) / 26;

            if (quotient > 0)
            {
                return translateColumnIndexToName(quotient - 1) + (char)((index % 26) + 65);
            }
            else
            {
                return "" + (char)((index % 26) + 65);
            }


        }

        public List<Person> GetPersonsList()
        {

            List<Person> persons = new List<Person>()
            {
                new Person() {FirstName="Brecht", LastName="Baekelandt 1", Age="29"},
                new Person() {FirstName="Pieter", LastName="Baekelandt 2", Age="28"},
                new Person() {FirstName="Leonie", LastName="Baekelandt 3", Age="21"}
           };

            return persons;

        }



        public List<ExcelFields> GetListCOlumns()
        {

            List<ExcelFields> excelfields = new List<ExcelFields>()
            {
                new ExcelFields() {ColumnName="Nome", ColumnProperty="FirstName", ColumnWidth="100", Order=1},
                new ExcelFields() {ColumnName="Sobrenome",ColumnProperty="LastName", ColumnWidth="200", Order =2},
                new ExcelFields() {ColumnName="Idade", ColumnProperty="Age",ColumnWidth="100", Order = 3}
           };

            return excelfields;

        }


    }
}

alfinete · 24 de Outubro de 2023

boa tarde entretanto consegui resolver esta questão
obrigada na mesma a todas as visualizares
assim envio o code

Código base
_______________________________________________________________________________________________________________

Código:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using openxmlexcel.classes;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using FontSize = DocumentFormat.OpenXml.Spreadsheet.FontSize;

namespace openxmlexcel
{
    public partial class Teste2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)

        {

            ImportExcelFile InfoFile = GetInfoFile();

          


            CreateExcel( GetPersonsList(), GetListCOlumns(),InfoFile);

            // string indexname = translateColumnIndexToName(0);
        }

        private Stylesheet GenerateStylesheet(ImportExcelFile FileInfo)
        {
            Stylesheet styleSheet = null;

            Fonts fonts = new Fonts(
                new Font( // Index 0 - default
                    new FontSize() { Val = FileInfo.FontSizeRows },
                     new Color() { Rgb = FileInfo.FontColorRows },
                     new FontName() { Val = FileInfo.FontTypeRows }
                ),
                new Font( // Index 1 - header
                    new FontSize() { Val = FileInfo.FontSizeHeader },
                    new Bold(),
                    new Color() { Rgb = FileInfo.FontColorHeader },
                    new FontName() { Val = FileInfo.FontTypeHeader }
                ));

            Fills fills = new Fills(
                    new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
                   new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
                   //new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "00bfff" } })), // Index 1 - default
                  // cor headers
                  new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = FileInfo.BackGroundHeaderColor } })
                       { PatternType = PatternValues.Solid }) // Index 2 - header
                );

            Borders borders = new Borders(
                    new Border(), // index 0 default
                    new Border( // index 1 black border
                        new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new DiagonalBorder())
                );

            CellFormats cellFormats = new CellFormats(
                    new CellFormat(), // default
                    new CellFormat { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // body
                    new CellFormat { FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true } // header
                );

            styleSheet = new Stylesheet(fonts, fills, borders, cellFormats);

            return styleSheet;
        }
        

        public void CreateExcel( dynamic ListData, List<ExcelFields> excelFields, ImportExcelFile InfoFile)
        {
            //string directory = Server.MapPath(@"\Uploads\");// Path.Combine(@"f:\Alfredo2\Testes\openxmlexcel\openxmlexcel\Uploads");

            //string fileName = Path.Combine(directory, string.Format(FileName + "{0:yyyy-MM-dd HH.mm.ss}.xlsx", DateTime.Now));
            string directory = Server.MapPath(@"\Uploads\");// Path.Combine(@"f:\Alfredo2\Testes\openxmlexcel\openxmlexcel\Uploads");

            string fileName = Path.Combine(directory, string.Format(InfoFile.FileName,"1", DateTime.Now));

            string SheetName = InfoFile.SheetName;

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
             Create(fileName, SpreadsheetDocumentType.Workbook))
            {


                // Add a WorkbookPart to the document.
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                // Add a WorksheetPart to the WorkbookPart.
                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();



                WorkbookStylesPart stylePart = workbookPart.AddNewPart
                       <WorkbookStylesPart>();
                stylePart.Stylesheet = GenerateStylesheet(InfoFile);
                stylePart.Stylesheet.Save();

                //Criação das colunas 
                //*******************************************************************************
                Columns columns = new Columns();
                UInt32 max = 1;
                UInt32 Maxfields = UInt32.Parse(excelFields.Count().ToString());

                foreach (var Col in excelFields)
                {
                

                    Column column = new Column
                    {
                        // coluna Actual
                        Min = Col.Order ,
                        // coluna seguinte em todas excepto a primeira                  
                        Max = Col.Order ==  1  ? Col.Order 
                                              :  Col.Order +1,  
                        Width = double.Parse(Col.ColumnWidth),
                        CustomWidth = true,
                        BestFit = true
                    };

                    columns.Append(column);
                }

                worksheetPart.Worksheet.AppendChild(columns);

                // Add Sheets to the Workbook.
                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
               

                // Append a new worksheet and associate it with the workbook.
                Sheet sheet = new Sheet()
                {
                    Id = workbookPart.GetIdOfPart(worksheetPart),
                    SheetId = 1,
                    Name = SheetName
                };

                //add the sheet to the workbook sheet aray
                sheets.Append(sheet);
                workbookPart.Workbook.Save();

                SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());


                UInt32 rowIndex = 1;
                Int32 ColIndex = 1;
                string AlphaColumn = string.Empty;
              
                #region Header

                var row = new Row() { RowIndex = rowIndex };
                //Columns Lstcolumns = new Columns() ;

                foreach (var Col in excelFields)
                {

                    //Descrição da coluna de excel referente ao index da mesma
                    AlphaColumn = translateColumnIndexToName(ColIndex - 1);
                    var MyCell = new Cell() { CellReference = AlphaColumn + (rowIndex), StyleIndex = 2 };
                    MyCell.CellValue = new CellValue(Col.ColumnName);
                    MyCell.DataType = CellValues.String;
                    
                    row.AppendChild(MyCell);
                                       

                    ColIndex++;
                }

                //worksheetPart.Worksheet.Append(Lstcolumns);

                sheetData.AppendChild(row);

                rowIndex++;

                #endregion


                /// a sequencia de Criação das propriedades do objecto referente á lista tem de estar pela mesma oudem  dos headers da tabela
                /// 
                #region Dados

                
                rowIndex = 2;

                //Por Cada Item da lista Uma Nova Linha
                for (int i = 0; i < ListData.Count; i++)
                {

                    row = new Row() { RowIndex = rowIndex };
                    // Lista de propertyInfo do Nosso Objecto
                    // List<System.Reflection.PropertyInfo> lstPrpByObj = ListData[i].GetType().GetProperties().ToList();

                    List<ExcelFields> lstPrpByObj = excelFields.OrderBy(p => p.Order).ToList();

                    Int32 ColumnValueIndex = 0;
                    //Por cada propriedade do objecto uma Coluna
                    foreach (var prop in lstPrpByObj)
                    {
                        // Trazemos a Propriedade por Nopme referente ao Nosso Objecto 
                        PropertyInfo Prop = ListData[i].GetType().GetProperty(prop.ColumnProperty);
                        // Trazemos os Valor da propriedade
                        string PropValue = Prop.GetValue(ListData[i], null).ToString();

                        // Designação da COluna no excel A,B,C,D 
                        AlphaColumn = translateColumnIndexToName(ColumnValueIndex);
                        string RowColumn = AlphaColumn + rowIndex;
                        var Cell = new Cell() { CellReference = RowColumn, StyleIndex = 1 };
                        Cell.CellValue = new CellValue(PropValue);
                        Cell.DataType = CellValues.String;
                        row.AppendChild(Cell);
                        ColumnValueIndex++;
                    }



                    sheetData.AppendChild(row);
                    rowIndex++;


                }




                #endregion






             
                workbookPart.Workbook.Save();
            }
        }


        /// <summary>
        /// Devolve a designação de uma coluna excel baseado num index da mesma
        /// 0-A,1-B........
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public static String translateColumnIndexToName(int index)
        {
            //assert (index >= 0);

            int quotient = (index) / 26;

            if (quotient > 0)
            {
                return translateColumnIndexToName(quotient - 1) + (char)((index % 26) + 65);
            }
            else
            {
                return "" + (char)((index % 26) + 65);
            }


        }

        public List<Person> GetPersonsList()
        {

            List<Person> persons = new List<Person>()
            {
                new Person() {FirstName="Brecht", LastName="Baekelandt 1", Age="29",Localidade="Freixo de Espada a cinta",Cpostal="2870-120"},
                new Person() {FirstName="Pieter", LastName="Baekelandt 2", Age="28",Localidade="portalegre",Cpostal="2875-126"},
                new Person() {FirstName="Leonie", LastName="Baekelandt 3", Age="21",Localidade="castelo de Vide",Cpostal="3875-426"}
                
           };

            return persons;

        }



        public List<ExcelFields> GetListCOlumns()
        {

            List<ExcelFields> excelfields = new List<ExcelFields>()
            {
                new ExcelFields() {ColumnName="Nome", ColumnProperty="FirstName", ColumnWidth="50", Order=1},
                new ExcelFields() {ColumnName="Sobrenome",ColumnProperty="LastName", ColumnWidth="30", Order =2},
                new ExcelFields() {ColumnName="Idade", ColumnProperty="Age",ColumnWidth="10", Order = 3},
                new ExcelFields() {ColumnName="Localidade", ColumnProperty="Localidade",ColumnWidth="100", Order = 4},
                 new ExcelFields() {ColumnName="Cod postal", ColumnProperty="Cpostal",ColumnWidth="30", Order = 5}
           };

            return excelfields;

        }



        public ImportExcelFile GetInfoFile()
        {

            ImportExcelFile excelfile = new ImportExcelFile
            {
                FileName = "Tranche_{0}_{1:yyyy-MM-dd HH.mm.ss}.xlsx",
                FieldsReplaceFileName = "TrancheID,Data",
                SheetName = "Tranche",
                FieldsReplaceSheetName = "TrancheID",
                BackGroundHeaderColor = "0000ff",
                FontColorHeader = "FFFFFF",
                FontColorRows ="000000",
                FontTypeHeader= "Yu Gothic",
                FontTypeRows = "Arial",
                FontSizeHeader = 12,
                FontSizeRows = 10,
                SeparatorString =","
               
             
            };

            return excelfile;

        }



        


    }
}

Classe de dados
_______________________________________________________________________________________________________________

Código:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace openxmlexcel.classes
{
    public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Age { get; set; }
        public string Localidade { get; set; }
        public string Cpostal { get; set; }
        
    }
}

Classe ExcelFields [colunas a aparecer no excel]
_______________________________________________________________________________________________________________

Código:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace openxmlexcel.classes
{
    public class ExcelFields
    {
        public string ColumnName        {get;set;}
        public string ColumnProperty    { get; set; }
        public string ColumnWidth       { get; set; }
        public UInt32 Order            { get; set; }
    }
}

Classe ImportExcelFile [dados referentes ao dicheiro e styles do mesmo]
_______________________________________________________________________________________________________________

Código:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace openxmlexcel.classes
{
    public class ImportExcelFile
    {
        public int    ID                        {get;set;}
        public String FileName                  { get; set; }
        public string FieldsReplaceFileName     { get; set; }
        public String SheetName                 { get; set; }
        public string FieldsReplaceSheetName    { get; set; }

        public String BackGroundHeaderColor     { get; set; }
        public UInt32 FontSizeHeader            { get; set; }
        public UInt32 FontSizeRows              { get; set; }
        public String FontColorHeader           { get; set; }
        public String FontColorRows             { get; set; }
        public String FontTypeHeader            { get; set; }
        public String FontTypeRows              { get; set; }
        public String SeparatorString           { get; set; }
    }
}

Open Xml SDK (Gerar excel) .net c#

alfinete

Power Member

alfinete

Power Member