Menu
Following is the code I have and in the final method below, I do all the operation. Please ignore the return types of methods, I have changed it later.
public static byte[] CreateExcelDocument<T>(List<T> list, string filename)
{
DataSet ds = new DataSet();
ds.Tables.Add(ListToDataTable(list));
byte[] byteArray = CreateExcelDocumentAsStream(ds, filename);
return byteArray;
}
public static bool CreateExcelDocumentAsStream(DataSet ds, string filename, System.Web.HttpResponse Response)
{
try
{
System.IO.MemoryStream stream = new System.IO.MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
{
WriteExcelFile(ds, document);
}
stream.Flush();
stream.Position = 0;
Response.ClearContent();
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
// NOTE: If you get an "HttpCacheability does not exist" error on the following line, make sure you have
// manually added System.Web to this project's References.
Response.Cache.SetCacheability(System.Web.HttpCacheability.NoCache);
Response.AddHeader("content-disposition", "attachment; filename=" + filename);
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
byte[] data1 = new byte[stream.Length];
stream.Read(data1, 0, data1.Length);
stream.Close();
Response.BinaryWrite(data1);
Response.Flush();
Response.End();
return true;
}
catch (Exception ex)
{
Trace.WriteLine("Failed, exception thrown: " + ex.Message);
return false;
}
}
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Create the Excel file contents. This function is used when creating an Excel file either writing
// to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//var workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
Stylesheet stylesheet = new Stylesheet(new Fills(
// Index 0 - required, reserved by Excel - no pattern
new Fill(new PatternFill { PatternType = PatternValues.None }),
// Index 1 - required, reserved by Excel - fill of gray 125
new Fill(new PatternFill { PatternType = PatternValues.Gray125 }),
// Index 2 - no pattern text on gray background
new Fill(new PatternFill
{
PatternType = PatternValues.Solid,
BackgroundColor = new BackgroundColor { Indexed = 64U },
ForegroundColor = new ForegroundColor { Rgb = "FFD9D9D9" }
})
));
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
// create a solid red fill
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
foreach (DataTable dt in ds.Tables)
{
// For each worksheet you want to create
string worksheetName = dt.TableName;
// Create worksheet part, and add it to the sheets collection in workbook
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = worksheetNumber, Name = worksheetName };
sheets.Append(sheet);
// Append this worksheet's data to our Workbook, using OpenXmlWriter, to prevent memory problems
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
spreadsheet.Close();
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
excelColumnNames[n] = GetExcelColumnName(n);
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
writer.WriteStartElement(new Row { RowIndex = rowIndex });
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
//AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
AppendTextCell1(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row { RowIndex = rowIndex });
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
// Create cell with data
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
// Add a new Excel Cell to our Row
//writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
}
private static void AppendTextCell1(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
// Add a new Excel Cell to our Row
//writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String });
writer.WriteElement(new Cell(new CellValue(cellStringValue)) { CellReference = cellReference, DataType = CellValues.String, StyleIndex = 2 });
}
private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer)
{
// Add a new Excel Cell to our Row
writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.Number });
}
Above is my code. I do try to add color by filling in stylesheet, however i dont understand the concept of styleindex to apply to a cell. Please help.
396
Select the cell or range of cells you want to format. Click Home > Format Cells dialog launcher, or press Ctrl+Shift+F. On the Fill tab, under Background Color, pick the color you want. To use a pattern with two colors, pick a color in the Pattern Color box, and then pick a pattern in the Pattern Style box.
Go to your Solution Explorer > right click on references and then click Manage NuGet Packages . Then search in tab "Online" for DocumentFormat. OpenXml and install it. After you can use DocumentFormat.
Open format? Office Open XML (also informally known as OOXML) is a zipped, XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents. Ecma International standardized the initial version as ECMA-376.
A console application using OPENXML which will generate excel file and set background color
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Data;
using System.IO;
namespace OpenXMLBGColorOfACell
{
class Program
{
static void Main(string[] args)
{
try
{
WorkbookPart workbookPart = null;
try
{
using (var memoryStream = new MemoryStream())
{
using (var excel = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true))
{
workbookPart = excel.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
uint sheetId = 1;
excel.WorkbookPart.Workbook.Sheets = new Sheets();
Sheets sheets = excel.WorkbookPart.Workbook.GetFirstChild<Sheets>();
WorkbookStylesPart stylesPart = excel.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
string relationshipId = "rId1";
WorksheetPart wSheetPart = workbookPart.AddNewPart<WorksheetPart>(relationshipId);
string sheetName = "BackgroundColor";
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
Worksheet worksheet = new Worksheet();
wSheetPart.Worksheet = worksheet;
SheetData sheetData = new SheetData();
worksheet.Append(sheetData);
string[] excelColumns = new string[] { "A", "B", "C", "D", "E", "F", "G" };
AddToCell(sheetData,4, 2, excelColumns[0], CellValues.String, "Test Cell Background");
excel.Close();
}
FileStream fileStream = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "CellBackgroundColor.xlsx", FileMode.Create, FileAccess.Write);
memoryStream.WriteTo(fileStream);
fileStream.Close();
memoryStream.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
catch (Exception ex)
{
// logging, etc.
}
}
public static void AddToCell(SheetData sheetData, UInt32Value styleIndex, UInt32 uint32rowIndex, string strColumnName, DocumentFormat.OpenXml.EnumValue<CellValues> CellDataType, string strCellValue)
{
Row row = new Row() { RowIndex = uint32rowIndex };
Cell cell = new Cell();
cell = new Cell() { StyleIndex = styleIndex };
cell.CellReference = strColumnName + row.RowIndex.ToString();
cell.DataType = CellDataType;
cell.CellValue = new CellValue(strCellValue);
row.AppendChild(cell);
sheetData.Append(row);
}
public static Stylesheet GenerateStyleSheet()
{
return new Stylesheet(
new DocumentFormat.OpenXml.Spreadsheet.Fonts(
new DocumentFormat.OpenXml.Spreadsheet.Font(new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }),// Index 0 - The default font.
new Font(new Bold(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }), // Index 1 - The bold font.
new Font(new Italic(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }), // Index 2 - The Italic font.
new Font(new FontSize() { Val = 18 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }), // Index 3 - The Times Roman font. with 16 size
new Font(new Bold(), new FontSize() { Val = 18 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }), // Index 4 - The Times Roman font. with 16 size
new Font(new Bold(), new FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "FFFFFF" } }, new FontName() { Val = "Calibri" }) // Index 5 - The bold font.
),
new Fills(
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 0 - The default fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.None }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 1 - The default fill of gray 125 (required)
new DocumentFormat.OpenXml.Spreadsheet.PatternFill() { PatternType = PatternValues.Gray125 }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 2 - The yellow fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill(
new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }
)
{ PatternType = PatternValues.Solid }),
new DocumentFormat.OpenXml.Spreadsheet.Fill( // Index 3 - The Blue fill.
new DocumentFormat.OpenXml.Spreadsheet.PatternFill(
new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor() { Rgb = new HexBinaryValue() { Value = "8EA9DB" } }
)
{ PatternType = PatternValues.Solid })
),
new Borders(
new Border( // Index 0 - The default border.
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(),
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(),
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(),
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(),
new DiagonalBorder()),
new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.Thin },
new DiagonalBorder()),
new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell
new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.None },
new DocumentFormat.OpenXml.Spreadsheet.RightBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.None },
new DocumentFormat.OpenXml.Spreadsheet.TopBorder(
new Color() { Auto = true }
)
{ Style = BorderStyleValues.None },
new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(
new Color() { Rgb = new HexBinaryValue() { Value = "FFA500" } }
)
{ Style = BorderStyleValues.Thin },
new DiagonalBorder())
),
new CellFormats(
new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead
new CellFormat() { FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 1 - Bold
new CellFormat() { FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 2 - Italic
new CellFormat() { FontId = 3, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 3 - Times Roman
new CellFormat() { FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }, // Index 4 - Yellow Fill
new CellFormat( // Index 5 - Alignment
new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
)
{ FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
new CellFormat() { FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }, // Index 6 - Border
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) // Index 7 - Alignment
{ FontId = 1, FillId = 0, BorderId = 0, ApplyAlignment = true },
new CellFormat() { FontId = 4, FillId = 0, BorderId = 0, ApplyFont = true }, // Index 8 - Times Roman
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) { FontId = 0, FillId = 0, BorderId = 2, ApplyFont = true }, // Index 9 - Bottom Border with Color 70AD47
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) // Index 10 - Alignment
{ FontId = 5, FillId = 3, BorderId = 0, ApplyAlignment = true }
)
); // return
}
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
