首先安装NPOI ，点击VS的—>工具—>NuGet包管理器—>管理解决方案的NuGet程序包
导出Excel如下:
/// <summary> /// 导出Excel /// </summary> /// <param name="TableName"></param> public static void ExportDataToExcel(DataTable TableName) { string FileName= DateTime.Now.GetHashCode().ToString(); SaveFileDialog saveFileDialog = new SaveFileDialog(); //设置文件标题 saveFileDialog.Title = "导出Excel文件"; //设置文件类型 saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls"; //设置默认文件类型显示顺序 saveFileDialog.FilterIndex = 1; //是否自动在文件名中添加扩展名 saveFileDialog.AddExtension = true; //是否记忆上次打开的目录 saveFileDialog.RestoreDirectory = true; //设置默认文件名 saveFileDialog.FileName = FileName; //按下确定选择的按钮 if (saveFileDialog.ShowDialog() == DialogResult.OK) { //获得文件路径 string localFilePath = saveFileDialog.FileName.ToString(); //数据初始化 int TotalCount; //总行数 int RowRead = 0; //已读行数 int Percent = 0; //百分比 TotalCount = TableName.Rows.Count; //NPOI IWorkbook workbook; string FileExt = Path.GetExtension(localFilePath).ToLower(); if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (FileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return; } ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName); //秒钟 Stopwatch timer = new Stopwatch(); timer.Start(); try { //读取标题 IRow rowHeader = sheet.CreateRow(0); for (int i = 0; i < TableName.Columns.Count; i++) { ICell cell = rowHeader.CreateCell(i); cell.SetCellValue(TableName.Columns[i].ColumnName); } //读取数据 for (int i = 0; i < TableName.Rows.Count; i++) { IRow rowData = sheet.CreateRow(i + 1); for (int j = 0; j < TableName.Columns.Count; j++) { ICell cell = rowData.CreateCell(j); cell.SetCellValue(TableName.Rows[i][j].ToString()); } //状态栏显示 RowRead++; Percent = (int)(100 * RowRead / TotalCount); Application.DoEvents(); } Application.DoEvents(); //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); fs.Close(); } Application.DoEvents(); //关闭秒钟 timer.Reset(); timer.Stop(); //成功提示 if (MessageBox.Show("导出成功，是否立即打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { System.Diagnostics.Process.Start(localFilePath); } } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { //关闭秒钟 timer.Reset(); timer.Stop(); } } }
/// <summary> /// 导出Excel模板 /// </summary> public static void ExportExampleToExcel() { DataTable TableName = new DataTable(); TableName.Columns.Add("字段1", typeof(string)); TableName.Columns.Add("字段2", typeof(string)); TableName.Columns.Add("字段3", typeof(string)); TableName.Columns.Add("字段4", typeof(string)); TableName.Columns.Add("字段5", typeof(string)); TableName.Columns.Add("字段6", typeof(string)); TableName.Columns.Add("字段7", typeof(string)); TableName.Columns.Add("字段8", typeof(string)); TableName.Columns.Add("字段9", typeof(string)); TableName.Columns.Add("字段10", typeof(string)); TableName.Columns.Add("字段12", typeof(string)); TableName.Columns.Add("字段13", typeof(string)); TableName.Columns.Add("字段14", typeof(string)); TableName.Columns.Add("字段15", typeof(string));
string FileName = "模板信息导入Excel模板"; SaveFileDialog saveFileDialog = new SaveFileDialog(); //设置文件标题 saveFileDialog.Title = "导出Excel文件"; //设置文件类型 saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls"; //设置默认文件类型显示顺序 saveFileDialog.FilterIndex = 1; //是否自动在文件名中添加扩展名 saveFileDialog.AddExtension = true; //是否记忆上次打开的目录 saveFileDialog.RestoreDirectory = true; //设置默认文件名 saveFileDialog.FileName = FileName; //按下确定选择的按钮 if (saveFileDialog.ShowDialog() == DialogResult.OK) { //获得文件路径 string localFilePath = saveFileDialog.FileName.ToString(); //数据初始化 int TotalCount; //总行数 int RowRead = 0; //已读行数 int Percent = 0; //百分比 TotalCount = 15; //NPOI IWorkbook workbook; string FileExt = Path.GetExtension(localFilePath).ToLower(); if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (FileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return; } ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName); //秒钟 Stopwatch timer = new Stopwatch(); timer.Start(); try { //读取标题 IRow rowHeader = sheet.CreateRow(0); for (int i = 0; i < TableName.Columns.Count; i++) { ICell cell = rowHeader.CreateCell(i); cell.SetCellValue(TableName.Columns[i].ColumnName); } Application.DoEvents(); //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); fs.Close(); } Application.DoEvents(); //关闭秒钟 timer.Reset(); timer.Stop(); //成功提示 if (MessageBox.Show("导出成功，是否立即打开？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { System.Diagnostics.Process.Start(localFilePath); } } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { //关闭秒钟 timer.Reset(); timer.Stop(); } } }
导入方法如下:
/// <summary> /// 将excel导入到datatable /// </summary> /// <param name="nosqllist">存放数据库取出的编号list</param> /// <param name="msge">返回结果</param> /// <returns>返回datatable</returns> public static DataTable ExcelToDataTable(List<string> nosqllist,out string msge) { bool isColumnName = true;//第一行是否是列名 msge = "0"; string filePath = "";//excel路径 List<string> NoList = new List<string>();//储存编号，防止重复 //打开文件对话框选择文件 OpenFileDialog file = new OpenFileDialog(); file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls"; file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); file.Multiselect = false; if (file.ShowDialog() == DialogResult.OK) { filePath = file.FileName; } DataTable dataTable = null; FileStream fs = null; DataColumn column = null; DataRow dataRow = null; IWorkbook workbook = null; ISheet sheet = null; IRow row = null; ICell cell = null; IRow rowisrepeat = null; ICell cellisrepeat = null; int startRow = 0; try { using (fs = File.OpenRead(filePath)) { // 2007版本 if (filePath.IndexOf(".xlsx") > 0) workbook = new XSSFWorkbook(fs); // 2003版本 else if (filePath.IndexOf(".xls") > 0) workbook = new HSSFWorkbook(fs); if (workbook != null) { sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet dataTable = new DataTable(); if (sheet != null) { int rowCount = sheet.LastRowNum;//总行数 if (rowCount > 0) { IRow firstRow = sheet.GetRow(0);//第一行 #region 判断列名是否一致 string[] exlist = GetStringNum();//得到定义的标题 for(int i=0;i< exlist.Length;i++) { if(Convert.ToString(firstRow.Cells[i])!= exlist[i]) { msge = "第"+i+1+"列标题不是"+exlist[i]+",列标题错误！"; return dataTable; } } #endregion int cellCount = firstRow.LastCellNum;//列数 //构建datatable的列 if (isColumnName) { startRow = 1;//如果第一行是列名，则从第二行开始读取 for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { cell = firstRow.GetCell(i); if (cell != null) { if (cell.StringCellValue != null) { column = new DataColumn(cell.StringCellValue); dataTable.Columns.Add(column); } } } } else { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { column = new DataColumn("column" + (i + 1)); dataTable.Columns.Add(column); } } #region 判断编号是否有重复 NoList = nosqllist; for (int i=startRow;i<=rowCount;++i) { rowisrepeat = sheet.GetRow(i); if (rowisrepeat == null) continue; cellisrepeat = rowisrepeat.GetCell(13); string noisrpt = Convert.ToString(cellisrepeat); if (!NoList.Contains(noisrpt)) { NoList.Add(noisrpt); } else { msge = "编号:"+ noisrpt+"重复，插入失败，请确定编号唯一、无重复！"; return dataTable; } } #endregion //填充行 for (int i = startRow; i <= rowCount; ++i) { row = sheet.GetRow(i); if (row == null) continue; dataRow = dataTable.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { cell = row.GetCell(j); if (cell == null) { dataRow[j] = ""; } else { //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,) switch (cell.CellType) { case CellType.Blank: dataRow[j] = ""; break; case CellType.Numeric: short format = cell.CellStyle.DataFormat; //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理 if (format == 14 || format == 31 || format == 57 || format == 58) dataRow[j] = cell.DateCellValue; else dataRow[j] = cell.NumericCellValue; break; case CellType.String: dataRow[j] = cell.StringCellValue; break; } } } dataTable.Rows.Add(dataRow); } } } } } return dataTable; } catch (Exception e) { msge = e.Message; if (fs != null) { fs.Close(); } return null; } } /// <summary> /// 定义待验证的Excel标题 /// </summary> /// <returns></returns> private static string[] GetStringNum() { string[] ExcelHeadList=new string[15] { "字段1", "字段2" , "字段3" , "字段4" , "字段5", "字段6","字段7", "字段8", "字段9", "字段10", "字段11", "字段12", "字段13", "字段14", "字段15" }; return ExcelHeadList; }