public Liststring> GetExcel(string year, string month, string type, out string statusCode, out string errMsg)
2 {
3 statusCode = "0000";
4 errMsg = "";
5 Liststring> response = new Liststring>();
6 string strFilePath = "";
7 string strGuid = Guid.NewGuid().ToString();
8 string FilePath = "\\BufFile\\OutFiles\\DownLoadFiles\\ExportExcel\\" + strGuid;
//构建文件缓存目录
9 strFilePath = System.IO.Directory.GetParent(System.IO.Directory.GetParent(System.AppDomain.CurrentDomain.BaseDirectory).FullName).FullName + FilePath;
10 if (!System.IO.Directory.Exists(strFilePath))
11 {
12 System.IO.Directory.CreateDirectory(strFilePath);
13 }
14 //文件命名
15 string strFileName = strFilePath + "\\" + "XXXXXX.xls";
16 string ret = FilePath + "\\" + "XXXXXX.xls";
17 string uploadPath = System.IO.Directory.GetParent(System.IO.Directory.GetParent(System.AppDomain.CurrentDomain.BaseDirectory).FullName).FullName + "\\BufFile\\OutFiles\\UploadFiles\\";
18 List data = GetCompletionRate(year, month, out statusCode, out errMsg);
19 try
20 { //创建工作薄
21 HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
//编辑文件信息，如文件所属公司、作者、创建日期等
22 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
23 dsi.Company = "ZondyCyber";
24 SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
25 si.Author = "ZondyCyber";
26 si.LastAuthor = "ZondyCyber";
27 si.CreateDateTime = DateTime.Now;
28 hssfWorkBook.DocumentSummaryInformation = dsi;
29 hssfWorkBook.SummaryInformation = si;
//创建名为YYYYYY的表sheet
30 HSSFSheet hssfSheet = hssfWorkBook.CreateSheet("YYYYYY") as HSSFSheet;
31
32 //设置列宽
33 for (int c = 0; c 7; c++)
34 {
35 hssfSheet.SetColumnWidth(c, 12 * 266);
36 }
37
38 //设置列头的单元格样式
39 HSSFCellStyle cellStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle;
40 HSSFFont cellFont = hssfWorkBook.CreateFont() as HSSFFont;
41 cellFont.Boldweight = Convert.ToInt16(FontBoldWeight.Bold);
42 cellFont.FontName = "宋体";
43 cellFont.FontHeightInPoints = 12;
44 cellStyle.Alignment = HorizontalAlignment.Center;
45 cellStyle.VerticalAlignment = VerticalAlignment.Center;
46 cellStyle.WrapText = true;
47 cellStyle.SetFont(cellFont);
48 cellStyle.BorderTop = cellStyle.BorderRight = cellStyle.BorderBottom = cellStyle.BorderLeft = BorderStyle.Thin;//BorderStyle.None
49 //左对齐样式
50 HSSFCellStyle leftCellStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle;
51 leftCellStyle.CloneStyleFrom(cellStyle);
52 leftCellStyle.Alignment = HorizontalAlignment.Left;
53 //居中填充样式
54 HSSFCellStyle fillCellStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle;
55 fillCellStyle.CloneStyleFrom(cellStyle);
56 fillCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
57 //fillCellStyle.FillPattern = FillPattern.Diamonds;
58 //fillCellStyle.FillPattern = FillPattern.FineDots;
59 fillCellStyle.FillPattern = FillPattern.LeastDots;
60 fillCellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
61
62 //居中填充样式(无边框)
63 HSSFCellStyle fillCellStyle2 = hssfWorkBook.CreateCellStyle() as HSSFCellStyle;
64 fillCellStyle2.CloneStyleFrom(fillCellStyle);
65 //fillCellStyle2.FillPattern = FillPattern.NoFill;
66 fillCellStyle2.BorderTop = fillCellStyle2.BorderRight = fillCellStyle2.BorderBottom = fillCellStyle2.BorderLeft = BorderStyle.None;//BorderStyle.None
67 //值的样式
68 HSSFCellStyle valueStyle = hssfWorkBook.CreateCellStyle() as HSSFCellStyle;
69 HSSFFont valueFont = hssfWorkBook.CreateFont() as HSSFFont;
70 valueFont.FontName = "宋体";
71 valueFont.FontHeightInPoints = 10;
72 valueStyle.BorderTop = valueStyle.BorderRight = valueStyle.BorderBottom = valueStyle.BorderLeft = BorderStyle.Thin;//BorderStyle.None
73 valueStyle.WrapText = true;
74 valueStyle.Alignment = HorizontalAlignment.Center;
75 valueStyle.VerticalAlignment = VerticalAlignment.Center;
76 valueStyle.SetFont(valueFont);
77 //值的样式（无边框）
78 HSSFCellStyle valueStyle2 = hssfWorkBook.CreateCellStyle() as HSSFCellStyle;
79 valueStyle2.CloneStyleFrom(valueStyle);
80 valueStyle2.BorderTop = valueStyle2.BorderRight = valueStyle2.BorderBottom = valueStyle2.BorderLeft = BorderStyle.None;//BorderStyle.None
81
82 //开始构建表格
83 int rowIndex = 0;//记录用到第几行
84 //构建表题
85 String Title = "XXXXXX(" + type + ")统计 " + year + "年" + month + "月";
86 //创建表格的第一行的第一个单元格
hssfSheet.CreateRow(0).CreateCell(0).CellStyle = fillCellStyle;
//获取表格的第一行的第一个单元格，并为其赋值
87 hssfSheet.GetRow(0).GetCell(0).SetCellValue(Title);
88 //合并单元格
/*
* cellRangeAddress可以合并行或列，第一个参数是起始行号，第二个参数是终止行号，第三个参数是起始列号，第三个参数是终止列号
*/
89 CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex + 2, 0, 6);
90 hssfSheet.AddMergedRegion(region);
91 hssfSheet.SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
92 rowIndex = rowIndex + 3; //3
93 String Type = "部门";
94 //构建表头
95 hssfSheet.CreateRow(rowIndex).CreateCell(0).CellStyle = valueStyle;
96 hssfSheet.GetRow(rowIndex).GetCell(0).SetCellValue("序号");
97 /**
** 构建表格的具体细节省略，无非是合并单元格，填充数据
**/
323 //表格构建完毕
324 FileStream file = new FileStream(strFileName, FileMode.Create);
325 hssfWorkBook.Write(file);
326 file.Close();
327 response.Add(ret);
328 }
