Wednesday 30 October 2013

Print Data Table data into Excel in c#.net

NameSpace--

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Word;

Source Code--

 private void printExcelReport(DataTable dtFarmDetail)
        {
            //Declare Excel variable

            object misValue = System.Reflection.Missing.Value;
            Excel.Application xlApp = new Excel.Application();
            Excel.Worksheet xlWorkSheet = new Microsoft.Office.Interop.Excel.Worksheet();
            Excel.Workbook xlWorkBook;
            Excel.Range chartRange;

            //Declare value
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            int Rowcount = dtFarmDetail.Rows.Count;
            int Colcount = dtFarmDetail.Columns.Count;

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            //Write Header
            for (int p = 1; p <= dtFarmDetail.Columns.Count; p++)
            {
                xlWorkSheet.Cells[1, p] = dtFarmDetail.Columns[p - 1].ColumnName;
            }

            //Assing data in array
            object[,] arrFarm = new object[dtFarmDetail.Rows.Count, dtFarmDetail.Columns.Count];
            for (int i = 0; i < dtFarmDetail.Rows.Count; i++)
            {
                for (int j = 0; j < dtFarmDetail.Columns.Count; j++)
                {
                    string cellvalues = dtFarmDetail.Rows[i][j].ToString();
                    string tickcross = "";
                    if (j > 10)
                    {
                        if (cellvalues == "Yes")
                        {
                            tickcross = "ü";
                        }
                        if (cellvalues == "No" || cellvalues == "")
                        {
                            tickcross = "û";
                        }                      
                    }
                    else
                    { tickcross = cellvalues; }

                    arrFarm[i, j] = tickcross;
                }
            }

            //Write data in sheet
            if (dtFarmDetail.Rows.Count > 0)
            {
                xlWorkSheet.Range[xlWorkSheet.Cells[2,1], xlWorkSheet.Cells[dtFarmDetail.Rows.Count, dtFarmDetail.Columns.Count]] = arrFarm;
                dtFarmDetail.Rows.Clear();
            }

            //Color header
            chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]);
            chartRange.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]).Font.Bold = true;
            chartRange.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]).Font.Size = 12;
            chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
           
            //Set Alingment
            chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[2 + Rowcount, Colcount]);
            chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            //Assign font and size
            chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]);
            chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Name = "Wingdings";
            chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Bold = true;
            chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Size = 12;
            chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            //write sheet
            chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin;
            chartRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).Weight = Excel.XlBorderWeight.xlThin;
            chartRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin;
            chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin;
            chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin;
            chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin;
            xlWorkSheet.Cells.EntireColumn.AutoFit();

            xlApp.Visible = true;
            Marshal.ReleaseComObject(xlWorkSheet);
            Marshal.ReleaseComObject(xlWorkBook);
            Marshal.ReleaseComObject(xlApp);
            xlWorkSheet = null;
            xlWorkBook = null;
            xlApp = null;
            System.GC.Collect();
       
        }

No comments:

Post a Comment

Excel Sort values in ascending order using function TEXTJOIN

 Excel ::  Text ::  1,3,5,2,9,5,11 Result :: 1,2,3,5,5,9,11 Formula ::     TEXTJOIN ( ",",1,SORT(MID(SUBSTITUTE( A1 ,","...