指定したセル内に代入した文字列が保存時に反映されない

前提・実現したいこと

Saveas()でのExcelデータの保存

発生している問題・エラーメッセージ

該当のソースコード

C#
1using System;
2using System.IO;
3using System.Text;
4using System.Text.RegularExpressions;
5using System.Threading;
6using System.Threading.Tasks;
7using System.Web;
8using System.Windows.Forms;
9using ClosedXML.Excel;
10
11namespace AuxiliaryTool
12{
13
14
15    public partial class Main : Form
16    {
17        public int TopCell;
18        public int LastCell;
19        public int WorkTopCell;
20        public int WorkLastCell;
21        public string Movie_change_word_default;
22        public string Copy_text;
23        public string TestXLPath /*= "C:\TestData\contact_test_data\test.xlsx"*/;
24        public IXLWorkbook XLData;
25        public IXLWorksheet WorkXL;
26        public int num_ = 5000;
27        public Task task;
28
29
30        public Main()
31        {
32            InitializeComponent();
33            PriInitialize();
34        }
35
36        private void PriInitialize()//起動処理
37        {
38            XLSetup(TestXLPath,WorkXL);
39            text_main.WatermarkText = "/*省略*/";
40            string[] str = { "A", "B", "C", "D","E" };
41            CmbObject[] obj_ = new CmbObject[str.Length];
42            int num__ = 0;
43            foreach(String str_ in str){
44                obj_[num__] = new CmbObject(num__++, str_);
45            }
46            Condition.Items.AddRange(obj_);
47            //Condition.SelectedIndex = 0;
48        }
49
50        private void XLInitialize()
51        {
52            num.Value = WorkTopCell;
53            Search_word.Text = InfoList(Search_num());
54            Work_range_min.Text = WorkTopCell.ToString();
55            Work_range_max.Text = WorkLastCell.ToString();
56        }
57
58        private string InfoList(int a)//Excel内の情報を読み込む
59        {
60            int row = a - TopCell + 2;
61            if(WorkXL != null)
62            {
63                Reason.Text = WorkXL.Cell(Cell_row(), 12).Value.ToString();
64                return WorkXL.Cell(row,4).Value.ToString() + " " + WorkXL.Cell(row, 5).Value.ToString() + " " + WorkXL.Cell(row, 6).Value.ToString();
65            }else { return "#N/A"; }
66
67        }
68
69        private int Cell_row()
70        {
71            return Search_num() - TopCell + 2;
72        }
73
74        private int Cell_row(int a)
75        {
76            return Search_num() - TopCell + 2 + a;
77        }
78
79        private int Search_num()//現在値を返す
80        {
81            return decimal.ToInt32(num.Value);
82        }
83
84        private int Search_num(int a)
85        {
86            return decimal.ToInt32(num.Value) + a;
87        }
88
89        private void XLSetup(string a, IXLWorksheet xl)//Excelデータの処理
90        {
91            WorkXL = XLOpen(a,xl);
92            if(WorkXL != null)
93            {
94                XLDataSet();
95                XLInitialize();
96            }
97        }
98
99        private IXLWorksheet XLOpen(string a,IXLWorksheet xl)//Excelを開く処理
100        {
101            try
102            {
103                XLData = new XLWorkbook(a);
104                return XLData.Worksheet(2);
105            }
106            catch (FileNotFoundException)
107            {
108                return xl;
109            }
110            catch (ArgumentException)
111            {
112                return xl;
113            }
114            catch(DirectoryNotFoundException)
115            {
116                return xl;
117            }
118
119        }
120
121        private void XLDataSet()//Excelを開く処理
122        {
123            IXLWorksheet data = XLData.Worksheet(4);
124            int row = 0;
125
126            int i = 1;
127            int j = 2;
128            TopCell = int.Parse(WorkXL.Cell(i + 1, j).Value.ToString());
129            num.Minimum = decimal.ToInt32(int.Parse(WorkXL.Cell(i + 1, j).Value.ToString()));
130            while (!WorkXL.Cell(++i, j).IsEmpty()) { }
131            LastCell = int.Parse(WorkXL.Cell(i - 1, j).Value.ToString());
132            num.Maximum = decimal.ToInt32(int.Parse(WorkXL.Cell(i - 1, j).Value.ToString()));
133
134            string CellColor = WorkXL.Cell(++row, 1).Style.Fill.BackgroundColor.ToString();
135            while (CellColor == "00000000" || CellColor == "Color Index: 64")
136            {
137                CellColor = WorkXL.Cell(++row, 1).Style.Fill.BackgroundColor.ToString();
138            }
139
140            WorkTopCell = row + TopCell - 2;
141
142            while (!(CellColor == "00000000" || CellColor == "Color Index: 64"))
143            {
144                CellColor = WorkXL.Cell(++row, 1).Style.Fill.BackgroundColor.ToString();
145            }
146
147            WorkLastCell = row + TopCell - 3;
148
149            CmbObject[] obj = new CmbObject[0];
150            for (int k = 1; !data.Cell(k, 2).IsEmpty();)
151            {
152                Array.Resize(ref obj, ++k);
153                obj[k - 2] = new CmbObject(k - 1, data.Cell(k,2).Value.ToString());
154            }
155            try {
156                Reason.Items.AddRange(obj);
157            }
158            catch(ArgumentNullException)
159            {
160
161            }
162        }
163
164
165        private void Config()
166        {
167            Movie_change_word_default = "/*省略*/";
168        }
169
170        private void URL_prev()//前に戻る処理
171        {
172            num_ = decimal.ToInt32(num.Value);
173            if (num.Minimum < num.Value)
174            {
175                num_--;
176                Search_word.Text = InfoList(Search_num() - 1);
177                //Reason.Text = WorkXL.Cell(Cell_row(-1),12).Value.ToString();
178            }
179
180            num.Value = num_;
181            var task = Task.Run(() => Thread.Sleep(0));
182            Thread.Sleep(10);
183            if (task.IsCompleted)
184            {
185                task = XL_save();
186            }
187        }
188
189        private void URL_next()//次
190        {
191            num_ = decimal.ToInt32(num.Value);
192            if (num.Maximum > num.Value)
193            {
194                num_++;
195                Search_word.Text = InfoList(Search_num() + 1);
196            }
197
198            num.Value = num_;
199            var task = Task.Run(() => Thread.Sleep(0));
200            Thread.Sleep(10);
201            if (task.IsCompleted)
202            {
203            task = XL_save();
204            }
205        }
206
207        private Task XL_save()
208        {
209            string str = File_path.Text.ToString();
210            string path = str.Substring(0, str.Length - 5) + "/*省略*/" + ".xlsx";
211
212            //var task = Task.Run(() => Thread.Sleep(0));
213            //Thread.Sleep(100);
214            try
215            {
216            if(task.IsCompleted || task.IsFaulted || task.IsCanceled)
217            {
218            task = Task.Run(() => XL_save_async(path));
219            }
220
221            }
222            catch (NullReferenceException)
223            {
224                task = Task.Run(() => XL_save_async(path));
225            }
226            //task.Wait();
227            return Task.WhenAll(task);
228        }
229
230        private Task XL_save_async(string path)
231        {
232            App_status.Text = "セーブ中";
233            XLData.SaveAs(path);
234            if (path != File_path.Text.ToString())
235            {
236
237            }
238            App_status.Text = "セーブ完了";
239                XLData = new XLWorkbook(path);
240                //XLSetup(path, WorkXL);
241            return Task.CompletedTask;
242        }      
243
244        private void File_path_DragDrop(object sender, DragEventArgs e)
245        {
246            string[] files = (string[])e.Data.GetData(DataFormats.FileDrop);
247            XLSetup(files[0], WorkXL);
248            File_path.Text = files[0];
249            XLData.SaveAs(File_path.Text.ToString().Substring(0, File_path.Text.ToString().Length - 5) + "_cfat" + ".xlsx");
250        }
251
252        private void File_path_DragEnter(object sender, DragEventArgs e)
253        {
254            if (e.Data.GetDataPresent(DataFormats.FileDrop))
255            {
256
257                // ドラッグ中のファイルやディレクトリの取得
258                string[] drags = (string[])e.Data.GetData(DataFormats.FileDrop);
259
260                foreach (string d in drags)
261                {
262                    if (!System.IO.File.Exists(d))
263                    {
264                        // ファイル以外であればイベント・ハンドラを抜ける
265                        return;
266                    }
267                }
268                e.Effect = DragDropEffects.Copy;
269            }
270        }
271
272        private void Main_FormClosed(object sender, FormClosedEventArgs e)
273        {
274            if(XLData != null)
275            {
276                var task = XL_save();
277                task.Wait();
278            }
279            
280        }
281
282        private void Reason_SelectedIndexChanged(object sender, EventArgs e)
283        {
284            if (WorkXL.Cell(Cell_row(), 12).Value.ToString() != Reason.Text)
285            {
286            WorkXL.Cell(Cell_row(), 12).Value = Reason.Text;
287            }
288        }
289
290    }
291
292    class CmbObject
293    {
294        public int Key { get; set; }
295        public string Value { get; set; }
296
297        public CmbObject(int Key, string Value)
298        {
299            this.Key = Key;
300            this.Value = Value;
301        }
302
303        public override string ToString()
304        {
305            return Value;
306        }
307    }
308
309    }
310}