C#写入模板excel数据

以你之姓@ 2023-09-28 08:39 23阅读 0赞

public ReturnNode exportReviewResult()
            {
                List<ReplaceExcelData> repData = new List<ReplaceExcelData>();
                repData.Add(new ReplaceExcelData(3, 2, "名称1"));
                repData.Add(new ReplaceExcelData(3, 7, "单位1"));
                repData.Add(new ReplaceExcelData(3, 14, "单位1"));
                string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
                string tempFileName = localPath + "exporttemp.xlsx";
                string newFile = localPath + fileName;
                using (ExcelHelper excelHelper = new ExcelHelper(newFile))
                {
                    excelHelper.ReplaceDataToExcel(tempFileName,0, repData);
                    return ReturnNode.ReturnSuccess(fileName);
                }
            }

using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Diagnostics;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace YFAPICommon.Libs
    {
        public class ReplaceExcelData
        {
            public int rowIndex { set; get; }
            public int cellIndex { set; get; }
            public object value { set; get; }
            public ReplaceExcelData(int _row,int _cell,object _value)
            {
                this.rowIndex = _row;
                this.cellIndex = _cell;
                this.value = _value;
            }
        }
    
        class ExcelHelper : IDisposable
        {
            private string fileName = null; //文件名
            private NPOI.SS.UserModel.IWorkbook workbook = null;
            private FileStream fs = null;
            private bool disposed;
    
            public ExcelHelper(string fileName)
            {
                this.fileName = fileName;
                disposed = false;
            }
    
    
            public int ReplaceDataToExcel(string tempFilePath,int sheetIndex,List<ReplaceExcelData> replaceData)
            {
                int count = 0;
                ISheet sheet = null;
                fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
    
                using (FileStream tempfs = new FileStream(tempFilePath, FileMode.Open, FileAccess.Read))
                {
                    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                        workbook = new XSSFWorkbook(tempfs);
                    else if (fileName.IndexOf(".xls") > 0) // 2003版本
                        workbook = new HSSFWorkbook(tempfs);
                }
    
                try
                {
                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(sheetIndex);
                    }
                    else
                    {
                        return -1;
                    }
    
                    foreach(var node in replaceData)
                    {
                        var row = sheet.GetRow(node.rowIndex);
                        var valuestr = node.value.ToString();
                        double valDouble = 0;
                        if(double.TryParse(valuestr,out valDouble))
                        {
                            row.GetCell(node.cellIndex).SetCellValue(valDouble);
                        }
                        else
                        {
                            row.GetCell(node.cellIndex).SetCellValue(valuestr);
                        }
                        count++;
                    }
                    workbook.Write(fs); //写入到excel
                    return count;
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                    return -1;
                }
            }
    
        }
    }