ASP.NET 存取EXCEL 使用NPOI相關小技巧

2013-05-10

12286
0
ASP.NET
2013-05-21

整理ＮＰＯＩ相關小技巧

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匯出後儲存格的樣式

 /// <summary>
/// 設定匯出Excel儲存格框線
/// </summary>
/// <param name="oStyle"></param>
/// <returns></returns>
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
/// </summary>
/// <returns></returns>
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;
}

回首頁