导入, 导出 EXCel

最新推荐文章于 2024-06-26 16:10:09 发布

bestreally

最新推荐文章于 2024-06-26 16:10:09 发布

阅读量783

点赞数

文章标签： string excel javascript 脚本 null

本文链接：https://blog.csdn.net/bestreally/article/details/7904064

版权

1.首先在页面PageLoad 的时候加入JS 脚本防止死页

            // js 用于可多次进行 Excel导出
            this.Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "alterFormSubmitEvent", "beforeSubmit();", true);
            this.btnExportOutExcel.Attributes["onclick"] = "javascript:exportRequested=true;";

2.使用方法（添加引用命名空间 using OfficeOpenXml;）

        /// <summary>
        /// 将DATATABLE导入到EXCEL
        /// </summary>
        /// <param name="tbl"></param>
        public void DumpExcel(DataTable tbl, string sheetName)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                if (string.IsNullOrEmpty(sheetName))
                {
                    sheetName = "sheet1";
                }
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);

//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);

                //Write it back to the client
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.Clear();
                Response.AddHeader("content-disposition", "attachment; filename=AssociationSearchExport.xlsx");
                Response.BinaryWrite(pck.GetAsByteArray());
                Response.Flush();
                Response.End();
            }
        }

......................./下面是导入到EXCEL

1.添加引用（using system.IO）

string strFileExtention = Path.GetExtension(FileUpload1.FileName);
            string strFileName = AppDomain.CurrentDomain.BaseDirectory + @"tempFiles\" + Guid.NewGuid() + strFileExtention;
            FileStream fs = new FileStream(strFileName, FileMode.OpenOrCreate);
            fs.Write(FileUpload1.FileBytes, 0, FileUpload1.FileBytes.Length);
            fs.Close();
            FileInfo fi = new FileInfo(strFileName);
            if (fi != null)
            {
                //SqlConnection conn = con();
                ExcelPackage ep = new ExcelPackage(fi);
                ExcelWorksheet ew = ep.Workbook.Worksheets[1];
                for (int i = 2; ; i++)
                {
                    string strqr = "";
                    string strId = Convert.ToString(ew.Cells[i, 1].Value);
                    if (string.IsNullOrEmpty(strId))
                    {
                        break;
                    }
                    int id = Convert.ToInt32(strId);

                    string strPA = ew.Cells[i, 2].Value.ToString();
                    string strPaticipate = ew.Cells[i, 4].Value.ToString();
                    DateTime dtStartTime = Convert.ToDateTime(ew.Cells[i, 5].Value);
                    DateTime dtEndTime = Convert.ToDateTime(ew.Cells[i, 6].Value);
                    string strSummary = ew.Cells[i, 7].Value.ToString();
                    string strResult = ew.Cells[i, 8].Value.ToString();

}

File.Delete(strFileName);
}

就是将 Excel中的数据变成一个Datatable 然后再做相应的处理！