c# – 使用openxml将背景颜色应用于excel中的特定单元格

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,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",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(),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),DataType = CellValues.String });            
        }
        private static void AppendTextCell1(string cellReference,DataType = CellValues.String });
            writer.WriteElement(new Cell(new CellValue(cellStringValue)) { CellReference = cellReference,DataType = CellValues.String,StyleIndex = 2 });
        }

        private static void AppendNumericCell(string cellReference,ref OpenXmlWriter writer)
        {
            //  Add a new Excel Cell to our Row 
            writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue),DataType = CellValues.Number });
        }

以上是我的代码.我尝试通过填写样式表来添加颜色,但是我不理解styleindex应用于单元格的概念.请帮忙.