@manhnguyenv

Welcome to my blog!

Work with EXCEL using NPOI and EPPlus

May 11, 2018 22:38

Work with EXCEL using NPOI and EPPlus
https://www.codeproject.com/Articles/1241654/Export-to-Excel-using-NPOI-Csharp-and-WEB-API
http://www.zachhunter.com/2015/11/xlsx-template-with-epplus-and-web-api-save-as-xlsx-or-pdf/
https://www.codeproject.com/Articles/1194712/Advanced-Excels-With-EPPlus

Làm việc với Excel trong .NET

March 24, 2018 20:43

Làm việc với Excel trong .NET

1) Sử dụng thư viện EPPlus

1.1) Thao tác cơ bản khi làm việc với Excel sử dụng thư viện EPPlus

https://www.codeproject.com/Tips/1118204/EPPlus-Basics-and-Snippets-Cheatsheet-for-generati

27 Aug 2016

1.2) Simple POCO Mapper for EPPlus

https://www.codeproject.com/Articles/1202924/Simple-POCO-Mapper-for-EPPlus

27 Aug 2017

Mapping theo cột hoặc theo hàng để đọc tệp Excel theo chiều ngang và chiều dọc:

Merge cells

http://w3cgeek.com/epplus-how-to-style-merged-cells.html

c# - Merge cells using EPPlus? - Stack Overflow

ws.Cells["A1:C1"].Merge = true;

2) Sử dụng thư viện Bytescout.SpreadSheet

https://www.c-sharpcorner.com/UploadFile/17e8f6/reading-merged-cell-from-excel-using-bytescout-spreadsheet-i/

Đọc tệp Excel khi đã merge rows hoặc merge cells

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/

Phím tắt Excel

July 1, 2017 12:40

https://www.facebook.com/permalink.php?story_fbid=1304749679660585&id=100003766955259

Split text into different columns with the Convert Text to Columns Wizard

May 11, 2017 15:43

You can take the text in one or more cells, and spread it out across multiple cells. This is called parsing, and is the opposite of concatenating, where you can combine text from two or more cells into one cell. For example, if you have a column of full names, you can split that column into separate first name and last name columns, like this:

Before and after of text split into different columns

Go to Data > Text to Columns, and the wizard will walk you through the process. Here’s a full breakdown of how it works:

  1. Select the cell or column that contains the text you want to split.

    NOTE:  Select as many rows as you want, but no more than one column. Make sure there’s enough empty columns to the right so nothing over there gets overwritten. If you don’t have enough empty columns, add them.

  2. Click Data >Text to Columns.

    Click the Data tab, and then click Text to Columns

  3. This starts the Convert Text to Columns Wizard. Click Delimited > Next.

  4. Check Space, and clear the rest of the boxes, or check Comma and Space if that is how your text is split (Smith, John, with a comma and space between the names). You can see a preview of your data in the Data preview window.

    Step 2 in the Convert Text to Columns Wizard

  5. Click Next.

  6. In this step, you pick the format for your new columns, or you can let Excel do it for you. If you want to pick your own format, select the format you want, such as Text, click the second column of data in the Data preview window, and click the same format again. Repeat for all the columns in the preview window.

    Step 3 in the Convert Text to Columns Wizard

  7. Click the Collapse Dialog Box button image button to the right of the Destination box to collapse the dialog box.

  8. Select the cells in your workbook where you want to paste your split data. For example, if you are dividing a full name into a first name column and a last name column, select the appropriate number of cells in two adjacent columns.

    Select the cells where you wnt to paste your split cells

  9. Click the Expand Dialog Box button image button to expand the dialog box, and then click Finish.

    Expand the dialog box when you're done selecting your cells.

Categories

Recent posts