C#：Excel美化(字体颜色/单元格边框&颜色/单元格合并)

石马是石马

已于 2024-01-19 14:52:03 修改

阅读量1.6k

点赞数 10

分类专栏： C# 文章标签： excel

于 2024-01-19 14:49:29 首次发布

本文链接：https://blog.csdn.net/m0_57164983/article/details/135697246

版权

C# 专栏收录该内容

4 篇文章

订阅专栏

using Excel = Microsoft.Office.Interop.Excel;

1. 字体设置（针对Range）

// 字体颜色/字体加粗
                    Excel.Range cellFont1 = ws1.Cells[1, 1];
                    Excel.Range cellFont2 = ws1.Cells[objDataArr.GetLength(0), objDataArr.GetLength(1)];
                    ws1.get_Range(cellFont1, cellFont2).Font.Bold = true; // 加粗
                    ws1.get_Range(cellFont1, cellFont2).Font.ColorIndex = Color.FromArgb(0, 0, 0); // 黑色
                    ws1.get_Range(cellFont1, cellFont2).EntireRow.AutoFit(); // 自动适应行宽
                    ws1.get_Range(cellFont1, cellFont2).EntireColumn.AutoFit(); // 自动适应列宽
                    ws1.get_Range(cellFont1, cellFont2).HorizontalAlignment = Excel.Constants.xlCenter; //水平居中
                    ws1.get_Range(cellFont1, cellFont2).VerticalAlignment = Excel.Constants.xlCenter; //垂直居中
                    ws1.get_Range(cellFont1, cellFont2).Interior.Color = Color.FromArgb(255, 255, 255); // 白色

2. 单元格边框设置（针对Cells）

 边框：上下左右添加连续线段
                    for (int i = 1; i <= objDataArr.GetLength(0); i++)
                    {
                        for (int j = 1; j <= objDataArr.GetLength(1); j++)
                        {
                            Excel.Range cellBorders = ws1.Cells[i, j];
                            cellBorders.Borders.Weight = 2;
                            cellBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
                            cellBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
                            cellBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
                            cellBorders.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;

                        }
                    }

3. 单元格填充颜色（针对Range）

//-- 单元格颜色填充：前2行 --
                    Excel.Range cellTitle1 = ws1.Cells[1, 1];
                    Excel.Range cellTitle2 = ws1.Cells[2, objDataArr.GetLength(1)];
                    ws1.get_Range(cellTitle1, cellTitle2).Interior.Color = Color.FromArgb(184, 204, 228);

//-- 单元格颜色填充：前5数据行 --
                    Excel.Range cellNG1 = ws1.Cells[3, 1];
                    Excel.Range cellNG2 = ws1.Cells[7, objDataArr.GetLength(1)];
                    if (dayEquipNumbers.Max() < 5)
                    {
                        cellNG1 = ws1.Cells[3, 1];
                        cellNG2 = ws1.Cells[objDataArr.GetLength(0) - 1, objDataArr.GetLength(1)];
                    }
                    ws1.get_Range(cellNG1, cellNG2).Interior.Color = Color.FromArgb(242, 220, 219);

//-- 单元格颜色填充：最后一行-合计 --
                    Excel.Range cellSum1 = ws1.Cells[objDataArr.GetLength(0), 1];
                    Excel.Range cellSum2 = ws1.Cells[objDataArr.GetLength(0), objDataArr.GetLength(1)];
                    ws1.get_Range(cellSum1, cellSum2).Interior.Color = Color.FromArgb(255, 255, 0);

4. 字体颜色（针对Cells）

// 持续ng-标记红色
                    int[] cellNgRow = { 3, 7 };
                    if (dayEquipNumbers.Max() < 5)
                    {
                        cellNgRow[1] = objDataArr.GetLength(0) - 1;
                    }
                    for (int rowi = cellNgRow[0]; rowi <= cellNgRow[1]; rowi++)
                    {
                        for (int dayInd = 0; dayInd < dayEquipNumbers.Length; dayInd++)
                        {
                            int coli = 2 + dayInd * 2;
                            if (objDataArr[rowi - 1, coli - 1] != null)
                            {
                                string equip = objDataArr[rowi - 1, coli - 1].ToString();
                                int equipInd = Array.IndexOf(NgEquip_New, equip);
                                if (equipInd != -1)
                                {
                                    Excel.Range cellFontNg = ws1.Cells[rowi, coli];
                                    cellFontNg.Font.Color = Color.FromArgb(255, 0, 0); // 红色
                                }
                            }
                        }
                    }

5. 单元格合并（针对Range）

// 合并
                    Microsoft.Office.Interop.Excel.Range mergeStart_0= ws1.Cells[1, 1];
                    Microsoft.Office.Interop.Excel.Range mergeEnd_0 = ws1.Cells[2, 1];
                    ws1.get_Range(mergeStart_0, mergeEnd_0).MergeCells = true;

                    foreach (int colInd in mergeColInd)
                    {
                        Microsoft.Office.Interop.Excel.Range mergeStart_1 = ws1.Cells[1, colInd];
                        Microsoft.Office.Interop.Excel.Range mergeEnd_1 = ws1.Cells[1, colInd + 1];
                        ws1.get_Range(mergeStart_1, mergeEnd_1).MergeCells = true;

                        Microsoft.Office.Interop.Excel.Range mergeStart_2 = ws1.Cells[2 + dayEquipNumbers.Max() + 1, colInd];
                        Microsoft.Office.Interop.Excel.Range mergeEnd_2 = ws1.Cells[2 + dayEquipNumbers.Max() + 1, colInd + 1];
                        ws1.get_Range(mergeStart_2, mergeEnd_2).MergeCells = true;
                    }

备注1：