I want to write excel file using excel package from System.Data.DataTable
and need to format some cells in excel file like back ground color.
How can I do that?
Set all automatic formatting options at onceClick File > Options. In the Excel Options box, click Proofing > AutoCorrect Options. On the AutoFormat As You Type tab, check the boxes for the auto formatting you want to use.
Formatting in Excel is a neat trick used to change the appearance of the data represented in the worksheet. We can do formatting in multiple ways, such as we can format the font of the cells or format the table by using the “Styles” and “Format” tabs available in the “Home” tab.
Excel is a spreadsheet program from Microsoft and a component of its Office product group for business applications. Microsoft Excel enables users to format, organize and calculate data in a spreadsheet.
Although you may use Interop calls to create an excel file, I prefer solutions without it. According to your given details I found a solution based on OpenXML. No need for Interop but you have to learn the according objectModel from openXML. What you need before you can start:
Afterwards within your project...
And than you are ready to use this piece of code:
// important namespaces
using System.IO.Packaging;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using Excel = DocumentFormat.OpenXml.Office2010.Excel;
static void Main(string[] args)
{
// created a demoTable with id column and five string columns with arbitrary values
DataTable demoTable = CreateDemoTable();
CreateWorkbook(@"c:\ExcelAndStyles.xlsx", demoTable);
}
private static void CreateWorkbook(string fileName, DataTable table)
{
try
{
if (File.Exists(fileName))
File.Delete(fileName);
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
// add styles to sheet
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// generate data (row&cells) and add it to workbook
SheetData sd = CreateSheetData(table);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = table.TableName;
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
}
catch (Exception e)
{ // do some errorHandling here }
}
private static Stylesheet CreateStylesheet()
{
// add namespace info
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// fonts
Fonts fonts1 = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
Font font1 = new Font();
FontSize fontSize1 = new FontSize() { Val = 11D };
Color color1 = new Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
// fillColor
Fills fills1 = new Fills() { Count = (UInt32Value)5U };
// FillId = 0
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
// FillId = 1
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
// FillId = 2,RED
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFFF0000" };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
// FillId = 3,BLUE
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "FF0070C0" };
BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
// FillId = 4,YELLO
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFFFF00" };
BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill5.Append(foregroundColor3);
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
fills1.Append(fill1); // 0U
fills1.Append(fill2); // 1U
fills1.Append(fill3); // 2U
fills1.Append(fill4); // 3U
fills1.Append(fill5); // 4U
// border styles
Borders borders1 = new Borders() { Count = (UInt32Value)1U };
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
// cellFormats
CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat1 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats() { Count = (UInt32Value)4U };
// see a few lines above - refer to: fills1.Append(XXXX);
// see also ApplyFill = true!!
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
cellFormats1.Append(cellFormat2); // fillId 0U --- cellFormat Index 0
cellFormats1.Append(cellFormat3); // fillId 2U --- cellFormat Index 1
cellFormats1.Append(cellFormat4); // fillId 3U --- cellFormat Index 2
cellFormats1.Append(cellFormat5); // fillId 4U --- cellFormat Index 3
// cellStyles
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
// extensions
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
Excel.SlicerStyles slicerStyles1 = new Excel.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
stylesheetExtension1.Append(slicerStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
// add all formats/styles to stylesheet
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
return stylesheet1;
}
private static SheetData CreateSheetData(DataTable table)
{
SheetData sheetData = new SheetData();
for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++)
{
DataRow dataRow = table.Rows[rowIndex];
Row row = new Row() { RowIndex = new UInt32Value((uint)(rowIndex + 1)) };
for (int colIndex = 0; colIndex < dataRow.ItemArray.Length; colIndex++)
{
object value = dataRow[colIndex].ToString();
Cell cell = new Cell();
cell.CellReference = ((char)(colIndex + 65)).ToString() + (rowIndex+1).ToString();
// assigned simple red background style
// have a deeper look in CreateStylesheet for details what 1U contains
// for demo purposes may choose 2U and 3U too
cell.StyleIndex = (UInt32Value)1U;
// for demo purposes, treat all values as string
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(table.Rows[rowIndex][colIndex].ToString());
row.Append(cell);
}
sheetData.Append(row);
}
return sheetData;
}
Most of this code has been taken out of several sites listed above! I adopted it and changed it to fit your question. This example code worked for me to create an excelFile containing several rows and columns with differen values and styles.
Hope that helps!
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