/
Form1.cs
49 lines (42 loc) · 2.02 KB
/
Form1.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
using System.Data;
using System.Windows.Forms;
namespace ExportToDataTableWorkbookExample {
public partial class Form1 : DevExpress.XtraBars.Ribbon.RibbonForm {
DataTable ds;
public Form1() {
InitializeComponent();
}
private void barBtnExport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) {
if (ds != null) return;
#region #exportdatatable
Workbook wbook = new Workbook();
wbook.LoadDocument("TopTradingPartners.xlsx");
Worksheet worksheet = wbook.Worksheets[0];
Range range = worksheet.Tables[0].Range;
DataTable dataTable = worksheet.CreateDataTable(range, true);
// Change the data type of the "As Of" column to text.
dataTable.Columns["As Of"].DataType = System.Type.GetType("System.String");
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
exporter.CellValueConversionError += exporter_CellValueConversionError;
MyConverter myconverter = new MyConverter();
exporter.Options.CustomConverters.Add("As Of", myconverter);
// Set the export value for empty cell.
myconverter.EmptyCellValue = "N/A";
exporter.Options.ConvertEmptyCells = true;
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false;
exporter.Export();
#endregion #exportdatatable
ds = dataTable;
gridControl1.DataSource = ds;
}
#region #CellValueConversionError
void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e) {
MessageBox.Show("Error in cell " + e.Cell.GetReferenceA1());
e.DataTableValue = null;
e.Action = DataTableExporterAction.Continue;
}
#endregion #CellValueConversionError
}
}