EPPlus is an open source .NET library. It is so good when you want to read and write Excel files (2007/2010), it makes life easy in a fast paced development requirement. I would like to give brief introduction to the developers who don’t know about it.
The Office Interop is NOT supported by MS in “server-like scenarios” like Citrix. It throws automation exceptions. I am not going into much detail there. But once you have EPPlus then there is no need to install Excel component.
Refer the link http://epplus.codeplex.com/ and download the library binaries.
In the example code below, I am creating a new excel file using a template excel file. The template file has the columns and styles defined. In the newly created file, I am just pasting the data from second row onwards.Note some of the code here is little crude, sorry about that. (Please ignore) 🙂
Create a C#Dot Net project and add a reference [Project -> Add Reference].
Add the following two lines to your module:
using OfficeOpenXml;
using OfficeOpenXml.Style;
//Writing an Excel file:
FileInfo template = new FileInfo(“T:\\template\\sampletemplate.xlsx”);
FileInfo newFile = new FileInfo(“T:\\output\\CopiedFile.xlsx”);
using (ExcelPackage xlPackage = new ExcelPackage(newFile, template))
{
//The new Excel file is not copied, it is still in memory
//Get the wosksheet called SAMPLESHEET from the new excel file
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[“SAMPLESHEET”];
DataTable dt = GetDataTable();
//The function code not listed and no validation added for the validation
//This populates the excel file, I wanted it to paste the data from column C and //second row
worksheet.Cells[“C2”].LoadFromDataTable(dt, false);
//I know in my code that the excel file has 14 columns, note that the
const int startRow = 2;
int row = startRow;
for (int iCol = 1; iCol < 14; iCol++)
{
//Change the style of all the cells for the rows written using a style from a particular cell which had the style set
for (int iRow = startRow; iRow < (startRow + dt.Rows.Count); iRow++)
{
worksheet.Cells[iRow, iCol].StyleID = worksheet.Cells[startRow, 3].StyleID;
}
}
//The template file has more than 10000 rows, deleting the extra rows and just keeping rows written from datatable.
worksheet.DeleteRow((startRow + dt.Rows.Count), (worksheet.Dimension.End.Row – dt.Rows.Count));
//This will save the file as T:\\output\\CopiedFile.xlsx
xlPackage.Save();
}
Reading an excel file
using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
{
// get the first worksheet in the workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
//Get start and end range values from the excel file.
var start = worksheet.Dimension.Start;
var end = worksheet.Dimension.End;
for (int iRow = 2; iRow <= end.Row; iRow++)
{
string cellval = (string)worksheet.Cells[iRow, 1].Value;
if (string.IsNullOrEmpty(cellval) == false)
{
//Perform required action
}
}
//usage of LINQ – Get all the values from column D, then filter it
var dValues = from cell in worksheet.Cells[“D2:D” + worksheet.Dimension.End.Row.ToString()]
where cell.Value.ToString().Equals(worksheet.Cells[irow, 4].Value.ToString())
select cell;
//Copy range of values from a spreadsheet to another. The code for creating errorworksheet is not given here
worksheet.Cells[string.Format(“C{0}:T{0}”, irow)].Copy(errorworksheet.Cells[string.Format(“C{0}:T{0}”, errrowcnt+2)]);
}
vijaysambhe 