NPOI_Help

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace YueXianHuoWuLiu
{
    public class Common
    {

        private static DateTime _dtStart = new DateTime(1970, 1, 1, 8, 0, 0);

        /// <summary> 
        /// 获取时间戳 
        /// </summary>  
        public static string GetTimeStamp(DateTime dateTime)
        {
            return Convert.ToInt64(dateTime.Subtract(_dtStart).TotalSeconds).ToString();
        }

        /// <summary>
        /// DataTable转Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static byte[] DataTableToExcel(DataTable dt)
        {
            if (dt == null)
            {
                throw new ArgumentException("参数dt错误");
            }
            else
            {
                IWorkbook workbook = null;
                IRow row = null;
                ISheet sheet = null;
                ICell cell = null;

                workbook = new HSSFWorkbook();
                sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表  
                int rowCount = dt.Rows.Count;//行数  
                int columnCount = dt.Columns.Count;//列数  

                //设置列头  
                row = sheet.CreateRow(0);//excel第一行设为列头  
                for (int c = 0; c < columnCount; c++)
                {
                    cell = row.CreateCell(c);
                    cell.SetCellValue(dt.Columns[c].ColumnName);
                }

                //设置每行每列的单元格,  
                for (int i = 0; i < rowCount; i++)
                {
                    row = sheet.CreateRow(i + 1);
                    for (int j = 0; j < columnCount; j++)
                    {
                        cell = row.CreateCell(j);//excel第二行开始写入数据  
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }

                //#region AutoSize
                自动调整列宽
                //for (int i = 0; i < columnCount; i++)
                //{
                //    sheet.AutoSizeColumn(i);
                //}
                //#endregion

                using (MemoryStream ms = new MemoryStream())
                {
                    workbook.Write(ms);
                    return ms.GetBuffer();
                }
            }
        }


        /// <summary>  
        /// 将excel导入到datatable  
        /// </summary>  
        /// <param name="filePath">excel路径</param>  
        /// <param name="isColumnName">第一行是否是列名</param>  
        /// <returns>返回datatable</returns>  
        public static DataTable ExcelToDataTable(string filePath, int sheetNo, int headerRowIndex, out string errorColumn)
        {
            errorColumn = string.Empty;
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = headerRowIndex + 1;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本  
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本  
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(sheetNo);//读取第一个sheet，当然也可以循环读取每个sheet  
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数  
                            if (rowCount > 0)
                            {
                                //headerRow
                                IRow firstRow = sheet.GetRow(headerRowIndex);//第一行  
                                int startCellNum = firstRow.FirstCellNum;
                                int cellCount = firstRow.LastCellNum;//列数  

                                for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                {
                                    cell = firstRow.GetCell(i);
                                    if (cell != null && !string.IsNullOrWhiteSpace(cell.ToString().Trim()))
                                    {
                                        column = new DataColumn(cell.StringCellValue.Trim());
                                        dataTable.Columns.Add(column);
                                    }
                                }
                                cellCount = dataTable.Columns.Count;
                                //填充行  
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);

                                    if (row == null)
                                    {
                                        continue;
                                    }
                                    dataRow = dataTable.NewRow();
                                    for (int j = startCellNum; j < cellCount; ++j)
                                    {
                                        errorColumn = "（" + dataTable.Columns[j].ColumnName + "）所在列与" + "（第" + (i + 1) + "行）交集单元格数据有问题";
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = DBNull.Value;
                                        }
                                        else
                                        {
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = DBNull.Value;
                                                    break;
                                                case CellType.Numeric:
                                                    if (DateUtil.IsCellDateFormatted(cell))
                                                    {
                                                        dataRow[j] = cell.DateCellValue.ToString("yyyy/MM/dd");
                                                    }
                                                    else
                                                    {
                                                        dataRow[j] = cell.NumericCellValue.ToString().Trim();
                                                    }
                                                    break;
                                                case CellType.String:
                                                    if (string.IsNullOrWhiteSpace(cell.StringCellValue))
                                                    {
                                                        dataRow[j] = DBNull.Value;
                                                    }
                                                    else
                                                    {
                                                        //过滤单引号
                                                        dataRow[j] = cell.StringCellValue.Replace("'", "").Trim();
                                                    }
                                                    break;
                                                case CellType.Formula:
                                                    dataRow[j] = cell.NumericCellValue.ToString().Trim();
                                                    break;
                                                default:
                                                    dataRow[j] = DBNull.Value;
                                                    break;
                                            }
                                        }
                                    }
                                    bool isNull = true;
                                    for (int k = 0; k < cellCount; k++)
                                    {
                                        if (dataRow[k] != DBNull.Value)
                                        {
                                            isNull = false;
                                            break;
                                        }
                                    }
                                    if (!isNull) dataTable.Rows.Add(dataRow);
                                    else dataRow.Delete();
                                }
                            }
                        }
                    }
                }
                errorColumn = string.Empty;
                return dataTable;
            }
            catch (Exception ex)
            {
              
                return null;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                    fs.Dispose();
                }
            }
        }



    }
}