using System;

 using System.Collections.Generic;

 using System.Data;

 using System.Data.OleDb;

 using System.IO;

 using System.Linq;

 using System.Runtime.InteropServices;

 using System.Text;

 using System.Text.RegularExpressions;

 using System.Windows.Forms;

 namespace ExcelDemo

 {

     /// <summary>

     /// Excel帮助类

     /// string column = "商品编码,商品名称,刊登单号,门店名称";

     /// 导入数据

     /// var action = new Action<string, DataTable>((str, dtExcel) =>

     /// {

     /// this.dgvData.DataSource = dtExcel;

     /// });

     /// excelHelper.ImportExcelToDataTable(this, action, "Ebay侵权下线");

     /// 导出模版

     /// string message = string.Empty;

     //  excelHelper.SaveExcelTemplate(column.Split(','), "Ebay侵权下线", "Ebay侵权下线", ref message);

     /// </summary>

     public class ExcelHelper

     {

         [DllImport("User32.dll", CharSet = CharSet.Auto)]

         public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);

         /// <summary>

         /// 保存Excel模版

         /// </summary>

         /// <param name="columns">列名,例如：商品编码,商品名称,刊登单号,门店名称</param>

         /// <param name="FileName">文件名，例如：Ebay侵权下线</param>

         /// <param name="SheetName">工作表名称，例如：Ebay侵权下线</param>

         /// <param name="message">错误信息</param>

         public void SaveExcelTemplate(string[] columns, string FileName, string SheetName, ref string message)

         {

             string Filter = "Excel文件|*.csv|Excel文件|*.xls|Excel文件|*.xlsx";

             SaveFileDialog saveFileDialog1 = new SaveFileDialog();

             saveFileDialog1.DefaultExt = "csv";

             saveFileDialog1.FileName = FileName;

             saveFileDialog1.Filter = Filter;

             saveFileDialog1.FilterIndex = ;

             saveFileDialog1.RestoreDirectory = true;

             saveFileDialog1.CreatePrompt = true;

             saveFileDialog1.Title = "Excel文件";

             saveFileDialog1.InitialDirectory = Directory.GetCurrentDirectory();

             if (saveFileDialog1.ShowDialog() != DialogResult.OK)

                 return;

             //获得文件路径

             string localFilePath = saveFileDialog1.FileName.ToString();

             if (Regex.IsMatch(localFilePath, @"\.csv$"))

             {

                 localFilePath = Regex.Replace(saveFileDialog1.FileName, @"\.csv$", "", RegexOptions.IgnoreCase) + ".csv";

                 File.WriteAllText(localFilePath, string.Join(",", columns), Encoding.Default);

             }

             else

             {

                 //获取文件路径，不带文件名

                 ArrayToExcelTemplate(columns, localFilePath, SheetName, ref message);

             }

             if (string.IsNullOrEmpty(message))

                 MessageBox.Show("\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);

         }

         /// <summary>

         /// 导出模版

         /// </summary>

         /// <param name="columns">列名,例如：商品编码,商品名称,刊登单号,门店名称</param>

         /// <param name="localFilePath">本地路径</param>

         /// <param name="SheetName">工作表名称，例如：Ebay侵权下线</param>

         /// <param name="message">错误信息</param>

         public void ArrayToExcelTemplate(string[] columns, string localFilePath, string SheetName, ref string message)

         {

             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

             if (xlApp == null)

             {

                 message = "无法创建Excel对象，可能计算机未安装Excel！";

                 return;

             }

             //創建Excel對象

             Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

             Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

             Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

             if (worksheet == null) worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(Type.Missing, Type.Missing, , Type.Missing);

             Microsoft.Office.Interop.Excel.Range range = null;

             long totalCount = columns.Length;

             worksheet.Name = SheetName;//第一个sheet在Excel中显示的名称

             int c;

             c = ;

             ////写入标题

             for (int i = , count = columns.Length; i < count; i++)

             {

                 //if (string.IsNullOrEmpty(columns[i])) continue;

                 worksheet.Cells[, c + ] = columns[i];

                 range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[, c + ];

                 range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中

                 c++;

             }

             try

             {

                 localFilePath = Regex.Replace(localFilePath, ".xls$|.xlsx$", "", RegexOptions.IgnoreCase);

                 localFilePath += xlApp.Version.CompareTo("11.0") ==  ? ".xls" : ".xlsx";

                 workbook.SaveCopyAs(localFilePath);

             }

             catch (Exception ex)

             {

                 message = "生成Excel附件过程中出现异常，详细信息如：" + ex.ToString();

             }

             try

             {

                 if (xlApp != null)

                 {

                     int lpdwProcessId;

                     GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), out lpdwProcessId);

                     System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();

                 }

             }

             catch (Exception ex)

             {

                 message = "Delete Excel Process Error:" + ex.Message;

             }

         }

         /// <summary>

         /// 导入Excel

         /// </summary>

         /// <param name="form"></param>

         /// <param name="callback"></param>

         public void ImportExcelToDataTable(Form form, Action<string, DataTable> callback, string SheetName = "Sheet1")

         {

             string Filter = "Excel文件|*.csv|Excel文件|*.xls|Excel文件|*.xlsx";

             OpenFileDialog openFileDialog1 = new OpenFileDialog();

             openFileDialog1.Title = "Excel文件";

             openFileDialog1.Filter = Filter;

             openFileDialog1.ValidateNames = true;

             openFileDialog1.CheckFileExists = true;

             openFileDialog1.CheckPathExists = true;

             if (openFileDialog1.ShowDialog() != DialogResult.OK)

                 return;

             var action = new Action(() =>

             {

                 string localFilePath = openFileDialog1.FileName;

                 if (File.Exists(localFilePath))

                 {

                     string message = string.Empty;

                     string fileExten = Path.GetExtension(localFilePath);

                     DataTable dtExcel;

                     if (fileExten.ToLower().Contains(".csv"))

                     {

                         dtExcel = ImportCSVFile(localFilePath, "Table1", ref message);

                     }

                     else

                     {

                         dtExcel = ImportExcelFile(localFilePath, "Table1", SheetName, ref message);

                     }

                     if (callback != null)

                     {

                         if (form.InvokeRequired)

                         {

                             form.Invoke(callback, message, dtExcel);

                         }

                         else

                         {

                             callback(message, dtExcel);

                         }

                     }

                 }

             });

             action.BeginInvoke(null, null);

         }

         /// <summary>

         /// 执行导入

         /// </summary>

         /// <param name="strFileName">对应文件路径</param>

         /// <param name="typeName">返回的Table名称</param>

         /// <param name="message">返回的错误</param>

         /// <returns>DataTable</returns>

         public DataTable ImportCSVFile(string strFileName, string typeName, ref string message)

         {

             if (string.IsNullOrEmpty(strFileName)) return null;

             string line = string.Empty;

             string[] split = null;

             bool isReplace;

             int subBegion;

             int subEnd;

             string itemString = string.Empty;

             string oldItemString = string.Empty;

             DataTable table = new DataTable(typeName);

             DataRow row = null;

             StreamReader sr = new StreamReader(strFileName, System.Text.Encoding.Default);

             //创建与数据源对应的数据列

             line = sr.ReadLine();

             split = line.Split(',');

             foreach (String colname in split)

             {

                 table.Columns.Add(colname, System.Type.GetType("System.String"));

             }

             //将数据填入数据表

             int j = ;

             while ((line = sr.ReadLine()) != null)

             {

                 subEnd = ;

                 subBegion = ;

                 if (line.IndexOf('\"') > )

                 {

                     isReplace = true;

                 }

                 else

                 {

                     isReplace = false;

                 }

                 itemString = string.Empty;

                 while (isReplace)

                 {

                     subBegion = line.IndexOf('\"');

                     subEnd = line.Length - ;

                     if (line.Length -  > subBegion)

                     {

                         subEnd = line.IndexOf('\"', subBegion + );

                     }

                     if (subEnd - subBegion > )

                     {

                         itemString = line.Substring(subBegion, subEnd - subBegion + );

                         oldItemString = itemString;

                         itemString = itemString.Replace(',', '|').Replace("\"", string.Empty);

                         line = line.Replace(oldItemString, itemString);

                     }

                     if (line.IndexOf('\"') == -)

                     {

                         isReplace = false;

                     }

                 }

                 j = ;

                 row = table.NewRow();

                 split = line.Split(',');

                 foreach (String colname in split)

                 {

                     row[j] = colname.Replace('|', ',');

                     j++;

                 }

                 table.Rows.Add(row);

             }

             sr.Close();

             //显示数据 

             return table;

         }

         /// <summary>

         /// Excel执行导入

         /// </summary>

         /// <param name="strFileName">对应文件路径</param>

         /// <param name="typeName">返回的Table名称</param>

         /// <param name="message">返回的错误</param>

         /// <returns></returns>

         public DataTable ImportExcelFile(string strFileName, string typeName, string SheetName, ref string message)

         {

             if (string.IsNullOrEmpty(strFileName)) return null;

             DataSet Exceldt;

             Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

             OleDbConnection con = new OleDbConnection();

             try

             {

                 //OleDbDataAdapter ExcelO = new OleDbDataAdapter(selectStr, @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileName + ";Extended Properties=Excel 8.0;");

                 string ConnStr = xlApp.Version.CompareTo("11.0") ==  ? @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + strFileName + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES;'" : @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 8.0;IMEX=1;HDR=YES'; Data Source=" + strFileName;

                 con.ConnectionString = ConnStr;

                 con.Open();

                 DataTable dtOle = con.GetSchema("Tables");

                 DataTableReader dtReader = new DataTableReader(dtOle);

                 string TableName = "";

                 while (dtReader.Read())

                 {

                     TableName = dtReader["Table_Name"].ToString();

                     break;

                 }

                 OleDbDataAdapter excel = new OleDbDataAdapter(string.Format("select * from [" + SheetName + "$];", TableName), ConnStr);

                 //OleDbDataAdapter excel = new OleDbDataAdapter(selectStr, @"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 8.0;IMEX=1;HDR=YES'; Data Source=" + strFileName);

                 Exceldt = new DataSet();

                 excel.Fill(Exceldt, typeName);

                 return Exceldt.Tables.Count >  ? Exceldt.Tables[] : null;

             }

             catch (OleDbException ex)

             {

                 message = ex.Message;

                 return null;

             }

             catch (Exception ex)

             {

                 message = ex.Message;

                 return null;

             }

             finally

             {

                 con.Close();

             }

         }

     }

 }

 using System;

 using System.Collections.Generic;

 using System.ComponentModel;

 using System.Data;

 using System.Drawing;

 using System.Linq;

 using System.Text;

 using System.Windows.Forms;

 namespace ExcelDemo

 {

     /// <summary>

     /// 针对Excel写的帮助模型

     /// </summary>

     public partial class Form1 : Form

     {

         #region 变量

         /// <summary>

         /// 导出模板列集合

         /// </summary>

         List<string> columnListOut = new List<string>()

         {

             "列1",

             "列2",

             "列3",

             "列4"

         };

         /// <summary>

         /// 导出模板文件名称

         /// </summary>

         string FileName = "导出模板";

         /// <summary>

         /// Excel底层页签名称

         /// </summary>

         string SheetName = "Excel页签名称";

         /// <summary>

         /// ExcelHelper实例化

         /// </summary>

         ExcelHelper excelHelper = new ExcelHelper();

         #endregion

         #region 初始化、数据加载

         public Form1()

         {

             InitializeComponent();

         }

         #endregion

         #region 控件事件

         /// <summary>

         /// 选择文件

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         private void btnChooseFile_Click(object sender, EventArgs e)

         {

             //对于选择文件转化的DataTable和提示信息msg的委托

             Action<string, DataTable> action = new Action<string, DataTable>((string str, DataTable dt) =>

             {

                 if (dt == null || dt.Rows.Count == )

                 {

                     MessageBox.Show("dt为空的");

                     return;

                 }

                 if (dt.Rows.Count > )

                 {

                     MessageBox.Show("导入的数据已超过最大限制1000条");

                     return;

                 }

                 if (!this.columnListOut.ToArray().All(t => dt.Columns.Contains(t)))

                 {

                     MessageBox.Show("导入的数据字段不匹配");

                     return;

                 }

                 //获取列1的可枚举集合

                 IEnumerable<string> column1List = dt.Rows.Cast<DataRow>().Select(r => r["列1"].ToString());

                 //验证列1必须是整数切不能是负数

                 decimal isDecimal = ;

                 foreach (var item in column1List)

                 {

                     if ((!decimal.TryParse(item, out isDecimal)) && !string.IsNullOrEmpty(item))

                     {

                         MessageBox.Show("列1必须是Decimal类型");

                         return;

                     }

                     if (isDecimal < )

                     {

                         MessageBox.Show("列1不允许是负数");

                         return;

                     }

                 }

                 dt.AcceptChanges();

                 this.dgv.DataSource = dt;

             });

             this.excelHelper.ImportExcelToDataTable(this, action, this.SheetName);

         }

         /// <summary>

         /// 导出模板

         /// </summary>

         /// <param name="sender"></param>

         /// <param name="e"></param>

         private void btnOut_Click(object sender, EventArgs e)

         {

             string[] columnList = this.columnListOut.ToArray();

             string msg = string.Empty;

             this.excelHelper.SaveExcelTemplate(columnList, this.FileName, this.SheetName, ref msg);

         }

         #endregion

     }

 }