C#对Excel的操作

//1 首先引入npoi操作的程序集
引用 NPOI.dll Ionic.Zip.dll
using NPOI.SS.UserModel; //包含对excel进行操作的方法
using NPOI.HSSF.UserModel; //包含excel每个sheet的属性

（1）Excel读取数据、Excel写入数据

（2）项目导入、项目导出

/// <summary>
        /// 从Excel读 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnReadFromExcel_Click(object sender, EventArgs e)
        {
            //从流stream中读取
            using(Stream stream=new FileStream("/Files/myexcel.xls",FileMode.Open,FileAccess.Read))
            {
                //读取workbook
                IWorkbook workbook=new HSSFWorkbook(stream);
                ISheet sheet = workbook.GetSheetAt(0);
                for (int i = 0; i < sheet.LastRowNum;i++ )
                {
                    IRow row = sheet.GetRow(i);
                    foreach(ICell cell in row.Cells)
                    {
                        string c = cell.StringCellValue;
                        MessageBox.Show(c);
                    }
                }
            }
            MessageBox.Show("ok,读取成功");
        }

        /// <summary>
        /// 写入Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnWriteToExcel_Click(object sender, EventArgs e)
        {
            //初始化workbook
            IWorkbook workbook = new HSSFWorkbook();
            //创建sheet
            ISheet sheet = workbook.CreateSheet("班级表");
            //创建row
            IRow row = sheet.CreateRow(0);
            //创建cell
            ICell cell = row.CreateCell(0);
            cell.SetCellType(CellType.STRING);
            cell.SetCellValue("hello");
            //写入流
            using(Stream stream=new FileStream("/Files/myexcel2.xls",FileMode.OpenOrCreate,FileAccess.Write))
            {
                workbook.Write(stream);
            }
            MessageBox.Show("ok,写入成功");
        }

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            //获得数据表
            List<Object> list = myORM_BLL.SelectAllModel(typeof(T_CUSTOMER));
            //初始化workbook
            IWorkbook workbook = new HSSFWorkbook();
            //创建sheet
            ISheet sheet = workbook.CreateSheet("T_CUSTOMER");
            //遍历数据表  名称用反射获得,数据行直接获得
            //创建头行headrow
            IRow headrow = sheet.CreateRow(0);
            Type type = typeof(T_CUSTOMER);
            PropertyInfo[] props = type.GetProperties();
            for(int i=0;i<props.Length; i++)
            {
                string propName = props[i].Name;
                ICell cell = headrow.CreateCell(i);
                cell.SetCellType(CellType.STRING);
                cell.SetCellValue(propName);
            }
            //创建数据行
            //遍历集合 每个对象创建一个行
            for (int j = 0; j < list.Count;j++ )
            {
                IRow row = sheet.CreateRow(j + 1);
                Object obj = list[j];
                Type tp = obj.GetType();
                PropertyInfo[] props2 = tp.GetProperties();
                //对象的每个属性 创建一个Cell
                for (int k = 0; k < props2.Length;k++ )
                {
                    string propName2 = props2[k].Name;
                    object propValue2 = props2[k].GetValue(obj);
                    //对于每行创建cell，设置值
                    ICell cell = row.CreateCell(k);
                    cell.SetCellType(CellType.STRING);
                    cell.SetCellValue(propValue2.ToString());
                }
            }
            //写入stream
            using(Stream stream=new FileStream("/Files/exportdata.xls",FileMode.OpenOrCreate,FileAccess.Write))
            {
                workbook.Write(stream);
            }
            MessageBox.Show("导出成功");
        }

        /// <summary>
        /// 把C#中类型转Excel中类型
        /// </summary>
        /// <param name="cTyName">C#中类型 的名称</param>
        /// <returns>Excel中类型</returns>
        private CellType CelltypeToCType(string cTyName)
        {
            switch (cTyName)
            {
                case "Int16":
                case "Int64":
                case "Int32": return CellType.NUMERIC; 
                case "String": return CellType.STRING; 
                case "Data": 
                case "DateTime": return CellType.FORMULA; 
                default: throw new Exception("未知类型：" + cTyName);
            }
        }


        /// <summary>
        /// 从DB直接导出到Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnExportToExcelFromDB_Click(object sender, EventArgs e)
        {
            //初始化workbook，创建sheet
            IWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("T_CUSTOMER");
            //打开conn ,发出cmd，reader查询
            string sql = "SELECT * FROM T_CUSTOMER";
            using(OracleConnection conn=OracleHelper.CreateConnection())
            using (OracleCommand cmd = new OracleCommand(sql, conn))
            using (OracleDataReader reader = cmd.ExecuteReader())
            {
                //根据查询字段数fieldcount创建headrow 及遍历字段创建cell    
                IRow headrow = sheet.CreateRow(0);
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    ICell cell = headrow.CreateCell(i);
                    cell.SetCellType(CellType.STRING);
                    cell.SetCellValue(reader.GetName(i));
                }
                //循环reader查询，每一条查询，创建row 及遍历字段创建cell
                int datarowIndex = 1; //数据行索引，从1开始
                while(reader.Read())
                {
                    IRow row = sheet.CreateRow(datarowIndex);
                    for (int i = 0; i < reader.FieldCount;i++ )
                    {
                        ICell cell = row.CreateCell(i);
                        cell.SetCellType(CellType.STRING);
                        cell.SetCellValue(reader.GetValue(i).ToString());
                    }
                    datarowIndex++;
                }
            }
            //关闭连接
            //写入stream
            //写入stream
            using (Stream stream = new FileStream("/Files/exportdataFromDB.xls", FileMode.OpenOrCreate, FileAccess.Write))
            {
                workbook.Write(stream);
            }
            MessageBox.Show("导出成功");
        }

        /// <summary>
        /// 从Excel导入  OracleBulkCopy大数据导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImportFromExcel_Click(object sender, EventArgs e)
        {
            //创建datatable
            DataTable dt = new DataTable();
            Type type = typeof(T_CUSTOMER);
            PropertyInfo[] props = type.GetProperties();
            DataColumn[] dcArr = new DataColumn[props.Length];
            int j=0;
            foreach(PropertyInfo prop in props)
            {
                string propName = prop.Name;
                Type propTy = prop.PropertyType; //属性的类型
                DataColumn dc = new DataColumn();
                dc.ColumnName = propName;
                dcArr[j] = dc; //把表的列 存入数组
                dc.DataType = propTy; //列中数据的类型
                dt.Columns.Add(dc);
                j++;
            }
            //读取Excel文件,获得stream
            using (Stream stream = new FileStream("/Files/exportdata.xls", FileMode.Open, FileAccess.Read))
            {
                //获得workbook
                IWorkbook workbook = new HSSFWorkbook(stream);
                //读取sheet
                ISheet sheet = workbook.GetSheetAt(0);
                //读取row 及行中的cell 放入一个datatable
                for (int i = 1; i < sheet.LastRowNum;i++ )
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dr = dt.NewRow();
                    foreach(ICell cell in row.Cells)
                    {
                        if (dcArr[i-1].DataType.Name=="Int32")
                        {
                            dr[dcArr[i - 1]] = (Int32)cell.NumericCellValue; //还可能需 Convert.ToInt32
                        }
                        else if (dcArr[i - 1].DataType.Name == "String")
                        {
                            dr[dcArr[i - 1]] = cell.StringCellValue;
                        }
                        else if (dcArr[i - 1].DataType.Name == "DateTime?")
                        {
                            dr[dcArr[i - 1]] = (DateTime?)cell.DateCellValue;
                        }
                        else
                        {
                            throw new Exception("未知类型:" + cell.CellType);
                        }
                    }
                    dt.Rows.Add(dr); //把表的行加入表的行集合中,最终获得表
                }
            }
             //OracleBulk
            using (OracleBulkCopy bulkCopy = new OracleBulkCopy(OracleHelper.CreateConnection()))
            {
                bulkCopy.DestinationTableName = "T_CUSTOMER";
                foreach (DataColumn dc in dcArr)
                {
                    string columnName = dc.ColumnName;
                    bulkCopy.ColumnMappings.Add(columnName, columnName);
                }
                bulkCopy.WriteToServer(dt);
            }
            //把数据表插入DB
        }


        /// <summary>
        /// 从Execel直接导入DB
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnImportToDBFromExcel_Click(object sender, EventArgs e)
        {
            //读取excel
            using (Stream stream = new FileStream("/Files/exportdataFromDB.xls", FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(stream);
                //获得sheet
                ISheet sheet = workbook.GetSheetAt(0);
                
                //行headrow 去除首行 获得含有':'列名数组
                IRow headrow = sheet.GetRow(0);
                string[] columnNameArr = new string[headrow.LastCellNum-1];
                for (int i = 1; i < headrow.LastCellNum;i++ )
                {
                    columnNameArr[i - 1] = ":" + headrow.Cells[i].StringCellValue;
                }
                StringBuilder sb=new StringBuilder();
                sb.Append("INSERT INTO T_CUSTOMER VALUES(SE_T_CUSTOMER.NEXTVAL,").Append(string.Join(",",columnNameArr)).AppendLine(")");
                //遍历行row 去除首行 获得参数数组
                for (int j = 1; j < sheet.LastRowNum;j++ )
                {
                    IRow row = sheet.GetRow(j);
                    //除0行 的每一行都有一个参数数组，里面有各个cell值的参数
                    //声明一个参数数组，并对其中的灭一个参数赋值
                    OracleParameter[] paraArr = new OracleParameter[row.LastCellNum - 1]; { for (int i = 0; i < paraArr.Length;i++ ) { paraArr[i] = new OracleParameter(); } } 
                    for (int k = 1; k < row.LastCellNum; k++)
                    {
                        ICell cell = row.Cells[k];
                        paraArr[k - 1].ParameterName = columnNameArr[k - 1];
                        paraArr[k - 1].Value = (object)cell.StringCellValue; //判断 不需要
                    }
                    //除0每一行 都执 /插入DB
                    OracleHelper.ExecuteNonQuery(sb.ToString(), paraArr);
                }
            }
        }