网站优化常见的优化技术,阿里云 万网 网站,led论坛网站建设,最实用的上网网址一览表WPF编程excel表格操作 摘要NPOI安装封装代码测试代码 摘要
Excel操作几种方式
使用开源库NPOI(常用#xff0c;操作丰富)使用Microsoft.Office.Interop.Excel COM组件(兼容性问题)使用OpenXml(效率高)使用OleDb(过时)
NPOI安装 封装代码
using System;
using System.IO;
u… 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 namefilePath/param/// param namedataTable/parampublic 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 namefilePath/param/// param namedataTable/parampublic 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 namefilePath/param/// param nameitem/param/// param namecolIndex/param/// returns/returnspublic 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 nameworkbook/param/// param namesheetIndex/param/// param namecolumnIndex/parampublic 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 nameworkbook/param/// param namesheetIndex/param/// param namerowIndex/parampublic 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 nameworkbook/param/// param namesheetIndex/param/// param namecolumnIndex/param/// param namerowIndex/param/// param namewidth/param/// param nameheight/parampublic 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 namesheet/param/// param nameheightInPoints/parampublic 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 namesheet/param/// param namewidthInCharacters/parampublic void SetUniformColumnWidth(ISheet sheet, int widthInCharacters){for (int i 0; i sheet.GetRow(0).LastCellNum; i) // 以第一行的单元格数量为列数{sheet.SetColumnWidth(i, widthInCharacters * 256); // 设置列宽}}/// summary/// 设置统一行高和列宽/// /summary/// param namesheet/param/// param namerowHeightInPoints/param/// param namecolumnWidthCharacters/parampublic void SetUniformRowHeightAndColumnWidth(ISheet sheet, float rowHeightInPoints, int columnWidthCharacters){SetUniformRowHeight(sheet, rowHeightInPoints);SetUniformColumnWidth(sheet, columnWidthCharacters);}/// summary/// 合并单元格可以通过 CellRangeAddress 设置需要定义起始和结束的行列。/// /summary/// param nameworkbook/param/// param namesheetIndex/param/// param namefirstRow/param/// param namelastRow/param/// param namefirstCol/param/// param namelastCol/parampublic 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(请先设置表格文件保存);
}
