NPOI 自定义单元格背景颜色-Excel
NPOI 自定义单元格背景颜色-Excel
2016-12-27 10:44 by 杨新华, 6682 阅读, 2 评论, 收藏, 编辑
NPOI针对office2003使用HSSFWorkbook，对于offce2007及以上使用XSSFWorkbook；今天我以HSSFWorkbook自定义颜色为例说明，Office2007的未研究呢
在NPOI中默认的颜色类是HSSFColor，它内置的颜色有几十种供我们选择，如果不够怎么办，不能修改底层的HSSFColor类；
大概解决思路：
1、将颜色的RGB值添加进调色板HSSFPalette中
2、调用HSSFPalette中FindColor方法获取HSSFColor实例
3、在需要使用颜色的地方使用HSSFColor的Indexed属性获取index值（NPOI.dll版本不同，也可能是GetIndex方法节能Index值）
第一步：将颜色的RGB值添加进调色板HSSFPalette
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//工作簿实例
HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例
palette.SetColorAtIndex((short)8 ,(short)184,(short)204,(short)228);
参数解析
第一个参数：设置调色板新增颜色的编号，自已设置即可；取值范围8-64
第二、第三、第四个参数，组成RGB值
这里还有一点需要注意，如何去找到相应的RGB值呢，如果去用肉眼去找到匹配的太难；
可以下载调色板来拾取，也可以通过Excel固定模板去取，本次我讲解通过Excel去取颜色
客户可能给某个模板，模板中自带背景色，我们如何找到这个前景色的RGB值呢，通过以下方式
第二步：调用HSSFPalette中FindColor方法获取HSSFColor实例
HSSFColor hssFColor = palette.FindColor((short)184,(short)204,(short)228);
第一步中自定义设置的RGB值，直接复制过来使用；通过FindColor直接找到HSSFColor实例
第三步：设置具体对象颜色
//创建一个单元格(具体网上查查，本次不做重点讲解)
ISheet sheet = hssfWorkbook.GetSheetAt(0);
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
//设置单元格颜色
ICellStyle cellStyle= hssfWorkbook.CreateCellStyle();
cellStyle.FillPattern = FillPattern.SolidForeground; 老版本可能这样写FillPatternType.SOLID_FOREGROUND;
cellStyle.FillForegroundColor = hssFColor.Indexed;
cell.CellStyle = cellStyle;//设置
好像不能用在模板上(模板上有颜色 可能就会冲突)
public FileResult GetatmkeytableExport() { string CityName = Request["CityName[]"] + ""; string CustomerType = Request["CustomerType[]"] + ""; string startdate2 = Request["startdate2"] + ""; string enddate2 = Request["enddate2"] + ""; //SortByDocument sbd = new SortByDocument(); //IMongoQuery query = null; //List<IMongoQuery> list = new List<IMongoQuery>(); //List<IMongoQuery> mq0 = new List<IMongoQuery>(); //if (!string.IsNullOrEmpty(CityName)) //{ // foreach (var item in CityName.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) // { // mq0.Add(Query.Matches("Branch", item)); // } // list.Add(Query.Or(mq0)); //} //List<IMongoQuery> mq1 = new List<IMongoQuery>(); //if (!string.IsNullOrEmpty(CustomerType)) //{ // foreach (var item in CustomerType.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) // { // mq1.Add(Query.Matches("Bank", item)); // } // list.Add(Query.Or(mq1)); //} //List<IMongoQuery> mq2 = new List<IMongoQuery>(); //if (!string.IsNullOrEmpty(Area)) //{ // foreach (var item in Area.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) // { // mq2.Add(Query.Matches("Province", item)); // } // list.Add(Query.Or(mq2)); //} //List<IMongoQuery> mq3 = new List<IMongoQuery>(); //List<IMongoQuery> list2 = new List<IMongoQuery>(); //if (!string.IsNullOrEmpty(month)) //{ // foreach (var item in month.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) // { // mq3.Add(Query.EQ("JobMonth", item.ToInt())); // } // list.Add(Query.Or(mq3)); // list2.Add(Query.Or(mq3)); //} //else //{ // list.Add(Query.GTE("JobMonth", 1)); // list.Add(Query.LT("JobMonth", 13)); // list2.Add(Query.GTE("JobMonth", 1)); // list2.Add(Query.LT("JobMonth", 13)); // //list.Add(); // //list2.Add(); //} //List<IMongoQuery> mq4 = new List<IMongoQuery>(); //if (!string.IsNullOrEmpty(ServiceType)) //{ // foreach (var item in ServiceType.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) // { // mq4.Add(Query.EQ("IsNon", Convert.ToBoolean(item.ToInt()))); // } // list.Add(Query.Or(mq4)); //} //List<IMongoQuery> mq5 = new List<IMongoQuery>(); //if (!string.IsNullOrEmpty(AtmTypeName)) //{ // foreach (var item in AtmTypeName.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) // { // mq5.Add(Query.EQ("JobType", item.ToInt())); // } // list.Add(Query.Or(mq5)); //} //query = Query.And(list); //List<ATMSummaryATMNumber> atmsn = new List<ATMSummaryATMNumber>(); //atmsn = Common.MongodbHelper.FindListBy<ATMSummaryATMNumber>(query, sbd, "table_ATMSummaryATMNumber"); //// ////clo //SortByDocument sbd2 = new SortByDocument(); //IMongoQuery query2 = null; ////list1.Add(Query.EQ("JobMonth", st.Month)); //query2 = Query.And(list2); //List<ATMSummaryATMNumberClo> atmsnclo = new List<ATMSummaryATMNumberClo>(); //atmsnclo = Common.MongodbHelper.FindListBy<ATMSummaryATMNumberClo>(query2, sbd2, "table_ATMSummaryATMNumberClo"); ////excel //string configurl = System.Configuration.ConfigurationSettings.AppSettings["TempletsPath"].ToString(); //string FileFullPath = Server.MapPath(configurl) + "ATMnumberseparateMonth.xls"; //if (!System.IO.File.Exists(FileFullPath)) //{ // return null; //} //HSSFWorkbook workbook = new HSSFWorkbook(); //using (FileStream file = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read)) //{ // workbook = (HSSFWorkbook)WorkbookFactory.Create(file); //} //ICellStyle cstyHEAD = (HSSFCellStyle)(workbook.CreateCellStyle()); //// //cstyHEAD.FillForegroundColor = IndexedColors.GOLD.Index; ////TAN BRIGHT_GREEN //cstyHEAD.FillPattern = FillPatternType.SOLID_FOREGROUND; //// //cstyHEAD.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐 //cstyHEAD.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐 ////(BorderStyle)CellBorderType.THIN; //cstyHEAD.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; //cstyHEAD.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; //cstyHEAD.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; //cstyHEAD.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; //HSSFFont f = (HSSFFont)workbook.CreateFont(); //f.FontHeightInPoints = 9; //f.FontName = "Arial"; //cstyHEAD.SetFont(f); //// //ICellStyle cstybule = (HSSFCellStyle)(workbook.CreateCellStyle()); //// //cstybule.FillForegroundColor = IndexedColors.BRIGHT_GREEN.Index; ////TAN BRIGHT_GREEN //cstybule.FillPattern = FillPatternType.SOLID_FOREGROUND; //// ////cstybule.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐 ////cstybule.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐 ////(BorderStyle)CellBorderType.THIN; //cstybule.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; //cstybule.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; //cstybule.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; //cstybule.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; //cstybule.SetFont(f); //// //ICellStyle cstyHEADnonborder = (HSSFCellStyle)(workbook.CreateCellStyle()); //cstyHEADnonborder.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐 //cstyHEADnonborder.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐 ////(BorderStyle)CellBorderType.THIN; //HSSFFont f2 = (HSSFFont)workbook.CreateFont(); //f2.Boldweight = short.MaxValue; //f2.FontHeightInPoints = 9; //f2.FontName = "Arial"; //cstyHEADnonborder.SetFont(f2); //ICellStyle cstyHEADnonborder3 = (HSSFCellStyle)(workbook.CreateCellStyle()); //cstyHEADnonborder3.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐 //cstyHEADnonborder3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐 ////(BorderStyle)CellBorderType.THIN; //HSSFFont f3 = (HSSFFont)workbook.CreateFont(); //f3.FontHeightInPoints = 9; //f3.FontName = "Arial"; //cstyHEADnonborder3.SetFont(f3); //// //var sheet = 0; //List<ATMSummaryATMNumber> asantmp = new List<ATMSummaryATMNumber>(); //List<ATMSummaryATMNumberClo> asanclotmp = new List<ATMSummaryATMNumberClo>(); //if (month == "") //{ // month = "01,02,03,04,05,06,07,08,09,10,11,12"; //} //foreach (var item in month.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) //{ // asantmp = atmsn.Where(a => a.JobMonth == item.ToInt()).ToList(); // asanclotmp = atmsnclo.Where(a => a.JobMonth == item.ToInt()).ToList(); // if (asantmp.Count > 0 && asanclotmp.Count > 0) // { // workbook.CreateSheet(item); // HSSFSheet sheet1 = (HSSFSheet)workbook.GetSheetAt(++sheet); // for (int i = 0; i <= asantmp.Count + 9 + 4; i++) // {//行 构建 出excel 结构 // CreateRow(sheet1, i); // for (int z = 0; z <= asanclotmp.Count + 5; z++) // {//列 // CreateCell(sheet1, i, z); // //((HSSFRow)sheet1.GetRow(i)).GetCell(z).CellStyle = cstyHEAD; // //((HSSFRow)sheet1.GetRow(i)).GetCell(z).SetCellValue("1"); // } // } // sheet1.GetRow(3).Height = 600; // sheet1.GetRow(6).Height = 600; // sheet1.GetRow(7).Height = 600; // sheet1.GetRow(8).Height = 600; // //((HSSFRow)sheet1.GetRow(i)).GetCell(z). // //((HSSFRow)sheet1.GetRow(data1)).GetCell(1).SetCellValue(item["prname"].ToStr()); // sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 3, 22)); // sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 3, 22)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(0, 0, 3, 22), cstyHEADnonborder, sheet1); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(1, 1, 3, 22), cstyHEADnonborder, sheet1); // ((HSSFRow)sheet1.GetRow(0)).GetCell(3).SetCellValue("Guardforce Cash Solutions (Limited)"); // ((HSSFRow)sheet1.GetRow(1)).GetCell(3).SetCellValue("Updated Number of ATM - " + item + " " + DateTime.Now.Year); // sheet1.AddMergedRegion(new CellRangeAddress(6, 8, 0, 0)); // sheet1.AddMergedRegion(new CellRangeAddress(6, 8, 1, 1)); // sheet1.AddMergedRegion(new CellRangeAddress(6, 8, 2, 2)); //cstyHEAD // //((HSSFRow)sheet1.GetRow(6)).GetCell(0).CellStyle = cstyHEAD; // //((HSSFRow)sheet1.GetRow(6)).GetCell(1).CellStyle = cstyHEAD; // //((HSSFRow)sheet1.GetRow(6)).GetCell(2).CellStyle = cstyHEAD; // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(6, 8, 0, 0), cstyHEAD, sheet1); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(6, 8, 1, 1), cstyHEAD, sheet1); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(6, 8, 2, 2), cstyHEAD, sheet1); // ((HSSFRow)sheet1.GetRow(6)).GetCell(0).SetCellValue("NO.\nลำดับ"); // ((HSSFRow)sheet1.GetRow(6)).GetCell(1).SetCellValue("BRANCH\nสาขา"); // ((HSSFRow)sheet1.GetRow(6)).GetCell(2).SetCellValue("Service Area\nพื้นที่ให้บริการ"); // var bankstmp = asanclotmp.Distinct(a => a.Bank); // int bansinfos = 3; // foreach (var bankvartmp in bankstmp) // { // var bansinfo = asanclotmp.Where(a => a.Bank == bankvartmp.Bank); // //var bansinfoe = bansinfos + bansinfo - 1; // sheet1.AddMergedRegion(new CellRangeAddress(6, 6, bansinfos, bansinfos + bansinfo.Count() - 1)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(6, 6, bansinfos, bansinfos + bansinfo.Count() - 1), cstyHEAD, sheet1); // ((HSSFRow)sheet1.GetRow(6)).GetCell(bansinfos).SetCellValue(bankvartmp.Bank); // ((HSSFRow)sheet1.GetRow(6)).GetCell(bansinfos).CellStyle = cstyHEAD; // var bansisnon = bansinfo.Distinct(a => a.IsNon).ToList(); // var isnonclo = bansinfos; // foreach (var isnon in bansisnon) // { // if (isnon.IsNon) // { // var isnoninfo = bansinfo.Where(a => a.IsNon); // //isnon // sheet1.AddMergedRegion(new CellRangeAddress(7, 7, isnonclo, isnonclo + isnoninfo.Count() - 1)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(7, 7, isnonclo, isnonclo + isnoninfo.Count() - 1), cstyHEAD, sheet1); // ((HSSFRow)sheet1.GetRow(7)).GetCell(isnonclo).SetCellValue("ATM - DV\nเช่าเหมา"); // ((HSSFRow)sheet1.GetRow(7)).GetCell(isnonclo).CellStyle = cstyHEAD; // var jobis = 0; // foreach (var jobtypes in isnoninfo) // { // var jobtypename = ""; // switch (jobtypes.JobType) // { // case 1: // jobtypename = "CDM"; // break; // case 2: // jobtypename = "ATM"; // break; // case 3: // jobtypename = "RATM"; // break; // } // ((HSSFRow)sheet1.GetRow(8)).GetCell(isnonclo + jobis).SetCellValue(jobtypename); // ((HSSFRow)sheet1.GetRow(8)).GetCell(isnonclo + jobis).CellStyle = cstyHEAD; // jobis++; // } // isnonclo += (isnoninfo.Count()); // } // else // { // var isnoninfo = bansinfo.Where(a => !a.IsNon); // sheet1.AddMergedRegion(new CellRangeAddress(7, 7, isnonclo, isnonclo + isnoninfo.Count() - 1)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(7, 7, isnonclo, isnonclo + isnoninfo.Count() - 1), cstyHEAD, sheet1); // ((HSSFRow)sheet1.GetRow(7)).GetCell(isnonclo).SetCellValue("ATM Per Trip\nรายเที่ยว"); // ((HSSFRow)sheet1.GetRow(7)).GetCell(isnonclo).CellStyle = cstyHEAD; // var jobis = 0; // foreach (var jobtypes in isnoninfo) // { // var jobtypename = ""; // switch (jobtypes.JobType) // { // case 1: // jobtypename = "CDM"; // break; // case 2: // jobtypename = "ATM"; // break; // case 3: // jobtypename = "RATM"; // break; // } // ((HSSFRow)sheet1.GetRow(8)).GetCell(isnonclo + jobis).SetCellValue(jobtypename); // ((HSSFRow)sheet1.GetRow(8)).GetCell(isnonclo + jobis).CellStyle = cstyHEAD; // jobis++; // } // isnonclo += (isnoninfo.Count()); // } // } // //((HSSFRow)sheet1.GetRow(7)).GetCell(bansinfos).SetCellValue(bankvartmp.Bank); // bansinfos += (bansinfo.Count()); // } // //total // //-- // //bansinfos++; // sheet1.AddMergedRegion(new CellRangeAddress(6, 8, bansinfos, bansinfos)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(6, 8, bansinfos, bansinfos), cstyHEAD, sheet1); // ((HSSFRow)sheet1.GetRow(6)).GetCell(bansinfos).SetCellValue("TOTAL"); // //var branchGrou = asantmp.GroupBy(a => new { a.Branch,a.Province }).ToList(); // key var value List(item asantmp) // //var branchGrous = asantmp.Distinct(a => new { a.Branch, a.Province }).ToList(); // var branchGrous = asantmp.GroupBy(a => new { a.Branch, a.Province }).ToList(); // var notmp = 0; // var notrow = 9; // //var branchGrou = branchGrous.GroupBy(a => new { a.Branch, a.Province }).ToList(); // var zuih = 0; // foreach (var brhGrous in asantmp.Distinct(a => a.Branch)) // key城市 // { // var brhGrou = branchGrous.Where(a => a.Key.Branch == brhGrous.Branch); // sheet1.AddMergedRegion(new CellRangeAddress(notrow, (notrow) + brhGrou.Count() - 1, 0, 0)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(notrow, notrow + brhGrou.Count() - 1, 0, 0), cstyHEAD, sheet1); // sheet1.AddMergedRegion(new CellRangeAddress(notrow, notrow + brhGrou.Count() - 1, 1, 1)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(notrow, notrow + brhGrou.Count() - 1, 1, 1), cstyHEAD, sheet1); // ((HSSFRow)sheet1.GetRow(notrow)).GetCell(0).SetCellValue(++notmp); // ((HSSFRow)sheet1.GetRow(notrow)).GetCell(1).SetCellValue(brhGrous.Branch); // var totalflo = 0; // var totalflocoun = 0; // foreach (var itempro in brhGrou) // { // ((HSSFRow)sheet1.GetRow(notrow)).GetCell(2).SetCellValue(itempro.Key.Province); // //写数据 // var databij = 2; // var total = 0; // foreach (var itemdata in asanclotmp) // {//asantmp // var datas = asantmp.Where(a => a.Branch == brhGrous.Branch && a.Province == itempro.Key.Province && a.Bank == itemdata.Bank && a.IsNon == itemdata.IsNon && a.JobType == itemdata.JobType); // ((HSSFRow)sheet1.GetRow(notrow)).GetCell(++databij).SetCellValue(datas == null ? 0 : datas.Sum(a => a.Number)); // total += (datas == null ? 0 : datas.Sum(a => a.Number)); // } // var cellsdsds = ((HSSFRow)sheet1.GetRow(notrow)).GetCell(++databij); // cellsdsds.SetCellValue(total); // cellsdsds.CellStyle = cstyHEAD; // totalflo = ++databij; // totalflocoun += total; // notrow++; // } // ((HSSFRow)sheet1.GetRow(notrow - 1)).GetCell(totalflo).CellStyle = cstybule; // ((HSSFRow)sheet1.GetRow(notrow - 1)).GetCell(totalflo).SetCellValue(totalflocoun); // zuih = notrow; // //notrow += brhGrou.Count(); // } // //最后 // sheet1.AddMergedRegion(new CellRangeAddress(zuih, zuih, 0, 2)); // ((HSSFRow)sheet1.GetRow(zuih)).GetCell(0).SetCellValue("TOTAL of " + item); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(zuih, zuih, 0, 2), cstyHEAD, sheet1); // var zuihourow = 2; // var zuihourowtot = 0; // foreach (var zuihou in asanclotmp) // { // var clocount = asantmp.Where(a => a.Bank == zuihou.Bank && a.IsNon == zuihou.IsNon && a.JobType == zuihou.JobType); // ICell cel = ((HSSFRow)sheet1.GetRow(zuih)).GetCell(++zuihourow); // cel.SetCellValue(clocount != null ? clocount.Sum(a => a.Number) : 0); // cel.CellStyle = cstyHEAD; // zuihourowtot += (clocount != null ? clocount.Sum(a => a.Number) : 0); // } // sheet1.AddMergedRegion(new CellRangeAddress(zuih, zuih + 1, zuihourow + 1, zuihourow + 1)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(zuih, zuih + 1, zuihourow + 1, zuihourow + 1), cstyHEAD, sheet1); //先设置格式 再填值 // ((HSSFRow)sheet1.GetRow(zuih)).GetCell(++zuihourow).SetCellValue(zuihourowtot); // zuih++; // sheet1.AddMergedRegion(new CellRangeAddress(zuih, zuih, 0, 2)); // ((HSSFRow)sheet1.GetRow(zuih)).GetCell(0).SetCellValue("GRAND TOTAL"); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(zuih, zuih, 0, 2), cstyHEAD, sheet1); // int bansinfoszh = 3; // foreach (var itemzh in bankstmp) // { // var bansinfo = asanclotmp.Where(a => a.Bank == itemzh.Bank); // var bansisnon = bansinfo.Distinct(a => a.IsNon).ToList(); // foreach (var isnon in bansisnon) // { // if (isnon.IsNon) // { // var isnoninfo = bansinfo.Where(a => a.IsNon); // sheet1.AddMergedRegion(new CellRangeAddress(zuih, zuih, bansinfoszh, bansinfoszh + isnoninfo.Count() - 1)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(zuih, zuih, bansinfoszh, bansinfoszh + isnoninfo.Count() - 1), cstyHEAD, sheet1); // var datazh = asantmp.Where(a => a.Bank == itemzh.Bank && a.IsNon == isnon.IsNon); // ((HSSFRow)sheet1.GetRow(zuih)).GetCell(bansinfoszh).SetCellValue(datazh != null ? datazh.Sum(a => a.Number) : 0); // bansinfoszh += (isnoninfo.Count()); // } // else // { // var isnoninfo = bansinfo.Where(a => !a.IsNon); // sheet1.AddMergedRegion(new CellRangeAddress(zuih, zuih, bansinfoszh, bansinfoszh + isnoninfo.Count() - 1)); // NPOIHelper.SetMergedRegionStyle(new CellRangeAddress(zuih, zuih, bansinfoszh, bansinfoszh + isnoninfo.Count() - 1), cstyHEAD, sheet1); // var datazh = asantmp.Where(a => a.Bank == itemzh.Bank && a.IsNon == isnon.IsNon); // ((HSSFRow)sheet1.GetRow(zuih)).GetCell(bansinfoszh).SetCellValue(datazh != null ? datazh.Sum(a => a.Number) : 0); // bansinfoszh += (isnoninfo.Count()); // } // } // } // } //} //System.IO.MemoryStream ms = new System.IO.MemoryStream(); //workbook.Write(ms); HSSFWorkbook workbook = new HSSFWorkbook(); { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "stander"; //填加xls文件作者信息 si.ApplicationName = "stander"; //填加xls文件创建程序信息 si.LastAuthor = "stander"; //填加xls文件最后保存者信息 si.Comments = "stander"; //填加xls文件作者信息 si.Title = LangResources.Resource.Tab_StatusOfATMOrder; //填加xls文件标题信息 si.Subject = LangResources.Resource.Tab_StatusOfATMOrder;//填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex((short)8, 184, 204, 228); //添加 palette.SetColorAtIndex((short)9, 27, 27, 150);//添加 palette.SetColorAtIndex((short)10, 255, 9, 55);//添加 // ISheet sheet = workbook.CreateSheet("adas"); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); //1 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; //老版本可能这样写FillPatternType.SOLID_FOREGROUND; HSSFColor hssFColor = palette.FindColor(184, 204, 228); cellStyle.FillForegroundColor = hssFColor.GetIndex(); //2 ICellStyle cellStyle2 = workbook.CreateCellStyle(); cellStyle2.FillPattern = FillPatternType.SOLID_FOREGROUND; cellStyle2.FillForegroundColor = palette.FindColor(27, 27, 150).GetIndex(); //3 ICellStyle cellStyle3 = workbook.CreateCellStyle(); cellStyle3.FillPattern = FillPatternType.SOLID_FOREGROUND; cellStyle3.FillForegroundColor = palette.FindColor(255, 9, 55).GetIndex(); cell.CellStyle = cellStyle;//设置 cell = row.CreateCell(1); // cell.CellStyle = cellStyle2;//设置 // cell = row.CreateCell(2); // cell.CellStyle = cellStyle3;//设置 sheet.TabColorIndex = NPOI.HSSF.Util.HSSFColor.BLUE_GREY.index; //设置 tab 颜色 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); string dateTime = DateTime.Today.ToString("ddMMyyyy");//yyMMddHHmmssfff string fileName = "ATM number separate by month" + dateTime + ".xls"; return File(ms, "application/vnd.ms-excel", fileName); }
不调调色板 但只有固定几种颜色
public FileResult GetExport(string sdtstr, string edtstre) { string branch = Request["branch"]; List<Summary> data = GetData(sdtstr, edtstre, branch); #region excel string configurl = System.Configuration.ConfigurationSettings.AppSettings["TempletsPath"].ToString(); string FileFullPath = Server.MapPath(configurl) + "DispatchReport_Summary.xls"; if (!System.IO.File.Exists(FileFullPath)) { return null; } HSSFWorkbook workbook = new HSSFWorkbook(); using (FileStream file = new FileStream(FileFullPath, FileMode.Open, FileAccess.Read)) { workbook = (HSSFWorkbook)WorkbookFactory.Create(file); } HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); // ICellStyle cstyHEAD = (HSSFCellStyle)(workbook.CreateCellStyle()); //csty.FillForegroundColor = HSSFColor.PINK.index2; //cstyHEAD.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index; //cstyHEAD.FillForegroundColor = IndexedColors.AQUA.Index; cstyHEAD.FillForegroundColor = IndexedColors.YELLOW.Index; //IndexedColors.AQUA.inde cstyHEAD.FillPattern = FillPatternType.SOLID_FOREGROUND; cstyHEAD.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐 cstyHEAD.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐 cstyHEAD.BorderBottom = (BorderStyle)CellBorderType.THIN; cstyHEAD.BorderLeft = (BorderStyle)CellBorderType.THIN; cstyHEAD.BorderRight = (BorderStyle)CellBorderType.THIN; cstyHEAD.BorderTop = (BorderStyle)CellBorderType.THIN; HSSFFont f = (HSSFFont)workbook.CreateFont(); //f.Boldweight = (short)FontBoldWeight.BOLD; //f.Boldweight = 600; f.FontHeightInPoints = 14; f.FontName = "Cordia New"; //font.Color =HSSFColor.RED.index; f.Boldweight = short.MaxValue; cstyHEAD.SetFont(f); //---- #region 定义内容 int data1 = 4; IRow row; if (data.Count > 1) { row = sheet.GetRow(4); //第二行 源 NPOIHelper.MyInsertRow(sheet, 5, (data.Count - 1 + data.Select(a => a.Branck).Distinct().Count()), (HSSFRow)row); // 3 -- count - 1 第一行到最后一行 建模版 } foreach (string bran in data.Select(a => a.Branck).Distinct().ToList()) { foreach (var item in data.Where(a => a.Branck == bran)) { //CreateRow(sheet, data1); CreateCell(sheet, data1, 0); ((HSSFRow)sheet.GetRow(data1)).GetCell(0).SetCellValue(item.Branck); CreateCell(sheet, data1, 1); ((HSSFRow)sheet.GetRow(data1)).GetCell(1).SetCellValue(item.Item); CreateCell(sheet, data1, 2); ((HSSFRow)sheet.GetRow(data1)).GetCell(2).SetCellValue(item.Date.Value.ToString("dd-MM-yyyy")); CreateCell(sheet, data1, 3); ((HSSFRow)sheet.GetRow(data1)).GetCell(3).SetCellValue(item.Total); CreateCell(sheet, data1, 4); ((HSSFRow)sheet.GetRow(data1)).GetCell(4).SetCellValue(item.Issued); CreateCell(sheet, data1, 5); ((HSSFRow)sheet.GetRow(data1)).GetCell(5).SetCellValue(item.Returned); CreateCell(sheet, data1, 6); ((HSSFRow)sheet.GetRow(data1)).GetCell(6).SetCellValue(item.Damaged); data1++; } //CreateRow(sheet, data1); CreateCell(sheet, data1, 0); ((HSSFRow)sheet.GetRow(data1)).GetCell(0).SetCellValue(""); ((HSSFRow)sheet.GetRow(data1)).GetCell(0).CellStyle = cstyHEAD; ((HSSFRow)sheet.GetRow(data1)).GetCell(1).CellStyle = cstyHEAD; ((HSSFRow)sheet.GetRow(data1)).GetCell(2).CellStyle = cstyHEAD; ((HSSFRow)sheet.GetRow(data1)).GetCell(3).CellStyle = cstyHEAD; ((HSSFRow)sheet.GetRow(data1)).GetCell(4).CellStyle = cstyHEAD; ((HSSFRow)sheet.GetRow(data1)).GetCell(5).CellStyle = cstyHEAD; ((HSSFRow)sheet.GetRow(data1)).GetCell(6).CellStyle = cstyHEAD; CreateCell(sheet, data1, 1); ((HSSFRow)sheet.GetRow(data1)).GetCell(1).SetCellValue(""); CreateCell(sheet, data1, 2); sheet.AddMergedRegion(new CellRangeAddress(data1, data1, 0, 2)); //((HSSFRow)sheet.GetRow(data1)).GetCell(0). ((HSSFRow)sheet.GetRow(data1)).GetCell(0).SetCellValue("Total"); CreateCell(sheet, data1, 3); ((HSSFRow)sheet.GetRow(data1)).GetCell(3).SetCellValue(data.Where(a => a.Branck == bran).Sum(a => a.Total)); CreateCell(sheet, data1, 4); ((HSSFRow)sheet.GetRow(data1)).GetCell(4).SetCellValue(data.Where(a => a.Branck == bran).Sum(a => a.Issued)); CreateCell(sheet, data1, 5); ((HSSFRow)sheet.GetRow(data1)).GetCell(5).SetCellValue(data.Where(a => a.Branck == bran).Sum(a => a.Returned)); CreateCell(sheet, data1, 6); ((HSSFRow)sheet.GetRow(data1)).GetCell(6).SetCellValue(data.Where(a => a.Branck == bran).Sum(a => a.Damaged)); data1++; } #endregion System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); //string dateTime = DateTime.Today.ToString("ddMMyyyy");//yyMMddHHmmssfff string dateTime = sdtstr.ToDateTime().ToString("ddMMyyyy");//yyMMddHHmmssfff string fileName = "DispatchReport_Summary" + dateTime + ".xls"; return File(ms, "application/vnd.ms-excel", fileName); }
//
//HSSFPalette palette = workbook.GetCustomPalette();
//palette.SetColorAtIndex((short)(0x8), 196, 215, 155);
//var v1 = palette.FindColor(196, 215, 155);
////HSSFCellStyle csty = (HSSFCellStyle)(workbook.CreateCellStyle());
////csty.FillBackgroundColor = HSSFColor.RED.index;
////new HSSFCellStyle();
//ICellStyle csty = workbook.CreateCellStyle();
////csty.FillForegroundColor = v1.GetIndex();
//csty.FillPattern = FillPatternType.SOLID_FOREGROUND;
//csty.FillForegroundColor = v1.GetIndex();
//csty.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
//csty.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐
ICellStyle csty = (HSSFCellStyle)(workbook.CreateCellStyle());
//csty.FillForegroundColor = HSSFColor.PINK.index2;
csty.FillForegroundColor = IndexedColors.LIME.Index;
csty.FillPattern = FillPatternType.SOLID_FOREGROUND;
csty.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
csty.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐
//csty.
ICellStyle cstyHEAD = (HSSFCellStyle)(workbook.CreateCellStyle());
//csty.FillForegroundColor = HSSFColor.PINK.index2;
cstyHEAD.FillForegroundColor = IndexedColors.AQUA.Index;
cstyHEAD.FillPattern = FillPatternType.SOLID_FOREGROUND;
cstyHEAD.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER;//垂直对齐
cstyHEAD.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;//水平对齐
cstyHEAD.BorderBottom = (BorderStyle)CellBorderType.THIN;
cstyHEAD.BorderLeft = (BorderStyle)CellBorderType.THIN;
cstyHEAD.BorderRight = (BorderStyle)CellBorderType.THIN;
cstyHEAD.BorderTop = (BorderStyle)CellBorderType.THIN;
HSSFFont f = (HSSFFont)workbook.CreateFont();
//f.Boldweight = (short)FontBoldWeight.BOLD;
//f.Boldweight = 600;
f.FontHeightInPoints = 14;
f.FontName = "Cordia New";
//font.Color =HSSFColor.RED.index;
f.Boldweight = short.MaxValue;
cstyHEAD.SetFont(f);
