Write excel using excel package with formatting

Question

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?

Solution

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:

1. Download OpenXML SDK and install it from microsoft
2. Go to openXmlDeveloper.org and learn how to use openXml to create excelFiles
3. Have a look at several ressources in the web concerning how to access/read/write excelFiles with openXml (such as SO_Question1, social.msdn.microsoft.com_example, Blog1 and SO_Question2)

Afterwards within your project...

4. Add reference to DocumentFormat.OpenXML library (search your computer, somewhat like C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll)
5. Add reference to WindowBase

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!