Hanson.Common.ExcelUtils 1.0.1

There is a newer version of this package available.
See the version list below for details.
dotnet add package Hanson.Common.ExcelUtils --version 1.0.1                
NuGet\Install-Package Hanson.Common.ExcelUtils -Version 1.0.1                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Hanson.Common.ExcelUtils" Version="1.0.1" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Hanson.Common.ExcelUtils --version 1.0.1                
#r "nuget: Hanson.Common.ExcelUtils, 1.0.1"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Hanson.Common.ExcelUtils as a Cake Addin
#addin nuget:?package=Hanson.Common.ExcelUtils&version=1.0.1

// Install Hanson.Common.ExcelUtils as a Cake Tool
#tool nuget:?package=Hanson.Common.ExcelUtils&version=1.0.1                

README

Hanson.Common.ExcelUtils

主要功能

  1. 提供讀取功能

    • 支援讀取 Excel 97(.xls) / 2007(.xlsx) 檔案格式

    • 支援讀取 Excel 中的工作表名稱清單

    • 支援泛型方式讀取 Excel 資料

    • 支援指定工作表名稱讀取資料

  2. 提供 匯出功能

    • 支援匯出 Excel 97(.xls) / 2007(.xlsx) 檔案格式

    • 支援多工作表匯出功能

    • 支援設定 Excel Style 功能

前置條件

  • 開發環境需具備 .Net6.0 or .Net Core 3.1 or NET 472 or NET 462

  • 運行於 Windows Platform (x86, x64)

  • 運行於 Linux Platform (x86, x64)

安裝方式

授權

此專案採用的 License為 Apache-2.0

使用範例

  • 讀取Excel 傳回 DataTable
private void Simple()
{
    var path = @"C:\temp\temp.xlsx";
    
    // 建立 ExcelUtils
    IExcelUtils utils = ExcelUtils.CreateExcelUtils();
    
    // 讀取所有 sheet 名稱清單
    var sheetNames = utils.GetSheetNames(path);
    
    // 讀取 Excel 資料 - 預設讀取第一個 Sheet
    DataTable table = utils.ReadExcel(path);
    
    // 讀取 Excel 資料 - 指定 sheet 名稱 & 有標題列
    DataTable table = utils.ReadExcel(path,sheetName:"sheet");
}
  • Excel 資料格式範本
using System.ComponentModel;
public class ExcelClass
{
    [Description("id")] //對應 讀取/匯出 時的標題名稱
    public string Id { get; set; }

    [Description("code")]//對應 讀取/匯出 時的標題名稱
    public string Code { get; set; }

    [Description("emp Name")]//對應 讀取/匯出 時的標題名稱
    public string Name { get; set; }

    [Description("birthday")]//對應 讀取/匯出 時的標題名稱
    public DateTime Birthday { get; set; }

    [Description("age")]//對應 讀取/匯出 時的標題名稱
    public double Age { get; set; }

    [Description("amount")]//對應 讀取/匯出 時的標題名稱
    public double Amount { get; set; }
}
  • 使用泛型讀取範本
private void Simple()
{
    var path = @"C:\temp\temp.xlsx";

    // 建立 ExcelUtils
    IExcelUtils utils = ExcelUtils.CreateExcelUtils();
    
    // 讀取 Excel 資料 - 預設讀取第一個 Sheet
    var table = utils.ReadExcel<ExcelClass>(path);
    
    // 讀取 Excel 資料 - 指定 sheet 名稱
    table = utils.ReadExcel<ExcelClass>(path, sheetName: "sheet");

}
  • 簡易匯出 Excel 範本
private static void Simple()
{
    List<ExcelClass> exportValues = new List<ExcelClass>
            {
                new ExcelClass{Id = "1", Code="000001", Name = "Hanson", Birthday =new DateTime(2020,1,1), Age=4, Amount= 1000},
                new ExcelClass{Id = "2", Code="000002", Name = "Ivan", Birthday =new DateTime(2024,1,1), Age=0, Amount= 1000},
                new ExcelClass{Id = "3", Code="000003", Name = "Irene", Birthday =new DateTime(2022,1,1), Age=2, Amount= 1000}
            };
   
    // 建立 ExcelUtils
    IExcelUtils utils = ExcelUtils.CreateExcelUtils();
    
    // 設定 Excel Sheet 內容
     utils.SetExportSheet(values,"sheet");
    
    // 匯出 Excel 檔案
    utils.Export(@"C:\temp\export.xlsx");

}
  • 匯出多工作表範本
private static void Simple()
{
    List<ExcelClass> exportValues = new List<ExcelClass>
            {
                new ExcelClass{Id = "1", Code="000001", Name = "Hanson", Birthday =new DateTime(2020,1,1), Age=4, Amount= 1000},
                new ExcelClass{Id = "2", Code="000002", Name = "Ivan", Birthday =new DateTime(2024,1,1), Age=0, Amount= 1000},
                new ExcelClass{Id = "3", Code="000003", Name = "Irene", Birthday =new DateTime(2022,1,1), Age=2, Amount= 1000}
            };
   
    // 建立 ExcelUtils
    IExcelUtils utils = ExcelUtils.CreateExcelUtils();
    
    // 設定 Excel Sheet 內容(每設定一次匯出就會增加一個 Sheet)
     utils.SetExportSheet(values,"sheet"); // 匯出工作表名稱為 sheet
    
    // 設定 Excel Sheet1 內容 (重複設定相同 sheet 名稱時會自動加號)
     utils.SetExportSheet(values,"sheet");// 匯出工作表名稱為 sheet1
    
    // 設定 Excel Sheet2 內容
     utils.SetExportSheet(values,"sheet2");// 匯出工作表名稱為 sheet2
    
    // 匯出 Excel 會匯出三個 Sheet 資料
    utils.Export(@"C:\temp\export.xlsx");

}
  • 匯出標題 Style 範本
private void Simple
{
    List<ExcelClass> exportValues = new List<ExcelClass>
            {
                new ExcelClass{Id = "1", Code="000001", Name = "Hanson", Birthday =new DateTime(2020,1,1), Age=4, Amount= 1000},
                new ExcelClass{Id = "2", Code="000002", Name = "Ivan", Birthday =new DateTime(2024,1,1), Age=0, Amount= 1000},
                new ExcelClass{Id = "3", Code="000003", Name = "Irene", Birthday =new DateTime(2022,1,1), Age=2, Amount= 1000}
            };
    
    // 建立 ExcelUtils
    IExcelUtils utils = ExcelUtils.CreateExcelUtils();
    
    // 建立 Cell Style 介面
	ICellStyle cellStyle = utils.CreateExportCellStyle();

    // 設定水平對齊方式
    cellStyle.Alignment = HorizontalAlignment.Center;

    //Cell 前景顏色
    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
    cellStyle.FillPattern = FillPattern.SolidForeground;

    // 指定 Style 套用於標題位置
    utils.SetExportSheet(values, headerStyle:cellStyle, sheetName:"Sheet");

    // 匯出 Excel 內容
    utils.Export(@"C:\temp\export.xlsx");
}
  • 匯出 設定 Style 範本
private void Simple
{
    List<ExcelClass> exportValues = new List<ExcelClass>
            {
                new ExcelClass{Id = "1", Code="000001", Name = "Hanson", Birthday =new DateTime(2020,1,1), Age=4, Amount= 1000},
                new ExcelClass{Id = "2", Code="000002", Name = "Ivan", Birthday =new DateTime(2024,1,1), Age=0, Amount= 1000},
                new ExcelClass{Id = "3", Code="000003", Name = "Irene", Birthday =new DateTime(2022,1,1), Age=2, Amount= 1000}
            };
    
    // 建立 ExcelUtils
    IExcelUtils utils = ExcelUtils.CreateExcelUtils();
    
    // 建立 Cell Style 介面
	ICellStyle cellStyle = utils.CreateExportCellStyle();

    // 設定水平對齊方式
    cellStyle.Alignment = HorizontalAlignment.Center;

    // 字型設定
	IFont fontStyle = utils.CreateExportFont();
    fontStyle.FontName = "Arial";
    fontStyle.FontHeightInPoints = 14;
    fontStyle.IsBold = true;
    fontStyle.IsItalic = false;
    fontStyle.IsStrikeout = false;
    cellStyle.SetFont(fontStyle);

    //Cell 前景顏色
    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
    cellStyle.FillPattern = FillPattern.SolidForeground;
    
    // 指定各自 Cell 的 Style
    List<CellStyleInfo> cellStyles = new List<CellStyleInfo>
	{
    	new CellStyleInfo { ApplyRows = true, Row=0, Style=cellStyle},
	    new CellStyleInfo { ApplyRows = false, Row=1,Column=2, Style=cellStyle},
	    new CellStyleInfo { ApplyRows = false, Row=1,Column=3, Style=cellStyle},
    	new CellStyleInfo { ApplyRows = false, Row=2,Column=2, Style=cellStyle},
	};
    
    utils.SetExportSheet(values,styles:cellStyles,sheetName:"Sheet");

    // 匯出 Excel 內容
    utils.Export(@"C:\temp\export.xlsx");
}
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.1 is compatible. 
.NET Framework net462 is compatible.  net463 was computed.  net47 was computed.  net471 was computed.  net472 is compatible.  net48 was computed.  net481 was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
  • .NETCoreApp 3.1

  • .NETFramework 4.6.2

  • .NETFramework 4.7.2

  • net6.0

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
2.0.0 79 11/30/2024
1.0.1 118 4/8/2024