Excel导入导出提示代码

导出到Excel

  HSSFWorkbook hssfworkbook = new HSSFWorkbook();//工作簿
            ISheet sheet1 = hssfworkbook.CreateSheet("第一页");//页
            IRow rowHeader = sheet1.CreateRow(0);//行
            rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名");
            rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工资");
            rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职时间");
            rowHeader.CreateCell(3, CellType.STRING).SetCellValue("出生日期");
            SaveFileDialog openFile = new SaveFileDialog();
            openFile.Filter = "Excet文件|*.xls|07Excel|*..xlsx";
            if(openFile .ShowDialog ()==true)
            {
                using (Stream stream= File.OpenWrite(openFile .FileName ))
                {
                    //List<Employee> employee = (List<Employee>)datagrid.ItemsSource;
                    Employee[] employeeList = (Employee[])datagrid.ItemsSource; ;
                    //employeeList = employee.ToArray();
                    for (int i = 0; i < employeeList.Count(); i++)
                    {
                        Employee employee1 = employeeList [i];
                        IRow rowContent=sheet1 .CreateRow (i+1);
                        rowContent.CreateCell(0, CellType.STRING).SetCellValue(employee1.Name);
                        rowContent.CreateCell(1, CellType.STRING).SetCellValue(employee1.BaseSalary );
                        //数据量太大 导致下面无法执行
                        //ICellStyle styleDate = hssfworkbook.CreateCellStyle();
                        //IDataFormat format = hssfworkbook.CreateDataFormat();
                        //styleDate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\"");
                        //ICell cellInDate = rowContent.CreateCell(2, CellType.NUMERIC);
                        //cellInDate.CellStyle = styleDate;
                        //cellInDate.SetCellValue(employee1.InDate);
                        //ICell cellBirthday = rowContent.CreateCell(3, CellType.NUMERIC);
                        //cellBirthday.CellStyle = styleDate;
                        //cellBirthday.SetCellValue(employee1.BirthDay);
                    }
                    hssfworkbook.Write(stream);
                }

导入Excel，其中出现类型无法转换问题，如bool和GUID转换时报错，只提供思路

  HSSFWorkbook hssfworkbook;
        //创建一个Excel簿，并读入一个文件流
        public void InitializeWorkbook(string path)
        {
            using (FileStream file=new FileStream (path ,FileMode .Open ,FileAccess .Read ))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
        }
        //将Excel表转换成DataTable表
        public DataTable ConverToDataTable()
        {
            ISheet sheet = hssfworkbook.GetSheetAt(0);//获取Excel簿的页
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable table = new DataTable("T_Operator");
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr = table.NewRow();
                for (int j = 0; j < row.LastCellNum; j++) 
                { 
                    table.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        switch (cell.CellType)
                        {
                            case CellType .BLANK:
                                dr[i] = "[null]"; 
                                break;
                            case CellType.BOOLEAN:
                                dr[i] = cell.BooleanCellValue;
                                break;
                            case CellType.NUMERIC: 
                                dr[i] = cell.ToString();    //This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number.
                                break;
                            case CellType.STRING: 
                                dr[i] = cell.StringCellValue; 
                                break;
                            case CellType.ERROR:
                                dr[i] = cell.ErrorCellValue; 
                                break;
                            case CellType.FORMULA:
                            default: 
                            dr[i] = "=" + cell.CellFormula;
                            break;
                        }
                        //dr[i] = cell;
                    }
                }
                table.Rows.Add(dr);
            }
            return table;
        }
        private void btnImpot_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Filter = "Excel文件|.xls|07Excel|.xlsx|*|*.*";
            if (openfile.ShowDialog() == true)
            {
                InitializeWorkbook(openfile.FileName);
                DataTable table = ConverToDataTable();
                foreach (DataRow dr in table.Rows)
                {
                    Operator op = ToOperator (dr);
                    OperatorDAL opDAL = new OperatorDAL();
                    opDAL.Insert(op);
                }                
            }
        }
        private Operator ToOperator(DataRow row)
        {
            Operator op = new Operator();
            //op.Id = (Guid)row[0];
            op.UserName = (string)row[1];
            op.Password = (string)row[2];
            op.IsDeleted = (bool)row[3];
            op.RealName = (string)row[4];
            op.IsLocked = (bool)row[5];
            return op;
        }