//Exemplo de implementação para leitura do excel e parse para objetos fortemente tipados
private void FillDataGrid(string filePath)
{
using (var ExcelDocumentService = new ExcelDocumentService(filePath, new OpenXmlApplication(filePath)))
{
//O objeto para retorno deve possuir as properties iguais às colunas header do documento
var lst = ExcelDocumentService.ReadExcelDocument <Signus>("Global");
var source = new BindingSource {
DataSource = lst
};
dgvExcelDocument.DataSource = source;
}
}
public void GetScheduledDBReport(DataSourceColumnList DBobj1, DataSourceColumnList DBobj2)
{
DataTable ColumnValues1 = dBServiceRepoObj.GetColumnData(DBobj1.DbServerName, DBobj1.DBName, DBobj1.TableName, DBobj1.SqlQuery, DBobj1.ColumnName, DBobj1.DatabaseObjectType, DBobj1.StoredProcedureName, DBobj1.SpParameterValue, DBobj1.AuthenticationType, DBobj1.UserName, DBobj1.Password);
DataTable ColumnValues2 = dBServiceRepoObj.GetColumnData(DBobj2.DbServerName, DBobj2.DBName, DBobj2.TableName, DBobj2.SqlQuery, DBobj2.ColumnName, DBobj2.DatabaseObjectType, DBobj2.StoredProcedureName, DBobj2.SpParameterValue, DBobj2.AuthenticationType, DBobj2.UserName, DBobj2.Password);
ComparisionOutput ListObj = new ComparisionOutput();
IDBSourceBL stringMetricObj = new DBSourceBL();
ListObj = stringMetricObj.CompareColumnValues(ColumnValues1, ColumnValues2, "scheduledjob");
string FilePath = "E:\\ScheduledDBReport\\";
string FileName = FilePath + "ScheduledDBReport_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
IExcelDocumentService excelDocumentServiceObj = new ExcelDocumentService();
excelDocumentServiceObj.DataTableToExcel(ListObj.RowSet, FileName);
}
public async Task <string> ExcelExport(string langId)
{
AppSettings.SetSiteLanguage(langId);
var parentDirectory = Directory.GetParent(Environment.CurrentDirectory).FullName;
var fileName = string.Format("OperatingRooms_{0}.xlsx", Guid.NewGuid().ToString());
FileStream fs = new FileStream(Path.Combine(parentDirectory, "Surgicalogic.Web", "static", fileName), FileMode.CreateNew);
var excelService = new ExcelDocumentService();
var items = await _operatingRoomStoreService.GetExportAsync <OperatingRoomExportModel>();
await excelService.WriteAsync(fs, "Worksheet", items, typeof(OperatingRoomExportModel), System.Globalization.CultureInfo.CurrentCulture);
return(fileName);
}
private void btnAbrir_Click(object sender, EventArgs e)
{
using (var dialog = new OpenFileDialog())
{
dialog.Filter = "Excel Files|*.xls;*.xlsx;";
var result = dialog.ShowDialog();
if (result == DialogResult.OK)
{
txtLocal.Text = dialog.FileName;
FillDataGrid(dialog.FileName);
ExcelDocumentService.DeleteExcelDocument(dialog.FileName);
MessageBox.Show("Documento carregado com sucesso. Após o carregamento o arquivo foi removido.");
}
}
}
public async Task <string> ExcelExport(string langId)
{
AppSettings.SetSiteLanguage(langId);
var parentDirectory = Directory.GetParent(Environment.CurrentDirectory).FullName;
var fileName = string.Format("Equipments_{0}.xlsx", Guid.NewGuid().ToString());
FileStream fs = new FileStream(Path.Combine(parentDirectory, "Surgicalogic.Web", "static", fileName), FileMode.CreateNew);
var excelService = new ExcelDocumentService();
var items = await _equipmentStoreService.GetExportAsync <EquipmentExportModel>();
foreach (var item in items)
{
item.IsPortable = item.IsPortable == "+" ? Resources.Resource.Yes : Resources.Resource.No;
}
await excelService.WriteAsync(fs, "Worksheet", items, typeof(EquipmentExportModel), System.Globalization.CultureInfo.CurrentCulture);
return(fileName);
}
private void BuildDocument(IEnumerable <Order> orders, DocumentType documentType, Stream stream)
{
switch (documentType)
{
case DocumentType.Excel:
{
var documentService = new ExcelDocumentService();
documentService.WriteDocument(orders, stream);
break;
}
case DocumentType.Xml:
{
var documentService = new XmlDocumentService();
documentService.WriteDocument(orders, stream);
break;
}
default:
throw new ArgumentException($"Document type: {documentType}. Type not found.", nameof(documentType));
}
}
//Exemplo de implementação para escrita de objetos fortemente tipados para excel
private void btnCriar_Click(object sender, EventArgs e)
{
if (dgvExcelDocument.Rows.Count > 0)
{
var path = Path.GetDirectoryName(txtLocal.Text);
var filePath = $"{path}\\Signus_{DateTime.Now.ToString("dd-MM-yyyy-hh-mm-ss")}.xlsx";
using (var ExcelDocumentService = new ExcelDocumentService(filePath, new OpenXmlApplication(filePath)))
{
var lst = new List <Signus>();
foreach (DataGridViewRow row in dgvExcelDocument.Rows)
{
lst.Add(row.DataBoundItem as Signus);
}
ExcelDocumentService.WriteExcelDocument(lst);
}
ClearDataGrid();
}
else
{
MessageBox.Show("Carregue algum documento antes de criar.");
}
}
public ComparisionOutput CompareColumns(List <DataSourceColumnList> dataSourceColumnList, string RequestType = "immediate")
{
try
{
ComparisionOutput finalOutput = new ComparisionOutput();
finalOutput.RowSet = new DataTable();
IExcelDocumentService excelDocumentServiceObj = new ExcelDocumentService();
List <ExcelDocument> excelDocuments = new List <ExcelDocument>();
foreach (var item in dataSourceColumnList)
{
ExcelDocument excelDoc = new ExcelDocument();
if (RequestType == "immediate")
{
if (item.DataSourceType == "file-upload")
{
//excelDoc = IntermediateCache<ExcelDocumentDTO>.Current.Get(item.Source, excelDoc);
var bytes = Convert.FromBase64String(item.FilePath.Replace("data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,", ""));
excelDoc.Table = excelDocumentServiceObj.ExcelToDataTableUsingExcelDataReader(new MemoryStream(bytes));
excelDoc.FileName = item.FileName;
}
else
{
excelDoc.Table = DbNamesObj.GetColumnData(item.DbServerName, item.DBName, item.TableName, item.SqlQuery, item.ColumnName, item.DatabaseObjectType, item.StoredProcedureName, item.SpParameterValue, item.AuthenticationType, item.UserName, item.Password);
}
excelDoc.Source = item.Source;
excelDocuments.Add((ExcelDocument)excelDoc.Clone());
}
else
{
excelDoc.FileName = item.FileName;
excelDoc.Source = item.Source;
excelDoc.Table = excelDocumentServiceObj.ExcelToDataTableUsingExcelDataReader(item.FileName);
excelDocuments.Add(excelDoc);
}
}
string commonCol1 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).Select(y => y.CommonColumn).FirstOrDefault();
string commonCol2 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).Select(y => y.CommonColumn).FirstOrDefault();
List <DataTableFilter> source1DataTableFilterDTO = dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).FirstOrDefault().DataTableFilterList == null ? new List <DataTableFilter>() : dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).FirstOrDefault().DataTableFilterList;
List <DataTableFilter> source2DataTableFilterDTO = dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).FirstOrDefault().DataTableFilterList == null ? new List <DataTableFilter>() : dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).FirstOrDefault().DataTableFilterList;
List <DataColumns> ColumnNameList1 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource1")).Select(y => y.ColumnNameList).FirstOrDefault();
List <DataColumns> ColumnNameList2 = dataSourceColumnList.Where(y => y.Source.Contains("DataSource2")).Select(y => y.ColumnNameList).FirstOrDefault();
var TuppledOutput0 = DataTableRowEqualizer.RemoveColumnDuplicatesList(ColumnNameList1, ColumnNameList2);
ColumnNameList1 = TuppledOutput0.Item1;
ColumnNameList2 = TuppledOutput0.Item2;
DataTable dataTable1 = excelDocuments.Where(y => y.Source.Contains("DataSource1")).FirstOrDefault().Table;
DataTable dataTable2 = excelDocuments.Where(y => y.Source.Contains("DataSource2")).FirstOrDefault().Table;
var TuppledOutput1 = DataTableRowEqualizer.RemoveColumnDuplicatesDatable(dataTable1, dataTable2);
dataTable1 = TuppledOutput1.Item1;
dataTable2 = TuppledOutput1.Item2;
var TuppledOutput2 = FilterDatatable.FilterDataTable(dataTable1, dataTable2, source1DataTableFilterDTO, source2DataTableFilterDTO);
dataTable1 = TuppledOutput2.Item1;
dataTable2 = TuppledOutput2.Item2;
if (string.IsNullOrWhiteSpace(commonCol1) || string.IsNullOrWhiteSpace(commonCol2))
{
int IteratrionLength = ColumnNameList1.Count() > ColumnNameList2.Count() ? ColumnNameList2.Count() : ColumnNameList1.Count();
var TuppledOutput = DataTableRowEqualizer.EqualizeDatable(dataTable1, dataTable2);
dataTable1 = TuppledOutput.Item1;
dataTable2 = TuppledOutput.Item2;
for (int i = 0; i < IteratrionLength; i++)
{
string Col1 = ColumnNameList1[i].ColumnName.ToString();
string Col2 = ColumnNameList2[i].ColumnName.ToString();
DataView view = new DataView(dataTable1);
DataTable crunchDataTable1 = view.ToTable(false, ColumnNameList1[i].ColumnName.ToString());
view = new DataView(dataTable2);
DataTable crunchDataTable2 = view.ToTable(false, ColumnNameList2[i].ColumnName.ToString());
DataTable dataTableOutput = stringMetricObj.StringComparisonResult(crunchDataTable1, crunchDataTable2, "ExelFile", Col1, Col2, i + 1);
if (i == 0)
{
finalOutput.RowSet = dataTableOutput.Copy();
}
else
{
finalOutput.RowSet = MergeDataTable.MergeDataTables(finalOutput.RowSet, dataTableOutput);
}
}
//if (RequestType == "immediate")
//{
// DataTable ChartDataTable = new DataTable();
// ChartDataTable.Merge(MergeDataTable.MergeDataTablesPieChart(finalOutput.RowSet));
// finalOutput.RowSet = MergeDataTable.CalculatePieChartNumbers(ChartDataTable);
//}
}
else
{
finalOutput.RowSet = JoinedDataTableResultset(dataTable1, dataTable2, commonCol1, commonCol2, ColumnNameList1, ColumnNameList2);
}
finalOutput.ColumnSet = finalOutput.RowSet.Columns.Cast <DataColumn>().Select(col => Convert.ToString(col)).ToList();
//if (RequestType == "immediate")
//{
// IntermediateCache<DataTable>.Current.Set("ComparisionOutputGrid", finalOutput.RowSet);
//}
return(finalOutput);
}
catch (Exception ex)
{
throw ex;
}
}
public ExcelDocumentServiceTest()
{
excelDocumentService = new ExcelDocumentService();
}
