如果需要查看更多文章，請微信搜索公眾號 csharp編程大全，需要進C#交流群群請加微信z438679770，備注進群， 我邀請你進群！ ！ ！
前言
1.整個Excel表格叫做作業表：WorkBook（作業薄），包含的叫頁（作業表）：Sheet；行：Row；單元格Cell，
2.忘了告訴大家npoi是做什么的了，npoi 能夠讀寫幾乎所有的Office檔案格式，至少能夠支持Word, PowerPoint, Excel, Visio的格式，
運行環境：
-
系統win10
-
office：2019
mainfrom中控制元件：
button：2個 用來觸發資料匯入匯出excel的事件
lable: 2個 用來顯示資料匯入匯出excel的耗時
datagridview ：1個 用來顯示資料匯入的結果
npoi參考：第一步：專案-->>管理Nuget程式包
第二步：搜索并安裝NPOI
第三步：using命名空間
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
運行結果：
代碼：
using System;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
namespace npoi1
{
public partial class mainForm : Form
{
public mainForm()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
//dataGridView1.DataSource = NPOIExcel.ExcelToDataTable("電腦統計表.xlsx", true);//方式1
dataGridView1.DataSource = NPOIExcel.ExcelToTable("電腦統計表.xlsx");//方式2
sw.Stop();
label1.Text = sw.ElapsedMilliseconds.ToString("資料匯入耗時：" + "0000"+"ms");
MessageBox.Show("資料匯入完成");
}
private void dataGridView1_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
{
for (int i = 0; i < dataGridView1.Rows.Count; i++)
this.dataGridView1.Rows[i].HeaderCell.Value = https://www.cnblogs.com/zyr365/archive/2020/10/20/(i + 1).ToString();
}
private void button2_Click(object sender, EventArgs e)
{
Stopwatch sw = new Stopwatch();
sw.Start();
DataTable dt = (dataGridView1.DataSource as DataTable);
//NPOIExcel.DataTableToExcel(dt,"d:\\111.xlsx");//方式1
NPOIExcel.TableToExcel(dt, "d:\\111.xlsx");//方式2
sw.Stop();
label2.Text = sw.ElapsedMilliseconds.ToString("資料匯出耗時：" + "0000" + "ms");
MessageBox.Show("資料匯出完成");
}
}
}
npoi.cs
using System;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;
using System.IO;
namespace npoi1
{
public class NPOIExcel
{
/// <summary>
/// 將excel匯入到datatable
/// </summary>
/// <param name="filePath">excel路徑</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>回傳datatable</returns>
public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
{
DataTable dataTable = null;
FileStream fs = null;
DataColumn column = null;
DataRow dataRow = null;
IWorkbook workbook = null;
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
// 2007版本
if (filePath.IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
// 2003版本
else if (filePath.IndexOf(".xls") > 0)
workbook = new HSSFWorkbook(fs);
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);//讀取第一個sheet，當然也可以回圈讀取每個sheet
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//總行數
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//第一行
int cellCount = firstRow.LastCellNum;//列數
//構建datatable的列
if (isColumnName)
{
startRow = 1;//如果第一行是列名，則從第二行開始讀取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//對時間格式（2015.12.5、2015/12/5、2015-12-5等）的處理
if (format == 14 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
return dataTable;
}
catch (Exception)
{
if (fs != null)
{
fs.Close();
}
return null;
}
}
/// <summary>
/// 寫入excel
/// </summary>
/// <param name="dt">datatable</param>
/// <param name="strFile">strFile</param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable dt, string strFile)
{
bool result = false;
IWorkbook workbook = null;
FileStream fs = null;
IRow row = null;
ISheet sheet = null;
ICell cell = null;
try
{
if (dt != null && dt.Rows.Count > 0)
{
workbook = new XSSFWorkbook();//HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，擴展名是.xls XSSFWorkbook:是操作Excel2007的版本，擴展名是.xlsx
sheet = workbook.CreateSheet("Sheet0");//創建一個名稱為Sheet0的表
int rowCount = dt.Rows.Count;//行數
int columnCount = dt.Columns.Count;//列數
//設定列頭
row = sheet.CreateRow(0);//excel第一行設為列頭
for (int c = 0; c < columnCount; c++)
{
cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
}
//設定每行每列的單元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//excel第二行開始寫入資料
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
using (fs = File.OpenWrite(strFile))
{
workbook.Write(fs);//向打開的這個xls檔案中寫入資料
result = true;
}
}
return result;
}
catch (Exception ex)
{
if (fs != null)
{
fs.Close();
}
Console.WriteLine(ex.StackTrace + ex.Message);
return false;
}
}
/// <summary>
/// Excel匯入成Datable
/// </summary>
/// <param name="file">匯入路徑(包含檔案名與擴展名)</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 適用XLSX格式，HSSFWorkbook 適用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheetAt(0);
//表頭
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//資料
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = https://www.cnblogs.com/zyr365/archive/2020/10/20/false;
foreach (int j in columns)
{
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
///
/// Datable匯出成Excel
///
///
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/182403.html
標籤：.NET技术
上一篇：NPOI操作EXCEL
下一篇：Express專案搭建與配置