C# 导出到excel 相关技巧
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);
}
```
Slowly I find myself
