@manhnguyenv

Welcome to my blog!

Work with Excel

March 15, 2018 21:49

EPPlus

https://github.com/JanKallman/EPPlus

Work with Excel in .NET

Tạo tệp Excel một cách nhanh nhất, ngắn gọn nhất sử dụng DocumentFormat.OpenXml

Install-Package DocumentFormat.OpenXml

This will create a file called Empty.xlsx in the directory you ran the code from. If you wanted to work from this code you could start by injecting your own data into the SheetData() constructor.

https://blogs.msdn.microsoft.com/chrisrae/2011/08/18/creating-a-simple-xlsx-from-scratch-using-the-open-xml-sdk/

Sample Code 1:

using DocumentFormat.OpenXml; //SpreadsheetDocumentType
using DocumentFormat.OpenXml.Packaging; //SpreadsheetDocument
using DocumentFormat.OpenXml.Spreadsheet; //Worksheet, SheetData, Workbook, Sheets, Sheet

namespace ConsoleApp4
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            // Create a spreadsheet document by providing a file name.
            string fileName = @"Book1.xlsx";

            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
                Create(fileName, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Sheet1"
            };
            sheets.Append(sheet);
            Worksheet worksheet = new Worksheet();
            SheetData sheetData = new SheetData();
            Row row = new Row();
            Cell cell = new Cell()
            {
                CellReference = "A1",
                DataType = CellValues.String,
                CellValue = new CellValue("Microsoft")
            };
            row.Append(cell);
            sheetData.Append(row);
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;

            // Close the document.
            spreadsheetDocument.Close();
        }
    }
}

Sample Code 2:

using DocumentFormat.OpenXml; // Use for SpreadsheetDocumentType
using DocumentFormat.OpenXml.Packaging; // User for SpreadsheetDocument
using DocumentFormat.OpenXml.Spreadsheet; // User for Worksheet, SheetData, Workbook, Sheets, Sheet
using System.Linq;

namespace ConsoleApp4
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            CreateExcelFile();
        }

        private static void CreateExcelFile()
        {
            // By default AutoSave will be true and Editable = true, and Type = xlsx
            using (var doc = SpreadsheetDocument.Create("Empty.xlsx", SpreadsheetDocumentType.Workbook))
            {
                // Creates 4 things: WorkBookPart, WorkSheetPart, WorkSheet, SheetData
                doc.AddWorkbookPart().AddNewPart().Worksheet = new Worksheet(new SheetData());

                doc.WorkbookPart.Workbook =
                  new Workbook(
                    new Sheets(
                      new Sheet
                      {
                          // Id is used to create Sheet to WorksheetPart relationship
                          Id = doc.WorkbookPart.GetIdOfPart(doc.WorkbookPart.WorksheetParts.First()),
                          // SheetId and Name are both required
                          SheetId = 1,
                          Name = "Sheet1"
                      }));
            }
        }
    }
}

Các thư viện làm việc với Excel

March 14, 2018 21:18

SpreadsheetLight (edit)

SpreadsheetLight is an open source Open XML spreadsheet library for .NET Framework written in C#, and is released under the MIT License. You can create new Open XML spreadsheets, or work with existing Open XML spreadsheets that are compatible with Microsoft Excel 2007/2010/2013 and LibreOffice Calc.

No Office/Excel Interop needed. You do not need Microsoft Office/Excel to be installed.

Dependency: Open XML SDK 2.5

 

DocumentFormat.OpenXml

The Open XML SDK provides tools for working with Office Word, Excel, and PowerPoint documents.

This is the 4th part of OpenXML Spreadsheet Tutorial Series:

http://www.dispatchertimer.com/tutorial/how-to-create-an-excel-file-in-net-using-openxml-part-1-basics/

http://www.dispatchertimer.com/tutorial/how-to-create-an-excel-file-in-net-using-openxml-part-2-export-a-collection-to-spreadsheet/

http://www.dispatchertimer.com/tutorial/how-to-create-an-excel-file-in-net-using-openxml-part-3-add-stylesheet-to-the-spreadsheet/

http://www.dispatchertimer.com/tutorial/how-to-create-an-excel-file-in-net-using-openxml-part-4-draw-chart/

http://www.dispatchertimer.com/troubleshooting/troubleshooting-openxml-spreadsheet/

Autofit column

https://stackoverflow.com/questions/31197038/how-to-autofit-excel-column-using-openxml-package

Number Format:

https://stackoverflow.com/questions/7872116/applying-number-format-to-a-cell-value-using-openxml

ClosedXML

 

ExcelPackage

 

OpenXmlPowerTools

The Open XML PowerTools provides guidance and example code for programming with Open XML Documents (DOCX, XLSX, and PPTX). It is based on, and extends the functionality of the Open XML SDK.

ExtremeML

ExtremeML adds new power to the OpenXML SDK 2.0, enabling packages to be created and edited via an intuitive object model and freeing developers from the burden of implementing the low-level detail of OpenXML.

Tim Coulter viết ra thư viện ExtremeML, một thư viện Opensource tuyệt vời để làm việc với Excel 2007.

ExtremeML được mô tả như sau:


Sau đây là các tính năng chính của ExtremeML:

  • Tạo mới hoặc mở 1 workbook.
  • Đọc và ghi dữ liệu từ stream.
  • Thêm, xóa, thay đổi các worksheet.
  • Thêm, xóa, sửa row và column.
  • Hỗ trợ strongly-typed.
  • Đọc và ghi các formula của cells.
  • Đọc, ghí và thay đổi dữ liệu ảnh, rich text.
  • Hỗ trợ footer và header.

Ngoài ra ExtremeML còn hỗ trợ các tính năng sau:

  • Hỗ trợ dữ liệu dựa trên dạng bảng của Excel table.
  • Hỗ trợ template.
  • Các tính năng nâng cao như: group, merge, macro và phiên bản mới nhất hỗ trợ Excel 2010.

Ví dụ đoạn code sau:

public static void PopulateDataTypesRandom()
{

    using (var package = SpreadsheetDocumentWrapper.Open("MyWorkbook.xlsx"))
    {
        var table = package.WorkbookPart.GetTablePart("DataTypes").Table;
        var data = new List<object[]>();
        for (var i = 0; i < 10; i++)
        {
            data.Add(new
            object[]
            {
                Utility.GetRandomString(),
                Utility.GetRandomUri(),
                Utility.GetRandomDateTime(),
                Utility.GetRandomTimeSpan(),
                Utility.GetRandomInteger(),
                Utility.GetRandomDouble(),
                Utility.GetRandomDecimal(),
                Utility.GetRandomBoolean()
            }
            );
        }
        table.Fill(data.ToArray());
    }
}

Một số class hữu ích sưu tầm

https://gist.github.com/hadoan/

Categories