C# .NET - Export generic List into Excel with template file

Asked By Sujit Patil on 17-Oct-12 09:23 AM
Hello exprets,
I want to export the List<MyClass> into EXCEL.... USING windows application in C#......

I have successfully impliemented it without using template file.

But now i want to do it using template. user can provide a template file and data should be exported using that template.

Template may have formulas, some formatting which should be copied to new data file as is........
Can you please help me for same???

I have tried below code....... But its not working when we have lot of data in LIST...

I have hardcoded for loop with 250..... My list data has more than 500 data.........
For 250 it works but taking lot of time..........
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
 
      bool templateExists = false;
      Microsoft.Office.Interop.Excel._Workbook workbook;
      if (!string.IsNullOrEmpty(exportParam.XLTTemplateFileName) && System.IO.File.Exists(Core.ApplParameter.Instance.AppPath + exportParam.XLTTemplateFileName))
      {
        workbook = excelApp.Workbooks.Open(Core.ApplParameter.Instance.AppPath + exportParam.XLTTemplateFileName);
        templateExists = true;
      }
      else
        workbook = excelApp.Workbooks.Add(Type.Missing);
 
      // creating new Excelsheet in workbook
      Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
      // see the excel sheet behind the program
 
      if (exportParam.Preview)
        excelApp.Visible = true;
      // get the reference of first sheet. By default its name is Sheet1.
      // store its reference to worksheet
      if (templateExists == false)
        worksheet = workbook.Sheets["Sheet1"];
      worksheet = workbook.ActiveSheet;
      Microsoft.Office.Interop.Excel.Range range = worksheet.UsedRange;
      int col = range.Column;
      int row = range.Row;
 
      int totalRow = range.Rows.Count;
      int totalCol = range.Columns.Count;
 
      // storing header part in Excel
      if (templateExists == false)
      {
        // changing the name of active sheet
        worksheet.Name = "Export Data";
        int cnt = 1;
        for (int i = 0; i <= exportParam.lstColumnsToExport.Count - 1; i++)
        {
          worksheet.Cells[1, cnt] = exportParam.lstColumnsToExport[i];
          cnt++;
        }
      }
 
      //worksheet.Rows[i + totalRow].Select();
      //worksheet.Application.Selection.Copy();
      //excelApp.Selection.Insert();
 
      // storing Each row and column value to excel sheet
      //for (int i = 0; i < lstDataToEport.Count - 1; i++)
      for (int i = 0; i < 250; i++)
      {
        worksheet.Rows[i + totalRow].Select();
        worksheet.Application.Selection.Copy();
        worksheet.Rows[i + totalRow].Select();
        excelApp.Selection.Insert();
        int lrow = excelApp.ActiveCell.Row;
        int cell = excelApp.ActiveCell.Column + 1;
        worksheet.Cells[lrow, 1].Select();
        for (int j = 0; j < exportParam.lstColumnsToExport.Count; j++)
        {
          //worksheet.Cells[i + 2, j + 1] = lstDataToEport[i].GetValue(exportParam.lstColumnsToExport[j]);
          //TODO: Confirm...
          //worksheet.Cells[i + 3, j + 2] = lstDataToEport[i].GetValue(exportParam.lstColumnsToExport[j]);
          //worksheet.Cells[i + totalRow, j + 2] = lstDataToEport[i].GetValue(exportParam.lstColumnsToExport[j]);
          worksheet.Cells[lrow, j + cell] = lstDataToEport[i].GetValue(exportParam.lstColumnsToExport[j]);
        }
      }
 
      // save the application
      if (!string.IsNullOrEmpty(exportParam.SaveFilePath))
      {
        string pathToSave = exportParam.SaveFilePath;
        //if (!string.IsNullOrEmpty(exportParam.SaveFileName))
        //    pathToSave += exportParam.SaveFileName;
        //else
        pathToSave += "tmp" + System.Guid.NewGuid() + ".xls";
        workbook.SaveAs(pathToSave, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
      }
      // Exit from the application
      excelApp.Quit();

Thanks,
Sujit.
White Ashlin replied to Sujit Patil on 18-Oct-12 02:33 AM
Hello, you can use a free data export component to export from list to excel, if the data contains complex structure or content, you can choose a commercial one:
http://www.e-iceblue.com/Introduce/data-export-for-net-intro.html
http://www.e-iceblue.com/Introduce/free-dataexport-component.html