ASP.NET 存取EXCEL 使用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相关小技巧