Schreiben von excel mit excel mit Formatierung

Obwohl Sie möglicherweise verwenden Interop Aufrufe erstellen Sie eine excel-Datei, die ich lieber Lösungen ohne es. Nach deinen Vorgaben habe ich eine Lösung gefunden, basierend auf OpenXML. Keine Notwendigkeit für Interop-aber Sie haben zu lernen, die nach objectModel von openXML. Was Sie brauchen, bevor Sie anfangen können:

1. Download OpenXML SDK und installieren Sie es von microsoft
2. Gehen openXmlDeveloper.org und lernen, wie Sie mit openXml zu erstellen excelFiles
3. Haben Sie einen Blick auf einige Ressourcen im web zu wie Zugriff/Lesen/schreiben excelFiles mit openXml (wie SO_Question1, sozial.msdn.microsoft.com_example, Blog1 und SO_Question2)

Dann in dein Projekt...

1. Verweis hinzufügen, um DocumentFormat.OpenXML Bibliothek (durchsuchen Sie Ihren computer, so etwas wie C:\Program Files (x86)\Open XML SDK\V2.0\lib\DocumentFormat.OpenXml.dll)
2. Verweis hinzufügen, um WindowBase

Und als Sie bereit sind zu verwenden, dieses Stück 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;
}

Meisten dieser code wurde aus mehreren Seiten oben aufgeführt! Ich nahm es und es geändert, um zu passen Ihre Frage. Dieser Beispiel-code war für mich zu erstellen, eine excelFile mit mehreren Zeilen und Spalten mit Unterschiede, Werte und Stile.

Hoffe, das hilft!