使用ClosedXML开发Office之–Excel的开发

⼀、ClosedXML介绍

1.简单的说ClosedXML就是⼀个类库，主要⽤于开发Office组件。它也是基于OpenXML，但在它的基础上做了很多封装，⽐OpenXML开发更加容易、⽅便；2.ClosedXML是以OpenXML为基础的，所以在实际项⽬应⽤中，除了要添加对ClosedXML.dll应⽤，还必须添加DocumentFormat.OpenXml.dll的应⽤；3.ClosedXML.dll⽬前有.NET3.5和.NET4.0两个版本，可以去官⽹下载：；4.ClosedXML开发不需要Office组件的⽀持，可以摆脱对Office组件的依赖；⼆、ClosedXML开发实例

1.⽣成⼀个新的Excel，并对其进⾏插⼊数据，设置格式等

public class Program {

static void Main(string[] args) {

string path = @\"F:\\Test\\OpenXMLTest\est.xlsx\"; if (!Directory.Exists(@\"F:\\Test\\OpenXMLTest\")) {

Directory.CreateDirectory(@\"F:\\Test\\OpenXMLTest\"); }

if (File.Exists(path)) {

File.Delete(path); }

GetWorkBook(path); }

private static void GetWorkBook(string strFile) {

string[,] data = new string[,] { { \"年级\", \"班级\", \"姓名\", \"学号\" }, { \"⼋年级\", \"⼀班\", \"LiuHui\", \"080831\" }, { \"⼋年级\", \"⼆班\", \"LIYang\", \"080832\" }, { \"九年级\", \"⼀班\", \"WangHui\", \"080833\" }, { \"⼋年级\", \"三班\", \"LiMing\", \"080834 XLWorkbook workBook = new XLWorkbook();

IXLWorksheet workSheet = workBook.AddWorksheet(\"测试\"); IXLStyle style = workSheet.Style;

style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; style.Alignment.Vertical = XLAlignmentVerticalValues.Bottom; //设置底部边框及颜⾊

style.Border.BottomBorder = XLBorderStyleValues.MediumDashDot; style.Border.BottomBorderColor = XLColor.Red;

//设置顶部边框及颜⾊

style.Border.TopBorder = XLBorderStyleValues.SlantDashDot; style.Border.TopBorderColor = XLColor.Black;

//设置左部边框及颜⾊

style.Border.LeftBorder = XLBorderStyleValues.MediumDashDotDot; style.Border.LeftBorderColor = XLColor.Green; //设置右部边框及颜⾊

style.Border.RightBorder = XLBorderStyleValues.Hair; style.Border.RightBorderColor = XLColor.Yellow;

style.Font.Bold = true;

style.Font.FontColor = XLColor.Red; style.Font.FontName = \"微软雅⿊\"; style.Font.FontSize = 12; style.Font.Italic = true; style.Font.Shadow = false;

style.Font.Underline = XLFontUnderlineValues.Double;

//设置A1，B1的字体颜⾊为灰⾊

workSheet.Range(\"A1\", \"B1\").Style.Font.FontColor = XLColor.Gray; //把第5⾏第1列和第2列合并单元格 workSheet.Range(5, 1, 5, 2).Merge(); //设置第5⾏第1列和第2列内容左对齐

workSheet.Range(5, 1, 5, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; //设置第⼆列的宽度

workSheet.Column(2).Width = 30; //设置第4到7列的宽度

workSheet.Columns(4, 7).Width = 40;

for (int i = 0; i < data.GetLength(0);i++ ) {

for (int j = 0; j < data.GetLength(1);j++ ) {

workSheet.Cell(i + 1, j + 1).Value = data.GetValue(i, j); } }

workBook.SaveAs(strFile); } }