I want to export the List<MyClass> into EXCEL.... USING windows application in C#......
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........
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.........
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();
Sujit.