Small is New Big in C#

Coding

Archive for September 2010

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#