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;        }