c# 使用NPOI按模板導出excel

 excel模板設置 字體

使用方式：this

1.首先查詢數據，DataSet ds=GetData(orderno);spa

2.而後調用，支持一個sheet模板導出多個數據sheet，多個sheet模板導出多個數據sheetexcel

string fpath =  "私車公用單.xls";

            ExcelHelper.Helper excel = new ExcelHelper.Helper();


            NPOI241.SS.UserModel.IWorkbook workbook = excel.Export(fpath, (book) =>
            {

                List<ExcelHelper.Helper.ExcelHelperSheetInfo> sinfo = new List<ExcelHelper.Helper.ExcelHelperSheetInfo>();
                ExcelHelper.Helper.ExcelHelperSheetInfo info = new ExcelHelper.Helper.ExcelHelperSheetInfo();
                sinfo.Add(info);

                info.ModelSheet = book.GetSheetAt(0);
                info.NewSheetData = new List<object>();
               
                Hashtable ht = new Hashtable();
                info.NewSheetData.Add( ht);

                ht.Add("header", ds.Tables[0].Rows[0]);
                ht.Add("detail", ds.Tables[1].DefaultView);
                ht.Add("ad",ds.Tables[2].DefaultView);

                return sinfo;
            }, (book, sheet, info) => {
                sheet.ProtectSheet("123456");
            });

using Newtonsoft.Json;
using NPOI241.HSSF.Record;
using NPOI241.HSSF.UserModel;
using NPOI241.HSSF.Util;
using NPOI241.SS.UserModel;
using NPOI241.SS.UserModel.Charts;
using NPOI241.SS.Util;
using NPOI241.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;

namespace WebPortal.Common.ExcelHelper
{
    public struct SimpleCell
    {
        /// <summary>
        /// 文本
        /// </summary>
        public string T { get; set; }

        /// <summary>
        /// colspan
        /// </summary>
        public int C { get; set; }
        /// <summary>
        /// rowspan
        /// </summary>
        public int R { get; set; }

        /// <summary>
        /// 樣式 文本顏色，背景顏色，字體是否加粗,對齊,寬度
        /// </summary>
        public string S { get; set; }

        /// <summary>
        /// 備註
        /// </summary>
        public string RK { get; set; }

    }


    public struct SimpleChart
    {
        /// <summary>
        /// 標題
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 類型 chart類型  bar和line
        /// </summary>
        public string Type { get; set; }
        /// <summary>
        /// 數據起始單元格
        /// </summary>
        public int Col1 { get; set; }
        /// <summary>
        /// 數據起始行
        /// </summary>
        public int Row1 { get; set; }

        /// <summary>
        /// 數據結束單元格
        /// </summary>
        public int Col2 { get; set; }
        /// <summary>
        /// 數據結束行
        /// </summary>
        public int Row2 { get; set; }

        /// <summary>
        /// 是否應用右邊Y軸，默認左邊Y軸
        /// </summary>
        public bool RightY { get; set; }
    }

    public class SimpleTable
    {
        /// <summary>
        /// 行數據如
        /// </summary>
        public List<List<SimpleCell>> Rows { get; set; }

        /// <summary>
        /// 顏色
        /// </summary>
        public List<string> Colors { get; set; }

        /// <summary>
        /// 樣式
        /// </summary>
        public List<string> Styles { get; set; }

        /// <summary>
        /// 凍結的行 只考慮凍結表頭
        /// </summary>
        public int FRow { get; set; }

        /// <summary>
        /// 凍結的列
        /// </summary>
        public int FCol { get; set; }

        /// <summary>
        /// 圖表信息
        /// </summary>
        public List<SimpleChart> Chart { get; set; }

        /// <summary>
        /// X軸數據起始單元格
        /// </summary>
        public int Col1 { get; set; }
        /// <summary>
        /// X軸數據起始行
        /// </summary>
        public int Row1 { get; set; }

        /// <summary>
        /// X軸數據結束單元格
        /// </summary>
        public int Col2 { get; set; }
        /// <summary>
        /// X軸數據結束行
        /// </summary>
        public int Row2 { get; set; }

        /// <summary>
        /// 是否設置右邊Y軸
        /// </summary>
        public bool RightY { get; set; }

        /// <summary>
        /// 是否只讀
        /// </summary>
        public bool IsReadOnly { get; set; }

        public string PWD { get; set; }
    }

    public struct CellStyleWrap
    {
        public int? Width { get; set; }
        public ICellStyle CellStyle { get; set; }




        public CellStyleWrap(int? w, ICellStyle cs)
            : this()
        {
            this.Width = w;
            this.CellStyle = cs;
        }

    }


    public class Helper
    {


        void CreateChart(ISheet sheet, int col1, int col2, int row1, int row2, SimpleTable st)
        {
            IDrawing drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, col1, row1, col2, row2);

            var chart = drawing.CreateChart(anchor) as XSSFChart;

            //生成圖例
            var legend = chart.GetOrCreateLegend();
            //圖例位置
            legend.Position = LegendPosition.Top;


            // X軸.
            var bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);


            //Y軸
            IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
            leftAxis.Crosses = (AxisCrosses.AutoZero);



            //暫時不支持雙y軸
            IValueAxis rigthAxis = leftAxis;
            //if (st.RightY)
            //{
            //    rigthAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Right);
            //    rigthAxis.Crosses = (AxisCrosses.AutoZero);
            //}

            //x軸
            IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(st.Row1, st.Row2, st.Col1, st.Col2));


            IBarChartData<double, double> bardata = null;
            ILineChartData<double, double> linedata = null;
            IScatterChartData<double, double> scatterdata = null;
            foreach (SimpleChart sc in st.Chart)
            {
                //圖表
                var data = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(sc.Row1, sc.Row2, sc.Col1, sc.Col1));
                SetDoubleData(sheet, sc.Col1, sc.Col2, sc.Row1, sc.Row2);
                switch (sc.Type)
                {
                    case "line":
                        if (linedata == null)
                            linedata = chart.ChartDataFactory.CreateLineChartData<double, double>();

                        var sline = linedata.AddSeries(xs, data);
                        sline.SetTitle(sc.Title);
                        break;
                    case "bar":
                        if (bardata == null)
                            bardata = chart.ChartDataFactory.CreateBarChartData<double, double>();

                        var sbar = bardata.AddSeries(xs, data);
                        sbar.SetTitle(sc.Title);
                        break;
                    case "scatter":
                        if (scatterdata == null)
                            scatterdata = chart.ChartDataFactory.CreateScatterChartData<double, double>();

                        var stline = scatterdata.AddSeries(xs, data);
                        stline.SetTitle(sc.Title);
                        break;
                    default:
                        throw new Exception("暫不支持【" + sc.Type + "】類型的圖表,只支持line、bar、scatter！");

                }
            }

            if (bardata != null)
                chart.Plot(bardata, bottomAxis, leftAxis);
            if (linedata != null)
                chart.Plot(linedata, bottomAxis, leftAxis);
            if (scatterdata != null)
                chart.Plot(scatterdata, bottomAxis, leftAxis);
        }

        public void SetDoubleData(ISheet sheet, int col1, int col2, int row1, int row2)
        {
            while (row1 <= row2)
            {
                IRow row = sheet.GetRow(row1);
                if (row == null)
                    continue;
                int begcol = col1;
                while (begcol <= col2)
                {
                    ICell cell = row.GetCell(begcol);
                    if (cell.CellType == CellType.String)
                    {
                        var value = cell.StringCellValue;
                        if (!string.IsNullOrWhiteSpace(value))
                        {
                            value = value.Replace(",", "").Trim();
                            double dbval;
                            if (double.TryParse(value, out dbval))
                            {
                                cell.SetCellType(CellType.Numeric);
                                cell.SetCellValue(dbval);
                            }
                        }
                    }


                    begcol += 1;
                }


                row1 += 1;
            }

        }


        private void aa(List<short> listcolor, short color)
        {
            if (color > PaletteRecord.STANDARD_PALETTE_SIZE)
                return;
            if (listcolor.IndexOf(color) < 0)
            {
                listcolor.Add(color);
            }
        }

        private Dictionary<string, short> CreateColor(HSSFWorkbook hssfWorkbook, List<string> colors)
        {
            Dictionary<string, short> dictret = new Dictionary<string, short>();
            HSSFPalette palette = hssfWorkbook.GetCustomPalette();

            List<short> listcolor = new List<short>();

            short index = 0;
            IFont existsfont;
            while (index < hssfWorkbook.NumberOfFonts)
            {

                existsfont = hssfWorkbook.GetFontAt(index);
                aa(listcolor, existsfont.Color);

                index += 1;
            }
            index = 0;

            ICellStyle existscs;
            while (index < hssfWorkbook.NumCellStyles)
            {
                existscs = hssfWorkbook.GetCellStyleAt(index);
                aa(listcolor, existscs.FillBackgroundColor);
                aa(listcolor, existscs.FillForegroundColor);
                aa(listcolor, existscs.BottomBorderColor);
                aa(listcolor, existscs.TopBorderColor);
                aa(listcolor, existscs.LeftBorderColor);
                aa(listcolor, existscs.RightBorderColor);
                index += 1;
            }


            for (int i = 0; i < colors.Count; i++)
            {

                string color = colors[i];
                string[] colorstr = color.Replace("rgba(", "").Replace("rgb(", "").Replace(")", "").Split(',');

                byte r = Convert.ToByte(colorstr[0].Trim()), g = Convert.ToByte(colorstr[1].Trim()), b = Convert.ToByte(colorstr[2].Trim());


                HSSFColor hc = palette.FindColor(r, g, b);
                if (hc != null)
                {
                    dictret.Add(color, hc.Indexed);
                    listcolor.Add(hc.Indexed);
                    continue;
                }

                short colorindex = PaletteRecord.FIRST_COLOR_INDEX;
                short endcolorindex = PaletteRecord.STANDARD_PALETTE_SIZE;

                while (colorindex < endcolorindex)
                {
                    if (listcolor.IndexOf(colorindex) < 0)
                    {
                        palette.SetColorAtIndex(colorindex, r, g, b);
                        dictret.Add(color, colorindex);
                        listcolor.Add(colorindex);
                        break;
                    }
                    colorindex += 1;
                }

            }
            return dictret;
        }

        private Dictionary<string, XSSFColor> CreateColor(XSSFWorkbook hssfWorkbook, List<string> colors)
        {
            Dictionary<string, XSSFColor> dictret = new Dictionary<string, XSSFColor>();


            List<short> listcolor = new List<short>();


            for (int i = 0; i < colors.Count; i++)
            {

                string color = colors[i];
                string[] colorstr = color.Replace("rgba(", "").Replace("rgb(", "").Replace(")", "").Split(',');

                byte r = Convert.ToByte(colorstr[0].Trim()), g = Convert.ToByte(colorstr[1].Trim()), b = Convert.ToByte(colorstr[2].Trim());

                dictret.Add(color, new XSSFColor(new byte[] { r, g, b }));

            }
            return dictret;
        }

        private Dictionary<string, CellStyleWrap> CreateCellStyle(IWorkbook book, ICellStyle df, List<string> style, Dictionary<string, short> diccolor)
        {
            Dictionary<string, CellStyleWrap> dicret = new Dictionary<string, CellStyleWrap>();

            foreach (string str in style)
            {
                string[] strs = str.Split(';');
                string fc = strs[0];
                string bc = strs[1];
                string fw = strs[2];
                string al = strs[3];
                string w = strs[4];

                if (fw == "bold")
                    fw = "400";
                ICellStyle cs;
                if (fc != "" || bc != "" || fw != "" || al != "")
                {
                    cs = book.CreateCellStyle();
                    cs.CloneStyleFrom(df);
                }
                else
                {
                    cs = df;
                }
                short color;

                if (bc != "" && diccolor.TryGetValue(bc, out color))
                {

                    cs.FillForegroundColor = color;
                    cs.FillPattern = FillPattern.SolidForeground;
                }
                color = 0;
                short fontcolor = 0;
                if (fw != "")
                    short.TryParse(fw, out color);
                if (fc != "")
                    diccolor.TryGetValue(fc, out fontcolor);
                if (color > 0 || fontcolor > 0)
                {
                    color *= 2;
                    IFont font = cs.GetFont(book);
                    IFont fontnew = book.CreateFont();
                    fontnew.Charset = font.Charset;
                    fontnew.Color = fontcolor == 0 ? font.Color : fontcolor;
                    fontnew.FontHeight = font.FontHeight;
                    fontnew.FontHeightInPoints = font.FontHeightInPoints;
                    fontnew.FontName = font.FontName;
                    fontnew.IsItalic = font.IsItalic;
                    fontnew.IsStrikeout = font.IsStrikeout;
                    fontnew.TypeOffset = font.TypeOffset;
                    fontnew.Underline = font.Underline;
                    fontnew.Boldweight = color == 0 ? font.Boldweight : color;
                    cs.SetFont(fontnew);
                }
                switch (al.ToLower())
                {
                    case "center":
                        cs.Alignment = HorizontalAlignment.Center;
                        break;
                    case "right":
                        cs.Alignment = HorizontalAlignment.Right;
                        break;
                }

                int? with = null;
                int widthtmp;
                if (int.TryParse(w, out widthtmp))
                    with = widthtmp;

                dicret.Add(str, new CellStyleWrap(with, cs));
            }

            return dicret;

        }

        private Dictionary<string, CellStyleWrap> CreateCellStyle(IWorkbook book, ICellStyle df, List<string> style, Dictionary<string, XSSFColor> diccolor)
        {
            Dictionary<string, CellStyleWrap> dicret = new Dictionary<string, CellStyleWrap>();

            foreach (string str in style)
            {
                string[] strs = str.Split(';');
                string fc = strs[0];
                string bc = strs[1];
                string fw = strs[2];
                string al = strs[3];
                string w = strs[4];

                if (fw == "bold")
                    fw = "400";
                ICellStyle cs;
                if (fc != "" || bc != "" || fw != "" || al != "")
                {
                    cs = book.CreateCellStyle();
                    cs.CloneStyleFrom(df);
                }
                else
                {
                    cs = df;
                }
                short color;
                XSSFColor bcolor;

                if (bc != "" && diccolor.TryGetValue(bc, out bcolor))
                {
                    ((XSSFCellStyle)cs).SetFillForegroundColor(bcolor);
                    cs.FillPattern = FillPattern.SolidForeground;
                }
                color = 0;
                XSSFColor fontcolor = null;
                bool hasfc = false;
                if (fw != "")
                    short.TryParse(fw, out color);
                if (fc != "")
                    hasfc = diccolor.TryGetValue(fc, out fontcolor);
                if (color > 0 || hasfc)
                {
                    color *= 2;
                    IFont font = cs.GetFont(book);

                    IFont fontnew = book.CreateFont();
                    fontnew.Charset = font.Charset;
                    if (!hasfc)
                        fontnew.Color = font.Color;
                    else
                        ((XSSFFont)fontnew).SetColor(fontcolor);
                    fontnew.FontHeight = font.FontHeight;
                    fontnew.FontHeightInPoints = font.FontHeightInPoints;
                    fontnew.FontName = font.FontName;
                    fontnew.IsItalic = font.IsItalic;
                    fontnew.IsStrikeout = font.IsStrikeout;
                    fontnew.TypeOffset = font.TypeOffset;
                    fontnew.Underline = font.Underline;
                    fontnew.Boldweight = color == 0 ? font.Boldweight : color;
                    cs.SetFont(fontnew);
                }
                switch (al.ToLower())
                {
                    case "center":
                        cs.Alignment = HorizontalAlignment.Center;
                        break;
                    case "right":
                        cs.Alignment = HorizontalAlignment.Right;
                        break;
                }

                int? with = null;
                int widthtmp;
                if (int.TryParse(w, out widthtmp))
                    with = widthtmp;

                dicret.Add(str, new CellStyleWrap(with, cs));
            }

            return dicret;

        }


        public void ToExcel(string data, Stream stream, bool is2003)
        {
            try
            {
                SimpleTable st = JsonConvert.DeserializeObject<SimpleTable>(data);



                IWorkbook book = null;
                if (is2003)
                {
                    book = new HSSFWorkbook();
                }
                else
                {
                    book = new XSSFWorkbook();
                }

                ICellStyle dfcs = book.CreateCellStyle();
                dfcs.Alignment = HorizontalAlignment.Left;
                dfcs.VerticalAlignment = VerticalAlignment.Center;
                dfcs.BorderBottom = BorderStyle.Thin;
                dfcs.BorderLeft = BorderStyle.Thin;
                dfcs.BorderRight = BorderStyle.Thin;
                dfcs.BorderTop = BorderStyle.Thin;
                if (st.IsReadOnly)
                    dfcs.IsLocked = true;


                Dictionary<string, CellStyleWrap> dicstyle = null;
                if (is2003)
                {
                    Dictionary<string, short> dicColor = CreateColor((HSSFWorkbook)book, st.Colors);
                    dicstyle = CreateCellStyle(book, dfcs, st.Styles, dicColor);
                }
                else
                {
                    Dictionary<string, XSSFColor> dicColor = CreateColor((XSSFWorkbook)book, st.Colors);
                    dicstyle = CreateCellStyle(book, dfcs, st.Styles, dicColor);
                }

                ISheet sheet = book.CreateSheet();

                IDrawing patr = sheet.CreateDrawingPatriarch();
                int rowindex = 0;
                foreach (List<SimpleCell> cells in st.Rows)
                {
                    IRow row = sheet.CreateRow(rowindex);
                    int cellindex = 0;
                    foreach (SimpleCell sc in cells)
                    {
                        ICell cell = row.CreateCell(cellindex);
                        if (!string.IsNullOrEmpty(sc.T))
                        {
                            cell.SetCellValue(sc.T);
                        }

                        CellStyleWrap csw = default(CellStyleWrap);
                        if (sc.S != null && dicstyle.TryGetValue(sc.S, out csw))
                        {
                            cell.CellStyle = csw.CellStyle;
                        }
                        else
                        {
                            cell.CellStyle = dfcs;
                        }

                        if (!string.IsNullOrEmpty(sc.RK))
                        {
                            IComment comment = null;
                            if (is2003)
                            {
                                comment = patr.CreateCellComment(new HSSFClientAnchor(255, 125, 1023, 150, cellindex, rowindex, cellindex + 2, rowindex + 4));
                                comment.String = new HSSFRichTextString(sc.RK);
                            }
                            else
                            {
                                comment = patr.CreateCellComment(new XSSFClientAnchor(255, 125, 1023, 150, cellindex, rowindex, cellindex + 2, rowindex + 4));
                                comment.String = new XSSFRichTextString(sc.RK);
                            }

                            cell.CellComment = comment;
                        }

                        if (sc.C > 0 || sc.R > 0)
                        {
                            sheet.AddMergedRegion(new NPOI241.SS.Util.CellRangeAddress(rowindex, rowindex + (sc.R == 0 ? 0 : sc.R - 1), cellindex, cellindex + (sc.C == 0 ? 0 : sc.C - 1)));
                        }

                        cellindex += 1;
                    }
                    rowindex += 1;
                }



                int lastrowindex = rowindex;



                List<SimpleCell> rows = st.Rows[st.FRow];
                int index = 0;
                foreach (SimpleCell sc in rows)
                {
                    CellStyleWrap csw = default(CellStyleWrap);
                    if (sc.S != null && dicstyle.TryGetValue(sc.S, out csw) && csw.Width != null)
                    {
                        sheet.SetColumnWidth(index, csw.Width.Value * 256);
                    }
                    index += 1;
                }

                if (st.FCol > 0 || st.FRow > 0)
                {
                    sheet.CreateFreezePane(st.FCol, st.FRow);
                }
                if (st.Chart != null && st.Chart.Count > 0)
                {
                    CreateChart(sheet, 0, 0 + 10, lastrowindex + 3, lastrowindex + 3 + 20, st);
                }

                if (st.IsReadOnly)
                {
                    sheet.ProtectSheet(st.PWD);
                }
                book.Write(stream);


            }
            catch
            {
                throw;
            }

        }



        public void ToExcel(Stream stream, DataTable[] dts, NameValueCollection[] titles, bool is2003)
        {
            try
            {
                IWorkbook book = null;
                if (is2003)
                {
                    book = new HSSFWorkbook();
                }
                else
                {
                    book = new XSSFWorkbook();
                }

                ICellStyle dfcs = book.CreateCellStyle();
                dfcs.Alignment = HorizontalAlignment.Left;
                dfcs.VerticalAlignment = VerticalAlignment.Center;
                dfcs.BorderBottom = BorderStyle.Thin;
                dfcs.BorderLeft = BorderStyle.Thin;
                dfcs.BorderRight = BorderStyle.Thin;
                dfcs.BorderTop = BorderStyle.Thin;

                int i = 0;

                foreach (DataTable dt in dts)
                {
                    ISheet sheet = book.CreateSheet(dt.TableName);

                    NameValueCollection nvc = titles[i];
                    int rowindex = 0;
                    int cellindex = 0;
                    IRow row;
                    ICell cell;
                    row = sheet.CreateRow(rowindex);
                    foreach (string key in nvc)
                    {
                        cell = row.CreateCell(cellindex);
                        cell.CellStyle = dfcs;
                        cell.SetCellValue(nvc[key]);
                        cellindex += 1;
                    }

                    foreach (DataRow dr in dt.Rows)
                    {
                        rowindex += 1;
                        row = sheet.CreateRow(rowindex);
                        cellindex = 0;
                        foreach (string key in nvc)
                        {
                            cell = row.CreateCell(cellindex);
                            cell.CellStyle = dfcs;
                            cell.SetCellValue(dr[key].ToString());
                            cellindex += 1;
                        }
                    }
                    i += 1;
                }
                book.Write(stream);
            }
            catch
            {
                throw;
            }
        }


        public class ExcelHelperSheetInfo
        {

            /// <summary>
            /// 新sheet數據，每個數據項生產一個sheet頁
            /// </summary>
            public List<object> NewSheetData { get; set; }

            /// <summary>
            /// 模板Sheet
            /// </summary>
            public ISheet ModelSheet { get; set; }

        }

        /// <summary>
        /// 綁定數據前綴,所有小寫，默認$
        /// </summary>
        public string Prefix
        {
            get;
            set;
        }

        /// <summary>
        /// 列表開始前綴,,所有小寫默認$list.
        /// </summary>
        public string BeginListPrefix
        {
            get;
            set;
        }

        /// <summary>
        /// 列表結束前綴,所有小寫,默認$listend.
        /// </summary>
        public string EndListPrefix
        {
            get;
            set;
        }

        /// <summary>
        /// 自動行高前綴,所有小寫 默認$auotheight,放到列後面
        /// </summary>
        public string AutoHeightPrefix
        {
            get;
            set;
        }

        //列表序列號字符
        public string ListSeqNoStr
        {
            get;
            set;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="prefix">綁定數據前綴，默認$</param>
        /// <param name="beglistprefix">列表開始前綴,默認$list.</param>
        /// <param name="endlistprefix">列表結束前綴,默認$listend.</param>
        public Helper(string prefix = "$", string beglistprefix = "$list.", string endlistprefix = "$listend.", string seqnostr = "listseqno", string autoheight = "$auotheight")
        {
            this.Prefix = prefix;
            this.BeginListPrefix = beglistprefix;
            this.EndListPrefix = endlistprefix;
            this.ListSeqNoStr = seqnostr;
            this.AutoHeightPrefix = autoheight;
        }
        /// <summary>
        /// 檢查是不是2003之前的版本，根據後綴判斷
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static bool Is2003(string file)
        {
            string ext = Path.GetExtension(file);

            return ext.ToLower() == ".xls";
        }


        /// <summary>
        /// 返回正確的sheet名稱
        /// </summary>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static string GetSheetName(string sheetname)
        {
            sheetname = sheetname.Replace("//", "").Replace("\"", "").Replace("[", "").Replace("]", "");
            sheetname = sheetname.Replace("<", "").Replace(">", "").Replace("\r", "").Replace("\n", "");
            sheetname = sheetname.Replace("*", "").Replace("|", "").Replace(":", "");
            sheetname = sheetname.Replace("?", "").Replace("/", "");
            return sheetname;
        }


        /// <summary>
        /// 按模板導出數據，保存到流
        /// </summary>
        /// <param name="modalfilepath">模板路徑</param>
        /// <param name="dicdata">數據</param>
        /// <param name="fncreate">根據數據建立sheet</param>
        /// <param name="ms">保存到流</param>
        /// <param name="acend">數據填充完成後執行的方法</param>
        public IWorkbook Export(string modalfilepath, Func<IWorkbook, List<ExcelHelperSheetInfo>> fncreate, Action<IWorkbook, ISheet, ExcelHelperSheetInfo> acend = null)
        {

            try
            {


                IWorkbook excel = null;

                using (FileStream fs = new FileStream(modalfilepath, FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    if (Is2003(modalfilepath))
                    {
                        excel = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        excel = new XSSFWorkbook(fs);
                    }

                    List<ExcelHelperSheetInfo> list = fncreate(excel);



                    foreach (ExcelHelperSheetInfo dr in list)
                    {
                        ISheet sheet = dr.ModelSheet;
                        int modelsheetindex = excel.GetSheetIndex(sheet);

                        foreach (object sheetData in dr.NewSheetData)
                        {
                            ISheet sheetnew = excel.CloneSheet(modelsheetindex);
                            ExprotGongYiBiao(sheetnew, sheetData);
                            if (acend != null)
                            {
                                acend(excel, sheetnew, dr);
                            }
                        }
                        excel.RemoveSheetAt(modelsheetindex);
                    }
                }

                return excel;

            }
            catch
            {

                throw;
            }

        }

        /// <summary>
        /// 按模板導出單個sheet
        /// </summary>
        /// <param name="sheetnew"></param>
        /// <param name="dicdata"></param>
        public void ExprotGongYiBiao(ISheet sheetnew, object dicdata)
        {
            int maxrow = sheetnew.LastRowNum;

            int minrow = 0;
            int newrowno = 0;

            sheetnew.FitToPage = true;

            while (minrow <= maxrow)
            {
                IRow row = sheetnew.GetRow(minrow);
                if (row == null)
                {
                    minrow += 1;
                    continue;
                }


                int cellindex = 0;
                int maxcell = row.LastCellNum;

                bool islist = false, islistend = false;
                int listbegnum = 0;

                string listkey = "";
                List<string> listkeys = null;
                bool hassetdata = false;
                bool autoheight = false;
                while (cellindex <= maxcell)
                {
                    var modelcell = row.GetCell(cellindex);
                    if (modelcell == null)
                    {
                        cellindex += 1;
                        continue;
                    }

                    string cellvalue = modelcell.ToString().Trim();
                    string cellvaluelower = cellvalue.ToLower();

                    if (cellvaluelower.StartsWith(BeginListPrefix))
                    {
                        islist = true;
                        listbegnum = cellindex;
                        cellvalue = cellvalue.Substring(BeginListPrefix.Length);
                        listkey = cellvalue.Split(' ')[0];
                        listkeys = new List<string>();
                        listkeys.Add(cellvalue.Split(' ')[1]);
                    }
                    else if (islist)
                    {
                        if (cellvaluelower.StartsWith(EndListPrefix))
                        {
                            islistend = true;
                            cellvalue = cellvalue.Substring(EndListPrefix.Length);
                            listkeys.Add(cellvalue);
                        }
                        else if (cellvaluelower == AutoHeightPrefix)
                        {
                            autoheight = true;
                            row.RemoveCell(modelcell);
                        }
                        else if (cellvaluelower.StartsWith(Prefix))
                        {
                            listkeys.Add(cellvalue.Substring(Prefix.Length));
                        }
                        else if (!islistend)
                        {
                            listkeys.Add("");
                        }
                    }
                    else if (cellvaluelower == AutoHeightPrefix)
                    {
                        autoheight = true;
                        row.RemoveCell(modelcell);
                    }
                    else if (cellvaluelower.StartsWith(Prefix))
                    {
                        cellvalue = cellvalue.Substring(Prefix.Length);
                        SetCellValue(modelcell, cellvalue, dicdata);
                        hassetdata = true;
                    }
                    cellindex += 1;
                }
                if (islist)
                {
                    newrowno = SetList(sheetnew, row, minrow, listkey, listkeys, listbegnum, dicdata, ListSeqNoStr, autoheight);

                    minrow += newrowno == 0 ? 1 : newrowno;

                    maxrow += newrowno;



                }
                else
                {
                    if (hassetdata && autoheight)
                    {
                        SetRowHeight(row);
                    }
                    minrow += 1;

                }
            }







        }


        /// <summary>
        /// 解析字符串轉換成對象字符串例如Order.OrderNo,Order.Detail.ID，若是錯誤返回error
        /// </summary>
        /// <param name="key"></param>
        /// <param name="dicdata"></param>
        /// <returns></returns>
        public static object GetDataByKey(string key, object dicdata)
        {
            string[] keys = key.Split('.');
            object ret = dicdata;
            try
            {
                foreach (string str in keys)
                {

                    if (ret == null)
                        return null;

                    if (ret is IDictionary)
                    {
                        ret = (ret as IDictionary)[str];
                        continue;
                    }

                    if (ret is DataRow)
                    {
                        ret = (ret as DataRow)[str];
                        continue;
                    }
                    if (ret is DataRowView)
                    {
                        ret = (ret as DataRowView)[str];
                        continue;
                    }

                    PropertyInfo pi = ret.GetType().GetProperty(str, BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic);
                    if (pi == null)
                    {
                        throw new Exception(ret.GetType().FullName + "不包含屬性:" + str);
                    }
                    if (!pi.CanRead)
                    {
                        throw new Exception(ret.GetType().FullName + "屬性:" + str + "不能夠讀");
                    }
                    ret = pi.GetValue(ret);
                }
            }
            catch (Exception ex)
            {
                return "Error:" + ex.Message;
            }
            return ret;
        }

        /// <summary>
        /// 按模板設置單元格數據
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="key"></param>
        /// <param name="dicdata"></param>
        public static void SetCellValue(ICell cell, string key, object dicdata)
        {
            object value = GetDataByKey(key, dicdata);

            if (value != null && value != DBNull.Value)
            {
                switch (value.GetType().Name.ToLower())
                {
                    case "int":
                    case "int32":
                    case "int16":
                    case "short":
                    case "byte":
                    case "double":
                    case "float":
                    case "decimal":
                    case "long":
                        cell.SetCellValue(Convert.ToDouble(value));
                        break;
                    case "datetime":
                        cell.SetCellValue(Convert.ToDateTime(value));
                        break;
                    default:
                        cell.SetCellValue(value.ToString());
                        break;
                }
            }
            else
            {
                cell.SetCellValue("");
            }
        }


        public static int SetList(ISheet sheet, IRow row, int begrowindex, string listkey, List<string> keys, int listbegcell, object dicdata, string seqnostr, bool autoheight)
        {

            bool has = false;
            int total = 0;
            object listdata = GetDataByKey(listkey, dicdata);

            if (listdata == null || listdata == DBNull.Value)
                goto lblend;
            if (!(listdata is IEnumerable))
                goto lblend;

            IEnumerator aa = (listdata as IEnumerable).GetEnumerator();
            aa.Reset();

            ICell cellnew;

            int newrowindex = begrowindex;
            for (int i = 0; aa.MoveNext(); i++)
            {
                total += 1;
                IRow newrow;

                if (i == 0)
                {
                    newrow = row;
                }
                else
                {
                    newrow = sheet.CopyRow(begrowindex, newrowindex);
                    newrow.Height = row.Height;
                }
                newrowindex += 1;
                has = true;

                int cellindex = listbegcell;
                object dr = aa.Current;

                foreach (string str in keys)
                {

                    if (str == "")
                    {
                        cellindex += 1;
                        continue;
                    }

                    cellnew = newrow.GetCell(cellindex);

                    if (str == seqnostr)
                    {
                        cellnew.SetCellValue(i + 1);

                    }
                    else
                    {
                        SetCellValue(cellnew, str, dr);
                    }
                    cellindex += 1;
                }
                if (autoheight)
                    SetRowHeight(newrow);
            }

        lblend:
            if (!has)
            {
                int cellindex = listbegcell;
                foreach (string key in keys)
                {
                    if (key == "")
                    {
                        cellindex += 1;
                        continue;
                    }
                    cellnew = row.GetCell(cellindex);
                    cellnew.SetCellValue("");
                }
                row.Height = row.Sheet.DefaultRowHeight;
            }
            return total;
        }


        public static float GetCellWidth(ICell cell)
        {
            if (cell == null)
                return 0;
            ISheet sheet = cell.Sheet;
            int mcount = sheet.NumMergedRegions;

            CellRangeAddress mergerange = null;

            for (int i = 0; i < mcount; i++)
            {
                CellRangeAddress range = sheet.GetMergedRegion(i);

                if (range.FirstColumn <= cell.ColumnIndex
                    && range.LastColumn >= cell.ColumnIndex
                    && range.FirstRow <= cell.RowIndex
                    && range.LastRow >= cell.RowIndex)
                {

                    if (range.FirstColumn == cell.ColumnIndex && range.FirstRow == cell.RowIndex)
                    {
                        mergerange = range;
                    }
                    else
                    {
                        break;
                    }
                }
            }

            if (mergerange != null)
            {
                float width = 0;
                for (int i = mergerange.FirstColumn; i <= mergerange.LastColumn; i++)
                {
                    width += sheet.GetColumnWidthInPixels(i);
                }
                return width;
            }
            return sheet.GetColumnWidthInPixels(cell.ColumnIndex);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="begrow">從0開始</param>
        /// <param name="begcol">從0開始</param>
        /// <param name="endrow">從0開始，請比真實位置多一行</param>
        /// <param name="endcol">從0開始，請比真實位置多一列</param>
        /// <param name="filepath"></param>
        public static void AddImg(ISheet sheet, int begrow, int begcol, int endrow, int endcol, string filepath)
        {

            IWorkbook excel = sheet.Workbook;
            byte[] imgdata = File.ReadAllBytes(filepath);

            PictureType imgtype = PictureType.None;
            switch (Path.GetExtension(filepath).ToLower())
            { 
                case ".png":
                    imgtype = PictureType.PNG;
                    break;
                case ".jpg":
                    imgtype = PictureType.JPEG;
                    break;
                case ".gif":
                    imgtype = PictureType.GIF;
                    break;
            }

            int pictureIdx = excel.AddPicture(imgdata, imgtype);
            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            // 插圖片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 後面再做解釋
            IClientAnchor anchor = null;
            if (excel is HSSFWorkbook)

                anchor = new HSSFClientAnchor(0, 0, 0, 0, begcol, begrow, endcol, endrow);
            else
                anchor = new XSSFClientAnchor(0, 0, 0, 0, begcol, begrow, endcol, endrow);


            //把圖片插到相應的位置
            IPicture pict = (IPicture)patriarch.CreatePicture(anchor, pictureIdx);

        }


        public static void SetRowHeight(IRow row)
        {


            ICell cell = null;

            ISheet sheet = row.Sheet;
            IWorkbook book = sheet.Workbook;
            IFont font = null;

            float rowheight = row.HeightInPoints;

            int begnum = row.FirstCellNum;

            StringFormat sf = new StringFormat(StringFormat.GenericDefault);
            sf.Trimming = StringTrimming.Word;

            for (; begnum <= row.LastCellNum; begnum++)
            {
                cell = row.GetCell(begnum);
                if (cell == null)
                    continue;
                string value = cell.ToString();
                if (cell.CellStyle != null)
                    font = cell.CellStyle.GetFont(book);
                if (font == null || string.IsNullOrWhiteSpace(value))
                    continue;
                float width = GetCellWidth(cell);

                if (width >= 0 && width <= 0)
                    continue;


                Font newfont = new Font(font.FontName, (float)(font.FontHeightInPoints), GraphicsUnit.Point);

                SizeF maxsize = new SizeF(width, float.MaxValue);
                int c0, lc;
                //須要每次從新生成位圖進行測量
                using (var graphics = Graphics.FromHwnd(IntPtr.Zero))
                {
                    var size1 = graphics.MeasureString(value, newfont, maxsize, sf, out c0, out lc);
                    rowheight = Math.Max(rowheight, size1.Height);//(float)( lc * Math.Max(sheet.DefaultRowHeightInPoints, font.FontHeightInPoints))
                }
            }

            row.HeightInPoints = rowheight;// beishu* rowheight;

        }
    }
}