Friday, September 4, 2015

Export to excel in c# from list and using Linq filter

Here with I am giving simple List collection to export to excel sheet using .Net c# coding. You may find lot export to excel coding in other website. I am not going to give any new in this. I am also giving here with simple List collection with basic LINQ filter and export to excel sheet.
Note: Include the Microsoft.Office.Interop.Excel in your project.

List Collection With LINQ Filter:
        Eg: lstLocal = lstFlspc.Where(m => m.ClientLocation == “Chennai”).ToList();


Export to Excel in c#.net with List and LINQ Filter Query:



        public void ExportToExcel(List<FileInfoLocal> lst)
        {
            try
            {
//Filter in List collection
              lstLocal = lstFlspc.Where(m => m.ClientPath.Path == child.Text).ToList();


              Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

              if (xlApp == null)
              {
                  MessageBox.Show("Excel is not properly installed!!");
                  return;
              }    

              Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
              Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
              object misValue = System.Reflection.Missing.Value;

              xlWorkBook = xlApp.Workbooks.Add(misValue);
              xlWorkSheet =(Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                  

              int iRow = 1, iColumn = 1;
              //Header Test
              xlWorkSheet.Cells[iRow, iColumn] = "Column1 Name";
              xlWorkSheet.Cells[iRow, iColumn + 1] = "Column2 Name";
              xlWorkSheet.Cells[iRow, iColumn + 2] = "Column3 Name";
              xlWorkSheet.Cells[iRow, iColumn + 3] = "Column4 Name";
              xlWorkSheet.Cells[iRow, iColumn + 4] = "Column5 Name";
              iRow++;
 
// Filter in List collection using LINQ

              if (lstLocal!= null)
              {
              foreach (var item in lstLocal)
                {
                  // .. Add other parameters here. Body Text
                  xlWorkSheet.Cells[iRow, iColumn] = item.Name;
                  xlWorkSheet.Cells[iRow, iColumn + 1] = item.Date;
                  xlWorkSheet.Cells[iRow, iColumn + 2] = item.Path;
                  xlWorkSheet.Cells[iRow, iColumn + 3] = item.UserName;
                  xlWorkSheet.Cells[iRow, iColumn + 4] = item.Desc;
                  iRow++;                                   
                }
              }

             string sExcelFile = Directory.GetCurrentDirectory() + "Report.xls";

             xlWorkBook.SaveAs(sExcelFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
             xlWorkBook.Close(true, misValue, misValue);
             xlApp.Quit();

             releaseObject(xlWorkSheet);
             releaseObject(xlWorkBook);
             releaseObject(xlApp);

             MessageBox.Show("Excel file created successfully , you can find the file " + sExcelFile, "Export To Excell");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }



        // Release the Excel object from memory
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

No comments: