WEB使用NPOI导出Excle

1、到官网下载NPOI组件

http://npoi.codeplex.com/releases

2、在使用中的实例

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Diagnostics;
  5 using System.Drawing;
  6 using System.IO;
  7 using System.Linq;
  8 using System.Web;
  9 using System.Web.UI;
 10 using System.Web.UI.WebControls;
 11 using Microsoft.Win32;
 12 using NPOI.HSSF.UserModel;
 13 using System.IO;
 14 using NPOI.HPSF;
 15 using NPOI.SS.UserModel;
 16 using NPOI.HSSF.Util;
 17 using NPOI;
 18 using NPOI.HPSF;
 19 using NPOI.HSSF;
 20 using NPOI.HSSF.UserModel;
 21 using NPOI.HSSF.Util;
 22 using NPOI.POIFS;
 23 using NPOI.Util;
 24 using System.Text; 
 25 
 26 namespace CloudCustoms.jiekou
 27 {
 28     public partial class IKEAList : System.Web.UI.Page
 29     {
 30         static HSSFWorkbook hssworkbook;
 31 
 32         
 33         /// <summary>
 34         /// 下载生成的RAR文件
 35         /// </summary>
 36         private void DownloadRAR(string file)
 37         {
 38             FileInfo fileInfo = new FileInfo(file);
 39             Response.Clear();
 40             Response.ClearContent();
 41             Response.ClearHeaders();
 42             Response.AddHeader("Content-Disposition", "attachment;filename=" + fileInfo.Name);
 43             Response.AddHeader("Content-Length", fileInfo.Length.ToString());
 44             Response.AddHeader("Content-Transfer-Encoding", "binary");
 45             Response.ContentType = "application/octet-stream";
 46             Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
 47             Response.WriteFile(fileInfo.FullName);
 48             Response.Flush();
 49             string tempPath = file.Substring(0, file.LastIndexOf(".rar"));
 50             //删除临时目录下的所有文件
 51             DeleteFiles(tempPath);
 52             //删除空目录
 53             Directory.Delete(tempPath);
 54             //删除压缩文件
 55             File.Delete(file);
 56 
 57             Response.End();
 58         }
 59 
 60 
 61 
 62 
 63         //导出Excle
 64         protected void btnExportExcle_Click(object sender, EventArgs e)
 65         {
 66             #region 声明新的表格和SHEET
 67             hssworkbook = new HSSFWorkbook();
 68 
 69             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
 70             dsi.Company = "KeYue Team";
 71             hssworkbook.DocumentSummaryInformation = dsi;
 72 
 73             SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
 74             si.Subject = "IKEA Infomation";
 75             hssworkbook.SummaryInformation = si;
 76 
 77             HSSFSheet sheet1 = (HSSFSheet)hssworkbook.CreateSheet("IKEA Information");
 78             HSSFRow row;
 79             HSSFCell cell; 
 80             #endregion
 81             string TempID = string.Empty;
 82 
 83             
 84 
 85             #region 循环获取选中的ID
 86             for (int i = 0; i < gv_ikealist.Rows.Count; i++)
 87             {
 88                 CheckBox cb = gv_ikealist.Rows[i].Cells[0].FindControl("ckbSelect") as CheckBox;
 89                 if (cb.Checked)
 90                 {
 91                     HiddenField hidfield = gv_ikealist.Rows[i].Cells[0].FindControl("hidvalue") as HiddenField;
 92                     if (hidfield != null)
 93                         TempID += "'" + hidfield.Value + "',";
 94                 }
 95             } 
 96             #endregion
 97 
 98 
 99             //找出dt数据  
100             if (!string.IsNullOrEmpty(TempID))
101             {
102                 DataTable dt = new DataTable();
103                 dt = HsListBind(" and id in(" + TempID.Substring(0, TempID.Length - 1) + ")", 1, 10);
104 
105                 #region 创建表头
106                 row = (HSSFRow)sheet1.CreateRow(0);
107                 row.HeightInPoints = 60;   //行高
108                 //1、集装箱号
109                 cell = (HSSFCell)row.CreateCell(0);
110                 cell.SetCellValue(String.Format("集装箱号\nContainer"));
111                 cell.CellStyle = SetHeadStyle();
112                 //2、运输号
113                 cell = (HSSFCell)row.CreateCell(1);
114                 cell.SetCellValue(String.Format("运输号\nShip ID"));
115                 cell.CellStyle = SetHeadStyle();
116                 //3、装箱日期
117                 cell = (HSSFCell)row.CreateCell(2);
118                 cell.SetCellValue(String.Format("装箱日期\nLoading Date"));
119                 cell.CellStyle = SetHeadStyle();
120                 //4、货物批号
121                 cell = (HSSFCell)row.CreateCell(3);
122                 cell.SetCellValue(String.Format("货物批号\nCSM ID"));
123                 cell.CellStyle = SetHeadStyle();
124                 //5、运单
125                 cell = (HSSFCell)row.CreateCell(4);
126                 cell.SetCellValue(String.Format("运单\nSWB"));
127                 cell.CellStyle = SetHeadStyle();
128                 //6、船名
129                 cell = (HSSFCell)row.CreateCell(5);
130                 cell.SetCellValue(String.Format("船名\nVessel"));
131                 cell.CellStyle = SetHeadStyle();
132                 //7、航次
133                 cell = (HSSFCell)row.CreateCell(6);
134                 cell.SetCellValue(String.Format("航次\nVoyage"));
135                 cell.CellStyle = SetHeadStyle();
136                 //8、起运码头
137                 cell = (HSSFCell)row.CreateCell(7);
138                 cell.SetCellValue(String.Format("起运码头\nPOL"));
139                 cell.CellStyle = SetHeadStyle();
140                 //9、发货人
141                 cell = (HSSFCell)row.CreateCell(8);
142                 cell.SetCellValue(String.Format("发货人\nSender"));
143                 cell.CellStyle = SetHeadStyle();
144                 //10、收货人
145                 cell = (HSSFCell)row.CreateCell(9);
146                 cell.SetCellValue(String.Format("收货人\nReceiver"));
147                 cell.CellStyle = SetHeadStyle();
148                 //11、船公司
149                 cell = (HSSFCell)row.CreateCell(10);
150                 cell.SetCellValue(String.Format("船公司\nCarrier"));
151                 cell.CellStyle = SetHeadStyle();
152                 //12、开船日期
153                 cell = (HSSFCell)row.CreateCell(11);
154                 cell.SetCellValue(String.Format("开船日期\nOn Board Date"));
155                 cell.CellStyle = SetHeadStyle();
156                 //13、产地证书上传日期
157                 cell = (HSSFCell)row.CreateCell(12);
158                 cell.SetCellValue(String.Format("产地证书扫描上传系统日期\nscanned COO upload date"));
159                 cell.CellStyle = SetHeadStyle();
160                 //14、产地证寄出日期
161                 cell = (HSSFCell)row.CreateCell(13);
162                 cell.SetCellValue(String.Format("产地证寄出日期\nCOO send out date"));
163                 cell.CellStyle = SetHeadStyle();
164                 //15、产地证收到日期
165                 cell = (HSSFCell)row.CreateCell(14);
166                 cell.SetCellValue(String.Format("产地证收到日期\nCOO received date"));
167                 cell.CellStyle = SetHeadStyle();
168                 //16、快递单号
169                 cell = (HSSFCell)row.CreateCell(15);
170                 cell.SetCellValue(String.Format("快递单号\nCourier tracking number"));
171                 cell.CellStyle = SetHeadStyle();
172                 //17、证书数量
173                 cell = (HSSFCell)row.CreateCell(16);
174                 cell.SetCellValue(String.Format("证书数量\nCertificate quantity"));
175                 cell.CellStyle = SetHeadStyle();
176                 //18、证书号码
177                 cell = (HSSFCell)row.CreateCell(17);
178                 cell.SetCellValue(String.Format("证书号码\nCertificate number"));
179                 cell.CellStyle = SetHeadStyle();
180 
181                 #endregion
182 
183                 #region 循环写数据
184                 for (int i = 0; i < dt.Rows.Count; i++)
185                 {
186                     row = (HSSFRow)sheet1.CreateRow(i + 1);    //循环得到一个新的行
187                     row.HeightInPoints = 15;        //行高
188 
189                     for (int j = 0; j < 18; j++)    //创建列，并且插入数据
190                     {
191                         cell = (HSSFCell)row.CreateCell(j);
192                         cell.CellStyle = SetRowStyle();
193 
194                         try
195                         {
196                             DateTime dttime = Convert.ToDateTime(dt.Rows[i][j + 2].ToString());
197                             cell.SetCellValue(String.Format(dttime.ToString("yyyy年MM月dd日")));
198                         }
199                         catch (Exception)
200                         {
201                             cell.SetCellValue(String.Format(dt.Rows[i][j + 2].ToString().Trim()));
202                         }
203                     }
204                 }
205                 #endregion
206 
207                 #region 设置列宽度
208 
209                 #region 自适应宽度
210                 //for (int columnNum = 0; columnNum < 18; columnNum++)
211                 //{
212                 //    int ColumnWidth = sheet1.GetColumnWidth(columnNum) / 256;
213                 //    for (int rowNum = 1; rowNum < sheet1.LastRowNum; rowNum++)
214                 //    {
215                 //        IRow currentrow = sheet1.GetRow(rowNum);
216                 //        ICell currentcell = currentrow.GetCell(columnNum);
217                 //        int length = Encoding.UTF8.GetBytes(currentcell.ToString()).Length;
218 
219                 //        if (ColumnWidth < length + 1)
220                 //            ColumnWidth = length + 1;
221                 //    }
222                 //    sheet1.SetColumnWidth(columnNum, ColumnWidth * 256);
223                 //} 
224                 #endregion
225                 sheet1.SetColumnWidth(0, 15 * 256);   //集装箱号
226                 sheet1.SetColumnWidth(1, 17 * 256);   //运输号
227                 sheet1.SetColumnWidth(2, 16 * 256);   //装箱日期
228                 sheet1.SetColumnWidth(3, 18 * 256);   //货物批号
229                 sheet1.SetColumnWidth(4, 16 * 256);   //运单
230                 sheet1.SetColumnWidth(5, 19 * 256);   //船名
231                 sheet1.SetColumnWidth(6, 13 * 256);   //航次
232                 sheet1.SetColumnWidth(7, 14 * 256);   //起运码头
233                 sheet1.SetColumnWidth(8, 14 * 256);   //发货人
234                 sheet1.SetColumnWidth(9, 13 * 256);   //收货人
235                 sheet1.SetColumnWidth(10, 14 * 256);  //船公司
236                 sheet1.SetColumnWidth(11, 16 * 256);  //开船日期
237                 sheet1.SetColumnWidth(12, 16 * 256);  //产地证书上传日期
238                 sheet1.SetColumnWidth(13, 16 * 256);  //产地证书寄出日期
239                 sheet1.SetColumnWidth(14, 16 * 256);  //产地证书收到日期
240                 sheet1.SetColumnWidth(15, 20 * 256);  //快递单号
241                 sheet1.SetColumnWidth(16, 14 * 256);  //证书数量
242                 sheet1.SetColumnWidth(17, 20 * 256);  //证书号码 
243                 #endregion
244 
245                 string filePath = Server.MapPath(@"../Files/IKEA_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
246                 WriteToFile(filePath);
247             }
248             else
249             {
250                 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('没有相关数据，无法导出表格！');</script>");
251             }
252         }
253 
254 
255 
256         private HSSFCellStyle SetHeadStyle()
257         {
258             HSSFCellStyle headStyle = (HSSFCellStyle)hssworkbook.CreateCellStyle();
259             headStyle.Alignment = HorizontalAlignment.Center;    //左右居中
260             headStyle.VerticalAlignment = VerticalAlignment.Center;    //上下居中
261             headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
262             headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
263             headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
264             headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
265 
266             HSSFFont font = (HSSFFont)hssworkbook.CreateFont();
267             font.FontHeightInPoints = 11;
268             font.Boldweight = 700;
269             headStyle.SetFont(font);
270             headStyle.WrapText = true;    //加了之后，单元格内换行会起作用
271 
272             return headStyle;
273         }
274 
275 
276         private HSSFCellStyle SetRowStyle()
277         {
278             HSSFCellStyle rowStyle = (HSSFCellStyle)hssworkbook.CreateCellStyle();
279             rowStyle.Alignment = HorizontalAlignment.Center;    //左右居中
280             rowStyle.VerticalAlignment = VerticalAlignment.Center;    //上下居中
281             rowStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
282             rowStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
283             rowStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
284             rowStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
285 
286             HSSFFont font = (HSSFFont)hssworkbook.CreateFont();
287             font.FontHeightInPoints = 10;
288             rowStyle.SetFont(font);
289 
290             return rowStyle;
291         }
292 
293 
294 
295         private void WriteToFile(string filePath)
296         {
297             //写文件
298             FileStream file = new FileStream(filePath, FileMode.Create);
299             hssworkbook.Write(file);
300             file.Close();
301 
302 
303             //下载
304             FileInfo fileInfo = new FileInfo(filePath);
305             Response.Clear();
306             Response.ClearContent();
307             Response.ClearHeaders();
308             Response.AddHeader("Content-Disposition", "attachment;filename=" + fileInfo.Name);
309             Response.AddHeader("Content-Length", fileInfo.Length.ToString());
310             Response.AddHeader("Content-Transfer-Encoding", "binary");
311             Response.ContentType = "application/octet-stream";
312             Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
313             Response.WriteFile(fileInfo.FullName);
314             Response.Flush();
315             string tempPath = filePath.Substring(0, filePath.LastIndexOf(".xls"));
316 
317             //删除文件
318             File.Delete(filePath);
319 
320             Response.End();
321         }
322 
323     }
324 }

 

posted @ 2016-08-19 09:37  殇琉璃  阅读(1226)  评论(0)  编辑  收藏  举报