.net mvc epplus

帮助类

using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;

namespace Sfq.Web.Code
{
    public class EpPlusHelper
    {
        ExcelPackage package;
        public ExcelWorksheet worksheet;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="path"></param>
        public EpPlusHelper(string sheetName = "", string path = "")
        {
            try
            {
                if (!string.IsNullOrEmpty(path))
                {
                    package = new ExcelPackage(new FileInfo(path));
                }
                else
                {
                    package = new ExcelPackage();
                }

                if (package.Workbook.Worksheets.Count > 0)
                {
                    worksheet = package.Workbook.Worksheets.First();
                }
                else
                {
                    CreateSheet(DateTime.Now.ToString("yyyyMMdd"));
                }

                if (!string.IsNullOrWhiteSpace(sheetName))
                {
                    worksheet.Name = sheetName;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 创建工作薄
        /// </summary>
        /// <param name="sheetName"></param>
        public void CreateSheet(string sheetName)
        {
            try
            {
                worksheet = package.Workbook.Worksheets.Add(sheetName);//创建worksheet
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 切换工作薄
        /// </summary>
        /// <param name="index"></param>
        public void ChangeSheet(int index)
        {
            try
            {
                worksheet = package.Workbook.Worksheets[index];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 切换工作簿
        /// </summary>
        /// <param name="sheetName"></param>
        public void ChangeSheet(string sheetName)
        {
            try
            {
                worksheet = package.Workbook.Worksheets[sheetName];
            }
            catch (Exception ex)
            {
                throw ex;

            }
        }

        /// <summary>
        /// 保存excel
        /// </summary>
        /// <param name="password"></param>
        public void SaveExcel(HttpResponseBase response, string excelName)
        {
            try
            {
                if (package != null)
                {
                    if (!string.IsNullOrEmpty(excelName))
                    {
                        //package.Save();
                        //package.SaveAs();
                        response.BinaryWrite(package.GetAsByteArray());
                        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        response.AddHeader("content-disposition", "attachment;  filename=" + excelName + ".xlsx");
                    }
                    else
                    {
                        response.BinaryWrite(package.GetAsByteArray());
                        response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        response.AddHeader("content-disposition", "attachment;  filename=" + (DateTime.Now.ToString("yyyyMMddHHmmss")) + ".xlsx");
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过索引赋值，索引从1开始
        /// </summary>
        /// <param name="x">行</param>
        /// <param name="y">列</param>
        /// <param name="value"></param>
        public void SetValue(int x, int y, string value)
        {
            worksheet.Cells[x, y].Value = value;//直接指定行列数进行赋值
        }

        /// <summary>
        /// 单元格赋值
        /// </summary>
        /// <param name="cell">单元格，如：A1</param>
        /// <param name="value"></param>
        public void SetValue(string cell, string value)
        {
            worksheet.Cells[cell].Value = value;//直接指定单元格进行赋值
        }

        /// <summary>
        /// 设置样式
        /// </summary>
        /// <param name="x"></param>
        /// <param name="y"></param>
        /// <param name="isWrapText">是否换行</param>
        /// <param name="horizontal">水平格式</param>
        /// <param name="vertical">垂直格式</param>
        /// <param name="isBold">是否粗体</param>
        /// <param name="size">文字大小</param>
        /// <param name="height">行高</param>
        /// <param name="isShowGridLines">是否显示网格线</param>
        private void SetStyle(int x, int y, bool isWrapText = true, ExcelHorizontalAlignment horizontal = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment vertical = ExcelVerticalAlignment.Center, bool isBold = false, int size = 12, int height = 15, bool isShowGridLines = false)
        {
            //worksheet.Cells[x, y].Style.Numberformat.Format = "#,##0.00";//这是保留两位小数

            worksheet.Cells[x, y].Style.HorizontalAlignment = horizontal;//水平居中
            worksheet.Cells[x, y].Style.VerticalAlignment = vertical;//垂直居中
            //worksheet.Cells[1, 4, 1, 5].Merge = true;//合并单元格
            worksheet.Cells.Style.WrapText = isWrapText;//自动换行

            worksheet.Cells[x, y].Style.Font.Bold = isBold;//字体为粗体
            //worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White);//字体颜色
            //worksheet.Cells[1, 1].Style.Font.Name = "微软雅黑";//字体
            worksheet.Cells[x, y].Style.Font.Size = size;//字体大小

            //worksheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
            //worksheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色

            worksheet.Cells[x, y].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);//设置单元格所有边框
            //worksheet.Cells[x, y].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//单独设置单元格底部边框样式和颜色（上下左右均可分开设置）
            //worksheet.Cells[x, y].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));

            //worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
            worksheet.Row(x).Height = height;//设置行高
            //worksheet.Row(1).CustomHeight = true;//自动调整行高
            worksheet.Column(y).Width = 20;//设置列宽

            worksheet.View.ShowGridLines = isShowGridLines;//去掉sheet的网格线
            //worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
            //worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray);//设置背景色
            //worksheet.BackgroundImage.Image = Image.FromFile(@"firstbg.jpg");//设置背景图片
        }

        public void SetCellStyle(int x, int y, Color color)
        {
            //Color.FromArgb(128, 128, 128)
            worksheet.Cells[x, y].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells[x, y].Style.Fill.BackgroundColor.SetColor(color);
        }

        public void SetMergeCell(int x1, int y1, int x2, int y2)
        {
            worksheet.Cells[x1, y1, x2, y2].Merge = true;//合并单元格
        }

        public void SetCellStyle(int x, int y, int x1, int y1)
        {
            worksheet.Cells[x, y, x1, y1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(0, 0, 0));//设置单元格所有边框
            //worksheet.Cells[x, y, x1, y1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//单独设置单元格底部边框样式和颜色（上下左右均可分开设置）
            //worksheet.Cells[x, y, x1, y1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));
        }

        public void SetDefaultStyle()
        {//设置单元格边框
            using (ExcelRange r = worksheet.Cells[worksheet.Dimension.Address])
            {
                r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                r.Style.Border.Top.Color.SetColor(Color.Black);
                r.Style.Border.Bottom.Color.SetColor(Color.Black);
                r.Style.Border.Left.Color.SetColor(Color.Black);
                r.Style.Border.Right.Color.SetColor(Color.Black);
            }
            // 自动列宽
            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
            // 居中
            worksheet.Cells[worksheet.Dimension.Address].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
            worksheet.Cells[worksheet.Dimension.Address].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
        }

        public void TableToExcel(DataTable dt, string title)
        {
            try
            {
                DataColumnCollection columns = dt.Columns;

                //表格标题
                if (!string.IsNullOrEmpty(title))
                {
                    SetMergeCell(1, 1, 1, columns.Count);
                    SetStyle(1, 1, false, ExcelHorizontalAlignment.Center, ExcelVerticalAlignment.Center, true, 16, 25);
                    SetValue(1, 1, title);
                }

                //加载DataTable到Excel单元格
                worksheet.Cells["A2"].LoadFromDataTable(dt, true);
                
                SetDefaultStyle();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        public void TableToExcel(DataTable dt, string title, string title1, string bottom, int startIndex = 1, bool isHeader = true)
        {
            DataColumnCollection columns = dt.Columns;

            int addIndex = startIndex;

            //表格标题
            if (!string.IsNullOrEmpty(title))
            {
                SetMergeCell(1, 1, 1, columns.Count);
                SetStyle(1, 1, false, ExcelHorizontalAlignment.Center, ExcelVerticalAlignment.Center, true, 16, 50);
                //worksheet.Cells.Merge(1, 1, 1, columns.Count);
                SetCellStyle(1, 1, 1, columns.Count);
                SetValue(1, 1, title);
            }

            if (!string.IsNullOrWhiteSpace(title1))
            {
                SetMergeCell(2, 1, 2, columns.Count);
                SetStyle(2, 1, false, ExcelHorizontalAlignment.Center, ExcelVerticalAlignment.Center, false, 16, 25);
                //worksheet.Cells.Merge(1, 1, 1, columns.Count);
                SetCellStyle(2, 1, 2, columns.Count);
                SetValue(2, 1, title1);

                SetMergeCell(3, 4, 3, 7);
            }

            if (!string.IsNullOrWhiteSpace(bottom))
            {
                int start = dt.Rows.Count + 3;

                SetMergeCell(start, 1, start + 5, columns.Count);
                SetStyle(start, 1, false, ExcelHorizontalAlignment.Left, ExcelVerticalAlignment.Top, false, 10, 25);
                //worksheet.Cells.Merge(1, 1, 1, columns.Count);
                SetCellStyle(start, 1, start + 5, columns.Count);
                SetValue(start, 1, bottom);

                SetMergeCell(1, 1, 1, 7);

                AddPicture(0, 0);
            }

            if (isHeader)
            {
                //表头
                if (columns.Count > 0)
                {
                    int columnIndex = 1;

                    foreach (DataColumn dc in columns)
                    {
                        SetStyle(addIndex, columnIndex);
                        SetValue(addIndex, columnIndex, dc.ColumnName);
                        columnIndex += 1;
                    }
                }
            }

            //数据
            if (dt.Rows.Count > 0)
            {
                int rowIndex = 1 + addIndex;

                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < columns.Count; i++)
                    {
                        SetStyle(rowIndex, i + 1);
                        SetValue(rowIndex, i + 1, dr[i].ToString());
                    }

                    rowIndex += 1;
                }
            }
        }

        public void AddPicture(int RowIndex, int colIndex)
        {
            int pixelTop = 88;
            int pixelLeft = 129;
            int width = 40;
            int height = 40;

            string path = HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString());//HttpContext.Current.Server.MapPath("~/");
            var img_url = path + "/Images/logo/logo-240x60.png";
            Image img = Image.FromStream(WebRequest.Create(img_url).GetResponse().GetResponseStream());
            //img = cutEllipse(img, new Rectangle(0, 0, img.Width, img.Height), new Size(60, 60));
            ExcelPicture pic = worksheet.Drawings.AddPicture("LOGO", img);
            pic.SetPosition(RowIndex, 0, colIndex, 0);
            //pic.SetPosition(pixelTop, pixelLeft);
            //pic.EditAs = OfficeOpenXml.Drawing.eEditAs.TwoCell;
            //pic.SetSize(width, height);            
            //pic.SetSize(65); //只设置一个值宽度等于高度
        }

        public DataTable ReadExcelToDatatable(string filePath)
        {
            DataTable dt = new DataTable();

            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
            {
                using (ExcelPackage package = new ExcelPackage())
                {
                    package.Load(fs);

                    // 工作簿只取第一页
                    if (package.Workbook.Worksheets.Count == 0)
                    {
                        return dt;
                    }

                    ExcelWorksheet sheet = package.Workbook.Worksheets.First();

                    // 必须要有数据
                    if (sheet.Dimension == null)
                    {
                        return dt;
                    }

                    int columnCount = sheet.Dimension.End.Column;
                    int rowCount = sheet.Dimension.End.Row;

                    if (rowCount == 0 || columnCount == 0)
                    {
                        return dt;
                    }

                    // 生成表格列
                    object objCellValue;
                    string cellValue;

                    for (int j = 0; j < columnCount; j++)
                    {
                        objCellValue = sheet.Cells[1, j + 1].Value;
                        cellValue = objCellValue == null ? "" : objCellValue.ToString();
                        dt.Columns.Add(cellValue, typeof(string));
                    }

                    // 添加数据
                    DataRow dr;

                    for (int i = 2; i <= rowCount; i++)
                    {
                        dr = dt.NewRow();
                        for (int j = 1; j <= columnCount; j++)
                        {
                            objCellValue = sheet.Cells[i, j].Value;
                            if (objCellValue != null)
                            {
                                if (sheet.Cells[i, j].Style.Numberformat.Format.IndexOf("yyyy") > -1 && sheet.Cells[i, j].Value.GetType().ToString() == "System.Double")//注意这里，是处理日期时间格式的关键代码 
                                    objCellValue = sheet.Cells[i, j].GetValue<DateTime>();
                            }
                            cellValue = objCellValue == null ? "" : objCellValue.ToString();
                            dr[j - 1] = cellValue;
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }

            return dt;
        }

        public static DataSet ReadExcelToDataSet(string filePath)
        {
            DataSet ds = new DataSet();
            DataRow dr;
            object objCellValue;
            string cellValue;
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
            {
                //EPPlus 5.0 以后的版本需要指定 商业证书 或者非商业证书
                //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (ExcelPackage package = new ExcelPackage())
                {
                    package.Load(fs);

                    int count = 0;

                    foreach (var sheet in package.Workbook.Worksheets)
                    {
                        if (count != 0)
                        {
                            break;
                        }

                        count++;

                        //if (sheet.Name.ToUpper().Trim() == sheetName.ToUpper().Trim())
                        //{
                        if (sheet.Dimension == null) continue;
                        #region                       
                        var columnCount = sheet.Dimension.End.Column;
                        var rowCount = sheet.Dimension.End.Row;
                        if (rowCount > 0)
                        {
                            DataTable dt = new DataTable(sheet.Name);
                            for (int j = 0; j < columnCount; j++)//设置DataTable列名
                            {
                                objCellValue = sheet.Cells[1, j + 1].Value;
                                cellValue = objCellValue == null ? "" : objCellValue.ToString();
                                dt.Columns.Add(cellValue, typeof(string));
                            }
                            for (int i = 2; i <= rowCount; i++)
                            {
                                dr = dt.NewRow();
                                for (int j = 1; j <= columnCount; j++)
                                {
                                    objCellValue = sheet.Cells[i, j].Value;
                                    if (objCellValue != null)
                                    {
                                        if (sheet.Cells[i, j].Style.Numberformat.Format.IndexOf("yyyy") > -1 && sheet.Cells[i, j].Value.GetType().ToString() == "System.Double")//注意这里，是处理日期时间格式的关键代码 
                                            objCellValue = sheet.Cells[i, j].GetValue<DateTime>();
                                    }
                                    cellValue = objCellValue == null ? "" : objCellValue.ToString();
                                    dr[j - 1] = cellValue;
                                }
                                dt.Rows.Add(dr);
                            }
                            ds.Tables.Add(dt);
                            //}
                            #endregion
                        }
                    }
                }
            }
            return ds;
        }
    }
}