1 ## Exece表格添加批注方法
2
3 ```C#
4 var demon = strCommonModel;
5 Comment comment = objSheet.Comments[intLines, 2];
6 if (comment == null)
7 {
8 int intCommentNO = objSheet.Comments.Add(intLines, 2);
9 comment = objSheet.Comments[intCommentNO];
10 }
11 comment.WidthCM = 10;
12 DataTable dt = GetPanelSnellist(strCommonModel);
13 if (dt.Rows.Count > 0)
14 {
15 comment.Note = " " + strCommonModel + " \r\n " +
16 "EOL Date："+dt.Rows[0]["PlannedEOL"].ToString()+" \r\n " +
17 "EOL Status: " + dt.Rows[0]["EOLStatus"].ToString() + " \r\n " +
18 "替代Source：" + dt.Rows[0]["NewProduct"].ToString() + "";
19 objSheet.Cells[intLines, cintColPanel].PutValue(strCommonModel);
20 }
21 else
22 {
23 comment.Note = " " + strCommonModel + " \r\n " +
24 "EOL Date：无 \r\n " +
25 "EOL Status: 无 \r\n " +
26 "替代Source：无";
27 objSheet.Cells[intLines, cintColPanel].PutValue(strCommonModel);
28 }
29 ```
1 ## 添加筛选项
2
3 ```C#
4 //1行到7行添加筛选项
5 objSheet.AutoFilter.Range = CellsHelper.CellIndexToName(1, 0) + ":" +
6 CellsHelper.CellIndexToName(1, 7);
7 ```
8
9 ## 自動調整行高以適應內容
10
11 ```C#
12
13 // 自動調整行高以適應內容
14 // 自動調整行高以適應內容
15 AutoFitterOptions options = new AutoFitterOptions();
16 options.AutoFitMergedCells = true;
17 sheet.AutoFitRows(options);
18 ```
19
20 ## 报表公式计算优化速度语法
21
22 ```C# 代码出处，E:\工作\AD\DotNet\Src\QAS\SCM\SPS\Web\SPS\Bll\report\tb_match_rate_Bll.cs
23 if (EOHSumList?.Count > 0)
24 {
25 formula += $"+SUM({CellsHelper.CellIndexToName(EOHSumList.Min(), iColumn)}:{CellsHelper.CellIndexToName(EOHSumList.Max(), iColumn)})";
26 }
27
28 // Panel、SSB要扣掉所有Demand
29 if (RowPartName == MatchRatePartName.Panel || RowPartName == MatchRatePartName.SSB)
30 {
31 if (DemandList?.Count > 0)
32 {
33 formula += $"-SUM({CellsHelper.CellIndexToName(DemandList.Min(), iColumn)}:{CellsHelper.CellIndexToName(DemandList.Max(), iColumn)})";
34 }
35 }
36 // SKD只有扣掉對應的Demand
37 else if (RowPartName == MatchRatePartName.SKD)
38 {
39 if(demandRowNo?.Count > 0)
40 {
41 formula += $"-SUM({string.Join(", ", demandRowNo.Select(rowNo => CellsHelper.CellIndexToName(rowNo, iColumn)))})";
42 }
43 }
44 ```
45
46 ## 在原有表单新增工作表
47
48 ```C#
49 Workbook book = new Workbook(templatefileName);
50 Worksheet sheet = book.Worksheets[0];
51 Worksheet newSheet = book.Worksheets.Add("whereuse");
52 ```
53
54 ## 直接插入表操作方法
55
56 ```C#
57 Workbook wb = new Workbook();
58 Worksheet sheet = wb.Worksheets[0];
59 Style stTitle = wb.CreateStyle();
60 sheet.Name = "BOM未建";
61 ImportTableOptions importTable = new ImportTableOptions();
62 importTable.CheckMergedCells = true;
63 sheet.Cells.ImportData(BomDt, 0, 0, importTable);
64 ```
65
66 ## 设置颜色方法之一
67
68 ``` C#
69 string InquiryFile = HttpContext.Current.Server.MapPath("\\Download\\DownloadFormat\\PanelSupplierRtf.xlsx");
70 if (!File.Exists(InquiryFile)) { throw new Exception("NO Template"); }
71 //设置方法名
72 Workbook workbook = new Workbook();
73 //读取文件内容
74 workbook.Open(InquiryFile);
75 //将文件读取到worksheet方法里，进行读取
76 Worksheet worksheet = workbook.Worksheets[0];
77 //读取EXCEL表格里行设置的颜色
78 Style brown = worksheet.Cells[1, 9].GetStyle();//深棕色
79 //文本读取对应类型剧中
80 //HorizontalAlignment 水平对齐 TextAlignmentType.Center; 剧中
81 brown.HorizontalAlignment = TextAlignmentType.Center;
82 //VerticalAlignment 垂直对其 TextAlignmentType.Center; 剧中
83 brown.VerticalAlignment = TextAlignmentType.Center;
84 Style blue = worksheet.Cells[1, 10].GetStyle();//浅蓝色
85 blue.HorizontalAlignment = TextAlignmentType.Center;
86 blue.VerticalAlignment = TextAlignmentType.Center;
87 Style lightBrown = worksheet.Cells[3, 0].GetStyle();//浅棕色
88 lightBrown.HorizontalAlignment = TextAlignmentType.Center;
89 lightBrown.VerticalAlignment = TextAlignmentType.Center;
90 Style green = worksheet.Cells[4, 8].GetStyle();//浅绿色
91 Style lightGreen = worksheet.Cells[3, 8].GetStyle();//浅绿色
92
93
94 //给第一行第一列到第二行第二列总共4个单元格设置样式
95 Range range= worksheet.Cells.CreateRange(0, 0, 2, 2);
96 range.ApplyStyle(style, new StyleFlag() { All=true})
97
98
99
100 ```
101
102 ## DataRow表重新排序方法
103
104 ```C#
105
106 newDt.DefaultView.Sort = "vendorCode,vendor,inch,TPVModel";
107 //DefaultView 获取可能包含筛选视图或游标位置的表的自定义视图。
108 //ToTable 基于现有的 System.Data.DataView 中的行，创建并返回一个新的 System.Data.DataTable。
109 newDt = newDt.DefaultView.ToTable();
110 ```
## Exece表格添加批注方法
```C# vardemon = strCommonModel; Commentcomment = objSheet.Comments[intLines, 2]; if (comment == null) { intintCommentNO = objSheet.Comments.Add(intLines, 2); comment = objSheet.Comments[intCommentNO]; } comment.WidthCM = 10; DataTabledt = GetPanelSnellist(strCommonModel); if (dt.Rows.Count > 0) { comment.Note = " " + strCommonModel + " \r\n " + "EOL Date："+dt.Rows[0]["PlannedEOL"].ToString()+" \r\n " + "EOL Status: " + dt.Rows[0]["EOLStatus"].ToString() + " \r\n " + "替代Source：" + dt.Rows[0]["NewProduct"].ToString() + ""; objSheet.Cells[intLines, cintColPanel].PutValue(strCommonModel); } else { comment.Note = " " + strCommonModel + " \r\n " + "EOL Date：无 \r\n " + "EOL Status: 无 \r\n " + "替代Source：无"; objSheet.Cells[intLines, cintColPanel].PutValue(strCommonModel); } ``` 标签：comment,C#,excel,Cells,导出,strCommonModel,objSheet,dt From： https://www.cnblogs.com/SDdemon/p/18151045