WPF編程excel表格操作
- 摘要
- NPOI安裝
- 封裝代碼
- 測試代碼
摘要
Excel操作幾種方式
- 使用開源庫NPOI(常用，操作豐富)
- 使用Microsoft.Office.Interop.Excel COM組件(兼容性問題)
- 使用OpenXml(效率高)
- 使用OleDb(過時)
NPOI安裝
封裝代碼
using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; // 對于.xlsx文件
using NPOI.HSSF.UserModel; // 對于.xls文件namespace GasAlarmTestTool
{internal class ExcelTools{/// <summary>/// 創建Excel表/// </summary>/// <param name="filePath"></param>/// <param name="dataTable"></param>public void CreateNewExcel(string filePath, DataTable dataTable){IWorkbook workbook;if (filePath.EndsWith(".xlsx")){workbook = new XSSFWorkbook(); // 創建 .xlsx 文件}else{workbook = new HSSFWorkbook(); // 創建 .xls 文件}var sheet = workbook.CreateSheet("Sheet1");// 寫入表頭var headerRow = sheet.CreateRow(0);for (int i = 0; i < dataTable.Columns.Count; i++){headerRow.CreateCell(i).SetCellValue(dataTable.Columns[i].ColumnName);}// 寫入數據for (int i = 0; i < dataTable.Rows.Count; i++){var dataRow = sheet.CreateRow(i + 1);for (int j = 0; j < dataTable.Columns.Count; j++){dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());}}SetUniformColumnWidth(sheet, 20); // 默認統一列寬20// 保存文件using (var stream = new FileStream(filePath, FileMode.Create, FileAccess.Write)){workbook.Write(stream);}}/// <summary>/// 追加Excel表/// 追加數據時，可以定位到現有數據的末尾，創建新行并寫入。/// </summary>/// <param name="filePath"></param>/// <param name="dataTable"></param>public void AppendDataToExistingExcel(string filePath, DataTable dataTable){IWorkbook workbook;using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read)){workbook = filePath.EndsWith(".xlsx") ? (IWorkbook)new XSSFWorkbook(stream) : new HSSFWorkbook(stream);}var sheet = workbook.GetSheetAt(0);int lastRowNum = sheet.LastRowNum;for (int i = 0; i < dataTable.Rows.Count; i++){var dataRow = sheet.CreateRow(lastRowNum + i + 1);for (int j = 0; j < dataTable.Columns.Count; j++){dataRow.CreateCell(j).SetCellValue(dataTable.Rows[i][j].ToString());}}using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Write)){workbook.Write(stream);}}/// <summary>/// 查找指定列是否存在item項/// </summary>/// <param name="filePath"></param>/// <param name="item"></param>/// <param name="colIndex"></param>/// <returns></returns>public bool SearchColumnExitsItem(string filePath, string item, int colIndex){IWorkbook workbook;using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read)){workbook = filePath.EndsWith(".xlsx") ? (IWorkbook)new XSSFWorkbook(stream) : new HSSFWorkbook(stream);}var sheet = workbook.GetSheetAt(0);// 遍歷每一行for (int row = 0; row <= sheet.LastRowNum; row++){IRow currentRow = sheet.GetRow(row);if (currentRow != null){// 遍歷每一列for (int column = 0; column < currentRow.LastCellNum; column++){ ICell currentCell = currentRow.GetCell(column);var cellValue = currentCell.ToString();if ((column == colIndex) && (cellValue == item)){return true;}}}} return false;}/// <summary>/// 設置列寬/// </summary>/// <param name="workbook"></param>/// <param name="sheetIndex"></param>/// <param name="columnIndex"></param>public void SetColumnWidth(IWorkbook workbook, int sheetIndex, int columnIndex){var sheet = workbook.GetSheetAt(sheetIndex);// 設置列寬(單位是 1/256 字符寬度)sheet.SetColumnWidth(columnIndex, 20 * 256); // 設置第1列寬度為20}/// <summary>/// 設置行高/// </summary>/// <param name="workbook"></param>/// <param name="sheetIndex"></param>/// <param name="rowIndex"></param>public void SetColumnRowHeight(IWorkbook workbook, int sheetIndex, int rowIndex){var sheet = workbook.GetSheetAt(sheetIndex);// 設置行高（單位是點數）var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);row.HeightInPoints = 25; // 設置行高為25點}/// <summary>/// 同時設置列寬和行高/// </summary>/// <param name="workbook"></param>/// <param name="sheetIndex"></param>/// <param name="columnIndex"></param>/// <param name="rowIndex"></param>/// <param name="width"></param>/// <param name="height"></param>public void SetColumnWidthAndRowHeight(IWorkbook workbook, int sheetIndex, int columnIndex, int rowIndex, int width, int height){var sheet = workbook.GetSheetAt(sheetIndex);// 設置列寬（單位是1/256字符寬度）sheet.SetColumnWidth(columnIndex, width * 256); // 設置第1列寬度為 20 var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.HeightInPoints = height; // 25}/// <summary>/// 設置統一行高/// </summary>/// <param name="sheet"></param>/// <param name="heightInPoints"></param>public void SetUniformRowHeight(ISheet sheet, float heightInPoints){for (int i = 0; i < sheet.LastRowNum; i++){ var row = sheet.GetRow(i) ?? sheet.CreateRow(i);row.HeightInPoints = heightInPoints;}}/// <summary>/// 設置統一列寬/// </summary>/// <param name="sheet"></param>/// <param name="widthInCharacters"></param>public void SetUniformColumnWidth(ISheet sheet, int widthInCharacters){for (int i = 0; i < sheet.GetRow(0).LastCellNum; i++) // 以第一行的單元格數量為列數{sheet.SetColumnWidth(i, widthInCharacters * 256); // 設置列寬}}/// <summary>/// 設置統一行高和列寬/// </summary>/// <param name="sheet"></param>/// <param name="rowHeightInPoints"></param>/// <param name="columnWidthCharacters"></param>public void SetUniformRowHeightAndColumnWidth(ISheet sheet, float rowHeightInPoints, int columnWidthCharacters){SetUniformRowHeight(sheet, rowHeightInPoints);SetUniformColumnWidth(sheet, columnWidthCharacters);}/// <summary>/// 合并單元格可以通過 CellRangeAddress 設置，需要定義起始和結束的行列。/// </summary>/// <param name="workbook"></param>/// <param name="sheetIndex"></param>/// <param name="firstRow"></param>/// <param name="lastRow"></param>/// <param name="firstCol"></param>/// <param name="lastCol"></param>public void MergeCells(IWorkbook workbook, int sheetIndex, int firstRow, int lastRow, int firstCol, int lastCol){var sheet = workbook.GetSheetAt(sheetIndex);// 合并單元格var cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);sheet.AddMergedRegion(cellRangeAddress);// 可以對合并后的單元格設置樣式var cell = sheet.GetRow(firstRow).GetCell(firstCol) ?? sheet.GetRow(firstRow).CreateCell(firstCol);var style = workbook.CreateCellStyle();style.Alignment = HorizontalAlignment.Center;cell.CellStyle = style; }public void SetCellStyle(IWorkbook workbook, int sheetIndex, int rowIndex, int colIndex){var sheet = workbook.GetSheetAt(sheetIndex);var cell = sheet.GetRow(rowIndex).GetCell(colIndex) ?? sheet.GetRow(rowIndex).CreateCell(colIndex);var style = workbook.CreateCellStyle();// 設置字體var font = workbook.CreateFont();font.FontHeightInPoints = 1;font.FontName = "Arial";font.IsBold = true;style.SetFont(font);// 設置邊框style.BorderBottom = BorderStyle.Thin;style.BorderLeft = BorderStyle.Thin;style.BorderRight = BorderStyle.Thin;style.BorderTop = BorderStyle.Thin;// 設置背景顏色style.FillForegroundColor = IndexedColors.LightBlue.Index;style.FillPattern = FillPattern.SolidForeground;cell.CellStyle = style;cell.SetCellValue("示例文本內容");}}
}測試代碼
private ExcelTools excel = new ExcelTools();
string excelFileName = Properties.Settings.Default.SavePath + "/燃氣報警器數據表格.xlsx";// TODO: 生成保存數據
DataTable dt = new DataTable();
dt.Columns.Add("設備UUID", typeof(string));
dt.Columns.Add("SIM卡號", typeof(string));
dt.Columns.Add("設備型號", typeof(string));
dt.Columns.Add("網絡型號", typeof(string));
dt.Columns.Add("生產日期", typeof(string));DataRow dr = dt.NewRow();
dr["設備UUID"] = "2021886000001";
dr["SIM卡號"] = "86452215642112345675";
dr["設備型號"] = "單甲烷";
dr["網絡型號"] = "NB-IoT";
dr["生產日期"] = DateTime.Now.ToString();
dt.Rows.Add(dr);if (excelFileName.EndsWith(".xls") || excelFileName.EndsWith(".xlsx"))
{if (!File.Exists(excelFileName)){// TODO: 文件不存在創建文件excel.CreateNewExcel(excelFileName, dt);}else{// TODO: 將IMEI號寫入個Excel表格，若已經寫入過則不再寫入，防止重復寫入if (excel.SearchColumnExitsItem(excelFileName, label_imei.Content.ToString(), 0) == false){excel.AppendDataToExistingExcel(excelFileName, dt);}}
}
else
{MessageBox.Show("請先設置表格文件保存！");
}
