var buildingCustomsPass = from CityBuilding in cityBuilding select new {

MapBuildingModelID = 0, MapModelID = 0, BuildingModelID = CityBuilding.BuildingModelID, BuildingLevel = CityBuilding.BuildingLevel, UnitPosX = CityBuilding.UnitPosX, UnitPosY = CityBuilding.UnitPosY, GarrisonIndex = 0, BuildingUID = CityBuilding.BuildingUID };

做的时候利用的是NPOI.dll这个开源项目，当然要引入NPOI.dll（自己网上搜，很多，这里就不提供了）

一：Excel导出单个sheet

//创建一个有Excel（构造的大概框架，标题）public HSSFWorkbook CreateExcel(string[] titles){HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

//标题样式ICellStyle cellStyleLable = sheet.Workbook.CreateCellStyle();cellStyleLable.Alignment = HorizontalAlignment.CENTER;cellStyleLable.VerticalAlignment = VerticalAlignment.CENTER;cellStyleLable.FillPattern = FillPatternType.SOLID_FOREGROUND;cellStyleLable.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;cellStyleLable.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;cellStyleLable.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;cellStyleLable.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;cellStyleLable.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;//设置字体IFont font = workbook.CreateFont();font.Boldweight = (short)FontBoldWeight.BOLD;cellStyleLable.SetFont(font);

 //文本样式 //ICellStyle cellStyleText = sheet.Workbook.CreateCellStyle(); //cellStyleText.Alignment = HorizontalAlignment.CENTER; //cellStyleText.VerticalAlignment = VerticalAlignment.CENTER; //cellStyleText.BorderBottom = BorderStyle.THIN; //cellStyleText.BorderTop = BorderStyle.THIN; //cellStyleText.BorderRight = BorderStyle.THIN; //cellStyleText.BorderLeft = BorderStyle.THIN;

 IRow row = sheet.CreateRow(0); for (int i = 0; i < titles.Length; i++){ICell cell = row.CreateCell(i);cell.CellStyle = cellStyleLable;cell.SetCellValue(titles[i]);

 sheet.SetColumnWidth(i, 20 * 256);

} return workbook;}

//导出

protected void ButtonExport_Click(object sender, EventArgs e){

这里是以linq得到的匿名类为数据源

var buildingCustomsPass = from CityBuilding in cityBuilding select new {

MapBuildingModelID = 0, MapModelID = 0, BuildingModelID = CityBuilding.BuildingModelID, BuildingLevel = CityBuilding.BuildingLevel, UnitPosX = CityBuilding.UnitPosX, UnitPosY = CityBuilding.UnitPosY, GarrisonIndex = 0, BuildingUID = CityBuilding.BuildingUID }; if (buildingCustomsPass.Count() > 0) {HSSFWorkbook workbook = CreateExcel(new string[] { "MapBuildingModelID", "MapModelID", "BuildingModelID", "BuildingLevel", "UnitPosX", "UnitPosY", "GarrisonIndex" });

int i = 0;

foreach (var building in buildingCustomsPass){ IRow row = workbook.GetSheetAt(0).CreateRow(i + 1); row.CreateCell(0).SetCellValue(building.MapBuildingModelID); row.CreateCell(1).SetCellValue(building.MapModelID);row.CreateCell(2).SetCellValue(building.BuildingModelID);row.CreateCell(3).SetCellValue(building.BuildingLevel);row.CreateCell(4).SetCellValue(building.UnitPosX);row.CreateCell(5).SetCellValue(building.UnitPosY);row.CreateCell(6).SetCellValue(building.GarrisonIndex); i = i + 1;

}

MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose();

}

二.Excel 多个sheet（这里是数据超过65335时写入另一个sheet）

/// /// 生成Excel表头(这里只是生成表头) /// /// /// public void CreateHeader(HSSFSheet sheet, string[] titles) { //标题样式 ICellStyle cellStyleLable = sheet.Workbook.CreateCellStyle(); cellStyleLable.Alignment = HorizontalAlignment.CENTER; cellStyleLable.VerticalAlignment = VerticalAlignment.CENTER; cellStyleLable.FillPattern = FillPatternType.SOLID_FOREGROUND; cellStyleLable.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; IRow row = sheet.CreateRow(0); for (int i = 0; i < titles.Length; i++) { ICell cell = row.CreateCell(i); cell.CellStyle = cellStyleLable; cell.SetCellValue(titles[i]); sheet.SetColumnWidth(i, 20 * 256); } }

 //导出

 protected void ButtonExporet_Click(object sender, EventArgs e){

 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

var customFighter = from CityFighter in cityFighter select new { CustomFighterSetID =0, CustomModelID = 0, FighterModelID = CityFighter.FighterModelID, FighterLevel = CityFighter.FighterLevel, IsForeignAid = 0, BonusRatePer =0 }; if (customFighter.Count() > 0) { int rowCount = 0; int sheetCount = 1; //添加一个sheet NPOI.SS.UserModel.ISheet newSheet = null; newSheet = book.CreateSheet("Sheet" + sheetCount);

CreateHeader((HSSFSheet)newSheet, new string[] { "CustomFighterSetID", "CustomModelID", "FighterModelID", "FighterLevel", "IsForeignAid", "BonusRatePer" }); foreach (var fighter in customFighter) { rowCount++; if (rowCount == 65335) { rowCount = 1; sheetCount++; newSheet = book.CreateSheet("Sheet" + sheetCount); CreateHeader((HSSFSheet)newSheet, new string[] { "CustomFighterSetID", "CustomModelID", "FighterModelID", "FighterLevel", "IsForeignAid", "BonusRatePer" }); } // IRow row = book.GetSheetAt(sheetCount).CreateRow(rowCount + 1); IRow row = newSheet.CreateRow(rowCount); row.CreateCell(0).SetCellValue(fighter.CustomFighterSetID); row.CreateCell(1).SetCellValue(fighter.CustomModelID); row.CreateCell(2).SetCellValue(fighter.FighterModelID); row.CreateCell(3).SetCellValue(fighter.FighterLevel); row.CreateCell(4).SetCellValue(fighter.IsForeignAid); row.CreateCell(5).SetCellValue(fighter.BonusRatePer); } MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose();

}

三.Excel下的重新的sheet（类似2）

/// /// 生成Excel头 /// /// /// public void CreateHeader(HSSFSheet sheet, string[] titles) { //标题样式 ICellStyle cellStyleLable = sheet.Workbook.CreateCellStyle(); cellStyleLable.Alignment = HorizontalAlignment.CENTER; cellStyleLable.VerticalAlignment = VerticalAlignment.CENTER; cellStyleLable.FillPattern = FillPatternType.SOLID_FOREGROUND; cellStyleLable.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; cellStyleLable.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; IRow row = sheet.CreateRow(0); for (int i = 0; i < titles.Length; i++) { ICell cell = row.CreateCell(i); cell.CellStyle = cellStyleLable; cell.SetCellValue(titles[i]); sheet.SetColumnWidth(i, 20 * 256); } }

//开始导出 这里是导出一个Excel里面有来自三个数据源生成三个sheet

 protected void ButtonSearch_export(object sender, EventArgs e)

{

if (playerInfo != null) { DBContext dbContext = DBHelper.GetGameDBContext(iServerId); using (ISession session = dbContext.OpenSession()) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //sheet NPOI.SS.UserModel.ISheet newSheet = null; PlayerCity[] playerCity = session.CreateCriteria(typeof(PlayerCity)).Add(Restrictions.Eq("PlayerUID", playerInfo.PlayerUID)).List().ToArray(); ICollection cityBuilding = session.CreateCriteria(typeof(CityBuilding)).Add(Restrictions.Eq("CityUID", playerCity[0].CityUID)).List().ToArray(); var buildingCustomsPass = from CityBuilding in cityBuilding select new { MapBuildingModelID = 0, MapModelID = 0, BuildingModelID = CityBuilding.BuildingModelID, BuildingLevel = CityBuilding.BuildingLevel, UnitPosX = CityBuilding.UnitPosX, UnitPosY = CityBuilding.UnitPosY, GarrisonIndex = 0, BuildingUID = CityBuilding.BuildingUID }; if (buildingCustomsPass.Count() > 0) { int rowCount = 0; newSheet = book.CreateSheet("建筑"); CreateHeader((HSSFSheet)newSheet, new string[] { "MapBuildingModelID", "MapModelID", "BuildingModelID", "BuildingLevel", "UnitPosX", "UnitPosY", "GarrisonIndex", "BuildingUID" }); foreach (var building in buildingCustomsPass) { rowCount++; IRow row = newSheet.CreateRow(rowCount); row.CreateCell(0).SetCellValue(building.MapBuildingModelID); row.CreateCell(1).SetCellValue(building.MapModelID); row.CreateCell(2).SetCellValue(building.BuildingModelID); row.CreateCell(3).SetCellValue(building.BuildingLevel); row.CreateCell(4).SetCellValue(building.UnitPosX); row.CreateCell(5).SetCellValue(building.UnitPosY); row.CreateCell(6).SetCellValue(building.GarrisonIndex); row.CreateCell(7).SetCellValue(building.BuildingUID.ToString());

} newSheet = null; } //英雄 Guid guid = new Guid("00000000-0000-0000-0000-000000000000"); ICollection cityWarrior = session.CreateCriteria(typeof(CityWarrior)).Add(Restrictions.Eq("CityUID", playerCity[0].CityUID)).Add(Restrictions.Not(Restrictions.Eq("BuildingUID", guid))).List().ToArray(); var customWarrior = from CityWarrior in cityWarrior select new { CustomWarriorSetID = 0, CustomModelID = 0, WarriorModelID = CityWarrior.WarriorModelID, Rank = CityWarrior.Rank, FighterLevel = CityWarrior.FighterLevel, Coordination = CityWarrior.Coordination, Formation_STR = CityWarrior.Formation_STR, SpellsLevelSet_STR = CityWarrior.SpellsLevelSet_STR, EquimentSpellsSlot = CityWarrior.EquimentSpellsSlot, AstrolabeSlotState_STR = CityWarrior.AstrolabeSlotState_STR, DetachTime = 0, UnitPosX = 0, UnitPosY = 0, Direction = 0, GarrisonIndex = 0, IsForeignAid = 0, HitRate = 0, BonusRatePer = 0, HitPointBonusRatePer = 0, DamageBonusRatePer = 0, DefenseBonusRatePer = 0 }; if (customWarrior.Count() > 0) { int rowCount = 0; newSheet = book.CreateSheet("英雄"); CreateHeader((HSSFSheet)newSheet, new string[] { "CustomWarriorSetID", " CustomModelID", "WarriorModelID", "Rank", "FighterLevel", "Coordination", "Formation_STR", "SpellsLevelSet_STR", "EquimentSpellsSlot ", "AstrolabeSlotState_STR", "DetachTime", "UnitPosX", "UnitPosY", "Direction", "GarrisonIndex", "IsForeignAid", "HitRate", "BonusRatePer", "HitPointBonusRatePer", "DamageBonusRatePer", "DefenseBonusRatePer" }); foreach (var warrior in customWarrior) { rowCount++; IRow row = newSheet.CreateRow(rowCount); row.CreateCell(0).SetCellValue(warrior.CustomWarriorSetID); row.CreateCell(1).SetCellValue(warrior.CustomModelID); row.CreateCell(2).SetCellValue(warrior.WarriorModelID); row.CreateCell(3).SetCellValue(warrior.Rank.ToString()); row.CreateCell(4).SetCellValue(warrior.FighterLevel); row.CreateCell(5).SetCellValue(warrior.Coordination.ToString()); row.CreateCell(6).SetCellValue(warrior.Formation_STR); row.CreateCell(7).SetCellValue(warrior.SpellsLevelSet_STR); row.CreateCell(8).SetCellValue(warrior.EquimentSpellsSlot); row.CreateCell(9).SetCellValue(warrior.AstrolabeSlotState_STR); row.CreateCell(10).SetCellValue(warrior.DetachTime); row.CreateCell(11).SetCellValue(warrior.UnitPosX); row.CreateCell(12).SetCellValue(warrior.UnitPosY); row.CreateCell(13).SetCellValue(warrior.Direction.ToString()); row.CreateCell(14).SetCellValue(warrior.GarrisonIndex); row.CreateCell(15).SetCellValue(warrior.IsForeignAid); row.CreateCell(16).SetCellValue(warrior.HitRate); row.CreateCell(17).SetCellValue(warrior.BonusRatePer); row.CreateCell(18).SetCellValue(warrior.HitPointBonusRatePer); row.CreateCell(19).SetCellValue(warrior.DamageBonusRatePer); row.CreateCell(20).SetCellValue(warrior.DefenseBonusRatePer); } newSheet = null; } ICollection cityFighter = session.CreateCriteria(typeof(CityFighter)).Add(Restrictions.Eq("CityUID", playerCity[0].CityUID)).List().ToArray(); var customFighter = from CityFighter in cityFighter select new { CustomFighterSetID = 0, CustomModelID = 0, FighterModelID = CityFighter.FighterModelID, FighterLevel = CityFighter.FighterLevel, IsForeignAid = 0, BonusRatePer = 0 }; if (customFighter.Count() > 0) { int rowCount = 0; newSheet = book.CreateSheet("部队"); CreateHeader((HSSFSheet)newSheet, new string[] { "CustomFighterSetID", "CustomModelID", "FighterModelID", "FighterLevel", "IsForeignAid", "BonusRatePer" }); foreach (var fighter in customFighter) { rowCount++; IRow row = newSheet.CreateRow(rowCount); row.CreateCell(0).SetCellValue(fighter.CustomFighterSetID); row.CreateCell(1).SetCellValue(fighter.CustomModelID); row.CreateCell(2).SetCellValue(fighter.FighterModelID); row.CreateCell(3).SetCellValue(fighter.FighterLevel); row.CreateCell(4).SetCellValue(fighter.IsForeignAid); row.CreateCell(5).SetCellValue(fighter.BonusRatePer); } newSheet = null; } MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff"))); Response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); } } else { LabelError.Text = "该玩家不存在"; return; }

}

用到了就整理了一下，table数据源导出，类似上面，只是循环出table中的数据