ExcelHelper.cs

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

namespace DAL
{
    public class ExcelHelper
    {
        #region Excel2007
        /// <summary>
        /// 将Excel文件中的数据读出到DataTable中(xlsx)
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLSX(string file)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
                ISheet sheet = xssfworkbook.GetSheetAt(0);

                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));

                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        if (sheet.GetRow(i) != null)
                        {
                            dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
                            if (dr[j] != null && dr[j].ToString() != string.Empty)
                            {
                                hasValue = true;
                            }
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 获取单元格类型(xlsx)
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLSX(XSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                    if (cell.CachedFormulaResultType == CellType.Numeric)
                    {
                        return cell.NumericCellValue;
                    }
                    else
                    {
                        return cell.StringCellValue;
                    }
                default:
                    return "=" + cell.CellFormula;
            }
        }
        #endregion

        /// <summary>
        /// 将DataTable数据导出到Excel文件中(xlsx)
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void TableToExcelForXLSX(DataTable dt, string file)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet("Test");

            //表头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        #region Excel2003
        /// <summary>
        /// 讀取Excel資料到DataTable(xls)
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ExcelToTableForXLS(string file)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
                ISheet sheet = hssfworkbook.GetSheetAt(0);

                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueTypeForXLS2(header.GetCell(i) as HSSFCell);
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        if (sheet.GetRow(i) != null)
                        {
                            dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
                            if (dr[j] != null && dr[j].ToString() != string.Empty)
                            {
                                hasValue = true;
                            }
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;

        }
        private static object GetValueTypeForXLS(HSSFCell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN: 
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                    if (cell.CachedFormulaResultType == CellType.Numeric)
                    {
                        return cell.NumericCellValue;
                    }
                    else
                    {
                        return cell.StringCellValue;
                    }
                default:
                    return "=" + cell.CellFormula;
            }
        }

        private static object GetValueTypeForXLS2(HSSFCell cell)
        {
            object returnValue = "";
            if (cell == null)
                returnValue = "";
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    returnValue = null; break;
                case CellType.Boolean: //BOOLEAN:
                    returnValue = cell.BooleanCellValue; break;
                case CellType.Numeric: //NUMERIC:
                    short format = cell.CellStyle.DataFormat;
                    if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20)
                    {
                        returnValue = cell.DateCellValue.ToString("yyyy/MM/dd");
                    }
                    else
                    {
                        returnValue = cell.NumericCellValue;
                    }
                    break;
                case CellType.String: //STRING:
                    returnValue = cell.StringCellValue; break;
                case CellType.Error: //ERROR:
                    returnValue = cell.ErrorCellValue; break;
                case CellType.Formula: //FORMULA:
                    if (cell.CachedFormulaResultType == CellType.Numeric)
                    {
                        returnValue = cell.NumericCellValue;
                    }
                    else
                    {
                        returnValue = cell.StringCellValue;
                    }
                    break;
                default:
                    returnValue = cell.CellFormula; break;
            }
            return returnValue;
        }
        #endregion

        // 使用NPOI導出DataTable到指定的Excel模板中

        #region   通过模板导出Excel文件

        /// <summary>
        /// 通过模板导出Excel文件
        /// </summary>
        /// <param name="dt">需要导出的DataTable表</param>
        /// <param name="templatePath">使用的的Excel模板路径和名称</param>
        /// <param name="outFilePath">导出的Excel模板路径和名称</param>
        /// <param name="sheetName">sheetName</param>
        /// <param name="startRowIndex">开始添加数据行的索引</param>
        public void ExcelExportByTemplate(DataTable dt, string templatePath, string outFilePath, string sheetName,
            int startRowIndex = 0)
        {
            MemoryStream stream = new MemoryStream();
            try
            {
                IWorkbook workBook;
                var outPath = "";
                var fileExt = Path.GetExtension(outFilePath);
                if (fileExt == ".xlsx")
                {
                    outPath = outFilePath.Substring(0, outFilePath.Length - 5) + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
                    File.Copy(templatePath, outPath, true);//可以覆蓋已存在的目的文件
                    //File.Copy(templatePath, outPath);//不可以覆蓋已存在的目的文件 
                    using (var fs = File.Open(outPath, FileMode.Open, FileAccess.Read, FileShare.Write))
                    {
                        workBook = new XSSFWorkbook(fs);
                    }
                }
                else if (fileExt == ".xls")
                {
                    outPath = outFilePath.Substring(0, outFilePath.Length - 4) + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
                    File.Copy(templatePath, outPath, true);
                    using (var fs = File.Open(outPath, FileMode.Open, FileAccess.Read, FileShare.Write))
                    {
                        workBook = new HSSFWorkbook(fs);
                    }
                }
                else
                {
                    workBook = null;
                }

                if (workBook != null)
                {
                    var style = workBook.CreateCellStyle();
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

                    /** 边框样式(四周边框)，如果文本框内本身有内容，设置边框样式会失效 **/
                    style.BorderTop = BorderStyle.Thin;//上边框
                    style.BorderBottom = BorderStyle.Thin;//下边框
                    style.BorderLeft = BorderStyle.Thin;//左边框
                    style.BorderRight = BorderStyle.Thin;//右边框
                    /** 边框颜色 **/
                    style.TopBorderColor = 55;//上边框颜色
                    style.BottomBorderColor = 55;下边框颜色
                    style.LeftBorderColor = 55;//左边框颜色
                    style.RightBorderColor = 55; //右边框颜色
                    /** 字体 **/
                    IFont font = style.GetFont(workBook);
                    font.FontName = "DengXian";//字体
                    font.Color = 8;//黑色
                    font.FontHeightInPoints = 11;//字体高度(与excel中的字号一致)
                    //font.IsBold = true;//是否加粗
                    //font.IsItalic = true;//是否斜体
                    //font.IsStrikeout = true;//是否加删除线
                    style.SetFont(font);
                    var sheet = string.IsNullOrEmpty(sheetName) ? workBook.GetSheetAt(0) : workBook.GetSheet(sheetName);

                    for (var i = startRowIndex; i < dt.Rows.Count; i++)
                    {
                        var row = sheet.CreateRow(i + 1);
                        //var row = sheet.CreateRow(i);
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            var cell = row.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            cell.CellStyle = style;
                        }
                    }
                }

                转为字节数组

                workBook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件
                using (FileStream fs = new FileStream(outFilePath, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception:" + ex.Message);
            }
            finally
            {
                stream.Dispose();
            }
        }
        public void ExcelExportByTemplateOfBatch(DataTable dt, string templatePath, string outFilePath, string sheetName,
            int startRowIndex = 0)
        {
            MemoryStream stream = new MemoryStream();
            try
            {
                IWorkbook workBook;
                var outPath = "";
                var fileExt = Path.GetExtension(outFilePath);
                if (fileExt == ".xlsx")
                {
                    outPath = outFilePath.Substring(0, outFilePath.Length - 5) + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xlsx";
                    File.Copy(templatePath, outPath, true);//可以覆蓋已存在的目的文件
                    //File.Copy(templatePath, outPath);//不可以覆蓋已存在的目的文件 
                    using (var fs = File.Open(outPath, FileMode.Open, FileAccess.Read, FileShare.Write))
                    {
                        workBook = new XSSFWorkbook(fs);
                    }
                }
                else if (fileExt == ".xls")
                {
                    outPath = outFilePath.Substring(0, outFilePath.Length - 4) + DateTime.Now.ToString("yyyyMMddhhmmssffff") + ".xls";
                    File.Copy(templatePath, outPath, true);
                    using (var fs = File.Open(outPath, FileMode.Open, FileAccess.Read, FileShare.Write))
                    {
                        workBook = new HSSFWorkbook(fs);
                    }
                }
                else
                {
                    workBook = null;
                }

                if (workBook != null)
                {
                    var style = workBook.CreateCellStyle();
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    var sheet = string.IsNullOrEmpty(sheetName) ? workBook.GetSheetAt(0) : workBook.GetSheet(sheetName);

                    for (var i = startRowIndex; i < dt.Rows.Count; i++)
                    {
                        var row = sheet.CreateRow(i + 1);
                        //var row = sheet.CreateRow(i);
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            var cell = row.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            //PC導入的日期欄位
                            if (j == 8 || j == 15 || j == 21 || j == 24 || j == 27)
                            {
                                style.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/M/d");//設定日期格式
                            }
                            cell.CellStyle = style;
                        }
                    }
                }

                转为字节数组

                workBook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件
                using (FileStream fs = new FileStream(outFilePath, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Exception:" + ex.Message);
            }
            finally
            {
                stream.Dispose();
            }
        }
        #endregion
    }
}