(.net)通过NPOI操作Excel

1. 引用dll
   去管网下载NPOI：http://npoi.codeplex.com/releases/
   或者百度网盘：链接：https://pan.baidu.com/s/1GxG42R_TAo606D3IZ8yWvQ
   提取码：g4yx
   里面包含.net2.0，.net4.0，.net4.5

2. 工具类

using Brettle.Web.NeatUpload;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;
using System.Web.UI;

/// <summary>
/// ExcelImport 的摘要说明
/// 将Excel数据导入Datatable
/// 
/// 将dataset导出为Excel并下载
/// </summary>
public class ExcelImport
{
    public ExcelImport()
    {
    }

    //获取Excel上传路径
    public static string setFilepath(InputFile inputfile)
    {
        string SaveFilePath = "";
        string end = Path.GetExtension(inputfile.FileName);  //获取扩展名
        string datetime = DateTime.Now.Date.ToString("yyyyMMdd");
        string time = DateTime.Now.ToShortTimeString().Replace(":", "");
        string newFilename = datetime + time + DateTime.Now.Millisecond.ToString() + end;
        SaveFilePath = System.IO.Path.Combine(System.Web.HttpContext.Current.Request.MapPath("~/UpLoads/Excel"), newFilename);//文件存放路径
        inputfile.MoveTo(SaveFilePath, Brettle.Web.NeatUpload.MoveToOptions.Overwrite);
        return SaveFilePath;
    }

    //将Excel转为datatable
    public static DataTable ImportExcel(string filepath)
    {
        DataTable dt = new DataTable();
        using (FileStream fsRead = System.IO.File.OpenRead(filepath))
        {
            IWorkbook wk = null;
            //获取后缀名
            string extension = filepath.Substring(filepath.LastIndexOf(".")).ToString().ToLower();
            //判断是否是excel文件
            if (extension == ".xlsx" || extension == ".xls")
            {
                //判断excel的版本
                if (extension == ".xlsx")
                {
                    wk = new XSSFWorkbook(fsRead);
                }
                else
                {
                    wk = new HSSFWorkbook(fsRead);
                }

                //获取第一个sheet
                ISheet sheet = wk.GetSheetAt(0);
                //获取第一行
                IRow headrow = sheet.GetRow(0);
                //创建列
                for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
                {
                    //  DataColumn datacolum = new DataColumn(headrow.GetCell(i).StringCellValue);
                    DataColumn datacolum = new DataColumn("F" + (i + 1));
                    dt.Columns.Add(datacolum);
                }
                //读取每行,从第二行起
                for (int r = 1; r <= sheet.LastRowNum; r++)
                {
                    bool result = false;
                    DataRow dr = dt.NewRow();
                    //获取当前行
                    IRow row = sheet.GetRow(r);
                    //读取每列
                    for (int j = 0; j < headrow.Cells.Count; j++)
                    {
                        ICell cell = row.GetCell(j); //一个单元格
                        dr[j] = GetCellValue(cell); //获取单元格的值
                                                    //全为空则不取
                        if (dr[j].ToString() != "")
                        {
                            result = true;
                        }
                    }
                    if (result == true)
                    {
                        dt.Rows.Add(dr); //把每行追加到DataTable
                    }
                }
            }
        }


        return dt;
    }
    //对单元格进行判断取值
    private static string GetCellValue(ICell cell)
    {
        if (cell == null)
        {
            return string.Empty;
        }
        switch (cell.CellType)
        {
            case CellType.Blank:     //空数据类型
                return string.Empty;
            case CellType.Boolean:    //bool类型
                return cell.BooleanCellValue.ToString();
            case CellType.Error:
                return cell.ErrorCellValue.ToString();
            case CellType.Numeric: //数字类型
                if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                {
                    return cell.DateCellValue.ToString();
                }
                else //其它数字
                {
                    return cell.NumericCellValue.ToString();
                }
            case CellType.Unknown: //无法识别类型
            default: //默认类型
                return cell.ToString();//
            case CellType.String: //string 类型
                return cell.StringCellValue;
            case CellType.Formula: //带公式类型
                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return cell.ToString();
                }
                catch
                {
                    return cell.NumericCellValue.ToString();
                }
        }


    }

    //下载
    public static void downloadExcel(Page page,string fileName)
    {
        try
        {
            string strfile = page.Server.MapPath("~/UpLoads/Excel/"+fileName);
            FileInfo fileInfo = new FileInfo(strfile);
            page.Response.Clear();
            //Response.Charset = "GB2312";
            page.Response.ContentEncoding = System.Text.Encoding.UTF8;
            page.Response.AddHeader("Content-Disposition", "attachment;filename=" + page.Server.UrlEncode(fileInfo.Name));
            page.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
            page.Response.ContentType = "application/x-bittorrent";
            page.Response.WriteFile(fileInfo.FullName);
            page.Response.End();
        }
        catch (System.Threading.ThreadAbortException ex)
        {
            //不做处理
        }
        catch (Exception ex)
        {
            //做处理
        }
    }

    //将dataset导入Excel
    public static bool DsToExcel(Page page,DataSet _ds)
    {
        try
        {
            string datetime = DateTime.Now.Date.ToString("yyyyMMdd");
            string time = DateTime.Now.ToShortTimeString().Replace(":", "");
            string newFilename = datetime + time + DateTime.Now.Millisecond.ToString() + ".xlsx";
            string path= System.IO.Path.Combine(System.Web.HttpContext.Current.Request.MapPath("~/UpLoads/Excel"), newFilename);  //Excel存放路径
            #region NPOI 导出方式
            XSSFWorkbook hw = new XSSFWorkbook();

            #region output every sheet

            for (int t = 0; t < _ds.Tables.Count; t++)
            {
                ISheet sheet2 = (ISheet)hw.CreateSheet(_ds.Tables[t].TableName);

                IRow rowCol2 = (IRow)sheet2.CreateRow(0);
                for (int j = 0; j < _ds.Tables[t].Columns.Count; j++)
                {

                    ICell cell = (ICell)rowCol2.CreateCell(j);

                    cell.SetCellValue(_ds.Tables[t].Columns[j].ColumnName);
                }

                for (int i = 0; i < _ds.Tables[t].Rows.Count; i++)
                {
                    IRow row = (IRow)sheet2.CreateRow(i + 1);
                    for (int j = 0; j < _ds.Tables[t].Columns.Count; j++)
                    {
                        ICell cell = row.CreateCell(j);
                        //如果某一列是整型数据，在导出的Excel结果文件中那一列的值会转换成数值类型
                        if (j == 0 || j == 7 || j == 11 || j == 12 || j == 13 || j == 14 || j == 15 || j == 16 || j == 17)
                        {

                            int num = -1;
                            bool flag = int.TryParse(_ds.Tables[t].Rows[i][j].ToString(), out num);

                            if (flag)
                            {
                                cell.SetCellValue(num);
                            }
                            else
                            {
                                cell.SetCellValue(_ds.Tables[t].Rows[i][j].ToString());
                            }

                        }
                        else
                        {

                            if (_ds.Tables[t].Rows[i][j].ToString().Contains("<br>"))
                            {
                                ICellStyle cs = hw.CreateCellStyle();

                                cs.WrapText = true;
                                cell.CellStyle = cs;


                            }

                            cell.SetCellValue(_ds.Tables[t].Rows[i][j].ToString().Replace("<br>", "\r\n"));
                        }
                    }
                }
            }


            #endregion

            FileStream file = new FileStream(path, FileMode.Create);
            hw.Write(file);
            file.Close();

            #endregion

            //sw.WriteLine("生产的excel 建立完成，再次检查文件是否成功建立");
            #region check if the excel file is created
            if (!File.Exists(path))
            {
                //sw.WriteLine("生产的excel 未建立成功");
                return false;
            }
            #endregion

            //下载
            downloadExcel(page, newFilename);
            return true;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message, ex);
        }
    }



}