EXCELXLSX 1.0.0.3

Excel.XLSX

Excel package

This package is used to simplify working excel files. OpenXML has been utilized to process Excel file. It leverages all functionalities of openXML and provide them into a simple interface.

Code snippets

• Read Excel

using (ExcelPackage pck = new ExcelPackage())
{
// Open the Excel file and load it to the ExcelPackage
using (var stream = System.IO.File.OpenRead(FileName))
{
    pck.Load(stream);

    foreach (ExcelWorkSheet ws in pck.Workbook.Worksheets)
    {

            
    }
}

}

• Write Excel

 FileInfo file = new FileInfo(FileName);
 using (ExcelPackage pck = new ExcelPackage(file))
{
// Add a new worksheet to the empty workbook
ExcelWorkSheet ws = pck.Workbook.Worksheets.Add(sheetName);
// Load data from DataTable to the worksheet
ws.Cells ["A1"].LoadFromDataTable(Data, true);
ws.Cells.AutoFitColumns();

// Add some styling
using (ExcelRange rng = ws.Cells[1, 1, 1, Data.Columns.Count])
{
    rng.Style.Font.Bold = false;
    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
    rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(200, 129, 189));
    rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}

// Save the new workbook
pck.Save();
}

• Access Cells

var file = @"TestSource\marks.xlsx";
using (ExcelPackage pck = new ExcelPackage())
{
    // Open the Excel file and load it to the ExcelPackage
    using (var stream = System.IO.File.OpenRead(file))
    {
        
        pck.Load(stream);
        ExcelWorkSheet ws = pck.Workbook.Worksheets["Sheet1"];
        using(var rng = ws.Cells[1,1,4,3])
        {
            foreach(var c in rng)
            {
                var x = c.Text;
            }
        }
    }
}

• Read Sheet information to DataTable

using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(fileName, false))
{
    res = new DataTable();
    res.Columns.Add("Attribute", typeof(string));
    res.Columns.Add("Value", typeof(string));
    S sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets;

    // For each sheet, display the sheet information.
    foreach (E sheet in sheets)
    {
        foreach (A attr in sheet.GetAttributes())
        {
            DataRow r = res.NewRow();
            r["Attribute"] = attr.LocalName;
            r["Value"] = attr.Value;
            res.Rows.Add(r);
        }
    }
}

• Create file with some data validations in cell range

//Assume Data has been set into DataTable called Data
sting file = @"\Testfile.xlsx";
string cellRange = "C3:C100";
string optionsList = "Option1,Option2,Option3"
using (ExcelPackage pck = new ExcelPackage(file))
{
    // Add a new worksheet to the empty workbook
    ExcelWorkSheet ws = pck.Workbook.Worksheets.Add(sheetName);
    // Load data from DataTable to the worksheet
    ws.Cells["A1"].LoadFromDataTable(Data, true);
    ws.Cells.AutoFitColumns();

    //Add data options as list in drop down box
    IExcelDataValidationList values = ws.DataValidations.AddListValidation(cellRange);
    values.ShowErrorMessage = true;
    values.ErrorStyle = ExcelDataValidationWarningStyle.stop;
    values.ErrorTitle = "An invalid value was entered";
    values.Error = "Select a value from the list";
    values.Formula.ExcelFormula = optionsList;

    // Add some styling
    using (ExcelRange rng = ws.Cells[1, 1, 1, Data.Columns.Count])
    {
        rng.Style.Font.Bold = true;
        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
        rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
        rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
    }

    // Save the new workbook
    pck.Save();
}

• Iterator Example

// Assume DataTable Data has been loaded into work sheet ws. Then Iterating the worksheet ws to do some operations with the cells 
 for (int i = 1; i <= Data.Rows.Count; i++)
    {
        using (ExcelRange rng = ws.Cells[i + 1,1, i + 1,  Data.Columns.Count])
        {
            rng.Style.Font.Bold = true;
            rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
            foreach (var cell in rng)
            {
                Console.WriteLine(cell.Text) ;
            }
        }

    }

*** promo Promotions can raise awareness of an important concept.

License

Free to use and to distribute. Just mention it in your code distribution, web site.

Package Name: EXCELXLSX

Author: Amr M. Gody

Email: amr.m.gody@gmail.com

Release Notes

Report Bugs or feedback

https://amglab.atlassian.net/servicedesk/customer/portal/1