如何运用NPOI技巧高效存取ASP.NET中的EXCEL文件？

本文共计384个文字，预计阅读时间需要2分钟。

整理：关于删除Excel行并上移下方数据的技巧+Technorati的标签：.Net, NPOI? 1. 删除row并上移下方数据+/+//EX: 删除ROW(8)Sheet.RemoveRow(Sheet.GetRow(8))//+//EX: 删除行并将下方行上移//+//EX: 将第8行往上移一行=移除第7行

整理ＮＰＯＩ相关小技巧

Technorati 的标签: .Net,NPOI

?

1.删除row & 删除并将下方数据行上移

//EX:删除ROW(8) Sheet.RemoveRow(Sheet.GetRow(8)); //删除行并将下方行上移 //EX:将第8行往上移一行=移除第7行 Sheet.ShiftRows(8, Sheet.LastRowNum, -1);

2.Sheet Copy

HSSFSheet newSheet = (HSSFSheet)workbook.CloneSheet(0);

3.style

设定EXCEL导出后保存格的模式

///

/// 设定导出Excel保存格框线 ///

///

/// public HSSFCellStyle SetBorderStyle(HSSFCellStyle oStyle) { //设定保存格框线 oStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; oStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; oStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; oStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; oStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; oStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; oStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; oStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; return oStyle; } #endregion

4.参照档

当导出的数据有既定EXCEL要套表时，可以参考旧有EXCEL来产生新的

HSSFWorkbook workbook = ReadSampleExcel(); if (workbook == null) return; //从范本档复制新的EXCEL页签 HSSFSheet newSheet = (HSSFSheet)workbook.CloneSheet(0); //TODO将数据放入newSheet //移除范本档 workbook.RemoveSheetAt(0);

/// 读取范例EXCEL /// /// private static HSSFWorkbook ReadSampleExcel() { FileStream fm; HSSFWorkbook workbook; //范本档路径 string Path = HttpContext.Current.Server.MapPath(string.Format("~/bin/Common/Data/{0}.xls", "CDR210Sample")); try { //读取范本 fm = new FileStream(Path, FileMode.Open, FileAccess.Read); //范本Stream转成EXCEL workbook = new HSSFWorkbook(fm); } catch (Exception) { workbook = null; } return workbook; }

原文:大专栏 ASP.NET 存取EXCEL 使用NPOI相关小技巧