.Net操作excel（使用NPOI）

来源：互联网发布：腾讯网络加速器独立版编辑：程序博客网时间：2024/05/21 11:03

本文主要使用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.csdn.net/yzj_xiaoyue/article/details/78365975

阅读全文

0 0