1-) C# RMOS - Gerçek Excel Yükleme ve Kaydetme real excel upload and save xlsx spreadsheetControl1 kullanımı
ilk önce bu iki kütüphaneyi import et
using DevExpress.Spreadsheet;
using Worksheet = DevExpress.Spreadsheet.Worksheet;
1-) Excel Yükleme ve Cell'ine veri yazma
public void excelYukleVeExceleVeriYaz() { spreadsheetControl1.LoadDocument(@"C:\cc\Anakart.xlsx", DocumentFormat.Xlsx); spreadsheetControl1.Document.BeginUpdate(); // hızlı yazması için for (int i = 2; i < 1000; i++) { spreadsheetControl1.ActiveWorksheet.Cells["B" + i].Value = "B" + i; spreadsheetControl1.ActiveWorksheet.Cells["A" + i].Value = "A" + i; } spreadsheetControl1.Document.EndUpdate(); //spreadsheetControl1.SaveDocument(); // kaydetme }
|
2-) Sayfaları almak için
Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; // ilk sayfa demek worksheet.Cells["B2"].Value = "ramazan";
Worksheet worksheet = spreadsheetControl1.Document.Worksheets["Sayfa1"]; // isim ile sayfa alma
|
3-) Excel to Datatable
public DataTable excelToDatatable() { spreadsheetControl1.LoadDocument(@"C:\cc\Anakart.xlsx", DocumentFormat.Xlsx); Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; // isim ile sayfa alma var range = worksheet.GetUsedRange(); bool rangeHasHeaders = true; DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders); DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true); exporter.CellValueConversionError += exporter_CellValueConversionError; MyConverter myconverter = new MyConverter(); myconverter.EmptyCellValue = "N/A"; exporter.Options.ConvertEmptyCells = true; exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false; exporter.Export();
return dataTable; }
void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e) { MessageBox.Show("Error in cell " + e.Cell.GetReferenceA1()); e.DataTableValue = null; e.Action = DataTableExporterAction.Continue; } |
MyConverter.cs
public class MyConverter : ICellValueToColumnTypeConverter { public bool SkipErrorValues { get; set; } public CellValue EmptyCellValue { get; set; }
public ConversionResult Convert(Cell readOnlyCell, CellValue cellValue, Type dataColumnType, out object result) { result = DBNull.Value; ConversionResult converted = ConversionResult.Success; if (cellValue.IsEmpty) { result = EmptyCellValue; return converted; } if (cellValue.IsError) { // You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled. //return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error; result = "N/A"; return ConversionResult.Success; } result = String.Format("{0:MMMM-yyyy}", cellValue.DateTimeValue); return converted; } } |