C# Open Excel File

On May 8, 2022 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;
        }
    }

EXCEL DISTINCT AMAÇLI FONKSIYON

On April 8, 2022April 8, 2022 By semt20In EXCELLeave a comment

İhtiyaç sebebi: Tabloda J alanında farklı olan değerlerin (text) kaç adet olduğunu görebilme.

LOGO EXCEL NAVIGATOR SMART KURULUMU

On May 7, 2020 By semt20In EXCEL, LOGO ERP, NAVIGATOR SMARTLeave a comment

logo 3 serisi için navigator smart kurulumu (lisans olmalı)

EXCEL SELECT SQL QUERY FROM CELL

On February 19, 2020 By semt20In EXCEL, SqlLeave a comment

C# excel dxgridview fast load

On June 19, 2019August 20, 2021 By semt20In C SHARPLeave a comment

//DXGRIDVIEW EXCEL ACMA HIZLI
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using Excel;
using System;
using System.Data;
using System.IO;
using System.Windows.Forms;
//F:\googledrive\programming\csharp içinde iki adet dll var  Excel.dll  ve ICSharpCode.SharpZipLib.dll referans olarak ekle




        public void SET_GC_DATASOURCE(DataTable DT, string GVNAME){
            try
            {
                if (DT == null || DT.Rows.Count <= 0)
                    throw new Exception("EXCEL VERİLERİ OKUNAMADI.");
                gridControl1.DataSource = DT;
                gridControl1.RefreshDataSource();
                gridControl1.Refresh();
                INITIALIZE_GRID(gridControl1, gridView1, new GRIDOPTIONS() { ALLOWSORT = true, CLEARSORTINFO = true, ENABLEGROUPPANELMENU = true, HORIZONTALSCROOLVISIBILITY = true, SHOWGROUPPANEL = true }, GVNAME);
                //for (int i = 0; i < COLUMNS.Length / 2; i++)
                //{
                //    _gv_EKSTRE.Columns[i].Caption = (string)COLUMNS[i, 0];
                //    _gv_EKSTRE.Columns[i].Visible = (bool)COLUMNS[i, 1];
                //}

            }
            catch (Exception E)
            {

                MessageBox.Show(E.Message, "HATA!", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
        }



    public string READ_FILE(string title){
            try
            {
                string[] fileformats = new string[] { "xls", "xlsx" };
                OpenFileDialog openFileDialogEKSTRE = new OpenFileDialog();
                openFileDialogEKSTRE.Title = title;
                //openFileDialogEKSTRE.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);//@"C:\Users\BilMark Yazılım\Desktop";//AppDomain.CurrentDomain.BaseDirectory;
                string filterText = string.Empty;
                for (int i = 0; i < fileformats.Length; i++)
                {
                    filterText += "(*." + fileformats[i] + "*)|*." + fileformats[i];
                    if (i != fileformats.Length - 1)
                        filterText += "|";
                }
                openFileDialogEKSTRE.Filter = "Excel Files|*.xls;*.xlsx;";
                //openFileDialogEKSTRE.FileName = "1001.xlsx";
                openFileDialogEKSTRE.FilterIndex = 0;
                openFileDialogEKSTRE.RestoreDirectory = true;

                if (openFileDialogEKSTRE.ShowDialog() == DialogResult.OK)
                {
                    return openFileDialogEKSTRE.FileName;
                }
                else return "closed";
            }
            catch (Exception E)
            {

                return null;
            }
        }





      public DataTable CONVERT_EXCEL_TO_DATATABLE(bool ISXLSX, bool ISFIRSTROWCOLUMNNAME, string PATH, int RETURNEDTABLENR){
            FileStream stream = File.Open(PATH, FileMode.Open, FileAccess.Read);
            IExcelDataReader excelReader = null;

            if (ISXLSX)
                excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            else
                excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

            excelReader.IsFirstRowAsColumnNames = ISFIRSTROWCOLUMNNAME;

            DataSet result = excelReader.AsDataSet();

            ////5. Data Reader methods
            //while (excelReader.Read())
            //{
            //    //excelReader.GetInt32(0);
            //}

            excelReader.Close();

            return result.Tables[RETURNEDTABLENR];}





       public void INITIALIZE_GRID(GridControl GC, GridView GV, GRIDOPTIONS GO, string XMLLAYOUTNAME){

            try
            {
                RESTORE_GRIDVIEW_LAYOUTS(GV, XMLLAYOUTNAME);
                //GV.RestoreLayoutFromXml(AppDomain.CurrentDomain.BaseDirectory + "\\Layouts\\" + XMLLAYOUTNAME + ".xml");
                if (GO.CLEARSORTINFO)
                    GV.SortInfo.Clear();
                GV.ClearColumnsFilter();
                GV.FocusedRowHandle = 0;
            }
            catch { }

            GV.OptionsBehavior.Editable = GO.EDITABLE;
            GV.OptionsBehavior.AllowAddRows = GO.ALLOWADDROW ? DevExpress.Utils.DefaultBoolean.True : DevExpress.Utils.DefaultBoolean.False;
            GV.OptionsBehavior.AllowDeleteRows = GO.ALLOWDELETEROW ? DevExpress.Utils.DefaultBoolean.True : DevExpress.Utils.DefaultBoolean.False;

            GV.OptionsCustomization.AllowFilter = GO.ALLOWFILTER;
            GV.OptionsCustomization.AllowQuickHideColumns = GO.ALLOWQUICKHIDECOLUMNS;
            GV.OptionsCustomization.AllowSort = GO.ALLOWSORT;

            GV.OptionsMenu.EnableColumnMenu = GO.ENABLECOLUMNMENU;
            GV.OptionsMenu.EnableFooterMenu = GO.ENABLEFOOTERMENU;
            GV.OptionsMenu.EnableGroupPanelMenu = GO.ENABLEGROUPPANELMENU;

            GV.OptionsSelection.MultiSelect = GO.MULTISELECT;
            if (GO.MULTISELECTMODECELLSELECT)
                GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.CellSelect;
            else if (GO.MULTISELECTMODECHECKBOXSELECT)
                GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.CheckBoxRowSelect;
            else if (GO.MULTISELECTMODEWORSELECT)
                GV.OptionsSelection.MultiSelectMode = GridMultiSelectMode.RowSelect;

            GV.OptionsView.ColumnAutoWidth = GO.COLUMNAUTOWIDTH;
            GV.OptionsView.ShowAutoFilterRow = GO.SHOWAUTOFILTERROW;
            GV.OptionsView.ShowGroupPanel = GO.SHOWGROUPPANEL;
            GV.OptionsView.ShowFilterPanelMode = GO.SHOWFILTERPANELMODE ? DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.ShowAlways : DevExpress.XtraGrid.Views.Base.ShowFilterPanelMode.Default;}





     public void RESTORE_GRIDVIEW_LAYOUTS(GridView GV, string XMLLAYOUTNAME){
            try
            {
                GV.RestoreLayoutFromXml(AppDomain.CurrentDomain.BaseDirectory + "\\Layouts\\" + XMLLAYOUTNAME + ".xml");
            }
            catch { }}




public class GRIDOPTIONS{
        #region OPTIONSBEHAVIOR
        public bool EDITABLE { get; set; }
        public bool ALLOWADDROW { get; set; }
        public bool ALLOWDELETEROW { get; set; }
        #endregion

        #region OPTIONSCUSTOMIZATION
        public bool ALLOWFILTER { get; set; }
        public bool ALLOWSORT { get; set; }
        public bool ALLOWQUICKHIDECOLUMNS { get; set; }
        #endregion

        #region OPTIONSMENU
        public bool ENABLECOLUMNMENU { get; set; }
        public bool ENABLEGROUPPANELMENU { get; set; }
        public bool ENABLEFOOTERMENU { get; set; }
        #endregion

        #region OPTIONSSELECTION
        public bool MULTISELECT { get; set; }
        public bool MULTISELECTMODECELLSELECT { get; set; }
        public bool MULTISELECTMODECHECKBOXSELECT { get; set; }
        public bool MULTISELECTMODEWORSELECT { get; set; }
        #endregion

        #region OPTIONSVIEW
        public bool COLUMNAUTOWIDTH { get; set; }
        public bool SHOWGROUPPANEL { get; set; }
        public bool SHOWAUTOFILTERROW { get; set; }
        public bool SHOWFILTERPANELMODE { get; set; }
        #endregion

        #region BEHAVIOR
        public bool HORIZONTALSCROOLVISIBILITY { get; set; }
        #endregion

        public bool CLEARSORTINFO { get; set; }
        public bool CLEARFILTERINFO { get; set; }}




        private void simpleButton1_Click(object sender, EventArgs e){
            gridControl1.DataSource = null;
            gridView1.Columns.Clear();
            string  FILEPATH = READ_FILE("gridView1");
            SET_GC_DATASOURCE(CONVERT_EXCEL_TO_DATATABLE(true, true, FILEPATH, 0), "gridView1");}

Sql Query Excel File

On June 13, 2019August 20, 2021 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$]

ASPXGRIDVIEW İşlemleri

On December 18, 2018December 19, 2019 By semt20In C SHARP, DevexpressLeave a comment

Gridviewde seçili satırın bilgisini alabilme :
protected void ASPxGridView1_SelectionChanged(object sender, EventArgs e)
{
var products = ASPxGridView1.GetSelectedFieldValues(“NAME”);
Label1.Text = products[0].ToString();
}

Gridviewin Sonuna İşlemler Seç eklemek için source da sona ekle

Gridview devexpress 7.2 sonrası için gecerli export Gridview
un altina ekle

un ustune ekle
  <Toolbars>
            <dx:GridViewToolbar EnableAdaptivity=“true”>
                <Items>
                    <dx:GridViewToolbarItem Command=“ExportToPdf” />
                    <dx:GridViewToolbarItem Command=“ExportToXls” />
                    <dx:GridViewToolbarItem Command=“ExportToXlsx” />
                    <dx:GridViewToolbarItem Command=“ExportToDocx” />
                    <dx:GridViewToolbarItem Command=“ExportToRtf” />
                    <dx:GridViewToolbarItem Command=“ExportToCsv” />
                </Items>
            </dx:GridViewToolbar>
        </Toolbars>

Gridview grup category combobox ve baglantisi
birinci sqldatasource_musteriler yarat ve bunu aspxgridviewe bind
Ikinci datasource sqldatasource_musterigruplari yarat bu bir yere bind olmayacak

Sqlden iki tablo yarat birinin adi musteri(id,adi,grupid) olsun
digeride musteri_grup (id,grup_adi) olsun

aspxgridview designerda columnsa tikla combobox column ekle captionunu grup yap, Fieldname grupid sec, sag tabda comboboxpropertiesde datasourceid musteri_gruplarini sec text field grup_adi , valuefield=id

Gridview Horizontal scrollbar

Datasource eklemede hata alırsan sol taraftan server explorerden modify deyip save passwordu seç

Gridview Required zorunlu alan ayari
Designerda ac ve columnsdan zorunlu alana tıkla ve sağda textboxpropertiesden en aşağıda validation settingste requiredi true yap

Master Detail Grid
ONCE 2 TANE SQLDATASOURCE VE 2 TANE ASPXGRIDVIEW YARAT BIRI MUSTERILER DIGERI MUSTERI HAREKETLER
MUSTERILERI NORMAL TABLODAN AL ASPXGRIDVIEW MUSTERILERIN SETTINGS DETAILDE DETAILROWU TRUE YAP,

MUSTERI HAREKETLERINIDE AL WHERE KISMINA TIKLA COLUMN MUSTERI_ID OPERATOR ” = ” , SOURCE SESSION , SESSION FIELD = MUSTERI_ID

VE DAHA SONRA KODA EKLE
protected void ASPxGridViewmusterihareketler_BeforePerformDataSelect(object sender, EventArgs e)
{
Session[“MUSTERI_ID”] = (sender as ASPxGridView).GetMasterRowKeyValue();
}

SON OLARAKDA ASPXGRIDVIEWMUSTERILERE TIKLA EDITTEMPLATE VE DETAILROWU SEC VE ASPXGRIDMUSTERIHAREKETLERINI ICINE SURUKLE VE END TEMPLATE YAP

Gridviewda son kolumun genişliğini max yapmak

<dx:GridViewDataTextColumn FieldName="ADI" VisibleIndex="2" Width=”100%”>

gridview detay masterda detay width sorunu once masterin altina ekle :
<dx:ASPxGridView Width=”100%”

sonra masterin son colomunun widthini %100 yap

sonra detaya ekle
<dx:ASPxGridView2 Width=”100%”

Excelde (Large) Kritere Göre En büyük Tarihi , 2nci En büyük Tarihi, 3ncu En büyük Tarihi Bulmak

On October 27, 2018December 19, 2019 By semt20In EXCELLeave a comment

=IFERROR(LARGE(IF($A$2:$A$9=A2,$B$2:$B$9),1),””)

=IFERROR(LARGE(IF($A$2:$A$9=A2,$B$2:$B$9),2),””)
=IFERROR(LARGE(IF($A$2:$A$9=A2,$B$2:$B$9),3),””)

Excel Kriterli Subtotal (Alttoplam)

On October 11, 2018December 19, 2019 By semt20In EXCELLeave a comment

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B3:B6,ROW(A3:A6)-MIN(ROW(A3:A6)),,1)),–(A3:A6=”çek”))

xml to excel

On May 18, 2018December 19, 2019 By semt20In EXCELLeave a comment

excel 2003 ama 2010dada test edildi ondada oldu
önce indir : https://www.microsoft.com/en-us/download/details.aspx?id=3108
sonra excelden
excel file options ->addins -> go -> browse ->
C:\Office Samples\OfficeExcel2003XMLToolsAddin\XmlTools.xla bul tamam de exceli kapat aç ve xmli aç okdir.

Blog of Sem

Tag: EXCEL