Wednesday, May 30, 2012

Code for exporting data into excel

 This is the code for exporting data into excel.

int rowID = 2;
            Excel.Application xlApp;
            Excel._Workbook workBook;
            Excel._Worksheet workSheet;
            object misValu = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();
            workBook = xlApp.Workbooks.Add(misValu);
            workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
            workSheet.Name = "Church List";

            workSheet.Cells[1, 1] = "Church ID";
            workSheet.Cells[1, 2] = "Church Name";
            workSheet.Cells[1, 3] = "Centre Name";
            workSheet.Cells[1, 4] = "Church Address";
            workSheet.Cells[1, 5] = "Panchayath";
            workSheet.Cells[1, 6] = "Village";
            workSheet.Cells[1, 7] = "Secretary";
            workSheet.Cells[1, 8] = "Phone";
            workSheet.Cells[1, 9] = "Email";
            workSheet.Cells[1, 10] = "Year Formulated";
            workSheet.Cells[1, 11] = "Number Of Members";
            workSheet.Cells[1, 12] = "Have Building";
            workSheet.Cells[1, 13] = "Have Parsonage";
            workSheet.Cells[1, 14] = "Have Cemetry";

            Excel.Range cell = xlApp.ActiveCell;
            cell.EntireRow.Font.Bold = true;
            cell.EntireRow.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Beige);

            churchs.ForEach(c =>
            {
                CentreDetails centre = dataBase.CentreDetailsCollection.GetCentreByID(c.CentreID);
                workSheet.Cells[rowID, 1] = c.ChurchID.ToString();
                workSheet.Cells[rowID, 2] = c.ChurchName;
                workSheet.Cells[rowID, 3] = centre != null ? centre.CentreName : " - ";
                workSheet.Cells[rowID, 4] = c.ChurchAddress.Place + ", " + c.ChurchAddress.District + ", " + c.ChurchAddress.State + ", " + c.ChurchAddress.Country + ", " + c.ChurchAddress.Pin;
                workSheet.Cells[rowID, 5] = c.Panchayath;
                workSheet.Cells[rowID, 6] = c.Village;
                workSheet.Cells[rowID, 7] = c.Secretory;
                workSheet.Cells[rowID, 8] = c.Phone.ToString();
                workSheet.Cells[rowID, 9] = c.Email;
                workSheet.Cells[rowID, 10] = c.YearFormulated.ToShortDateString();
                workSheet.Cells[rowID, 11] = c.NumberOfMembers.ToString();
                workSheet.Cells[rowID, 12] = c.OwnBuilding ? "Yes" : "No";
                workSheet.Cells[rowID, 13] = c.HaveParsonage ? "yes" : "No";
                workSheet.Cells[rowID, 14] = c.HaveCemetry ? "Yes" : "No";
                rowID++;
            });
            try
            {

                SaveFileDialog s = new SaveFileDialog();
                s.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                s.Filter = "xls|*.xls";

                DialogResult result = s.ShowDialog();
                if (result == DialogResult.OK)
                {
                    string name = s.FileName;
                    workBook.SaveAs(name, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                }
                else
                {
                  
                }
            }
            catch
            {
                MessageBox.Show("File is not accessible", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            xlApp.Quit();

No comments:

Post a Comment