C# excel 常用操作

1、excel文件读取

1. 　com组件操作excel 读写

2. 　ado.net方式操作excel 读写

3. 　开源的第三方组件npoi

4.　open xml 方式读写excel

方式一使用OleDbConnection

System.Data.DataTable dt =GetExcelDatatable("C:\Users\Administrator\Desktop\技术协助录入.xlsx", "mapTable");

fileSvr.InsetData(dt);

方法体GetExcelDatatable

/// <summary>
        /// Excel数据导入Datable
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
        {
            //office2007之前 仅支持.xls
            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";

            System.Data.DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }


                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();

                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                dt = ds.Tables[0];

                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }

方式二

namespace _05_FileStream
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnWrite_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "文本文件|*.txt|所有文件|*.*";
            if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtPath.Text = sfd.FileName;

                using (FileStream fs = new FileStream(txtPath.Text, FileMode.Create))
                {
                    using (StreamWriter sw = new StreamWriter(fs, Encoding.UTF8))
                    {
                        sw.Write(txtLog.Text);
                    }

                    //
                    //byte[] buffer = Encoding.UTF8.GetBytes(txtLog.Text);
                    //fs.Write(buffer, 0, buffer.Length);

                    //清空缓冲，并将缓冲中的数据写入文件
                    //fs.Flush();
                    //fs.Close();

                    //fs.Dispose();
                }
            }
        }

        private void btnRead_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "文本文件|*.txt";
            if (ofd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtPath.Text = ofd.FileName;
                using (FileStream fs = new FileStream(txtPath.Text, FileMode.Open))
                {
                    using (StreamReader sr = new StreamReader(fs,Encoding.UTF8))
                    {
                        string msg = sr.ReadToEnd();
                        txtLog.Text = msg;
                    }
                }


                //using (FileStream fs = new FileStream(txtPath.Text,FileMode.Open))
                //{ 
                //    byte[] buffer = new byte[fs.Length];
                //    fs.Read(buffer, 0, buffer.Length);
                //    string msg = Encoding.UTF8.GetString(buffer,0,buffer.Length);
                //    txtLog.Text = msg;
                //}
            }
        }
    }
}

2、excel文件导出

3、文档下载　

/// <summary>
        /// 征信材料
        /// </summary>
        /// <param name="fileName"></param>
        public void DownLoad(string fileName = "金融交易平台-搜房金融管理后台使用说明V1.1.pdf")
        {
            string browser = Request.UserAgent.ToUpper();
            FileStream filestream = new FileStream(Server.MapPath("/Manager/Attach/" + fileName), FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read);
            long filesize = filestream.Length;
            //让客户端浏览器正确识别这个文件的类型和文件大小
            System.IO.Path.GetFileName(Server.MapPath("/Manager/Attach/" + fileName)).ToLower();
            Response.ContentType = "application/octet-stream";
            if (browser.Contains("FIREFOX") == true)
            {
                fileName = fileName;
            }
            else
            {
                fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);

            }
            Response.AddHeader("Content-Disposition", "attachment; filename="" + fileName + "";");
            Response.AddHeader("Content-Length", filesize.ToString());
            //将文件中的数据发送到客户端
            byte[] filebuffer = new byte[filesize];
            filestream.Read(filebuffer, 0, (int)filesize);
            Response.BinaryWrite(filebuffer);

            filestream.Close();
            Response.End();
        }

 MVC

public FileResult SubjectExcel()
{
IEnumerable<SubjectExcel> listExcel = ServiceFactory.GetService<ISubjectService>().GetSubjectExcel((int)(base.ProductEnum));
new NPOIHelper().Export<SubjectExcel>(listExcel, "", Server.MapPath("~/题目导入模板/标准科目参考.xlsx"));
return File(Server.MapPath("~/题目导入模板/标准科目参考.xlsx"), "application/ms-excel", "标准科目参考.xls");
}