WPF通过NPIO读写Excel操作

自已摸索实现了对excel简单的上传和下载并做了一个封装类，下面分享一下。

 

先去官网：http://npoi.codeplex.com/下载需要引入dll（可以选择.net2.0或者.net4.0的dll），然后在网站中添加引用。

 

1、定义tools工具类：工具类封装了对excel的上传和下载功能

 public class Tools
    {
        #region 打开保存excel对话框返回文件名
       public static string SaveExcelFileDialog()
        {
            var sfd = new Microsoft.Win32.SaveFileDialog()
            {
                DefaultExt = "xls",
                Filter = "excel files(*.xls)|*.xls|All files(*.*)|*.*",
                FilterIndex = 1
            };

            if (sfd.ShowDialog() != true)
                return null;
            return sfd.FileName;
        }
        #endregion
        #region 打开excel对话框返回文件名
        public static string OpenExcelFileDialog()
        {
            var ofd = new Microsoft.Win32.OpenFileDialog()
            {
                DefaultExt = "xls",
                Filter = "excel files(*.xls)|*.xls|All files(*.*)|*.*",
                FilterIndex = 1
            };

            if (ofd.ShowDialog() != true)
                return null;
            return ofd.FileName;
        }
        #endregion
        #region 读excel
        public static DataTable ImportExcelFile()
        {
            DataTable dt = new DataTable();

            //打开excel对话框
            var filepath = OpenExcelFileDialog();
            if (filepath != null)
            {

                HSSFWorkbook hssfworkbook = null;
                #region//初始化信息
                try
                {
                    using (FileStream file = new FileStream(filepath, FileMode.Open, FileAccess.Read))
                    {
                        hssfworkbook = new HSSFWorkbook(file);
                    }
                }
                catch (Exception e)
                {
                    throw e;
                }
                #endregion

                var sheet = hssfworkbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
              
                for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                while (rows.MoveNext())
                {
                    HSSFRow row = (HSSFRow)rows.Current;
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        var cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = "";
                        }
                        else
                        {
                          if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
                            {
                                if (HSSFDateUtil.IsCellDateFormatted(cell))
                                {
                                    dr[i] = cell.DateCellValue;
                                }
                                else
                                {
                                    dr[i] = cell.NumericCellValue;
                                }
                            }
                            else if (cell.CellType == NPOI.SS.UserModel.CellType.Boolean)
                            {
                                dr[i] = cell.BooleanCellValue;
                            }
                            else
                            {
                                dr[i] = cell.StringCellValue;
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }
        #endregion

      

        #region list转datatable
        public static DataTable ListToDataTable<T>(IEnumerable<T> c)
        {
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
            if (c.Count() > 0)
            {
                for (int i = 0; i < c.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo item in props)
                    {
                        object obj = item.GetValue(c.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    dt.LoadDataRow(tempList.ToArray(), true);
                }
            }
            return dt;
        }
        #endregion
        #region 写入excel
        public static bool WriteExcel<T>(IList<T> list)
        {

            //打开保存excel对话框
            var filepath = SaveExcelFileDialog();
            if (filepath == null)
                return false;

            var dt = ListToDataTable<T>(list);

            if (!string.IsNullOrEmpty(filepath) && null != dt && dt.Rows.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1");

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                // 写入到客户端  
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filepath, FileMode.Create, FileAccess.Write))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }
            return true;
        }
        #endregion
    }

 

2、上传导入：list是集合，User是一个Model类

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            
            //读取数据至datatable
            var dt =Tools.ImportExcelFile();
            if (dt == null) return;

            //根据实际model转换成集合，规则：第一行一般是标题行，数据一般都是从第二行开始，所有从i=1开始读取
            for (int i = 1; i < dt.Rows.Count; i++)
            {
                var row = dt.Rows[i];
                list.Add(new User
                {
                    name = row[0].ToString(),
                    pwd = row[1].ToString()
                });
            }
        }

 

3、下载保存： list是一个集合，保存list中的数据到excel表中。

 //读取数据至datatable
            var IsOk = Tools.WriteExcel(list);
            if (IsOk)
            {
                MessageBox.Show("保存成功");
            }

 

使用非常方便吧。