AX2009使用NPOI导出EXCEL常用格式设置 

 1 static void NPOI_ExportExcel2007(Args _args)
 2 {
 3     System.IO.FileStream                file;
 4     NPOI.XSSF.UserModel.XSSFWorkbook    workBook= new NPOI.XSSF.UserModel.XSSFWorkbook();
 5     NPOI.SS.UserModel.ISheet            sheet=workBook.CreateSheet("NPOISheet");
 6     NPOI.SS.UserModel.IRow              row;
 7     NPOI.SS.UserModel.ICell             cell;
 8     NPOI.SS.UserModel.ICellStyle        cellStyle;
 9     NPOI.SS.UserModel.IFont             cellFont;
10 
11     InventTable                         inventTable;
12     SysDictTable                        dictTable = new SysDictTable(inventTable.TableId);
13     SysDictField                        dictField;
14     FieldId                             fieldId;
15     int                                 i,j,k,t,cor;
16     str                                 fieldValue;
17     Types                               a;
18     ;
19     t=timenow();
20     cellFont=workBook.CreateFont();//字体设置
21     cellFont.set_FontName("Arial");//字体
22     cellFont.set_FontHeightInPoints(System.Convert::ToInt16(10));//字体大小
23     cellFont.set_Color(System.Convert::ToInt16(4));//字体颜色
　　　　cellFont.set_Boldweight(System.Convert::ToInt16(700));//粗体
24     while select inventTable
25     {
26         row=sheet.CreateRow(i);
27         cellStyle=workBook.CreateCellStyle();
28         cellStyle.SetFont(cellFont);
29         cellStyle.set_BorderBottom(NPOI.SS.UserModel.BorderStyle::HAIR);//单元格下边框
30         cellStyle.set_BorderLeft(NPOI.SS.UserModel.BorderStyle::HAIR);//单元格左边框
31         if(i mod 2==0)
32             cellStyle.set_FillForegroundColor(System.Convert::ToInt16(7));//背景色
33         else
34             cellStyle.set_FillForegroundColor(System.Convert::ToInt16(1));
35         cellStyle.set_FillPattern(NPOI.SS.UserModel.FillPatternType::SOLID_FOREGROUND);//背景色显示样式
36         for(j=1;j<dictTable.fieldCnt();j++)
37         {
38             cell=row.CreateCell(j-1);
39             fieldId=dictTable.fieldCnt2Id(j);
40             a=typeof(inventTable.(fieldId));
41             dictField=dictTable.fieldObject(fieldId);
42             if(a!=types::BLOB && a!=types::Class && a!=types::Container && a!=types::void && a!=types::Record)
43             {
44                 for(k=1;k<=dictField.arraySize();k++)
45                     fieldValue=queryvalue(inventTable.(fieldId2Ext(fieldId,k)));
46 
47             }
48             cell.SetCellValue(fieldValue);
49             cell.set_CellStyle(cellStyle);
50 
51         }
52         i++;
53     }
54 
55     file =new System.IO.FileStream("D:\\test.xlsx", System.IO.FileMode::Create,System.IO.FileAccess::ReadWrite);
56     sheet.SetAutoFilter(new NPOI.SS.Util.CellRangeAddress(0,10,0,i));//自动过滤
57     sheet.set_DisplayGridlines(false);//是否显示格子
58     sheet.AddMergedRegion( new NPOI.SS.Util.CellRangeAddress(1,1,1,2));//合并单元格
59 　　 sheet.AutoSizeColumn(2);//自动宽度
60　　  sheet.CreateFreezePane(2,1,5,3);//冻结行列 冻结窗口
61     workBook.Write(file);
62     file.Close();
63     info("export2007 use time:"+int2str(timenow()-t)+"s");
64 }

运行效果

更多设置可参考官方例子，但都是C#的代码，需要自己转换以下

 

posted @ 2013-06-24 17:10  止在入门  阅读(1545)  评论(0)  编辑  收藏  举报