RExcel

这是一篇关于在MyFrameWork中对Excel(NPOI)操作的简单总结。可以适当更改之后运用在WebForm和MVC中。

namespace YZR.Core
{

    using System.Data;
    using System.IO;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using System.Web;
    /// <summary>
    /// RExcel根据需要进行修改(使用NPOI)
    /// Greate By YZR  2016.4.7
    /// </summary>
    public class RExcel
    {
        #region 导出
        public static MemoryStream RenderToExcel(DataTable table)
        {
            MemoryStream ms = new MemoryStream();

            using (table)
            {
                IWorkbook workbook = new HSSFWorkbook();

                ISheet sheet = workbook.CreateSheet();

                IRow headerRow = sheet.CreateRow(0);

                // handling header.
                foreach (DataColumn column in table.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value

                // handling value.
                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    foreach (DataColumn column in table.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }

                    rowIndex++;
                }

                workbook.Write(ms);
                //下面两句代码等价于ms.Seek(0, SeekOrigin.Begin);
                ms.Flush();
                ms.Position = 0;


            }
            return ms;
        }

        public static MemoryStream RenderToExcel(IDataReader reader)
        {
            MemoryStream ms = new MemoryStream();

            using (reader)
            {
                IWorkbook workbook = new HSSFWorkbook();

                ISheet sheet = workbook.CreateSheet();

                IRow headerRow = sheet.CreateRow(0);
                int cellCount = reader.FieldCount;

                // handling header.
                for (int i = 0; i < cellCount; i++)
                {
                    headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
                }

                // handling value.
                int rowIndex = 1;
                while (reader.Read())
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);

                    for (int i = 0; i < cellCount; i++)
                    {
                        dataRow.CreateCell(i).SetCellValue(reader[i].ToString());
                    }

                    rowIndex++;
                }

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

            }
            return ms;
        }


        /// <summary>
        /// 以文件形式保存在本地
        /// </summary>
        /// <param name="ms"></param>
        /// <param name="filePath"></param>
        public static string SaveToFile(MemoryStream ms, string filePath)
        {
            string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            string fileName = filePath + "\" + filename;
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();

                fs.Write(data, 0, data.Length);
                fs.Flush();

                data = null;
            }
            ClearFile(filePath);//Excel文件清理
            return fileName;
        }
        /// <summary>
        /// 以文件形式保存在本地
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fileName"></param>
        public static string SaveToFile(DataTable table, string fileName)
        {
            MemoryStream ms = RenderToExcel(table);
            return SaveToFile(ms, fileName);
        }

        /// <summary>
        /// 将流保存在浏览器客户端 
        /// </summary>
        /// <param name="ms"></param>
        /// <param name="context"></param>
        /// <param name="fileName"></param>
        public static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
        {
            if (context.Request.Browser.Browser == "IE")
                fileName = HttpUtility.UrlEncode(fileName);
            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            context.Response.BinaryWrite(ms.ToArray());
        }
        /// <summary>
        /// 将文件保存在浏览器客户端====》需要先使用SaveToFile获取Excel文件保存在本地，再通过此方法将本地文件发送到浏览器
        /// </summary>
        /// <param name="fileName"></param>
        public static void RenderToBrowser(string fileName)
        {
            //string filePath = HttpContext.Current.Server.MapPath("..\Util\Excel") + "\" + fileName;
            DownLoad(fileName);
        }
        /// <summary>
        /// 推荐版本
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="ms"></param>
        /// <param name="encoding"></param>
        public static void RenderToBrowser(string fileName, MemoryStream ms, Encoding encoding = null)
        {
            encoding = encoding ?? Encoding.UTF8;
            if (ms != null && !string.IsNullOrEmpty(fileName))
            {
                System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
                response.Clear();
                response.AddHeader("Content-Type", "application/octet-stream");
                response.Charset = encoding.BodyName;// "utf-8";
                if (!HttpContext.Current.Request.UserAgent.Contains("Firefox") && !HttpContext.Current.Request.UserAgent.Contains("Chrome"))
                {
                    fileName = HttpUtility.UrlEncode(fileName, encoding);
                }
                response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.BinaryWrite(ms.GetBuffer());
                ms.Close();
                ms = null;
                response.Flush();
                response.End();
            }
        }
        private static void DownLoad(string path)
        {
            HttpContext context = HttpContext.Current;
            if (path != null && File.Exists(path))
            {
                System.IO.FileInfo file = new System.IO.FileInfo(path);
                //清除缓冲区流中的所有内容输出

                context.Response.Clear();

                // Response.Redirect("~/Content.aspx?content=" + ExtHelper.Escape("<h1>"+path+"</h1>"));

                //将下载保存对话框指定默认的文件名添加到HTTP头中
                //Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                string[] split = path.Split(new Char[] { ':', '\' });
                string fileName = "";
                if (split.Length > 0)
                {
                    fileName = split[split.Length - 1];
                }
                context.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName == "" ? path : fileName, System.Text.Encoding.UTF8));//避免中文出现乱码现象   

                //在header中指定文件的大小，使浏览器能显示下载过程
                context.Response.AddHeader("Content-Length", file.Length.ToString());

                //设置输出流的 HTTP MIME 类型
                context.Response.ContentType = "application/vnd.ms-excel";

                // 发送文件流到客户端
                context.Response.WriteFile(file.FullName);
                // 停止该页的执行

                context.Response.End();
                //HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
            else
            {
                context.Response.Write("文件自动下载中断,请手动下载,Path路径不存在或者为null");
            }
        }
        /// <summary>
        /// 清理Excel文件
        /// </summary>
        /// <param name="FilePath"></param>
        public static void ClearFile(string FilePath)
        {
            String[] Files = System.IO.Directory.GetFiles(FilePath);
            if (Files.Length > 10)
            {
                for (int i = 0; i < 10; i++)
                {
                    try
                    {
                        System.IO.File.Delete(Files[i]);
                    }
                    catch
                    {
                    }

                }
            }
        }
        #endregion



        #region 导入
        /*
         需要注意的是，sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1，这里可能存在BUG：
         当没有数据或只有一行数据时sheet.LastRowNum为0，PhysicalNumberOfRows 表现正常。

         这里读取流中的Excel来创建Workbook对象，并转换成DataTable：
         */

        /// <summary>
        /// 将上传的流转为DataTable
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <returns></returns>
        public static DataTable RenderFromExcel(Stream excelFileStream)
        {
            using (excelFileStream)
            {
                IWorkbook workbook = new HSSFWorkbook(excelFileStream);

                ISheet sheet = workbook.GetSheetAt(0);//取第一个表

                DataTable table = new DataTable();

                IRow headerRow = sheet.GetRow(0);//第一行为标题行
                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

                //handling header.
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                                dataRow[j] = GetCellValue(row.GetCell(j));     //根据Excel列类型获取列的值
                        }
                    }

                    table.Rows.Add(dataRow);
                }
                return table;

            }

        }

        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }
        /// <summary>
        /// 这里的Excel可能没有数据，所以可以加一个方法来检测：
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <returns></returns>
        public static bool HasData(Stream excelFileStream)
        {
            using (excelFileStream)
            {
                IWorkbook workbook = new HSSFWorkbook(excelFileStream);

                if (workbook.NumberOfSheets > 0)
                {
                    ISheet sheet = workbook.GetSheetAt(0);

                    return sheet.PhysicalNumberOfRows > 0;

                }

            }
            return false;
        }

        /// <summary>
        ///  或者是直接生成SQL语句来插入到数据库：
        /// </summary>
        /// <param name="excelFileStream"></param>
        /// <param name="insertSql"></param>
        /// <param name="dbAction">action</param>
        /// <returns></returns>
        public static int RenderToDb(Stream excelFileStream, string insertSql)
        {
            int rowAffected = 0;
            using (excelFileStream)
            {
                IWorkbook workbook = new HSSFWorkbook(excelFileStream);

                ISheet sheet = workbook.GetSheetAt(0);//取第一个工作表

                StringBuilder builder = new StringBuilder();

                IRow headerRow = sheet.GetRow(0);//第一行为标题行
                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        builder.Append(insertSql);
                        builder.Append(" values (");
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            builder.AppendFormat("'{0}',", GetCellValue(row.GetCell(j)).Replace("'", "''"));   //根据Excel列类型获取列的值
                        }
                        builder.Length = builder.Length - 1;
                        builder.Append(");");
                    }

                    if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
                    {
                        //每50条记录一次批量插入到数据库
                        //这里写数据库的插入操作
                        

                        builder.Length = 0;
                    }
                }

            }
            return rowAffected;
        }

        #endregion




        #region 扩展
        /*  将集合的导出操作
         public static MemoryStream RenderToExcel<T>(List<T> listRainInfo)
        {
            string schoolname = "401";
            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
            //获取list数据
            List<FX_LOG_ICK_T> listRainInfo = bll.GetAllToList();
            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("电脑号");
            row1.CreateCell(1).SetCellValue("姓名");
            //将数据逐步写入sheet1各个行
            for (int i = 0; i < listRainInfo.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(listRainInfo[i].PrimaryKey.ToString());
                rowtemp.CreateCell(1).SetCellValue(listRainInfo[i].XM.ToString());
            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }
         * */
        #endregion





    }
}

Demo:

1.Logic层的写法:

        /// <summary>
        /// Excel操作==>保存在本地
        /// </summary>
        /// <returns></returns>
        public string RExcelDemo()
        {
            IDataBase action = RUtility.Instance.GetDbUtility("Register");
            action.ROpen();
            DataTable dt = action.Select();
            string filePath = System.Web.HttpContext.Current.Server.MapPath("..\Util\Excel");
            RExcel.SaveToFile(dt, filePath);
            string Result = "{"Text":"Success"}";
            return Result;
        }
        /// <summary>
        /// Response
        /// </summary>
        /// <returns></returns>
        public void RexcelDemo2()
        {
            IDataBase action = RUtility.Instance.GetDbUtility(TableName);
            action.ROpen();
            DataTable dt = action.Select();
            action.RClose();
            /*
            RExcel类可以根据需要进行更改
            */
            MemoryStream ms = RExcel.RenderToExcel(dt);
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            //方式一：将数据流发送到浏览器客户端
            RExcel.RenderToBrowser(ms, HttpContext.Current, fileName);
            //方式二：将数据流发送到浏览器客户端
            WebHelper.SendFile(fileName, ms);//发送文件流到客户端
        }

2.在WebForm后置文件的写法:

            IDataBase action = RUtility.Instance.GetDbUtility("Register");
            action.ROpen();
            DataTable dt = action.Select();
            action.RClose();
            /*
            RExcel类可以根据需要进行更改
            */
            //方式一：将数据流发送到浏览器客户端
            MemoryStream ms = RExcel.RenderToExcel(dt);
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            RExcel.RenderToBrowser(ms, HttpContext.Current, fileName);
            return;
            //方式二：将本地文件发送到浏览器客户端
            string filePath = System.Web.HttpContext.Current.Server.MapPath("\Util\Excel");
            string strFile = RExcel.SaveToFile(dt, filePath);
            RExcel.RenderToBrowser(strFile);