private void ExportDataTable(DataTableExporter exporter, Workbook book, WorkerThread thread)
{
//TudqThem
if (modExport == 1 & columnRemove != null)
{
for (var j = 0; j < columnRemove.Rows.Count; j++)
{
for (var i = 0; i < ResultTable.Columns.Count; i++)
{
if (ResultTable.Columns[i].ColumnName == columnRemove.Rows[j]["Value"].ToString())
{
ResultTable.Columns.RemoveAt(i);
break;
}
}
}
}
//End
var sheet = book.Worksheets[book.Worksheets.Add()];
sheet.Name = string.Format(Language.SheetName, book.Worksheets.Count);
thread.JobName = string.Format(Language.SaveToSheetStatus, book.Worksheets.Count);
exporter.ExportDataTable(sheet, ResultTable);
thread.JobName = string.Format(Language.ApplyFormatToSheetStatus, book.Worksheets.Count);
sheet.AutoFitColumns();
}
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;
}
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);
}
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);
}
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 string DownEnrollTpl(string pRequest)
{
var rp = pRequest.DeserializeJSONTo <APIRequest <JIT.CPOS.BS.BLL.LEventSignUpBLL.DownEnrollTplRP> >();
rp.Parameters.Validate();
var rd = new EmptyRD(); // new JIT.CPOS.BS.BLL.LEventSignUpBLL.DownEnrollTplRD();
DataTable dataTable = PrivateLEventSignUpBLL.DownEnrollTpl(rp.Parameters.EventID, rp.Parameters.Status);
//数据获取
Workbook wb = DataTableExporter.WriteXLS(dataTable, 0);
string savePath = HttpContext.Current.Server.MapPath(@"~/File/Excel");
if (!System.IO.Directory.Exists(savePath))
{
System.IO.Directory.CreateDirectory(savePath);
}
savePath = savePath + "\\报名人员模板-" + dataTable.TableName + ".xls";
wb.Save(savePath);//保存Excel文件
new ExcelCommon().OutPutExcel(HttpContext.Current, savePath);
HttpContext.Current.Response.End();
var rsp = new SuccessResponse <IAPIResponseData>(rd);
if (dataTable == null || !(dataTable.Columns.Count > 0))
{
rsp.ResultCode = 201;
rsp.Message = "生成失败!";
}
return(rsp.ToJSON());
}
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 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 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);
}
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);
}
public void ExportVipInfo(Dictionary <string, string> columns, DataTable dt, string fileName)
{
Workbook wb = DataTableExporter.WriteXLS(dt, 0);
string savePath = HttpContext.Current.Server.MapPath(@"~/File/VipCard");
if (!System.IO.Directory.Exists(savePath))
{
System.IO.Directory.CreateDirectory(savePath);
}
savePath = savePath + "\\" + fileName + DateTime.Now.ToFileTime() + ".xls";
wb.Save(savePath);//保存Excel文件
new ExcelCommon().OutPutExcel(HttpContext.Current, savePath);
HttpContext.Current.Response.End();
}
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);
}
private static void ExportDataSet(ExportContext context, DataSet dataSet, JsonWriter writer)
{
Debug.Assert(context != null);
Debug.Assert(dataSet != null);
Debug.Assert(writer != null);
writer.WriteStartObject();
foreach (DataTable table in dataSet.Tables)
{
writer.WriteMember(table.TableName);
DataTableExporter.ExportTable(context, table, writer);
}
writer.WriteEndObject();
}
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);
}
public static string DataTableToExcel(DataTable dataTable, string folderName, string fileNamePre, string postOrGet = "Get")
{
//数据获取
Workbook wb = DataTableExporter.WriteXLS(dataTable, 0);
string savePath = HttpContext.Current.Server.MapPath(@"~/File/Excel/" + folderName);
if (!System.IO.Directory.Exists(savePath))
{
System.IO.Directory.CreateDirectory(savePath);
}
string fileName = fileNamePre + DateTime.Now.ToFileTime() + ".xls";
wb.Save(savePath + "\\" + fileName);//保存Excel文件
if (postOrGet.ToLower() == "get")
{
OutputExcel(HttpContext.Current, savePath);
HttpContext.Current.Response.End();
}
return(@"/File/Excel/" + folderName + "/" + fileName);
}
private void ExportTable(Dictionary <string, string> columns, DataTable dt, string fileName)
{
DataColumn dc = new DataColumn();
dt = dt.DefaultView.ToTable(false, columns.Keys.ToArray());
foreach (DataColumn c in dt.Columns)
{
string title;
columns.TryGetValue(c.ColumnName, out title);
c.ColumnName = title;
}
Workbook wb = DataTableExporter.WriteXLS(dt, 0);
string savePath = HttpContext.Current.Server.MapPath(@"~/File/Vip");
if (!System.IO.Directory.Exists(savePath))
{
System.IO.Directory.CreateDirectory(savePath);
}
savePath = savePath + "\\" + fileName + DateTime.Now.ToFileTime() + ".xls";
wb.Save(savePath);//保存Excel文件
new ExcelCommon().OutPutExcel(HttpContext.Current, savePath);
HttpContext.Current.Response.End();
}
public string ExportStaff(string pRequest)
{
var rd = new APIResponse <ExportStaffRD>();
var rdData = new ExportStaffRD();
var rp = pRequest.DeserializeJSONTo <APIRequest <ExportStaffRP> >();
if (rp.Parameters == null)
{
throw new ArgumentException();
}
if (rp.Parameters != null)
{
rp.Parameters.Validate();
}
var loggingSessionInfo = new LoggingSessionManager().CurrentSession;
try
{
//获取普通员工employee角色标识
string roleId = string.Empty;
var appSysService = new AppSysService(loggingSessionInfo);
RoleModel list = new RoleModel();
string key = "D8C5FF6041AA4EA19D83F924DBF56F93";
list = appSysService.GetRolesByAppSysId(key, 1000, 0, "", "", "");
foreach (var item in list.RoleInfoList)
{
if (item.Role_Code.ToLower() == "employee")
{
roleId = item.Role_Id;
break;
}
}
if (string.IsNullOrEmpty(roleId) || roleId == "")
{
throw new APIException("employee的roleId未获取到")
{
ErrorCode = 103
}
}
;
rp.Parameters.ExportType = rp.Parameters.ExportType.ToLower();
//所有页
if (rp.Parameters.ExportType == "allpage")
{
rp.Parameters.PageIndex = 0;
rp.Parameters.PageSize = 5000;
}
//数据获取
T_UserBLL userBll = new T_UserBLL(loggingSessionInfo);
int totalPage = 0;
QueryUserEntity entity = new QueryUserEntity();
entity.QUserName = rp.Parameters.Keyword;
entity.QUnitID = rp.Parameters.UnitID;
entity.QJobFunctionID = rp.Parameters.JobFunctionID;
entity.QRoleID = roleId;
DataTable dTable = userBll.GetUserList(rp.Parameters.UserID, rp.Parameters.PageIndex, rp.Parameters.PageSize, out totalPage, entity);
//过滤选择的员工
if (rp.Parameters.ExportType == "select")
{
string[] ids = rp.Parameters.StaffIds.Split(',');
string strIds = "'',";
for (int i = 0; i < ids.Length; i++)
{
strIds += "'" + ids[i] + "',";
}
strIds = strIds.Substring(0, strIds.Length - 1);
DataRow[] drs = dTable.Select("UserID in (" + strIds + ")");
DataTable d = dTable.Clone();
foreach (var item in drs)
{
d.ImportRow(item);
}
dTable = d;
}
else
if (rp.Parameters.ExportType == "noselect")
{
dTable = dTable.Clone();
}
//排序
DataView dv = dTable.DefaultView;
string sort = string.IsNullOrEmpty(rp.Parameters.sort) ? "UserEmail asc" : rp.Parameters.sort;
sort = "UserStatus desc," + sort;
dv.Sort = sort;
DataTable dt2 = dv.ToTable();
dTable = dt2;
//表格标题设置
DataTable lastTable = dTable.DefaultView.ToTable(false, "UserCode", "UserName", "UserNameEn", "UserEmail", "UserGenderT", "UserBirthday", "UserTelephone", "UserCellphone", "DeptName", "JobFuncName", "UserStatusDesc");
//重置标题名称
lastTable.Columns["UserCode"].ColumnName = "用户编码";
lastTable.Columns["UserName"].ColumnName = "姓名";
lastTable.Columns["UserNameEn"].ColumnName = "英文名";
lastTable.Columns["UserEmail"].ColumnName = "邮箱";
lastTable.Columns["UserGenderT"].ColumnName = "性别";
lastTable.Columns["UserBirthday"].ColumnName = "生日";
lastTable.Columns["UserTelephone"].ColumnName = "手机";
lastTable.Columns["UserCellphone"].ColumnName = "电话";
lastTable.Columns["DeptName"].ColumnName = "部门";
lastTable.Columns["JobFuncName"].ColumnName = "职务";
lastTable.Columns["UserStatusDesc"].ColumnName = "状态";
//数据获取
Workbook wb = DataTableExporter.WriteXLS(lastTable, 0);
string savePath = HttpContext.Current.Server.MapPath(@"~/File/Excel");
if (!System.IO.Directory.Exists(savePath))
{
System.IO.Directory.CreateDirectory(savePath);
}
savePath = savePath + "\\企信员工-" + lastTable.TableName + ".xls";
wb.Save(savePath);//保存Excel文件
new ExcelCommon().OutPutExcel(HttpContext.Current, savePath);
HttpContext.Current.Response.End();
rd.ResultCode = 0;
}
catch (Exception ex)
{
rd.ResultCode = 103;
rd.Message = ex.Message;
}
return(rd.ToJSON());
}
private void DoWork()
{
while (true)
{
try
{
ConfigurationManager.RefreshSection("appSettings");
var time = ConfigurationManager.AppSettings["Timing"];
TimeSpan.TryParse(time, out _timeSpan);
if (DateTime.Now.TimeOfDay.Hours == _timeSpan.Hours)
{
//支持配置多客户
string[] customerIDs = ConfigurationManager.AppSettings["CustomerIDs"].Split(',');
foreach (string customerID in customerIDs)
{
loggingSessionInfo = Default.GetBSLoggingSession(customerID, "1");
var eventsService = new LEventsBLL(loggingSessionInfo);
LEventsEntity queryEntity = new LEventsEntity();
queryEntity.EventType = 0;
var data = eventsService.WEventGetWebEvents(queryEntity, 0, 100000000);
foreach (var item in data)
{
XieHuiBaoBLL b = new XieHuiBaoBLL(loggingSessionInfo, "vip");
List <DefindControlEntity> l = new List <DefindControlEntity>();
PageResultEntity pageEntity = null;
try
{
pageEntity = b.GetPageDataByEventID(l, 100000000, 0, item.EventID);
}
catch (Exception e)
{
Loggers.Debug(new DebugLogInfo
{
Message = string.Format("{0} {1} (EventID:{3})报名人员信息通知邮件发送{2}", DateTime.Now.ToShortDateString(), DateTime.Now.TimeOfDay, "失败", item.EventID)
});
Loggers.Exception(new BasicUserInfo(), e);
continue;
}
if (pageEntity.GridData != null)
{
if (pageEntity.GridData.Rows.Count == 0)
{
continue;
}
}
GridInitEntity g = GetInitGridDataByEventID(item.EventID);
if (pageEntity != null && pageEntity.GridData != null)
{
#region 替换标题信息
if (g != null && g.GridColumnDefinds != null)
{
if (pageEntity.GridData.Columns.Contains("ROW_NUMBER"))
{
pageEntity.GridData.Columns.Remove("ROW_NUMBER");
}
if (pageEntity.GridData.Columns.Contains("SignUpID"))
{
pageEntity.GridData.Columns.Remove("SignUpID");
}
if (pageEntity.GridData.Columns.Count == 0)
{
continue;
}
for (int i = 0; i < pageEntity.GridData.Columns.Count; i++)
{
for (int j = 0; j < g.GridColumnDefinds.Count; j++)
{
if (pageEntity.GridData.Columns[i].ColumnName.ToLower() == g.GridColumnDefinds[j].DataIndex.ToLower())
{
pageEntity.GridData.Columns[i].ColumnName = g.GridColumnDefinds[j].ColumnText;
break;
}
}
}
}
#endregion
}
//数据获取
Workbook wb = DataTableExporter.WriteXLS(pageEntity.GridData, 0);
wb.Worksheets[0].Name = "参加活动人员信息";
string savePath = ConfigurationManager.AppSettings["FileURL"];
if (!Directory.Exists(savePath))
{
Directory.CreateDirectory(savePath);
}
savePath = savePath + "/" + item.Title + DateTime.Now.ToString("yyyyMMdd") + ".xls";
wb.Save(savePath);//保存Excel文件
#region 邮件发送
FromSetting fs = new FromSetting();
fs.SMTPServer = ConfigurationManager.AppSettings["SmtpServer"];
fs.SendFrom = ConfigurationManager.AppSettings["MailSendFrom"];
fs.UserName = ConfigurationManager.AppSettings["MailUserName"];
fs.Password = ConfigurationManager.AppSettings["MailUserPassword"];
string mailTo = ConfigurationManager.AppSettings["MailTo"];
string isSendMail = ConfigurationManager.AppSettings["IsSendMailToCreator"];
//是否发送成功
bool result = false;
if (!string.IsNullOrEmpty(mailTo))
{
result = Mail.SendMail(fs, mailTo, ConfigurationManager.AppSettings["MailTitle"], ConfigurationManager.AppSettings["Subject"], savePath.Split(','));
}
if (isSendMail == "1")
{
if (!string.IsNullOrEmpty(item.Email))
{
if (DateTime.Now < DateTime.Parse(item.EndTime))
{
//等于0表示不发送邮件
if (item.MailSendInterval == null || item.MailSendInterval == 0)
{
continue;
}
//日期差与间隔时间取余判断是否符合发送邮件日期
int dateDiff = ((TimeSpan)(DateTime.Now.Date - item.LastUpdateTime.Value.Date)).Days;
if (dateDiff % item.MailSendInterval != 0)
{
continue;
}
result = Mail.SendMail(fs, item.Email, ConfigurationManager.AppSettings["MailTitle"], ConfigurationManager.AppSettings["Subject"], savePath.Split(','));
Loggers.Debug(new DebugLogInfo
{
Message = string.Format("{0} {1} (EventID:{3})报名人员信息通知邮件发送{2}", DateTime.Now.ToShortDateString(), DateTime.Now.TimeOfDay, result ? "成功" : "失败", item.EventID)
});
}
}
}
#endregion
}
}
}
}
catch (Exception ex)
{
Loggers.Exception(new BasicUserInfo(), ex);
}
Thread.Sleep(new TimeSpan(0, 1, 0, 0));
}
}
public override void Execute()
{
base.Execute();
if (ValidateModule())
{
string exportFileName;
exportFileName = (string)this["E02"];
var gridView = PrintGrid.DefaultView as GridView;
if (gridView != null)
{
lnkFile.Visible = false;
foreach (var control in CommonControlByID.Values)
{
control.Enabled = false;
}
var columns = new List <string>();
var fields = new List <ModuleFieldInfo>();
var headers = new List <string>();
foreach (GridColumn column in gridView.Columns)
{
var flagAddColumn = true;
if (column.Visible && column.VisibleIndex >= 0)
{
//TUDQ them
if (modExport == 1 & columnRemove != null)
{
for (var j = 0; j < columnRemove.Rows.Count; j++)
{
if (columnRemove.Rows[j]["Value"].ToString() == column.FieldName)
{
flagAddColumn = false;
}
}
}
if (flagAddColumn)
{
var field = (ModuleFieldInfo)column.Tag;
if (field != null)
{
fields.Add(field);
columns.Add(column.FieldName);
headers.Add(column.ToolTip);
}
}
}
//if (column.Visible && column.VisibleIndex >= 0)
//{
// var field = (ModuleFieldInfo)column.Tag;
// if (field != null)
// {
// fields.Add(field);
// columns.Add(column.FieldName);
// headers.Add(column.Caption);
// }
//}
}
if ((string)this["E01"] == CODES.EXPORT.EXPORTTYPE.XML || (string)this["E01"] == CODES.EXPORT.EXPORTTYPE.TXT)
{
CurrentThread = new WorkerThread(
delegate(WorkerThread thread)
{
LockUserAction();
ExportSuccess = false;
try
{
using (var ctrlSA = new SAController())
{
DataContainer container;
DataSet ds = new DataSet("main");
DataTable tempTable;
ctrlSA.FetchAllSearchResult(out container, ModuleInfo.ModuleID, ModuleInfo.SubModule, LastSearchResultKey, LastSearchTime, 0);
tempTable = container.GetTable(
FieldUtils.GetModuleFields(
ModuleInfo.ModuleID,
CODES.DEFMODFLD.FLDGROUP.SEARCH_COLUMN
));
tempTable.TableName = "DATA_RECORD";
ds.Tables.Add(tempTable);
//ds.Tables.Add(table);
if ((string)this["E01"] == CODES.EXPORT.EXPORTTYPE.XML)
{
ds.WriteXml((string)this["E02"]);
}
else
{
WriteFileText(ds.Tables[0], (string)this["E02"]);
}
CurrentThread.JobName = string.Format(Language.SaveToFileStatus, exportFileName);
thread.ExecuteUpdateGUI(true);
}
thread.JobName = Language.CompletedStatus;
thread.PercentComplete = 100;
ExportSuccess = true;
}
catch (Exception ex)
{
ShowError(ex);
}
finally
{
UnLockUserAction();
}
thread.ExecuteUpdateGUI(true);
}, this);
btnExport.Enabled = false;
CurrentThread.ProcessComplete += thread_ProcessComplete;
CurrentThread.DoUpdateGUI += thread_DoUpdateGUI;
CurrentThread.Start();
}
else
{
var exporter = new DataTableExporter(columns.ToArray(), headers.ToArray(), ModuleInfo, fields.ToArray());
CurrentThread = new WorkerThread(
delegate(WorkerThread thread)
{
LockUserAction();
ExportSuccess = false;
FileFormatType exportType;
int maxRowCount;
switch ((string)this["E01"])
{
case CODES.EXPORT.EXPORTTYPE.EXCEL_XLSX:
exportType = FileFormatType.Excel2007Xlsx;
maxRowCount = CONSTANTS.MAX_ROWS_IN_EXPORT_XLSX;
break;
default:
exportType = FileFormatType.Excel2003;
maxRowCount = CONSTANTS.MAX_ROWS_IN_EXPORT_XLS;
break;
}
try
{
using (var ctrlSA = new SAController())
{
DataContainer container;
DataTable tempTable;
var book = exporter.CreateWorkBook();
var fromRow = 0;
var rowCount = 0;
ResultTable = null;
do
{
ctrlSA.FetchAllSearchResult(out container, ModuleInfo.ModuleID, ModuleInfo.SubModule, LastSearchResultKey, LastSearchTime, fromRow);
tempTable = container.GetTable(
FieldUtils.GetModuleFields(
ModuleInfo.ModuleID,
CODES.DEFMODFLD.FLDGROUP.SEARCH_COLUMN
));
if (tempTable.Rows.Count > 0)
{
fromRow = fromRow + tempTable.Rows.Count;
rowCount = rowCount + tempTable.Rows.Count;
if (ResultTable == null)
{
ResultTable = tempTable;
}
else
{
foreach (DataRow row in tempTable.Rows)
{
ResultTable.ImportRow(row);
}
// TODO: CHANGE NAME OF THREAD
thread.JobName = string.Format("{0} rows buffered", rowCount);
thread.ExecuteUpdateGUI();
}
}
if (ResultTable != null && ResultTable.Rows.Count > maxRowCount)
{
ExportDataTable(exporter, book, thread);
ClearExportedRows();
}
}while (tempTable.Rows.Count > 0);
if (ResultTable != null && ResultTable.Rows.Count > 0)
{
ExportDataTable(exporter, book, thread);
}
else
{
book.Worksheets.Add("No result");
}
CurrentThread.JobName = string.Format(Language.SaveToFileStatus, exportFileName);
thread.ExecuteUpdateGUI(true);
book.Save(exportFileName, exportType);
}
thread.JobName = Language.CompletedStatus;
thread.PercentComplete = 100;
ExportSuccess = true;
}
catch (Exception ex)
{
thread.PercentComplete = 100;
thread.JobName = Language.ErrorStatus;
ShowError(ex);
}
finally
{
UnLockUserAction();
}
thread.ExecuteUpdateGUI(true);
}, this);
btnExport.Enabled = false;
CurrentThread.ProcessComplete += thread_ProcessComplete;
CurrentThread.DoUpdateGUI += thread_DoUpdateGUI;
CurrentThread.Start();
}
}
}
}
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
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
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);
}
}
}
private string ExportCard(string pRequest)
{
var loggingSessionInfo = new SessionManager().CurrentUserLoginInfo;
var cardDepositBLL = new CardDepositBLL(loggingSessionInfo);
var rd = new GetCardRD();
var rp = pRequest.DeserializeJSONTo <APIRequest <GetCardRP> >();
rp.Parameters.Validate();
DataSet ds = new DataSet();
if (rp.Parameters.CardIDs != null && rp.Parameters.CardIDs.Length > 0)
{
ds = cardDepositBLL.GetCardByIDs(rp);
}
else if (!string.IsNullOrEmpty(rp.Parameters.BatchID))
{
ds = cardDepositBLL.GetCardByBatchID(rp);
}
else
{
rp.Parameters.PageSize = 1000000;
rp.Parameters.PageIndex = 0;
ds = cardDepositBLL.PagedSearch(rp.Parameters, loggingSessionInfo.ClientID);
}
if (ds.Tables.Count > 0)
{
DataTable dt = new DataTable();
if (ds.Tables[0] != null)
{
ds.Tables[0].Columns.Add(new DataColumn()
{
ColumnName = "CardPasswordDecrypt", DataType = typeof(string)
});
#region 解密密码
foreach (DataRow item in ds.Tables[0].Rows)
{
item["CardPasswordDecrypt"] = System.Text.Encoding.UTF8.GetString(cardDepositBLL.DecryptCardPassword((byte[])item["CardPassword"]));
}
#endregion
Dictionary <string, string> exportFields = new Dictionary <string, string>();
exportFields.Add("ChannelTitle", "渠道");
exportFields.Add("CardNo", "卡号");
exportFields.Add("CardPasswordDecrypt", "密码");
exportFields.Add("Amount", "金额");
exportFields.Add("Bonus", "赠送金额");
#region 替换标题信息
DataColumn dc = new DataColumn();
dt = ds.Tables[0].DefaultView.ToTable(false, exportFields.Keys.ToArray());
foreach (DataColumn c in dt.Columns)
{
string title;
exportFields.TryGetValue(c.ColumnName, out title);
c.ColumnName = title;
}
#endregion
}
//数据获取
Workbook wb = DataTableExporter.WriteXLS(dt, 0);
string savePath = HttpContext.Current.Server.MapPath(@"~/File/Excel");
if (!System.IO.Directory.Exists(savePath))
{
System.IO.Directory.CreateDirectory(savePath);
}
savePath = savePath + "\\卡数据导出" + DateTime.Now.ToFileTime() + ".xls";
wb.Save(savePath);//保存Excel文件
new ExcelCommon().OutPutExcel(HttpContext.Current, savePath);
HttpContext.Current.Response.End();
}
var rsp = new SuccessResponse <IAPIResponseData>(rd);
return(rsp.ToJSON());
}
