C#创建Excel表格（样式设置）
首先 引入 Excel的命名空间
using Microsoft.Office.Interop.Excel;
创建Excel需要 创建Excel ' 运行环境' Application 类
Excel中 由 ‘工作薄’ Workbook 和 '页' Worksheet 两个类组成
层级 依赖 关系为
Application
Workbook
Worksheet
//创建一个Excel运行环境
Application app = new Application();
//
app.Visible = false;
//创建Excel中的工作薄
Workbook wb = (Workbook)app.Workbooks.Add(Missing.Value);
//创建Excel工作薄中的 页 sheet
Worksheet sheet =(Worksheet ) wb.ActiveSheet;
sheet下 由 单元格组成 cells sheet.cells[RowIndex,ColumnIndex]= 根据行号和列号来定位单元格 赋值
设置表头 给 第二列赋值 第一列还有用
View Code
sheet.Cells[2, 1] = "航运公司";//也可以这样赋值
sheet.Cells[2, 2] = "船名";
sheet.Cells[2, 3] = "航次";
sheet.Cells[2, 4] = "流向";
sheet.Cells[2, 5] = "装港";
sheet.Cells[2, 6] = "供货方";
sheet.Cells[2, 7] = "性质";
sheet.Cells[2, 8] = "煤质";
sheet.Cells[2, 9] = "贸易性质";
sheet.Cells[2, 10] = "煤种";
sheet.Cells[2, 11] = "状态";
sheet.Cells[2, 12] = "锚地时间";
sheet.Cells[2, 13] = "手续办理时间";
sheet.Cells[2, 14] = "靠泊时间";
sheet.Cells[2, 15] = "载重";
sheet.Cells[2, 16] = "离港时间";
sheet.Cells[2, 17] = "在港时间";
sheet.Cells[2, 18] = "锚地时间";
sheet.Cells[2, 19] = "靠御时间";
sheet.Cells[2, 20] = "离港时间";
sheet.Cells[2, 21] = "御港时间";
sheet.Cells[2, 22] = "预估滞期费";
sheet.Cells[2, 23] = "卸港效率";
给第一列赋值
//这个 列的Index无所谓 只要在 想要合并的单元格列（如 合并 12-22 列） Index的 范围内就行
sheet.Cells[1, 12] = "装港";
sheet.Cells[1, 22] = "卸港";
合并单元格 （表头）； 这时要用到 Range 可以理解为 域 （区域） 我们设置样式 是都是设置的Range
View Code
//合并 单元格 设置表头
sheet.get_Range("A1", "A2").Merge(sheet.get_Range("A1", "A2").MergeCells);
sheet.get_Range("B1", "B2").Merge(sheet.get_Range("B1", "B2").MergeCells);
sheet.get_Range("C1", "C2").Merge(sheet.get_Range("C1", "C2").MergeCells);
sheet.get_Range("D1", "D2").Merge(sheet.get_Range("D1", "D2").MergeCells);
sheet.get_Range("E1", "E2").Merge(sheet.get_Range("E1", "E2").MergeCells);
sheet.get_Range("F1", "F2").Merge(sheet.get_Range("F1", "F2").MergeCells);
sheet.get_Range("G1", "G2").Merge(sheet.get_Range("G1", "G2").MergeCells);
sheet.get_Range("H1", "H2").Merge(sheet.get_Range("H1", "H2").MergeCells);
sheet.get_Range("I1", "I2").Merge(sheet.get_Range("I1", "I2").MergeCells);
sheet.get_Range("J1", "J2").Merge(sheet.get_Range("J1", "J2").MergeCells);
sheet.get_Range("K1", "K2").Merge(sheet.get_Range("K1", "K2").MergeCells);
sheet.get_Range("L1", "Q1").Merge(sheet.get_Range("L1", "Q1").MergeCells);
sheet.get_Range("R1", "W1").Merge(sheet.get_Range("R1", "W1").MergeCells);
//得到 Range 范围 域对象 Range range = sheet.get_Range("A1", "W69"); 从A1到 W69 的区域 A是表头编号 后面的数组时 列的Index
设置 样式
View Code
//得到 Range 范围 域对象
Range range = sheet.get_Range("A1", "W69");
//设置 该range内的 样式 颜色 边框
Console.WriteLine("该域内有"+range.Rows.Count.ToString ()+"行数据");
//设置Excel表格的 列宽
sheet.get_Range("L1" , "L69" ).ColumnWidth = 30;
sheet.get_Range("P1", "Q69").ColumnWidth = 30;
sheet.get_Range("R1", "U69").ColumnWidth = 30;
//设置 域 Range 的颜色 从 A1到W1
sheet.get_Range("A1", "W1").Interior.ColorIndex = 15;
sheet.get_Range("A2", "W2").Interior.ColorIndex = 15;
//设置 某个域 range 被选中
//sheet.get_Range("A1", "W1").Select();
//sheet.get_Range("A2", "W2").Select();
sheet.get_Range("C3", "W3").Select();
//左右 设置 选中域内的 Excel单元格从C 到W 是活动的 前面的A B 为固定的
//但是 上下 方向 表头（这里表头合并两行 ）没有固定 选C3 到W3（表示从C的第三行开始 为 活动 的 上面两行为固定的）
app.ActiveWindow.FreezePanes = true;
//设置 某个域range内 单元格里的字体颜色
sheet.get_Range("A1", "W2").Font.Color = -16744448;//（搜索Excel颜色对照表）
sheet.get_Range("A3", "W24").Font.Color = -16776961;
//文字 居中
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Size = 10;
range.Borders.LineStyle = 1;
//固定 列的原有 格式
//range.EntireColumn.AutoFit();
//设置边框
range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlMedium;
range.WrapText = true;
赋值：
完整代码：
View Code
//创建一个Excel运行环境
Application app = new Application();
//
app.Visible = false;
//创建Excel中的工作薄
Workbook wb = (Workbook)app.Workbooks.Add(Missing.Value);
//创建Excel工作薄中的 页 sheet
Worksheet sheet =(Worksheet ) wb.ActiveSheet;
sheet.Cells[2, 1] = "航运公司";//也可以这样赋值
sheet.Cells[2, 2] = "船名";
sheet.Cells[2, 3] = "航次";
sheet.Cells[2, 4] = "流向";
sheet.Cells[2, 5] = "装港";
sheet.Cells[2, 6] = "供货方";
sheet.Cells[2, 7] = "性质";
sheet.Cells[2, 8] = "煤质";
sheet.Cells[2, 9] = "贸易性质";
sheet.Cells[2, 10] = "煤种";
sheet.Cells[2, 11] = "状态";
sheet.Cells[2, 12] = "锚地时间";
sheet.Cells[2, 13] = "手续办理时间";
sheet.Cells[2, 14] = "靠泊时间";
sheet.Cells[2, 15] = "载重";
sheet.Cells[2, 16] = "离港时间";
sheet.Cells[2, 17] = "在港时间";
sheet.Cells[2, 18] = "锚地时间";
sheet.Cells[2, 19] = "靠御时间";
sheet.Cells[2, 20] = "离港时间";
sheet.Cells[2, 21] = "御港时间";
sheet.Cells[2, 22] = "预估滞期费";
sheet.Cells[2, 23] = "卸港效率";
//这个 列的Index无所谓 只要在 想要合并的单元格列（如 合并 12-22 列） Index的 范围内就行
sheet.Cells[1, 12] = "装港";
sheet.Cells[1, 22] = "卸港";
//合并 单元格 设置表头
sheet.get_Range("A1", "A2").Merge(sheet.get_Range("A1", "A2").MergeCells);
sheet.get_Range("B1", "B2").Merge(sheet.get_Range("B1", "B2").MergeCells);
sheet.get_Range("C1", "C2").Merge(sheet.get_Range("C1", "C2").MergeCells);
sheet.get_Range("D1", "D2").Merge(sheet.get_Range("D1", "D2").MergeCells);
sheet.get_Range("E1", "E2").Merge(sheet.get_Range("E1", "E2").MergeCells);
sheet.get_Range("F1", "F2").Merge(sheet.get_Range("F1", "F2").MergeCells);
sheet.get_Range("G1", "G2").Merge(sheet.get_Range("G1", "G2").MergeCells);
sheet.get_Range("H1", "H2").Merge(sheet.get_Range("H1", "H2").MergeCells);
sheet.get_Range("I1", "I2").Merge(sheet.get_Range("I1", "I2").MergeCells);
sheet.get_Range("J1", "J2").Merge(sheet.get_Range("J1", "J2").MergeCells);
sheet.get_Range("K1", "K2").Merge(sheet.get_Range("K1", "K2").MergeCells);
sheet.get_Range("L1", "Q1").Merge(sheet.get_Range("L1", "Q1").MergeCells);
sheet.get_Range("R1", "W1").Merge(sheet.get_Range("R1", "W1").MergeCells);
//得到 Range 范围 域对象
Range range = sheet.get_Range("A1", "W69");
//设置 该range内的 样式 颜色 边框
Console.WriteLine("该域内有"+range.Rows.Count.ToString ()+"行数据");
//设置Excel表格的 列宽
sheet.get_Range("L1" , "L69" ).ColumnWidth = 30;
sheet.get_Range("P1", "Q69").ColumnWidth = 30;
sheet.get_Range("R1", "U69").ColumnWidth = 30;
//设置 域 Range 的颜色 从 A1到W1
sheet.get_Range("A1", "W1").Interior.ColorIndex = 15;
sheet.get_Range("A2", "W2").Interior.ColorIndex = 15;
//设置 某个域 range 被选中
//sheet.get_Range("A1", "W1").Select();
//sheet.get_Range("A2", "W2").Select();
sheet.get_Range("C3", "W3").Select();
//左右 设置 选中域内的 Excel单元格从C 到W 是活动的 前面的A B 为固定的
//但是 上下 方向 表头（这里表头合并两行 ）没有固定 选C3 到W3（表示从C的第三行开始 为 活动 的 上面两行为固定的）
app.ActiveWindow.FreezePanes = true;
//设置 某个域range内 单元格里的字体颜色
sheet.get_Range("A1", "W2").Font.Color = -16744448;//（搜索Excel颜色对照表）
sheet.get_Range("A3", "W24").Font.Color = -16776961;
//文字 居中
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.Font.Size = 10;
range.Borders.LineStyle = 1;
//固定 列的原有 格式
//range.EntireColumn.AutoFit();
//设置边框
range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
range.Borders.get_Item(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlMedium;
range.WrapText = true;
//赋值
for (int i = 0; i <22; i++)
{
sheet.Cells[i + 3, 1] = i.ToString();
sheet.Cells[i + 3, 2] = i.ToString();
sheet.Cells[i + 3, 3] = i.ToString();
sheet.Cells[i + 3, 4] = i.ToString();
sheet.Cells[i + 3, 5] = i.ToString();
sheet.Cells[i + 3, 6] = i.ToString();
sheet.Cells[i + 3, 7] = i.ToString();
sheet.Cells[i + 3, 8] = i.ToString();
sheet.Cells[i + 3, 9] = i.ToString();
sheet.Cells[i + 3, 10] = i.ToString();
sheet.Cells[i + 3, 11] = i.ToString();
}
wb.Saved = true;
//保存工作薄
app.ActiveWorkbook.SaveCopyAs(@"C:\Users\ITCA\Desktop\新建Excel.xlsx");
finally
{
#region 释放内存
wb.Close(null, null, null);
app.Workbooks.Close();
app.Quit();
Marshal.ReleaseComObject((object)app);
Marshal.ReleaseComObject((object)wb);
Marshal.ReleaseComObject((object)sheet);
GC.Collect();
#endregion
}
