OpenXml源码test目录下有大量参考例子，有时间的同学可以结合例子和官方文档深入研究，本人也只是学到能解决项目需求的程度。
一、首先讲一下如何设置单元格格式，与CellFormat这个类相关，下图的属性都可以设置：
先贴出初始化Excel文档的代码，后续提到的方法在里面查找：
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System.IO; using A = DocumentFormat.OpenXml.Drawing; using X14 = DocumentFormat.OpenXml.Office2010.Excel; using X15 = DocumentFormat.OpenXml.Office2013.Excel; namespace Gnt.Utils { public class GeneratedSpreadsheetDocument { // Creates a SpreadsheetDocument. public SpreadsheetDocument CreatePackage(Stream stream) { SpreadsheetDocument package = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook); CreateParts(package); return package; } // Creates a SpreadsheetDocument. public SpreadsheetDocument CreatePackage(string filepath) { SpreadsheetDocument package = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook); CreateParts(package); return package; } // Adds child parts and generates content of the specified part. private void CreateParts(SpreadsheetDocument document) { WorkbookPart workbookPart1 = document.AddWorkbookPart(); GenerateWorkbookPart1Content(workbookPart1); ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId3"); GenerateThemePart1Content(themePart1); WorkbookStylesPart workbookStylesPart1 = workbookPart1.AddNewPart<WorkbookStylesPart>("rId4"); GenerateWorkbookStylesPart1Content(workbookStylesPart1); } // Generates content of workbookPart1. private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1) { Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } }; workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420" }; WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = 124226U }; BookViews bookViews1 = new BookViews(); WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = 14805U, WindowHeight = 8010U }; bookViews1.Append(workbookView1); CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = 152511U }; workbook1.Append(fileVersion1); workbook1.Append(workbookProperties1); workbook1.Append(bookViews1); workbook1.Append(calculationProperties1); workbookPart1.Workbook = workbook1; } // Generates content of themePart1. private void GenerateThemePart1Content(ThemePart themePart1) { A.Theme theme1 = new A.Theme() { Name = "Office Theme" }; theme1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main"); A.ThemeElements themeElements1 = new A.ThemeElements(); A.ColorScheme colorScheme1 = new A.ColorScheme() { Name = "Office" }; A.Dark1Color dark1Color1 = new A.Dark1Color(); A.SystemColor systemColor1 = new A.SystemColor() { Val = A.SystemColorValues.WindowText, LastColor = "000000" }; dark1Color1.Append(systemColor1); A.Light1Color light1Color1 = new A.Light1Color(); A.SystemColor systemColor2 = new A.SystemColor() { Val = A.SystemColorValues.Window, LastColor = "FFFFFF" }; light1Color1.Append(systemColor2); A.Dark2Color dark2Color1 = new A.Dark2Color(); A.RgbColorModelHex rgbColorModelHex1 = new A.RgbColorModelHex() { Val = "1F497D" }; dark2Color1.Append(rgbColorModelHex1); A.Light2Color light2Color1 = new A.Light2Color(); A.RgbColorModelHex rgbColorModelHex2 = new A.RgbColorModelHex() { Val = "EEECE1" }; light2Color1.Append(rgbColorModelHex2); A.Accent1Color accent1Color1 = new A.Accent1Color(); A.RgbColorModelHex rgbColorModelHex3 = new A.RgbColorModelHex() { Val = "4F81BD" }; accent1Color1.Append(rgbColorModelHex3); A.Accent2Color accent2Color1 = new A.Accent2Color(); A.RgbColorModelHex rgbColorModelHex4 = new A.RgbColorModelHex() { Val = "C0504D" }; accent2Color1.Append(rgbColorModelHex4); A.Accent3Color accent3Color1 = new A.Accent3Color(); A.RgbColorModelHex rgbColorModelHex5 = new A.RgbColorModelHex() { Val = "9BBB59" }; accent3Color1.Append(rgbColorModelHex5); A.Accent4Color accent4Color1 = new A.Accent4Color(); A.RgbColorModelHex rgbColorModelHex6 = new A.RgbColorModelHex() { Val = "8064A2" }; accent4Color1.Append(rgbColorModelHex6); A.Accent5Color accent5Color1 = new A.Accent5Color(); A.RgbColorModelHex rgbColorModelHex7 = new A.RgbColorModelHex() { Val = "4BACC6" }; accent5Color1.Append(rgbColorModelHex7); A.Accent6Color accent6Color1 = new A.Accent6Color(); A.RgbColorModelHex rgbColorModelHex8 = new A.RgbColorModelHex() { Val = "F79646" }; accent6Color1.Append(rgbColorModelHex8); A.Hyperlink hyperlink1 = new A.Hyperlink(); A.RgbColorModelHex rgbColorModelHex9 = new A.RgbColorModelHex() { Val = "0000FF" }; hyperlink1.Append(rgbColorModelHex9); A.FollowedHyperlinkColor followedHyperlinkColor1 = new A.FollowedHyperlinkColor(); A.RgbColorModelHex rgbColorModelHex10 = new A.RgbColorModelHex() { Val = "800080" }; followedHyperlinkColor1.Append(rgbColorModelHex10); colorScheme1.Append(dark1Color1); colorScheme1.Append(light1Color1); colorScheme1.Append(dark2Color1); colorScheme1.Append(light2Color1); colorScheme1.Append(accent1Color1); colorScheme1.Append(accent2Color1); colorScheme1.Append(accent3Color1); colorScheme1.Append(accent4Color1); colorScheme1.Append(accent5Color1); colorScheme1.Append(accent6Color1); colorScheme1.Append(hyperlink1); colorScheme1.Append(followedHyperlinkColor1); A.FontScheme fontScheme1 = new A.FontScheme() { Name = "Office" }; A.MajorFont majorFont1 = new A.MajorFont(); A.LatinFont latinFont1 = new A.LatinFont() { Typeface = "Cambria", Panose = "020F0302020204030204" }; A.EastAsianFont eastAsianFont1 = new A.EastAsianFont() { Typeface = string.Empty }; A.ComplexScriptFont complexScriptFont1 = new A.ComplexScriptFont() { Typeface = string.Empty }; A.SupplementalFont supplementalFont1 = new A.SupplementalFont() { Script = "Jpan", Typeface = "ＭＳ Ｐゴシック" }; A.SupplementalFont supplementalFont2 = new A.SupplementalFont() { Script = "Hang", Typeface = "맑은 고딕" }; A.SupplementalFont supplementalFont3 = new A.SupplementalFont() { Script = "Hans", Typeface = "宋体" }; A.SupplementalFont supplementalFont4 = new A.SupplementalFont() { Script = "Hant", Typeface = "新細明體" }; A.SupplementalFont supplementalFont5 = new A.SupplementalFont() { Script = "Arab", Typeface = "Times New Roman" }; A.SupplementalFont supplementalFont6 = new A.SupplementalFont() { Script = "Hebr", Typeface = "Times New Roman" }; A.SupplementalFont supplementalFont7 = new A.SupplementalFont() { Script = "Thai", Typeface = "Tahoma" }; A.SupplementalFont supplementalFont8 = new A.SupplementalFont() { Script = "Ethi", Typeface = "Nyala" }; A.SupplementalFont supplementalFont9 = new A.SupplementalFont() { Script = "Beng", Typeface = "Vrinda" }; A.SupplementalFont supplementalFont10 = new A.SupplementalFont() { Script = "Gujr", Typeface = "Shruti" }; A.SupplementalFont supplementalFont11 = new A.SupplementalFont() { Script = "Khmr", Typeface = "MoolBoran" }; A.SupplementalFont supplementalFont12 = new A.SupplementalFont() { Script = "Knda", Typeface = "Tunga" }; A.SupplementalFont supplementalFont13 = new A.SupplementalFont() { Script = "Guru", Typeface = "Raavi" }; A.SupplementalFont supplementalFont14 = new A.SupplementalFont() { Script = "Cans", Typeface = "Euphemia" }; A.SupplementalFont supplementalFont15 = new A.SupplementalFont() { Script = "Cher", Typeface = "Plantagenet Cherokee" }; A.SupplementalFont supplementalFont16 = new A.SupplementalFont() { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }; A.SupplementalFont supplementalFont17 = new A.SupplementalFont() { Script = "Tibt", Typeface = "Microsoft Himalaya" }; A.SupplementalFont supplementalFont18 = new A.SupplementalFont() { Script = "Thaa", Typeface = "MV Boli" }; A.SupplementalFont supplementalFont19 = new A.SupplementalFont() { Script = "Deva", Typeface = "Mangal" }; A.SupplementalFont supplementalFont20 = new A.SupplementalFont() { Script = "Telu", Typeface = "Gautami" }; A.SupplementalFont supplementalFont21 = new A.SupplementalFont() { Script = "Taml", Typeface = "Latha" }; A.SupplementalFont supplementalFont22 = new A.SupplementalFont() { Script = "Syrc", Typeface = "Estrangelo Edessa" }; A.SupplementalFont supplementalFont23 = new A.SupplementalFont() { Script = "Orya", Typeface = "Kalinga" }; A.SupplementalFont supplementalFont24 = new A.SupplementalFont() { Script = "Mlym", Typeface = "Kartika" }; A.SupplementalFont supplementalFont25 = new A.SupplementalFont() { Script = "Laoo", Typeface = "DokChampa" }; A.SupplementalFont supplementalFont26 = new A.SupplementalFont() { Script = "Sinh", Typeface = "Iskoola Pota" }; A.SupplementalFont supplementalFont27 = new A.SupplementalFont() { Script = "Mong", Typeface = "Mongolian Baiti" }; A.SupplementalFont supplementalFont28 = new A.SupplementalFont() { Script = "Viet", Typeface = "Times New Roman" }; A.SupplementalFont supplementalFont29 = new A.SupplementalFont() { Script = "Uigh", Typeface = "Microsoft Uighur" }; A.SupplementalFont supplementalFont30 = new A.SupplementalFont() { Script = "Geor", Typeface = "Sylfaen" }; majorFont1.Append(latinFont1); majorFont1.Append(eastAsianFont1); majorFont1.Append(complexScriptFont1); majorFont1.Append(supplementalFont1); majorFont1.Append(supplementalFont2); majorFont1.Append(supplementalFont3); majorFont1.Append(supplementalFont4); majorFont1.Append(supplementalFont5); majorFont1.Append(supplementalFont6); majorFont1.Append(supplementalFont7); majorFont1.Append(supplementalFont8); majorFont1.Append(supplementalFont9); majorFont1.Append(supplementalFont10); majorFont1.Append(supplementalFont11); majorFont1.Append(supplementalFont12); majorFont1.Append(supplementalFont13); majorFont1.Append(supplementalFont14); majorFont1.Append(supplementalFont15); majorFont1.Append(supplementalFont16); majorFont1.Append(supplementalFont17); majorFont1.Append(supplementalFont18); majorFont1.Append(supplementalFont19); majorFont1.Append(supplementalFont20); majorFont1.Append(supplementalFont21); majorFont1.Append(supplementalFont22); majorFont1.Append(supplementalFont23); majorFont1.Append(supplementalFont24); majorFont1.Append(supplementalFont25); majorFont1.Append(supplementalFont26); majorFont1.Append(supplementalFont27); majorFont1.Append(supplementalFont28); majorFont1.Append(supplementalFont29); majorFont1.Append(supplementalFont30); A.MinorFont minorFont1 = new A.MinorFont(); A.LatinFont latinFont2 = new A.LatinFont() { Typeface = "Calibri", Panose = "020F0502020204030204" }; A.EastAsianFont eastAsianFont2 = new A.EastAsianFont() { Typeface = string.Empty }; A.ComplexScriptFont complexScriptFont2 = new A.ComplexScriptFont() { Typeface = string.Empty }; A.SupplementalFont supplementalFont31 = new A.SupplementalFont() { Script = "Jpan", Typeface = "ＭＳ Ｐゴシック" }; A.SupplementalFont supplementalFont32 = new A.SupplementalFont() { Script = "Hang", Typeface = "맑은 고딕" }; A.SupplementalFont supplementalFont33 = new A.SupplementalFont() { Script = "Hans", Typeface = "宋体" }; A.SupplementalFont supplementalFont34 = new A.SupplementalFont() { Script = "Hant", Typeface = "新細明體" }; A.SupplementalFont supplementalFont35 = new A.SupplementalFont() { Script = "Arab", Typeface = "Arial" }; A.SupplementalFont supplementalFont36 = new A.SupplementalFont() { Script = "Hebr", Typeface = "Arial" }; A.SupplementalFont supplementalFont37 = new A.SupplementalFont() { Script = "Thai", Typeface = "Tahoma" }; A.SupplementalFont supplementalFont38 = new A.SupplementalFont() { Script = "Ethi", Typeface = "Nyala" }; A.SupplementalFont supplementalFont39 = new A.SupplementalFont() { Script = "Beng", Typeface = "Vrinda" }; A.SupplementalFont supplementalFont40 = new A.SupplementalFont() { Script = "Gujr", Typeface = "Shruti" }; A.SupplementalFont supplementalFont41 = new A.SupplementalFont() { Script = "Khmr", Typeface = "DaunPenh" }; A.SupplementalFont supplementalFont42 = new A.SupplementalFont() { Script = "Knda", Typeface = "Tunga" }; A.SupplementalFont supplementalFont43 = new A.SupplementalFont() { Script = "Guru", Typeface = "Raavi" }; A.SupplementalFont supplementalFont44 = new A.SupplementalFont() { Script = "Cans", Typeface = "Euphemia" }; A.SupplementalFont supplementalFont45 = new A.SupplementalFont() { Script = "Cher", Typeface = "Plantagenet Cherokee" }; A.SupplementalFont supplementalFont46 = new A.SupplementalFont() { Script = "Yiii", Typeface = "Microsoft Yi Baiti" }; A.SupplementalFont supplementalFont47 = new A.SupplementalFont() { Script = "Tibt", Typeface = "Microsoft Himalaya" }; A.SupplementalFont supplementalFont48 = new A.SupplementalFont() { Script = "Thaa", Typeface = "MV Boli" }; A.SupplementalFont supplementalFont49 = new A.SupplementalFont() { Script = "Deva", Typeface = "Mangal" }; A.SupplementalFont supplementalFont50 = new A.SupplementalFont() { Script = "Telu", Typeface = "Gautami" }; A.SupplementalFont supplementalFont51 = new A.SupplementalFont() { Script = "Taml", Typeface = "Latha" }; A.SupplementalFont supplementalFont52 = new A.SupplementalFont() { Script = "Syrc", Typeface = "Estrangelo Edessa" }; A.SupplementalFont supplementalFont53 = new A.SupplementalFont() { Script = "Orya", Typeface = "Kalinga" }; A.SupplementalFont supplementalFont54 = new A.SupplementalFont() { Script = "Mlym", Typeface = "Kartika" }; A.SupplementalFont supplementalFont55 = new A.SupplementalFont() { Script = "Laoo", Typeface = "DokChampa" }; A.SupplementalFont supplementalFont56 = new A.SupplementalFont() { Script = "Sinh", Typeface = "Iskoola Pota" }; A.SupplementalFont supplementalFont57 = new A.SupplementalFont() { Script = "Mong", Typeface = "Mongolian Baiti" }; A.SupplementalFont supplementalFont58 = new A.SupplementalFont() { Script = "Viet", Typeface = "Arial" }; A.SupplementalFont supplementalFont59 = new A.SupplementalFont() { Script = "Uigh", Typeface = "Microsoft Uighur" }; A.SupplementalFont supplementalFont60 = new A.SupplementalFont() { Script = "Geor", Typeface = "Sylfaen" }; minorFont1.Append(latinFont2); minorFont1.Append(eastAsianFont2); minorFont1.Append(complexScriptFont2); minorFont1.Append(supplementalFont31); minorFont1.Append(supplementalFont32); minorFont1.Append(supplementalFont33); minorFont1.Append(supplementalFont34); minorFont1.Append(supplementalFont35); minorFont1.Append(supplementalFont36); minorFont1.Append(supplementalFont37); minorFont1.Append(supplementalFont38); minorFont1.Append(supplementalFont39); minorFont1.Append(supplementalFont40); minorFont1.Append(supplementalFont41); minorFont1.Append(supplementalFont42); minorFont1.Append(supplementalFont43); minorFont1.Append(supplementalFont44); minorFont1.Append(supplementalFont45); minorFont1.Append(supplementalFont46); minorFont1.Append(supplementalFont47); minorFont1.Append(supplementalFont48); minorFont1.Append(supplementalFont49); minorFont1.Append(supplementalFont50); minorFont1.Append(supplementalFont51); minorFont1.Append(supplementalFont52); minorFont1.Append(supplementalFont53); minorFont1.Append(supplementalFont54); minorFont1.Append(supplementalFont55); minorFont1.Append(supplementalFont56); minorFont1.Append(supplementalFont57); minorFont1.Append(supplementalFont58); minorFont1.Append(supplementalFont59); minorFont1.Append(supplementalFont60); fontScheme1.Append(majorFont1); fontScheme1.Append(minorFont1); A.FormatScheme formatScheme1 = new A.FormatScheme() { Name = "Office" }; A.FillStyleList fillStyleList1 = new A.FillStyleList(); A.SolidFill solidFill1 = new A.SolidFill(); A.SchemeColor schemeColor1 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; solidFill1.Append(schemeColor1); A.GradientFill gradientFill1 = new A.GradientFill() { RotateWithShape = true }; A.GradientStopList gradientStopList1 = new A.GradientStopList(); A.GradientStop gradientStop1 = new A.GradientStop() { Position = 0 }; A.SchemeColor schemeColor2 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Tint tint1 = new A.Tint() { Val = 50000 }; A.SaturationModulation saturationModulation1 = new A.SaturationModulation() { Val = 300000 }; schemeColor2.Append(tint1); schemeColor2.Append(saturationModulation1); gradientStop1.Append(schemeColor2); A.GradientStop gradientStop2 = new A.GradientStop() { Position = 35000 }; A.SchemeColor schemeColor3 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Tint tint2 = new A.Tint() { Val = 37000 }; A.SaturationModulation saturationModulation2 = new A.SaturationModulation() { Val = 300000 }; schemeColor3.Append(tint2); schemeColor3.Append(saturationModulation2); gradientStop2.Append(schemeColor3); A.GradientStop gradientStop3 = new A.GradientStop() { Position = 100000 }; A.SchemeColor schemeColor4 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Tint tint3 = new A.Tint() { Val = 15000 }; A.SaturationModulation saturationModulation3 = new A.SaturationModulation() { Val = 350000 }; schemeColor4.Append(tint3); schemeColor4.Append(saturationModulation3); gradientStop3.Append(schemeColor4); gradientStopList1.Append(gradientStop1); gradientStopList1.Append(gradientStop2); gradientStopList1.Append(gradientStop3); A.LinearGradientFill linearGradientFill1 = new A.LinearGradientFill() { Angle = 16200000, Scaled = true }; gradientFill1.Append(gradientStopList1); gradientFill1.Append(linearGradientFill1); A.GradientFill gradientFill2 = new A.GradientFill() { RotateWithShape = true }; A.GradientStopList gradientStopList2 = new A.GradientStopList(); A.GradientStop gradientStop4 = new A.GradientStop() { Position = 0 }; A.SchemeColor schemeColor5 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Shade shade1 = new A.Shade() { Val = 51000 }; A.SaturationModulation saturationModulation4 = new A.SaturationModulation() { Val = 130000 }; schemeColor5.Append(shade1); schemeColor5.Append(saturationModulation4); gradientStop4.Append(schemeColor5); A.GradientStop gradientStop5 = new A.GradientStop() { Position = 80000 }; A.SchemeColor schemeColor6 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Shade shade2 = new A.Shade() { Val = 93000 }; A.SaturationModulation saturationModulation5 = new A.SaturationModulation() { Val = 130000 }; schemeColor6.Append(shade2); schemeColor6.Append(saturationModulation5); gradientStop5.Append(schemeColor6); A.GradientStop gradientStop6 = new A.GradientStop() { Position = 100000 }; A.SchemeColor schemeColor7 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Shade shade3 = new A.Shade() { Val = 94000 }; A.SaturationModulation saturationModulation6 = new A.SaturationModulation() { Val = 135000 }; schemeColor7.Append(shade3); schemeColor7.Append(saturationModulation6); gradientStop6.Append(schemeColor7); gradientStopList2.Append(gradientStop4); gradientStopList2.Append(gradientStop5); gradientStopList2.Append(gradientStop6); A.LinearGradientFill linearGradientFill2 = new A.LinearGradientFill() { Angle = 16200000, Scaled = false }; gradientFill2.Append(gradientStopList2); gradientFill2.Append(linearGradientFill2); fillStyleList1.Append(solidFill1); fillStyleList1.Append(gradientFill1); fillStyleList1.Append(gradientFill2); A.LineStyleList lineStyleList1 = new A.LineStyleList(); A.Outline outline1 = new A.Outline() { Width = 9525, CapType = A.LineCapValues.Flat, CompoundLineType = A.CompoundLineValues.Single, Alignment = A.PenAlignmentValues.Center }; A.SolidFill solidFill2 = new A.SolidFill(); A.SchemeColor schemeColor8 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Shade shade4 = new A.Shade() { Val = 95000 }; A.SaturationModulation saturationModulation7 = new A.SaturationModulation() { Val = 105000 }; schemeColor8.Append(shade4); schemeColor8.Append(saturationModulation7); solidFill2.Append(schemeColor8); A.PresetDash presetDash1 = new A.PresetDash() { Val = A.PresetLineDashValues.Solid }; outline1.Append(solidFill2); outline1.Append(presetDash1); A.Outline outline2 = new A.Outline() { Width = 25400, CapType = A.LineCapValues.Flat, CompoundLineType = A.CompoundLineValues.Single, Alignment = A.PenAlignmentValues.Center }; A.SolidFill solidFill3 = new A.SolidFill(); A.SchemeColor schemeColor9 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; solidFill3.Append(schemeColor9); A.PresetDash presetDash2 = new A.PresetDash() { Val = A.PresetLineDashValues.Solid }; outline2.Append(solidFill3); outline2.Append(presetDash2); A.Outline outline3 = new A.Outline() { Width = 38100, CapType = A.LineCapValues.Flat, CompoundLineType = A.CompoundLineValues.Single, Alignment = A.PenAlignmentValues.Center }; A.SolidFill solidFill4 = new A.SolidFill(); A.SchemeColor schemeColor10 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; solidFill4.Append(schemeColor10); A.PresetDash presetDash3 = new A.PresetDash() { Val = A.PresetLineDashValues.Solid }; outline3.Append(solidFill4); outline3.Append(presetDash3); lineStyleList1.Append(outline1); lineStyleList1.Append(outline2); lineStyleList1.Append(outline3); A.EffectStyleList effectStyleList1 = new A.EffectStyleList(); A.EffectStyle effectStyle1 = new A.EffectStyle(); A.EffectList effectList1 = new A.EffectList(); A.OuterShadow outerShadow1 = new A.OuterShadow() { BlurRadius = 40000L, Distance = 20000L, Direction = 5400000, RotateWithShape = false }; A.RgbColorModelHex rgbColorModelHex11 = new A.RgbColorModelHex() { Val = "000000" }; A.Alpha alpha1 = new A.Alpha() { Val = 38000 }; rgbColorModelHex11.Append(alpha1); outerShadow1.Append(rgbColorModelHex11); effectList1.Append(outerShadow1); effectStyle1.Append(effectList1); A.EffectStyle effectStyle2 = new A.EffectStyle(); A.EffectList effectList2 = new A.EffectList(); A.OuterShadow outerShadow2 = new A.OuterShadow() { BlurRadius = 40000L, Distance = 23000L, Direction = 5400000, RotateWithShape = false }; A.RgbColorModelHex rgbColorModelHex12 = new A.RgbColorModelHex() { Val = "000000" }; A.Alpha alpha2 = new A.Alpha() { Val = 35000 }; rgbColorModelHex12.Append(alpha2); outerShadow2.Append(rgbColorModelHex12); effectList2.Append(outerShadow2); effectStyle2.Append(effectList2); A.EffectStyle effectStyle3 = new A.EffectStyle(); A.EffectList effectList3 = new A.EffectList(); A.OuterShadow outerShadow3 = new A.OuterShadow() { BlurRadius = 40000L, Distance = 23000L, Direction = 5400000, RotateWithShape = false }; A.RgbColorModelHex rgbColorModelHex13 = new A.RgbColorModelHex() { Val = "000000" }; A.Alpha alpha3 = new A.Alpha() { Val = 35000 }; rgbColorModelHex13.Append(alpha3); outerShadow3.Append(rgbColorModelHex13); effectList3.Append(outerShadow3); A.Scene3DType scene3DType1 = new A.Scene3DType(); A.Camera camera1 = new A.Camera() { Preset = A.PresetCameraValues.OrthographicFront }; A.Rotation rotation1 = new A.Rotation() { Latitude = 0, Longitude = 0, Revolution = 0 }; camera1.Append(rotation1); A.LightRig lightRig1 = new A.LightRig() { Rig = A.LightRigValues.ThreePoints, Direction = A.LightRigDirectionValues.Top }; A.Rotation rotation2 = new A.Rotation() { Latitude = 0, Longitude = 0, Revolution = 1200000 }; lightRig1.Append(rotation2); scene3DType1.Append(camera1); scene3DType1.Append(lightRig1); A.Shape3DType shape3DType1 = new A.Shape3DType(); A.BevelTop bevelTop1 = new A.BevelTop() { Width = 63500L, Height = 25400L }; shape3DType1.Append(bevelTop1); effectStyle3.Append(effectList3); effectStyle3.Append(scene3DType1); effectStyle3.Append(shape3DType1); effectStyleList1.Append(effectStyle1); effectStyleList1.Append(effectStyle2); effectStyleList1.Append(effectStyle3); A.BackgroundFillStyleList backgroundFillStyleList1 = new A.BackgroundFillStyleList(); A.SolidFill solidFill5 = new A.SolidFill(); A.SchemeColor schemeColor11 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; solidFill5.Append(schemeColor11); A.GradientFill gradientFill3 = new A.GradientFill() { RotateWithShape = true }; A.GradientStopList gradientStopList3 = new A.GradientStopList(); A.GradientStop gradientStop7 = new A.GradientStop() { Position = 0 }; A.SchemeColor schemeColor12 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Tint tint4 = new A.Tint() { Val = 40000 }; A.SaturationModulation saturationModulation8 = new A.SaturationModulation() { Val = 350000 }; schemeColor12.Append(tint4); schemeColor12.Append(saturationModulation8); gradientStop7.Append(schemeColor12); A.GradientStop gradientStop8 = new A.GradientStop() { Position = 40000 }; A.SchemeColor schemeColor13 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Tint tint5 = new A.Tint() { Val = 45000 }; A.Shade shade5 = new A.Shade() { Val = 99000 }; A.SaturationModulation saturationModulation9 = new A.SaturationModulation() { Val = 350000 }; schemeColor13.Append(tint5); schemeColor13.Append(shade5); schemeColor13.Append(saturationModulation9); gradientStop8.Append(schemeColor13); A.GradientStop gradientStop9 = new A.GradientStop() { Position = 100000 }; A.SchemeColor schemeColor14 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Shade shade6 = new A.Shade() { Val = 20000 }; A.SaturationModulation saturationModulation10 = new A.SaturationModulation() { Val = 255000 }; schemeColor14.Append(shade6); schemeColor14.Append(saturationModulation10); gradientStop9.Append(schemeColor14); gradientStopList3.Append(gradientStop7); gradientStopList3.Append(gradientStop8); gradientStopList3.Append(gradientStop9); A.PathGradientFill pathGradientFill1 = new A.PathGradientFill() { Path = A.PathShadeValues.Circle }; A.FillToRectangle fillToRectangle1 = new A.FillToRectangle() { Left = 50000, Top = -80000, Right = 50000, Bottom = 180000 }; pathGradientFill1.Append(fillToRectangle1); gradientFill3.Append(gradientStopList3); gradientFill3.Append(pathGradientFill1); A.GradientFill gradientFill4 = new A.GradientFill() { RotateWithShape = true }; A.GradientStopList gradientStopList4 = new A.GradientStopList(); A.GradientStop gradientStop10 = new A.GradientStop() { Position = 0 }; A.SchemeColor schemeColor15 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Tint tint6 = new A.Tint() { Val = 80000 }; A.SaturationModulation saturationModulation11 = new A.SaturationModulation() { Val = 300000 }; schemeColor15.Append(tint6); schemeColor15.Append(saturationModulation11); gradientStop10.Append(schemeColor15); A.GradientStop gradientStop11 = new A.GradientStop() { Position = 100000 }; A.SchemeColor schemeColor16 = new A.SchemeColor() { Val = A.SchemeColorValues.PhColor }; A.Shade shade7 = new A.Shade() { Val = 30000 }; A.SaturationModulation saturationModulation12 = new A.SaturationModulation() { Val = 200000 }; schemeColor16.Append(shade7); schemeColor16.Append(saturationModulation12); gradientStop11.Append(schemeColor16); gradientStopList4.Append(gradientStop10); gradientStopList4.Append(gradientStop11); A.PathGradientFill pathGradientFill2 = new A.PathGradientFill() { Path = A.PathShadeValues.Circle }; A.FillToRectangle fillToRectangle2 = new A.FillToRectangle() { Left = 50000, Top = 50000, Right = 50000, Bottom = 50000 }; pathGradientFill2.Append(fillToRectangle2); gradientFill4.Append(gradientStopList4); gradientFill4.Append(pathGradientFill2); backgroundFillStyleList1.Append(solidFill5); backgroundFillStyleList1.Append(gradientFill3); backgroundFillStyleList1.Append(gradientFill4); formatScheme1.Append(fillStyleList1); formatScheme1.Append(lineStyleList1); formatScheme1.Append(effectStyleList1); formatScheme1.Append(backgroundFillStyleList1); themeElements1.Append(colorScheme1); themeElements1.Append(fontScheme1); themeElements1.Append(formatScheme1); A.ObjectDefaults objectDefaults1 = new A.ObjectDefaults(); A.ExtraColorSchemeList extraColorSchemeList1 = new A.ExtraColorSchemeList(); theme1.Append(themeElements1); theme1.Append(objectDefaults1); theme1.Append(extraColorSchemeList1); themePart1.Theme = theme1; } // Generates content of workbookStylesPart1. private void GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1) { 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 fonts1 = new Fonts() { Count = 5U, KnownFonts = true }; Font font1 = new Font(); FontSize fontSize3 = new FontSize() { Val = 11D }; Color color3 = new Color() { Theme = 1U }; FontName fontName1 = new FontName() { Val = "Calibri" }; FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering() { Val = 2 }; FontScheme fontScheme2 = new FontScheme() { Val = FontSchemeValues.Minor }; font1.Append(fontSize3); font1.Append(color3); font1.Append(fontName1); font1.Append(fontFamilyNumbering1); font1.Append(fontScheme2); Font font2 = new Font(); font2.Append(new Bold()); font2.Append(new FontSize() { Val = 12D }); font2.Append(new Color() { Theme = 1U }); font2.Append(new FontName() { Val = "Calibri" }); font2.Append(new FontFamilyNumbering() { Val = 2 }); font2.Append(new FontScheme() { Val = FontSchemeValues.Minor }); Font font3 = new Font(); Underline underline2 = new Underline(); FontSize fontSize5 = new FontSize() { Val = 11D }; Color color5 = new Color() { Theme = 10U }; FontName fontName3 = new FontName() { Val = "Calibri" }; FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering() { Val = 2 }; FontScheme fontScheme4 = new FontScheme() { Val = FontSchemeValues.Minor }; font3.Append(underline2); font3.Append(fontSize5); font3.Append(color5); font3.Append(fontName3); font3.Append(fontFamilyNumbering3); font3.Append(fontScheme4); Font font4 = new Font(); FontSize fontSize6 = new FontSize() { Val = 9D }; Color color6 = new Color() { Indexed = 81U }; FontName fontName4 = new FontName() { Val = "Tahoma" }; FontCharSet fontCharSet1 = new FontCharSet() { Val = 1 }; font4.Append(fontSize6); font4.Append(color6); font4.Append(fontName4); font4.Append(fontCharSet1); Font font5 = new Font(); Bold bold3 = new Bold(); FontSize fontSize7 = new FontSize() { Val = 9D }; Color color7 = new Color() { Indexed = 81U }; FontName fontName5 = new FontName() { Val = "Tahoma" }; FontCharSet fontCharSet2 = new FontCharSet() { Val = 1 }; font5.Append(bold3); font5.Append(fontSize7); font5.Append(color7); font5.Append(fontName5); font5.Append(fontCharSet2); fonts1.Append(font1); fonts1.Append(font2); fonts1.Append(font3); fonts1.Append(font4); fonts1.Append(font5); Fills fills1 = new Fills() { Count = 2U }; Fill fill1 = new Fill(); PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None }; fill1.Append(patternFill1); Fill fill2 = new Fill(); PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 }; fill2.Append(patternFill2); fills1.Append(fill1); fills1.Append(fill2); Borders borders1 = new Borders() { Count = 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); CellStyleFormats cellStyleFormats1 = new CellStyleFormats() { Count = 2U }; CellFormat cellFormat1 = new CellFormat() { NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U }; CellFormat cellFormat2 = new CellFormat() { NumberFormatId = 0U, FontId = 2U, FillId = 0U, BorderId = 0U, ApplyNumberFormat = false, ApplyFill = false, ApplyBorder = false, ApplyAlignment = false, ApplyProtection = false }; cellStyleFormats1.Append(cellFormat1); cellStyleFormats1.Append(cellFormat2); CellFormats cellFormats1 = new CellFormats() { Count = 4U }; var alignment1 = new Alignment { Horizontal = new EnumValue<HorizontalAlignmentValues>(HorizontalAlignmentValues.Center), Vertical = new EnumValue<VerticalAlignmentValues>(VerticalAlignmentValues.Center), WrapText = true }; var alignment2 = new Alignment { Horizontal = new EnumValue<HorizontalAlignmentValues>(HorizontalAlignmentValues.Center), Vertical = new EnumValue<VerticalAlignmentValues>(VerticalAlignmentValues.Center) }; CellFormat cellFormat3 = new CellFormat() { NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U, Alignment = alignment1, ApplyFont = true, ApplyAlignment = true }; CellFormat cellFormat4 = new CellFormat() { NumberFormatId = 0U, FontId = 1U, FillId = 0U, BorderId = 0U, FormatId = 0U, Alignment = alignment2, ApplyFont = true, ApplyAlignment = true }; cellFormats1.Append(cellFormat3); cellFormats1.Append(cellFormat4); CellStyles cellStyles1 = new CellStyles() { Count = 2U }; CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = 0U, BuiltinId = 0U }; CellStyle cellStyle2 = new CellStyle() { Name = "Header", FormatId = 1U, BuiltinId = 8U }; cellStyles1.Append(cellStyle1); cellStyles1.Append(cellStyle2); DifferentialFormats differentialFormats1 = new DifferentialFormats() { Count = 0U }; TableStyles tableStyles1 = new TableStyles() { Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" }; 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"); X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" }; stylesheetExtension1.Append(slicerStyles1); StylesheetExtension stylesheetExtension2 = new StylesheetExtension() { Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" }; stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"); X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles() { DefaultTimelineStyle = "TimeSlicerStyleLight1" }; stylesheetExtension2.Append(timelineStyles1); stylesheetExtensionList1.Append(stylesheetExtension1); stylesheetExtensionList1.Append(stylesheetExtension2); 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); workbookStylesPart1.Stylesheet = stylesheet1; } } }
1、创建SpreadsheetDocument，然后添加AddWorkbookPart和创建Workbook，这一步官方文档非常简洁，不知道为什么这里这么复杂，下面是官网创建Excel文件代码示例：
public static void CreateSpreadsheetWorkbook(string filepath) { // Create a spreadsheet document by supplying the filepath. // By default, AutoSave = true, Editable = true, and Type = xlsx. SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create(filepath, SpreadsheetDocumentType.Workbook); // Add a WorkbookPart to the document. WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild<Sheets>(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart. GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" }; sheets.Append(sheet); workbookpart.Workbook.Save(); // Close the document. spreadsheetDocument.Close(); }
2、定义主题，主要是为了定义主题色和字体，后续定义Color时指定主题色ID，而不是通过RBG色值；定义字体时指定字体ID。下面是相关代码：
ThemePart themePart1 = workbookPart1.AddNewPart<ThemePart>("rId3"); GenerateThemePart1Content(themePart1);
3、定义字体：
Stylesheet stylesheet1 = new Stylesheet(); Fonts fonts1 = new Fonts() { Count = 5U, KnownFonts = true }; Font font1 = new Font(); font1.Append(new FontSize() { Val = 11D }); font1.Append(new Color() { Theme = 1U }); font1.Append(new FontName() { Val = "Calibri" }); font1.Append(new FontFamilyNumbering() { Val = 2 }); font1.Append(new FontScheme() { Val = FontSchemeValues.Minor }); Font font2 = new Font(); font2.Append(new Bold()); font2.Append(new FontSize() { Val = 12D }); font2.Append(new Color() { Theme = 1U }); font2.Append(new FontName() { Val = "Calibri" }); font2.Append(new FontFamilyNumbering() { Val = 2 }); font2.Append(new FontScheme() { Val = FontSchemeValues.Minor }); fonts1.Append(font1); fonts1.Append(font2); stylesheet1.Append(fonts1);
4、定义填充：
Fills fills1 = new Fills() { Count = 2U }; Fill fill1 = new Fill(); PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None }; fill1.Append(patternFill1); Fill fill2 = new Fill(); PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 }; fill2.Append(patternFill2); fills1.Append(fill1); fills1.Append(fill2);
5、定义边框：
Borders borders1 = new Borders() { Count = 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);
6、定义单元格格式，这一步才是最重要的，定义了单元格的数字格式、对齐、字体、边框、填充、保护。
CellFormats cellFormats1 = new CellFormats() { Count = 4U }; var alignment1 = new Alignment { Horizontal = new EnumValue<HorizontalAlignmentValues>(HorizontalAlignmentValues.Center), Vertical = new EnumValue<VerticalAlignmentValues>(VerticalAlignmentValues.Center), WrapText = true }; var alignment2 = new Alignment { Horizontal = new EnumValue<HorizontalAlignmentValues>(HorizontalAlignmentValues.Center), Vertical = new EnumValue<VerticalAlignmentValues>(VerticalAlignmentValues.Center) }; CellFormat cellFormat3 = new CellFormat() { NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U, Alignment = alignment1, ApplyFont = true, ApplyAlignment = true }; CellFormat cellFormat4 = new CellFormat() { NumberFormatId = 0U, FontId = 1U, FillId = 0U, BorderId = 0U, FormatId = 0U, Alignment = alignment2, ApplyFont = true, ApplyAlignment = true }; cellFormats1.Append(cellFormat3); cellFormats1.Append(cellFormat4); CellStyles cellStyles1 = new CellStyles() { Count = 2U }; CellStyle cellStyle1 = new CellStyle() { Name = "Normal", FormatId = 0U, BuiltinId = 0U }; CellStyle cellStyle2 = new CellStyle() { Name = "Header", FormatId = 1U, BuiltinId = 8U }; cellStyles1.Append(cellStyle1); cellStyles1.Append(cellStyle2);
最后给单元格设置格式时，通过StyleIndex指定使用第几个CellStyle。
二、计算列宽，与Column这个类相关，至今我也没弄懂规则，以下是我简单得出的结论：
1、BestFit和CustomWidth肯定要设为True。
2、With要根据内容计算具体宽度，后面再详说。
3、Min和Max最是迷惑，根据经验，第几列就设为几就行。
下面是列宽的计算方式，因为字体和中英文的原因，自己不好计算，用到了SizeF System.Drawing.Graphics.MeasureString(string text, Font font)方法，最后得到的宽度再简单地除以一定的比例（7）即可，如果你觉得这个比例不对，应该跟字体数量或字体大小相关，可以在此基础上研究一套公式，下面是示例代码：
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); var worksheet = worksheetPart.Worksheet = new Worksheet(); var cellColumns = new Columns(); worksheet.Append(cellColumns); var graphics = Graphics.FromHwnd(IntPtr.Zero); var font = new System.Drawing.Font("宋体", 12); var j = 1U; foreach (DataColumn col in columns) { var text = col.ColumnName; var index = InsertSharedStringItem(text, sharedStringTablePart); var cell = new Cell { CellValue = new CellValue(index.ToString()), DataType = new EnumValue<CellValues>(CellValues.SharedString), StyleIndex = 1U }; head_row.Append(cell); cellColumns.Append(new Column { Min = j, Max = j, Width = graphics.MeasureString(text, font).Width / 7, BestFit = true, CustomWidth = true }); j++; }
三、最后是为快速导出Excel整出的一个工具类OpenXmlHelper.ExportToExcel(string filepath, params DataTable[] dts)，参数传入文件路径，如果存在就打开，否则创建，根据DataTable插入不同的Sheet表，根据DataTable.TableName命名Sheet.Name，根据DataTable.Columns插入表头，设置表头对应的单元格格式，最后根据DataTable.Rows插入行。
/// <summary> /// 导出Excel /// </summary> /// <param name="filepath"></param> /// <param name="dts"></param> /// <returns></returns> public static ResultValue ExportToExcel(string filepath, params DataTable[] dts) { var result = new ResultValue(); SpreadsheetDocument spreadsheetDocument = null; var graphics = Graphics.FromHwnd(IntPtr.Zero); try { var generatedSpreadsheetDocument = new GeneratedSpreadsheetDocument(); var exist = File.Exists(filepath); if (exist) spreadsheetDocument = SpreadsheetDocument.Open(filepath, true); else spreadsheetDocument = generatedSpreadsheetDocument.CreatePackage(filepath); var workbookPart = spreadsheetDocument.WorkbookPart ?? spreadsheetDocument.AddWorkbookPart(); if (workbookPart.Workbook == null) workbookPart.Workbook = new Workbook(); var wordbook = workbookPart.Workbook; var sheets = wordbook.GetFirstChild<Sheets>(); if (sheets == null) { sheets = new Sheets(); wordbook.Append(sheets); } foreach (var dt in dts) { var worksheetPart = workbookPart.AddNewPart<WorksheetPart>(); var worksheet = worksheetPart.Worksheet = new Worksheet(); var sheetId = sheets.Elements<Sheet>().Count() == 0 ? 1 : sheets.Elements<Sheet>().Max(q => q.SheetId.Value) + 1; var sheetName = dt.TableName; var i = 1; while (sheets.Elements<Sheet>().Any(q => q.Name == sheetName)) { sheetName = $"{dt.TableName}({i++})"; } var sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetId, Name = sheetName }; sheets.Append(sheet); var sheetData = new SheetData(); worksheet.Append(sheetData); var cellColumns = new Columns(); worksheet.Append(cellColumns); var sharedStringTablePart = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault() ?? workbookPart.AddNewPart<SharedStringTablePart>(); var columns = dt.Columns; var head_row = new Row(); var j = 1U; var font = new System.Drawing.Font("宋体", 12); foreach (DataColumn col in columns) { var text = col.ColumnName; var index = InsertSharedStringItem(text, sharedStringTablePart); var cell = new Cell { CellValue = new CellValue(index.ToString()), DataType = new EnumValue<CellValues>(CellValues.SharedString), StyleIndex = 1U }; head_row.Append(cell); cellColumns.Append(new Column { Min = j, Max = j, Width = graphics.MeasureString(text, font).Width / 7, BestFit = true, CustomWidth = true }); j++; } sheetData.Append(head_row); font = new System.Drawing.Font("宋体", 11); foreach (DataRow dr in dt.Rows) { var row = new Row(); i = 0; foreach (DataColumn col in columns) { var dc = dr[col.ColumnName]; var text = dc?.ToString() ?? String.Empty; var index = InsertSharedStringItem(text, sharedStringTablePart); var cell = new Cell { CellValue = new CellValue(index.ToString()), DataType = new EnumValue<CellValues>(CellValues.SharedString), StyleIndex = 0U }; row.Append(cell); var w = text.Split(Environment.NewLine.ToCharArray()).Max(q => graphics.MeasureString(q, font).Width / 7); var column = cellColumns.Elements<Column>().ElementAt(i++); if (column.Width.Value < w) column.Width = w; } sheetData.Append(row); } } spreadsheetDocument.Close(); result.IsSucc = true; } catch (Exception ex) { result.Msg = "导出Excel失败，错误原因：" + ex.GetBaseException().Message; } finally { graphics.Dispose(); spreadsheetDocument?.Dispose(); } return result; } // Given text and a SharedStringTablePart, creates a SharedStringItem with the specified text // and inserts it into the SharedStringTablePart. If the item already exists, returns its index. private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart) { // If the part does not contain a SharedStringTable, create one. if (shareStringPart.SharedStringTable == null) shareStringPart.SharedStringTable = new SharedStringTable(); var i = 0; // Iterate through all the items in the SharedStringTable. If the text already exists, return its index. foreach (var item in shareStringPart.SharedStringTable.Elements<SharedStringItem>()) { if (item.InnerText == text) return i; i++; } // The text does not exist in the part. Create the SharedStringItem and return its index. shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new Text(text))); return i; }
