using System;using System.Collections.Generic;using System.Linq;using System.Text;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System.Data;using System.Windows.Forms;using System.IO;using System.Data.OleDb;using System.Collections;//////使用该类 需要添加 NOPI.dll 的引用///版本为 2.0.0.0 namespace CommonClass{    public static class ExcelHelper    {        #region  导出        /// 
        /// 导出一个Datatable的数据到Excel文件        /// 
        /// 需要导出的DataTable        public static void ExportDataTableToExcel(DataTable dt)        {            try            {                HSSFWorkbook wk = new HSSFWorkbook();                ISheet sheet = wk.CreateSheet("Sheet1");                int RowCount = dt.Rows.Count;                int ColumnCount = dt.Columns.Count;                IRow row = sheet.CreateRow(0);                for (int j = 0; j < ColumnCount; j++) //列标题                {                    sheet.SetColumnWidth(j, 20 * 256);                    ICell cell = row.CreateCell(j);                    cell.SetCellType(CellType.STRING);                    cell.SetCellValue(dt.Columns[j].ColumnName);                }                for (int i = 0; i < RowCount; i++)                {                    row = sheet.CreateRow(i + 1);                    for (int j = 0; j < ColumnCount; j++)                    {                        sheet.SetColumnWidth(j, 20 * 256);                        ICell cell = row.CreateCell(j);                        cell.SetCellType(CellType.STRING);                        cell.SetCellValue(dt.Rows[i][j].ToString());                    }                }                SaveFileDialog dlg = new SaveFileDialog();                dlg.Filter = "Execl files (*.xls)|*.xls";                dlg.FilterIndex = 0;                dlg.RestoreDirectory = true;                dlg.CreatePrompt = true;                dlg.Title = "保存为Excel文件";                if (dlg.ShowDialog() == DialogResult.OK)                {                    using (FileStream fs = File.OpenWrite(dlg.FileName)) //打开一个xls文件，如果没有则自行创建，如果存在文件则在创建是不要打开该文件！                    {                        wk.Write(fs);   //向打开的这个xls文件中写入Sheet1表并保存。                        MessageBox.Show("导出成功！");                    }                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message);            }        }        /// 
        /// 导出一个DataGridView的数据到Excel文件        /// 
        /// 绑定了数据源的DataGridView控件        public static void ExportDataGridViewToExcel(DataGridView Gridview)        {            HSSFWorkbook wk = new HSSFWorkbook();            ISheet sheet = wk.CreateSheet("Sheet1");            int RowCount = Gridview.RowCount;            int ColumnCount = Gridview.ColumnCount;            IRow row = sheet.CreateRow(0);            for (int j = 0; j < ColumnCount; j++) //列标题            {                sheet.SetColumnWidth(j, 20 * 256);                ICell cell = row.CreateCell(j);                cell.SetCellType(CellType.STRING);                cell.SetCellValue(Gridview.Columns[j].HeaderText);            }            for (int i = 0; i < RowCount; i++)            {                row = sheet.CreateRow(i + 1);                for (int j = 0; j < ColumnCount; j++)                {                    sheet.SetColumnWidth(j, 20 * 256);                    ICell cell = row.CreateCell(j);                    cell.SetCellType(CellType.STRING);                    cell.SetCellValue(Gridview.Rows[i].Cells[j].Value.ToString());                }            }            SaveFileDialog dlg = new SaveFileDialog();            dlg.Filter = "Execl files (*.xls)|*.xls";            dlg.FilterIndex = 0;            dlg.RestoreDirectory = true;            dlg.CreatePrompt = true;            dlg.Title = "保存为Excel文件";            if (dlg.ShowDialog() == DialogResult.OK)            {                using (FileStream fs = File.OpenWrite(dlg.FileName)) //打开一个xls文件，如果没有则自行创建，如果存在文件则在创建是不要打开该文件！                {                    wk.Write(fs);   //向打开的这个xls文件中写入Sheet1表并保存。                    MessageBox.Show("导出成功！");                }            }        }        #endregion        #region 读取        /// 
        /// 读取一个Excel文件（只读取该文件的第一个Sheet的数据）        /// 
        /// 
     
      返回一个包含Excel文件数据的DataTable
             public static DataTable ReadExcelToDataTable()        {            DataTable dt = new DataTable();            OpenFileDialog OpenDialog = new OpenFileDialog();            OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx";            OpenDialog.FilterIndex = 0;            OpenDialog.RestoreDirectory = true;            OpenDialog.Title = "读取Excel文件";            OleDbConnection con;            OleDbDataAdapter da;            DataSet ds = new DataSet();            if (OpenDialog.ShowDialog() == DialogResult.OK)            {                if (string.IsNullOrEmpty(OpenDialog.FileName))                {                    MessageBox.Show("请选择Excel文件！");                    return dt;                }                else                {                    try                    {                        string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.'));                        string connStr = "";                        switch (filename)                        {                            case ".xls":                                {                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";                                    break;                                }                            case ".xlsx":                                {                                    connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";                                    break;                                }                            default:                                {                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";                                    break;                                }                        }                        string sql = "SELECT  * FROM [" + GetExcelFirstTableName(OpenDialog.FileName, connStr)[0].ToString() + "]  ";                        con = new OleDbConnection(connStr);                        con.Open();                        da = new OleDbDataAdapter(sql, con);                        da.Fill(ds);                        if (ds.Tables[0].Rows.Count == 0)                        {                            MessageBox.Show("选择的文件没数据！"); return dt;                        }                        else                        {                            dt = ds.Tables[0];                        }                        return dt;                    }                    catch (Exception ex)                    {                        MessageBox.Show(ex.Message); return dt;                    }                }            }            return dt;        }        /// 
        /// 读取一个Excel文件（读取该文件的所有Sheet的数据，但是该文件的所有Sheet数据格式必须一致）        /// 
        /// 
     
      返回一个包含Excel文件数据的DataTable
             public static DataTable ReadMoreExcelToDataTable()        {            DataTable dt = new DataTable();            OpenFileDialog OpenDialog = new OpenFileDialog();            OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx";            OpenDialog.FilterIndex = 0;            OpenDialog.RestoreDirectory = true;            OpenDialog.Title = "读取Excel文件";            OleDbConnection con;            OleDbDataAdapter da;            DataSet ds = new DataSet();            if (OpenDialog.ShowDialog() == DialogResult.OK)            {                if (string.IsNullOrEmpty(OpenDialog.FileName))                {                    MessageBox.Show("请选择Excel文件！");                    return dt;                }                else                {                    try                    {                        string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.'));                        string connStr = "";                        switch (filename)                        {                            case ".xls":                                {                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";                                    break;                                }                            case ".xlsx":                                {                                    connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";                                    break;                                }                            default:                                {                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";                                    break;                                }                        }                        ArrayList sqllist = new ArrayList();                        ArrayList tablelist = new ArrayList();                        tablelist = GetExcelAllTableName(OpenDialog.FileName, connStr);                        for (int i = 0; i < tablelist.Count; i++)                        {                            sqllist.Add("SELECT  * FROM [" + tablelist[i] + "]");                        }                        con = new OleDbConnection(connStr);                        con.Open();                        for (int i = 0; i < sqllist.Count; i++)                        {                            da = new OleDbDataAdapter(sqllist[i].ToString(), con);                            da.Fill(ds);                        }                        if (ds.Tables[0].Rows.Count == 0)                        {                            MessageBox.Show("选择的文件没数据！"); return dt;                        }                        else                        {                            dt = ds.Tables[0];                        }                        return dt;                    }                    catch (Exception ex)                    {                        MessageBox.Show(ex.Message); return dt;                    }                }            }            return dt;        }        /// 
        /// 读取一个Excel文件（读取该文件的所有Sheet的数据，该文件的Sheet数据格式可以一致）        /// 
        /// 
     
      返回一个包含Excel文件数据的DataSet
             public static DataSet ReadMoreNotSameExcelToDataTable()        {            DataTable dt = new DataTable();            OpenFileDialog OpenDialog = new OpenFileDialog();            OpenDialog.Filter = "Excel Files|*.xls|Excel Files|*.xlsx";            OpenDialog.FilterIndex = 0;            OpenDialog.RestoreDirectory = true;            OpenDialog.Title = "读取Excel文件";            OleDbConnection con;            OleDbDataAdapter da;            DataSet ds = new DataSet();            if (OpenDialog.ShowDialog() == DialogResult.OK)            {                if (string.IsNullOrEmpty(OpenDialog.FileName))                {                    MessageBox.Show("请选择Excel文件！");                    return ds;                }                else                {                    try                    {                        string filename = OpenDialog.FileName.Substring(OpenDialog.FileName.LastIndexOf('.'));                        string connStr = "";                        switch (filename)                        {                            case ".xls":                                {                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";                                    break;                                }                            case ".xlsx":                                {                                    connStr = "Provider=Microsoft.Ace.OLEDB.12.0;data source=" + OpenDialog.FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";                                    break;                                }                            default:                                {                                    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + OpenDialog.FileName + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";                                    break;                                }                        }                        ArrayList sqllist = new ArrayList();                        ArrayList tablelist = new ArrayList();                        tablelist = GetExcelAllTableName(OpenDialog.FileName, connStr);                        for (int i = 0; i < tablelist.Count; i++)                        {                            sqllist.Add("SELECT  * FROM [" + tablelist[i] + "]");                        }                        con = new OleDbConnection(connStr);                        con.Open();                        for (int i = 0; i < sqllist.Count; i++)                        {                            da = new OleDbDataAdapter(sqllist[i].ToString(), con);                            da.Fill(ds, "Table" + i);                        }                        bool isHave = false;                        for (int i = 0; i < ds.Tables.Count; i++)                        {                            if (ds.Tables[i].Rows.Count != 0)                            {                                isHave = true;                            }                        }                        if (!isHave)                        {                            MessageBox.Show("选择的文件没数据！"); return ds;                        }                        return ds;                    }                    catch (Exception ex)                    {                        MessageBox.Show(ex.Message); return ds;                    }                }            }            return ds;        }        /// 
        /// 获取第一个Sheet的名称        /// 
        /// Excel文件名称        /// 读取Excel文件数据的数据连接        /// 
             private static ArrayList GetExcelFirstTableName(string excelFileName, string strExtension)        {            ArrayList tablenamelist = new ArrayList();            try            {                if (File.Exists(excelFileName))                {                    using (OleDbConnection conn = new OleDbConnection(strExtension))                    {                        conn.Open();                        DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                        tablenamelist.Add(dt.Rows[0][2].ToString().Trim());                        return tablenamelist;                    }                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message.ToString());            }            return tablenamelist;        }        /// 
        /// 获取所有Sheet的名称        /// 
        /// Excel文件名称        /// 读取Excel文件数据的数据连接        /// 
             private static ArrayList GetExcelAllTableName(string excelFileName, string strExtension)        {            ArrayList tablenamelist = new ArrayList();            try            {                if (File.Exists(excelFileName))                {                    using (OleDbConnection conn = new OleDbConnection(strExtension))                    {                        conn.Open();                        DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                        for (int i = 0; i < dt.Rows.Count; i++)                        {                            tablenamelist.Add(dt.Rows[i][2].ToString().Trim());                        }                        return tablenamelist;                    }                }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message.ToString());            }            return tablenamelist;        }        #endregion    }}