asp.net-coreopenxmlexport-to-excel
Export list to excel using asp.net core set header style
I'm exporting data to excel. I would like to add excel header color and header width. I tried different way but not working. Please see my below function. I'm using asp.net and OpenXml library. Any one expert here to suggest any solution. Thanks
public byte[] ExportExcelFile<T>(List<T> data, string sheetName)
{
var table = ToDataTable(data);
var memoryStream = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet(sheetData);
var sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>();
var relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
sheets.Append(new Sheet { Id = relationshipId, SheetId = 1, Name = sheetName });
var headerRow = new Row();
var columns = new List<string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
var cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
var newRow = new Row();
foreach (var col in columns)
{
var cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbook.Save();
workbook.Close();
}
return memoryStream.ToArray();
}
Solution
You could refer the following steps to add css style for the excel content:
Add Stylesheet. Create a GenerateStylesheet class and contain the following code:
private Stylesheet GenerateStylesheet() { Stylesheet styleSheet = null; //Fonts can have one or more Font children which each have different properties like FontSize, Bold, Color, and etc. //Here, we add two Font children to the Fonts object. The first one is the default font use by all cells, and the second one is specific to header. Fonts fonts = new Fonts( new Font( // Index 0 - default new FontSize() { Val = 10 } ), new Font( // Index 1 - header new FontSize() { Val = 10 }, new Bold(), new Color() { Rgb = "FFFFFF" } )); //Fills can have one or more Fill children which you can set its ForegroundColor. 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 = "66666666" } }) { PatternType = PatternValues.Solid }) // Index 2 - header ); //Borders can have one or more Border children which each defines how the border should look like 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 which has one or many CellFormat children. Each CellFormat gets the index of Font, Border, Fill, or etc. 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; }Add the style to the workbook.
Add a WorkbookStylePart to the WorkbookPart and initialize its Stylesheet:
//Add a WorkbookStylePart to the WorkbookPart and initialize its Stylesheet: WorkbookStylesPart stylePart = workbook.WorkbookPart.AddNewPart<WorkbookStylesPart>(); stylePart.Stylesheet = GenerateStylesheet(); stylePart.Stylesheet.Save();Add style to Cells
foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); var cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); cell.StyleIndex = 2; //add css style to Cells headerRow.AppendChild(cell); }The Complete ExportExcelFile code:
public byte[] ExportExcelFile<T>(List<T> data, string sheetName) { var table = ToDataTable(data); var memoryStream = new MemoryStream(); using (var workbook = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook)) { workbook.AddWorkbookPart(); workbook.WorkbookPart.Workbook = new Workbook(); workbook.WorkbookPart.Workbook.Sheets = new Sheets(); //Add a WorkbookStylePart to the WorkbookPart and initialize its Stylesheet: WorkbookStylesPart stylePart = workbook.WorkbookPart.AddNewPart<WorkbookStylesPart>(); stylePart.Stylesheet = GenerateStylesheet(); stylePart.Stylesheet.Save(); var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>(); var sheetData = new SheetData(); sheetPart.Worksheet = new Worksheet(sheetData); var sheets = workbook.WorkbookPart.Workbook.GetFirstChild<Sheets>(); var relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); sheets.Append(new Sheet { Id = relationshipId, SheetId = 1, Name = sheetName }); var headerRow = new Row(); var columns = new List<string>(); foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); var cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); cell.StyleIndex = 2; //add css style to Cells headerRow.AppendChild(cell); } sheetData.AppendChild(headerRow); foreach (DataRow dsrow in table.Rows) { var newRow = new Row(); foreach (var col in columns) { var cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(dsrow[col].ToString()); // cell.StyleIndex = 1; //add css style to Cells newRow.AppendChild(cell); } sheetData.AppendChild(newRow); } workbook.Save(); workbook.Close(); } return memoryStream.ToArray(); }
Then, when export excel using the above code:
public IActionResult Privacy()
{
List<Employee> Students = new List<Employee>(){
new Employee() { Name = "Pradeep", salary = 15000, EmpId = 100 },
new Employee() { Name = "Smith", salary = 25000, EmpId = 101},
new Employee() { Name = "John", salary = 21000, EmpId = 102 }
};
byte[] result = ExportExcelFile<Employee>(Students, "sheetA");
return File(result, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
//return View();
}
The result as below:
Update:
To set the columns width, you could update the ExportExcelFile method as below:
public byte[] ExportExcelFile<T>(List<T> data, string sheetName)
{
var table = ToDataTable(data);
var memoryStream = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = workbook.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart<WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns widthcolumns = new Columns(
new Column // first column
{
Min = 1,
Max = 1,
Width = 20,
CustomWidth = true
},
new Column // second columns
{
Min = 2,
Max = 3,
Width = 15,
CustomWidth = true
},
new Column // third column
{
Min = 4,
Max = 4,
Width = 8,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(widthcolumns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
var headerRow = new Row();
var columns = new List<string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
var cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
cell.StyleIndex = 2; //add css style to Cells
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
var newRow = new Row();
foreach (var col in columns)
{
var cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString()); //
cell.StyleIndex = 1; //add css style to Cells
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbook.Save();
workbook.Close();
}
return memoryStream.ToArray();
}
The result as below:
