DataTable数据导出保存到文件、Excel文件导入到DataTable

文件、Excel文件导入到DataTable"/>

DataTable数据导出保存到文件、Excel文件导入到DataTable

 一、DataTable转换成Excel表格，导出保存到文件

        // GET: /Excel/Models.zbwxglEntities myMdl = new Models.zbwxglEntities();/// <summary>/// 第一种方法,利用文件输出流进行读写操作/// </summary>public void outExcel(DataTable table){DataTable dtData = table;string shtnl = "";shtnl = "<table border='1' cellspacing='1' cellpadding='1'>";shtnl = shtnl + "<thead>";for (int j = 0; j < dtData.Columns.Count; j++){shtnl = shtnl + "<th>" + j + "</th>";}shtnl = shtnl + "</thead><tbody>";for (int i = 0; i < dtData.Rows.Count; i++){shtnl = shtnl + "<tr>";for (int j = 0; j < dtData.Columns.Count; j++){shtnl = shtnl + "<td>" + dtData.Rows[i][j] + "</td>";}shtnl = shtnl + "</tr>";}shtnl = shtnl + "</tbody></table>";ExportToExcel("application/x-excel", "1234.xls", shtnl);}public void ExportToExcel(string FieldType, string FileName, string dt){System.Web.HttpContext.Current.Response.Charset = "utf-8";System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");System.Web.HttpContext.Current.Response.ContentType = FieldType;StringWriter tw = new StringWriter();System.Web.HttpContext.Current.Response.Output.Write(dt);System.Web.HttpContext.Current.Response.Flush();System.Web.HttpContext.Current.Response.End();}/// <summary>/// 第二种方法，利用微软自带插件/// </summary>/// <returns></returns>public ActionResult DownloadFile(DataTable table){try{DataTable dt = table;string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");string str = Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "Content\\DownLoadTest\\" + Session["YongHuID"] + strdate + "Excel.xls";if (System.IO.File.Exists(str)){//如果存在则删除System.IO.File.Delete(str);}ConvertHelper myConvertHelper = new ConvertHelper();DataTableToExcel(dt, str);//将DataTable数据转换成Excel数据System.Threading.Thread.Sleep(5000);return File(str, "application/vnd.ms-excel", strdate + "12345.xls");}catch{DataTable dt = new DataTable();List<Dictionary<string, object>> ListReturn = ConvertHelper.DtToList(dt);return Json(ListReturn, JsonRequestBehavior.AllowGet);}}public void DataTableToExcel(DataTable datas, string p){Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();app.SheetsInNewWorkbook = 1;app.Workbooks.Add();Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)app.ActiveWorkbook.Worksheets[1];for (int i = 0; i < datas.Columns.Count; i++){sheet.Cells[1, i + 1] = datas.Columns[i].ColumnName;}for (int i = 0; i < datas.Rows.Count; i++){for (int j = 0; j < datas.Columns.Count; j++){sheet.Cells[2 + i, j + 1] = datas.Rows[i][j].ToString();}}app.Visible = true;System.Threading.Thread.Sleep(500);try{app.ActiveWorkbook.SaveAs(p);}catch { }app.Quit();}/// <summary>/// 第三种方法，利用NPOI插件/// </summary>/// <returns></returns>public FileResult DownLoadExcelJiZuChaXunGenRenXiaoFeiJiLu(DataTable table){DataTable dt = table;//获取需要导出的datatable数据//创建Excel文件的对象NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();//添加一个sheetNPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");//给sheet1添加第一行的头部标题NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);//row1.RowStyle.FillBackgroundColor = "";for (int i = 0; i < dt.Columns.Count; i++){row1.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);}//将数据逐步写入sheet1各个行for (int i = 0; i < dt.Rows.Count; i++){NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);for (int j = 0; j < dt.Columns.Count; j++){rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString().Trim());}}string strdate = DateTime.Now.ToString("yyyyMMddhhmmss");//获取当前时间// 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream();book.Write(ms);ms.Seek(0, SeekOrigin.Begin);return File(ms, "application/vnd.ms-excel", strdate + "Excel.xls");}

二、Excel数据导入DataTable

        /// <summary>/// Excel导入/// </summary>/// <returns></returns>public ActionResult GetTableFromExcel(){//FileStream file = new FileStream(Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "excel\\123.xlsx", FileMode.Open, FileAccess.Read);HttpPostedFileBase fostFile = Request.Files["file1"];Stream streamfile = fostFile.InputStream;//HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);HSSFWorkbook hssfworkbook = new HSSFWorkbook(streamfile);using (NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0)){DataTable table = new DataTable();IRow headerRow = sheet.GetRow(0);//第一行为标题行int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCellsint rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1//handling header.for (int i = headerRow.FirstCellNum; i < cellCount; i++){DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);table.Columns.Add(column);}for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++){IRow row = sheet.GetRow(i);DataRow dataRow = table.NewRow();if (row != null){for (int j = row.FirstCellNum; j < cellCount; j++){if (row.GetCell(j) != null)dataRow[j] = GetCellValue(row.GetCell(j));}}table.Rows.Add(dataRow);}for (int i = 0; i < table.Rows.Count; i++){//myUpLoadBLL.ForDownLoad(table.Rows[i][1].ToString(), table.Rows[i][2].ToString(),Convert.ToBoolean( table.Rows[i][3]));}}return Content("");}/// <summary>/// 根据Excel列类型获取列的值/// </summary>/// <param name="cell">Excel列</param>/// <returns></returns>private static string GetCellValue(ICell cell){if (cell == null)return string.Empty;switch (cell.CellType){case CellType.BLANK:return string.Empty;case CellType.BOOLEAN:return cell.BooleanCellValue.ToString();case CellType.ERROR:return cell.ErrorCellValue.ToString();case CellType.NUMERIC:case CellType.Unknown:default:return cell.ToString();case CellType.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();}}}