private SqlDataSource BindToData()
{
// Create a data source with the required connection parameters.
Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Create a SELECT query.
// Join the Categories and Products table by the CategoryID column.
// Return the list of categories and the number of products in each category.
// Sort the categories by the number of products in them.
// The included categories must contain a specific number of products.
// The chain ends with calling the Build method accepting the query name as a parameter.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Categories")
.SelectColumn("CategoryName")
.GroupBy("CategoryName")
.Join("Products", "CategoryID")
.SelectColumn("ProductName", AggregationType.Count, "ProductCount")
.SortBy("ProductName", AggregationType.Count, System.ComponentModel.ListSortDirection.Descending)
.GroupFilter("[ProductCount] > 7")
.Build("selectQuery");
// Add the query to the collection and return the data source.
ds.Queries.Add(query);
ds.Fill();
return(ds);
}
static Customer()
{
SqlDataSource ds = new SqlDataSource("NWindConnectionString");
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Customers")
.SelectAllColumns()
.Build("Customers");
ds.Queries.Add(query);
ds.RebuildResultSchema();
ds.Fill();
ITable src = ds.Result["Customers"];
foreach (var row in src)
{
customers.Add(new Customer()
{
CustomerID = row.GetValue <string>("CustomerID"),
Address = row.GetValue <string>("Address"),
CompanyName = row.GetValue <string>("CompanyName"),
ContactName = row.GetValue <string>("ContactName"),
ContactTitle = row.GetValue <string>("ContactTitle"),
Country = row.GetValue <string>("Country"),
City = row.GetValue <string>("City"),
Fax = row.GetValue <string>("Fax"),
Phone = row.GetValue <string>("Phone"),
PostalCode = row.GetValue <string>("PostalCode"),
Region = row.GetValue <string>("Region")
});
}
}
private void SqltoSeries(Series series1, string name, string sql)
{
SqlDataSource sqlDataSourceTrend = new SqlDataSource();
CustomSqlQuery query = new CustomSqlQuery();
query.Name = name;
query.Sql = sql;
sqlDataSourceTrend.ConnectionName = "GlobeTradeGIS.Properties.Settings.GlobeTradeConnectionString";//用到了connectionstrings下的连接名
sqlDataSourceTrend.Queries.Add(query);
sqlDataSourceTrend.Fill();
ITable src = sqlDataSourceTrend.Result[name];
DataTable table = new DataTable(name);
foreach (IColumn column in src.Columns)
{
table.Columns.Add(column.Name, column.Type);
}
foreach (IRow row in src)
{
table.Rows.Add(row.ToArray());
}
for (int i = 0; i < table.Columns.Count; i++)
{
{
for (int j = 0; j < table.Rows.Count; j++)
{
//if(typeof())
double num = 0;
num = (double)table.Rows[j][i];
series1.Points.Add(new SeriesPoint(table.Columns[i].ColumnName, num));
}
}
}
}
public DataTable GetSsccProducts(string SSCCcode)
{
SqlDataSource ds = new SqlDataSource(DatabaseConnection);
var query = new MySqlQuery().QuerySSCCProducts(SSCCcode);
ds.Queries.Add(query);
ds.Fill();
ITable source = ds.Result[0];
DataTable dest = new DataTable("Products");
foreach (IColumn column in source.Columns)
{
dest.Columns.Add(column.Name, column.Type);
}
foreach (IRow row in source)
{
DataRow dr = dest.NewRow();
for (int i = 0; i < dest.Columns.Count; i++)
{
dr[i] = row[i];
}
dest.Rows.Add(dr);
}
return(dest);
}
private void dashboardViewer1_DataLoading(object sender, DataLoadingEventArgs e)
{
if (e.DataSourceName.StartsWith("ods|"))
{
string[] names = e.DataSourceName.Split("|".ToCharArray());
DashboardSqlDataSource dataSource = dataSources.First(ds => ds.ComponentName == names[1]);
SqlQuery query = dataSource.Queries.First(q => q.Name == names[2]);
XElement dsXML = dataSource.SaveToXml();
SqlDataSource sqlDS = new SqlDataSource();
sqlDS.LoadFromXml(dsXML);
sqlDS.ConnectionName = "Connection";
sqlDS.Fill(query.Name);
ResultSet rSet = ((IListSource)sqlDS).GetList() as ResultSet;
ResultTable rTable = rSet.Tables.First(t => t.TableName == query.Name);
if (query.Name == "Invoices")
{
var dt = ConvertResultTableToDataTable(rTable);
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
if (((DateTime)dt.Rows[i]["OrderDate"]).Year < 2016)
{
dt.Rows.RemoveAt(i);
}
}
e.Data = dt;
}
else
{
e.Data = rTable;
}
}
}
private static SqlDataSource InitData()
{
MsSqlConnectionParameters connectionParameters = new MsSqlConnectionParameters("localhost", "nwind.mdf", "username", "password", MsSqlAuthorizationType.SqlServer);
SqlDataSource ds = new SqlDataSource(connectionParameters);
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery1";
query.Sql = "SELECT * FROM Products";
ds.Queries.Add(query);
ds.Fill();
return(ds);
}
private void BindingTable(string name)
{
SqlDataSource sqlDataSource = new SqlDataSource(new CustomStringConnectionParameters(
Connection.connectionString));
CustomSqlQuery customSqlQuery = new CustomSqlQuery("cq", "select * from nhanvien");
SqlDataSource.DisableCustomQueryValidation = true;
sqlDataSource.Queries.Clear();
sqlDataSource.Queries.Add(customSqlQuery);
sqlDataSource.Fill();
gridControl1.DataSource = sqlDataSource;
gridControl1.DataMember = "cq";
}
public ITable GetSsccList(string customerCode, string depotDate)
{
SqlDataSource ds = new SqlDataSource(DatabaseConnection);
var query = new MySqlQuery().QuerySSCCList(customerCode, depotDate);
ds.Queries.Add(query);
ds.Fill();
DisplayMember = "sscc";
ValueMember = "sscc";
return(ds.Result["SSCCList"]);
}
public ITable GetDepotList()
{
SqlDataSource ds = new SqlDataSource(DatabaseConnection);
var query = new MySqlQuery().QueryDepotList();
ds.Queries.Add(query);
ds.Fill();
DisplayMember = "customername";
ValueMember = "customercode";
return(ds.Result["DepotList"]);
}
private void button1_Click(object sender, EventArgs e)
{
// Create a data source connection.
Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Create a query and specify its SELECT expression.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Products")
.SelectColumn("CategoryID")
.GroupBy("CategoryID")
.SelectExpression("StDev([Products].[UnitPrice])", "PriceDeviation")
.Build("Products");
//Add the query to the data source.
ds.Queries.Add(query);
// Fill the data source.
ds.Fill();
// Create a new report and bind it to the data source.
XtraReport report = new XtraReport();
report.DataSource = ds;
report.DataMember = "Products";
// Create a report layout.
DetailBand detailBand = new DetailBand();
detailBand.Height = 50;
report.Bands.Add(detailBand);
XRLabel labelCategory = new XRLabel();
labelCategory.DataBindings.Add("Text", report.DataSource, "Products.CategoryID", "Category ID: {0}");
labelCategory.TopF = 15;
detailBand.Controls.Add(labelCategory);
XRLabel labelDeviation = new XRLabel();
labelDeviation.DataBindings.Add("Text", report.DataSource, "Products.PriceDeviation", "Price Deviation: {0}");
labelDeviation.TopF = 30;
labelDeviation.WidthF = 500;
detailBand.Controls.Add(labelDeviation);
// Publish the report.
ReportPrintTool pt = new ReportPrintTool(report);
pt.ShowPreviewDialog();
}
//public UC_DM()
//{
// InitializeComponent();
//}
#endregion
#region Private functions
private void BindingTable(string name)
{
SqlDataSource sqlDataSource = new SqlDataSource("DESKTOP-TC830C5_QLDatTiecNhaHang");
//sqlDataSource1 = new SqlDataSource(setConnectionString);
CustomSqlQuery customSqlQuery = new CustomSqlQuery("cq", name);
SqlDataSource.DisableCustomQueryValidation = true;
sqlDataSource.Queries.Clear();
sqlDataSource.Queries.Add(customSqlQuery);
sqlDataSource.Fill();
gridControl1.DataSource = sqlDataSource;
gridControl1.DataMember = "cq";
}
static Customer()
{
try {
SqlDataSource ds = new SqlDataSource("NWindConnectionString");
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Customers")
.SelectAllColumns()
.Build("Customers");
ds.Queries.Add(query);
ds.RebuildResultSchema();
ds.Fill();
ITable src = ds.Result["Customers"];
foreach (var row in src)
{
currentCustomers.Add(new Customer()
{
CustomerID = row.GetValue <string>("CustomerID"),
Address = row.GetValue <string>("Address"),
CompanyName = row.GetValue <string>("CompanyName"),
ContactName = row.GetValue <string>("ContactName"),
ContactTitle = row.GetValue <string>("ContactTitle"),
Country = row.GetValue <string>("Country"),
City = row.GetValue <string>("City"),
Fax = row.GetValue <string>("Fax"),
Phone = row.GetValue <string>("Phone"),
PostalCode = row.GetValue <string>("PostalCode"),
Region = row.GetValue <string>("Region")
});
}
} catch {
currentCustomers.Add(new Customer()
{
Address = "Obere Str. 57",
City = "Berlin",
CompanyName = "Alfreds Futterkiste",
ContactName = "Maria Anders",
ContactTitle = "Sales Representative",
Country = "Germany",
CustomerID = "ALFKI",
Fax = "030-0076545",
Phone = "030-0074321",
PostalCode = "12209"
});
}
}
public void GenerateReportData(DoSoEmail email, bool prevewOnly)
{
if (string.IsNullOrWhiteSpace(ReportDataXml))
{
return;
}
CreateDataSourceFromXml();
ExcelDataSource?.Fill();
SqlDataSource?.Fill();
using (var report = new XtraReport())
{
AddDataSource2Report(report);
using (var ms = new MemoryStream())
{
using (var sr = new StreamWriter(ms, Encoding.Default))
{
var doc = new XmlDocument();
doc.LoadXml(ReportDataXml);
var definitionXml = doc.OuterXml;
sr.Write(definitionXml);
sr.Flush();
ms.Position = 0;
report.LoadLayoutFromXml(ms);
report.FilterString = $"{ObjectKeyExpression} == {email.ObjectKey}";
report.ApplyFiltering();
//report.FillDataSource();
if (prevewOnly)
{
using (ReportPrintTool printTool = new ReportPrintTool(report))
printTool.ShowRibbonPreviewDialog(UserLookAndFeel.Default);
}
else
{
ExportRportData(email, report);
}
}
}
}
}
public object CreateDataSource()
{
SQLiteConnectionParameters connectionParameters =
new SQLiteConnectionParameters("Data\\nwind.db", "");
SqlDataSource sqlDataSource = new SqlDataSource(connectionParameters);
CustomSqlQuery queryProducts = new CustomSqlQuery()
{
Name = "Products",
Sql = "SELECT " +
"Products.ProductID,Products.ProductName,Products.UnitPrice,Products.QuantityPerUnit," +
"Categories.CategoryID,Categories.CategoryName,Categories.Description,Categories.Picture " +
"FROM Products INNER JOIN Categories ON Products.CategoryID=Categories.CategoryID"
};
sqlDataSource.Queries.Add(queryProducts);
sqlDataSource.Fill();
return(sqlDataSource);
}
public void refresh_kuhni()
{
this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
OracleConnectionParameters connectionParameters = new OracleConnectionParameters(vivaprocess.Global.ora_serv, "kuhni_modul", "kuhni");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Create an SQL query to access the Products table.
CustomSqlQuery query = new CustomSqlQuery();
query.Name = "customQuery1";
if (chkInWork.Checked == true & chkGotov.Checked == false & chkOtgrugen.Checked == false)
{
query.Sql = "select distinct trunc(nvl(prod_id,0)) NN,prod_name NN_клиента,nvl(client_name,' ') клиент,nvl(prod_descr,' ') описание,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') дата_поступл,"
+ " nvl(to_char(prod_date_need,'dd.mm.yy'),' ') отгруз_план,nvl(to_char(date_tehnol,'dd.mm.yy'),' ') в_техотдел,"
+ " nvl(tehnolog,' ') исполнитель,nvl(prod_cena,0) цена,"
+ " nvl(to_char(prod_date_otgruzka,'dd.mm.yy'),' ') отгруз_факт,nvl(prod_descr_otgruzka,' ') примечания"
+ " FROM disp_kuhni where prod_date_otgruzka is null and prod_id in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and (vse=1 or vse=" + vivaprocess.Global.vse + ") and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null)"
+ " and (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) order by дата_поступл,NN";
}
else if (chkInWork.Checked == true & chkGotov.Checked == true & chkOtgrugen.Checked == false)
{
query.Sql = "select distinct trunc(nvl(prod_id,0)) NN,prod_name NN_клиента,nvl(client_name,' ') клиент,nvl(prod_descr,' ') описание,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') дата_поступл,"
+ " nvl(to_char(prod_date_need,'dd.mm.yy'),' ') отгруз_план,nvl(to_char(date_tehnol,'dd.mm.yy'),' ') в_техотдел,"
+ " nvl(tehnolog,' ') исполнитель,nvl(prod_cena,0) цена,"
+ " nvl(to_char(prod_date_otgruzka,'dd.mm.yy'),' ') отгруз_факт,nvl(prod_descr_otgruzka,' ') примечания"
+ " FROM disp_kuhni where prod_date_otgruzka is null"
+ " and (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy') and (vse=1 or vse=" + vivaprocess.Global.vse + ")) order by дата_поступл,NN";
}
else if (chkInWork.Checked == true & chkGotov.Checked == true & chkOtgrugen.Checked == true)
{
query.Sql = "select distinct trunc(nvl(prod_id,0)) NN,prod_name NN_клиента,nvl(client_name,' ') клиент,nvl(prod_descr,' ') описание,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') дата_поступл,"
+ " nvl(to_char(prod_date_need,'dd.mm.yy'),' ') отгруз_план,nvl(to_char(date_tehnol,'dd.mm.yy'),' ') в_техотдел,"
+ " nvl(tehnolog,' ') исполнитель,nvl(prod_cena,0) цена,"
+ " nvl(to_char(prod_date_otgruzka,'dd.mm.yy'),' ') отгруз_факт,nvl(prod_descr_otgruzka,' ') примечания"
+ " FROM disp_kuhni where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy') and (vse=1 or vse=" + vivaprocess.Global.vse + ")) order by дата_поступл,NN";
}
else if (chkInWork.Checked == true & chkGotov.Checked == false & chkOtgrugen.Checked == true)
{
query.Sql = "select distinct trunc(nvl(prod_id,0)) NN,prod_name NN_клиента,nvl(client_name,' ') клиент,nvl(prod_descr,' ') описание,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') дата_поступл,"
+ " nvl(to_char(prod_date_need,'dd.mm.yy'),' ') отгруз_план,nvl(to_char(date_tehnol,'dd.mm.yy'),' ') в_техотдел,"
+ " nvl(tehnolog,' ') исполнитель,nvl(prod_cena,0) цена,"
+ " nvl(to_char(prod_date_otgruzka,'dd.mm.yy'),' ') отгруз_факт,nvl(prod_descr_otgruzka,' ') примечания"
+ " FROM disp_kuhni where (prod_date_otgruzka is not null or prod_id in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null))"
+ " and (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy') and (vse=1 or vse=" + vivaprocess.Global.vse + ")) order by дата_поступл,NN";
}
else if (chkInWork.Checked == false & chkGotov.Checked == false & chkOtgrugen.Checked == true)
{
query.Sql = "select distinct trunc(nvl(prod_id,0)) NN,prod_name NN_клиента,nvl(client_name,' ') клиент,nvl(prod_descr,' ') описание,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') дата_поступл,"
+ " nvl(to_char(prod_date_need,'dd.mm.yy'),' ') отгруз_план,nvl(to_char(date_tehnol,'dd.mm.yy'),' ') в_техотдел,"
+ " nvl(tehnolog,' ') исполнитель,nvl(prod_cena,0) цена,"
+ " nvl(to_char(prod_date_otgruzka,'dd.mm.yy'),' ') отгруз_факт,nvl(prod_descr_otgruzka,' ') примечания"
+ " FROM disp_kuhni where prod_date_otgruzka is not null"
+ " and (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy') and (vse=1 or vse=" + vivaprocess.Global.vse + ")) order by дата_поступл,NN";
}
else if (chkInWork.Checked == false & chkGotov.Checked == true & chkOtgrugen.Checked == false)
{
query.Sql = "select distinct trunc(nvl(prod_id,0)) NN,prod_name NN_клиента,nvl(client_name,' ') клиент,nvl(prod_descr,' ') описание,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') дата_поступл,"
+ " nvl(to_char(prod_date_need,'dd.mm.yy'),' ') отгруз_план,nvl(to_char(date_tehnol,'dd.mm.yy'),' ') в_техотдел,"
+ " nvl(tehnolog,' ') исполнитель,nvl(prod_cena,0) цена,"
+ " nvl(to_char(prod_date_otgruzka,'dd.mm.yy'),' ') отгруз_факт,nvl(prod_descr_otgruzka,' ') примечания"
+ " FROM disp_kuhni where (prod_date_otgruzka is null and prod_id not in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null))"
+ " and (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy') and (vse=1 or vse=" + vivaprocess.Global.vse + ")) order by дата_поступл,NN";
}
else if (chkInWork.Checked == false & chkGotov.Checked == true & chkOtgrugen.Checked == true)
{
query.Sql = "select trunc(nvl(prod_id,0)) NN,prod_name NN_клиента,nvl(client_name,' ') клиент,nvl(prod_descr,' ') описание,nvl(to_char(prod_date_in,'dd.mm.yyyy'),' ') дата_поступл,"
+ " nvl(to_char(prod_date_need,'dd.mm.yy'),' ') отгруз_план,nvl(to_char(date_tehnol,'dd.mm.yy'),' ') в_техотдел,"
+ " nvl(tehnolog,' ') исполнитель,nvl(prod_cena,0) цена,"
+ " nvl(to_char(prod_date_otgruzka,'dd.mm.yy'),' ') отгруз_факт,nvl(prod_descr_otgruzka,' ') примечания"
+ " FROM disp_kuhni where (prod_date_otgruzka is not null or prod_id not in (select t.prod_id from disp_kuhni t where (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy')) and t.mat_descr<>'нет' and t.sklad_gotov_fakt is null))"
+ " and (prod_date_in between to_date('" + dateStart.Text + "','dd.mm.yyyy') and to_date('" + dateEnd.Text + "','dd.mm.yyyy') and (vse=1 or vse=" + vivaprocess.Global.vse + ")) order by дата_поступл,NN";
}
else
{
this.Cursor = System.Windows.Forms.Cursors.Default;
return;
}
ds.Queries.Add(query);
ds.Fill();
//Assign the data source for the grid and retrieve fields.
gridControl1.DataSource = ds;
gridControl1.DataMember = "customQuery1";
gridView1.Columns["NN"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
gridView1.Columns["NN"].DisplayFormat.FormatString = "n0";
gridView1.Columns["ЦЕНА"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Numeric;
gridView1.Columns["ЦЕНА"].DisplayFormat.FormatString = "n2";
if (vivaprocess.Global.prava == "технолог" || vivaprocess.Global.prava == "цех" || vivaprocess.Global.prava == "склад продукции" || vivaprocess.Global.prava == "снабжение")
{
gridView1.Columns["ЦЕНА"].Visible = false;
}
gridView1.BestFitColumns();
/*gridView1.Columns["ДАТА"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["ДАТА"].DisplayFormat.FormatString = "dd.M.yyyy";
* gridView1.Columns["ОТГРУЗ_ПЛАН"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["ОТГРУЗ_ПЛАН"].DisplayFormat.FormatString = "d";
* gridView1.Columns["В_ТЕХОТДЕЛ"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["В_ТЕХОТДЕЛ"].DisplayFormat.FormatString = "d";
* gridView1.Columns["ПРОИЗВ_ПЛАН"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["ПРОИЗВ_ПЛАН"].DisplayFormat.FormatString = "d";
* gridView1.Columns["ПРОИЗВ_ФАКТ"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["ПРОИЗВ_ФАКТ"].DisplayFormat.FormatString = "d";
* gridView1.Columns["КРАСКА_ПЛАН"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["КРАСКА_ПЛАН"].DisplayFormat.FormatString = "d";
* gridView1.Columns["КРАСКА_ФАКТ"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["КРАСКА_ФАКТ"].DisplayFormat.FormatString = "d";
* gridView1.Columns["ОТК_ПЛАН"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["ОТК_ПЛАН"].DisplayFormat.FormatString = "d";
* gridView1.Columns["ОТК_ФАКТ"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["ОТК_ФАКТ"].DisplayFormat.FormatString = "d";
* gridView1.Columns["СБОРКА_ПЛАН"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["СБОРКА_ПЛАН"].DisplayFormat.FormatString = "d";
* gridView1.Columns["СБОРКА_ФАКТ"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["СБОРКА_ФАКТ"].DisplayFormat.FormatString = "d";
* gridView1.Columns["СКЛ_ГОТ_ПЛАН"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["СКЛ_ГОТ_ПЛАН"].DisplayFormat.FormatString = "d";
* gridView1.Columns["СКЛ_ГОТ_ФАКТ"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["СКЛ_ГОТ_ФАКТ"].DisplayFormat.FormatString = "d";
* gridView1.Columns["СНАБЖ_ЗАЯВКА"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["СНАБЖ_ЗАЯВКА"].DisplayFormat.FormatString = "d";
* gridView1.Columns["ОТГРУЗ_ФАКТ"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
* gridView1.Columns["ОТГРУЗ_ФАКТ"].DisplayFormat.FormatString = "d";*/
this.Cursor = System.Windows.Forms.Cursors.Default;
}
public Workbook ExportFromSqlDataSource(SqlDataSource ds, SpreadsheetControl control)
{
ds.Fill();
var outDocument = GetOutDocument();
foreach (var item in control.Document.Worksheets)
{
control.Document.Worksheets.ActiveWorksheet = item;
if (control.ActiveWorksheet.DefinedNames.Any())
{
ExportByMailMerge(control, outDocument);
}
else
{
outDocument.Worksheets.Add(item.Name);
outDocument.Worksheets.LastOrDefault().CopyFrom(item);
var userdRange = item.GetDataRange().Where(x => x.HasFormula && x.Formula.ToLower().Contains("=field("));
if (userdRange.Any())
{
foreach (var rangeItem in userdRange)
{
if (rangeItem.RowIndex > 0)
{
var headerCell = outDocument.Worksheets.LastOrDefault().Cells[rangeItem.RowIndex - 1, rangeItem.ColumnIndex];
if (headerCell.Value.IsEmpty)
{
headerCell.SetValue(rangeItem.DisplayText.Replace("]", "").Replace("[", ""));
}
}
var dataMember = control.Document.MailMergeDataMember;
var splitedItem = rangeItem.DisplayText.Split('.');
if (splitedItem.Count() > 1)
{
dataMember = splitedItem.FirstOrDefault().Replace("[", "");
}
var query = ds.Result.Where(x => x.Name == dataMember).SelectMany(x => x.Columns).Where(x => x.Name == splitedItem.LastOrDefault().Replace("]", "").Replace("[", "")).FirstOrDefault() as DevExpress.DataAccess.Native.Sql.ResultColumn;
for (int i = 0; i < query.Count; i++)
{
var value = query.Values[i];
var cell = outDocument.Worksheets.LastOrDefault().Cells[rangeItem.RowIndex + i, rangeItem.ColumnIndex];
cell.SetValue(value);
}
}
}
else
{
var lastIndex = 0;
foreach (var result in ds.Result)
{
outDocument.Worksheets.LastOrDefault().Import(result, 1, lastIndex);
foreach (var column in result.Columns)
{
var headerCell = outDocument.Worksheets.LastOrDefault().Cells[0, lastIndex];
if (headerCell.Value.IsEmpty)
{
headerCell.SetValue(column.Name);
}
lastIndex++;
}
}
}
ReplaceTemplateValues(item, outDocument);
}
}
return(outDocument);
}
