C# Excel 讀取 NPOI 並轉 DataTable

NPOI載點:http://npoi.codeplex.com/
只限分析.xls格式

public DataTable ExcelForDataTable(string strFilePath)
{
    HSSFWorkbook wk;

    using (FileStream fs = new FileStream(strFilePath, FileMode.Open))
    {
        wk = new HSSFWorkbook(fs);
    }

    //取得sheet名稱
    HSSFSheet hst;
    hst = (HSSFSheet)wk.GetSheetAt(0);

    //單行單行的取得sheet理的資料
    HSSFRow hr;

    DataTable dt = new DataTable();

    for (int i = 0; i < hst.LastRowNum; i++)
    {
        hr = (HSSFRow)hst.GetRow(i);

        //加入DataTable欄位
        if (i == 0)
        {
            for (int a = 0; a < hr.LastCellNum; a++)
            {
                dt.Columns.Add(hr.Cells[a].ToString());
            }

        }

        //加入資料
        else
        {
            DataRow dr = dt.NewRow();
            for (int a = 0; a < hr.LastCellNum; a++)
            {
                dr[a] = hr.Cells[a];
            }
            dt.Rows.Add(dr);

        }

    }

    return dt;

}

C# Excel 讀取 NPOI

NPOI載點:http://npoi.codeplex.com/
只限分析.xls格式

string strFilePath = string.Format(@"D:\old桌面\testWed2\NewFolder\test1.xls");
HSSFWorkbook wk;

using (FileStream fs = new FileStream(strFilePath, FileMode.Open))
{
    wk = new HSSFWorkbook(fs);
}

//取得sheet名稱
HSSFSheet hst;
hst = (HSSFSheet)wk.GetSheetAt(0);
string strSheetname = hst.SheetName;

//單行單行的取得sheet理的資料
HSSFRow hr;
hr = (HSSFRow)hst.GetRow(0);

string a = hr.Cells[0].ToString();
string b = hr.Cells[1].ToString();

C# Excel 寫入 NPOI

下載 NPIO:http://npoi.codeplex.com/
參考資料:http://msdn.microsoft.com/zh-tw/ee818993.aspx

Dim Workbook As HSSFWorkbook = New HSSFWorkbook
Dim ms As MemoryStream = New MemoryStream

Dim sheet As HSSFSheet = Workbook.CreateSheet("My Sheet")

for(int i = 0; i < 5; i ++)
{
    sheet.CreateRow(i)
    sheet.GetRow(i).CreateCell(0).SetCellValue( i.ToString())
    sheet.GetRow(i).CreateCell(1).SetCellValue("A" + i.ToString() )
}

Workbook.Write(ms)
Response.AddHeader("Content-Disposition", String.Format("attachment; filename=EmptyWorkbook.xls"))
        Response.BinaryWrite(ms.ToArray())

Workbook = Nothing
ms.Close()
ms.Dispose()