NPOI读取Excel表格类-创新互联

           IDictionary fieldindexdic = new Dictionary();
           for (int i = rowstartindex; i <= rowlastindex; i++)
           {
               ICell cell = titlerow.GetCell(i);
               if (cell != null)
               {
                   string fieldstr = cell.ToString();
                   if (!string.IsNullOrEmpty(fieldstr))
                   {
                       fieldindexdic.Add(cell.ColumnIndex, fieldstr.ToUpper());
                   }
               }
           }

           IEnumerable fieldtitle = fieldindexdic.Select(x => x.Value).Distinct();

           IDictionary pifdic = GetPropertyInfoDic(fieldtitle);
           for (int i = 0; i < data.Count; i++)
           {
               IRow datarow = sheet.CreateRow(datarowindex + i);
               foreach (var titlekv in fieldindexdic)
               {
                   object dataobject = pifdic[titlekv.Value].GetValue(data[i], null);
                   if (dataobject != null)
                   {
                       ICell datacell = datarow.CreateCell(titlekv.Key);
                       datacell.SetCellValue(dataobject.ToString());
                   }
               }
           }
           return workbook.GetSheetIndex(sheet);
       }

       public IDictionary GetPropertyInfoDic(IEnumerable namelist)
           where T : new()
       {
           IDictionary pifdic = new Dictionary();
           PropertyInfo[] pifs = typeof(T).GetProperties();

           IEnumerable filedpifs = pifs.Where(x => namelist.Contains(x.Name.ToUpper()));
           foreach (var kv in filedpifs)
           {
               pifdic.Add(kv.Name.ToUpper(), kv);
           }
           return pifdic;
       }
       public void DeleteSheet(string sheetname)
       {
            int sheetindex = workbook.GetSheetIndex(sheetname);
            workbook.RemoveSheetAt(sheetindex);
       }
       public void Write(Stream sm)
       {
           workbook.Write(sm);
       }
       public void DeleteRow(string sheetname, int rowindex)
       {
           ISheet sheet = workbook.GetSheet(sheetname);
           IRow row = sheet.GetRow(rowindex);
           sheet.RemoveRow(row);
       }

       #region add by pcitdbt 2013/11/11

       #region 将DataTable的数据读取成MemoryStream
       public static MemoryStream RenderToExcel(DataTable dt)
       {
           MemoryStream ms = new MemoryStream();
           using (dt)
           {
               //创建Workbook
               HSSFWorkbook book = new HSSFWorkbook();
               ISheet sheet = book.CreateSheet(dt.TableName);
               //创建一个日期类型的格式
               ICellStyle dataStyle = book.CreateCellStyle();
               IDataFormat dataFormat = book.CreateDataFormat();
               dataStyle.DataFormat = dataFormat.GetFormat("yyyy-mm-dd");
               //创建表头
               IRow row = sheet.CreateRow(0);
               foreach (DataColumn col in dt.Columns)
               {
                   //给表头添加字段名字
                   row.CreateCell(col.Ordinal).SetCellValue(col.Caption);//Caption没有值则获取ColumnName
                   //设置列宽
                   sheet.SetColumnWidth(col.Ordinal, 30 * 110);
               }
               //创建数据行并添加值
               int rowIndex = 1;//标记数据行的位置
               foreach (DataRow dr in dt.Rows)
               {
                   IRow dataRow = sheet.CreateRow(rowIndex);

                   //通过列来获取值
                   foreach (DataColumn column in dt.Columns)
                   {
                       //判断是否是DataTime类型
                       ICell newCell = dataRow.CreateCell(column.Ordinal);
                       string drValue = dr[column].ToString();
                       switch (column.DataType.ToString())
                       {
                           case "System.String"://字符串类型
                               newCell.SetCellValue(drValue);
                               break;
                           case "System.DateTime"://日期类型
                               DateTime dateV;
                               DateTime.TryParse(drValue, out dateV);
                               //newCell.SetCellValue(dateV);
                               newCell.SetCellValue(drValue);

                               newCell.CellStyle = dataStyle;//格式化显示
                               break;
                           case "System.Boolean"://布尔型
                               bool boolV = false;
                               bool.TryParse(drValue, out boolV);
                               newCell.SetCellValue(boolV);
                               break;
                           case "System.Int16"://整型
                           case "System.Int32":
                           case "System.Int64":
                           case "System.Byte":
                               int intV = 0;
                               int.TryParse(drValue, out intV);
                               newCell.SetCellValue(intV);
                               break;
                           case "System.Decimal"://浮点型
                           case "System.Double":
                               double doubV = 0;
                               double.TryParse(drValue, out doubV);
                               newCell.SetCellValue(doubV);
                               break;
                           case "System.DBNull"://空值处理
                               newCell.SetCellValue("");
                               break;
                           default:
                               newCell.SetCellValue("");
                               break;
                       }

                   }

                   //循环一行后i的值自增1
                   rowIndex++;
               }

               book.Write(ms);
               ms.Flush();
               ms.Position = 0;//指定内存流的当前位置
             }

           return ms;
       }
       #endregion

       #region 将DataReader的数据转换成MemoryStream并返回
       public static MemoryStream RenderToExcel(IDataReader dataReader)
       {
           MemoryStream ms = new MemoryStream();
           using (dataReader)
           {
               HSSFWorkbook book = new HSSFWorkbook();
               ISheet sheet = book.CreateSheet("数据表1");
               //创建表头
               IRow row = sheet.CreateRow(0);
               //列的数目
               int columnCount = dataReader.FieldCount;
               for (int i = 0; i < columnCount; i++)
               {
                   row.CreateCell(i).SetCellValue(dataReader.GetName(i));
               }

               //创建数据行
               int rowIndex = 1;
               while (dataReader.Read())//dataReader只能一行一行地读取数据
               {
                   IRow dataRow = sheet.CreateRow(rowIndex);
                   for (int i = 0; i < columnCount; i++)
                   {

                       dataRow.CreateCell(i).SetCellValue(dataReader[i].ToString());
                   }

                   rowIndex++;
               }

               book.Write(ms);
               ms.Flush();
               ms.Position = 0;

           }

           return ms;
       }

       #endregion

       #region 将流输出到指定的位置
       //保存输出到文件
       public static void SaveToFile(MemoryStream ms, string fileName)
       {
           using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
           {
               byte[] data = ms.ToArray();
               fs.Write(data, 0, data.Length);
               fs.Flush();

               data = null;
           }
       }
       #endregion

       #region 保存输出到浏览器
       public static void SaveToBrowser(MemoryStream ms, System.Web.HttpContext context, string fileName)
       {
           // 设置编码和附件格式
           context.Response.ContentType = "application/vnd.ms-excel";
           context.Response.ContentEncoding = Encoding.UTF8;
           context.Response.Charset = "";
           context.Response.AppendHeader("Content-Disposition",
               "attachment;filename=" +System.Web.HttpUtility.UrlEncode(fileName, Encoding.UTF8));

           //添加请求报文头
           //context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
           context.Response.BinaryWrite(ms.ToArray());
           context.Response.End();
       }
       #endregion

       #region NPOI读取Excel流的数据相关
       public DataTable ReadFromExcel(Stream excelStream)
       {
           DataTable dt = new DataTable();
           using (excelStream)
           {
               //创建WorkBook
               HSSFWorkbook book = new HSSFWorkbook(excelStream);
               ISheet sheet = book.GetSheetAt(0);//获取第一个表
               //获取第一行表头
               IRow headRow = sheet.GetRow(0);
               //列数
               int columnCount = headRow.LastCellNum;//LastCellNum=PhysicalNumberOfCells
               int rowCount = sheet.LastRowNum;//LastRowNum=PhysicalNumberOfCellsRow-1
               //创建DataTable的表头
               for (int i = headRow.FirstCellNum; i < columnCount; i++)
               {
                   DataColumn dc = new DataColumn(headRow.GetCell(i).StringCellValue.ToString());
                   dt.Columns.Add(dc);
               }
               //创建数据
               for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
               {
                   //一行一行地创建
                   DataRow dr = dt.NewRow();
                   IRow dataRow = sheet.GetRow(i);
                   if (dataRow != null)
                   {
                       for (int j = headRow.FirstCellNum; j < columnCount; j++)
                       {
                           string cellValue = dataRow.GetCell(j).StringCellValue.ToString();
                           if (cellValue != "" || cellValue != null)
                           {
                               dr[j] = dataRow.GetCell(j).StringCellValue.ToString();
                           }

                       }

                       dt.Rows.Add(dr);
                   }

               }

           }

           return dt;
       }

       public static int RenderToDb(Stream excelFileStream, string insertSql)
       {
           int rowAffected = 0;
           using (excelFileStream)
           {
               HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
               ISheet sheet = workbook.GetSheetAt(0);//取第一个工作表
               StringBuilder builder = new StringBuilder();

               IRow headerRow = sheet.GetRow(0);//第一行为标题行
               int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
               int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

               for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
               {
                   IRow row = sheet.GetRow(i);
                   if (row != null)
                   {
                       builder.Append(insertSql);
                       builder.Append(" values (");
                       for (int j = row.FirstCellNum; j < cellCount; j++)
                       {
                           builder.AppendFormat("'{0}',", row.GetCell(j).StringCellValue.Replace("'", "''"));
                       }
                       builder.Length = builder.Length - 1;
                       builder.Append(");");
                   }

                   if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
                   {
                       //每50条记录一次批量插入到数据库
                       //rowAffected += dbAction(builder.ToString());
                       builder.Length = 0;
                   }
               }

           }
           return rowAffected;
       }

       public static bool HasData(Stream excelFileStream)
       {
           using (excelFileStream)
           {
               HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
               if (workbook.NumberOfSheets > 0)
               {
                   ISheet sheet = workbook.GetSheetAt(0);
                   return sheet.PhysicalNumberOfRows > 0;

               }
           }
           return false;
       }
       #endregion

       #endregion

   }