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);
}
}
}
