C#Excel数据导入导出方法总结

一.OLEDB

1.导入：

 public DataSet ExcelToDS(string Path, string name)        {            FileInfo file = new FileInfo(Path);            string extension = file.Extension;            string strConn = "";            switch (extension)            {                case ".xls":                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                    break;                case ".xlsx":                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";                    break;                default:                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                    break;            }            //ModifyRegeditTypeGuessRow(0);            OleDbConnection conn = new OleDbConnection(strConn);            conn.Open();            System.Data.DataTable sTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);            //Sheets Name            name = name + "$";            string strExcel = "";            OleDbDataAdapter myCommand = null;            DataSet ds = null;            strExcel = "select * from [" + name + "]";            myCommand = new OleDbDataAdapter(strExcel, strConn);            ds = new DataSet();            myCommand.Fill(ds, "RecordTable");            conn.Close();            if(name.Equals("记录表$"))            {                DataTable dt = ImportExcelFile(Path);                ReplaceWorkMatter(ds,dt);            }                        return ds;        }
2.导出：

 public DataSet ExcelToDS(string Path, int i)        {            FileInfo file = new FileInfo(Path);            string extension = file.Extension;            string strConn = "";            switch (extension)            {                case ".xls":                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                    break;                case ".xlsx":                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";                    break;                default:                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";                    break;            }            //ModifyRegeditTypeGuessRow(0);            OleDbConnection conn = new OleDbConnection(strConn);            conn.Open();            System.Data.DataTable sTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);            //Sheets Name            string tableName = sTable.Rows[i][2].ToString().Trim();            if (tableName == "")            {                return null;            }            else            {                tableName = "[" + tableName + "]";            }            string strExcel = "";            OleDbDataAdapter myCommand = null;            DataSet ds = null;            strExcel = "select * from " + tableName;            myCommand = new OleDbDataAdapter(strExcel, strConn);            ds = new DataSet();            myCommand.Fill(ds, "RecordTable");            conn.Close();            //if (i==0)            //{            //    DataTable dt = ImportExcelFile(Path);            //    ReplaceWorkMatter(ds, dt);            //}                       return ds;        }
使用这种方法时，曾经遇到过Excel的某列前面部分是数字，后面部分是字符。通过这种方法导入的时候发现后面字符部分的数据并没有导入。还有，使用“不包含表头的连接”时，在Excel中某一行是列名行（列名一般是字符），而该列的值若是数字或日期等，导入DataTable时，列名无法导入。调试后发现，导入的时候，系统会根据Excel里面的内容为DataTable的列设置类型。前面是数字，这一列就是整型的了，后面的字符当然导不进去了。
 
IMEX是用来告诉驱动程序使用Excel文件的模式，其值有0、1、2三种，分别代表导出、导入、混合模式。当我们设置IMEX＝1时将强制混合数据转换为文本，但仅仅这种设置并不可靠，IMEX＝1只确保在某列前8行数据至少有一个是文本项的时候才起作用，它只是把查找前8行数据中数据类型占优选择的行为作了略微的改变。例如某列前8行数据全为纯数字，那么它仍然以数字类型作为该列的数据类型，随后行里的含有文本的数据仍然变空。 
另一个改进的措施是IMEX＝1与注册表值TypeGuessRows配合使用，TypeGuessRows 值决定了ISAM 驱动程序从前几条数据采样确定数据类型，默认为“8”。可以通过修改“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel”下的该注册表值来更改采样行数。（http://liweibird.blog.51cto.com/631764/274434）

二.NOPI方式

    使用此方式效率高，避免了OLEDB的数据截断，不依赖于系统Offic

using System;using System.Collections.Generic;using System.Text;using System.IO;using NPOI.SS.UserModel;        //NPOIusing NPOI.HSSF.Util;           //NPOIusing NPOI.HSSF.UserModel;      //NPOIusing NPOI.XSSF.UserModel;      //NPOIusing System.Data.SqlClient;using System.Data;

 

1.导入

/// <summary>/// Excel某sheet中内容导入到DataTable中/// 区分xsl和xslx分别处理/// </summary>/// <param name="filePath">Excel文件路径,含文件全名</param>/// <param name="sheetName">此Excel中sheet名</param>/// <returns></returns>public DataTable ExcelSheetImportToDataTable(string filePath, string sheetName){                DataTable dt = new DataTable();    if (Path.GetExtension(filePath).ToLower() == ".xls".ToLower())    {//.xls        #region .xls文件处理:HSSFWorkbook        try        {            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))            {                hssfworkbook = new HSSFWorkbook(file);            }        }        catch (Exception e)        {            throw e;        }                    ISheet sheet = hssfworkbook.GetSheet(sheetName);        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();        HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);                        //一行最后一个方格的编号 即总的列数          for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)        {            //SET EVERY COLUMN NAME            HSSFCell cell = (HSSFCell)headerRow.GetCell(j);            dt.Columns.Add(cell.ToString());        }        while (rows.MoveNext())        {            IRow row = (HSSFRow)rows.Current;            DataRow dr = dt.NewRow();            if (row.RowNum == 0) continue;//The firt row is title,no need import            for (int i = 0; i < row.LastCellNum; i++)            {                if (i>=dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213                {                    break;                }                ICell cell = row.GetCell(i);                if ((i==0)&&(string.IsNullOrEmpty(cell.ToString())==true))//每行第一个cell为空,break                {                    break;                }                if (cell == null)                {                    dr[i] = null;                }                else                {                    dr[i] = cell.ToString();                }            }                                dt.Rows.Add(dr);        }        #endregion    }    else    {//.xlsx        #region .xlsx文件处理:XSSFWorkbook        try        {            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))            {                hssfworkbook = new XSSFWorkbook(file);            }        }        catch (Exception e)        {            throw e;        }                    ISheet sheet = hssfworkbook.GetSheet(sheetName);        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();        XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);                        //一行最后一个方格的编号 即总的列数          for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)        {            //SET EVERY COLUMN NAME            XSSFCell cell = (XSSFCell)headerRow.GetCell(j);            dt.Columns.Add(cell.ToString());        }        while (rows.MoveNext())        {            IRow row = (XSSFRow)rows.Current;            DataRow dr = dt.NewRow();            if (row.RowNum == 0) continue;//The firt row is title,no need import            for (int i = 0; i < row.LastCellNum; i++)            {                if (i >= dt.Columns.Count)//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213                {                    break;                }                ICell cell = row.GetCell(i);                if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break                {                    break;                }                if (cell == null)                {                    dr[i] = null;                }                else                {                    dr[i] = cell.ToString();                }            }            dt.Rows.Add(dr);        }        #endregion    }    return dt;}

 2.导出

 /// <summary>        /// NPOI导出Excel，不依赖本地是否装有Excel，导出速度快        /// </summary>        /// <param name="dataGridView1">要导出的dataGridView控件</param>        /// <param name="sheetName">sheet表名</param>        ///         public static void ExportToExcel(DataGridView dataGridView1, string sheetName)        {            SaveFileDialog fileDialog = new SaveFileDialog();            fileDialog.Filter = "Excel(97-2003)|*.xls";            if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)            {                return;            }            //不允许dataGridView显示添加行，负责导出时会报最后一行未实例化错误            dataGridView1.AllowUserToAddRows = false;            HSSFWorkbook workbook = new HSSFWorkbook();            ISheet sheet = workbook.CreateSheet(sheetName);            IRow rowHead = sheet.CreateRow(0);            //填写表头            for (int i = 0; i < dataGridView1.Columns.Count; i++)            {                rowHead.CreateCell(i, CellType.String).SetCellValue(dataGridView1.Columns[i].HeaderText.ToString());            }            //填写内容            for (int i = 0; i < dataGridView1.Rows.Count; i++)            {                IRow row = sheet.CreateRow(i + 1);                for (int j = 0; j < dataGridView1.Columns.Count; j++)                {                    row.CreateCell(j, CellType.String).SetCellValue(dataGridView1.Rows[i].Cells[j].Value.ToString());                }            }            using (FileStream stream = File.OpenWrite(fileDialog.FileName))            {                workbook.Write(stream);                stream.Close();            }            MessageBox.Show("导出数据成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);            GC.Collect();        }