前台页面：
1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="b2ccarriersimport.aspx.cs" Inherits="web.financeimport.b2ccarriersimport" %> 2 3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 4 <html xmlns="http://www.w3.org/1999/xhtml"> 5 <head id="Head1" runat="server"> 6 <title></title> 7 <link href="/css/default.css" rel="stylesheet" type="text/css" /> 8 <link href="/css/jquery.ui.all.css" rel="stylesheet" type="text/css" /> 9 <script type="text/javascript" src="/js/jquery-1.9.1.js"></script> 10 <script type="text/javascript" src="/js/jquery.ui.core.js"></script> 11 <script type="text/javascript" src="/js/jquery.ui.datepicker.js"></script> 12 <script src="/js/jquery-ui-timepicker-addon.js" type="text/javascript"></script> 13 <script type="text/javascript" src="/js/common.js?r=20140613"></script> 14 </head> 15 <body> 16 <form id="form1" runat="server"> 17 <div class="container"> 18 <div class="content"> 19 <div class="cPanel pbg"> 20 <div class="detailPanel" style="padding: 0"> 21 <table cellpadding="0" cellspacing="0" class="oderAdmin"> 22 <tr> 23 <td colspan="2"> 24 B2C航司导入 25 </td> 26 </tr> 27 <tr> 28 <th> 29 </th> 30 <td> 31 <asp:FileUpload ID="file" runat="server" /> <asp:Button 32 ID="btnok" CssClass="button button-primary" runat="server" Text="导入航司报表" OnClick="btnok_Click" /> 33 </td> 34 </tr> 35 <tr> 36 <th> 37 </th> 38 <td style="color: Red"> 39 注意：文件名必须是XXX-XXX-X这样的类型组合【不允许使用其它的相隔符】-(目前可导入账单包括【<span style="color: Green"> SZX348-KY;SZX348-KY-R;SZX348-ZH;SZX348-ZH-R</span>】) 40 </td> 41 </tr> 42 <tr><th></th><td><asp:Label style="color:Red;font-size:xx-large" ID="lbmessage" runat="server" Text="请选择您要导入的航司文件"></asp:Label></td></tr> 43 </table> 44 </div><div style="display:none"><asp:Button 45 ID="Button1" CssClass="button button-primary" 46 runat="server" Text="匹配数据" onclick="Button1_Click" /></div> 47 </div> 48 </div> 49 <div class="clr"> 50 </div> 51 </div> 52 </form> 53 </body> 54 </html>
后台页面：
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Web.UI; 6 using System.Web.UI.WebControls; 7 using System.IO; 8 using System.Data; 9 using web.code; 10 using MySql.Data.MySqlClient; 11 using System.Threading; 12 using webframework.model; 13 using webframework.bll; 14 15 namespace web.financeimport 16 { 17 public partial class b2ccarriersimport : System.Web.UI.Page 18 { 19 private bllFI_repsource_excel bll = new bllFI_repsource_excel(); 20 private static DataTable dt = new DataTable(); 21 private static string FI_Aircode = ""; 22 private static string FI_Office = ""; 23 private static string EndFlag = ""; 24 protected void Page_Load(object sender, EventArgs e) 25 { 26 27 } 28 29 protected void btnok_Click(object sender, EventArgs e) 30 { 31 if (file.HasFile) 32 { 33 bool fileOK = false; 34 if (!string.IsNullOrEmpty(FI_Aircode)) FI_Aircode = ""; 35 if (!string.IsNullOrEmpty(FI_Office)) FI_Office = ""; 36 if (!string.IsNullOrEmpty(EndFlag)) EndFlag = ""; 37 if (dt != null || dt.Rows.Count > 0) dt.Clear(); 38 String fileExtension = Path.GetExtension(file.FileName).ToLower(); 39 string qianzhui = Path.GetFileNameWithoutExtension(file.FileName); 40 FI_Aircode = qianzhui.Split('-')[1].ToUpper(); 41 FI_Office = qianzhui.Split('-')[0].ToUpper(); 42 EndFlag = qianzhui.Split('-').Length == 3 ? qianzhui.Split('-')[2].ToUpper() : ""; 43 String[] allowedExtensions = { ".xls", ".xlsx", ".csv", ".CSV" }; 44 for (int i = 0; i < allowedExtensions.Length; i++) 45 { 46 if (fileExtension == allowedExtensions[i]) 47 { 48 fileOK = true; 49 } 50 } 51 if (fileOK) 52 { 53 string path = Server.MapPath("~/Temp/"); 54 if (!System.IO.Directory.Exists(path)) 55 { 56 System.IO.Directory.CreateDirectory(path); 57 } 58 try 59 { 60 path = path + file.FileName; 61 file.SaveAs(path); 62 if (!AnaylerXls(path, fileExtension, FI_Aircode, FI_Office, EndFlag)) 63 { 64 Alert("报表解析失败"); 65 } 66 } 67 catch (Exception ex) 68 { 69 Alert("程序出错：" + ex.TargetSite + "==[" + ex.Message + "]"); 70 } 71 } 72 else 73 { 74 Alert("文件格式错误,只支持xls/xlsx/csv"); 75 } 76 } 77 else 78 { 79 Alert("请选择文件"); 80 } 81 } 82 83 private bool AnaylerXls(string path, String filetype, string FI_Aircode, string FI_Office, string EndFlag) 84 { 85 if (dt == null) dt = new DataTable(); 86 if (filetype.Contains("xlsx")) 87 { 88 #region<<<< 89 #endregion 90 } 91 else if (filetype.Contains("xls")) 92 { 93 #region<<< 94 if (FI_Aircode.Contains("ZH")) 95 { 96 if (string.IsNullOrEmpty(EndFlag)) 97 dt = ExcelHelper.GetExcelDataAsTableNPOI(path, "订单号"); 98 else 99 dt = ExcelHelper.GetExcelDataAsTableNPOI(path, "订单编号"); 100 } 101 if (FI_Aircode.Contains("KY")) 102 { 103 if (string.IsNullOrEmpty(EndFlag)) 104 dt = ExcelHelper.GetExcelDataAsTableNPOI(path, "订单号"); 105 else 106 dt = ExcelHelper.GetExcelDataAsTableNPOI(path, "订单编号"); 107 } 108 else 109 { 110 111 } 112 #endregion 113 } 114 else 115 { 116 #region<<<< .zip svc格式 117 if (FI_Office.Contains("HFDZC")) 118 { 119 //dt = CSVUtil.getCsvDataBy(path, "序号"); 120 } 121 else 122 { 123 //dt = CSVUtil.getCsvDataBy(path, "流水号"); 124 } 125 #endregion 126 } 127 System.IO.File.Delete(path); 128 if (dt.Rows.Count > 0) 129 { 130 lbmessage.Text = "正在解析航司数据。。。,请勿关闭页面！"; 131 this.Page.ClientScript.RegisterStartupScript(this.GetType(), "success", "$(\"#Button1\").click();", true); 132 } 133 else 134 { 135 Alert("您选择的文件报表中没有数据"); 136 } 137 return true; 138 } 139 140 protected void Alert(string str) 141 { 142 lbmessage.Text = str; 143 //this.Page.ClientScript.RegisterStartupScript(this.GetType(), "success", "alert('" + str + "');", true); 144 } 145 private void InsertCarrierR(DataTable dt, string FI_Aircode, string FI_Office, string EndFlag) 146 { 147 DataTable newdt = CreateRepsource(); 148 MySql.Data.MySqlClient.MySqlConnection conn = new MySqlConnection(webframework.common.Config.CONMYSQL_172_16_6_4_READ); 149 try 150 { 151 int connum = 0; 152 DateTime nowtime = DateTime.Now; 153 foreach (DataRow dr in dt.Rows) 154 { 155 #region<<<数据入库 156 DataRow newdr = newdt.NewRow(); 157 string tikeno = dr["票号"].ToString(); 158 if (string.IsNullOrEmpty(tikeno)) 159 continue; 160 newdr["FI_Eticket"] = dr["票号"].ToString().Replace("-", "").Trim(); 161 162 newdr["FI_Aircode"] = FI_Aircode.Trim(); 163 newdr["FI_Price"] = decimal.Parse(dr["原票金额"].ToString()); 164 newdr["FI_Tax"] = decimal.Parse(dr["原票机场税"].ToString()); 165 newdr["FI_Agenfee"] = 0; 166 newdr["FI_Rate"] = 0; 167 newdr["FI_Amount1"] = decimal.Parse(dr["订单支付金额"].ToString()); 168 newdr["FI_Amount2"] = 0M; 169 170 newdr["FI_DateIss"] = DateTime.Parse(dr["订单支付日期"].ToString()); 171 newdr["FI_Pnr"] = dr["PNR编号"].ToString().Trim(); 172 newdr["FI_Vendor"] = dr["退票旅行社编号"].ToString().Trim(); 173 newdr["FI_Vnumber"] = dr["退票旅行社名称"].ToString().Trim(); 174 newdr["FI_Orderno"] = dr["订单编号"].ToString().Trim(); 175 newdr["FI_Paybank"] = dr["订单支付银行"].ToString().Trim(); 176 newdr["FI_Bankno"] = dr["原支付平台流水号"].ToString().Replace("'", "").Trim(); 177 newdr["FI_office"] = FI_Office; 178 newdr["FI_Cpnr"] = ""; 179 newdr["FI_Jobno"] = ""; 180 newdr["FI_Buyno"] = ""; 181 newdr["FI_DataTyp"] = "B2B"; 182 newdr["FI_Flag"] = "22"; 183 newdr["FI_OpMan"] = "F018"; 184 newdr["FI_OpTime"] = DateTime.Now; 185 string sql = "SELECT o.orderState, o.outDate,j.startCity,j.reachCity,j.takeoffDate,j.seat,j.flightNo,jp.pnr,p.name,p.ptype FROM t_order o LEFT JOIN t_journey j ON o.id=j.orderId LEFT JOIN t_journeypassenger jp ON j.id=jp.journeyId LEFT JOIN t_passenger p ON jp.passengerId=p.id WHERE jp.tktNo1='{0}' "; 186 DataTable ordt = webframework.common.MySqlHelper.ExecuteDataTable(conn, CommandType.Text, 187 string.Format(sql, tikeno.Trim()), null); 188 if (ordt.Rows.Count > 0) 189 { 190 191 newdr["FI_toman"] = ordt.Rows[0]["name"].ToString(); 192 newdr["FI_mantype"] = ordt.Rows[0]["ptype"].ToString() == "1" ? "ADT" : "CHD"; 193 194 newdr["FI_State"] = GetOrderStateDesc(ordt.Rows[0]["orderState"].ToString()); 195 newdr["FI_Voyage"] = ordt.Rows[0]["startCity"].ToString().Trim() + "-" + ordt.Rows[0]["reachCity"].ToString().Trim(); 196 newdr["FI_Flight"] = ordt.Rows[0]["flightNo"].ToString().Trim(); 197 newdr["FI_Space"] = ordt.Rows[0]["seat"].ToString().Trim(); 198 newdr["FI_Dateto2"] = DateTime.Parse("1900-01-01"); 199 newdr["FI_Dateto1"] = DateTime.Parse(ordt.Rows[0]["takeoffDate"].ToString()); 200 } 201 newdt.Rows.Add(newdr); 202 203 #endregion 204 if (connum != 0 && connum % 100 == 0) 205 { 206 conn.Close(); 207 } 208 connum++; 209 } 210 SqlHelper.BulkCopy("Initial Catalog=LHETWINDB;Data Source=(local);uid=sa;pwd=123", "FI_repsource", newdt, newdt.Rows.Count); 211 //SqlHelper.BulkCopy(webframework.common.Config.CONSQL_611_LHETWINDB, "FI_repsource", newdt, newdt.Rows.Count); 212 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]导入航司成功[" + newdt.Rows.Count + "],耗时[" + (int)(DateTime.Now - nowtime).TotalSeconds + "]秒"); 213 } 214 catch (Exception ex) 215 { 216 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]报表导入数据报错：" + ex.Message); 217 } 218 finally 219 { 220 if (conn != null) conn.Dispose(); 221 } 222 } 223 private void InsertCarrier(DataTable dt, string FI_Aircode, string FI_Office) 224 { 225 DataTable newdt = CreateRepsource(); 226 MySql.Data.MySqlClient.MySqlConnection conn = new MySqlConnection(webframework.common.Config.CONMYSQL_172_16_6_4_READ); 227 try 228 { 229 int connum = 0; 230 int count = 1; 231 DateTime nowtime = DateTime.Now; 232 foreach (DataRow dr in dt.Rows) 233 { 234 #region<<<数据入库 235 236 string tikeno = dr["起始票号"].ToString(); 237 string tikenoend = dr["终止票号"].ToString(); 238 if (string.IsNullOrEmpty(tikeno)) 239 continue; 240 if (tikeno != tikenoend) 241 { 242 string sql = @"SELECT o.outDate,o.orderNo,o.orderFrom,o.shopName,jp.tktNo1 243 FROM t_order o LEFT JOIN t_journey j 244 ON o.id=j.orderId LEFT JOIN t_journeypassenger jp 245 ON j.id=jp.journeyId WHERE o.orderNo=( 246 SELECT o.orderNo 247 FROM t_order o LEFT JOIN t_journey j 248 ON o.id=j.orderId LEFT JOIN t_journeypassenger jp 249 ON j.id=jp.journeyId WHERE jp.tktNo1='{0}' 250 ) "; 251 DataTable ordt = webframework.common.MySqlHelper.ExecuteDataTable(conn, CommandType.Text, 252 string.Format(sql, tikeno.Trim()), null); 253 count = ordt.Rows.Count; 254 } 255 else 256 count = 1; 257 258 for (int i = 1; i <= count; i++) 259 { 260 DataRow newdr = newdt.NewRow(); 261 newdr["FI_Eticket"] = dr["起始票号"].ToString().Replace("-", "").Trim(); 262 newdr["FI_State"] = dr["订单状态"].ToString().Trim(); 263 newdr["FI_Aircode"] = FI_Aircode.Trim(); 264 newdr["FI_Price"] = decimal.Parse(dr["票价"].ToString()) / count; 265 newdr["FI_Tax"] = decimal.Parse(dr["机建费"].ToString()) / count; 266 newdr["FI_Agenfee"] = 0; 267 newdr["FI_Rate"] = 0; 268 newdr["FI_Amount1"] = decimal.Parse(dr["支付金额"].ToString()) / count; 269 newdr["FI_Amount2"] = 0M; 270 newdr["FI_Voyage"] = dr["起飞城市1"].ToString().Trim() + "-" + dr["目的城市1"].ToString().Trim(); 271 newdr["FI_Flight"] = dr["航班号1"].ToString().Trim(); 272 newdr["FI_Space"] = dr["舱位1"].ToString().Trim(); 273 newdr["FI_Dateto2"] = DateTime.Parse("1900-01-01"); 274 newdr["FI_Dateto1"] = DateTime.Parse(dr["乘机日期1"].ToString()); 275 newdr["FI_DateIss"] = DateTime.Parse(dr["出票日期"].ToString()); 276 newdr["FI_Pnr"] = dr["PNR"].ToString().Trim(); 277 newdr["FI_Vendor"] = dr["代理人号"].ToString().Trim(); 278 newdr["FI_Vnumber"] = dr["操作人"].ToString().Trim(); 279 newdr["FI_Orderno"] = dr["订单号"].ToString().Trim(); 280 newdr["FI_Paybank"] = dr["支付银行"].ToString().Trim(); 281 if (FI_Aircode == "KY") 282 { 283 newdr["FI_Bankno"] = dr["支付记帐号"].ToString().Replace("'", "").Trim(); 284 } 285 else if (FI_Aircode == "ZH") 286 { 287 newdr["FI_Bankno"] = dr["支付平台流水号"].ToString().Replace("'", "").Trim(); 288 } 289 290 newdr["FI_office"] = FI_Office; 291 newdr["FI_Cpnr"] = ""; 292 newdr["FI_Jobno"] = ""; 293 newdr["FI_Buyno"] = ""; 294 newdr["FI_DataTyp"] = "B2B"; 295 newdr["FI_Flag"] = "22"; 296 newdr["FI_OpMan"] = "F013"; 297 newdr["FI_OpTime"] = DateTime.Now; 298 string sql = @"SELECT p.name,p.ptype FROM 299 t_journeypassenger jp LEFT JOIN t_passenger p 300 ON jp.passengerId=p.id WHERE jp.tktNo1='{0}' "; 301 DataTable ordt = webframework.common.MySqlHelper.ExecuteDataTable(conn, CommandType.Text, 302 string.Format(sql, tikeno.Trim()), null); 303 if (ordt.Rows.Count > 0) 304 { 305 newdr["FI_toman"] = ordt.Rows[0]["name"].ToString(); 306 newdr["FI_mantype"] = ordt.Rows[0]["ptype"].ToString() == "1" ? "ADT" : "CHD"; 307 } 308 newdt.Rows.Add(newdr); 309 } 310 #endregion 311 if (connum != 0 && connum % 100 == 0) 312 { 313 conn.Close(); 314 } 315 connum++; 316 } 317 SqlHelper.BulkCopy("Initial Catalog=LHETWINDB;Data Source=(local);uid=sa;pwd=123", "FI_repsource", newdt, newdt.Rows.Count); 318 319 //SqlHelper.BulkCopy(webframework.common.Config.CONSQL_611_LHETWINDB, "FI_repsource", newdt, newdt.Rows.Count); 320 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]导入航司成功[" + newdt.Rows.Count + "],耗时[" + (int)(DateTime.Now - nowtime).TotalSeconds + "]秒"); 321 } 322 catch (Exception ex) 323 { 324 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]报表导入数据报错：" + ex.Message); 325 } 326 finally 327 { 328 if (conn != null) conn.Dispose(); 329 } 330 } 331 332 private void InsertCarrierRTemp(DataTable dt, string FI_Aircode, string FI_Office, string EndFlag) 333 { 334 List<modelFI_repsource_excel> list = new List<modelFI_repsource_excel>(); 335 try 336 { 337 DateTime nowtime = DateTime.Now; 338 foreach (DataRow dr in dt.Rows) 339 { 340 //string tikeno = dr["票号"].ToString(); 341 //if (string.IsNullOrEmpty(tikeno)) 342 // continue; 343 string pnr = dr["PNR编号"].ToString().Trim(); 344 if (string.IsNullOrEmpty(pnr)) 345 continue; 346 modelFI_repsource_excel m = new modelFI_repsource_excel(); 347 #region model赋值 348 m.FI_Eticket = dr["票号"].ToString().Replace("-", "").Trim(); 349 m.FI_Aircode = FI_Aircode.Trim(); 350 m.FI_Price = decimal.Parse(dr["原票金额"].ToString()); 351 m.FI_Tax = decimal.Parse(dr["原票机场税"].ToString()); 352 m.FI_Agenfee = 0; 353 m.FI_Rate = 0; 354 m.FI_Amount1 = decimal.Parse(dr["订单支付金额"].ToString()); 355 m.FI_Amount2 = 0M; 356 m.FI_DateIss = DateTime.Parse(dr["订单支付日期"].ToString()); 357 m.FI_Pnr = dr["PNR编号"].ToString().Trim(); 358 m.FI_Vendor = dr["退票旅行社编号"].ToString().Trim(); 359 m.FI_Vnumber = dr["退票旅行社名称"].ToString().Trim(); 360 m.FI_Orderno = dr["订单编号"].ToString().Trim(); 361 m.FI_Paybank = dr["订单支付银行"].ToString().Trim(); 362 if (FI_Aircode == "KY") 363 { 364 m.FI_Bankno = dr["订单支付记帐号"].ToString().Replace("'", "").Trim(); 365 } 366 else if (FI_Aircode == "ZH") 367 { 368 m.FI_Bankno = dr["原支付平台流水号"].ToString().Replace("'", "").Trim(); 369 } 370 371 m.FI_office = FI_Office; 372 //m.FI_Cpnr = ""; 373 //m.FI_Jobno = ""; 374 //m.FI_Buyno = ""; 375 m.FI_DataTyp = "B2B"; 376 //m.FI_Flag= 22; 377 //m.FI_OpMan= "F018"; 378 m.FI_OpTime = DateTime.Now; 379 //m.FI_toman = ""; 380 //m.FI_mantype = ""; 381 //m.FI_State = ""; 382 //m.FI_Voyage = ""; 383 //m.FI_Flight = ""; 384 //m.FI_Space = ""; 385 m.FI_Dateto2 = DateTime.Parse("1900-01-01"); 386 //m.FI_Dateto1 = null; 387 #endregion 388 list.Add(m); 389 } 390 string msg = ""; 391 var count = bll.InsertList(list, webframework.common.Config.CONSQL_611_LHETWINDB, ref msg); 392 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]导入航司成功[" + msg + "],耗时[" + (int)(DateTime.Now - nowtime).TotalSeconds + "]秒"); 393 } 394 catch (Exception ex) 395 { 396 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]报表导入数据报错：" + ex.Message); 397 } 398 } 399 private void InsertCarrierTemp(DataTable dt, string FI_Aircode, string FI_Office) 400 { 401 List<modelFI_repsource_excel> list = new List<modelFI_repsource_excel>(); 402 try 403 { 404 DateTime nowtime = DateTime.Now; 405 foreach (DataRow dr in dt.Rows) 406 { 407 408 //string tikeno = dr["起始票号"].ToString(); 409 //string tikenoend = dr["终止票号"].ToString(); 410 //if (string.IsNullOrEmpty(tikeno)) 411 // continue; 412 string pnr = dr["PNR"].ToString().Trim(); 413 if (string.IsNullOrEmpty(pnr)) 414 continue; 415 modelFI_repsource_excel m = new modelFI_repsource_excel(); 416 #region model赋值 417 m.FI_Eticket = dr["起始票号"].ToString().Replace("-", "").Trim(); 418 m.FI_State = dr["订单状态"].ToString().Trim(); 419 m.FI_Aircode = FI_Aircode.Trim(); 420 m.FI_Price = decimal.Parse(dr["票价"].ToString()); 421 m.FI_Tax = decimal.Parse(dr["机建费"].ToString()); 422 m.FI_Agenfee = 0; 423 m.FI_Rate = 0; 424 m.FI_Amount1 = decimal.Parse(dr["支付金额"].ToString()); 425 m.FI_Amount2 = 0M; 426 m.FI_Voyage = dr["起飞城市1"].ToString().Trim() + "-" + dr["目的城市1"].ToString().Trim(); 427 m.FI_Flight = dr["航班号1"].ToString().Trim(); 428 m.FI_Space = dr["舱位1"].ToString().Trim(); 429 m.FI_Dateto2 = DateTime.Parse("1900-01-01"); 430 m.FI_Dateto1 = DateTime.Parse(dr["乘机日期1"].ToString()); 431 m.FI_DateIss = DateTime.Parse(dr["出票日期"].ToString()); 432 m.FI_Pnr = dr["PNR"].ToString().Trim(); 433 m.FI_Vendor = dr["代理人号"].ToString().Trim(); 434 m.FI_Vnumber = dr["操作人"].ToString().Trim(); 435 m.FI_Orderno = dr["订单号"].ToString().Trim(); 436 m.FI_Paybank = dr["支付银行"].ToString().Trim(); 437 if (FI_Aircode == "KY") 438 { 439 m.FI_Bankno = dr["支付记帐号"].ToString().Replace("'", "").Trim(); 440 } 441 else if (FI_Aircode == "ZH") 442 { 443 m.FI_Bankno = dr["支付平台流水号"].ToString().Replace("'", "").Trim(); 444 } 445 446 m.FI_office = FI_Office; 447 //m.FI_Cpnr = ""; 448 //m.FI_Jobno = ""; 449 //m.FI_Buyno = ""; 450 m.FI_DataTyp = "B2B"; 451 //m.FI_Flag = 0; 452 //m.FI_OpMan = ""; 453 m.FI_OpTime = DateTime.Now; 454 #endregion 455 list.Add(m); 456 } 457 string msg = ""; 458 var count = bll.InsertList(list, webframework.common.Config.CONSQL_611_LHETWINDB,ref msg); 459 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]导入航司成功[" + msg + "],耗时[" + (int)(DateTime.Now - nowtime).TotalSeconds + "]秒"); 460 } 461 catch (Exception ex) 462 { 463 Alert("[" + FI_Office + "-" + FI_Aircode + (string.IsNullOrEmpty(EndFlag) ? "" : "-" + EndFlag) + "]报表导入数据报错：" + ex.Message); 464 } 465 } 466 protected void Button1_Click(object sender, EventArgs e) 467 { 468 if (dt == null || dt.Rows.Count == 0) return; 469 if (FI_Office.Equals("AAA111") && (FI_Aircode.Equals("KY") || FI_Aircode.Equals("ZH"))) 470 { 471 if (string.IsNullOrEmpty(EndFlag)) 472 InsertCarrierTemp(dt, FI_Aircode, FI_Office); 473 else 474 InsertCarrierRTemp(dt, FI_Aircode, FI_Office, EndFlag); 475 } 476 else 477 { 478 479 } 480 } 481 482 483 484 private DataTable CreateRepsource() 485 { 486 DataTable dt = new DataTable(); 487 dt.Columns.Add("FI_Eticket", typeof(System.String)); 488 dt.Columns.Add("FI_State", typeof(System.String)); 489 dt.Columns.Add("FI_Aircode", typeof(System.String)); 490 dt.Columns.Add("FI_Price", typeof(System.Decimal)); 491 dt.Columns.Add("FI_Tax", typeof(System.Decimal)); 492 dt.Columns.Add("FI_Agenfee", typeof(System.Decimal)); 493 dt.Columns.Add("FI_Rate", typeof(System.Decimal)); 494 dt.Columns.Add("FI_Amount1", typeof(System.Decimal)); 495 dt.Columns.Add("FI_Amount2", typeof(System.Decimal)); 496 dt.Columns.Add("FI_Voyage", typeof(System.String)); 497 dt.Columns.Add("FI_Flight", typeof(System.String)); 498 dt.Columns.Add("FI_Space", typeof(System.String)); 499 dt.Columns.Add("FI_DateIss", typeof(System.DateTime)); 500 dt.Columns.Add("FI_Dateto1", typeof(System.DateTime)); 501 dt.Columns.Add("FI_Pnr", typeof(System.String)); 502 dt.Columns.Add("FI_Vendor", typeof(System.String)); 503 dt.Columns.Add("FI_Vnumber", typeof(System.String)); 504 dt.Columns.Add("FI_Orderno", typeof(System.String)); 505 dt.Columns.Add("FI_Paybank", typeof(System.String)); 506 dt.Columns.Add("FI_Bankno", typeof(System.String)); 507 dt.Columns.Add("FI_office", typeof(System.String)); 508 dt.Columns.Add("FI_Cpnr", typeof(System.String)); 509 dt.Columns.Add("FI_toman", typeof(System.String)); 510 dt.Columns.Add("FI_Jobno", typeof(System.String)); 511 dt.Columns.Add("FI_Buyno", typeof(System.String)); 512 dt.Columns.Add("FI_Dateto2", typeof(System.DateTime)); 513 dt.Columns.Add("FI_mantype", typeof(System.String)); 514 dt.Columns.Add("FI_DataTyp", typeof(System.String)); 515 dt.Columns.Add("FI_Flag", typeof(System.Int32)); 516 dt.Columns.Add("FI_OpMan", typeof(System.String)); 517 dt.Columns.Add("FI_OpTime", typeof(System.DateTime)); 518 return dt; 519 } 520 521 /// <summary> 522 /// 523 /// </summary> 524 /// <param name="orderstate">订单状态（0未出票1已出票2已取消3已改签4已退款5出票失败6出票中9已退票10.二次出票）（dtom）</param> 525 /// <returns></returns> 526 private string GetOrderStateDesc(string orderstate) 527 { 528 string result = ""; 529 switch (orderstate) 530 { 531 case "0": 532 result = "未出票"; 533 break; 534 case "1": 535 result = "已出票"; 536 break; 537 case "2": 538 result = "已取消"; 539 break; 540 case "3": 541 result = "已改签"; 542 break; 543 case "4": 544 result = "已退款"; 545 break; 546 case "5": 547 result = "出票失败"; 548 break; 549 case "6": 550 result = "出票中"; 551 break; 552 case "9": 553 result = "已退票"; 554 break; 555 case "10": 556 result = "二次出票"; 557 break; 558 default: 559 break; 560 } 561 return result; 562 } 563 } 564 }
ExcelHelper:
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using NPOI.SS.Converter; using System.Web; namespace Utility { public class ExcelUtils { public static string ExcelContentType { get { return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; } } ///// <summary> ///// 导出Excel ///// </summary> ///// <typeparam name="T"></typeparam> ///// <param name="data"></param> ///// <param name="headDict"></param> ///// <param name="sheetName"></param> ///// <returns></returns> //public static byte[] ExportExcel<T>(List<T> data, Dictionary<string, string> headDict, string sheetName = "", bool showSrNo = false) //{ // DataTable dt = ListToDataTable<T>(data); // byte[] result = null; // List<string> keyList = new List<string>(); // if (showSrNo) // { // keyList.Add("RowNum"); // dt.Columns.Add("RowNum"); // for (int i = 0; i < dt.Rows.Count; i++) // { // dt.Rows[i]["RowNum"] = i + 1; // } // } // //通过键的集合取 // foreach (string key in headDict.Keys) // { // keyList.Add(key); // } // using (ExcelPackage package = new ExcelPackage()) // { // ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetName.IsNullOrEmpty() ? "Sheet1" : sheetName); // if (showSrNo) // { // headDict.Add("RowNum", "序号"); // } // for (int i = 0; i < keyList.Count; i++) // { // sheet.Cells[1, i + 1].Value = headDict[keyList[i]]; // } // if (dt.Rows.Count > 0) // { // for (int i = 0; i < dt.Rows.Count; i++) // { // for (int j = 0; j < keyList.Count; j++) // { // sheet.Cells[i + 2, j + 1].Value = dt.Rows[i][keyList[j]].ToString(); // } // } // } // ExcelRange cells = sheet.Cells[1, 1, 1 + dt.Rows.Count, keyList.Count]; // cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; // cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; // cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; // cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; // cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中 // cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中 // cells.AutoFitColumns();//自适应列宽 // result = package.GetAsByteArray(); // } // return result; //} public static DataTable ImportExcel(string filePath) { DataTable dt = new DataTable(); using (FileStream fsRead = System.IO.File.OpenRead(filePath)) { IWorkbook wk = null; //获取后缀名 string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower(); //判断是否是excel文件 if (extension == ".xlsx" || extension == ".xls") { //判断excel的版本 if (extension == ".xlsx") { wk = new XSSFWorkbook(fsRead); } else { wk = new HSSFWorkbook(fsRead); } //获取第一个sheet ISheet sheet = wk.GetSheetAt(0); //获取第一行 IRow headrow = sheet.GetRow(0); //创建列 int colCount = headrow.Cells.Count; for (int i = 0; i < headrow.Cells.Count; i++) { DataColumn datacolum = new DataColumn("Col" + (i + 1)); dt.Columns.Add(datacolum); } dt.Columns.Add("OrderCode"); //读取每行,从第二行起 for (int r = 1; r <= sheet.LastRowNum; r++) { DataRow dr = dt.NewRow(); //获取当前行 IRow row = sheet.GetRow(r); //读取每列 for (int j = 0; j < colCount; j++) { ICell cell = row.GetCell(j); //一个单元格 dr[j] = GetCellValue(cell); //获取单元格的值 } dr["OrderCode"] = r; dt.Rows.Add(dr); //把每行追加到DataTable } } } return dt; } public static void ReadExcel(string filePath, Action<IWorkbook> action) { using (FileStream fsRead = System.IO.File.OpenRead(filePath)) { IWorkbook wk = null; //获取后缀名 string extension = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower(); //判断是否是excel文件 if (extension == ".xlsx" || extension == ".xls") { //判断excel的版本 if (extension == ".xlsx") { wk = new XSSFWorkbook(fsRead); } else { wk = new HSSFWorkbook(fsRead); } action(wk); } } } //对单元格进行判断取值 public static string GetCellValue(ICell cell) { if (cell == null) return string.Empty; switch (cell.CellType) { case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写) return string.Empty; case CellType.Boolean: //bool类型 return cell.BooleanCellValue.ToString(); case CellType.Error: return cell.ErrorCellValue.ToString(); case CellType.Numeric: //数字类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { return cell.DateCellValue.ToString(); } else //其它数字 { return cell.NumericCellValue.ToString(); } case CellType.Unknown: //无法识别类型 default: //默认类型 return cell.ToString();// case CellType.String: //string 类型 return cell.StringCellValue; case CellType.Formula: //带公式类型 try { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return cell.ToString(); } catch { return cell.NumericCellValue.ToString(); } } } ///// <summary> ///// 导出Excel ///// </summary> ///// <typeparam name="T"></typeparam> ///// <param name="data">数据</param> ///// <param name="headDict">头部信息</param> ///// <param name="imgColumnList">图片列</param> ///// <param name="sheetName">Sheet名</param> ///// <param name="showSrNo">是否要加上序号</param> ///// <returns></returns> //public static byte[] ExportExcel<T>(List<T> data, Dictionary<string, string> headDict, List<string> imgColumnList, string sheetName = "", bool showSrNo = false) //{ // if (imgColumnList == null) // { // imgColumnList = new List<string>(); // } // DataTable dt = ListToDataTable<T>(data); // byte[] result = null; // List<string> keyList = new List<string>(); // if (showSrNo) // { // keyList.Add("RowNum"); // dt.Columns.Add("RowNum"); // for (int i = 0; i < dt.Rows.Count; i++) // { // dt.Rows[i]["RowNum"] = i + 1; // } // } // //通过键的集合取 // foreach (string key in headDict.Keys) // { // keyList.Add(key); // } // IWorkbook workbook = new XSSFWorkbook(); // //设置宽度 // ICellStyle style = workbook.CreateCellStyle(); // style.BorderBottom = BorderStyle.Thin; // style.BorderLeft = BorderStyle.Thin; // style.BorderRight = BorderStyle.Thin; // style.BorderTop = BorderStyle.Thin; // style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 // style.Alignment = HorizontalAlignment.Center;//水平对齐; // if (showSrNo) // { // headDict.Add("RowNum", "序号"); // } // ISheet sheet = sheetName.IsNullOrEmpty() ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName); // //表头 // IRow row = sheet.CreateRow(0); // for (int i = 0; i < keyList.Count; i++) // { // ICell cell = row.CreateCell(i); // cell.SetCellValue(headDict[keyList[i]]); // cell.CellStyle = style; // } // //数据 // for (int i = 0; i < dt.Rows.Count; i++) // { // IRow row1 = sheet.CreateRow(i + 1); // for (int j = 0; j < keyList.Count; j++) // { // if (imgColumnList.Contains(keyList[j])) // { // //插入图片 // byte[] bytes = HttpMethods.GetImage(dt.Rows[i][keyList[j]].ToString()); // if (bytes != null) // { // ICell cell = row1.CreateCell(j); // cell.CellStyle = style; // try // { // int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); // IDrawing patriarch = sheet.CreateDrawingPatriarch(); // XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 100, j, i + 1, j + 1, i + 2); // //##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50 // XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); // } // catch // { // cell.SetCellValue(dt.Rows[i][keyList[j]].ToString()); // } // } // } // else // { // ICell cell = row1.CreateCell(j); // cell.SetCellValue(dt.Rows[i][keyList[j]].ToString()); // cell.CellStyle = style; // } // } // } // //自适应列宽 // for (int i = 0; i < keyList.Count; i++) // { // sheet.AutoSizeColumn(i, true); // } // using (MemoryStream ms = new MemoryStream()) // { // workbook.Write(ms); // result = ms.GetBuffer(); // ms.Close(); // }; // return result; //} ///// <summary> ///// 导出Excel ///// </summary> ///// <param name="dataTable">数据源</param> ///// <param name="heading">工作簿Worksheet</param> ///// <param name="showSrNo">//是否显示行编号</param> ///// <param name="columnsToTake">要导出的列</param> ///// <returns></returns> //public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake) //{ // byte[] result = null; // using (ExcelPackage package = new ExcelPackage()) // { // ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.Format("{0}Data", heading)); // int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 3; //开始的行 // //是否显示行编号 // if (showSrNo) // { // DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int)); // dataColumn.SetOrdinal(0); // int index = 1; // foreach (DataRow item in dataTable.Rows) // { // item[0] = index; // index++; // } // } // //Add Content Into the Excel File // workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true); // // autofit width of cells with small content // int columnIndex = 1; // foreach (DataColumn item in dataTable.Columns) // { // ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex]; // int maxLength = columnCells.Max(cell => cell.Value.ToString().Count()); // if (maxLength < 150) // { // workSheet.Column(columnIndex).AutoFit(); // } // columnIndex++; // } // // format header - bold, yellow on black // using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count]) // { // r.Style.Font.Color.SetColor(System.Drawing.Color.White); // r.Style.Font.Bold = true; // r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; // r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad")); // } // // format cells - add borders // using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count]) // { // r.Style.Border.Top.Style = ExcelBorderStyle.Thin; // r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; // r.Style.Border.Left.Style = ExcelBorderStyle.Thin; // r.Style.Border.Right.Style = ExcelBorderStyle.Thin; // r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); // r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black); // r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black); // r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black); // } // // removed ignored columns // for (int i = dataTable.Columns.Count - 1; i >= 0; i--) // { // if (i == 0 && showSrNo) // { // continue; // } // if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName)) // { // workSheet.DeleteColumn(i + 1); // } // } // if (!String.IsNullOrEmpty(heading)) // { // workSheet.Cells["A1"].Value = heading; // workSheet.Cells["A1"].Style.Font.Size = 20; // workSheet.InsertColumn(1, 1); // workSheet.InsertRow(1, 1); // workSheet.Column(1).Width = 5; // } // result = package.GetAsByteArray(); // } // return result; //} ///// <summary> ///// 导出Excel ///// </summary> ///// <typeparam name="T"></typeparam> ///// <param name="data"></param> ///// <param name="heading"></param> ///// <param name="isShowSlNo"></param> ///// <param name="ColumnsToTake"></param> ///// <returns></returns> //public static byte[] ExportExcel<T>(List<T> data, string heading = "", bool isShowSlNo = false, params string[] ColumnsToTake) //{ // return ExportExcel(ListToDataTable<T>(data), heading, isShowSlNo, ColumnsToTake); //} /// <summary> /// List转DataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="data"></param> /// <returns></returns> public static DataTable ListToDataTable<T>(List<T> data) { PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T)); System.Data.DataTable dataTable = new DataTable(); for (int i = 0; i < properties.Count; i++) { PropertyDescriptor property = properties[i]; dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType); } object[] values = new object[properties.Count]; foreach (T item in data) { for (int i = 0; i < values.Length; i++) { values[i] = properties[i].GetValue(item); } dataTable.Rows.Add(values); } return dataTable; } public static object lockObj = new object(); //public static string Excel2html(HttpRequestBase request, string strFile) //{ // //资源锁，为了保证该资源只能存在一个 // lock (lockObj) // { // string temp = request.MapPath("/Uploads/Temp/"); // temp = temp.Replace("\\", "/"); // if (!temp.EndsWith("/")) temp += "/"; // temp = temp + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; // temp = temp.Replace("/", "\\"); // //将strFile另存为xls 可能为旧版的xls 也可能是xlsx 统一转成03 xp版的xls文件 // try // { // object oMissing = Type.Missing; // var app = new Microsoft.Office.Interop.Excel.Application(); // var wb = app.Workbooks.Open(strFile, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); // wb.CheckCompatibility = false; // app.DisplayAlerts = false; // wb.DoNotPromptForConvert = true; // wb.SaveAs(temp, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // app.Quit(); // //垃圾回收 // //GC.Collect(); // //System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); // //wb = null; // //GC.Collect(); // //依据时间杀灭进程 // System.Diagnostics.Process[] process = System.Diagnostics.Process.GetProcessesByName("EXCEL"); // foreach (System.Diagnostics.Process p in process) // { // LogHelper.WriteLog("正在关闭Excel"); // p.Kill(); // } // GC.Collect(); // } // //转换失败 // catch (Exception ex) // { // LogHelper.WriteLog("1"); // LogHelper.WriteLog(ex.Message); // try { File.Delete(temp); } catch { } // return ""; // } // //文档转换成功，利用NPOI将Excel转为html // string html = ""; // try // { // HSSFWorkbook workbook = ExcelToHtmlUtils.LoadXls(temp); // ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(); // excelToHtmlConverter.OutputColumnHeaders = false; // excelToHtmlConverter.OutputHiddenColumns = false; // excelToHtmlConverter.OutputHiddenRows = false; // excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = false; // excelToHtmlConverter.OutputRowNumbers = false; // excelToHtmlConverter.UseDivsToSpan = false; // //excelToHtmlConverter.ProcessWorkbook(workbook); // // 处理的Excel文件 // excelToHtmlConverter.ProcessWorkbook(workbook); // //添加表格样式 // excelToHtmlConverter.Document.InnerXml = // excelToHtmlConverter.Document.InnerXml.Insert( // excelToHtmlConverter.Document.InnerXml.IndexOf("<head>", 0) + 6, // @"<style>table, td, th{border:1px solid green;}th{background-color:green;color:white;}</style>" // ); // //方法一 // html = excelToHtmlConverter.Document.InnerXml; // } // catch (Exception exp) // { // LogHelper.WriteLog("2"); // LogHelper.WriteLog(exp.Message); // html = ""; // } // finally // { // //没问题，最后将临时文件删除 // try { File.Delete(temp); } catch { } // } // return html; // } //} public static string Excel2html2(string strFile) { //资源锁，为了保证该资源只能存在一个 lock (lockObj) { //文档转换成功，利用NPOI将Excel转为html string html = ""; try { HSSFWorkbook workbook = ExcelToHtmlUtils.LoadXls(strFile); ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(); excelToHtmlConverter.OutputColumnHeaders = false; excelToHtmlConverter.OutputHiddenColumns = false; excelToHtmlConverter.OutputHiddenRows = false; excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = false; excelToHtmlConverter.OutputRowNumbers = false; excelToHtmlConverter.UseDivsToSpan = false; // 处理的Excel文件 excelToHtmlConverter.ProcessWorkbook(workbook); //添加表格样式 excelToHtmlConverter.Document.InnerXml = excelToHtmlConverter.Document.InnerXml.Insert( excelToHtmlConverter.Document.InnerXml.IndexOf("<head>", 0) + 6, @"<style>table, td, th{border:1px solid green;}th{background-color:green;color:white;}</style>" ); //方法一 html = excelToHtmlConverter.Document.InnerXml; html += @"<style>tr:nth-of-type(1){background: #fcf;white-space: no-wrap;}</style>"; html += @"<script>var col = document.getElementsByTagName('col');for(i=0;i<col.length;i++){col[i].setAttribute('width','70');}</script>"; } catch { html = ""; } return html; } } public static string Excel2html3(string strFile) { //资源锁，为了保证该资源只能存在一个 lock (lockObj) { //文档转换成功，利用NPOI将Excel转为html string html = ""; try { HSSFWorkbook workbook = ExcelToHtmlUtils.LoadXls(strFile); ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(); excelToHtmlConverter.OutputColumnHeaders = false; excelToHtmlConverter.OutputHiddenColumns = false; excelToHtmlConverter.OutputHiddenRows = false; excelToHtmlConverter.OutputLeadingSpacesAsNonBreaking = false; excelToHtmlConverter.OutputRowNumbers = false; excelToHtmlConverter.UseDivsToSpan = false; // 处理的Excel文件 excelToHtmlConverter.ProcessWorkbook(workbook); //添加表格样式 excelToHtmlConverter.Document.InnerXml = excelToHtmlConverter.Document.InnerXml.Insert( excelToHtmlConverter.Document.InnerXml.IndexOf("<head>", 0) + 6, @"<style>table, td, th{border:1px solid green;}th{background-color:green;color:white;}</style>" ); //方法一 html = excelToHtmlConverter.Document.InnerXml; html += @"<style>tr:nth-of-type(1){background: #fcf;white-space: no-wrap;}</style>"; html += @"<script>var col = document.getElementsByTagName('col');for(i=0;i<col.length;i++){col[i].setAttribute('width','220');}</script>"; } catch { html = ""; } return html; } } /// <summary> /// DataTable创建excel 2003 .xls /// </summary> /// <param name="dt"></param> /// <param name="path"></param> /// <param name="name"></param> /// <returns></returns> public static bool CreateExcel(DataTable dt, string path, string name) { List<string> exceltitlelist = new List<string>(); foreach (DataColumn dc in dt.Columns) { exceltitlelist.Add(dc.ColumnName); } try { NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(name); sheet.AutoSizeColumn(0); var cellFont = workbook.CreateFont(); var cellStyle = workbook.CreateCellStyle(); var cellStyle2 = workbook.CreateCellStyle(); ////- 加粗，白色前景色 cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellFont.FontHeightInPoints = 12;//设置字号为12 ////- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND ////cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; ////- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！ cellStyle.SetFont(cellFont); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //CellStyle cellStyleDate = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); string[] titles = exceltitlelist.ToArray(); int rowIndex = 0; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); for (int i = 0; i < titles.Length; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.ICell celltmp = row.CreateCell(i); celltmp.SetCellValue(titles[i]); celltmp.CellStyle = cellStyle; } NPOI.SS.UserModel.ICell cell; rowIndex++; string tmp; DataRow m; for (int i = 0; i < dt.Rows.Count; i++) { try { m = dt.Rows[i]; row = sheet.CreateRow(rowIndex); for (int j = 0; j < titles.Length; j++) { cell = row.CreateCell(j); cell.CellStyle = cellStyle2; cell.SetCellValue(m.ItemArray[j].ToString()); } rowIndex++; } catch (Exception e1) { //logclass.Debug("===== 生成excel报错 =====" + e1.Message); } } sheet.ForceFormulaRecalculation = true; using (FileStream file = new FileStream(path, FileMode.Create)) { workbook.Write(file); //创建xls文件。 file.Close(); } } catch (Exception e) { //policyframework.common.logclass.Debug("=====CreateExcel 生成excel报错 =====" + e.Message); return false; } return true; } /// <summary> /// DataTable创建excel 2007 .xlsx /// </summary> /// <param name="dt"></param> /// <param name="path"></param> /// <param name="name"></param> /// <returns></returns> public static bool CreateExcel2007(DataTable dt, string path, string name) { List<string> exceltitlelist = new List<string>(); foreach (DataColumn dc in dt.Columns) { exceltitlelist.Add(dc.ColumnName); } try { NPOI.XSSF.UserModel.XSSFWorkbook workbook = new XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(name); sheet.AutoSizeColumn(0); var cellFont = workbook.CreateFont(); var cellStyle = workbook.CreateCellStyle(); var cellStyle2 = workbook.CreateCellStyle(); ////- 加粗，白色前景色 cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellFont.FontHeightInPoints = 12;//设置字号为12 ////- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND ////cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; ////- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！ cellStyle.SetFont(cellFont); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //CellStyle cellStyleDate = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); string[] titles = exceltitlelist.ToArray(); int rowIndex = 0; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); for (int i = 0; i < titles.Length; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.ICell celltmp = row.CreateCell(i); celltmp.SetCellValue(titles[i]); celltmp.CellStyle = cellStyle; } NPOI.SS.UserModel.ICell cell; rowIndex++; string tmp; DataRow m; for (int i = 0; i < dt.Rows.Count; i++) { try { m = dt.Rows[i]; row = sheet.CreateRow(rowIndex); for (int j = 0; j < titles.Length; j++) { cell = row.CreateCell(j); cell.CellStyle = cellStyle2; cell.SetCellValue(m.ItemArray[j].ToString()); } rowIndex++; } catch (Exception e1) { //logclass.Debug("===== 生成excel报错 =====" + e1.Message); } } sheet.ForceFormulaRecalculation = true; using (FileStream file = new FileStream(path, FileMode.Create)) { workbook.Write(file); //创建xlsx文件。 file.Close(); } } catch (Exception e) { //policyframework.common.logclass.Debug("=====CreateExcel 生成excel报错 =====" + e.Message); return false; } return true; } public static void ExportDtToExcel(DataTable dt,string name) { try { List<string> exceltitlelist = new List<string>(); foreach (DataColumn dc in dt.Columns) { exceltitlelist.Add(dc.ColumnName); } NPOI.XSSF.UserModel.XSSFWorkbook workbook = new XSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(name); sheet.AutoSizeColumn(0); var cellFont = workbook.CreateFont(); var cellStyle = workbook.CreateCellStyle(); var cellStyle2 = workbook.CreateCellStyle(); ////- 加粗，白色前景色 cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cellFont.FontHeightInPoints = 12;//设置字号为12 ////- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND ////cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; ////- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！ cellStyle.SetFont(cellFont); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //CellStyle cellStyleDate = workbook.CreateCellStyle(); //DataFormat format = workbook.CreateDataFormat(); //cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日"); string[] titles = exceltitlelist.ToArray(); int rowIndex = 0; NPOI.SS.UserModel.IRow row = sheet.CreateRow(rowIndex); for (int i = 0; i < titles.Length; i++) //生成sheet第一行列名 { NPOI.SS.UserModel.ICell celltmp = row.CreateCell(i); celltmp.SetCellValue(titles[i]); celltmp.CellStyle = cellStyle; } NPOI.SS.UserModel.ICell cell; rowIndex++; string tmp; DataRow m; for (int i = 0; i < dt.Rows.Count; i++) { try { m = dt.Rows[i]; row = sheet.CreateRow(rowIndex); for (int j = 0; j < titles.Length; j++) { cell = row.CreateCell(j); cell.CellStyle = cellStyle2; cell.SetCellValue(m.ItemArray[j].ToString()); } rowIndex++; } catch (Exception e1) { //logclass.Debug("===== 生成excel报错 =====" + e1.Message); } } sheet.ForceFormulaRecalculation = true; // 清理脏数据 HttpContext.Current.Response.Clear(); MemoryStream ms = new MemoryStream(); workbook.Write(ms); byte[] bytes = ms.ToArray(); HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //通知浏览器下载文件而不是打开 HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); //HttpContext.Current.Response.End(); } catch (Exception ex) { //policyframework.common.logclass.Debug("=====CreateExcel 生成excel报错 =====" + e.Message); LogUtils.Error("出错：" + ex.Message, ex); } } /// <summary> /// 相对路径 /// </summary> /// <param name="name"></param> /// <returns></returns> public static DataTable NPOILoadExcel(string name) { FileStream fs = null; IWorkbook book; try { fs = File.OpenRead(name); if (name.IndexOf(".xlsx") > -1) { book = new XSSFWorkbook(fs); } else if (name.IndexOf(".xls") > -1) { book = new HSSFWorkbook(fs); } else { book = null; } } catch { throw new Exception("导入文件错误"); } finally { fs.Dispose(); fs.Close(); } if (book == null) { throw new Exception("导入文件格式错误"); } ISheet sheet = book.GetSheetAt(0); if (sheet == null) { throw new Exception("报个数据不能为空"); } IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.Cells.Count; DataTable dt = new DataTable(); ICell cell; IRow row; for (int i = 0; i < cellCount; i++) { cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); dt.Columns.Add(column); } } } for (int i = 1; i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = 0; j < cellCount; j++) { if (row.GetCell(j) == null) { continue; } dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; } } }
