mvc导入导出

 

  1. public class ExcelController : Controller  
  2.     {  
  3.         //  
  4.         // GET: /Excel/  
  5.         Models.zbwxglEntities myMdl = new Models.zbwxglEntities();  
  6.         /// <summary>  
  7.         /// 第一种方法,利用文件输出流进行读写操作  
  8.         /// </summary>  
  9.         public void outExcel()  
  10.         {  
  11.             DataTable dtData = (DataTable)Session["datatable"];  
  12.             string shtnl = "";  
  13.             shtnl = "<table border='1' cellspacing='1' cellpadding='1'>";  
  14.             shtnl = shtnl + "<thead>";  
  15.             for (int j = 0; j < dtData.Columns.Count; j++)  
  16.             {  
  17.                 shtnl = shtnl + "<th>" + j + "</th>";  
  18.             }  
  19.             shtnl = shtnl + "</thead><tbody>";  
  20.             for (int i = 0; i < dtData.Rows.Count; i++)  
  21.             {  
  22.                 shtnl = shtnl + "<tr>";  
  23.                 for (int j = 0; j < dtData.Columns.Count; j++)  
  24.                 {  
  25.                         shtnl = shtnl + "<td>" + dtData.Rows[i][j] + "</td>";  
  26.                 }  
  27.                 shtnl = shtnl + "</tr>";  
  28.             }  
  29.             shtnl = shtnl + "</tbody></table>";  
  30.             ExportToExcel("application/x-excel", "123.xls", shtnl);  
  31.         }  
  32.         public void ExportToExcel(string FieldType, string FileName, string dt)  
  33.         {  
  34.             System.Web.HttpContext.Current.Response.Charset = "utf-8";  
  35.             System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());  
  36.             System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");  
  37.             System.Web.HttpContext.Current.Response.ContentType = FieldType;  
  38.             StringWriter tw = new StringWriter();  
  39.             System.Web.HttpContext.Current.Response.Output.Write(dt);  
  40.             System.Web.HttpContext.Current.Response.Flush();  
  41.             System.Web.HttpContext.Current.Response.End();  
  42.         }  
  43.         /// <summary>  
  44.         /// 第二种方法，利用微软自带插件  
  45.         /// </summary>  
  46.         /// <returns></returns>  
  47.         public ActionResult DownloadFile()  
  48.         {  
  49.             try  
  50.             {  
  51.                 DataTable dt = (DataTable)Session["datatable"];  
  52.                 string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");  
  53.                 string str = Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "Content\\DownLoadTest\\" + Session["YongHuID"] + strdate + "Excel.xls";  
  54.                 if (System.IO.File.Exists(str))  
  55.                 {  
  56.                     //如果存在则删除  
  57.                     System.IO.File.Delete(str);  
  58.                 }  
  59.                 ConvertHelper myConvertHelper = new ConvertHelper();  
  60.                 DataTableToExcel(dt, str);  
  61.                 System.Threading.Thread.Sleep(5000);  
  62.                 return File(str, "application/vnd.ms-excel", strdate + "Excel.xls");  
  63.             }  
  64.             catch  
  65.             {  
  66.                 DataTable dt = new DataTable();  
  67.                 List<Dictionary<string, object>> ListReturn = ConvertHelper.DtToList(dt);  
  68.                 return Json(ListReturn, JsonRequestBehavior.AllowGet);  
  69.             }  
  70.         }  
  71.         public void DataTableToExcel(DataTable datas, string p)  
  72.         {  
  73.   
  74.             Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();  
  75.             app.SheetsInNewWorkbook = 1;  
  76.             app.Workbooks.Add();  
  77.             Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)app.ActiveWorkbook.Worksheets[1];  
  78.   
  79.             for (int i = 0; i < datas.Columns.Count; i++)  
  80.             {  
  81.                 sheet.Cells[1, i + 1] = datas.Columns[i].ColumnName;  
  82.             }  
  83.   
  84.             for (int i = 0; i < datas.Rows.Count; i++)  
  85.             {  
  86.                 for (int j = 0; j < datas.Columns.Count; j++)  
  87.                 {  
  88.                     sheet.Cells[2 + i, j + 1] = datas.Rows[i][j].ToString();  
  89.                 }  
  90.             }  
  91.   
  92.             app.Visible = true;  
  93.             System.Threading.Thread.Sleep(500);  
  94.             try  
  95.             {  
  96.                 app.ActiveWorkbook.SaveAs(p);  
  97.             }  
  98.             catch { }  
  99.             app.Quit();  
  100.         }  
  101.         /// <summary>  
  102.         /// 第三种方法，利用NPOI插件  
  103.         /// </summary>  
  104.         /// <returns></returns>  
  105.         public FileResult DownLoadExcelJiZuChaXunGenRenXiaoFeiJiLu()  
  106.         {  
  107.             DataTable dt = (DataTable)Session["datatable"];//获取需要导出的datatable数据  
  108.             //创建Excel文件的对象  
  109.             NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();  
  110.             //添加一个sheet  
  111.             NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");  
  112.             //给sheet1添加第一行的头部标题  
  113.             NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);  
  114.             //row1.RowStyle.FillBackgroundColor = "";  
  115.             for (int i = 0; i < dt.Columns.Count; i++)  
  116.             {  
  117.                 row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);  
  118.             }  
  119.             //将数据逐步写入sheet1各个行  
  120.             for (int i = 0; i < dt.Rows.Count; i++)  
  121.             {  
  122.                 NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);  
  123.                 for (int j = 0; j < dt.Columns.Count; j++)  
  124.                 {  
  125.                     rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());  
  126.                 }  
  127.             }  
  128.             string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");//获取当前时间  
  129.             // 写入到客户端   
  130.             System.IO.MemoryStream ms = new System.IO.MemoryStream();  
  131.             book.Write(ms);  
  132.             ms.Seek(0, SeekOrigin.Begin);  
  133.             return File(ms, "application/vnd.ms-excel", strdate + "Excel.xls");  
  134.         }  
  135.         /// <summary>  
  136.         /// Excel导入  
  137.         /// </summary>  
  138.         /// <returns></returns>  
  139.         public ActionResult GetTableFromExcel()  
  140.         {  
  141.             //FileStream file = new FileStream(Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "excel\\123.xlsx", FileMode.Open, FileAccess.Read);  
  142.   
  143.             HttpPostedFileBase fostFile = Request.Files["file1"];  
  144.             Stream streamfile = fostFile.InputStream;  
  145.             //HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);  
  146.             HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);  
  147.             using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0))  
  148.             {  
  149.                 DataTable table = new DataTable();  
  150.                 IRow headerRow = sheet.GetRow(0);//第一行为标题行  
  151.                 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells  
  152.                 int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1  
  153.                 //handling header.  
  154.                 for (int i = headerRow.FirstCellNum; i < cellCount; i++)  
  155.                 {  
  156.                     DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);  
  157.                     table.Columns.Add(column);  
  158.                 }  
  159.                 for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)  
  160.                 {  
  161.                     IRow row = sheet.GetRow(i);  
  162.                     DataRow dataRow = table.NewRow();  
  163.                     if (row != null)  
  164.                     {  
  165.                         for (int j = row.FirstCellNum; j < cellCount; j++)  
  166.                         {  
  167.                             if (row.GetCell(j) != null)  
  168.                                 dataRow[j] = GetCellValue(row.GetCell(j));  
  169.                         }  
  170.                     }  
  171.                     table.Rows.Add(dataRow);  
  172.                 }  
  173.                 for (int i = 0; i < table.Rows.Count; i++)  
  174.                 {  
  175.                     //myUpLoadBLL.ForDownLoad(table.Rows[i][1].ToString(), table.Rows[i][2].ToString(),Convert.ToBoolean( table.Rows[i][3]));  
  176.                 }  
  177.             }  
  178.             return Content("");  
  179.         }  
  180.         /// <summary>  
  181.         /// 根据Excel列类型获取列的值  
  182.         /// </summary>  
  183.         /// <param name="cell">Excel列</param>  
  184.         /// <returns></returns>  
  185.         private static string GetCellValue(ICell cell)  
  186.         {  
  187.             if (cell == null)  
  188.                 return string.Empty;  
  189.             switch (cell.CellType)  
  190.             {  
  191.                 case CellType.BLANK:  
  192.                     return string.Empty;  
  193.                 case CellType.BOOLEAN:  
  194.                     return cell.BooleanCellValue.ToString();  
  195.                 case CellType.ERROR:  
  196.                     return cell.ErrorCellValue.ToString();  
  197.                 case CellType.NUMERIC:  
  198.                 case CellType.Unknown:  
  199.                 default:  
  200.                     return cell.ToString();  
  201.                 case CellType.STRING:  
  202.                     return cell.StringCellValue;  
  203.                 case CellType.FORMULA:  
  204.                     try  
  205.                     {  
  206.                         HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);  
  207.                         e.EvaluateInCell(cell);  
  208.                         return cell.ToString();  
  209.                     }  
  210.                     catch  
  211.                     {  
  212.                         return cell.NumericCellValue.ToString();  
  213.                     }  
  214.             }  
  215.         }  
  216.     } 

 

 

posted on 2017-03-11 09:32  夏昭  阅读(302)  评论(0)    收藏  举报

博客园  ©  2004-2025
浙公网安备 33010602011771号 浙ICP备2021040463号-3