C# Excel导入导出

 

 

 /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据源List<T></param>
        /// <param name="fileName">页面文件 输出名称 *.xls</param>
        public void Export<T>(IList<T> list, string fileName)
        {

            //得到DataTable
            System.Data.DataTable dt = ListToTableHelper.ToDataTable(list);

            //导出Excel的临时文件
            string exportPath = Server.MapPath("~/Excel/") + DateTime.Now.Ticks + fileName;

            CreateExcel(dt, exportPath);
            FileInfo fileInfo = new FileInfo(exportPath);
            if (fileInfo.Exists)
            {
                const long ChunkSize = 102400;//100K 每次读取文件，只读取100K，这样可以缓解服务器的压力
                byte[] buffer = new byte[ChunkSize];
                Response.Clear();
                System.IO.FileStream iStream = System.IO.File.OpenRead(exportPath);
                long dataLengthToRead = iStream.Length;//获取下载的文件总大小
                Response.ContentType = "application/octet-stream";
                Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName));
                while (dataLengthToRead > 0 && Response.IsClientConnected)
                {
                    int lengthRead = iStream.Read(buffer, 0, Convert.ToInt32(ChunkSize));//读取的大小
                    Response.OutputStream.Write(buffer, 0, lengthRead);
                    Response.Flush();
                    dataLengthToRead = dataLengthToRead - lengthRead;
                }
                iStream.Close();
                Response.Close();
            }

            // 删除生成的Excel 临时文件
            File.Delete(exportPath);



        }

 

 

 

 /// <summary>
        /// 打开模板Excel,重新写入 另存Excel 文件格式 xls 97-2003
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="excelPath"></param>
        /// <param name="tempExcelPath"></param>
        public void CreateExcel(System.Data.DataTable dt, string savePath)
        {
            savePath = savePath.Replace("/", "\\");
            Application application = new ApplicationClass();
            application.Visible = false;


            //创建 新的Excel
            Workbook workbook = application.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            //插入 现有的Excel
            //application.Workbooks._Open(tempExcelPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Worksheet worksheet = (Worksheet)workbook.Sheets[1];
            int rowNum = 1;
            int excelColumNum = 0;
            try
            {
                int count = dt.Rows.Count;


                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                    Range range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                    range.Interior.ColorIndex = 15;
                    range.Font.Bold = true;
                }

                foreach (System.Data.DataRow row in dt.Rows)
                {
                    rowNum++;
                    //Console.WriteLine("当前处理记录:{0}/{1}", rowNum, count);
                    for (int i = 1; i <= dt.Columns.Count; i++)
                    {
                        excelColumNum = i;
                        string text = row[i - 1].ToString();
                        Range range = (Range)worksheet.Cells[rowNum, excelColumNum];
                        range.Value2 = text;
                    }
                }
                workbook.Saved = true;
                workbook.SaveAs(savePath, XlFileFormat.xlExcel8, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                workbook.Close(true, Type.Missing, Type.Missing);
                workbook = null;
                application.Quit();
                GC.Collect();
            }
            catch
            {
                workbook.Saved = false;
                workbook.Close(true, Type.Missing, Type.Missing);
                workbook = null;
                application.Quit();
                GC.Collect();
            }

        }

 

调用:

 protected void btn_ExportExcel_Click(object sender, EventArgs e)
        {
            
            IList<SongInfo> list = new List<SongInfo>();// 数据源


            string fileName = "data.xls"; //页面文件 输出名称
            Export(list, fileName);  //调用方法

            ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert('成功!')", true); //完成后显示消息
        }