WPF编程excel表格操作

摘要

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("请先设置表格文件保存！");
}