C#封裝一個快速讀取寫入操作excel的工具類
更新時間：2024年01月29日 16:49:14 作者：搬磚的詩人Z
這篇文章主要為大家詳細介紹了C#如何封裝一個快速讀取寫入操作excel的工具類,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學(xué)習一下
這里封裝了3個實用類ExcelDataReaderExtensions，ExcelDataSetConfiguration，ExcelDataTableConfiguration和一個實用代碼參考：
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ExeclHelper
{
/// <summary>
/// Processing configuration options and callbacks for AsDataTable().
/// </summary>
public class ExcelDataTableConfiguration
{
/// <summary>
/// Gets or sets a value indicating the prefix of generated column names.
/// </summary>
public string EmptyColumnNamePrefix { get; set; } = "Column";
/// <summary>
/// Gets or sets a value indicating whether to use a row from the data as column names.
/// </summary>
public bool UseHeaderRow { get; set; } = false;
/// <summary>
/// Gets or sets a callback to determine which row is the header row. Only called when UseHeaderRow = true.
/// </summary>
public Action<IExcelDataReader> ReadHeaderRow { get; set; }
/// <summary>
/// Gets or sets a callback to determine whether to include the current row in the DataTable.
/// </summary>
public Func<IExcelDataReader, bool> FilterRow { get; set; }
/// <summary>
/// Gets or sets a callback to determine whether to include the specific column in the DataTable. Called once per column after reading the headers.
/// </summary>
public Func<IExcelDataReader, int, bool> FilterColumn { get; set; }
}
}
using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ExeclHelper
{
/// <summary>
/// ExcelDataReader DataSet extensions
/// </summary>
public static class ExcelDataReaderExtensions
{
/// <summary>
/// Converts all sheets to a DataSet
/// </summary>
/// <param name="self">The IExcelDataReader instance</param>
/// <param name="configuration">An optional configuration object to modify the behavior of the conversion</param>
/// <returns>A dataset with all workbook contents</returns>
public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfiguration configuration = null)
{
if (configuration == null)
{
configuration = new ExcelDataSetConfiguration();
}
self.Reset();
var tableIndex = -1;
var result = new DataSet();
do
{
tableIndex++;
if (configuration.FilterSheet != null && !configuration.FilterSheet(self, tableIndex))
{
continue;
}
var tableConfiguration = configuration.ConfigureDataTable != null
? configuration.ConfigureDataTable(self)
: null;
if (tableConfiguration == null)
{
tableConfiguration = new ExcelDataTableConfiguration();
}
var table = AsDataTable(self, tableConfiguration);
result.Tables.Add(table);
}
while (self.NextResult());
result.AcceptChanges();
if (configuration.UseColumnDataType)
{
FixDataTypes(result);
}
self.Reset();
return result;
}
private static string GetUniqueColumnName(DataTable table, string name)
{
var columnName = name;
var i = 1;
while (table.Columns[columnName] != null)
{
columnName = string.Format("{0}_{1}", name, i);
i++;
}
return columnName;
}
private static DataTable AsDataTable(IExcelDataReader self, ExcelDataTableConfiguration configuration)
{
var result = new DataTable { TableName = self.Name };
result.ExtendedProperties.Add("visiblestate", self.VisibleState);
var first = true;
var emptyRows = 0;
var columnIndices = new List<int>();
while (self.Read())
{
if (first)
{
if (configuration.UseHeaderRow && configuration.ReadHeaderRow != null)
{
configuration.ReadHeaderRow(self);
}
for (var i = 0; i < self.FieldCount; i++)
{
if (configuration.FilterColumn != null && !configuration.FilterColumn(self, i))
{
continue;
}
var name = configuration.UseHeaderRow
? Convert.ToString(self.GetValue(i))
: null;
if (string.IsNullOrEmpty(name))
{
name = configuration.EmptyColumnNamePrefix + i;
}
// if a column already exists with the name append _i to the duplicates
var columnName = GetUniqueColumnName(result, name);
var column = new DataColumn(columnName, typeof(object)) { Caption = name };
result.Columns.Add(column);
columnIndices.Add(i);
}
result.BeginLoadData();
first = false;
if (configuration.UseHeaderRow)
{
continue;
}
}
if (configuration.FilterRow != null && !configuration.FilterRow(self))
{
continue;
}
if (IsEmptyRow(self))
{
emptyRows++;
continue;
}
for (var i = 0; i < emptyRows; i++)
{
result.Rows.Add(result.NewRow());
}
emptyRows = 0;
var row = result.NewRow();
for (var i = 0; i < columnIndices.Count; i++)
{
var columnIndex = columnIndices[i];
var value = self.GetValue(columnIndex);
row[i] = value;
}
result.Rows.Add(row);
}
result.EndLoadData();
return result;
}
private static bool IsEmptyRow(IExcelDataReader reader)
{
for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetValue(i) != null)
return false;
}
return true;
}
private static void FixDataTypes(DataSet dataset)
{
var tables = new List<DataTable>(dataset.Tables.Count);
bool convert = false;
foreach (DataTable table in dataset.Tables)
{
if (table.Rows.Count == 0)
{
tables.Add(table);
continue;
}
DataTable newTable = null;
for (int i = 0; i < table.Columns.Count; i++)
{
Type type = null;
foreach (DataRow row in table.Rows)
{
if (row.IsNull(i))
continue;
var curType = row[i].GetType();
if (curType != type)
{
if (type == null)
{
type = curType;
}
else
{
type = null;
break;
}
}
}
if (type == null)
continue;
convert = true;
if (newTable == null)
newTable = table.Clone();
newTable.Columns[i].DataType = type;
}
if (newTable != null)
{
newTable.BeginLoadData();
foreach (DataRow row in table.Rows)
{
newTable.ImportRow(row);
}
newTable.EndLoadData();
tables.Add(newTable);
}
else
{
tables.Add(table);
}
}
if (convert)
{
dataset.Tables.Clear();
dataset.Tables.AddRange(tables.ToArray());
}
}
}
}using ExcelDataReader;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ExeclHelper
{
/// <summary>
/// Processing configuration options and callbacks for IExcelDataReader.AsDataSet().
/// </summary>
public class ExcelDataSetConfiguration
{
/// <summary>
/// Gets or sets a value indicating whether to set the DataColumn.DataType property in a second pass.
/// </summary>
public bool UseColumnDataType { get; set; } = true;
/// <summary>
/// Gets or sets a callback to obtain configuration options for a DataTable.
/// </summary>
public Func<IExcelDataReader, ExcelDataTableConfiguration> ConfigureDataTable { get; set; }
/// <summary>
/// Gets or sets a callback to determine whether to include the current sheet in the DataSet. Called once per sheet before ConfigureDataTable.
/// </summary>
public Func<IExcelDataReader, int, bool> FilterSheet { get; set; }
}
}
運用實例：
private IList<string> GetTablenames(DataTableCollection tables)
{
var tableList = new List<string>();
foreach (var table in tables)
{
tableList.Add(table.ToString());
}
return tableList;
}
public void ExportExcel()
{
try
{
//創(chuàng)建一個工作簿
IWorkbook workbook = new HSSFWorkbook();
//創(chuàng)建一個 sheet 表
ISheet sheet = workbook.CreateSheet("合并數(shù)據(jù)");
//創(chuàng)建一行
IRow rowH = sheet.CreateRow(0);
//創(chuàng)建一個單元格
ICell cell = null;
//創(chuàng)建單元格樣式
ICellStyle cellStyle = workbook.CreateCellStyle();
//創(chuàng)建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//設(shè)置為文本格式，也可以為 text，即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
//設(shè)置列名
//foreach (DataColumn col in dt.Columns)
//{
// //創(chuàng)建單元格并設(shè)置單元格內(nèi)容
// rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
// //設(shè)置單元格格式
// rowH.Cells[col.Ordinal].CellStyle = cellStyle;
//}
for (int i = 0; i < Headers.Count(); i++)
{
rowH.CreateCell(i).SetCellValue(Headers[i]);
rowH.Cells[i].CellStyle = cellStyle;
}
//寫入數(shù)據(jù)
for (int i = 0; i < dataModels.Count; i++)
{
//跳過第一行，第一行為列名
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < 11; j++)
{
cell = row.CreateCell(j);
if (j == 0)
cell.SetCellValue(dataModels[i].title1.ToString());
if (j == 1)
cell.SetCellValue(dataModels[i].title2.ToString());
if (j == 2)
cell.SetCellValue(dataModels[i].title3.ToString());
if (j == 3)
cell.SetCellValue(dataModels[i].title4.ToString());
if (j == 4)
cell.SetCellValue(dataModels[i].title5.ToString());
if (j == 5)
cell.SetCellValue(dataModels[i].title6.ToString());
if (j == 6)
cell.SetCellValue(dataModels[i].title7.ToString());
if (j == 7)
cell.SetCellValue(dataModels[i].title8.ToString());
if (j == 8)
cell.SetCellValue(dataModels[i].title9.ToString());
if (j == 9)
cell.SetCellValue(dataModels[i].title10.ToString());
if (j == 10)
cell.SetCellValue(dataModels[i].title11.ToString());
cell.CellStyle = cellStyle;
}
}
//設(shè)置導(dǎo)出文件路徑
string path = textBox2.Text;
//設(shè)置新建文件路徑及名稱
string savePath = path + "合并" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss") + ".xls";
//創(chuàng)建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//創(chuàng)建一個 IO 流
MemoryStream ms = new MemoryStream();
//寫入到流
workbook.Write(ms);
//轉(zhuǎn)換為字節(jié)數(shù)組
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//還可以調(diào)用下面的方法，把流輸出到瀏覽器下載
//OutputClient(bytes);
//釋放資源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
sheet = null;
workbook = null;
}
catch (Exception ex)
{
}
}
以上就是C#封裝一個快速讀取寫入操作excel的工具類的詳細內(nèi)容，更多關(guān)于C#讀取寫入excel的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了C#圖像亮度調(diào)整的方法,涉及C#操作圖像亮度的相關(guān)技巧,需要的朋友可以參考下2015-04-04
這篇文章主要為大家詳細介紹了如何基于C#實現(xiàn)一個SM2加簽驗簽工具,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學(xué)習一下2024-10-10
本篇文章是對使用c#在word文檔中創(chuàng)建表格的方法進行了詳細的分析介紹，需要的朋友參考下2013-05-05
本文介紹了C#中的Random類,用于生成隨機數(shù),文中通過示例代碼介紹的非常詳細,對大家的學(xué)習或者工作具有一定的參考學(xué)習價值,需要的朋友們下面隨著小編來一起學(xué)習學(xué)習吧2024-12-12
Log4Net是從Java的log4j移植過來的,功能也與log4j類似,可以把日志信息輸出到文件、數(shù)據(jù)庫等不同的介質(zhì)或目標,下面我們就來學(xué)習一下如何使用log4net進行日志記錄吧2023-11-11
這篇文章主要為大家詳細介紹了C#實現(xiàn)圍棋游戲，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-05-05
這篇文章主要為大家詳細介紹了Unity shader實現(xiàn)遮罩效果，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-02-02
C# 手動/自動保存圖片的實例代碼，需要的朋友可以參考一下2013-03-03
這篇文章主要介紹了c#獲取客戶端IP地址(考慮代理)，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習或者工作具有一定的參考學(xué)習價值，需要的朋友們下面隨著小編來一起學(xué)習學(xué)習吧2020-01-01
MD5（消息摘要算法第5版）是一種廣泛使用的哈希函數(shù),可以生成一個128位（16字節(jié)）的哈希值,通常用于數(shù)據(jù)完整性校驗和密碼存儲,在Windows Forms應(yīng)用程序中實現(xiàn)MD5加密,可以用于用戶密碼的安全存儲和數(shù)據(jù)的完整性驗證,本文將詳細介紹了如何在WinForms中實現(xiàn)MD5加密2024-10-10