Asp.Net中导入Execl代码

Question

初学Asp.Net，在Asp.Net中导入Execl，实在不怎么明白怎么弄！希望各位能给点说明！

Answer 1

        using Aspose.Cells;        byte[] IExcelUCO.ImportExcel(System.IO.Stream streamExcel)
        {
            //1.读取Excel
            Workbook readExcel = new Workbook();
            try
            {
                readExcel.Open(streamExcel);//将上传的数据用readExcel记录
              }
            catch (ArgumentException ex)
            {
                throw new ArgumentException("File Format Exception");
            }
            catch (Exception ex)
            {
                throw new Exception("File Format Exception");
            }            DataTable dat = new DataTable();
            dat.Columns.Add("代号", typeof(string));
            dat.Columns.Add("名称", typeof(string));
            dat.Columns.Add("规格", typeof(string));            //2.循环新增
              Cells cells = readExcel.Worksheets[0].Cells;
            for (int i = 1; i <= cells.MaxRow; i++)
            {                string strPdcSize = cells[i, j].Value == null ? "" : Convert.ToString(cells[i, j].Value); j++;
                string strPdcSizeName = cells[i, j].Value == null ? "" : cells[i, j].Value.ToString(); j++; 
                  string strMainSize = cells[i, j].Value == null ? "" : Convert.ToString(cells[i, j].Value); j++;
                //新增操作
                ......
            }

Answer 2

如果表有很多字段，那不是要重复 dat.Columns.Add("规格", typeof(string)); 很多吗？

Answer 3

先把数据加载到gridview里面，然后再把gridview里面的数据导出到excel里面,下面是后台代码:public override void VerifyRenderingInServerForm(Control control)
    {
        //base.VerifyRenderingInServerForm(control);
    }
    protected void btnOut_Click(object sender, EventArgs e)//导出按钮事件
    {
        try
        {
            Response.Clear();
            Response.Charset = "";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            Response.AddHeader("content-disposition", "attachment;filename=informtion.xls");
            Response.ContentType = "application/ms-excel";
            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
            this.grvShow.RenderControl(htmlTextWriter);
            Response.Write(stringWriter.ToString());
            Response.End();
        }
        catch (Exception exc)
        {
            Response.Write("<script>alert('导出失败!,错误信息代码:" + exc.Message + "')</script>"); return;
        }
    }

Answer 4

先把数据加载到gridview里面，然后再把gridview里面的数据导出到excel里面,下面是后台代码
楼上的，你可能把我的意思弄反了！我是想把Execl中的数据通过Asp.Net导入到Sql server中

Answer 5

//string fileName = System.IO.Path.GetTempFileName();
                    //this.txtUpFile.PostedFile.SaveAs(fileName);
                    string strExcConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
                    System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strExcConn);
                    string strSQL = "SELECT 工号,码数,数量 FROM [模板$]";
                    System.Data.OleDb.OleDbDataAdapter adrExcel;
                    DataSet dsExcel = new DataSet();
                    adrExcel = new System.Data.OleDb.OleDbDataAdapter(strSQL, conn);
                    adrExcel.Fill(dsExcel);
                    dt = dsExcel.Tables[0];
fileName是你文件地址
模板$ 是你Excel中的Sheet

Answer 6

 protected void btn_xls_Click(object sender, EventArgs e)
    {
        Getsource();
        Response.ClearContent();
        Response.ClearHeaders();
        Response.Clear();
        Response.ContentType = "application/excel";
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Ebook.xls");
        Stream stream = reportDoc.ExportToStream(ExportFormatType.Excel);
        byte[] dataArray = new byte[stream.Length];
        stream.Read(dataArray, 0, Convert.ToInt32(stream.Length));
        Response.BinaryWrite(dataArray);
        Response.Flush();
        Response.Close();
        reportDoc.Close();
        reportDoc.Dispose();
    }

Answer 7

Getsource(); 這個函數得到是一個數據源..

Answer 8

给代码你参考一下,大檓就是这样，记得引用Microsoft.Excel进来
        /// <summary>
        /// 导出Excel后，在外层对Excel资源进行回收
        /// </summary>
        /// <param name="pLines"></param>
        /// <param name="pStartDate"></param>
        /// <param name="pEndDate"></param>
        /// <returns></returns>
        private string GCExport(string pLines, string pStartDate, string pEndDate)
        {
            string strFilePath = "";
            try
            {
                strFilePath=Export(pLines, pStartDate, pEndDate);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                GC.Collect();
            }
            return strFilePath;
        }
        
        /// <summary>
        /// 将MPS导出Excel
        /// </summary>
        /// <param name="pLines"></param>
        /// <param name="pStartDate"></param>
        /// <param name="pEndDate"></param>
        /// <returns></returns>
        private string Export(string pLines, string pStartDate, string pEndDate)
        {
           
            Application appExcel = new Excel.Application();
            Workbook wkSaveFile = null;
            Sheets wsSheets = null;            try
            {
                if (File.Exists(strFilePath))
                { File.Delete(strFilePath); }                appExcel.Visible = false;
                wkSaveFile = appExcel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                wsSheets = wkSaveFile.Worksheets;
                wsSheets.Add(Type.Missing, Type.Missing, arrLines.Length-1, Type.Missing);                    Worksheet worksheet = (Worksheet)wsSheets.get_Item(curSheet);
                    worksheet.Name = ln;
                    worksheet.Cells.Font.Size = "10";..............................                    else
                    {
                        Range infoRange = worksheet.get_Range("A1", "G2");
                        infoRange.Merge(Type.Missing);
                        infoRange.Font.Size = "15";
                        infoRange.Font.Color = "#FF0000";
                        infoRange.Value2="No recored in System!";
                    }
                    worksheet.Cells.EntireColumn.AutoFit();
                }
                
                wkSaveFile.SaveCopyAs(strFilePath);
            }
            catch (Exception ex)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wsSheets);
                wsSheets = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wkSaveFile);
                wkSaveFile = null;
                appExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;                //GC.Collect();
                throw ex;
            }
            finally
            {
                appExcel.ScreenUpdating = true;
                wkSaveFile.Saved = true;
                wkSaveFile.Close(false, Type.Missing, Type.Missing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wsSheets);
                wsSheets = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(wkSaveFile);
                wkSaveFile = null;
                appExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel);
                appExcel = null;               // GC.Collect();
            }
            return clientname;
        }