private void barButtonItemUseExporterOptions_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { #region #DataExportWithOptions Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; Range range = worksheet.Tables[0].Range; // Create a data table with column names obtained from the first row in a range. // Column data types are obtained from cell value types of cells in the first data row of the worksheet range. DataTable dataTable = worksheet.CreateDataTable(range, true); // Create the exporter that obtains data from the specified range which has a header row and populates the previously created data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true); // Handle value conversion errors. exporter.CellValueConversionError += exporter_CellValueConversionError; // Specify exporter options. exporter.Options.ConvertEmptyCells = true; exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0; exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = barCheckItemSkipErrors.Checked; // Perform the export. exporter.Export(); #endregion #DataExportWithOptions // A custom method that displays the resulting data table. ShowResult(dataTable); }
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]; CellRange 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; }
public static DataTable SimpleDataExport(Range range, bool rangeHasHeaders) { #region #SimpleDataExport Worksheet worksheet = range.Worksheet; // Create a data table with column names obtained from the first row in a range if it has headers. // Column data types are obtained from cell value types of cells in the first data row of the worksheet range. DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders); //Validate cell value types. If cell value types in a column are different, the column values are exported as text. for (int col = 0; col < range.ColumnCount; col++) { CellValueType cellType = range[0, col].Value.Type; for (int r = 1; r < range.RowCount; r++) { if (cellType != range[r, col].Value.Type) { dataTable.Columns[col].DataType = typeof(string); break; } } } // Create the exporter that obtains data from the specified range, // skips the header row (if required) and populates the previously created data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders); // Handle value conversion errors. exporter.CellValueConversionError += exporter_CellValueConversionError; // Perform the export. exporter.Export(); #endregion #SimpleDataExport // A custom method that displays the resulting data table. return(dataTable); }
private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0]; Range range = worksheet.Tables[0].Range; // Create a data table with column names obtained from the first row in a range. // Column data types are obtained from cell value types of cells in the first data row of the worksheet 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"); // Create the exporter that obtains data from the specified range, //skips header row if required and populates the specified data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true); // Specify a custom converter for the "As Of" column. DateTimeToStringConverter toDateStringConverter = new DateTimeToStringConverter(); exporter.Options.CustomConverters.Add("As Of", toDateStringConverter); // Set the export value for empty cell. toDateStringConverter.EmptyCellValue = "N/A"; // Specify that empty cells and cells with errors should be processed. exporter.Options.ConvertEmptyCells = true; exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false; // Perform the export. exporter.Export(); // A custom method that displays the resulting data table. ShowResult(dataTable); }
private void barButtonItem1_ItemClick(Range range) { Worksheet worksheet = range.Worksheet; // Create a data table with column names obtained from the first row in a range. // Column data types are obtained from cell value types of cells in the first data row of the worksheet 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"); // Create the exporter that obtains data from the specified range and populates the specified data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true); // Handle value conversion errors. exporter.CellValueConversionError += exporter_CellValueConversionError; // Specify a custom converter for the "As Of" column. DateTimeToStringConverter toDateStringConverter = new DateTimeToStringConverter(); exporter.Options.CustomConverters.Add("As Of", toDateStringConverter); // Set the export value for empty cell. toDateStringConverter.EmptyCellValue = "N/A"; // Specify that empty cells and cells with errors should be processed. exporter.Options.ConvertEmptyCells = true; exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false; // Perform the export. exporter.Export(); // A custom method that displays the resulting data table. //ShowResult(dataTable); }
private void ExportSelectionStopOnEmptyRow() { #region #StopExportOnEmptyRow Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet; Range range = worksheet.Selection; // Determine whether the first row in a range contains headers. bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked; // Determine whether an empty row must stop conversion. bool stopOnEmptyRow = barCheckItemStopEmptyRow.Checked; // Create a data table with column names obtained from the first row in a range if it has headers. // Column data types are obtained from cell value types of cells in the first data row of the worksheet range. DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders); // Create the exporter that obtains data from the specified range, // skips the header row (if required) and populates the previously created data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders); // Handle value conversion errors. exporter.CellValueConversionError += (sender, args) => { args.Action = DataTableExporterAction.Continue; }; if (stopOnEmptyRow) { exporter.Options.SkipEmptyRows = false; // Handle empty row. exporter.ProcessEmptyRow += (sender, args) => { args.Action = DataTableExporterAction.Stop; }; } // Perform the export. exporter.Export(); #endregion #StopExportOnEmptyRow // A custom method that displays the resulting data table. ShowResult(dataTable); }
public static DataTable ExportUseExporterOptions(Range range, bool rangeHasHeaders, bool skipErrorValues) { #region #DataExportWithOptions Worksheet worksheet = range.Worksheet; // Create a data table with column names obtained from the first row in a range. // Column data types are obtained from cell value types of cells in the first data row of the worksheet range. DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders); // Create the exporter that obtains data from the specified range which has a header row and populates the previously created data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders); // Handle value conversion errors. exporter.CellValueConversionError += exporter_CellValueConversionError; // Specify exporter options. exporter.Options.ConvertEmptyCells = true; exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0; exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = skipErrorValues; // Perform the export. exporter.Export(); #endregion #DataExportWithOptions // A custom method that displays the resulting data table. return(dataTable); }
public FileResult ExportInventoryListToExcel(InventoryListModel model) { var queryInfo = new InventoryListQueryInfo(); queryInfo.SupplierID = base.CurrentUser.SupplierID; queryInfo.ProductName = model.ProductName.SafeTrim(); queryInfo.Spu = model.Spu.SafeTrim(); queryInfo.Sku = model.Sku.SafeTrim(); queryInfo.BarCode = model.BarCode.SafeTrim(); queryInfo.SkuStatus = model.SkuStatus; queryInfo.IsLowStockAlarm = model.IsLowStockAlarm; var fileName = "Inventory" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"; try { var dateTable = bll.GetSkuInventoryListData(queryInfo, LanguageEnum.TraditionalChinese); var dtExporter = new DataTableExporter(EnumExcelType.XLSX); dtExporter.AddTable <InventoryListExportModel>(dateTable, "SKU庫存"); return(XlsxFile(dtExporter.Export(), fileName)); } catch (Exception ex) { LogHelper.Error(ex); } return(XlsxFile(new byte[0], fileName)); }
public DataTable GetTableFromExcel() { Workbook book = new Workbook(); book.LoadDocument(HomeControllerUploadControlSettings.resultFilePath); Worksheet sheet = book.Worksheets.ActiveWorksheet; CellRange range = sheet.GetUsedRange(); DataTable table = sheet.CreateDataTable(range, false); DataTableExporter exporter = sheet.CreateDataTableExporter(range, table, false); exporter.CellValueConversionError += exporter_CellValueConversionError; exporter.Export(); return(table); }
private DataTable GetTableFromExcel() { Workbook book = new Workbook(); book.InvalidFormatException += book_InvalidFormatException; book.LoadDocument(FilePath); Worksheet sheet = book.Worksheets.ActiveWorksheet; CellRange range = sheet.GetUsedRange(); DataTable table = sheet.CreateDataTable(range, false); DataTableExporter exporter = sheet.CreateDataTableExporter(range, table, false); exporter.CellValueConversionError += exporter_CellValueConversionError; exporter.Export(); return(table); }
public FileResult ExportInventoryListToExcel(InventoryListViewModel Model) { var fileName = "Inventory" + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"; try { var dateTable = productBll.GetProductInventoryListData(Model, LanguageEnum.TraditionalChinese); var dtExporter = new DataTableExporter(EnumExcelType.XLSX); dtExporter.AddTable <InventoryListExportModel>(dateTable, "SKU庫存"); return(XlsxFile(dtExporter.Export(), fileName)); } catch (Exception ext) { LogHelper.Error(ext); } return(XlsxFile(new byte[0], fileName)); }
private void barButtonItemRangeToDataTable_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { #region #SimpleDataExport Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet; Range range = worksheet.Selection; bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked; // Create a data table with column names obtained from the first row in a range if it has headers. // Column data types are obtained from cell value types of cells in the first data row of the worksheet range. DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders); // Create the exporter that obtains data from the specified range, // skips the header row (if required) and populates the specified data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders); // Perform the export. exporter.Export(); #endregion #SimpleDataExport // A custom method that displays the resulting data table. ShowResult(dataTable); }
private void barButtonItemRangeToDataTable_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e) { if (barCheckItemStopEmptyRow.Checked) { ExportSelectionStopOnEmptyRow(); return; } #region #SimpleDataExport Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet; Range range = worksheet.Selection; bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked; // Create a data table with column names obtained from the first row in a range if it has headers. // Column data types are obtained from cell value types of cells in the first data row of the worksheet range. DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders); //Validate cell value types. If cell value types in a column are different, the column values are exported as text. for (int col = 0; col < range.ColumnCount; col++) { CellValueType cellType = range[0, col].Value.Type; for (int r = 1; r < range.RowCount; r++) { if (cellType != range[r, col].Value.Type) { dataTable.Columns[col].DataType = typeof(string); break; } } } // Create the exporter that obtains data from the specified range, // skips the header row (if required) and populates the previously created data table. DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders); // Handle value conversion errors. exporter.CellValueConversionError += exporter_CellValueConversionError; // Perform the export. exporter.Export(); #endregion #SimpleDataExport // A custom method that displays the resulting data table. ShowResult(dataTable); }
public void ParseExcelFileForData() { if (string.IsNullOrWhiteSpace(model.bindingProperty.ExcelFilePath)) { XtraMessageBox.Show("Please provide the file path.", "Excel Parse Error"); model.bindingProperty.AllowNext = false; } else { try { using (Workbook workbook = new Workbook()) { workbook.LoadDocument(model.bindingProperty.ExcelFilePath); Worksheet worksheet = workbook.Worksheets[0]; Range range = worksheet.GetDataRange(); DataTable exportDataTable = worksheet.CreateDataTable(range, model.bindingProperty.FirstRowHasHeaders); DataTableExporter exporter = worksheet.CreateDataTableExporter(range, exportDataTable, model.bindingProperty.FirstRowHasHeaders); exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false; exporter.Options.ConvertEmptyCells = true; exporter.Export(); model.bindingProperty.ExcelDataTable = exportDataTable; model.bindingProperty.AllowNext = true; } var columnNames = model.GetExcelDataColumnNames(); if (columnNames.Contains(string.Empty)) { XtraMessageBox.Show("One or more column does not contains column name.", "Excel Parse Error"); model.bindingProperty.AllowNext = false; } } catch (Exception ex) { XtraMessageBox.Show(ex.Message, "Excel Parse Error"); model.bindingProperty.AllowNext = false; } } }
public static DataTable ExportRangeStopOnEmptyRow(Range range, bool rangeHasHeaders, bool stopOnEmptyRow) { // Create a data table with column names obtained from the first row in a range if it has headers. // Column data types are obtained from cell value types of cells in the first data row of the worksheet range. DataTable dataTable = range.Worksheet.CreateDataTable(range, rangeHasHeaders); // Create the exporter that obtains data from the specified range, // skips the header row (if required) and populates the previously created data table. DataTableExporter exporter = range.Worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders); // Handle value conversion errors. exporter.CellValueConversionError += (sender, args) => { args.Action = DataTableExporterAction.Continue; }; if (stopOnEmptyRow) { exporter.Options.SkipEmptyRows = false; // Handle empty row. exporter.ProcessEmptyRow += (sender, args) => { args.Action = DataTableExporterAction.Stop; }; } // Perform the export. exporter.Export(); // A custom method that displays the resulting data table. return(dataTable); }
private void MedStuffUpdateButton_Click(object sender, RoutedEventArgs e) { medStuffUpdateButton.IsEnabled = false; dtDb.Clear(); excelDataTable.Clear(); changesTable.Clear(); #region File choise OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "XLSX-files|*.xlsx"; openFileDialog.ShowDialog(); #endregion #region Range search Workbook wbook = new Workbook(); if (openFileDialog.FileName == "") { System.Windows.MessageBox.Show("Выберите файл", "", MessageBoxButton.OK, MessageBoxImage.Warning); } else { fileName = Path.GetFileNameWithoutExtension(openFileDialog.FileName); wbook.LoadDocument(openFileDialog.FileName); DevExpress.Spreadsheet.Worksheet worksheet = wbook.Worksheets[0]; int lastRow = 0; while (!worksheet.Cells[lastRow, 1].Value.IsEmpty) { lastRow++; } //var range = worksheet.Tables[0].Range; var range = worksheet.Range[$"A1:D{lastRow}"].CurrentRegion; #endregion #region Excel datatable definition //excelDataTable = worksheet..CreateDataTable(range, true); ////excelDataTable.Columns[1].DataType = System.Type.GetType("System.String"); //excelDataTable.Columns[0].ColumnName = "numberrrr"; //excelDataTable.Columns[1].ColumnName = "code"; //excelDataTable.Columns[2].ColumnName = "name"; //excelDataTable.Columns[3].ColumnName = "cost"; #endregion DataTableExporter exporter = worksheet.CreateDataTableExporter(range, excelDataTable, 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(); //grid.ItemsSource = excelDataTable; //grid.Visibility = Visibility.Visible; //DataTable dtDb = new DataTable(); //DataTable changesTable = new DataTable(); #region Datatable with changes definition //if (changesTable.Columns.Count < 1) //{ // changesTable.Columns.Add("code"); // changesTable.Columns.Add("old_cost"); // changesTable.Columns.Add("new_cost"); // changesTable.Columns.Add("old_name"); // changesTable.Columns.Add("new_name"); // changesTable.Columns.Add("file_n"); // changesTable.Columns.Add("difference"); //} #endregion //string connStr = @"Data Source=DESKTOP-N6R6KEU\SQLEXPRESS;Initial Catalog=STD_DB_DICT_HIVE;Integrated Security=True"; #region Connection to DB if (INI.KeyExists("Source", "DB_Connection") && INI.KeyExists("Catalog", "DB_Connection")) { try { string connStr = $"Data Source={INI.ReadINI("DB_Connection", "Source")};Initial Catalog={INI.ReadINI("DB_Connection", "Catalog")};Integrated Security=True"; using (SqlConnection connection = new SqlConnection(connStr)) { SqlDataAdapter adapter = new SqlDataAdapter(); //SqlDataAdapter adapter2 = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand("SELECT * FROM med_stuf", connection); adapter.Fill(dtDb); adapter.SelectCommand = new SqlCommand("SELECT * FROM log.med_stuf", connection); adapter.Fill(changesTable); changesTable.Clear(); if (changesTable.Columns.Count < 9) { changesTable.Columns.Add("difference"); } //gridControl1.DataSource = dtDb; #region Filling datatable with changes foreach (DataRow newDataRow in excelDataTable.Rows) { if (!newDataRow["code"].Equals(DBNull.Value)) { if (!newDataRow["name"].Equals(DBNull.Value)) { foreach (DataRow dbRow in dtDb.Select($"code={newDataRow["code"]}")) { object oldPrice = DBNull.Value; object oldName = DBNull.Value; object newPrice = DBNull.Value; object newName = DBNull.Value; if (!dbRow["cost"].Equals(newDataRow["cost"])) { oldPrice = dbRow["cost"]; dbRow["cost"] = newDataRow["cost"]; newPrice = newDataRow["cost"]; } if (!dbRow["name"].Equals(newDataRow["name"])) { oldName = dbRow["name"]; dbRow["name"] = newDataRow["name"]; newName = newDataRow["name"]; } if (!oldName.Equals(newName) || !oldPrice.Equals(newPrice)) { changesTable.Rows.Add(new Object[] { newDataRow["code"], oldPrice, newPrice, oldName, newName, fileName, DBNull.Value, DBNull.Value, Convert.ToDouble(newPrice) - Convert.ToDouble(oldPrice) }); } } } else { System.Windows.MessageBox.Show($"Наименования услуг в файле {openFileDialog.FileName} должны быть заполнены", "Ошибка!", MessageBoxButton.OK, MessageBoxImage.Error); changesTable.Clear(); } } else { System.Windows.MessageBox.Show($"Коды услуг в файле {openFileDialog.FileName} должны быть заполнены", "Ошибка!", MessageBoxButton.OK, MessageBoxImage.Error); changesTable.Clear(); } } #endregion if (changesTable.Rows.Count > 0) { grid.ItemsSource = changesTable; grid.Visibility = Visibility.Visible; OkButton.IsEnabled = true; CancelButton.IsEnabled = true; } else { System.Windows.MessageBox.Show($"В файле {openFileDialog.FileName} нет изменений", "Обновление", MessageBoxButton.OK, MessageBoxImage.Information); medStuffUpdateButton.IsEnabled = true; } }//connection } catch (Exception) { System.Windows.MessageBox.Show("Проверьте параметры подключения к базе данных"); medStuffUpdateButton.IsEnabled = true; } } else { System.Windows.MessageBox.Show("Проверьте наличие файла конфигурации подключкния к БД", "Ошибка подключения к БД", MessageBoxButton.OK, MessageBoxImage.Error); } } #endregion }//mainButtonClick
void LoadGrid() { if (string.IsNullOrEmpty(file)) { var openFileDialog = UtilDisplay.LoadOpenFileDialog; openFileDialog.Multiselect = false; openFileDialog.Filter = "CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*.xls|XLSX Files (*.xlsx)|*.xlsx|TXT Files (*.txt)|*.txt |All files (*.*)|*.*"; bool?userClickedOK = openFileDialog.ShowDialog(); if (userClickedOK == true) { file = openFileDialog.FileName; } if (string.IsNullOrEmpty(file)) { return; } } string fileExtension = System.IO.Path.GetExtension(file); try { bool hasType; DataTable DataTable; if (fileExtension == ".csv") { hasType = false; updateDelimiter = true; DataTable = FromCsv(file); } else if (fileExtension == ".xls" || fileExtension == ".xlsx") { hasType = true; IWorkbook workBook = importSpreadSheet.Document; workBook.LoadDocument(file); DevExpress.Spreadsheet.Worksheet worksheet = importSpreadSheet.Document.Worksheets[0]; var range = worksheet.GetUsedRange(); var dataTable = worksheet.CreateDataTable(range, true); for (int col = 0; col < range.ColumnCount; col++) { CellValueType cellType = range[0, col].Value.Type; for (int r = 1; r < range.RowCount; r++) { if (cellType != range[r, col].Value.Type) { dataTable.Columns[col].DataType = typeof(string); break; } } } DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true); exporter.Export(); DataTable = exporter.DataTable; } else if (fileExtension == ".txt") { hasType = false; DataSet theDataSet = new DataSet(); theDataSet.ReadXml(file); DataTable = theDataSet.Tables[0]; } else { file = string.Empty; return; } dgBankStmt.ItemsSource = DataTable; customDataColumnSource = CreateCustomDataColumn(DataTable, hasType).ToList(); dgBankStmt.ColumnsSource = customDataColumnSource; listBoxProperties.ItemsSource = customDataColumnSource; } catch (Exception ex) { UnicontaMessageBox.Show(ex); } }
private void medStufButton_Click(object sender, EventArgs e) { int changeCostCounter = 0; int changeNameCounter = 0; int totalChangeCounter = 0; int newCodeCounter = 0; int delCodeCounter = 0; int excelRowCounter; bool changeCost = false; medStufButton.Enabled = false; dtDb.Clear(); excelDataTable.Clear(); changesTable.Clear(); #region File choise OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "XLSX-files|*.xlsx"; openFileDialog.ShowDialog(); #endregion Workbook wbook = new Workbook(); if (openFileDialog.FileName == "") { MessageBox.Show("Выберите файл", "Ошибка выбора файла", MessageBoxButtons.OK, MessageBoxIcon.Error); medStufButton.Enabled = true; } else { fileName = System.IO.Path.GetFileNameWithoutExtension(openFileDialog.FileName); wbook.LoadDocument(openFileDialog.FileName); Worksheet worksheet = wbook.Worksheets[0]; excelRowCounter = 0; while (!worksheet.Cells[excelRowCounter, 1].Value.IsEmpty) { excelRowCounter++; } try { var range = worksheet.Range[$"A1:E{excelRowCounter}"].CurrentRegion; excelDataTable = worksheet.CreateDataTable(range, true); if (excelDataTable.Columns.Count == 5) { if (!excelDataTable.Columns[0].ColumnName.Equals("№ п.п.") || !excelDataTable.Columns[1].ColumnName.Equals("Код мед.изделия") || !excelDataTable.Columns[2].ColumnName.Equals("Наименование") || !excelDataTable.Columns[3].ColumnName.StartsWith("Цена за мед. изделие") || !excelDataTable.Columns[4].ColumnName.Equals("Действие"))// { throw new Exception("Структура файла отличается от ожидаемой"); } } else { throw new Exception("Структура файла отличается от ожидаемой"); } excelDataTable.Columns[3].DataType = System.Type.GetType("System.Double"); DataTableExporter exporter = worksheet.CreateDataTableExporter(range, excelDataTable, true); exporter.Options.ConvertEmptyCells = true; exporter.Export(); /* try * { * excelDataTable.PrimaryKey = new DataColumn[] { excelDataTable.Columns[1] }; * } * catch * { * * throw new Exception("Коды в файле не уникальны"); * } */ //для обесспечения уникальноти кодов в экселе excelDataTable.Columns[0].ColumnName = "numberrrr"; excelDataTable.Columns[1].ColumnName = "code"; excelDataTable.Columns[2].ColumnName = "name"; excelDataTable.Columns[3].ColumnName = "cost"; excelDataTable.Columns["cost"].DataType = System.Type.GetType("System.Double"); excelDataTable.Columns[4].ColumnName = "action"; #region Connection settings checking if (ini.KeyExists("Source", "DB_Connection") && ini.KeyExists("Catalog", "DB_Connection")) { connSource = ini.ReadINI("DB_Connection", "Source"); connCatalog = ini.ReadINI("DB_Connection", "Catalog"); try { string connStr = $"Data Source={connSource};Initial Catalog={connCatalog};Integrated Security=True"; using (SqlConnection connection = new SqlConnection(connStr)) { connection.Open(); SqlDataAdapter adapter = new SqlDataAdapter(); //SqlDataAdapter adapter2 = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand("SELECT * FROM dbo.med_stuf", connection); adapter.Fill(dtDb); // dtDb.Columns["cost"].DataType = System.Type.GetType("System.Single"); adapter.SelectCommand = new SqlCommand("SELECT * FROM dbo.med_stuf_temp", connection); adapter.Fill(changesTable); changesTable.Clear(); //if (changesTable.Columns.Count < 10) //{ // changesTable.Columns.Add("chng_knd"); // changesTable.Columns.Add("accept"); //} //gridControl1.DataSource = dtDb; #region Filling datatable with changes dtDb.PrimaryKey = new DataColumn[] { dtDb.Columns["code"] }; changesTable.PrimaryKey = new DataColumn[] { changesTable.Columns["code"] }; foreach (DataRow newDataRow in excelDataTable.Rows) { if (!newDataRow["code"].Equals(DBNull.Value)) { object oldPrice = DBNull.Value; object oldName = DBNull.Value; object newPrice = DBNull.Value; object newName = DBNull.Value; if (dtDb.Rows.Contains(newDataRow["code"])) { foreach (DataRow dbRow in dtDb.Select($"code={newDataRow["code"]}")) { object oldPriceNe = dbRow["cost"]; object oldNameNe = dbRow["name"]; object newPriceNe = newDataRow["cost"]; object newNameNe = newDataRow["name"]; if (!newDataRow["name"].Equals(DBNull.Value)) { if (!dbRow["cost"].Equals(newDataRow["cost"])) { oldPrice = dbRow["cost"]; dbRow["cost"] = newDataRow["cost"]; newPrice = newDataRow["cost"]; changeCostCounter++; totalChangeCounter++; changeCost = !changeCost; } //else //{ // oldPrice = newPrice = dbRow["cost"]; //} if (!dbRow["name"].Equals(newDataRow["name"])) { oldName = dbRow["name"]; dbRow["name"] = newDataRow["name"]; newName = newDataRow["name"]; if (!changeCost) { totalChangeCounter++; } changeNameCounter++; } if (!oldName.Equals(newName) || !oldPrice.Equals(newPrice)) { changeCost = !changeCost; changesTable.Rows.Add(new Object[] { DBNull.Value, newDataRow["code"], oldPrice, newPrice, oldName, newName, fileName, DBNull.Value, DBNull.Value, "изменение", true, oldPriceNe, newPriceNe, oldNameNe, newNameNe }); } if (!newDataRow["action"].Equals(DBNull.Value)) { delCodeCounter++; changesTable.Rows.Add(new Object[] { DBNull.Value, newDataRow["code"], dbRow["cost"], newPrice, dbRow["name"], newName, fileName, DBNull.Value, DBNull.Value, "удаление", true, oldPriceNe, newPriceNe, oldNameNe, newNameNe }); } }//if name!=0 else { if (!newDataRow["action"].Equals(DBNull.Value)) { delCodeCounter++; changesTable.Rows.Add(new Object[] { DBNull.Value, newDataRow["code"], dbRow["cost"], newPrice, dbRow["name"], newName, fileName, DBNull.Value, DBNull.Value, "удаление", true, oldPriceNe, newPriceNe, oldNameNe, newNameNe }); } else { MessageBox.Show($"Наименования услуг в файле \"{fileName}\" должны быть заполнены", "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error); changesTable.Clear(); } } //else name!=0 } //foreach dbRow } //contains else { if (!changesTable.Rows.Contains(newDataRow["code"])) { newPrice = newDataRow["cost"]; newName = newDataRow["name"]; changesTable.Rows.Add(new Object[] { DBNull.Value, newDataRow["code"], oldPrice, newPrice, oldName, newName, fileName, DBNull.Value, DBNull.Value, "добавление", true, DBNull.Value, newPrice, DBNull.Value, newName }); newCodeCounter++; } } //else contains } //if code not null else { MessageBox.Show($"Коды услуг в файле {openFileDialog.FileName} должны быть заполнены", "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error); changesTable.Clear(); } } #endregion if (changesTable.Rows.Count > 0) { SqlCommand command = connection.CreateCommand(); command.CommandText = "DELETE dbo.med_stuf_temp"; command.ExecuteNonQuery(); adapter.SelectCommand = new SqlCommand("SELECT * FROM dbo.med_stuf_temp", connection); SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter); adapter.Update(changesTable); changesTable.Clear(); adapter.Fill(changesTable); gridControl2.DataSource = changesTable; //dataGridResult//.Columns[6].Visibility = Visibility.Hidden; //gridView2.Columns[10].UnboundType=DataGridViewCheckBoxColumn //dataGridResult.Columns[7].Visibility = Visibility.Collapsed; //sqlDataSource1.Fill(); gridControl2.Visible = true; //buttonDBUpdate.IsEnabled = true; //buttonCancel.IsEnabled = true; MessageBox.Show($@"Записей в файле: {excelRowCounter - 1} Из них с изменениями: {totalChangeCounter} Изменений цены: {changeCostCounter} Изменений наименования: {changeNameCounter} Новых записей: {newCodeCounter} Записей на удаление: {delCodeCounter}", "Итого:", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show($"В файле \"{fileName}\" нет изменений", "Обновление", MessageBoxButtons.OK, MessageBoxIcon.Information); medStufButton.Enabled = true; } }//connection } catch (SqlException sqlEx) { MessageBox.Show($"Проверьте параметры подключения к базе данных {sqlEx.Message}"); medStufButton.Enabled = true; } } else { MessageBox.Show("Проверьте наличие файла конфигурации подключения к БД", "Ошибка подключения к БД", MessageBoxButtons.OK, MessageBoxIcon.Error); medStufButton.Enabled = true; } #endregion } catch (InvalidOperationException inOpEx) { MessageBox.Show($@"Ошибка структуры файла. {inOpEx.Message}", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); medStufButton.Enabled = true; } catch (Exception ex) { MessageBox.Show(ex.Message, "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } }//else от наличия файла в openFileDialogе updateButton.Enabled = true; cansButton.Enabled = true; }//medStufButton
public virtual void Import() { Control targetControl = FocusedGrid(); if (targetControl == null) { return; } if (targetControl.GetType().Equals(typeof(GridControl))) { try { GridControl gd = (GridControl)targetControl; GridView abv = (GridView)gd.MainView; DataTable sdt = (DataTable)gd.DataSource; OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "Excel|*.xls;*.xlsx"; if (openFileDialog.ShowDialog() != DialogResult.Cancel) { SharedFunc.ShowProcess(this, 0, 5); SpreadsheetControl spreadsheetControl = new SpreadsheetControl(); spreadsheetControl.LoadDocument(openFileDialog.FileName); SharedFunc.ShowProcess(1); Worksheet worksheet = spreadsheetControl.Document.Worksheets.ActiveWorksheet; CellCollection cellCollection = worksheet.Cells; DataTable idt = worksheet.CreateDataTable(cellCollection.CurrentRegion, true); //务必添加引用DevExpress.Docs.v16.2.dll DataTableExporter exporter = worksheet.CreateDataTableExporter(cellCollection.CurrentRegion, idt, true); exporter.Export(); SharedFunc.ShowProcess(1); foreach (DataColumn dc in idt.Columns) { foreach (GridColumn bgc in abv.Columns) { if (bgc.Caption.Trim().Equals(dc.ColumnName.Trim())) { dc.ColumnName = bgc.FieldName; break; } } } //克隆结构 变更数据类型 DataTable ndt = idt.Clone(); foreach (DataColumn dc in ndt.Columns) { foreach (DataColumn bgc in sdt.Columns) { if (bgc.Caption.Trim().Equals(dc.ColumnName.Trim())) { dc.ColumnName = bgc.ColumnName; dc.DataType = bgc.DataType; break; } } } foreach (DataRow dr in idt.Rows) { DataRow ndr = ndt.NewRow(); foreach (DataColumn dc in idt.Columns) { ndr[dc.ColumnName] = dr[dc.ColumnName]; } ndt.Rows.Add(ndr); } SharedFunc.ShowProcess(1); sdt.Merge(ndt, true); SharedFunc.ShowProcess(5); } } catch (Exception ex) { throw ex; } finally { SharedFunc.ShowProcess(int.MaxValue); } } }