alfinete
Power Member
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
_____________________________________________________________________________________________________________
Class Person
_____________________________________________________________________________________________________________
file.aspx.cs
________________________________________________
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;
}
}
}