C#之Excel的是与非

要想操作Excel，就必须要先引用它的dll，

引用的dll下载地址： http://npoi.codeplex.com/releases/view/616377

其实操作Excel的功能有很多，可惜我目前只学会了，Excel和DataTable之间的转换，并做了一个简单的封装，等以后遇到新的这方面的知识再来补充吧。

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

namespace DS.Applications.Tools.Excel.Helper
{
    public static class HandleExcel
    {
        public static IWorkbook workBook = null;
        public static ISheet sheet = null;
        public static int startRow = 0;

        /// <summary>
        /// 将Excel转换成DataTable
        /// </summary>
        /// <param name="filePath">Excel路径</param>
        /// <param name="sheetName">sheet名</param>
        /// <param name="isExistColumn">原来是否存在表头</param>
        /// <param name="isSavedColumn">现在是否要保留表头</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filePath, string sheetName, bool isExistColumn, bool isSavedColumn)
        {
            DataTable dt = new DataTable();
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.IndexOf("xls") > 0)// 2007以前版本
                {
                    workBook = new HSSFWorkbook(fs);
                }
                if (filePath.IndexOf(".xlsx") > 0) // 2007版本及以上
                {
                    workBook = new XSSFWorkbook(fs);
                }
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workBook.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        sheet = workBook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workBook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    //处理表头
                    if (isExistColumn && isSavedColumn)
                    {
                        IRow firstRow = sheet.GetRow(0);
                        if (firstRow != null)
                        {
                            int columnCount = firstRow.LastCellNum;
                            for (int i = firstRow.FirstCellNum; i < columnCount; i++)
                            {
                                ICell cell = firstRow.Cells[i];
                                if (cell != null && cell.StringCellValue != null)
                                {
                                    DataColumn dc = new DataColumn(cell.ToString());
                                    dt.Columns.Add(dc);
                                }
                            }
                            startRow = 1;
                        }
                    }
                    else if (isExistColumn && !isSavedColumn)
                    {
                        startRow = 1;
                    }
                    else
                    {
                        startRow = 0;
                    }

                    for (int i = startRow; i <= sheet.LastRowNum; i++)
                    {
                        IRow currentRow = sheet.GetRow(i);
                        if (currentRow == null)
                        {
                            continue;
                        }
                        if (dt.Columns.Count == 0)
                        {
                            for (int j = currentRow.FirstCellNum; j < currentRow.LastCellNum; j++)
                            {
                                DataColumn dc = new DataColumn("未定义" + j);
                                dt.Columns.Add(dc);
                            }
                        }
                        DataRow dr = dt.NewRow();
                        for (int j = currentRow.FirstCellNum; j < currentRow.LastCellNum; j++)
                        {
                            if (currentRow.Cells[j] != null)
                            {
                                dr[j] = currentRow.Cells[j].ToString();
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 将DataTable转换成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath">Excel路径</param>
        /// <param name="isSavedColumn">是否保留表头</param>
        /// <param name="sheetName">表名</param>
        /// <param name="count">Excel行数</param>
        /// <returns></returns>
        public static bool DataTableToExcel(DataTable dt, string filePath, bool isSavedColumn, string sheetName, out int count)
        {
            count = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
                {
                    try
                    {
                        if (filePath.IndexOf("xls") > 0)// 2007以前版本
                        {
                            workBook = new HSSFWorkbook();
                        }
                        if (filePath.IndexOf(".xlsx") > 0) // 2007版本及以上
                        {
                            workBook = new XSSFWorkbook();
                        }
                        if (workBook != null)
                        {
                            if (!string.IsNullOrEmpty(sheetName))
                            {
                                sheet = workBook.CreateSheet(sheetName);
                            }
                            else
                            {
                                sheet = workBook.CreateSheet();
                            }

                        }
                        if (isSavedColumn)
                        {
                            IRow firstRow = sheet.CreateRow(0);
                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                firstRow.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                            }
                            count = 1;
                        }
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            IRow row = sheet.CreateRow(count);
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                            }
                            count++;
                        }
                        workBook.Write(fs);
                        return true;
                    }
                    catch
                    {
                        return false;
                    }
                }
            }
            else
            {
                return false;
            }
        }
    }
}