用NPOI完成公司任务（主要就是导入导出操作）

注意要先添加程序集的引用

导出到excel:

   

    public override IWorkbook writeExecl(string filePath, DataTable dt)
        {
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }
            IWorkbook wk = new XSSFWorkbook(); ;
            //创建一个名称为data的表
            ISheet tb = wk.CreateSheet("data");
            //表头
            IRow row1 = tb.CreateRow(0);
            int k = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                ICell cell1 = row1.CreateCell(k);

                cell1.SetCellValue(getChinaName(dc.ColumnName));
                k = k + 1;
            }

            //表体
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = tb.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (j == 3)
                    {
                        ICell cell = row.CreateCell(j);
                        string value = dt.Rows[i][j].ToString();
                        cell.SetCellValue(tranSchoolType(value));
                    }
                    else
                    {
                        ICell cell = row.CreateCell(j);
                        string value = dt.Rows[i][j].ToString();
                        cell.SetCellValue(value);
                    }
                }

            }

           string uploadPath = HttpContext.Current.Server.MapPath(filePath);

             //打开一个xls文件，如果没有则自行创建

            //如果存在myxls.xls文件则在创建是不要打开该文件！
            using (FileStream fs = File.OpenWrite(uploadPath))
            {

              //向打开的这个xls文件中写入mySheet表并保存。
                wk.Write(fs); 

            }
        }

 

是用excel2003还是excel2007可以用下面这个方法：

      

 static IWorkbook isExcelType(string filePath)
        {
            IWorkbook wk = null;
            FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (filePath.IndexOf(".xlsx") > 0) // 2007版本
                wk = new XSSFWorkbook(fs);
            else if (filePath.IndexOf(".xls") > 0) // 2003版本
                wk = new HSSFWorkbook(fs);

            fs.Close();
            return wk;

        }

 

 

从excel读取数据：

 

   List<Dictionary<string, string>> readExcel(string filePath)      
  {          
           IWorkbook wk = null;         
           wk = isExcelType(filePath);          
           ISheet sheet = wk.GetSheetAt(0);           
           List<string> names = new List<string>();//LastRowNum 是当前表的总行数

            for (int j = 1; j <= sheet.LastRowNum; j++) 
            {  for (int j = 1; j <= sheet.LastRowNum; j++) 
            {

                 //读取当前行数据
                IRow row = sheet.GetRow(j); 
                if (row != null)
                {
                      //LastCellNum 是当前行的总列数
                    for (int k = 0; k <= row.LastCellNum; k++) 
                    {

 

                          //当前表格
                        ICell cell = row.GetCell(k); 
                        if (cell != null)
                        {
                            string name = cell.ToString();  
                            names.Add(name);
                        }
                    }
                }
            }
            List<Dictionary<string, string>> lists = new List<Dictionary<string, string>>();
            return lists;
        }

 

 

好了，以上就简单的完成导入导出了！