一、ClosedXML介绍
1.简单的说ClosedXML就是一个类库,主要用于开发Office组件。它也是基于OpenXML,但在它的基础上做了很多封装,比OpenXML开发更加容易、方便;
2.ClosedXML是以OpenXML为基础的,所以在实际项目应用中,除了要添加对ClosedXML.dll应用,还必须添加DocumentFormat.OpenXml.dll的应用;
3.ClosedXML.dll目前有.NET3.5和.NET4.0两个版本,可以去官网下载:http://closedxml.codeplex.com;
4.ClosedXML开发不需要Office组件的支持,可以摆脱对Office组件的依赖;
二、ClosedXML开发实例
1.生成一个新的Excel,并对其进行插入数据,设置格式等
public class Program
{
static void Main(string[] args)
{
string path = @"F:\Test\OpenXMLTest\test.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);
}
}