GetExcelNpoi.cs

using System;
using System.Collections.Generic;// List
using System.Data;// DataTable
using System.Data.OleDb;// OleDbConnection,OleDbDataAdapter
using System.IO;// FileStream
using System.Text.RegularExpressions;// Regex
using System.Web;// HttpResponse,HttpUtility
using System.Web.UI;// HtmlTextWriter
using System.Web.UI.WebControls;// GridView
using System.Drawing;// Color
using NPOI.HSSF.UserModel;// HSSFWorkbook,HSSFSheet,HSSFRow,HSSFCell
using NPOI.SS.UserModel;// IWorkbook,ISheet,IRow,ICell
using NPOI.SS.Util;// CellRangeAddress

/*/--------------------------------------------------------------------------------//
// GetExcelNpoi 的摘要说明
// Ver 2.3.1.0125 for NPOI 2.0

//--------------------------------------------------------------------------------/*/
public class GetExcelNpoi : System.Web.UI.Page
{
    // 构造函数
    public GetExcelNpoi() { }

    // GridView 导出 Excel
    // 注意: 使用此函数要定义事件 public override void VerifyRenderingInServerForm(Control control){}
    public void ExportExcel(HttpResponse response, GridView gridView, string fileName)
    {
        response.Clear();
        response.Buffer = false;
        response.Charset = "GB2312";
        response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        response.ContentType = "application/ms-excel";
        response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
        EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
        gridView.RenderControl(oHtmlTextWriter);
        response.Write(oStringWriter.ToString());
        response.End();
    }

    // 列名转索引
    public static int ColumnNameToIndex(string columnName)
    {
        int index = 0;
        if (!Regex.IsMatch(columnName, @"^[A-Za-z]+$"))
        {
            return -1;
        }
        char[] chars = columnName.ToUpper().ToCharArray();
        for (int i = 0; i < chars.Length; i++)
        {
            index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
        }
        return index - 1;
    }

    // 列索引转名
    public static string ColumnIndexToName(int columnIndex)
    {
        if (columnIndex < 0) return "";
        List<string> chars = new List<string>();
        do
        {
            if (chars.Count > 0) columnIndex--;
            chars.Insert(0, ((char)(columnIndex % 26 + (int)'A')).ToString());
            columnIndex = (int)((columnIndex - columnIndex % 26) / 26);
        }
        while (columnIndex > 0);
        return String.Join(string.Empty, chars.ToArray());
    }

    // 颜色索引转颜色对象
    public Color ColorIndexToObject(short index)
    {
        Color result = Color.Empty;
        switch (index)
        {
            case 49:
                result = Color.Aqua;
                break;
            case 8:
                result = Color.Black;
                break;
            case 12:
                result = Color.Blue;
                break;
            case 60:
                result = Color.Brown;
                break;
            case 29:
                result = Color.Coral;
                break;
            case 24:
                result = Color.CornflowerBlue;
                break;
            case 18:
                result = Color.DarkBlue;
                break;
            case 58:
                result = Color.DarkGreen;
                break;
            case 16:
                result = Color.DarkRed;
                break;
            case 51:
                result = Color.Gold;
                break;
            case 17:
                result = Color.Green;
                break;
            case 62:
                result = Color.Indigo;
                break;
            case 46:
                result = Color.Lavender;
                break;
            case 26:
                result = Color.LemonChiffon;
                break;
            case 48:
                result = Color.LightBlue;
                break;
            case 42:
                result = Color.LightGreen;
                break;
            case 43:
                result = Color.LightYellow;
                break;
            case 50:
                result = Color.Lime;
                break;
            case 25:
                result = Color.Maroon;
                break;
            case 53:
                result = Color.Orange;
                break;
            case 28:
                result = Color.Orchid;
                break;
            case 14:
                result = Color.Pink;
                break;
            case 61:
                result = Color.Plum;
                break;
            case 10:
                result = Color.Red;
                break;
            case 30:
                result = Color.RoyalBlue;
                break;
            case 57:
                result = Color.SeaGreen;
                break;
            case 40:
                result = Color.SkyBlue;
                break;
            case 47:
                result = Color.Tan;
                break;
            case 21:
                result = Color.Teal;
                break;
            case 15:
                result = Color.Turquoise;
                break;
            case 20:
                result = Color.Violet;
                break;
            case 13:
                result = Color.Yellow;
                break;
            case 9:
            case 64:// 自动色
                result = Color.White;
                break;
        }
        return result;
    }

    // 从Excel导出到DataSet
    public DataSet GetDataSet(string filePath, string sheetName)
    {
        DataSet ds = new DataSet();
        string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
        try
        {
            OleDbConnection objConn = new OleDbConnection(strConnect);
            OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
            objConn.Open();
            objDA.Fill(ds, sheetName + "$");
            objConn.Close();
        }
        catch
        {
        }
        return ds;
    }

    // 从Excel导出到DataSet
    public DataSet GetDataSet(string filePath, int sheetIndex)
    {
        DataSet ds = new DataSet();
        string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
        try
        {
            FileStream fs = File.Open(filePath, FileMode.Open);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            fs.Close();
            string sheetName = wb.GetSheetName(sheetIndex);
            OleDbConnection objConn = new OleDbConnection(strConnect);
            OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
            objConn.Open();
            objDA.Fill(ds, sheetName + "$");
            objConn.Close();
        }
        catch
        {
        }
        return ds;
    }

    // 从Excel导出到DataTable重载
    public System.Data.DataTable GetDataTable(string filePath, string sheetName)
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
        try
        {
            OleDbConnection objConn = new OleDbConnection(strConnect);
            OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
            objConn.Open();
            objDA.Fill(dt);
            objConn.Close();
        }
        catch
        {
        }
        return dt;
    }

    // 从Excel导出到DataTable重载
    public System.Data.DataTable GetDataTable(string filePath, int sheetIndex)
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        string strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
        try
        {
            FileStream fs = File.Open(filePath, FileMode.Open);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            fs.Close();
            string sheetName = wb.GetSheetName(sheetIndex);
            OleDbConnection objConn = new OleDbConnection(strConnect);
            OleDbDataAdapter objDA = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConnect);
            objConn.Open();
            objDA.Fill(dt);
            objConn.Close();
        }
        catch
        {
        }
        return dt;
    }

    // 创建工作簿
    public HSSFWorkbook Create()
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        return wb;
    }

    // 打开工作簿
    public HSSFWorkbook Open(string filePath)
    {
        FileStream fs = File.Open(filePath, FileMode.Open);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        fs.Close();
        return wb;
    }

    // 保存工作簿
    public void Save(HSSFWorkbook workbook, string filePath)
    {
        FileStream fs = File.Open(filePath, FileMode.OpenOrCreate);
        workbook.Write(fs);
        fs.Close();
    }

    // 创建表单
    public ISheet CreateSheet(HSSFWorkbook workbook, string sheetName)
    {
        return workbook.CreateSheet(sheetName);
    }

    // 打开表单重载
    public ISheet OpenSheet(HSSFWorkbook workbook, string sheetName)
    {
        int index = workbook.GetSheetIndex(sheetName);
        return index >= 0 ? workbook.GetSheetAt(index) : null;
    }

    // 打开表单重载
    public ISheet OpenSheet(HSSFWorkbook workbook, int sheetIndex)
    {
        int index = sheetIndex;
        return index >= 0 ? workbook.GetSheetAt(index) : null;
    }

    // 获取表单对象重载
    public ISheet GetSheet(string filePath, string sheetName)
    {
        FileStream fs = File.Open(filePath, FileMode.Open);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        fs.Close();
        int index = wb.GetSheetIndex(sheetName);
        return wb.GetSheetAt(index);
    }

    // 获取表单对象重载
    public ISheet GetSheet(string filePath, int sheetIndex)
    {
        FileStream fs = File.Open(filePath, FileMode.Open);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        fs.Close();
        int index = sheetIndex;
        return wb.GetSheetAt(index);
    }

    // 获取指定索引表单名
    public string GetSheetName(HSSFWorkbook workbook, int sheetIndex)
    {
        return workbook.GetSheetName(sheetIndex);
    }

    // 获取指定表单名索引
    public int GetSheetIndex(HSSFWorkbook workbook, string sheetName)
    {
        return workbook.GetSheetIndex(sheetName);
    }

    // 统计表单数
    public int CountSheet(HSSFWorkbook workbook)
    {
        return workbook.NumberOfSheets;
    }

    // 统计行数
    public int CountRow(ISheet sheet)
    {
        int result = sheet.LastRowNum;
        return result + 1;
    }

    // 统计列数
    public int CountColumn(ISheet sheet)
    {
        int result = 0;
        int rowCount = sheet.LastRowNum;
        for (int i = 0; i <= rowCount; i++)
        {
            try
            {
                int colCount = sheet.GetRow(i).LastCellNum;
                result = colCount > result ? colCount : result;
            }
            catch
            {
            }
        }
        return result;
    }

    // 获取单元格数据类型
    public string GetCellDataType(ISheet sheet, int rowNum, int colNum)
    {
        string result = "";
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            result = cell.CellType.ToString();
        }
        catch
        {
        }
        return result;
    }

    // 读取单元格重载
    public string ReadCell(ISheet sheet, int rowNum, int colNum)
    {
        string result = "";
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            result = cell.ToString();
        }
        catch
        {
        }
        return result;
    }

    // 读取单元格重载
    public string ReadCell(ISheet sheet, int rowNum, int colNum, string dataFormat)
    {
        string result = "";
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            short data = cell.CellStyle.DataFormat;
            cell.CellStyle.DataFormat = sheet.Workbook.CreateDataFormat().GetFormat(dataFormat);
            result = cell.ToString();
            cell.CellStyle.DataFormat = data;
        }
        catch
        {
        }
        return result;
    }

    // 写入单元格重载
    public bool WriteCell(ISheet sheet, int rowNum, int colNum, object value)
    {
        bool result = false;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(rowIndex).CreateCell(i);
            }
        }
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            result = true;
            switch (value.GetType().ToString())
            {
                case "System.Boolean":
                    cell.SetCellValue((bool)value);
                    break;
                case "System.DateTime":
                    cell.SetCellValue((DateTime)value);
                    break;
                case "System.Double":
                    cell.SetCellValue((double)value);
                    break;
                case "System.String":
                    cell.SetCellValue((string)value);
                    break;
                case "System.Int16":
                    cell.SetCellValue((short)value);
                    break;
                case "System.Int32":
                    cell.SetCellValue((int)value);
                    break;
                case "NPOI.HSSF.UserModel.HSSFRichTextString":
                    cell.SetCellValue((IRichTextString)value);
                    break;
                default:
                    result = false;
                    break;
            }
        }
        catch
        {
        }
        return result;
    }

    // 写入单元格重载
    public bool WriteCell(ISheet sheet, int rowNum, int colNum, object value, string dataFormat)
    {
        bool result = false;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(rowIndex).CreateCell(i);
            }
        }
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            result = true;
            switch (value.GetType().ToString())
            {
                case "System.Boolean":
                    cell.SetCellValue((bool)value);
                    break;
                case "System.DateTime":
                    cell.SetCellValue((DateTime)value);
                    break;
                case "System.Double":
                    cell.SetCellValue((double)value);
                    break;
                case "System.String":
                    cell.SetCellValue((string)value);
                    break;
                case "System.Int16":
                    cell.SetCellValue((short)value);
                    break;
                case "System.Int32":
                    cell.SetCellValue((int)value);
                    break;
                case "NPOI.HSSF.UserModel.HSSFRichTextString":
                    cell.SetCellValue((IRichTextString)value);
                    break;
                default:
                    result = false;
                    break;
            }
            cell.CellStyle.DataFormat = sheet.Workbook.CreateDataFormat().GetFormat(dataFormat);
            cell.SetCellValue(cell.ToString());
        }
        catch
        {
        }
        return result;
    }

    // 读取公式
    public string ReadFormula(ISheet sheet, int rowNum, int colNum)
    {
        string result = "";
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(rowIndex).CreateCell(i);
            }
        }
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            result = cell.NumericCellValue.ToString();
        }
        catch
        {
        }
        return result;
    }

    // 写入公式
    public bool WriteFormula(ISheet sheet, int rowNum, int colNum, string formulaText, ICellStyle cellStyle)
    {
        bool result = false;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(rowIndex).CreateCell(i);
            }
        }
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        ((HSSFSheet)sheet).SetBorderTopOfRegion(range, cellStyle.BorderTop, cellStyle.TopBorderColor);
                        ((HSSFSheet)sheet).SetBorderBottomOfRegion(range, cellStyle.BorderBottom, cellStyle.BottomBorderColor);
                        ((HSSFSheet)sheet).SetBorderLeftOfRegion(range, cellStyle.BorderLeft, cellStyle.LeftBorderColor);
                        ((HSSFSheet)sheet).SetBorderRightOfRegion(range, cellStyle.BorderRight, cellStyle.RightBorderColor);
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            cell.SetCellFormula(formulaText);
            sheet.ForceFormulaRecalculation = true;
            cell.CellStyle = cellStyle;
            result = true;
        }
        catch
        {
        }
        return result;
    }

    // 写入富文本
    // 在文本中上标符号为 ^ 下标符号为 _ 结束符号为 ! ,任何上下标必须以结束符作为结尾,如"x^3!+y_1!^2!=z_3!"
    public bool WriteRichText(ISheet sheet, int rowNum, int colNum, string richFormat)
    {
        bool result = false;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(rowIndex).CreateCell(i);
            }
        }
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            // 创建上标字体
            HSSFFont superFont = (HSSFFont)sheet.Workbook.CreateFont();
            superFont.TypeOffset = NPOI.SS.UserModel.FontSuperScript.Super;
            // 创建下标字体
            HSSFFont subFont = (HSSFFont)sheet.Workbook.CreateFont();
            subFont.TypeOffset = NPOI.SS.UserModel.FontSuperScript.Sub;
            // 处理文本格式
            string trimText = richFormat;
            trimText = trimText.Replace("^", "");// 上标符
            trimText = trimText.Replace("_", "");// 下标符
            trimText = trimText.Replace("!", "");// 结束符
            HSSFRichTextString richText = new HSSFRichTextString(trimText);
            int offset = 0;
            while (richFormat.IndexOf('!') != -1)
            {
                int superIndex = richFormat.IndexOf('^');
                int subIndex = richFormat.IndexOf('_');
                int endFlag = richFormat.IndexOf('!');
                if (superIndex != -1 && superIndex < endFlag)
                {// 处理上标
                    int start = superIndex + offset;
                    offset += endFlag - 1;
                    richText.ApplyFont(start, offset, superFont);
                }
                else if (subIndex != -1 && subIndex < endFlag)
                {// 处理下标
                    int start = subIndex + offset;
                    offset += endFlag - 1;
                    richText.ApplyFont(start, offset, subFont);
                }
                richFormat = richFormat.Substring(endFlag + 1);
            }
            cell.SetCellValue(richText);
            result = true;
        }
        catch
        {
        }
        return result;
    }

    // 判断合并单元格
    public bool IsMergeCell(ISheet sheet, int rowNum, int colNum)
    {
        bool result = false;
        if ((rowNum < 1) || (colNum < 1)) return result;
        try
        {
            int rowIndex = rowNum - 1;
            int colIndex = colNum - 1;
            result = sheet.GetRow(rowIndex).GetCell(colIndex).IsMergedCell;
        }
        catch
        {
        }
        return result;
    }

    // 获取单元格信息
    // 调用时要在输出变量前加 out
    public void GetCellInfo(ISheet sheet, int rowNum, int colNum, out int rowSpan, out int colSpan, out bool isMerged, out bool isHeader)
    {
        rowSpan = 0;
        colSpan = 0;
        isMerged = false;
        isHeader = false;
        if ((rowNum < 1) || (colNum < 1)) return;
        try
        {
            int rowIndex = rowNum - 1;
            int colIndex = colNum - 1;
            rowSpan = 1;
            colSpan = 1;
            isMerged = sheet.GetRow(rowIndex).GetCell(colIndex).IsMergedCell;
            if (isMerged == false)
            {
                isHeader = true;
            }
            else
            {
                for (int i = 0; i < sheet.NumMergedRegions; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    if (range.FirstRow == rowIndex && range.FirstColumn == colIndex)
                    {
                        rowSpan = range.LastRow - range.FirstRow + 1;
                        colSpan = range.LastColumn - range.FirstColumn + 1;
                        isHeader = true;
                        break;
                    }
                }
            }
        }
        catch
        {
        }
    }

    // 设置合并单元格
    // rowSpan 行跨度,colSpan 列跨度,最小值为1
    public bool SetMergeCell(ISheet sheet, int rowNum, int colNum, int rowSpan, int colSpan)
    {
        bool result = false;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int rowSpanIndex = (rowSpan < 1 ? 1 : rowSpan) - 1;
        int colSpanIndex = (colSpan < 1 ? 1 : colSpan) - 1;
        try
        {
            if (0 <= sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + rowSpanIndex, colIndex, colIndex + colSpanIndex)))
            {
                result = true;
            }
        }
        catch
        {
        }
        return result;
    }

    // 撤销合并单元格
    public bool UndoMergeCell(ISheet sheet, int rowNum, int colNum)
    {
        bool result = false;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        ((HSSFSheet)sheet).SetBorderTopOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
                        ((HSSFSheet)sheet).SetBorderBottomOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
                        ((HSSFSheet)sheet).SetBorderLeftOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
                        ((HSSFSheet)sheet).SetBorderRightOfRegion(range, NPOI.SS.UserModel.BorderStyle.None, NPOI.HSSF.Util.HSSFColor.Automatic.Index);
                        sheet.RemoveMergedRegion(i);
                        cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
                        cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
                        cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
                        cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
                        cell.CellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
                        cell.CellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
                        cell.CellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
                        cell.CellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
                        cell.CellStyle.FillPattern = FillPattern.NoFill;
                        cell.CellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
                        cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;
                        result = true;
                        break;
                    }
                }
            }
        }
        catch
        {
        }
        return result;
    }

    // 设置行高
    public void SetRowHeight(ISheet sheet, int rowNum, int value)
    {
        if (rowNum < 1) return;
        int rowIndex = rowNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        sheet.GetRow(rowIndex).Height = (short)(value * 20);
    }

    // 设置列宽
    // value = 0 时自动列宽
    public void SetColumnWidth(ISheet sheet, int colNum, int value)
    {
        if (colNum < 1) return;
        int colIndex = colNum - 1;
        if (1 > sheet.PhysicalNumberOfRows)
        {
            sheet.CreateRow(0);
        }
        int physicalColNum = sheet.GetRow(0).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(0).CreateCell(i);
            }
        }
        if (value == 0)
        {
            sheet.AutoSizeColumn(colNum);
        }
        else
        {
            sheet.SetColumnWidth(colIndex, (int)((value + 0.72) * 256));
        }
    }

    // 读取行高
    public int GetRowHeight(ISheet sheet, int rowNum)
    {
        int result = -1;
        if (rowNum < 1) return result;
        int rowIndex = rowNum - 1;
        result = sheet.GetRow(rowIndex).Height;
        return (int)result / 20;
    }

    // 读取列宽
    public int GetColumnWidth(ISheet sheet, int colNum)
    {
        int result = -1;
        if (colNum < 1) return result;
        int colIndex = colNum - 1;
        result = sheet.GetColumnWidth(colIndex);
        return (int)(result - 0.72) / 256;
    }

    // 获取单元格样式
    public ICellStyle GetCellStyle(ISheet sheet, int rowNum, int colNum)
    {
        ICellStyle result = null;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(rowIndex).CreateCell(i);
            }
        }
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            result = cell.CellStyle;
        }
        catch
        {
        }
        return result;
    }

    // 设置单元格样式
    public ICellStyle SetCellStyle(ISheet sheet, int rowNum, int colNum, ICellStyle cellStyle)
    {
        ICellStyle result = null;
        if ((rowNum < 1) || (colNum < 1)) return result;
        int rowIndex = rowNum - 1;
        int colIndex = colNum - 1;
        int physicalRowNum = sheet.PhysicalNumberOfRows;
        if (rowNum > physicalRowNum)
        {
            for (int i = physicalRowNum; i <= rowIndex; i++)
            {
                sheet.CreateRow(i);
            }
        }
        int physicalColNum = sheet.GetRow(rowIndex).PhysicalNumberOfCells;
        if (colNum > physicalColNum)
        {
            for (int i = physicalColNum; i <= colIndex; i++)
            {
                sheet.GetRow(rowIndex).CreateCell(i);
            }
        }
        try
        {
            ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
            if (cell.IsMergedCell)
            {
                int regionsCount = sheet.NumMergedRegions;
                for (int i = 0; i < regionsCount; i++)
                {
                    CellRangeAddress range = sheet.GetMergedRegion(i);
                    sheet.IsMergedRegion(range);
                    if (range.FirstRow <= rowIndex && rowIndex <= range.LastRow && range.FirstColumn <= colIndex && colIndex <= range.LastColumn)
                    {
                        ((HSSFSheet)sheet).SetBorderTopOfRegion(range, cellStyle.BorderTop, cellStyle.TopBorderColor);
                        ((HSSFSheet)sheet).SetBorderBottomOfRegion(range, cellStyle.BorderBottom, cellStyle.BottomBorderColor);
                        ((HSSFSheet)sheet).SetBorderLeftOfRegion(range, cellStyle.BorderLeft, cellStyle.LeftBorderColor);
                        ((HSSFSheet)sheet).SetBorderRightOfRegion(range, cellStyle.BorderRight, cellStyle.RightBorderColor);
                        cell = sheet.GetRow(range.FirstRow).GetCell(range.FirstColumn);
                        break;
                    }
                }
            }
            cell.CellStyle = cellStyle;
            result = cell.CellStyle;
        }
        catch
        {
        }
        return result;
    }

    // 新建样式重载
    public ICellStyle NewStyle(HSSFWorkbook workbook)
    {
        ICellStyle style = workbook.CreateCellStyle();
        return style;
    }

    // 新建样式重载
    // 日期: 			yyyy/MM/dd
    // 数字: 			0.00
    // 百分比: 			0.00%
    // 科学计数法( + ):	0.00E + 00
    // 科学计数法(-):	0.00E-00
    // 中文数字大写		[DbNum2][$-804]0
    public ICellStyle NewStyle(HSSFWorkbook workbook, string dataFormat)
    {
        ICellStyle style = workbook.CreateCellStyle();
        IDataFormat format = workbook.CreateDataFormat();
        style.DataFormat = format.GetFormat(dataFormat);
        return style;
    }

    // 新建字体
    // fontFamily = {"宋体"|"黑体"|"楷体"|"Tahoma"|"Times New Roman"|...}
    // fontColor = NPOI.HSSF.Util.HSSFColor.{Automatic|Black|Blue|DarkBlue|...}.Index
    // underlineType = FontUnderlineType.{None|Single|Double|SingleAccounting|DoubleAccounting}
    public IFont NewFont(HSSFWorkbook workbook, string fontFamily, short fontColor, int fontSize, bool isBold, bool isItalic, FontUnderlineType underlineType)
    {
        IFont font = workbook.CreateFont();
        font.FontName = fontFamily;// 字体名称
        font.Color = fontColor;// 字体颜色
        font.FontHeightInPoints = (short)fontSize;// 字体大小
        if (isBold) font.Boldweight = (short)FontBoldWeight.Bold;//加粗
        font.IsItalic = isItalic;// 斜体
        font.Underline = underlineType;// 下划线
        return font;
    }

    // 设置字体样式
    public ICellStyle SetFont(ICellStyle cellStyle, IFont cellFont)
    {
        cellStyle.SetFont(cellFont);
        return cellStyle;
    }

    // 设置数据格式
    public ICellStyle SetFormat(ICellStyle cellStyle, HSSFWorkbook workbook, string dataFormat)
    {
        IDataFormat format = workbook.CreateDataFormat();
        cellStyle.DataFormat = format.GetFormat(dataFormat);
        return cellStyle;
    }

    // 设置对齐样式
    // horizPos = 0常规,1靠左,2居中,3靠右,4填充,5两端,6跨列,7分散
    // vertiPos = 0靠上,1居中,2靠下,3两端,4分散
    public ICellStyle SetAlign(ICellStyle cellStyle, int horizPos, int vertiPos, bool isWrap)
    {
        horizPos = horizPos < 0 ? 0 : horizPos;
        vertiPos = vertiPos < 0 ? 0 : vertiPos;
        ((HSSFCellStyle)cellStyle).Alignment = (HorizontalAlignment)(horizPos % 8);// 水平对齐
        ((HSSFCellStyle)cellStyle).VerticalAlignment = (VerticalAlignment)(vertiPos % 5);// 垂直对齐
        ((HSSFCellStyle)cellStyle).WrapText = isWrap;
        return cellStyle;
    }

    // 设置边框颜色
    // topColor|bottomColor|leftColor|rightColor = NPOI.HSSF.Util.HSSFColor.{Automatic|White|Black|Blue|Green|...}.Index
    public ICellStyle SetBorderColor(ICellStyle cellStyle, short topColor, short bottomColor, short leftColor, short rightColor)
    {
        cellStyle.TopBorderColor = topColor;
        cellStyle.BottomBorderColor = bottomColor;
        cellStyle.LeftBorderColor = leftColor;
        cellStyle.RightBorderColor = rightColor;
        return cellStyle;
    }

    // 设置边框样式
    // topBorder|bottomBorder|leftBorder|rightBorder = BorderStyle.{None|Thin|Medium|Dashed|Dotted|Thick|Double|Hair|MediumDashed|DashDot|MediumDashDot|DashDotDot|MediumDashDotDot|SlantedDashDot}
    public ICellStyle SetBorderStyle(ICellStyle cellStyle, NPOI.SS.UserModel.BorderStyle topBorder, NPOI.SS.UserModel.BorderStyle bottomBorder, NPOI.SS.UserModel.BorderStyle leftBorder, NPOI.SS.UserModel.BorderStyle rightBorder)
    {
        cellStyle.BorderTop = topBorder;
        cellStyle.BorderBottom = bottomBorder;
        cellStyle.BorderLeft = leftBorder;
        cellStyle.BorderRight = rightBorder;
        return cellStyle;
    }

    // 设置填充样式
    // pattern = FillPattern.{NoFill|SolidForeground|FineDots|AltBars|SparseDots|ThickHorizontalBands|ThickVerticalBands|ThickBackwardDiagonals|ThickForwardDiagonals|BigSpots|Bricks|...}
    public ICellStyle SetFillStyle(ICellStyle cellStyle, FillPattern pattern, short foregroundColor, short backgroundColor)
    {
        cellStyle.FillPattern = pattern;// 图案样式
        cellStyle.FillForegroundColor = foregroundColor;// 图案颜色
        cellStyle.FillBackgroundColor = backgroundColor;// 背景颜色
        return cellStyle;
    }

    // 绘制表单
    public string DrawSheet(ISheet sheet)
    {
        int maxColNum = 0;
        string html;
        html = "<table border=\"1\" style=\"text-align:center;vertical-align:middle;border:2px solid darkgray;border-collapse:collapse;width:100%;\">";
        for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
        {// 表单行
            if (sheet.GetRow(rowIndex) == null)
            {// 处理空行并跳过当前循环
                if (maxColNum == 0)
                {
                    html += "<tr><td><br/></td></tr>";
                }
                else
                {
                    html += "<tr><td colspan=" + maxColNum + "><br/></td></tr>";
                }
                continue;
            }
            else
            {// 保存最后非空行单元列数
                maxColNum = sheet.GetRow(rowIndex).LastCellNum;
            }
            // 绘制非空行数据
            html += "<tr height=" + sheet.GetRow(rowIndex).Height / 20 + ">";
            for (int colIndex = 0; colIndex < sheet.GetRow(rowIndex).LastCellNum; colIndex++)
            {// 表单列
                ICell cell = sheet.GetRow(rowIndex).GetCell(colIndex);
                if (cell == null)
                {// 处理空单元格并跳过当前循环
                    html += "<td />";
                    continue;
                }
                int rowSpan = 1;
                int colSpan = 1;
                bool isHeader = false;
                if (cell.IsMergedCell == false)
                {
                    isHeader = true;
                }
                else
                {
                    for (int i = 0; i < sheet.NumMergedRegions; i++)
                    {
                        CellRangeAddress range = sheet.GetMergedRegion(i);
                        if (range.FirstRow == rowIndex && range.FirstColumn == colIndex)
                        {
                            rowSpan = range.LastRow - range.FirstRow + 1;
                            colSpan = range.LastColumn - range.FirstColumn + 1;
                            isHeader = true;
                            break;
                        }
                    }
                }
                if (isHeader)
                {// 仅头表单绘制内容
                    string colorName = "White";
                    switch (cell.CellStyle.FillForegroundColor)
                    {// 转换颜色索引为颜色名称
                        case 49:
                            colorName = "Aqua";
                            break;
                        case 8:
                            colorName = "Black";
                            break;
                        case 12:
                            colorName = "Blue";
                            break;
                        case 60:
                            colorName = "Brown";
                            break;
                        case 29:
                            colorName = "Coral";
                            break;
                        case 24:
                            colorName = "CornflowerBlue";
                            break;
                        case 18:
                            colorName = "DarkBlue";
                            break;
                        case 58:
                            colorName = "DarkGreen";
                            break;
                        case 16:
                            colorName = "DarkRed";
                            break;
                        case 51:
                            colorName = "Gold";
                            break;
                        case 17:
                            colorName = "Green";
                            break;
                        case 62:
                            colorName = "Indigo";
                            break;
                        case 46:
                            colorName = "Lavender";
                            break;
                        case 26:
                            colorName = "LemonChiffon";
                            break;
                        case 48:
                            colorName = "LightBlue";
                            break;
                        case 42:
                            colorName = "LightGreen";
                            break;
                        case 43:
                            colorName = "LightYellow";
                            break;
                        case 50:
                            colorName = "Lime";
                            break;
                        case 25:
                            colorName = "Maroon";
                            break;
                        case 53:
                            colorName = "Orange";
                            break;
                        case 28:
                            colorName = "Orchid";
                            break;
                        case 14:
                            colorName = "Pink";
                            break;
                        case 61:
                            colorName = "Plum";
                            break;
                        case 10:
                            colorName = "Red";
                            break;
                        case 30:
                            colorName = "RoyalBlue";
                            break;
                        case 57:
                            colorName = "SeaGreen";
                            break;
                        case 40:
                            colorName = "SkyBlue";
                            break;
                        case 47:
                            colorName = "Tan";
                            break;
                        case 21:
                            colorName = "Teal";
                            break;
                        case 15:
                            colorName = "Turquoise";
                            break;
                        case 20:
                            colorName = "Violet";
                            break;
                        case 9:
                            colorName = "White";
                            break;
                        case 13:
                            colorName = "Yellow";
                            break;
                    }
                    // white-space:nowrap;	自适应宽度
                    // word-break:keep-all;	避免长单词截断
                    html += "<td rowspan=" + rowSpan + " colspan=" + colSpan + " width=" + (sheet.GetColumnWidth(colIndex) - 0.72) / 256 + " bgcolor=" + colorName + " style=\"white-space:nowrap;word-break:keep-all;\">";
                    switch (cell.CellType.ToString())
                    {// 写入单元格内容
                        case "Formula":
                            html += cell.NumericCellValue.ToString("#0.0000");
                            break;
                        case "Numeric":
                            html += cell.ToString();
                            break;
                        default:
                            html += cell.ToString();
                            break;
                    }
                    html += "</td>";
                }
            }
            html += "</tr>";
        }
        html += "</table>";
        return html;
    }

}