ExcelHelper

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Data;
using System.IO;
using System.Web;

namespace student
{
    public class ExcelHelper
    {
        #region 导入excel

        /// <summary>
        /// 读取excel路径 生成 DataSet
        /// </summary>
        /// <param name="path"></param>
        public static DataSet GetExcelToDs(string path)
        {
            DataSet ds = new DataSet();
            using (FileStream fs = File.OpenRead(path))
            {
                using (Workbook wb = new HSSFWorkbook(fs))
                {
                    for (int i = 0; i < wb.NumberOfSheets; i++)
                    {
                        DataTable dt = ds.Tables[i];
                        using (Sheet s = wb.CreateSheet())
                        {
                            int RowLen = s.LastRowNum;
                            for (int j = 0; j < RowLen; j++)
                            {
                                DataRow dr = dt.NewRow();
                                Row r = s.GetRow(j);
                                int ColLen = r.LastCellNum;
                                for (int k = 0; k < ColLen; k++)
                                {
                                    dr[k] = r.GetCell(k);
                                }
                            }
                        }
                    }
                }
            }
            return ds;
        }
        public static DataTable GetExcelToDt(string path)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = File.OpenRead(path))
            {
                using (Workbook wb = new HSSFWorkbook(fs))
                {
                    using (Sheet s = wb.GetSheetAt(0))
                    {
                        Row HeadRow = s.GetRow(0);
                        for (int t = 0; t < HeadRow.LastCellNum; t++)   //set DataTable columns name
                        {
                            dt.Columns.Add(HeadRow.GetCell(t).ToString());
                        }

                        int RowLen = s.LastRowNum;
                        for (int j = 0; j < RowLen; j++)                // TO DataTable
                        {
                            DataRow dr = dt.NewRow();
                            Row r = s.GetRow(j);
                            int ColLen = r.LastCellNum;
                            for (int k = 0; k < ColLen; k++)
                            {
                                dr[k] = r.GetCell(k);
                            }
                            dt.Rows.Add(dr);
                        }
                    }
                }
            }
            return dt;
        }
        public static DataTable GetExcelToDt(FileStream fs)
        {
            DataTable dt = new DataTable();
            using (Workbook wb = new HSSFWorkbook(fs))
            {
                using (Sheet s = wb.GetSheetAt(0))
                {
                    Row HeadRow = s.GetRow(0);
                    for (int t = 0; t < HeadRow.LastCellNum; t++)   //set DataTable columns name
                    {
                        dt.Columns.Add(HeadRow.GetCell(t).ToString());
                    }

                    int RowLen = s.LastRowNum;
                    for (int j = 0; j < RowLen; j++)                // TO DataTable
                    {
                        DataRow dr = dt.NewRow();
                        Row r = s.GetRow(j);
                        int ColLen = r.LastCellNum;
                        for (int k = 0; k < ColLen; k++)
                        {
                            dr[k] = r.GetCell(k);
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }
        #endregion

        #region 导出excel
        /// <summary>
        /// 表转化成excel并且下载
        /// </summary>
        /// <param name="dt">表</param>
        /// <param name="title">文件名</param>
        public static void CreateExcelToDown(DataTable dt, string title)
        {
            using (Workbook book = new HSSFWorkbook())
            {
                Sheet sheet = book.CreateSheet("sheet1");

                Row headerrow = sheet.CreateRow(0);
                CellStyle style = book.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER;
                //1.转化表头
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Cell cell = headerrow.CreateCell(i);
                    cell.CellStyle = style;
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }
                //2.填写数据
                int RowLen = dt.Rows.Count;
                int ColLen = dt.Columns.Count;
                for (int i = 0; i < RowLen; i++)
                {
                    DataRow dr = dt.Rows[i];
                    Row r = sheet.CreateRow((i+1));
                    for (int j = 0; j < ColLen; j++)
                    {
                        r.CreateCell(j).SetCellValue(dr[j].ToString());
                    }
                }
                //3.下载
                using (MemoryStream ms = new MemoryStream())
                {
                    book.Write(ms);
                    HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
                    HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                    HttpContext.Current.Response.End();
                }
            }
        }

        /// <summary>
        ///  DataSet 创建生成 excel文件，保存到本地
        /// </summary>
        /// <param name="name"></param>
        /// <param name="ds"></param>
        public static bool CreateExcelToFile(string name, DataSet ds)
        {
            bool isValue = false;
            int DsLen = ds.Tables.Count;
            using (Workbook wk = new HSSFWorkbook())
            {
                foreach (DataTable dt in ds.Tables)
                {
                    using (Sheet s = wk.CreateSheet())
                    {
                        int RowLen = dt.Rows.Count;
                        int ColLen = dt.Columns.Count;
                        for (int i = 0; i < RowLen; i++)
                        {
                            DataRow dr = dt.Rows[i];
                            Row r = s.CreateRow(i);
                            for (int j = 0; j < ColLen; j++)
                            {
                                r.CreateCell(j).SetCellValue(dr[j].ToString());
                            }
                        }
                        using (FileStream fs = File.OpenWrite(System.Web.HttpContext.Current.Server.MapPath(name + @".xls")))
                        {
                            wk.Write(fs);
                        }
                    }
                }
                isValue = true;
            }
            return isValue;
        }

        /// <summary>
        /// DataTable创建生成Excel,保存到本地
        /// </summary>
        /// <param name="name"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool CreateExcelToFile(string name, DataTable dt)
        {
            bool isValue = false;
            using (Workbook wk = new HSSFWorkbook())
            {
                using (Sheet s = wk.CreateSheet())
                {
                    int RowLen = dt.Rows.Count;
                    int ColLen = dt.Columns.Count;
                    for (int i = 0; i < RowLen; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        Row r = s.CreateRow(i);
                        for (int j = 0; j < ColLen; j++)
                        {
                            r.CreateCell(j).SetCellValue(dr[j].ToString());
                        }
                    }
                    using (FileStream fs = File.OpenWrite(System.Web.HttpContext.Current.Server.MapPath(name + @".xls")))
                    {
                        wk.Write(fs);
                    }
                }
                isValue = true;
            }
            return isValue;
        }

        /// <summary>
        ///error DataTable 创建生成 Excel ,生成文件流 
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static MemoryStream CreateExcelToStream(DataTable dt)
        {
            using (Workbook wk = new HSSFWorkbook())
            {
                using (Sheet s = wk.CreateSheet())
                {
                    int RowLen = dt.Rows.Count;
                    int ColLen = dt.Columns.Count;
                    for (int i = 0; i < RowLen; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        Row r = s.CreateRow(i);
                        for (int j = 0; j < ColLen; j++)
                        {
                            r.CreateCell(j).SetCellValue(dr[j].ToString());
                        }
                    }
                    MemoryStream ms = new MemoryStream();
                    wk.Write(ms);
                    return ms;
                }
            }
        }
        #endregion


    }

}