Category: EXCEL

C# Open Excel File

On By semt20In C SHARP, EXCEL, visual studioLeave a comment 

        public static object OpenFile(string fileName)
        {
            if (!File.Exists(fileName))
            {
                MessageBox.Show("File not found");
                return null;
            }
            string connectionString = string.Empty;
            const string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
            const string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
            switch (Path.GetExtension(fileName))
            {
                case ".xls": //Excel 97-03
                    connectionString = string.Format(Excel03ConString, fileName);
                    break;
                case ".xlsx": //Excel 07
                    connectionString = string.Format(Excel07ConString, fileName);
                    break;
            }

            OleDbConnection con = new OleDbConnection(connectionString);
            con.Open();
            DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            var adapter = new OleDbDataAdapter(string.Format("select * from [{0}]", dt.Rows[0]["TABLE_NAME"].ToString()), connectionString);
            var ds = new DataSet();
            const string tableName = "excelData";
            adapter.Fill(ds, tableName);
            DataTable data = ds.Tables[tableName];
            return data;
        }
    }

Sql Query Excel File

On By semt20In EXCEL, SqlLeave a comment 
USE [master]
sp_configure 'show advanced options', 1; 
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1; 
RECONFIGURE; 
GO 




GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO 

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=F:\B.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

  SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source=F:\C.xls;Extended Properties=Excel 8.0')...[Sheet1$]

Sentez Dev Mizan Excele

On By semt20In EXCEL, sentez, SqlLeave a comment
excelde
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır…

 

profilerda mizanı çekerken kod:

 

exec sp_executesql N' if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp create table #GLReceiptBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,ForexDebit decimal(28,8),ForexCredit decimal(28,8),ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8),ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8))  insert into #GLReceiptBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,ForexDebit,ForexCredit,ForexDebitBalance,ForexCreditBalance ,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance,InflowQty,OutflowQty) select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,isnull(sum(AR.Debit),0) Debit,0 Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,isnull(sum(AR.ForexAmount),0)ForexDebit,0 ForexCredit,isnull(sum(AR.ForexAmount),0)ForexDebitBalance,0 ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,isnull(sum(AR.Quantity),0)InflowQty,0 OutflowQty  from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId)  left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId)  where  A.CompanyId = 5 and  (A.IsDeleted is null or A.IsDeleted = 0)  and  (AR.IsDeleted is null or AR.IsDeleted = 0)  and AR.Debit 0     and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5)  and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2         group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName
 union all select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,0 Debit,isnull(sum(AR.Credit),0) Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,0 ForexDebit,isnull(sum(AR.ForexAmount),0) ForexCredit,0 ForexDebitBalance,isnull(sum(AR.ForexAmount),0) ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,0 InflowQty,isnull(sum(AR.Quantity),0) OutflowQty  from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId)  left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId)  where  A.CompanyId = 5 and  (A.IsDeleted is null or A.IsDeleted = 0)  and  (AR.IsDeleted is null or AR.IsDeleted = 0)  and AR.Credit 0     and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5)  and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2         group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp  create table #GLAccountBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,DBalance decimal(28,8),CBalance decimal(28,8),ForexDebit decimal(28,8),ForexCredit decimal(28,8) ,ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8),FDBalance decimal(28,8),FCBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8) ,ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8),FCDBalance decimal(28,8),FCCBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLAccountBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,DBalance,CBalance,ForexDebit,ForexCredit ,ForexDebitBalance,ForexCreditBalance,FDBalance,FCBalance,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance ,FCDBalance,FCCBalance,InflowQty,OutflowQty) select CompanyId,AccountId,AccountCode,AccountName,isnull(sum(Debit),0)Debit,isnull(sum(Credit),0)Credit,isnull(sum(DebitBalance),0)DebitBalance,isnull(sum(CreditBalance),0)CreditBalance ,case when isnull(sum(DebitBalance),0)>isnull(sum(CreditBalance),0) then round(isnull(sum(DebitBalance),0)-isnull(sum(CreditBalance),0),2) else 0 end DBalance ,case when isnull(sum(DebitBalance),0)isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexDebitBalance),0)-isnull(sum(ForexCreditBalance),0),2) else 0 end FDBalance ,case when isnull(sum(ForexDebitBalance),0)isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateDebitBalance),0)-isnull(sum(ForexCalculateCreditBalance),0),2) else 0 end FCDBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)<isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateCreditBalance),0)-isnull(sum(ForexCalculateDebitBalance),0),2) else 0 end FCCBalance ,isnull(sum(InflowQty),0)InflowQty,isnull(sum(OutflowQty),0)OutflowQty  from #GLReceiptBalanceTmp  group by CompanyId,AccountId,AccountCode,AccountName  having round(isnull(sum(Debit),0),2) round(isnull(sum(Credit),0),2) select A.CompanyId,'''' [Şirket],isnull((select MF.ForexCode from Meta_Forex MF where MF.RecId=A.ForexId),'''') [Dvz],'''' [HDvz],A.AccountCode [Hesap Kodu],A.AccountName [Hesap Adı],A.SpecialCode [Özel Kod],0.0 [Devir Borç],0.0 [Devir Alacak],sum(isnull(B.Debit,0)) [Borç],sum(isnull(B.Credit,0)) [Alacak],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then sum(isnull(B.DBalance,0)) - sum(isnull(B.CBalance,0)) when sum(isnull(B.DBalance,0)) sum(isnull(B.CBalance,0)) then ''BB'' when sum(isnull(B.DBalance,0)) sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FDBalance,0)) - sum(isnull(B.FCBalance,0)),2) when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCBalance,0)) then ''BB'' when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCDBalance,0)) - sum(isnull(B.FCCBalance,0)),2)when sum(isnull(B.FCDBalance,0)) sum(isnull(B.FCCBalance,0)) then ''BB'' when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then ''AB'' else '''' end  [Hesaplanan Döviz Bakiye Tipi],sum(isnull(B.FCDBalance,0)) [Hesaplanan Döviz Borç Bakiye],sum(isnull(B.FCCBalance,0)) [Hesaplanan Döviz Alacak Bakiye],sum(isnull(B.InflowQty,0)) [Giriş Miktarı],sum(isnull(B.OutflowQty,0)) [Çıkış Miktarı],sum(isnull(B.InflowQty,0)) - sum(isnull(B.OutflowQty,0)) [Kalan],-1 CAccount  from Erp_GLAccount A with (nolock)  left join #GLAccountBalanceTmp B on (B.AccountId in(select AA.RecId from Erp_GLAccount AA where AA.CompanyId=A.CompanyId and AA.AccountCode like rtrim(A.AccountCode)+''%'' ))  where  A.CompanyId = 5 and  (A.IsDeleted is null or A.IsDeleted = 0)    group by A.CompanyId,A.ForexId,A.AccountCode,A.AccountName,A.SpecialCode  having round(isnull(sum(B.Debit),0),2) round(isnull(sum(B.Credit),0),2) order by A.AccountCode

if object_id(''tempdb..#GLReceiptTmp'') is not null drop table #GLReceiptTmp if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp  if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp  ',N'@0ARErp_GLReceiptItemReceiptDate1 datetime,@0ARErp_GLReceiptItemReceiptDate2 datetime',@0ARErp_GLReceiptItemReceiptDate1='2017-01-01 00:00:00',@0ARErp_GLReceiptItemReceiptDate2='2018-12-31 23:59:00'

Devexpress gridview export customised header

On By semt20In C SHARP, Devexpress, EXCEL, visual studioLeave a comment 
using DevExpress.XtraPrinting;
using DevExpress.Printing.ExportHelpers;
using DevExpress.Export;

private void simpleButton1_Click(object sender, EventArgs e) {
    // Ensure that the data-aware export mode is enabled.
    DevExpress.Export.ExportSettings.DefaultExportType = ExportType.DataAware;           
    // Create a new object defining how a document is exported to the XLSX format.
    XlsxExportOptionsEx options = new XlsxExportOptionsEx();
    // Subscribe to the CustomizeSheetHeader event. 
    options.CustomizeSheetHeader += options_CustomizeSheetHeader;
    // Export the grid data to the XLSX format.
    string file = "grid-export.xlsx";
    gridControl.ExportToXlsx(file, options);
    // Open the created document.
    System.Diagnostics.Process.Start(file);           
}

void options_CustomizeSheetHeader(DevExpress.Export.ContextEventArgs e) {
    // Create a new row.
    CellObject row = new CellObject();
    // Specify row values.
    row.Value = "The document is exported from the IssueList database.";
    // Specify row formatting.
    XlFormattingObject rowFormatting = new XlFormattingObject();
    rowFormatting.Font = new XlCellFont { Bold = true, Size = 14 };
    rowFormatting.Alignment = new DevExpress.Export.Xl.XlCellAlignment { HorizontalAlignment = DevExpress.Export.Xl.XlHorizontalAlignment.Center, VerticalAlignment = DevExpress.Export.Xl.XlVerticalAlignment.Top };
    row.Formatting = rowFormatting;
    // Add the created row to the output document.
    e.ExportContext.AddRow(new [] {row});
    // Add an empty row to the output document.
    e.ExportContext.AddRow();
    // Merge cells of two new rows. 
    e.ExportContext.MergeCells(new DevExpress.Export.Xl.XlCellRange(new DevExpress.Export.Xl.XlCellPosition(0, 0), new DevExpress.Export.Xl.XlCellPosition(5, 1)));
}