DevExpress spreadsheetControl导入

1，在工具箱拖一个spreadsheetControl，效果图：

2，窗体加载时添加列名：

        private void Form1_Load(object sender, EventArgs e)
        {
            //using DevExpress.Spreadsheet;
            sheet = spreadsheetControl1.ActiveWorksheet.Workbook.Worksheets[0];
            sheet.Cells["A1"].Value = "编号";
            sheet.Cells["B1"].Value = "姓名";
            sheet.Cells["C1"].Value = "性别";
            sheet.Cells["D1"].Value = "年龄";
            sheet.Cells["E1"].Value = "地址";
            sheet.Cells["F1"].Value = "合并单元格";

            //单元格填充颜色
            sheet.Cells["A1"].FillColor = Color.Red;

            //获取区域、设置样式
            Range range = sheet.Range["A1:E1"];
            range.RowHeight = 110;
            range.Font.Bold = true;
            range.Font.Size = 16;
            range.Font.Color = System.Drawing.Color.Black;
            range.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
            //设置边框
            Borders border = range.Borders;
            range.Borders.SetOutsideBorders(DevExpress.Utils.DXColor.Black, BorderLineStyle.Thin);
            range.Borders.SetAllBorders(DevExpress.Utils.DXColor.Black, BorderLineStyle.Thin);
            //单元格合并
            Range range2 = sheet.Range["F1:H2"];
            range2.Merge();

        }

效果图：

3，录入测试数据，保存，在保存数据之前还是需要做一些校验的，比如列名是否合法，输入的字符格式是否正确。

 private void simpleButton1_Click(object sender, EventArgs e)
        {
            try
            {
                List<Model.UserInfo> UserList = new List<Model.UserInfo>();
                Model.UserInfo Info = null;
                for (int i = 1; i < sheet.Cells.CurrentRegion.RowCount; i++)
                {
                    Info = new Model.UserInfo();
                    Info.Id = sheet.Cells[i, 0].Value.ToString() == "" ? null : (int?)Convert.ToInt32(sheet.Cells[i, 0].Value.ToString());
                    Info.Name = sheet.Cells[i, 1].Value.ToString();
                    Info.Sex = sheet.Cells[i, 2].Value.ToString();
                    Info.Age = sheet.Cells[i, 3].Value.ToString() == "" ? null : (int?)Convert.ToInt32(sheet.Cells[i, 3].Value.ToString());
                    Info.Address = sheet.Cells[i, 4].Value.ToString();
                    UserList.Add(Info);
                }
                //保存至数据库
                DevExpress.XtraEditors.XtraMessageBox.Show(this, "成功导入:" + UserList.Count.ToString() + "条!", "消息");
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(this, ex.Message, "消息");
            }
        }

4，加载数据到工作簿有两种方式，方式一是获取Sheet然后赋值（如上），方式二是加载指定路径的Excel,一般用于比较复杂的表格的导入导出。

   //DevExpress.XtraSpreadsheet.SpreadsheetControl spreadsheetControl1 = new DevExpress.XtraSpreadsheet.SpreadsheetControl();
   //spreadsheetControl1.LoadDocument(ExcelUrl, DocumentFormat.Xlsx);