转自http://www.oschina.net/code/snippet_156249_4855
目前MyXls已经实现了单元格(cell)的格式设置,包括文本颜色、文本大小、字体、单位格边框、底色、列宽、行高,合并单元格,多个sheet页等功能。以下是MyXLS组件的一些用法:
1.创建一个Excel文档:
XlsDocument xls = new XlsDocument();
2.创建一个WorkSheet:
Worksheet ws = xls.Workbook.Worksheets.Add("WorkSheet1");
3.指定列格式:
ColumnInfo colInfo = new ColumnInfo(xls, ws);
colInfo.ColumnIndexStart = ;
colInfo.ColumnIndexEnd = 17;
colInfo.Width = 15 * 256;
ws.AddColumnInfo(colInfo);
列格式必须每次都要重新定义,一个列格式不能重复使用。
4.指定单元格样式:
XF xf = xls.NewXF();
xf.HorizontalAlignment = HorizontalAlignments.Centered;
xf.VerticalAlignment = VerticalAlignments.Centered;
xf.Pattern = 1;
xf.PatternColor = Colors.Default30;
xf.UseBorder = true;
xf.TopLineStyle = 1;
xf.TopLineColor = Colors.Black;
xf.BottomLineStyle = 1;
xf.BottomLineColor = Colors.Black;
xf.LeftLineStyle = 1;
xf.LeftLineColor = Colors.Black;
xf.RightLineStyle = 1;
xf.RightLineColor = Colors.Black;
xf.Font.Bold = true;
xf.Font.Height = 11 * 20;
xf.Font.ColorIndex = 1;
5.给单元格赋值:
ws.Cells.Add(2, 3, "金额(万元)", xf);
6.合并单元格:
//或者
ws.AddMergeArea(new MergeArea(1, 2, 1, 1));
7.MyXls合并单元格有个bug,就是合并后只是第一个单元格有样式,其余的样式丢失。所以写了个函数来合并:
MergeRegion(ref ws, xf, "机构", 1, 1, 2, 1);
public void MergeRegion(ref Worksheet ws, XF xf, string title, int startRow, int startCol, int endRow, int endCol)
{
for (int i = startCol; i <= endCol; i++)
{
for (int j = startRow; j <= endRow; j++)
{
ws.Cells.Add(j, i, title, xf);
}
}
ws.Cells.Merge(startRow, endRow, startCol, endCol);
}
虽然效率不怎么样,但是对于出Excel报表,还OK。
8.指定单元格格式:
cell.Format = StandardFormats.Decimal_1;
具体更多请参考源代码的StandardFormats类。
9.保存或者发送Excel:
xls.Send();
//或者
xls.Save();
MyXls下载地址:http://myxls.in2bits.org/Downloads.ashx
代码片段(1)
[代码] [C#]代码
01 |
//1.创建一个Excel文档: |
02 |
03 |
XlsDocument xls = new XlsDocument(); |
04 |
05 |
//2.创建一个WorkSheet: |
06 |
07 |
Worksheet ws = xls.Workbook.Worksheets.Add( "WorkSheet1" ); |
08 |
09 |
//3.指定列格式: |
10 |
11 |
ColumnInfo colInfo = new ColumnInfo(xls, ws); |
12 |
colInfo.ColumnIndexStart = 0; |
13 |
colInfo.ColumnIndexEnd = 17; |
14 |
colInfo.Width = 15 * 256; |
15 |
ws.AddColumnInfo(colInfo); |
16 |
17 |
//列格式必须每次都要重新定义,一个列格式不能重复使用。 |
18 |
19 |
//4.指定单元格样式: |
20 |
21 |
XF xf = xls.NewXF(); |
22 |
xf.HorizontalAlignment = HorizontalAlignments.Centered; |
23 |
xf.VerticalAlignment = VerticalAlignments.Centered; |
24 |
xf.Pattern = 1; |
25 |
xf.PatternColor = Colors.Default30; |
26 |
xf.UseBorder = true ; |
27 |
xf.TopLineStyle = 1; |
28 |
xf.TopLineColor = Colors.Black; |
29 |
xf.BottomLineStyle = 1; |
30 |
xf.BottomLineColor = Colors.Black; |
31 |
xf.LeftLineStyle = 1; |
32 |
xf.LeftLineColor = Colors.Black; |
33 |
xf.RightLineStyle = 1; |
34 |
xf.RightLineColor = Colors.Black; |
35 |
xf.Font.Bold = true ; |
36 |
xf.Font.Height = 11 * 20; |
37 |
xf.Font.ColorIndex = 1; |
38 |
39 |
//5.给单元格赋值: |
40 |
41 |
ws.Cells.Add(2, 3, "金额(万元)" , xf); |
42 |
43 |
//6.合并单元格: |
44 |
ws.Cells.Merge(1, 2, 2, 2); |
45 |
//或者 |
46 |
ws.AddMergeArea( new MergeArea(1, 2, 1, 1)); |
47 |
48 |
//7.MyXls合并单元格有个bug,就是合并后只是第一个单元格有样式,其余的样式丢失。所以写了个函数来合并: |
49 |
50 |
MergeRegion( ref ws, xf, "机构" , 1, 1, 2, 1); |
51 |
52 |
public void MergeRegion( ref Worksheet ws, XF xf, string title, int startRow, int startCol, int endRow, int endCol) |
53 |
{ |
54 |
for ( int i = startCol; i <= endCol; i++) |
55 |
{ |
56 |
for ( int j = startRow; j <= endRow; j++) |
57 |
{ |
58 |
ws.Cells.Add(j, i, title, xf); |
59 |
} |
60 |
} |
61 |
ws.Cells.Merge(startRow, endRow, startCol, endCol); |
62 |
} |
63 |
64 |
//虽然效率不怎么样,但是对于出Excel报表,还OK。 |
65 |
66 |
//8.指定单元格格式: |
67 |
68 |
cell.Format = StandardFormats.Decimal_1; |
69 |
70 |
//具体更多请参考源代码的StandardFormats类。 |
71 |
72 |
//9.保存或者发送Excel: |
73 |
74 |
xls.Send(); |
75 |
//或者 |
76 |
xls.Save(); |