Small is New Big in C#

Coding

Archive for the ‘Tips of C#’ Category

Create Customization Header Excel File – Excel Class

leave a comment »

   
      class ExcelFile
    {
        private Microsoft.Office.Interop.Excel.Application app = null;
        private Microsoft.Office.Interop.Excel.Workbook workbook = null;
        private Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
        private Microsoft.Office.Interop.Excel.Range workSheet_range = null;

        public ExcelFile()
        {

        }

        public void excelFile()
        {
            try
            {
                app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = false;
                workbook = app.Workbooks.Add(1);
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1];

            }
            catch (Exception e)
            {
                Console.Write("Error");
            }
            finally
            {

            }
        }

        public void MergeCell(int row,string start,string end,object value)
        {
            Microsoft.Office.Interop.Excel.Range cell = worksheet.get_Range(start, end);
            cell.Value2 = null; //清空单元格中的数据
            cell.Merge(Missing.Value);//拆分单元格所对应的方法为UnMerge(Missing.Value)
            //cell.Name=name;
            cell.Value2 = value;

            workSheet_range = worksheet.get_Range(start, end);
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            if (row == 4)
            { workSheet_range.Font.Bold = true; }

        }

        public void createHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor,int maxrow)
        {
            worksheet.Cells[row, col] = htext;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            workSheet_range.Merge(mergeColumns);
            switch (b)
            {
                case "YELLOW":
                    workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
                    break;
                case "GRAY":
                    workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                    break;
                case "GAINSBORO":
                    workSheet_range.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
                    break;
                case "Turquoise":
                    workSheet_range.Interior.Color = System.Drawing.Color.Turquoise.ToArgb();
                    break;
                case "PeachPuff":
                    workSheet_range.Interior.Color = System.Drawing.Color.PeachPuff.ToArgb();
                    break;
                default:
                    break;

            }

            if (row != 1 && row != maxrow)
            {
                workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            }
            else if (row != maxrow)
            { workSheet_range.Font.Size = 20; }

            workSheet_range.Font.Bold = font;
            workSheet_range.ColumnWidth = size;
            workSheet_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
        

            if (fcolor.Equals(""))
            {
                workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
            }
            else
            {
                workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
            }
        
        }

        public void addData(int row, int col, string data, string cell1, string cell2, string format)
        {
            worksheet.Cells[row, col] = data;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.NumberFormat = format;
            workSheet_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            workSheet_range.Font.Bold = true;
        }

        public bool ExportExcelDS(DataSet ds)
        {
            System.Data.DataTable dt = ds.Tables[0];

            int maxCol = dt.Columns.Count;
            int maxRow = dt.Rows.Count;

            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[maxRow + 4, maxCol]).Borders.LineStyle = 1;
            worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[maxRow + 4, maxCol]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
          
           
            if (dt.Rows.Count == 0)
                return false;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    object dtType = dt.Rows[i][j].GetType();
                    if (dt.Rows[i][j].GetType() == typeof(string))
                    {
                        app.Cells[i + 5, j + 1] = "’" + dt.Rows[i][j].ToString();
                       
                    }
                    else
                    {
                        string temp = Convert.ToString(dt.Rows[i][j]);
                        app.Cells[i + 5, j + 1] = Convert.ToString(dt.Rows[i][j]);
                    }
                }
            }

            return true;

        }

        public bool ExportExcelDGV(DataGridView dgv)
        {
           

            int maxCol = dgv.ColumnCount;
            int maxRow = dgv.Rows.Count;

            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[maxRow + 4, maxCol]).Borders.LineStyle = 1;
            worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[maxRow + 4, maxCol]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
           

            if (dgv.Rows.Count == 0)
                return false;

            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                for (int j = 0; j < dgv.Columns.Count; j++)
                {
                    object dtType = dgv.Rows[i].Cells[j].GetType();
                    if (dgv.Rows[i].Cells[j].GetType() == typeof(string))
                    {
                        app.Cells[i + 5, j + 1] = "’" + dgv.Rows[i].Cells[j].Value.ToString();

                    }
                    else
                    {
                        string temp = Convert.ToString(dgv.Rows[i].Cells[j].Value);
                        app.Cells[i + 5, j + 1] = Convert.ToString(dgv.Rows[i].Cells[j].Value);
                    }
                }
            }

            return true;

        }

        public bool ExportExcelDataTable(System.Data.DataTable dt)
        {

            int maxCol = dt.Columns.Count;
            int maxRow = dt.Rows.Count;

            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[maxRow + 4, maxCol]).Borders.LineStyle = 1;
            worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[maxRow + 4, maxCol]).HorizontalAlignment = XlHAlign.xlHAlignCenter;

            if (dt.Rows.Count == 0)
                return false;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    object dtType = dt.Rows[i][j].GetType();
                    if (dt.Rows[i][j].GetType() == typeof(string))
                    {
                        app.Cells[i + 5, j + 1] = "’" + dt.Rows[i][j].ToString();

                    }
                    else
                    {
                        string temp = Convert.ToString(dt.Rows[i][j]);
                        app.Cells[i + 5, j + 1] = Convert.ToString(dt.Rows[i][j]);
                    }
                }
            }

            worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[maxRow + 4, maxCol]).ColumnWidth = 10;
            worksheet.get_Range(worksheet.Cells[5, 1], worksheet.Cells[maxRow + 4, maxCol]).EntireColumn.AutoFit();
            return true;

        }

        public void Close()
        {
            app = null;
            GC.Collect();
        }

        public void Show()
        {
            app.Visible = true;
        }
    }

Written by nauhil

September 10, 2010 at 11:56 am

Posted in Tips of C#

Make TextBox Number Only With Length Limit

leave a comment »

    //Detect Value
        private void txtID_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (Char.IsControl(e.KeyChar) || Char.IsDigit(e.KeyChar))
            { e.Handled = false; }
            else
            { e.Handled = true; }
        }

    //Check TextBox.Text Length
        private string CollectionIDCheck(string id,int length)
        {
            Regex check = new Regex("^[0-9]{" + length.ToString() +"}");
            int count;
            if (!check.IsMatch(id))
            {

                if (id.Length < length)
                {
                    count = length – id.Length;
                    string zero = "0";
                    for (int i = 1; i < count; i++)
                        zero = "0" + zero;
                    id = zero + id;
                }
                else if (id.Length > length)
                {
                    count = id.Length – length;
                    id = id.Substring(count,
length);
                }
            }
            return id;
        }

Written by nauhil

August 30, 2010 at 10:48 am

Posted in Tips of C#

Update DataSet using SqlDataAdapter

leave a comment »

        private DataSet ds;
        private SqlConnection sqlconn;
        private SqlCommand comm;
        private SqlDataAdapter sqlda;
        private SqlCommandBuilder commBuilder;

        public SqlConnection getcon()
        {
            string M_str_sqlcon = "Data Source=(local);Database=Northwind;user Id=sa;PWD=";
            SqlConnection mycon = new SqlConnection(M_str_sqlcon);
            return mycon;
        }

        private void btnExportSql_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < ds.Tables["Categories"].Rows.Count; i++)
            {
                ds.Tables["Categories"].Rows[i][1] = "Teeeeest";
            }
            sqlda.Update(ds, "Categories");
            dataGridView1.DataSource = ds.Tables["Categories"];
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            sqlconn = this.getcon();
            comm = sqlconn.CreateCommand();
            comm.CommandText = "SELECT CategoryID,CategoryName, Description FROM Categories";
            sqlda = new SqlDataAdapter(comm);
            commBuilder = new SqlCommandBuilder(sqlda);
            ds = new DataSet();
            sqlda.Fill(ds, "Categories");
            dataGridView1.DataSource = ds.Tables["Categories"];
        }

Written by nauhil

August 27, 2010 at 1:16 pm

Posted in Tips of C#

Selecting Suggestion of Combobox

leave a comment »



private SqlConnection conn;

public SqlConnection GetSqlConn()
{
try
{
string strconn = "Server=local;Database=AdventureWorks;User id=sa;PWD=";
conn = new SqlConnection(strconn);
conn.Open();
return conn;
}
catch (Exception ex)
{
return null;
}
}


public void cboxBind(string sqlComm, string tableName, string tableColumn, ComboBox cbox)
{
DataSet ds = this.GetDataSet(sqlComm, tableName);
cbox.DataSource = ds.Tables[tableName];
cbox.DisplayMember = tableColumn;
}

public DataSet GetDataSet(string sqlComm, string tableName)
{
SqlConnection sqlconn = this.GetSqlConn();
SqlDataAdapter sqlda = new SqlDataAdapter(sqlComm, sqlconn);
DataSet ds = new DataSet();
sqlda.Fill(ds, tableName);
return ds;
}


public Form1()
{
InitializeComponent();

DataSet ds = this.GetDataSet("SELECT Distinct Name FROM Person.CountryRegion","Person.CountryRegion");
string[] list = new string[ds.Tables[0].Rows.Count];
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
list[i] = ds.Tables[0].Rows[i][0].ToString();
}

comboBox1.AutoCompleteCustomSource.AddRange(list);
comboBox1.AutoCompleteMode = System.Windows.Forms.AutoCompleteMode.SuggestAppend;
comboBox1.AutoCompleteSource = System.Windows.Forms.AutoCompleteSource.CustomSource;
}

private void Form1_Load(object sender, EventArgs e)
{
this.cboxBind("SELECT DISTINCT Name FROM Person.CountryRegion", "Person.CountryRegion","Name", comboBox1);

}

Written by nauhil

June 18, 2010 at 2:03 pm

Posted in Tips of C#

Crytal report print preview

leave a comment »

public SqlConnection getcon()
{
string M_str_sqlcon = "Server=172.18.39.48;Database=ApressFinancial;User id=sa;PWD=";
SqlConnection mycon = new SqlConnection(M_str_sqlcon);
mycon.Open();
return mycon;
}

public DataSet getds(string M_str_sqlstr, string M_str_table)
{
SqlConnection sqlcon = this.getcon();
SqlDataAdapter sqlda = new SqlDataAdapter(M_str_sqlstr, sqlcon);
DataSet myds = new DataSet();
sqlda.Fill(myds, M_str_table);
return myds;
}

public ReportDocument DailyCrystalReports(string creportName, string sum_sql, System.Data.DataTable tableName)
{
string P_str_creportPath = System.Windows.Forms.Application.StartupPath.Substring(0, System.Windows.Forms.Application.StartupPath.Substring(0, System.Windows.Forms.Application.StartupPath.LastIndexOf("\\")).LastIndexOf("\\"));
P_str_creportPath += @"\DailyHB\CrReport\" + creportName; //Your report path
ReportDocument doc = new ReportDocument();
doc.Load(P_str_creportPath);
doc.SetDataSource(tableName);
return doc;
}

private void frmDailyCenterIssue_Load(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Maximized;
this.FormBorderStyle = FormBorderStyle.Sizable;

string sum_sql = "SELECT * FROM CenterIssueSummary WHERE SummaryDate = '" + frmSummaryCenterIssue.daily + "' Order By CenterNo"; //Change your sql
DataSet ds = this.GetDs(sum_sql, "CenterIssueSummary"); //Create DataSet
DataTable dt = ds.Tables[0]; //Get Table

crystalReportDaily.ReportSource = this.DailyCrystalReports("SummaryCenterIssue.rpt", sum_sql, sumDt);
}

Written by nauhil

June 13, 2010 at 10:57 pm

Posted in Tips of C#