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 }
