init

songjun

2024-09-04 cc908053e0b5075fbfd27350b6da4b39bca9e550

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
 
namespace BatchService.Framework.Utility
{
    /// <summary>
    /// Exl坐标
    /// </summary>
    public class TableValue
    {
        //行号
        public int GetRow { get; set; }
        //列号
        public int GetCell { get; set; }
 
        //坐标值
        public string GetValue { get; set; }
      
    }
 
    public class NPOIExcel
    {
        /// <summary>
        /// 修改excel数据
        /// </summary>
        /// <param name="outputFile">excel路径</param>
        /// <param name="sheetname">excel表单</param>
        /// <param name="list">修改集合</param>
        public static void UpdeteExcel(string outputFile, string sheetname, List<TableValue> list)
        {
            LogHelper.Debug("UpdeteExcel修改方法");
            try
            {
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
                XSSFWorkbook hssfworkbook = new XSSFWorkbook(readfile);
                ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
                for (int i = 0; i < list.Count; i++)
                {
                    try
                    {
                        sheet1.GetRow(list[i].GetRow).GetCell(list[i].GetCell).SetCellValue(list[i].GetValue);
                    }
                    catch (Exception ex)
                    {
                        LogHelper.Debug(ex.Message);
                        // wl.WriteLogs(ex.ToString());
                        throw;
                    }
                }
                try
                {
                    readfile.Close();
                    FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                    hssfworkbook.Write(writefile);
                    writefile.Close();
                }
                catch (Exception ex)
                {
                    LogHelper.Debug(ex.Message);
                    // wl.WriteLogs(ex.ToString());
                }
            }
            catch (Exception ex)
            {
                LogHelper.Debug("初始化失败");
                LogHelper.Debug(ex.Message.ToString());
            }
          
           
        }
        /// <summary>
        /// 返回修改后的Exl工作表
        /// </summary>
        /// <param name="TempletFileName">摸版路径</param>
        /// <param name="TableValues">修改坐标值</param>
        /// <returns>返回修改后的工作表</returns>
        public static XSSFWorkbook GetExcelValue(string TempletFileName, string Sheetnub,  List<TableValue> TableValues)
        {
            //返回工作表
            XSSFWorkbook hssfworkbook=null;
            //打开摸版文件
            using (FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new XSSFWorkbook(file);
                file.Close();
            }
            //获取摸版表单的对象
            XSSFSheet ws = (XSSFSheet)hssfworkbook.GetSheet(Sheetnub);
            //赋值表单      
            if (TableValues != null && TableValues.Count > 0)
            {
                int x = 0;
                TableValues.ForEach(Call =>
                {
                    x += 1;
                    if (Call.GetValue==null)
                    {
                        ws.GetRow(Call.GetRow).GetCell(Call.GetCell).SetCellValue("");
                    }
                    else
                    {
                        ws.GetRow(Call.GetRow).GetCell(Call.GetCell).SetCellValue(Call.GetValue);
                    }
 
                   
                });
            }
            return  hssfworkbook;
        }
    }
}