using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;


//打开Excel文件
//SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(openFileDialog.FileName, System.Globalization.CultureInfo.CurrentCulture);
spreadsheetControl1.LoadDocument(openFileDialog.FileName, DocumentFormat.Xls);


//从流中读取数据
var stream = new MemoryStream();
gridView1.ExportToXls(stream, true); //生成流
//var workbook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromStream(stream);
Workbook workbook = new Workbook();
workbook.LoadDocument(stream, DocumentFormat.Xls);


//保存Excel文件
//workbook.SaveAs(saveFileDialog.FileName, SpreadsheetGear.FileFormat.XLS97);
workbook.SaveDocument(saveFileDialog.FileName, DocumentFormat.Xls);


//新建Workbook和Worksheet
//SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
//SpreadsheetGear.IWorksheet workSheet = workbook.Worksheets[0];
//workSheet.Name = dtSource.TableName;
Workbook workbook = new Workbook();
Worksheet workSheet = workbook.Worksheets.Add(dtSource.TableName);	


//将DataTable填到Cell中
//SpreadsheetGear.IRange range = workSheet.Cells["A2"];
//range.CopyFromDataTable(dtSource, SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders);
workSheet.Import(dtSource, false, 1, 0);


//数据导出到DataTable
//var dataSet = workbook.GetDataSet(SpreadsheetGear.Data.GetDataFlags.FormattedText |
//                        SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
Worksheet worksheet = workbook.Worksheets[0];
Range range = worksheet.GetUsedRange();
bool rangeHasHeaders = false;
DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders); 
//务必添加引用DevExpress.Docs.v13.2.dll
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, ataTable, rangeHasHeaders);
exporter.Export();


//设置Cell值和格式
//workSheet.Cells[0, index].Columns.AutoFit();
//workSheet.Cells[0, index].Interior.Color = Color.Gray;
//workSheet.Cells[0, index].Borders.LineStyle = SpreadsheetGear.LineStyle.Continuous;
workSheet.Columns.AutoFit(0, dtSource.Columns.Count); 
workSheet.Cells[0, index].FillColor = Color.Gray; 
workSheet.Cells[0, index].Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin);