.Net操作excel（使用NPOI）

柔光的暖阳◎ 2022-06-06 02:27 233阅读 0赞

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

NPOI官方下载地址: [http://npoi.codeplex.com/releases][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.csdn.net/yzj\_xiaoyue/article/details/78365975

[http_npoi.codeplex.com_releases]: http://npoi.codeplex.com/releases