Hanson.Common.ExcelUtils
1.0.1
.NET 6.0
This package targets .NET 6.0. The package is compatible with this framework or higher.
.NET Core 3.1
This package targets .NET Core 3.1. The package is compatible with this framework or higher.
.NET Framework 4.6.2
This package targets .NET Framework 4.6.2. The package is compatible with this framework or higher.
There is a newer version of this package available.
See the version list below for details.
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
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#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
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
README
Hanson.Common.ExcelUtils
主要功能
提供讀取功能
支援讀取 Excel 97(.xls) / 2007(.xlsx) 檔案格式
支援讀取 Excel 中的工作表名稱清單
支援泛型方式讀取 Excel 資料
支援指定工作表名稱讀取資料
提供 匯出功能
支援匯出 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)
安裝方式
- 採用 Nuget 方式進行安裝作業 https://www.nuget.org/packages/Hanson.Common.ExcelUtils/
授權
此專案採用的 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 | Versions 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.
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.