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();
            }

        }

View Code

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; //} } } } }

/// <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(); }

相关推荐