NPOI 导入导出excel 支持 03 07

因为微软的office成本太高了，所以开发项目的时候电脑上没安装office,而是安装了wps。但开发语言用的是C#,所以直接调用微软的office组件是很方便的，但一方面慢，一方面成本高，所以从网上找到了NPOI这个开源的项目。http://npoi.codeplex.com/,引用的dll下载目录 http://npoi.codeplex.com/downloads/get/1476595

并且封装了通用的处理EXCEL 跟DataSet,DataTable的方法。方便调用 

以上是代码 （当前项目是.net 2.0 下的，如果需要.net 4.0则到NPOI官网下载相应的dll就可以了）

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

namespace MrLiu.Tools
{
    public sealed class ExcelHelper
    {
        #region Excel导入
        /// <summary>
        /// Excel 转换为DataTable
        /// </summary>
        /// <param name="file">文件路径</param>
        /// <param name="sheetName">Sheet名称，如果只有一个sheet可以传 null</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string file, string sheetName)
        {
            try
            {
                DataTable dt = new DataTable();
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    var workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
                    ISheet sheet = null;
                    if (sheetName == null)
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                    else
                    {
                        sheet = workbook.GetSheet(sheetName);
                    }
                    //列名
                    IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
                    for (int i = 0; i < rowHead.LastCellNum; i++)
                    {
                        string fildName = rowHead.GetCell(i).StringCellValue;
                        dt.Columns.Add(fildName, typeof(String));
                    }

                    //数据
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dr = dt.NewRow();
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            var cell = row.GetCell(j);
                            dr[j] = GetValueTypeForICell(cell);
                            if (dr[j] == null)
                            {
                                dr[j] = string.Empty;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// Excel 导入为DataTable
        /// </summary>
        /// <param name="file">文件路径</param>
        /// <param name="extension">后续名 XLS XLSX</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string file)
        {
            try
            {
                DataTable dt = new DataTable();
                string extension = Path.GetExtension(file);
                if (extension.ToUpper() == ".XLS")
                {
                    dt = ExcelToTableForXLS(file);
                }
                else if (extension.ToUpper() == ".XLS")
                {
                    dt = ExcelToTableForXLSX(file);
                }
                else
                {
                    throw new Exception("文件格式不正确");
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 读取xls格式的Excel
        /// </summary>
        /// <param name="file">文件全路径</param>
        /// <returns>返回DaTaTable</returns>
        public static DataTable ExcelToTableForXLS(string file)
        {
            try
            {
                DataTable dt = new DataTable();
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    var hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
                    ISheet sheet = hssfworkbook.GetSheetAt(0);

                    //列名
                    IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
                    for (int i = 0; i < rowHead.LastCellNum; i++)
                    {
                        string fildName = rowHead.GetCell(i).StringCellValue;
                        dt.Columns.Add(fildName, typeof(String));
                    }

                    //数据
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dr = dt.NewRow();
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;
                            dr[j] = GetValueTypeForXLS(cell);
                            if (dr[j] == null)
                            {
                                break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueTypeForXLS(NPOI.HSSF.UserModel.HSSFCell cell)
        {
            try
            {
                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:  
                    default:
                        return "=" + cell.CellFormula;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 读取xlsx格式的Excel
        /// </summary>
        /// <param name="file">文件全路径</param>
        /// <returns>返回DaTaTable</returns>
        public static DataTable ExcelToTableForXLSX(string file)
        {
            try
            {
                DataTable dt = new DataTable();
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.Read))
                {
                    var hssfworkbook = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
                    ISheet sheet = hssfworkbook.GetSheetAt(0);

                    //列名
                    IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
                    for (int i = 0; i < rowHead.LastCellNum; i++)
                    {
                        string fildName = rowHead.GetCell(i).StringCellValue;
                        dt.Columns.Add(fildName, typeof(String));
                    }

                    //数据
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dr = dt.NewRow();
                        for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                        {
                            NPOI.HSSF.UserModel.HSSFCell cell = row.GetCell(j) as NPOI.HSSF.UserModel.HSSFCell;
                            dr[j] = GetValueTypeForXLS(cell);
                            if (dr[j] == null)
                            {
                                break;
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>  
        /// 获取单元格类型(xlsx)  
        /// </summary>  
        /// <param name="cell"></param>  
        /// <returns></returns>  
        private static object GetValueTypeForXLSX(NPOI.XSSF.UserModel.XSSFCell cell)
        {
            try
            {
                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:  
                    default:
                        return "=" + cell.CellFormula;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>  
        /// 获取单元格类型不定
        /// </summary>  
        /// <param name="cell"></param>  
        /// <returns></returns>  
        private static object GetValueTypeForICell(ICell cell)
        {
            try
            {
                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:  
                    default:
                        return "=" + cell.CellFormula;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// Excel 转换为DataSet
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelToDataSet(string fileName)
        {
            try
            {
                if (!File.Exists(fileName))
                {
                    throw new Exception("文件不存在");
                }
                else
                {
                    DataSet ds = new DataSet();
                    using (FileStream reader = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                    {
                        IWorkbook book = WorkbookFactory.Create(reader);
                        int cnt = book.NumberOfSheets;
                        if (cnt <= 0)
                        {
                            throw new Exception("文件不是Excel文件");
                        }

                        for (int i = 0; i < cnt; i++)
                        {
                            ISheet sheet = book.GetSheetAt(i);
                            DataTable dt = new DataTable(sheet.SheetName);
                            IRow rowHead = sheet.GetRow(sheet.FirstRowNum);
                            for (int j = rowHead.FirstCellNum; j < rowHead.LastCellNum; j++)
                            {
                                ICell cell = rowHead.GetCell(j);
                                dt.Columns.Add(cell.StringCellValue);
                            }
                            for (int j = sheet.FirstRowNum + 1; j <= sheet.LastRowNum; j++)
                            {
                                DataRow dr = dt.NewRow();
                                IRow row = sheet.GetRow(j);
                                for (int k = rowHead.FirstCellNum; k < rowHead.LastCellNum; k++)
                                {
                                    dr[k] = row.GetCell(k).StringCellValue;
                                }
                                dt.Rows.Add(dr);
                            }
                            ds.Tables.Add(dt);
                        }
                    }
                    return ds;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion Excel导出

        #region Excel导出

        /// <summary>
        /// Excel导出
        /// </summary>
        /// <param name="dt">虚拟表</param>
        /// <param name="fileName">文件路径</param>
        /// <param name="sheetName">Sheet路径为空请传null</param>
        /// <returns></returns>
        public static bool DataTableToXLS(DataTable dt, string fileName, string sheetName)
        {
            try
            {
                if (dt == null)
                {
                    return false;
                }
                if (String.IsNullOrEmpty(sheetName))
                {
                    sheetName = Path.GetFileName(fileName);
                }
                var book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                book.CreateSheet();
                var sheet = book.CreateSheet(sheetName);

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

                using (FileStream fsWriter = new FileStream(fileName, FileMode.Append, FileAccess.Write, FileShare.Write))
                {
                    book.Write(fsWriter);
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

       
        /// <summary>
        /// DataSet 导出 到Excel
        /// </summary>
        /// <param name="ds">DataSet 表名默认为sheet名</param>
        /// <param name="fileName">文件路径</param>
        public static bool DataSetToExcel(DataSet ds, string fileName)
        {
            try
            {
                String extension = Path.GetExtension(fileName).ToUpper();
                IWorkbook book = null;
                if (extension == ".XLS")
                {
                    book = DataSetToHSSFWordbook(ds);
                }
                else if (extension == ".XLSX")
                {
                    book = DataSetToXSSFWorkbook(ds);
                }
                else
                {
                    throw new Exception("导入格式必须为xls或者xlsx");
                }

                using (FileStream fsWriter = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write, FileShare.ReadWrite))
                {
                    book.Write(fsWriter);
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// DataSet 转换为 XSSFWorkbook 07
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private static NPOI.XSSF.UserModel.XSSFWorkbook DataSetToXSSFWorkbook(DataSet ds)
        {
            try
            {
                var book = new NPOI.XSSF.UserModel.XSSFWorkbook();
                foreach (DataTable dt in ds.Tables)
                {
                    ISheet sheet = book.CreateSheet(dt.TableName);
                    IRow rowHead = sheet.CreateRow(0);
                    ICellStyle style = book.CreateCellStyle();
                    style.BorderBottom = BorderStyle.Thin;
                    style.BorderTop = BorderStyle.Thin;
                    style.BorderLeft = BorderStyle.Thin;
                    style.BorderRight = BorderStyle.Thin;
                    IFont font = book.CreateFont();
                    font.FontHeightInPoints = 12;
                    font.IsBold = true;
                    style.SetFont(font);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = rowHead.CreateCell(i);
                        cell.CellStyle = style;
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                    }
                    font.IsBold = false;
                    style.SetFont(font);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow row = sheet.CreateRow(i + 1);
                        DataRow dr = dt.Rows[i];
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row.CreateCell(j);
                            cell.CellStyle = style;
                            cell.SetCellValue(dr[j].ToString());
                        }
                    }
                }
                return book;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// DataSet 转换为 HSSFWorkbook 03
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        private static NPOI.HSSF.UserModel.HSSFWorkbook DataSetToHSSFWordbook(DataSet ds)
        {
            try
            {
                var book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                var dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "xx软件股份有限公司";
                var si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
                si.Subject = "xx系统自动导出";
                book.DocumentSummaryInformation = dsi;
                book.SummaryInformation = si;

                foreach (DataTable dt in ds.Tables)
                {
                    ISheet sheet = book.CreateSheet(dt.TableName);
                    IRow rowHead = sheet.CreateRow(0);
                    ICellStyle style = book.CreateCellStyle();
                    style.BorderBottom = BorderStyle.Thin;
                    style.BorderTop = BorderStyle.Thin;
                    style.BorderLeft = BorderStyle.Thin;
                    style.BorderRight = BorderStyle.Thin;
                    IFont font = book.CreateFont();
                    font.FontHeightInPoints = 12;
                    font.IsBold = true;
                    style.SetFont(font);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = rowHead.CreateCell(i);
                        cell.CellStyle = style;
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                    }
                    font.IsBold = false;
                    style.SetFont(font);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        IRow row = sheet.CreateRow(i + 1);
                        DataRow dr = dt.Rows[i];
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            ICell cell = row.CreateCell(j);
                            cell.CellStyle = style;
                            cell.SetCellValue(dr[j].ToString());
                        }
                    }
                }
                return book;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion
    }
}