EPPlus

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

namespace weirManagement.BaseClass
{
    public class EPPlusHelper : BaseClass
    {
        public static void ExportExcel(HttpContext context, DataTable dt, string filename)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");

                ws.Cells["A1"].LoadFromDataTable(dt, true);

                var data = pck.GetAsByteArray();
                context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                context.Response.AddHeader("content-disposition", "attachment;  filename=" + filename + ".xlsx");
                context.Response.AddHeader("Content-Length", data.Length.ToString());
                context.Response.BinaryWrite(data);
            }
        }

        public static void ExportExcel_set(HttpContext context, DataTable dt, string filename, Dictionary<int, int> dBold, Dictionary<int, int> dRed, Dictionary<int, int> dGray)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //foreach (var kp in dict)
                //{
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
                EPPlusHelper eh = new EPPlusHelper();
                DataRow dr = dt.NewRow();
                dr[0] = eh.GetExportExcel_Remark2(0);
                dt.Rows.Add(dr);
                DataRow dr1 = dt.NewRow();
                dr1[0] = eh.GetExportExcel_Remark2(1);
                dt.Rows.Add(dr1);
                DataRow dr2 = dt.NewRow();
                dr2[0] = eh.GetExportExcel_Remark2(2);
                dt.Rows.Add(dr2);
                DataRow dr3 = dt.NewRow();
                dr3[0] = eh.GetExportExcel_Remark2(3);
                dt.Rows.Add(dr3);


                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(dt, true);

                using (ExcelRange rng = ws.Cells[1, 1, 1, dt.Columns.Count])
                {
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);  //Set color to dark blue
                }
                foreach (var i in dBold)
                {
                    using (ExcelRange rng = ws.Cells[1, i.Value + 1, 1, i.Value + 1])
                    {
                        rng.Style.Font.Bold = true;
                        //rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    }
                }
                foreach (var i in dRed)
                {
                    using (ExcelRange rng = ws.Cells[1, i.Value + 1, 1, i.Value + 1])
                    {
                        //rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                        rng.Style.Font.Color.SetColor(System.Drawing.Color.Red);
                    }
                }
                foreach (var i in dGray)
                {
                    using (ExcelRange rng = ws.Cells[1, i.Value + 1, 1, i.Value + 1])
                    {
                        //rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                        rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Silver);
                    }
                }
                int rowcount = dt.Rows.Count;
                using (ExcelRange rng = ws.Cells[rowcount - 2, 1, rowcount + 1, 1])
                {
                    rng.Style.Font.Color.SetColor(System.Drawing.Color.Red);
                }
                //Format the header for column 1-3
                //using (ExcelRange rng = ws.Cells["A1:C1"])
                //{
                //    rng.Style.Font.Bold = true;
                //    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                //    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));  //Set color to dark blue
                //    rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
                //}


                ////Example how to Format Column 1 as numeric 
                //using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
                //{
                //    col.Style.Numberformat.Format = "#,##0.00";
                //    col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                //}

                //}
                //Write it back to the client
                var data = pck.GetAsByteArray();
                context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                context.Response.AddHeader("content-disposition", "attachment;  filename=" + filename + ".xlsx");
                context.Response.AddHeader("Content-Length", data.Length.ToString());
                context.Response.BinaryWrite(data);
            }
        }


        public string GetExportExcel_Remark2(int type)
        {
            //StringBuilder sb = new StringBuilder();
            string strTip = "";
            if (isZhCh())
            {
                switch (type)
                {
                    case 0:
                        strTip = "1. 黑色加粗的标题，表示此列数据不允许更改；";
                        break;
                    case 1:
                        strTip = "2. 红色标题，表示此列数据必须填写；";
                        break;
                    case 2:
                        strTip = "3. 灰底色标题，表示此列数据仅展示，不做导入编辑功能；";
                        break;
                    case 3:
                        strTip = "4. 导入数据前请将此备注信息删除！";
                        break;
                }
                //strTip = " 1. 黑色加粗的标题，表示此列数据不允许更改；\n\r 2. 红色标题，表示此列数据必须填写；\n\r 3. 灰底色标题，表示此列数据仅展示，不做导入编辑功能；\n\r 4. 导入数据请请将此备注信息删除！"; 
            }

            else
            {
                //strTip = " 1. Black bold title, says the column data is not allowed to change; \n\r 2. Red title, says the column data must be completed; \n\r 3. A title with gray background color, says the column data show only, do not be imported or edit; \n\r 4. Please delete this remark before you import data!";
                switch (type)
                {
                    case 0:
                        strTip = "1. Black bold title, says the column data is not allowed to change;";
                        break;
                    case 1:
                        strTip = "2. Red title, says the column data must be completed;";
                        break;
                    case 2:
                        strTip = "3. A title with gray background color, says the column data show only, do not be imported or edit;";
                        break;
                    case 3:
                        strTip = "4. Please delete this remark before you import data!";
                        break;
                }
            }
            return strTip;
        }


        public static DataTable Import(string fileName)
        {
            var dt = new DataTable();
            using (var excel = new ExcelPackage(new FileInfo(fileName)))
            {
                var sheet = excel.Workbook.Worksheets.First();
                if (sheet == null)
                {
                    return null;
                }
                foreach (var cell in sheet.Cells[1, 1, 1, sheet.Dimension.End.Column])
                {
                    dt.Columns.Add(cell.Value.ToString());
                }

                var rows = sheet.Dimension.End.Row;
                //for (var i = 1; i < rows; i++)
                //{
                //    var row = sheet.Cells[i + 1, 1, i + 1, sheet.Dimension.End.Column];
                //    dt.Rows.Add(row..ToArray());
                //}
                //for (var i = 1; i < rows; i++)
                //{                   
                //    var row = sheet.Cells[i + 1, 1, i + 1, sheet.Dimension.End.Column];
                //    dt.Rows.Add(row.Select(cell => cell.Value).ToArray());
                //}
                //----正确的
                int n = sheet.Dimension.End.Column;
                for (var i = 2; i < rows + 1; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (var j = 0; j < n; j++)
                    {
                        if (sheet.Cells[i, j + 1, i, j + 1].Value == null)
                        {
                            dr[j] = "";
                        }
                        else { dr[j] = sheet.Cells[i, j + 1, i, j + 1].Value.ToString(); }
                    }
                    dt.Rows.Add(dr);
                }
                //^正确的
                return dt;
            }
        }
    }
}