Small is New Big in C#

Coding

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;
        }
    }

Advertisements

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#

Import and Export Excel File to SQL Server

leave a comment »

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use


EXEC sp_makewebtask 
	@outputfile = 'd:\testing.xls', 
	@query = 'Select * from Database_name..SQLServerTable', 
	@colheaders =1, 
	@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)

5 To export data to new EXCEL file with heading(column names), create the following procedure


create procedure proc_generate_excel_with_columns
(
	@db_name	varchar(100),
	@table_name	varchar(100),	
	@file_name	varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select 
	@columns=coalesce(@columns+',','')+column_name+' as '+column_name 
from 
	information_schema.columns
where 
	table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file 
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

Written by nauhil

July 22, 2010 at 12:02 pm

Posted in Sql Server

Access Class

leave a comment »

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace Access
{
    class Access
    {
        private OleDbConnection conn;
        private string connstring;

        public Access(string DBPath)
        {
            connstring = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source="; //Microsoft.ACE.OLEDB.12.0  Microsoft.Jet.OleDb.4.0 does not support Access 2007
            connstring += DBPath;
            conn = new OleDbConnection(connstring);
            conn.Open();
        }

        public OleDbConnection DBConn()
        {
            conn.Open();
            return conn;
        }

        public void Close()
        {
            conn.Close();
        }

        public DataTable SelectToDataTable(string sqlcomm)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
         
        }

        public DataSet SelectToDataSet(string sqlcomm, string tableName)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            DataSet ds = new DataSet();
            ds.Tables.Add(tableName);
            adapter.Fill(ds, tableName);
            return ds;
        }

        public DataSet SelectToDataSet(string sqlcomm, string tableName, DataSet dsName)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            ds = dsName;
            adapter.Fill(dsName, tableName);
            return ds;
        }

        public OleDbDataAdapter SelectToOleDbDataAdapter(string sqlcomm)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand comm = new OleDbCommand(sqlcomm, conn);
            adapter.SelectCommand = comm;
            return adapter;
        }

        public bool ExecuteSQLNonquery(string sqlcomm)
        {
            OleDbCommand cmd = new OleDbCommand(sqlcomm, conn);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}

Written by nauhil

July 17, 2010 at 10:28 pm

Posted in Levels Of Details

How to get windows service start property

leave a comment »

using System;
using System.Collections.Generic;
using System.Text;
using System.Management;

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {
            ManagementObjectSearcher s =
                new ManagementObjectSearcher("root\\CIMV2","SELECT * FROM Win32_Service",
                new EnumerationOptions(
                null, System.TimeSpan.MaxValue,
                1, true, false, true,
                true, false, true, true));
            foreach (ManagementObject service in s.Get())
            {
                Console.WriteLine(service.Path.ToString());
               Console.WriteLine( service.GetPropertyValue("StartMode").ToString());
            }
            Console.ReadLine();
        }
    }
}

Written by nauhil

July 9, 2010 at 8:52 am

Posted in Levels Of Details

How to create customer Timer

leave a comment »

C# Verstion:

        private DateTime dt = new DateTime(2010, 06, 30, 00, 00, 01, 00);
        private System.Timers.Timer timer = new System.Timers.Timer();
        private string nowtime;
        public delegate void TimeString(string timestr);

        public void UpdateTimeString(string str)
        {
            label1.Text = str.ToString();
        }

        private void TimeTicking(object sender, System.Timers.ElapsedEventArgs e)
        {
            dt = dt.AddSeconds(1.0);
            nowtime = dt.ToString("HH:mm:ss");
            object[] para = { nowtime };
            this.Invoke(new TimeString(UpdateTimeString), para);
        }

        private void TimeTick()
        {
            timer.Interval = 1000.0;
            timer.Enabled = true;
            timer.Elapsed += new ElapsedEventHandler(TimeTicking);
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            this.TimeTick();
        }

VB.NET Version:

Private dt As New DateTime(2010, 6, 30, 0, 0, 1, 0)
Private timer As New System.Timers.Timer()
Private nowtime As String

Public Delegate Sub TimeString(timestr As String)

Public Sub UpdateTimeString(str As String)
label1.Text = str.ToString()
End Sub

Private Sub TimeTicking(sender As Object, e As System.Timers.ElapsedEventArgs)
dt = dt.AddSeconds(1.0)
nowtime = dt.ToString("HH:mm:ss")
Dim para As Object() = {nowtime}
Me.Invoke(New TimeString(AddressOf UpdateTimeString), para)
End Sub

Private Sub TimeTick()
timer.Interval = 1000.0
timer.Enabled = True
AddHandler timer.Elapsed, New ElapsedEventHandler(AddressOf TimeTicking)
End Sub

Private Sub Form3_Load(sender As Object, e As EventArgs)
Me.TimeTick()
End Sub


Written by nauhil

June 30, 2010 at 1:47 pm

Posted in Levels Of Details