xiaomiMes/MainForm/ClassFile/NPOIOperate.cs

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace MainForm.ClassFile
{
    public class NPOIOperate
    {
        private static Mutex mutex = new Mutex();
        /// <summary>
        /// 向已存在的excel写入数据
        /// </summary>
        /// <param name="fileName">excel路径</param>
        /// <param name="headData">列索引<列索引，单元格值></param>
        /// <param name="cellData">列索引<列索引，单元格值></param>
        public static string AddExcelData(string fileName, IDictionary<int, string> headData, IDictionary<int, string> cellData)
        {
            mutex.WaitOne();
            string ret = string.Empty;
            FileStream fileRead = null;
            FileStream fileWrite = null;
            try
            {
                if (!File.Exists(fileName))
                    CreateExcel(fileName, headData);
                IWorkbook workbook = null;
                //读
                fileRead = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);

                workbook = new HSSFWorkbook(fileRead);
                ISheet sheet = workbook.GetSheetAt(0);
                IRow row = sheet.CreateRow(sheet.LastRowNum + 1);
                ICell cell = null;
                foreach (KeyValuePair<int, string> keyValue in cellData)
                {
                    cell = row.CreateCell(keyValue.Key);
                    cell.SetCellValue(keyValue.Value);
                }                //写
                fileWrite = new FileStream(fileName, FileMode.Open, FileAccess.Write, FileShare.ReadWrite);

                workbook.Write(fileWrite);
                workbook.Close();
                ret = "写入成功";
            }
            catch(Exception ex)
            {
                ret = ex.Message.ToString();
            }
            finally
            {
                fileRead?.Close();
                fileWrite?.Close();
            }
            mutex.ReleaseMutex();
            return ret;
        }
        /// <summary>
        /// 创建excel写入头
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="headData"></param>
        public static void CreateExcel(string fileName, IDictionary<int, string> headData)
        {
            using (FileStream fileWrite = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.Read))
            {
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("加工数据");//获取工作表
                IRow row = sheet.CreateRow(0); //创建新行
                ICell cell = null;
                foreach (KeyValuePair<int, string> keyValue in headData)
                {
                    cell = row.CreateCell(keyValue.Key);
                    cell.SetCellValue(keyValue.Value);
                }
                workbook.Write(fileWrite);
                workbook.Close();
            }
        }
    }
}