.Net操作excel（使用NPOI）

本文主要使用c#来操作excel，主要使用程序集NPOI来导入导出。

NPOI官方下载地址: http://npoi.codeplex.com/releases，也可以在vs中使用Nuget来管理NPOI程序集

导入excel文件数据到数据库

        private void BtnInput_Click(object sender, EventArgs e)
        {
            btnInput.Enabled = false;
            //将excel中的Word导入到sqlserver中 
            //string connString = ConfigurationManager.ConnectionStrings["EnglishWordMemoryContext"].ConnectionString;
            OpenFileDialog fd = new OpenFileDialog()
            {
                Filter = "Excel表格(*.xls)|*.xls|Excel表格(*.xlsx)|*.xlsx",
                RestoreDirectory = true
            };
            if (fd.ShowDialog() == DialogResult.OK)
            {
                opd = fd;
            }
            else
            {
                btnInput.Enabled = true;
                return;
            }
            Thread thread = new Thread(InputFileExcel)
            {
                IsBackground = true
            };
            thread.Start();
        }

        private void InputFileExcel()
        {
            K = 0;
            TimeSpan time;
            using (var trans=_context.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
            {
                try
                {
                    IWorkbook book=null;
                    startTime = DateTime.Now;
                    FileStream fs = new FileStream(opd.FileName, FileMode.Open, FileAccess.Read);
                    string fileExt = Path.GetExtension(opd.FileName).ToLower();
                    if (fileExt == ".xlsx")
                    {
                        book = new XSSFWorkbook(fs);
                    }
                    else if (fileExt == ".xls")
                    {
                        book = new HSSFWorkbook(fs);
                    }                 
                    int sheetCount = book.NumberOfSheets;
                    for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
                    {
                        NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex);
                        if (sheet == null)
                        {
                            continue;
                        }
                        //第一行是标题，不要导入到数据
                        for (int i = 1; i < sheet.LastRowNum; i++)
                        {
                            if(sheet.GetRow(i).GetCell(0)==null&& sheet.GetRow(i).GetCell(1)==null&& sheet.GetRow(i).GetCell(2)==null&& sheet.GetRow(i).GetCell(3)==null)
                            {
                                break;
                            }
                            K++;
                            Word word = new Word()
                            {
                                Id = sheet.GetRow(i).GetCell(0).ToInt32(),
                                WordEntry = sheet.GetRow(i).GetCell(1).ToString(),
                                WordProperty = sheet.GetRow(i).GetCell(2).ToString(),
                                Meaning = sheet.GetRow(i).GetCell(3).ToString()
                            };
                            _context.Words.Add(word);
                        }
                        //保存数据到数据库
                        _context.SaveChanges();
                        endTime = DateTime.Now;
                        trans.Commit();
                    }
                }
                catch (Exception ex)
                {
                    time = DateTime.Now - startTime;
                    string timespan = "用时:"
                    + time.Hours.ToString() + "小时"
                    + time.Minutes.ToString() + "分钟"
                    + time.Seconds.ToString() + "秒";
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.InnerException);
                    Console.WriteLine(ex.Source);
                    IsException = true;
                    MessageBox.Show("在第" + (K + 1) + "行发生错误，" + timespan + "。导入失败！\r\n失败原因：" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
                    trans.Rollback();
                }
            }
            Invoke(new Input(InputThread), opd);
        }
        private void InputThread(OpenFileDialog opd)
        {
            if (InvokeRequired)
            {
                Invoke(new Input(InputThread), opd);
            }
            else
            {
                TimeSpan ts = endTime - startTime;
                string timespan = "用时:"
                + ts.Hours.ToString() + "小时"
                + ts.Minutes.ToString() + "分钟"
                + ts.Seconds.ToString() + "秒";
                if (!IsException)
                {
                    MessageBox.Show("导入成功，导入" + K + "条记录" + timespan, "提示", MessageBoxButtons.OK, MessageBoxIcon.None);
                }
                btnInput.Enabled = true;
                IsException = false;
            }
        }

导出到excel：

 /// <summary>
        /// 导出数据到Excel中
        /// </summary>
        /// <param name="Url">文件导出地址</param>
        public void Export(string Url, List<Word> list)
        {
            errorRow = 0;
            try
            {
                //创建Excel文件的对象
                IWorkbook book;
                string fileExt = Path.GetExtension(Url).ToLower();
                if (fileExt == ".xlsx")
                {
                    book = new XSSFWorkbook();
                }
                else if (fileExt == ".xls")
                {
                    book = new HSSFWorkbook();
                }
                else
                {
                    book = null;
                }
                if (book == null)
                {
                    return;
                }

                //添加一个sheet
                ISheet sheetWords = book.CreateSheet("Words");

                //给sheet添加第一行的头部标题
                IRow rowWords = sheetWords.CreateRow(0);
                rowWords.CreateCell(0).SetCellValue("序号");
                rowWords.CreateCell(1).SetCellValue("词条");
                rowWords.CreateCell(2).SetCellValue("词性词义");

                //将数据逐步写入sheet各个行
                for (int i = 0, k = 0; i < list.Count - 1; i++, k++)
                {
                      IRow rowtemps = sheetWords.CreateRow(k + 1);
                      rowtemps.CreateCell(0).SetCellValue(k + 1);
                      rowtemps.CreateCell(1).SetCellValue(list[i + 1].WordEntry);
                      rowtemps.CreateCell(2).SetCellValue(list[i + 1].WordProperty + list[i + 1].Meaning);
                }
                // 写入到文件           
                FileStream fs = new FileStream(Url, FileMode.Create);
                book.Write(fs);
                fs.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("在第" + errorRow + "行发生错误，" + ex.Message.ToString()+"\t"+ex.ToString());
            }
        }

注：数据操作使用EF

错误解决方法见：http://blog.youkuaiyun.com/yzj_xiaoyue/article/details/78365975