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

概述以下是我的代码,在下面的最终方法中,我做了所有的 *** 作.请忽略方法的返回类型,我稍后会更改它. public static byte[] CreateExcelDocument<T>(List<T> list, string filename) { DataSet ds = new DataSet(); ds.Tables.Add(Li 以下是我的代码,在下面的最终方法中,我做了所有的 *** 作.请忽略方法的返回类型,我稍后会更改它.

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应用于单元格的概念.请帮忙.

解决方法 这个 MSDN blog for Stylizing Excel拥有您需要的信息.

使用Open XML 2.0的Stylizing Excel工作表通过更改格式适用于背景颜色.

总结

以上是内存溢出为你收集整理的c# – 使用openxml将背景颜色应用于excel中的特定单元格全部内容，希望文章能够帮你解决c# – 使用openxml将背景颜色应用于excel中的特定单元格所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。