MyXls是一个操作Excel的开源类库,支持设置字体、列宽、行高(由BOSSMA实现)、合并单元格、边框、背景颜色、数据类型、自动换行、对齐方式等,通过众多项目的使用表现,证明MyXls对于创建简单格式的Excel文件十分快捷方便。
本文将通过实例的方式详细说明如何通过各种属性设置MyXls的样式,并附带示例程序的源代码。
List<PersonInfo> list = new List<PersonInfo>(); |
for ( int i = 1; i <= 200; i++) |
PersonInfo person = new PersonInfo() |
Gender = (i % 2 == 0 ? "男" : "女" ), |
int maxRecordCount = 100; |
XlsDocument xls = new XlsDocument(); |
xls.FileName = "MyXls-" + DateTime.Now.ToString( "yyyyMMddHHmmss" ) + ".xls" ; |
if (recordCount > maxRecordCount) |
sheetCount = ( int )Math.Ceiling(( decimal )recordCount / ( decimal )maxRecordCount); |
XF titleXF = xls.NewXF(); |
titleXF.HorizontalAlignment = HorizontalAlignments.Centered; |
titleXF.VerticalAlignment = VerticalAlignments.Centered; |
titleXF.UseBorder = true ; |
titleXF.TopLineStyle = 1; |
titleXF.TopLineColor = Colors.Black; |
titleXF.LeftLineStyle = 1; |
titleXF.LeftLineColor = Colors.Black; |
titleXF.RightLineStyle = 1; |
titleXF.RightLineColor = Colors.Black; |
titleXF.Font.FontName = "宋体" ; |
titleXF.Font.Bold = true ; |
titleXF.Font.Height = 12 * 20; |
XF columnTitleXF = xls.NewXF(); |
columnTitleXF.HorizontalAlignment = HorizontalAlignments.Centered; |
columnTitleXF.VerticalAlignment = VerticalAlignments.Centered; |
columnTitleXF.UseBorder = true ; |
columnTitleXF.TopLineStyle = 1; |
columnTitleXF.TopLineColor = Colors.Black; |
columnTitleXF.BottomLineStyle = 1; |
columnTitleXF.BottomLineColor = Colors.Black; |
columnTitleXF.LeftLineStyle = 1; |
columnTitleXF.LeftLineColor = Colors.Black; |
columnTitleXF.Pattern = 1; |
columnTitleXF.PatternBackgroundColor = Colors.Red; |
columnTitleXF.PatternColor = Colors.Default2F; |
dataXF.HorizontalAlignment = HorizontalAlignments.Centered; |
dataXF.VerticalAlignment = VerticalAlignments.Centered; |
dataXF.LeftLineStyle = 1; |
dataXF.LeftLineColor = Colors.Black; |
dataXF.BottomLineStyle = 1; |
dataXF.BottomLineColor = Colors.Black; |
dataXF.Font.FontName = "宋体" ; |
dataXF.Font.Height = 9 * 20; |
dataXF.UseProtection = false ; |
dataXF.TextWrapRight = true ; |
for ( int i = 1; i <= sheetCount; i++) |
sheet = xls.Workbook.Worksheets.Add( "人员信息表" ); |
sheet = xls.Workbook.Worksheets.Add( "人员信息表 - " + i); |
ColumnInfo col0 = new ColumnInfo(xls, sheet); |
col0.ColumnIndexStart = 0; |
sheet.AddColumnInfo(col0); |
ColumnInfo col1 = new ColumnInfo(xls, sheet); |
col1.ColumnIndexStart = 1; |
sheet.AddColumnInfo(col1); |
ColumnInfo col2 = new ColumnInfo(xls, sheet); |
col2.ColumnIndexStart = 2; |
sheet.AddColumnInfo(col2); |
ColumnInfo col3 = new ColumnInfo(xls, sheet); |
col3.ColumnIndexStart = 3; |
sheet.AddColumnInfo(col3); |
RowInfo rol1 = new RowInfo(); |
rol1.RowHeight = 16 * 20; |
rol1.RowIndexEnd = ( ushort )(maxRecordCount + 2); |
MergeArea titleArea = new MergeArea(1, 1, 1, 4); |
sheet.AddMergeArea(titleArea); |
Cells cells = sheet.Cells; |
Cell cell = cells.Add(1, 1, "人员信息统计表" , titleXF); |
cells.Add(1, 2, "" , titleXF); |
cells.Add(1, 3, "" , titleXF); |
cells.Add(1, 4, "" , titleXF); |
sheet.Rows[1].RowHeight = 40 * 20; |
cells.Add(2, 1, "序号" , columnTitleXF); |
cells.Add(2, 2, "姓名" , columnTitleXF); |
cells.Add(2, 3, "性别" , columnTitleXF); |
columnTitleXF.RightLineStyle = 1; |
columnTitleXF.RightLineColor = Colors.Black; |
cells.Add(2, 4, "年龄" , columnTitleXF); |
sheet.Rows[2].RowHeight = 18 * 20; |
for ( int j = 0; j < maxRecordCount; j++) |
int k = (i - 1) * maxRecordCount + j; |
cells.Add(rowIndex, 1, k + 1, dataXF); |
cells.Add(rowIndex, 2, list[k].RealName, dataXF); |
cells.Add(rowIndex, 3, list[k].Gender, dataXF); |
Cell lastCell = cells.Add(rowIndex, 4, list[k].Age, dataXF); |
lastCell.RightLineStyle = 1; |
lastCell.RightLineColor = Colors.Black; |