asp.net(C#)之NPOI"操作Excel

標籤：asp.net   excel   

1.首先到網上下載"NPOI.DLL"，引用。

2.建立一個操作類“ExcelHelper.cs”：

using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;public class ExcelHelper{    public class x2003    {        #region Excel2003        /// <summary>        /// 將Excel檔案中的資料讀出到DataTable中(xls)        /// </summary>        /// <param name="file"></param>        /// <returns></returns>        public static DataTable ExcelToTableForXLS(string file)        {            DataTable dt = new DataTable();            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))            {                HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);                ISheet sheet = hssfworkbook.GetSheetAt(0);                //表頭                IRow header = sheet.GetRow(sheet.FirstRowNum);                List<int> columns = new List<int>();                for (int i = 0; i < header.LastCellNum; i++)                {                    object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);                    if (obj == null || obj.ToString() == string.Empty)                    {                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));                        //continue;                    }                    else                        dt.Columns.Add(new DataColumn(obj.ToString()));                    columns.Add(i);                }                //資料                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)                {                    DataRow dr = dt.NewRow();                    bool hasValue = false;                    foreach (int j in columns)                    {                        dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);                        if (dr[j] != null && dr[j].ToString() != string.Empty)                        {                            hasValue = true;                        }                    }                    if (hasValue)                    {                        dt.Rows.Add(dr);                    }                }            }            return dt;        }        /// <summary>        /// 將DataTable資料匯出到Excel檔案中(xls)        /// </summary>        /// <param name="dt"></param>        /// <param name="file"></param>        public static void TableToExcelForXLS(DataTable dt, string file)        {            HSSFWorkbook hssfworkbook = new HSSFWorkbook();            ISheet sheet = hssfworkbook.CreateSheet("Test");            //表頭            IRow row = sheet.CreateRow(0);            for (int i = 0; i < dt.Columns.Count; i++)            {                ICell cell = row.CreateCell(i);                cell.SetCellValue(dt.Columns[i].ColumnName);            }            //資料            for (int i = 0; i < dt.Rows.Count; i++)            {                IRow row1 = sheet.CreateRow(i + 1);                for (int j = 0; j < dt.Columns.Count; j++)                {                    ICell cell = row1.CreateCell(j);                    cell.SetCellValue(dt.Rows[i][j].ToString());                }            }            //轉為位元組數組            MemoryStream stream = new MemoryStream();            hssfworkbook.Write(stream);            var buf = stream.ToArray();            //儲存為Excel檔案            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))            {                fs.Write(buf, 0, buf.Length);                fs.Flush();            }        }        /// <summary>        /// 擷取儲存格類型(xls)        /// </summary>        /// <param name="cell"></param>        /// <returns></returns>        private static object GetValueTypeForXLS(HSSFCell cell)        {            if (cell == null)                return null;            switch (cell.CellType)            {                case CellType.Blank: //BLANK:                    return null;                case CellType.Boolean: //BOOLEAN:                    return cell.BooleanCellValue;                case CellType.Numeric: //NUMERIC:                    return cell.NumericCellValue;                case CellType.String: //STRING:                    return cell.StringCellValue;                case CellType.Error: //ERROR:                    return cell.ErrorCellValue;                case CellType.Formula: //FORMULA:                default:                    return "=" + cell.CellFormula;            }        }        #endregion    }    public class x2007    {        #region Excel2007        /// <summary>        /// 將Excel檔案中的資料讀出到DataTable中(xlsx)        /// </summary>        /// <param name="file"></param>        /// <returns></returns>        public static DataTable ExcelToTableForXLSX(string file)        {            DataTable dt = new DataTable();            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))            {                XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);                ISheet sheet = xssfworkbook.GetSheetAt(0);                //表頭                IRow header = sheet.GetRow(sheet.FirstRowNum);                List<int> columns = new List<int>();                for (int i = 0; i < header.LastCellNum; i++)                {                    object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);                    if (obj == null || obj.ToString() == string.Empty)                    {                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));                        //continue;                    }                    else                        dt.Columns.Add(new DataColumn(obj.ToString()));                    columns.Add(i);                }                //資料                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)                {                    DataRow dr = dt.NewRow();                    bool hasValue = false;                    foreach (int j in columns)                    {                        dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);                        if (dr[j] != null && dr[j].ToString() != string.Empty)                        {                            hasValue = true;                        }                    }                    if (hasValue)                    {                        dt.Rows.Add(dr);                    }                }            }            return dt;        }        /// <summary>        /// 將DataTable資料匯出到Excel檔案中(xlsx)        /// </summary>        /// <param name="dt"></param>        /// <param name="file"></param>        public static void TableToExcelForXLSX(DataTable dt, string file)        {            XSSFWorkbook xssfworkbook = new XSSFWorkbook();            ISheet sheet = xssfworkbook.CreateSheet("Test");            //表頭            IRow row = sheet.CreateRow(0);            for (int i = 0; i < dt.Columns.Count; i++)            {                ICell cell = row.CreateCell(i);                cell.SetCellValue(dt.Columns[i].ColumnName);            }            //資料            for (int i = 0; i < dt.Rows.Count; i++)            {                IRow row1 = sheet.CreateRow(i + 1);                for (int j = 0; j < dt.Columns.Count; j++)                {                    ICell cell = row1.CreateCell(j);                    cell.SetCellValue(dt.Rows[i][j].ToString());                }            }            //轉為位元組數組            MemoryStream stream = new MemoryStream();            xssfworkbook.Write(stream);            var buf = stream.ToArray();            //儲存為Excel檔案            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))            {                fs.Write(buf, 0, buf.Length);                fs.Flush();            }        }        /// <summary>        /// 擷取儲存格類型(xlsx)        /// </summary>        /// <param name="cell"></param>        /// <returns></returns>        private static object GetValueTypeForXLSX(XSSFCell cell)        {            if (cell == null)                return null;            switch (cell.CellType)            {                case CellType.Blank: //BLANK:                    return null;                case CellType.Boolean: //BOOLEAN:                    return cell.BooleanCellValue;                case CellType.Numeric: //NUMERIC:                    return cell.NumericCellValue;                case CellType.String: //STRING:                    return cell.StringCellValue;                case CellType.Error: //ERROR:                    return cell.ErrorCellValue;                case CellType.Formula: //FORMULA:                default:                    return "=" + cell.CellFormula;            }        }        #endregion    }    public static DataTable GetDataTable(string filepath)    {        var dt = new DataTable("xls");        if (filepath.Last()=='s')        {            dt = x2003.ExcelToTableForXLS(filepath);        }        else        {            dt = x2007.ExcelToTableForXLSX(filepath);        }        return dt;    }}

3.程式後台主要代碼：

using System;using System.Collections.Generic;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data;using System.Linq;public partial class _Default : System.Web.UI.Page {    protected void Page_Load(object sender, EventArgs e)    {    }    protected void btn_read_03_click(object o, EventArgs e)    {        var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2003.xls"));        g1.DataSource = dt;        g1.DataBind();    }    protected void btn_read_07_click(object o, EventArgs e)    {        var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2007.xlsx"));        g1.DataSource = dt;        g1.DataBind();    }    protected void btn_import_03_click(object o, EventArgs e)    {        var name = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random(DateTime.Now.Second).Next(10000);        var path = Server.MapPath("~/xls_down/" + name + ".xls");        var dt = new System.Data.DataTable();        var Columns=Enumerable.Range(1, 10).Select(d => new DataColumn("a"+d.ToString(), typeof(string))).ToArray();        dt.Columns.AddRange(Columns);        for (int i = 0; i < 33333; i++)        {            var id = Guid.NewGuid().ToString();            dt.Rows.Add(id, id, id, id, id, id, id, id, id, id);        }        ExcelHelper.x2003.TableToExcelForXLS(dt, path);        downloadfile(path);    }    protected void btn_import_07_click(object o, EventArgs e)    {        var name = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random(DateTime.Now.Second).Next(10000);        var path = Server.MapPath("~/xls_down/" + name + ".xlsx");        var dt = new System.Data.DataTable();        var Columns = Enumerable.Range(1, 10).Select(d => new DataColumn("a" + d.ToString(), typeof(string))).ToArray();        dt.Columns.AddRange(Columns);        for (int i = 0; i < 33333; i++)        {            var id = Guid.NewGuid().ToString();            dt.Rows.Add(id, id, id, id, id, id, id, id, id, id);        }        ExcelHelper.x2007.TableToExcelForXLSX(dt, path);        downloadfile(path);    }    void downloadfile(string s_path)    {        System.IO.FileInfo file = new System.IO.FileInfo(s_path);        HttpContext.Current.Response.ContentType = "application/ms-download";        HttpContext.Current.Response.Clear();        HttpContext.Current.Response.AddHeader("Content-Type", "application/octet-stream");        HttpContext.Current.Response.Charset = "utf-8";        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8));        HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());        HttpContext.Current.Response.WriteFile(file.FullName);        HttpContext.Current.Response.Flush();        HttpContext.Current.Response.Clear();        HttpContext.Current.Response.End();    }}

asp.net(C#)之NPOI"操作Excel