C#-读写Excel学习笔记
一、C#读写Excel的几种方法
二、C#读写Excel-Microsoft.Office.Interop.Excel
前提是本机须安装office才能运行,且不同的office版本之间可能会有兼容问题,从Nuget下载 Microsoft.Office.Interop.Excel。
三、NPOI方法(推荐、在不安装office的时候也是可以读写的,速度很快):
1、Excel与DataTable转换:
/**
*┌──────────────────────────────────────────────────────────────┐
*│ 描 述:EXCEL操作类
*│ 作 者:执笔小白
*│ 版 本:1.0
*│ 创建时间:2020-11-26 10:16
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│ 命名空间: Util
*│ 类 名:NPOIHelper
*└──────────────────────────────────────────────────────────────┘
*/
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;
/// <summary>
/// EXCEL操作类
/// </summary>
public class NPOIHelper
{
/// <summary>
/// 读取Excel文件到DataTable
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataTable ReadExcel(string filePath)
{
NPOI.SS.UserModel.ISheet sheet;
if (filePath.Contains(".xlsx")) // 2007 xlsx
{
#region//初始化信息
XSSFWorkbook hssfworkbook;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
sheet = hssfworkbook.GetSheetAt(0);
#endregion
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) // cell数
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
else // 2003 xls
{
HSSFWorkbook hssfworkbook;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
return dt;
}
}
/// <summary>
/// 将DataTable数据写入Excel文件
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static void WriteExcel(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
if (filePath.Contains(".xlsx")) // 2007 xlsx
{
XSSFWorkbook book = new XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
else // 2003 xls
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(dt.TableName);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}
}
}
2、导入Excel修改完再保存(举例为.xls格式):
// 载入模板文件-xls
string excelTemplate_BaseMaterialTestReport = Path.Combine("目录", "文件名.xls");
if (excelTemplate_BaseMaterialTestReport.Equals(".xls"))
{
stringBuilder.Append("Excel文件格式不为‘.xls’");
return false;
}
HSSFWorkbook hssfworkbook;
using (FileStream file = new(excelTemplate_BaseMaterialTestReport, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0); // 找到sheet1
// 写入数据
DateTime dateTime = DateTime.Now;
string dateTime_Date = dateTime.ToString(); // 日期
sheet.GetRow(3).GetCell(8).SetCellValue(dateTime_Date); // 日期
sheet.GetRow(5).GetCell(6).SetCellValue("备注"); // 备注
string targetFileName = "文件保存目标地址"; // 文件名
// 保存Excel到指定文件夹中(xls)
using (System.IO.MemoryStream ms = new())
{
hssfworkbook.Write(ms);
using (FileStream fs = new(targetFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
hssfworkbook = null;
}
四、附录:
附录-1:C# DataTable转化为自定义DataTable。
附录-2:Winform的Excle展示与编辑控件:DSOFramer:
① C# 如何在winform中嵌入Excel,内嵌Excel,word
② 显示 Word、Excel 等文件(WinForm 窗口中)
本文来自博客园,作者:꧁执笔小白꧂,转载请注明原文链接:https://www.cnblogs.com/qq2806933146xiaobai/p/14191662.html