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("保存成功"); }
使用非常方便吧。