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