/// <summary>
/// Формирует Excel документ из формы и запроса выборки данных
/// </summary>
/// <param name="form">Форма, задающая оформление данных в выходном документе</param>
/// <param name="queryDef">Запрос для выборки данных из БД</param>
/// <returns>Массив двоичных данных тела Excel файла</returns>
public byte[] ExcelFromQuery(BizForm form, QueryDef queryDef)
{
var sqb = _sqlQueryBuilderFactory.Create();
using (var sqlQuery = sqb.Build(queryDef, form, null, null))
{
sqlQuery.AddAttribute("&Id");
using (var sqlReader = _sqlQueryReaderFactory.Create(sqlQuery) /*new SqlQueryReader(DataContext, sqlQuery)*/)
{
var defBuilder = new XlsGridDefBuilder(Provider, form, sqlReader);
using (var def = defBuilder.BuildFromBizForm())
{
var builder = new XlsBuilder(def);
using (var workbook = builder.Build())
{
using (var stream = new MemoryStream())
{
workbook.Write(stream);
return(stream.ToArray());
}
}
}
}
}
}
/// <summary>
/// Формирует Excel документ из формы и критериев выборки
/// </summary>
/// <param name="form">Форма, задающая оформление данных в выходном документе</param>
/// <param name="docStateId">Идентификатор статуса документа в качестве критерия выборки документов из БД</param>
/// <param name="filter">Форма фильтра с данными для ограничения выборки документов</param>
/// <returns>Массив двоичных данных тела Excel файла</returns>
public byte[] ExcelFromFormFilter(BizForm form, Guid?docStateId, BizForm filter)
{
var sqb = _sqlQueryBuilderFactory.Create();
using (var query = sqb.Build(form, docStateId, filter, null))
{
using (var reader = _sqlQueryReaderFactory.Create(query))
{
var defBuilder = new XlsGridDefBuilder(Provider, form, reader);
using (var def = defBuilder.BuildFromBizForm())
{
var builder = new XlsBuilder(def);
using (var workbook = builder.Build())
{
using (var stream = new MemoryStream())
{
workbook.Write(stream);
return(stream.ToArray());
}
}
}
}
}
}
/// <summary>
/// Формирует Excel документ из формы и списка документов
/// </summary>
/// <param name="form">Форма, задающая оформление данных в выходном документе</param>
/// <param name="docIdList">Список идентификаторов документов, которые необходимо вывести в файл</param>
/// <returns>Массив двоичных данных тела Excel файла</returns>
public byte[] ExcelFromDocIdList(BizForm form, List <Guid> docIdList)
{
var defBuilder = new XlsGridDefBuilder(DataContext, form, docIdList, CurrentUserId);
using (var def = defBuilder.BuildFromBizForm())
{
var builder = new XlsBuilder(def);
using (var workbook = builder.Build())
{
using (var stream = new MemoryStream())
{
workbook.Write(stream);
return(stream.ToArray());
}
}
}
}
public void TestMethod3()
{
using (var connection = new SqlConnection(CissaConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var provider = AppProvider.CreateProvider(dataContext))
{
var formRepo = provider.Get <IFormRepository>(); //new FormRepository(dataContext);
var form = formRepo.GetTableForm(Guid.Parse("{B46A77AB-3F36-42CD-998A-018BE911AD16}"));
var defRepo = provider.Get <IDocDefRepository>(); // new DocDefRepository(dataContext);
var docDef = defRepo.DocDefById(form.DocumentDefId ?? Guid.Empty);
var qb = new QueryBuilder(docDef.Id);
qb.Where("PostCode").Eq("10");
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
//var query = new DocQuery(qb.Def, dataContext);
using (var query = sqlQueryBuilder.Build(qb.Def, form, null))
{
query.TopNo = 100;
using (var reader = new SqlQueryReader(dataContext, query))
{
var defBuilder = new XlsGridDefBuilder(provider, form, reader /*.All()*/);
var def = defBuilder.BuildFromBizForm();
def.Style.FontName = "Arial Narrow";
def.Style.VAlign = VAlignment.Middle;
var builder = new XlsBuilder(def);
var workbook = builder.Build();
using (
var stream = new FileStream(@"c:\distr\cissa\testXlsDefApps.xls", FileMode.Create))
{
workbook.Write(stream);
}
}
}
}
}
}
}
public byte[] ExcelFromDocumentListForm(Guid documentId, BizDocumentListForm docListForm)
{
var form = docListForm.TableForm;
if (form == null && docListForm.FormId != null)
{
form = FormRepo.GetTableForm((Guid)docListForm.FormId);
}
if (form == null)
{
throw new ApplicationException("Табличная форма не указана!");
}
var sqb = _sqlQueryBuilderFactory.Create();
if (docListForm.AttributeDefId != null)
{
using (var query = sqb.BuildAttrList(form, documentId, (Guid)docListForm.AttributeDefId, null, null))
{
query.WithNoLock = true;
using (var reader = _sqlQueryReaderFactory.Create(query))
{
var defBuilder = new XlsGridDefBuilder(Provider, form, reader);
using (var def = defBuilder.BuildFromBizForm())
{
var builder = new XlsBuilder(def);
using (var workbook = builder.Build())
{
using (var stream = new MemoryStream())
{
workbook.Write(stream);
return(stream.ToArray());
}
}
}
}
}
}
if (docListForm.FormAttributeDefId != null)
{
using (var query = sqb.BuildRefList(form, documentId, (Guid)docListForm.FormAttributeDefId, null, null))
{
using (var reader = _sqlQueryReaderFactory.Create(query)) //new SqlQueryReader(DataContext, query))
{
var defBuilder = new XlsGridDefBuilder(Provider, form, reader);
using (var def = defBuilder.BuildFromBizForm())
{
var builder = new XlsBuilder(def);
using (var workbook = builder.Build())
{
using (var stream = new MemoryStream())
{
workbook.Write(stream);
return(stream.ToArray());
}
}
}
}
}
}
throw new ApplicationException("Не могу сформировать Excel файл! Атрибут не указан!");
}
