MVC3 使用NPOI导出excel

1 using NPOI.HSSF.UserModel;
2 using NPOI.SS.UserModel;

1 /// <summary>
2 /// //导出excel
3 /// </summary>
4 /// <param name="id">联盟ID</param>
5 /// <param name="param">游玩时间</param>
6 /// <returns></returns>
7 public ActionResult CreateExcel(string id,string param)
8 {
9 try
10 {
11 if (id == "全部")
12 {
13 string[] s = param.Split('-');
14 DateTime dt = new DateTime(int.Parse(s[0]), int.Parse(s[1]), int.Parse(s[2]));
15 List<TSceneryLeagueExportFax> list = tsceneryLeagueBll.getFaxScenery(dt);
16
17 DataSet tempDS = new DataSet();
18 foreach (TSceneryLeagueExportFax tslef in list)
19 {
20 if (tslef.SceneryBookNum == 0)
21 continue;
22 List<TSceneryLeagueExportFax> sceneryleague = tsceneryLeagueBll.getFaxSceneryDetail(tslef.LMID, dt);
23 DataTable dtabel = new DataTable();
24 dtabel.TableName = sceneryleague[0].SceneryName;
25 dtabel.Columns.Add("预订人");
26 //dtabel.Columns.Add("电话");
27 dtabel.Columns.Add("验证码");
28 dtabel.Columns.Add("票种");
29 dtabel.Columns.Add("张数");
30 dtabel.Columns.Add("单价");
31 dtabel.Columns.Add("总价");
32 dtabel.Columns.Add("游玩时间");
33
34 foreach (TSceneryLeagueExportFax tlef in sceneryleague)
35 {
36 if (tlef.TicketsNum == 0)
37 continue;
38 IList TempList = new ArrayList();
39 TempList.Add(tlef.TakeTicketPerson);
40 //TempList.Add(tlef.PhoneCode);
41 TempList.Add(tlef.VerifyCode);
42 TempList.Add(tlef.TicketName);
43 TempList.Add(tlef.TicketsNum);
44 TempList.Add(tlef.DiscountPrice);
45 TempList.Add(tlef.allMonery);
46 TempList.Add(tlef.DateOfVisit.ToString("yyyy-MM-dd"));
47
48 object[] itm = new object[7];
49 //遍历ArrayList向object[]里放数据
50 for (int j = 0; j < TempList.Count; j++)
51 {
52
53 itm.SetValue(TempList[j], j);
54 }
55 //将object[]的内容放入DataTable
56 dtabel.LoadDataRow(itm, true);
57
58 }
59 tempDS.Tables.Add(dtabel);
60 }
61 return File(ExportExecel(tempDS), "application/vnd.ms-excel", "全部景区联盟传真" + param + ".xls");
62 }
63 else
64 {
65 string[] s = param.Split('-');
66 DateTime dt = new DateTime(int.Parse(s[0]), int.Parse(s[1]), int.Parse(s[2]));
67 List<TSceneryLeagueExportFax> list = tsceneryLeagueBll.getFaxSceneryDetail(int.Parse(id), dt);
68 TSceneryLeague tsigle = tsceneryLeagueBll.GetModel(int.Parse(id));//用于获取景区名称，在导出文件时显示
69 DataSet tempDS = new DataSet();
70 if (list.Count > 0)
71 {
72 DataTable dtabel = new DataTable();
73 dtabel.TableName = list[0].SceneryName;
74 dtabel.Columns.Add("预订人");
75 //dtabel.Columns.Add("电话");
76 dtabel.Columns.Add("验证码");
77 dtabel.Columns.Add("票种");
78 dtabel.Columns.Add("张数");
79 dtabel.Columns.Add("单价");
80 dtabel.Columns.Add("总价");
81 dtabel.Columns.Add("游玩时间");
82
83 foreach (TSceneryLeagueExportFax tlef in list)
84 {
85 if (tlef.TicketsNum == 0)
86 continue;
87 IList TempList = new ArrayList();
88 TempList.Add(tlef.TakeTicketPerson);
89 //TempList.Add(tlef.PhoneCode);
90 TempList.Add(tlef.VerifyCode);
91 TempList.Add(tlef.TicketName);
92 TempList.Add(tlef.TicketsNum);
93 TempList.Add(tlef.DiscountPrice);
94 TempList.Add(tlef.allMonery);
95 TempList.Add(tlef.DateOfVisit.ToString("yyyy-MM-dd"));
96
97 object[] itm = new object[7];
98 //遍历ArrayList向object[]里放数据
99 for (int j = 0; j < TempList.Count; j++)
100 {
101
102 itm.SetValue(TempList[j], j);
103 }
104 //将object[]的内容放入DataTable
105 dtabel.LoadDataRow(itm, true);
106
107 }
108 tempDS.Tables.Add(dtabel);
109
110 }
111
112
113
114 return File(ExportExecel(tempDS), "application/vnd.ms-excel", tsigle.SceneryName+param+".xls");
115 }
116 }
117 catch (Exception ex)
118 {
119
120 throw ex;
121 }
122 }

1 public byte[] ExportExecel(DataSet tempDs)
2 {
3
4 HSSFWorkbook book = new HSSFWorkbook();
5
6 //普通单元格样式
7 ICellStyle cellstyle = book.CreateCellStyle();
8 cellstyle.Alignment = HorizontalAlignment.CENTER; //水平居中
9 cellstyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
10 cellstyle.WrapText = true; //自动换行
11 IFont cellfont = book.CreateFont();
12 cellfont.FontHeightInPoints = 11; //11号字体
13 cellstyle.SetFont(cellfont);
14
15 //头部单元格样式
16 ICellStyle cellheadstyle = book.CreateCellStyle();
17 cellheadstyle.Alignment = HorizontalAlignment.CENTER;
18 cellheadstyle.VerticalAlignment = VerticalAlignment.CENTER;
19 IFont cellheadfont = book.CreateFont();
20 cellheadfont.FontHeightInPoints = 11;
21 cellheadfont.Boldweight =(short)FontBoldWeight.BOLD; //字体加粗
22 cellheadstyle.SetFont(cellheadfont);
23
24
25 //标题单元格样式
26 ICellStyle titlestyle = book.CreateCellStyle();
27 titlestyle.Alignment = HorizontalAlignment.CENTER;
28 titlestyle.VerticalAlignment = VerticalAlignment.CENTER;
29 IFont titlefont = book.CreateFont();
30 titlefont.FontHeightInPoints = 14;
31 titlefont.Boldweight = (short)FontBoldWeight.BOLD; //字体加粗
32 titlestyle.SetFont(titlefont);
33
34
35 int count = tempDs.Tables.Count;
36 for (int i = 0; i < count; i++)
37 {
38 DataTable dt = tempDs.Tables[i];
39 ISheet sheet = book.CreateSheet(dt.TableName);
40 sheet.IsPrintGridlines = true; //打印时显示网格线
41 sheet.DisplayGridlines = true;//查看时显示网格线
42 //sheet.DefaultRowHeightInPoints = 25; 这种默认设置不起作用
43 sheet.SetColumnWidth(0, 20 * 256);//预订人宽度
44 sheet.SetColumnWidth(2, 25 * 256);//票种宽度
45 sheet.SetColumnWidth(6, 13 * 256);//游玩时间宽度
46
47 int rowIndex = 3;
48 int colIndex = 0;
49
50
51
52 IRow row = sheet.CreateRow(0);
53 row.HeightInPoints = 30;
54 ICell title = row.CreateCell(0);
55 string titlevalue = dt.TableName + dt.Rows[0]["游玩时间"]+"订票详情";
56 title.SetCellValue(titlevalue);
57 title.CellStyle = titlestyle;
58 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0,0,0,6));
59
60 row = sheet.CreateRow(1);
61 row.HeightInPoints = 25;
62 ICell subtitle = row.CreateCell(0);
63 string subtitlevalue = "来自：南北游旅行网 电话：0311-83052118 传真：0311-83058268 移动客服：18633827863";
64 subtitle.SetCellValue(subtitlevalue);
65 subtitle.CellStyle = cellstyle;
66 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 6));
67
68
69
70 //设置表头
71 row = sheet.CreateRow(2);
72 row.HeightInPoints = 25;
73 foreach (DataColumn col in dt.Columns)
74 {
75
76 ICell cell = row.CreateCell(colIndex);
77 cell.SetCellValue(col.ColumnName);
78 cell.CellStyle = cellheadstyle;
79
80 colIndex++;
81 }
82
83 //导入数据行
84 foreach (DataRow rows in dt.Rows)
85 {
86
87 colIndex = 0;
88 row = sheet.CreateRow(rowIndex);
89 string TicketType = rows["票种"].ToString();
90 if (TicketType.Length <= 12)
91 {
92 row.HeightInPoints = 25;
93 }
94 else
95 {
96 row.HeightInPoints = 50;
97 }
98 foreach (DataColumn col in dt.Columns)
99 {
100 ICell cell = row.CreateCell(colIndex);
101 cell.SetCellValue(rows[col.ColumnName].ToString());
102 cell.CellStyle = cellstyle;
103 colIndex++;
104 }
105 rowIndex++;
106 }
107
108 }
109
110
111 // 写入到客户端
112 System.IO.MemoryStream ms = new System.IO.MemoryStream();
113 book.Write(ms);
114 byte[] b = ms.ToArray();
115 book = null;
116 ms.Close();
117 ms.Dispose();
118 return b;
119 }