{工具} NPOI Excel 讀寫工具(2013 更新，可支援到2007版本)

2013 更新：NPOI 2.0 可支援2003~2007 Excel版本

差異：

//2007
XSSFWorkbook workbook = new XSSFWorkbook(FileUpload1.FileContent);
//2003
HSSFWorkbook workbook = new HSSFWorkbook(FileUpload1.FileContent);

//辯別是2007還是2003
IWorkbook workbook = WorkbookFactory.Create(FileUpload1.FileContent);


匯入Example:

protected void Button1_Click(object sender, EventArgs e)
        {
            //NPOI.DLL：NPOI 核心函式庫。
            //NPOI.DDF.DLL：NPOI 繪圖區讀寫函式庫。
            //NPOI.HPSF.DLL：NPOI 文件摘要資訊讀寫函式庫。
            //NPOI.HSSF.DLL：NPOI Excel BIFF 檔案讀寫函式庫。
            //NPOI.Util.DLL：NPOI 工具函式庫。
            //NPOI.POIFS.DLL：NPOI OLE 格式存取函式庫。
            //ICSharpCode.SharpZipLib.DLL：檔案壓縮函式庫。
            if(FileUpload1.HasFile)
            {
                //辯別是2007還是2003
                IWorkbook workbook = WorkbookFactory.Create(FileUpload1.FileContent);
                
                ////2007
                //XSSFWorkbook workbook = new XSSFWorkbook(FileUpload1.FileContent);
                ////2003
                //HSSFWorkbook workbook = new HSSFWorkbook(FileUpload1.FileContent);


                int sheetCount= workbook.NumberOfSheets;
                DataSet ds=new DataSet();
                //讀每一個Sheet
                for (int i = 0; i < sheetCount; i++)
                {
                    var  hs = workbook.GetSheetAt(i);
                    DataTable dt = new DataTable();
                    //第一行是標題
                    var  hr =  hs.GetRow(0);
                    if (hr == null) continue;
                    int cellCount = hr.LastCellNum;
                    for (int j = hs.FirstRowNum; j < cellCount; j++)
                    {
                        DataColumn column=new DataColumn(hr.GetCell(j).StringCellValue);
                        dt.Columns.Add(column);
                    }

                    int rowCount = hs.LastRowNum;
                    
                    bool flag = false;
                    //略過第0列
                    for (int j = (hs.FirstRowNum+1); j < rowCount; j++)
                    {
                        var  hssfRow = hs.GetRow(j);
                        if (hssfRow == null) continue;
                        DataRow dr = dt.NewRow();
                        flag = false;
                        for (int k = hssfRow.FirstCellNum; k < cellCount; k++)
                        {
                            if (hssfRow.GetCell(k) != null)
                            {
                                switch (hssfRow.GetCell(k).CellType)
                                {
                                    case CellType.NUMERIC:
                                        {
                                            if(!flag)
                                                flag = CheckValue(hssfRow.GetCell(k).ToString());
                                            dr[k] = hssfRow.GetCell(k).NumericCellValue;
                                            break;
                                        }
                                    case CellType.STRING:
                                        {
                                            if(!flag)
                                                flag = CheckValue(hssfRow.GetCell(k).StringCellValue);
                                            dr[k] = hssfRow.GetCell(k).StringCellValue;
                                            break;
                                        }
                                    default:
                                        {
                                            if(!flag)
                                                flag = CheckValue(hssfRow.GetCell(k).ToString());
                                            dr[k] = hssfRow.GetCell(k).ToString();
                                            break;
                                        }
                                }
                            }
                        }

                        if (flag)
                            dt.Rows.Add(dr);
                    }
                    //讀完一個Sheet關閉一個Sheet
                    hs = null;

                    ds.Tables.Add(dt);
                }
                //全部Sheet讀完關閉Excel
                workbook = null;
                GridView1.DataSource = ds.Tables[0];
                GridView1.DataBind();

                GridView2.DataSource = ds.Tables[1];
                GridView2.DataBind();

            }
        }

匯出Excel格式設定範例：
http://wenku.baidu.com/view/035c53d0c1c708a1284a4444.html

EEPlus vs NPOI
http://www.dotblogs.com.tw/kkman021/archive/2012/04/21/71692.aspx


--------------------------------我是分隔線---------------------------------------
相關文章：
官網
http://npoi.codeplex.com/documentation

轉出Excel
http://dotnetmis91.blogspot.tw/2011/02/npoi-gridview.html

NPOI Library--中文解說
http://msdn.microsoft.com/zh-tw/ee818993.aspx

與EPPlus比較
http://wupinny.blogspot.tw/2011/07/aspnet-export-excel-with-npoi-and.html
http://www.dotblogs.com.tw/kkman021/archive/2012/04/21/71692.aspx

詳細應用
http://tonyqus.sinaapp.com/

合併多個Excel
http://www.dotblogs.com.tw/hunterpo/archive/2010/02/25/13759.aspx

NPOI匯出圖表
http://dotnetmis91.blogspot.tw/2010/03/chart-control-excel-npoi.html
http://www.cnblogs.com/atao/archive/2009/09/28/1576044.html