ExcelHelper

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.Diagnostics;
using System.IO;

namespace Intesim.Common
{
    /// <summary>
    /// Excel 工具类
    /// </summary>
    public class ExcelUtil
    {
        public const int MAX_ROW0 = 100;   // 起始最大行 
        public const int MAX_COL0 = 100;   // 起始最大列
        public const int MAX_ROWS = 2000;  // 数据最大行 
        public const int MAX_COLS = 255;   // 数据最大列
        public const int MIN_ROWS = 2;     // 数据最少行数
        public const int MIN_COLS = 2;     // 数据最少列数
        public const int MAX_SHEET = 255;  // 最大sheet数

        /// <summary>
        /// 获取EXCEL单元格数据块
        /// </summary>
        /// <param name="fileName">EXCEL文件名</param>
        /// <param name="sheetName">EXCEL sheet名</param>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        /// <returns>单元格数据数组</returns>
        public static object[,] ReadExcelBlock(string fileName, string sheetName, int startRow, int startCol, int endRow, int endCol)
        {
            if(!File.Exists(fileName))
            {
                return null;
            }

            FileStream fs = null;

            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);

                ISheet sheet = null;

                if (Path.GetExtension(fileName) == ".xlsx")
                {
                    XSSFWorkbook workbook = new XSSFWorkbook(fs);

                    if (string.IsNullOrEmpty(sheetName))
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                }
                if (Path.GetExtension(fileName) == ".xls")
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);

                    if (string.IsNullOrEmpty(sheetName))
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                }

                if (sheet == null)
                {
                    return null;
                }

                int rows = endRow - startRow;
                int cols = endCol - startCol;

                if (rows < 1 || cols < 1)
                {
                    return null;
                }

                Object[,] xldata = new Object[rows, cols];

                for (int i = startRow; i < endRow; i++)
                {
                    for (int j = startCol; j < endCol; j++)
                    {
                        xldata[i - startRow, j - startCol] = sheet.GetRow(i).GetCell(j);
                    }
                }

                return xldata;
            }
            catch (Exception e)
            {
                Debug.WriteLine(string.Format("ReadExcelBlock错误，消息:{0}", e.Message));
                return null;
            }
            finally
            {
                fs.Close();
            }
        }

        /// <summary>
        /// 获取EXCEL单元格数据块
        /// </summary>
        /// <param name="fileName">EXCEL文件名</param>
        /// <param name="sheetName">EXCEL sheet名</param>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        /// <returns>单元格数据数组</returns>
        public static ICell[,] ReadExcelBlockXML(string fileName, string sheetName, int startRow, int startCol, int endRow, int endCol)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
            {
                XSSFWorkbook workbook = new XSSFWorkbook(fs);
                ISheet sheet = null;
                if (string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheetAt(0);
                }
                else
                {
                    sheet = workbook.GetSheet(sheetName);
                }

                if (sheet == null)
                {
                    return null;
                }

                int rows = endRow - startRow + 1;
                int cols = endCol - startCol + 1;
                if (rows < 1 || cols < 1)
                {
                    return null;
                }

                ICell[,] xldata = new XSSFCell[rows, cols];

                try
                {
                    for (int i = 0; i < rows; i++)
                    {
                        for (int j = 0; j < cols; j++)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row != null)
                            {
                                xldata[i, j] = row.GetCell(j);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine(string.Format("获取EXCEL单元格数据块出错，消息={0}，堆栈={1}", ex.Message, ex.StackTrace));
                }

                fs.Close();
                return xldata;
            }
        }

        /// <summary>
        /// NPOI读取Excel并返回读取的行数和列数
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <param name="rows"></param>
        /// <param name="cols"></param>
        /// <returns>返回的数组，索引从（1，1）开始</returns>
        public static object[,] ReadExcelBlockNPOI(string fileName, string sheetName, out int rows, out int cols)
        {
            rows = 0;
            cols = 0;

            if (!File.Exists(fileName))
            {
                return null;
            }

            FileStream fs = null;

            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);//获取其路径的文件内容

                ISheet sheet = null;

                if (Path.GetExtension(fileName) == ".xlsx")
                {
                    XSSFWorkbook workbook = new XSSFWorkbook(fs);

                    if (string.IsNullOrEmpty(sheetName))
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                }
                else if (Path.GetExtension(fileName) == ".xls")
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);

                    if (string.IsNullOrEmpty(sheetName))
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                }

                if (sheet == null)
                {
                    return null;
                }

                rows = sheet.LastRowNum + 1;//总行数

                object[,] xldata = new object[rows, MAX_COLS];

                for (int i = 0; i < rows; i++)
                {
                    // 遇空行结束
                    IRow row = sheet.GetRow(i);

                    if (!row.HasValue())
                    {
                        continue;
                    }
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        xldata[i, j] = sheet.GetRow(i).GetCell(j);

                        if (!xldata[i, j].HasValue())
                        {
                            break;
                        }

                        if (j + 1 > cols)
                        {
                            cols = j + 1;
                        }
                    }
                }

                return xldata;
            }
            catch (Exception e)
            {
                Debug.WriteLine(string.Format("ReadExcelBlockNPOI错误，消息：{0}", e.Message));

                return null;
            }
            finally
            {
                fs.Close();
            }
        }

        /// <summary>
        /// NPOI方式创建并写EXCEL文件
        /// </summary>
        /// <param name="xlFile">EXCEL文件</param>
        /// <param name="sheetName">EXCEL表名称</param>
        /// <param name="str">输出字符串数组</param>
        /// <param name="row0">输出起始行</param>
        /// <param name="col0">输出起始列</param>
        /// <param name="nRow">行数</param>
        /// <param name="nCol">列数</param>
        /// <returns>true=输出EXCEL文件成功, false=输出EXCEL文件失败</returns>
        public static bool WriteNewExcel(string xlFile, string sheetName, string[,] str, int row0, int col0, int nRow, int nCol)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);

            for (int i = 0; i < nRow - 1; i++)
            {
                IRow row = sheet.CreateRow(row0 + i);
                for (int j = 0; j < nCol - 1; j++)
                {
                    row.CreateCell(col0 + j).SetCellValue(str[i, j]);
                }

            }

            using (FileStream fs = new FileStream(xlFile, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                workbook.Write(fs);
            }

            return true;
        }

        #region 辅助函数

        /// <summary>
        /// object二维数组转换为DataTable
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="maxColsNum">最大列数</param>
        /// <param name="isAddNumCol">是否增加序号列，从1开始</param>
        /// <param name="isContainHeader">是否包含列头</param>
        /// <returns></returns>
        public static DataTable objectTwoDimensionToDataTable(object[,] obj , int maxColsNum , bool isAddNumCol = false, bool isContainHeader = false)
        {
            if(!obj.HasValue())
            {
                return null;
            }

            try
            {
                DataTable dt = new DataTable();

                for (int i = 0; i < maxColsNum; i++)
                {
                    DataColumn col = new DataColumn(i.ToString(), typeof(object)); //列名默认为0,1,2.....
                    dt.Columns.Add(col);
                }

                for (int i = 0; i < obj.GetLength(0); i++)
                {
                    DataRow row = dt.NewRow();

                    for (int j = 0; j < maxColsNum; j++)
                    {
                        row[j] = obj[i, j];
                    }

                    dt.Rows.Add(row);
                }

                if (isContainHeader)
                {
                    dt.Rows.RemoveAt(0);
                }

                if (isAddNumCol)
                {
                    dt.Columns.Add("num", typeof(int)).SetOrdinal(0); //插入序号列，默认列名为"num"，类型为int

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dt.Rows[i]["num"] = i + 1;
                    }
                }

                return dt;
            }
            catch(Exception e)
            {
                Debug.WriteLine(string.Format("objToDataTable错误，消息：{0}", e.Message));
                return null;
            }
        }

        #endregion

        // TODO:-------------------------------------------------- 重新整理 ---------------------------------------------------
        /// <summary>    
        /// 由DataSet导出Excel    
        /// </summary>    
        /// <param name="sourceTable">要导出数据的DataTable</param>    
        /// <param name="sheetName">工作表名称</param>    
        /// <returns>Excel工作表</returns>     
        private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheetNames = sheetName.Split(',');
            for (int i = 0; i < sheetNames.Length; i++)
            {
                ISheet sheet = workbook.CreateSheet(sheetNames[i]);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.            
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                // handling value.            
                int rowIndex = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workbook = null;
            return ms;
        }

        /// <summary>    
        /// 由DataTable导出Excel    
        /// </summary>    
        /// <param name="sourceTable">要导出数据的DataTable</param>    
        /// <returns>Excel工作表</returns>    
        public static bool ExportDataTableToExcel(DataTable sourceTable, string fileName ,string sheetName)
        {
            if(fileName.IsEmpty())
            {
                return false;
            }

            ISheet sheet = null;

            if (Path.GetExtension(fileName).Equals(".xls"))
            {
                HSSFWorkbook workbook = new HSSFWorkbook();

                sheet = workbook.CreateSheet(sheetName);

                if (!sheet.HasValue())
                {
                    return false;
                }

                FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);

                IRow headerRow = sheet.CreateRow(0);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }

                int rowIndex = 1;

                foreach (DataRow row in sourceTable.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }

                workbook.Write(fs);

                fs.Close();

                return true;

            }
            else if(Path.GetExtension(fileName).Equals(".xlsx"))
            {
                XSSFWorkbook workbook = new XSSFWorkbook();

                sheet = workbook.CreateSheet(sheetName);

                if (!sheet.HasValue())
                {
                    return false;
                }

                FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);

                IRow headerRow = sheet.CreateRow(0);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                }

                int rowIndex = 1;

                foreach (DataRow row in sourceTable.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                }

                workbook.Write(fs);

                fs.Close();

                return true;
            }

            return false;
        }
    }
}